Apache POI组件操作Excel,制作报表(四)
上一篇我们介绍了如何制作复杂报表的分析和设计,本篇结合Spring MVC框架来说一下如何在Web层使用POI组件来动态生成报表供用户查看或下载。为什么选择Spring MVC框架,理由很简单,因为Spring的MVC框架对Excel视图的扩展使用的正是POI组件,那么使用Spring来整合就非常方便了,当然其它的框架都有自己的处理办法,这个可以具体咨询其对视图扩展支持的配置方法。
使用Spring对Excel的支持,需要扩展MVC的视图组件,就是要创建一个自定义的View,这个在后面会详细介绍,通过这个view,就可以将Excel报表展现出来了,非常方便。
基于Spring MVC的程序,那么首先要构建起来Spring MVC的环境,这个很简单,下面我们来看看如何搭建Spring MVC环境。我们使用Spring 3.0.5的库,但是配置方式不用注解,用传统的配置方式来说明。
使用Maven管理项目,引入必要的依赖,如下:
并建立如下的项目结构:
在web.xml描述符中,首先配置Spring配置文件的路径:
<context-param><param-name>contextConfigLocation</param-name><param-value>classpath:org/ourpioneer/spring/*.xml,classpath:org/ourpioneer/spring/*/*.xml</param-value></context-param><listener><listener-class>org.springframework.web.context.ContextLoaderListener</listener-class></listener>
<filter><filter-name>EncodingFilter</filter-name><filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class><init-param><param-name>encoding</param-name><param-value>UTF-8</param-value></init-param><init-param><param-name>forceEncoding</param-name><param-value>true</param-value></init-param></filter><filter-mapping><filter-name>EncodingFilter</filter-name><url-pattern>/*</url-pattern></filter-mapping>
<servlet><servlet-name>board</servlet-name><servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class><init-param><param-name>contextConfigLocation</param-name><param-value>/WEB-INF/Dispatcher-Servlet.xml</param-value></init-param></servlet><servlet-mapping><servlet-name>board</servlet-name><url-pattern>*.do</url-pattern></servlet-mapping>
<!-- 扩展命名拦截器 --><bean id="extensionInterceptor"/></list></property><property name="mappings"><props><prop key="/excel.do">ExcelController</prop></props></property></bean>
<bean id="ExcelController" ref="reportService" /><property name="reportView" ref="reportView" /></bean>
package org.ourpioneer.excel.web.controller;import java.util.HashMap;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.ourpioneer.excel.bean.CruiseServiceLocation;import org.ourpioneer.excel.service.ReportService;import org.ourpioneer.excel.web.views.ReportView;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.mvc.AbstractController;/** * Excel处理控制器 * * @author Nanlei * */public class ExcelController extends AbstractController {private ReportView reportView;private ReportService reportService;public void setReportView(ReportView reportView) {this.reportView = reportView;}public void setReportService(ReportService reportService) {this.reportService = reportService;}@Overrideprotected ModelAndView handleRequestInternal(HttpServletRequest request,HttpServletResponse response) throws Exception {HashMap model = new HashMap();List<CruiseServiceLocation> cslList = reportService.getCruiseLocationList();model.put("cruiseServiceLocationList", cslList);return new ModelAndView(reportView, model); }}
package org.ourpioneer.excel.service;import java.util.Arrays;import java.util.List;import org.ourpioneer.excel.bean.CruiseServiceLocation;/** * 报表数据业务类 * * @author Nanlei * */public class ReportService {/** * 获取数据 * * @return */public List<CruiseServiceLocation> getCruiseLocationList() {CruiseServiceLocation csl[] = new CruiseServiceLocation[21];csl[0] = new CruiseServiceLocation("T001", "北京市", "北京总部", "bj", "清华大学",20);...csl[20] = new CruiseServiceLocation("", "", "", "", "", 0);// 合并算法捕捉最后一行有问题,增补一行无效数据,计算时去除return Arrays.asList(csl);}}
package org.ourpioneer.excel.web.views;/** * 报表的Excel视图 * * @author Nanlei * */public class ReportView extends AbstractExcelView {@Overrideprotected void buildExcelDocument(Map<String, Object> model,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {// 从model中获取数据对象List<CruiseServiceLocation> cruiseServiceLocationList = (List<CruiseServiceLocation>) model.get("cruiseServiceLocationList");…}}
package org.ourpioneer.excel.web.interceptor;import java.net.URLEncoder;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;/** * 扩展名命名处理拦截器 * * @author Nanlei * */public class ExtensionInterceptor extends HandlerInterceptorAdapter {/** * 用在流程处理之后 */@Overridepublic void postHandle(HttpServletRequest request,HttpServletResponse response, Object handler,ModelAndView modelAndView) throws Exception {// 获取Controller过来的Model对象Map model = modelAndView.getModel();// 获取请求URIString uri = request.getRequestURI();// 生成文件的名称String exportFileName = null;if (uri.endsWith("excel.do")) {String time = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());exportFileName = "报表" + time + ".xls";}if (StringUtils.isNotEmpty(exportFileName)) {response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(exportFileName, "UTF-8"));}super.postHandle(request, response, handler, modelAndView);}}