使用Spring MVC生成Excel文档
来源:互联网 发布:烽火通信 知乎 编辑:程序博客网 时间:2024/06/14 14:13
Spring不仅支持jsp,velocity,freemarker,同时可以将数据生成到Excel,PDF等非html文档
首先,我们编写控制器,返回逻辑名“ListStudentUseExcel”
package Action;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
import org.springframework.validation.BindException;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.AbstractCommandController;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)
public class TestExcelController extends AbstractCommandController ...{
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif)
protected ModelAndView handle(HttpServletRequest arg0, HttpServletResponse arg1, Object arg2, BindException arg3) throws Exception ...{
return null;
}
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif)
protected ModelAndView handleRequestInternal(HttpServletRequest arg0, HttpServletResponse arg1) throws Exception ...{
return new ModelAndView("ListStudentUseExcel","","");
}
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
}
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
配置文件:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd" >
<beans>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<bean id="simpleUrlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
<property name="mappings">
<props>
<prop key="/excel.mvc">TestExcelController</prop>
</props>
</property>
</bean>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<bean id="ListStudentUseExcel" class="Action.ListStudentUseExcel"></bean>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver"/>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<bean id="TestExcelController" class="Action.TestExcelController"/>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
</beans>
由于上述配置文件采用了beanNameViewResolver的当时,所以,我们要编写一个合controller返回逻辑名相同的
ViewClass---ListStudentUseExcel,代码如下:
package Action;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
import model.Student;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
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;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)
public class ListStudentUseExcel extends AbstractExcelView ...{
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
protected void buildExcelDocument(Map model,
HSSFWorkbook workbook,
HttpServletRequest request,
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif)
HttpServletResponse response)throws Exception...{
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
//设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition",
"attachment; filename="" + "excel.xls" + """);
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
//构造数据
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;
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedSubBlockStart.gif)
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);
}
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif)
}
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
测试页面:/exlce/ListStudentUseExcel.jsp
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)
<%...@ 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>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>Insert title here</title>
</head>
<body>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
<input type="button" onclick="javascript:window.location.href='<%=request.getContextPath() %>/excel.mvc'" value="download excel"></input>
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
</body>
</html>
运行ListStudentUseExcel.jsp,点击下载按钮,程序运行结果及生成的excel如下:
![](file:///C:/Documents%20and%20Settings/gaoxiang/桌面/11.JPG)