使用Spring MVC生成Excel文档

Spring不仅支持jsp,velocity,freemarker,同时可以将数据生成到Excel,PDF等非html文档

首先，我们编写控制器,返回逻辑名“ListStudentUseExcel”

package  Action;

import  javax.servlet.http.HttpServletRequest;
import  javax.servlet.http.HttpServletResponse;

import  org.springframework.validation.BindException;
import  org.springframework.web.servlet.ModelAndView;
import  org.springframework.web.servlet.mvc.AbstractCommandController;

public   class  TestExcelController  extends  AbstractCommandController  ... {



    protected ModelAndView handle(HttpServletRequest arg0, HttpServletResponse arg1, Object arg2, BindException arg3) throws Exception ...{
        return null;
    }

    protected ModelAndView handleRequestInternal(HttpServletRequest arg0, HttpServletResponse arg1) throws Exception ...{
        return new ModelAndView("ListStudentUseExcel","","");
    }

}

 配置文件：

 

<? xml version="1.0" encoding="UTF-8" ?>
<! DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"  >
< beans >

< bean  id ="simpleUrlMapping"  class ="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping" >
  < property  name ="mappings" >
    < props >
        < prop  key ="/excel.mvc" > TestExcelController </ prop >
    </ props >
  </ property >
</ bean >


< bean  id ="ListStudentUseExcel"  class ="Action.ListStudentUseExcel" ></ bean >

< bean  id ="beanNameViewResolver"  class ="org.springframework.web.servlet.view.BeanNameViewResolver" />  

< bean  id ="TestExcelController"  class ="Action.TestExcelController" />


</ beans >

 

由于上述配置文件采用了beanNameViewResolver的当时，所以，我们要编写一个合controller返回逻辑名相同的
ViewClass---ListStudentUseExcel,代码如下：

 

package  Action;

import  java.util.ArrayList;
import  java.util.Iterator;
import  java.util.Map;

import  javax.servlet.http.HttpServletRequest;
import  javax.servlet.http.HttpServletResponse;

import  model.Student;

import  org.apache.poi.hssf.usermodel.HSSFCellStyle;
import  org.apache.poi.hssf.usermodel.HSSFDataFormat;
import  org.apache.poi.hssf.usermodel.HSSFRow;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import  org.springframework.web.servlet.view.document.AbstractExcelView;

public   class  ListStudentUseExcel  extends  AbstractExcelView  ... {

    protected  void buildExcelDocument(Map model,
            HSSFWorkbook workbook,
            HttpServletRequest request,
            HttpServletResponse response)throws Exception...{

        
          //设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
          response.setContentType("APPLICATION/OCTET-STREAM"); 
          response.setHeader("Content-Disposition", 
          "attachment; filename="" + "excel.xls" + """); 

        //构造数据
        Student stu1=new Student("gaoxiang1","male1","20060101",1);
        Student stu2=new Student("gaoxiang2","male2","20060102",2);
        Student stu3=new Student("gaoxiang3","male3","20060103",3);
        Student stu4=new Student("gaoxiang4","male4","20060104",4);
        Student stu5=new Student("gaoxiang5","male5","20060105",5);
        ArrayList stuList=new ArrayList();
        stuList.add(stu1);
        stuList.add(stu2);
        stuList.add(stu3);
        stuList.add(stu4);
        stuList.add(stu5);
        
        //产生Excel表头
        HSSFSheet sheet=workbook.createSheet("studentList");
        HSSFRow header=sheet.createRow(0); //第0行
        //产生标题列
        header.createCell((short)0).setCellValue("name");
        header.createCell((short)1).setCellValue("sex");
        header.createCell((short)2).setCellValue("date");
        header.createCell((short)3).setCellValue("count");
        HSSFCellStyle cellStyle=workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));
        
        //填充数据
        int rowNum=1;
        for (Iterator iter = stuList.iterator(); iter.hasNext();) ...{
            Student element = (Student) iter.next();
            HSSFRow row=sheet.createRow(rowNum++);
            row.createCell((short)0).setCellValue(element.getName().toString());
            row.createCell((short)1).setCellValue(element.getSex().toString());
            row.createCell((short)2).setCellValue(element.getDate().toString());
            row.getCell((short)2).setCellStyle(cellStyle);
            row.createCell((short)3).setCellValue(element.getCount());
        }
        
        //列总和计算
        HSSFRow row=sheet.createRow(rowNum);
        row.createCell((short)0).setCellValue("TOTAL:");
        String formual="SUM(D2:D"+rowNum+")"; //D2到D[rowNum]单元格起(count数据)
        row.createCell((short)3).setCellFormula(formual);
        
    }

}

测试页面：/exlce/ListStudentUseExcel.jsp

 

<% ... @ page language="java" contentType="text/html; charset=GB18030"
    pageEncoding="GB18030" %>
<! DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd" >
< html >
< head >

< meta  http-equiv ="Content-Type"  content ="text/html; charset=GB18030" >
< title > Insert title here </ title >
</ head >
< body >

< input  type ="button"  onclick ="javascript:window.location.href='<%=request.getContextPath() %>/excel.mvc'"  value ="download excel" ></ input >

</ body >
</ html >

 

运行ListStudentUseExcel.jsp，点击下载按钮，程序运行结果及生成的excel如下：