使用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如下：