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","","");
}
}
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 >
<! 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);
}
}
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 >
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如下: