当时项目很紧张,客户又变态。要求大部分查询都要提供pdf和excel下载。好在大部分查询功能类似,后台查询接口高度一致,可以提取相同的部分,减少重复开发。
其他一些需求:
1、 为了防止符合条件的查询记录太多,提供分批下载
2、 提供内容的转义接口
3、 项目的其他模块可以重用
4、 其他开发人员不用了解pdf、excel的生成
5、 显示的内容可配置
6、 快速应用
7、 对后台传过来的数据能正确处理
SpringMVC提供给我们丰富的功能,其中支持了pdf、excel。但是如果项目很紧张的时候,需要开发pdf,excel报表的程序员都去学习poi,itext这些第三方软件包是很浪费资源的。下面提供一个方案,对springMVC进行一点包装,见下图:
说明:
1、AbstractReportSimpleController
是springmcv 的SimpleFormController抽象子类。根据请求控制是否要分批下载,否则直接下载pdf或者excel报表。
有两个属性:
view 是根据需求在运行时注入的pdf或者excel 视图
pageSize 是在配置中每次分批下载的大小值。
有三个模板方法:
abstract public int getPages(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
此方法用来测试符合条件的记录是否大于pageSize规定的每页大小。
abstract public Map getParameterMap(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
此方法用来传递请求的参数,当记录需要分批下载时候。第二次请求的参数要得到保证。
abstract public List getOutdtoList(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
此方法调用后台接口到符合条件的记录。
package
com.xyz.report;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.View;
import org.springframework.web.servlet.mvc.SimpleFormController;
public abstract class AbstractReportSimpleController extends
SimpleFormController ... {
public View view;
public int pageSize;
/** *//**
* 测试请求符合条件的记录有多少页
*
* @param request
* @param response
* @param command
* @param errors
* @return
* @throws Exception
*/
abstract public int getPages(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 子类实现这个方法,当请求返回的数据超过一页时候,要分页下载。 此方法用来设置请求的参数,保证第n次请求和第一次的参数一致
*
* @return map 中是参数的键值对,name-value.在jsp中for遍历
*/
abstract public Map getParameterMap(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 参数和onsubmit的一样,子类根据请求参数,返回后台查询的dto list
*
* @param request
* @param response
* @param command
* @param errors
* @return
* @throws Exception
*/
abstract public List getOutdtoList(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 当请求返回的数据超过一页的时候跳到分页页面, 否则将后台返回的dto list 传给View显示
*/
protected ModelAndView onSubmit(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception ...{
QryBaseInDTO in = (QryBaseInDTO) command;
in.setPageSize(this.pageSize);
// 如果是第一次请求,并且不是从分页页面来的第一次请求(这里还没有加上判断)
if (in.getPageNumber() == 1) ...{
if (this.getPages(request, response, command, errors) > 1) ...{
// 跳到分页页面
// 设置分页保存请求参数
request.setAttribute("REQ_PARAMETERS", this.getParameterMap(
request, response, command, errors));
return new ModelAndView("PAGINATION_VIEW");
}
}
//将数据传给View
Map model = new HashMap();
model.put("OUT_DTO_LIST", getOutdtoList(request, response, command,
errors));
return new ModelAndView(getSpecView(request), model);
}
protected View getSpecView(HttpServletRequest request) ...{
return getView();
}
public int getPageSize() ...{
return pageSize;
}
public void setPageSize(int pageSize) ...{
this.pageSize = pageSize;
}
public View getView() ...{
return view;
}
public void setView(View view) ...{
this.view = view;
}
}
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.View;
import org.springframework.web.servlet.mvc.SimpleFormController;
public abstract class AbstractReportSimpleController extends
SimpleFormController ... {
public View view;
public int pageSize;
/** *//**
* 测试请求符合条件的记录有多少页
*
* @param request
* @param response
* @param command
* @param errors
* @return
* @throws Exception
*/
abstract public int getPages(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 子类实现这个方法,当请求返回的数据超过一页时候,要分页下载。 此方法用来设置请求的参数,保证第n次请求和第一次的参数一致
*
* @return map 中是参数的键值对,name-value.在jsp中for遍历
*/
abstract public Map getParameterMap(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 参数和onsubmit的一样,子类根据请求参数,返回后台查询的dto list
*
* @param request
* @param response
* @param command
* @param errors
* @return
* @throws Exception
*/
abstract public List getOutdtoList(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception;
/** *//**
* 当请求返回的数据超过一页的时候跳到分页页面, 否则将后台返回的dto list 传给View显示
*/
protected ModelAndView onSubmit(HttpServletRequest request,
HttpServletResponse response, Object command, BindException errors)
throws Exception ...{
QryBaseInDTO in = (QryBaseInDTO) command;
in.setPageSize(this.pageSize);
// 如果是第一次请求,并且不是从分页页面来的第一次请求(这里还没有加上判断)
if (in.getPageNumber() == 1) ...{
if (this.getPages(request, response, command, errors) > 1) ...{
// 跳到分页页面
// 设置分页保存请求参数
request.setAttribute("REQ_PARAMETERS", this.getParameterMap(
request, response, command, errors));
return new ModelAndView("PAGINATION_VIEW");
}
}
//将数据传给View
Map model = new HashMap();
model.put("OUT_DTO_LIST", getOutdtoList(request, response, command,
errors));
return new ModelAndView(getSpecView(request), model);
}
protected View getSpecView(HttpServletRequest request) ...{
return getView();
}
public int getPageSize() ...{
return pageSize;
}
public void setPageSize(int pageSize) ...{
this.pageSize = pageSize;
}
public View getView() ...{
return view;
}
public void setView(View view) ...{
this.view = view;
}
}
2、视图类
视图类有两个分别是DefaultExcelView和DefaultPdfView,都实现了springMVC的View接口,也分别继承了springMVC的AbstractExcelView和AbstractPdfView。通过这两个类屏蔽了poi、itext的使用。
有属性:
cells:是AbstractReportCell类型,在配置中注入。
下面提供excel的实现参考:
package
com.xyz.report;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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 DefaultExcelView extends AbstractExcelView ... {
/** *//**
* 列
*/
protected AbstractReportCell[] cells;
public AbstractReportCell[] getCells() ...{
return cells;
}
public void setCells(AbstractReportCell[] cells) ...{
this.cells = cells;
}
protected void buildExcelDocument(Map model, HSSFWorkbook book,
HttpServletRequest request, HttpServletResponse response)
throws Exception ...{
HSSFSheet sheet = book.createSheet();
// 创建表头,并设置列的宽度
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < cells.length; i++) ...{
AbstractReportCell cell = cells[i];
row.createCell((short) i).setCellValue(cell.getCnName());
}
// 创建表内容
List dtos = (List) model.get("OUT_DTO_LIST");
for (int i = 1; i < dtos.size() + 1; i++) ...{
row = sheet.createRow(i);
Object dto = (Object) dtos.get(i);
// 将要显示的属性放到表格中
for (int j = 0; j < cells.length; j++) ...{
AbstractReportCell format = cells[i];
row.createCell((short) j).setCellValue(
format.formatSpecifyValue(dto));
}
}
}
}
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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 DefaultExcelView extends AbstractExcelView ... {
/** *//**
* 列
*/
protected AbstractReportCell[] cells;
public AbstractReportCell[] getCells() ...{
return cells;
}
public void setCells(AbstractReportCell[] cells) ...{
this.cells = cells;
}
protected void buildExcelDocument(Map model, HSSFWorkbook book,
HttpServletRequest request, HttpServletResponse response)
throws Exception ...{
HSSFSheet sheet = book.createSheet();
// 创建表头,并设置列的宽度
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < cells.length; i++) ...{
AbstractReportCell cell = cells[i];
row.createCell((short) i).setCellValue(cell.getCnName());
}
// 创建表内容
List dtos = (List) model.get("OUT_DTO_LIST");
for (int i = 1; i < dtos.size() + 1; i++) ...{
row = sheet.createRow(i);
Object dto = (Object) dtos.get(i);
// 将要显示的属性放到表格中
for (int j = 0; j < cells.length; j++) ...{
AbstractReportCell format = cells[i];
row.createCell((short) j).setCellValue(
format.formatSpecifyValue(dto));
}
}
}
}
3、AbstractReportCell
是报表单元格抽象类,负责设置单元格的宽度、名称、格式化单元格内容。有一个抽象方法,子类负责实现相关的格式化工作。这个类是重点,视图类对象通过此单元格抽象类对后台传过来的dto对象统一处理,因为报表内容是各不相同,视图类知道的只是要显示多少行多少列,至于要显示什么内容、怎么显示则委派给单元格类来处理。这个类原来只有一个方法format(Object propValue),后来经过实践改为三个方法。因为考虑代码重用和有些数据库表的字段可能有两种含义,比如帐号信息表中money可能根据flag字段的值表示“支出”和“收入”两个含义,即在显示的时候显示两列。这时候就要把整个dto作为参数传给单元格对象,才能判断。碰到这种情况需要重写formatSpecifyValue(Object dto)方法。单个含义的情况重写format(Object propValue)方法即可。
package
com.xyz.report;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
public abstract class AbstractReportCell ... {
/** *//**
* 属性英文名称
*/
private String propertyName;
/** *//**
* 列的宽度
*/
private int width;
/** *//**
* 属性的中文名称
*/
private String cnName;
/** *//**
* dto中某个属性的值
* @param propValue
* @return 返回转义后的字符串
*/
abstract protected String format(Object propValue);
/** *//**
* 根据属性名称从给定的dto 对象中取出对应的值
* @param outDto
* @param propName
* @return
*/
protected Object getSpecPropertyValue(Object outDto, String propName) ...{
BeanWrapper bw = new BeanWrapperImpl(outDto);
if (bw.isReadableProperty(propName)) ...{
return bw.getPropertyValue(propName);
} else ...{
return null;
}
}
/** *//**
* 格式化dto中的某个属性的值
* 如果特殊的要求,子类覆盖此方法
* @param dto
* @return
*/
public String formatSpecifyValue(Object dto) ...{
return format(getSpecPropertyValue(dto, getPropertyName()));
}
public String getCnName() ...{
return cnName;
}
public void setCnName(String cnName) ...{
this.cnName = cnName;
}
public String getPropertyName() ...{
return propertyName;
}
public void setPropertyName(String propertyName) ...{
this.propertyName = propertyName;
}
public int getWidth() ...{
return width;
}
public void setWidth(int width) ...{
this.width = width;
}
}
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
public abstract class AbstractReportCell ... {
/** *//**
* 属性英文名称
*/
private String propertyName;
/** *//**
* 列的宽度
*/
private int width;
/** *//**
* 属性的中文名称
*/
private String cnName;
/** *//**
* dto中某个属性的值
* @param propValue
* @return 返回转义后的字符串
*/
abstract protected String format(Object propValue);
/** *//**
* 根据属性名称从给定的dto 对象中取出对应的值
* @param outDto
* @param propName
* @return
*/
protected Object getSpecPropertyValue(Object outDto, String propName) ...{
BeanWrapper bw = new BeanWrapperImpl(outDto);
if (bw.isReadableProperty(propName)) ...{
return bw.getPropertyValue(propName);
} else ...{
return null;
}
}
/** *//**
* 格式化dto中的某个属性的值
* 如果特殊的要求,子类覆盖此方法
* @param dto
* @return
*/
public String formatSpecifyValue(Object dto) ...{
return format(getSpecPropertyValue(dto, getPropertyName()));
}
public String getCnName() ...{
return cnName;
}
public void setCnName(String cnName) ...{
this.cnName = cnName;
}
public String getPropertyName() ...{
return propertyName;
}
public void setPropertyName(String propertyName) ...{
this.propertyName = propertyName;
}
public int getWidth() ...{
return width;
}
public void setWidth(int width) ...{
this.width = width;
}
}
子类实现参考:
package
com.xyz.report;
import java.text.SimpleDateFormat;
public class DateFormatCell extends AbstractReportCell ... {
public String pattern = "yyyy-MM-dd";
protected String format(Object propValue) ...{
SimpleDateFormat format = new SimpleDateFormat(pattern);
return format.format(propValue);
}
public String getPattern() ...{
return pattern;
}
public void setPattern(String pattern) ...{
this.pattern = pattern;
}
}
import java.text.SimpleDateFormat;
public class DateFormatCell extends AbstractReportCell ... {
public String pattern = "yyyy-MM-dd";
protected String format(Object propValue) ...{
SimpleDateFormat format = new SimpleDateFormat(pattern);
return format.format(propValue);
}
public String getPattern() ...{
return pattern;
}
public void setPattern(String pattern) ...{
this.pattern = pattern;
}
}
配置文件参考:
<?
xml version="1.0" encoding="UTF-8"
?>
<! DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd" >
< beans >
< bean name ="/report/queryEmployee.do" class ="com.xyz.report.QueryEmployeeController" >
< property name ="view" >
< ref bean ="queryEmployeeExcelReport" />
</ property >
< property name ="pageSize" >
< value > 5000 </ value >
</ property >
</ bean >
< bean name ="queryEmployeeExcelReport" class ="com.xyz.report.DefaultExcelView" >
< property name ="cells" >
< ref bean ="employeeInfo" />
</ property >
</ bean >
<!-- 报表列名 -->
< bean id ="employeeInfo" class ="java.util.ArrayList" >
< constructor-arg >
< list >
< bean class ="com.xyz.report.NotFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 姓名 </ value >
</ property >
< property name ="propertyName" >
< value > name </ value >
</ property >
</ bean >
< bean class ="com.xyz.report.DateFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 出生年月 </ value >
</ property >
< property name ="propertyName" >
< value >birthday </ value >
</ property >
< property name ="pattern" >
< value > yyyy-MM-dd </ value >
</ property >
</ bean >
< bean class ="com.xyz.report.DeptFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 所在部门 </ value >
</ property >
< property name ="propertyName" >
< value > dept </ value >
</ property >
</ bean >
</ list >
</ constructor-arg >
</ bean >
</ beans >
<! DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd" >
< beans >
< bean name ="/report/queryEmployee.do" class ="com.xyz.report.QueryEmployeeController" >
< property name ="view" >
< ref bean ="queryEmployeeExcelReport" />
</ property >
< property name ="pageSize" >
< value > 5000 </ value >
</ property >
</ bean >
< bean name ="queryEmployeeExcelReport" class ="com.xyz.report.DefaultExcelView" >
< property name ="cells" >
< ref bean ="employeeInfo" />
</ property >
</ bean >
<!-- 报表列名 -->
< bean id ="employeeInfo" class ="java.util.ArrayList" >
< constructor-arg >
< list >
< bean class ="com.xyz.report.NotFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 姓名 </ value >
</ property >
< property name ="propertyName" >
< value > name </ value >
</ property >
</ bean >
< bean class ="com.xyz.report.DateFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 出生年月 </ value >
</ property >
< property name ="propertyName" >
< value >birthday </ value >
</ property >
< property name ="pattern" >
< value > yyyy-MM-dd </ value >
</ property >
</ bean >
< bean class ="com.xyz.report.DeptFormatCell" >
< property name ="width" >
< value > 25 </ value >
</ property >
< property name ="cnName" >
< value > 所在部门 </ value >
</ property >
< property name ="propertyName" >
< value > dept </ value >
</ property >
</ bean >
</ list >
</ constructor-arg >
</ bean >
</ beans >
注意:以上代码是没有通过运行的,只是凭记忆写出来的。
在项目中如果其他开发人员想做类似的报表,如果要求不高的话,只要继承AbstractReportSimpleController和根据需要实现AbstractReportCell子类,会配置xml就可以了。根本不需要了解poi、itext这些第三方软件包的使用。