最近在研究报表导出的方式,在网络上找了很多方法,因我要导出的报表是比较复杂一点的,涉及到复杂的表头,合并单元格,
最后找到了这篇文章里所写的方法,所以就总结了一下,希望对大家有帮助。
1、 controller层方法:这个为在controller层调用的方法。
/**
* 下载生成数据报表
*/
@RequestMapping(value = "/download")
public void download(HttpServletRequest request, HttpServletResponse response) throws Exception {
String yesterday = DateUtils.date2String(DateUtils.getLastDay(new Date()), DateUtils.DEFAULT_DATE_PATTERN);
//明细表
List<Map<String, Object>> cardList = this.orderReportDao.getOrderList(yesterday);
cardList = TsmBeanFactory.getInstance().getCardList(cardList);
List<Map<String, Object>> countList = TsmBeanFactory.getInstance().getCountList(orderReportDao);
try {
String sheetName1 = "交易信息汇总";
String sheetName2 = "交易信息明细";
String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
String yesterdayDate = DateUtils.date2String(DateUtils.getLastDay(new Date()), DateUtils.SHORT_DATE_FORMAT);
date = "【统计日期】:"+yesterdayDate+" 00:00:00"+"--"+yesterdayDate+" 23:59:00"+
" "+"【报表导出日期】:"+date;
String[] head0 = new String[] { "APP", "支付方式", "发卡合计", "发卡合计","发卡合计",
"充值合计", "充值合计","退卡合计", "退卡合计","退资合计", "退资合计" }; //在excel中的第3行每列的参数
String[] headnum0 = new String[] { "2,3,0,0", "2,3,1,1", "2,2,2,4","2,2,5,6", "2,2,7,8", "2,2,9,10"}; //对应excel中的行和列,下表从0开始{"开始行,结束行,开始列,结束列"}
String[] head1 = new String[] { "笔数","开卡费","预充值金额(单位:元)","笔数","金额(单位:元)","笔数","金额(单位:元)","笔数","金额(单位:元)"}; //在excel中的第4行每列(合并列)的参数
String[] headnum1 = new String[] { "3,3,2,2", "3,3,3,3", "3,3,4,4","3,3,5,5","3,3,6,6","3,3,7,7","3,3,8,8","3,3,9,9","3,3,10,10"};
String[] head2 = new String[] { "订单号", "总支付金额(单位:元)", "业务状态", "设备型号",
"实际支付金额(单位:元)", "手机号","开卡费(单位:元)", "订单交易日期","支付状态","卡号","实际开卡支付费用(单位:元)","业务发起来源","业务类型"}; //在excel中的第3行每列的参数
String[] headnum2 = new String[] { "2,3,0,0", "2,3,1,1", "2,3,2,2", "2,3,3,3",
"2,3,4,4","2,3,5,5","2,3,6,6","2,3,7,7","2,3,8,8","2,3,9,9","2,3,10,10","2,3,11,11","2,3,12,12"}; //对应excel中的行和列,下表从0开始{"开始行,结束行,开始列,结束列"}
//调用生成报表Util
ExportExcelUtil.reportMergeXls(request, response, cardList,countList,sheetName1,sheetName2, head0,
headnum0, head1, headnum1, date,head2,headnum2); //utils类需要用到的参数
} catch (Exception e) {
e.printStackTrace();
}
return;
}
2、 utils类: 这个为导出报表的util类,里面是生成复杂表头和如何合并单元格的方法。
package util;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.Map.Entry;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSF