Spring提供EXCEL下载方式(参考其他视频教程的进行整理的）

需要poi-2.5.1.jar。Spring中已经导入。

 

分两个步骤：

1.     从数据库中把数据读出来，通过poi组件把数据放到Excel里。

2.     将生成的EXCEL以输入流的形式（InputStream）返回到浏览器端。

 

一、         在service接口里定义一个方法：

方法实现如下：

public InputStream getInputStream() {

       HSSFWorkbook wb = new HSSFWorkbook();

       HSSFSheet sheet = wb.createSheet("sheet1");

      

       HSSFRow row = sheet.createRow(0);

      

       HSSFCell cell = row.createCell((short)0);

       cell.setEncoding(HSSFCell.ENCODING_UTF_16);

       cell.setCellValue("序号");

      

       cell = row.createCell((short)1);

       cell.setEncoding(HSSFCell.ENCODING_UTF_16);

       cell.setCellValue("姓");

      

       cell = row.createCell((short)2);

       cell.setEncoding(HSSFCell.ENCODING_UTF_16);

       cell.setCellValue("名");

      

       cell = row.createCell((short)3);

       cell.setEncoding(HSSFCell.ENCODING_UTF_16);

       cell.setCellValue("年龄");

      

       //查数据库

       List<User> list = this.findAll();

       for(int i=0;i<list.size();i++){

           User user = list.get(i);

          

           row = sheet.createRow(i+1);

          

           cell = row.createCell((short)0);

           cell.setEncoding(HSSFCell.ENCODING_UTF_16);

           cell.setCellValue(i+1);

 

          

           cell = row.createCell((short)1);

           cell.setEncoding(HSSFCell.ENCODING_UTF_16);

           cell.setCellValue(user.getFirstname());

          

           cell = row.createCell((short)2);

           cell.setEncoding(HSSFCell.ENCODING_UTF_16);

           cell.setCellValue(user.getLastname());

          

           cell = row.createCell((short)3);

           cell.setEncoding(HSSFCell.ENCODING_UTF_16);

           cell.setCellValue(user.getAge());

       }

      

       ByteArrayOutputStream os = new ByteArrayOutputStream();

       try {

           wb.write(os);

       } catch (IOException e) {

           e.printStackTrace();

       }     

       byte[] content = os.toByteArray();

       InputStream is = new ByteArrayInputStream(content);

       return is;

    }

 

二、         添加一个action:如GenerateExcelAction

public class GenerateExcelAction extends ActionSupport {

    private UserService userService;

 

    public UserService getUserService() {

       return userService;

    }

 

    public void setUserService(UserService userService) {

       this.userService = userService;

    }

   

    public InputStream getDownloadFile(){

      

       return this.userService.getInputStream();

    }

    public String execute() throws Exception{

      

       return SUCCESS;

    }

}  

 

三、         在sturts.xml进行配置

<action name="generateExcel" class="generateExcelAction">

           <result type="stream">

              <param name="contentType">application/vnd.ms-excel</param>

              <param name="contentDisposition">filename="AllUsers.xls"</param>

              <param name="inputName">downloadFile</param>

           </result>

</action>

 

四、         在JSP页面生成链接

<s:a href="generateExcel">生成Excel</s:a>

 

五、         处理Excel文件

（1）            随机生成Excel的文件名：

定义一个类CharacterUtils

public class CharacterUtils {

    public static String getRandomString(int length){

       String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";

       Random random = new Random();

       StringBuffer sb = new StringBuffer();

      

       for(int i=0;i<length;i++){

           int number = random.nextInt(62);

           sb.append(str.charAt(number));

       }

       return sb.toString();

    }

   

    public static void main(String[] args){

       System.out.println(getRandomString(10));

    }

}

 

在UserServiceImpl里随机生成字符名称：

 

       String fileName = CharacterUtils.getRandomString(10);

     fileName = new StringBuffer(fileName).append(".xls").toString();

       File file = new File(fileName);

 

在struts.xml里添加一个属性：attachment 表示总是有下载框提示

如            <param name="contentDisposition">attachment;filename="AllUsers.xls"</param>

 

六、         让tomcat目录bin里的自动生成的文件自动删除：

添加下面一段代码即可：

new Thread(new Runnable(){

           public void run(){

              try {

                  Thread.sleep(1500);

              } catch (InterruptedException e) {

                  e.printStackTrace();

font-size: 10pt; color: bla