最近工作需求使用POI,使用场景如下:
1、查询数据库获取数据
2、按照客户要求格式导出成excel
碰到的几个问题:
1、POI的基本操作
2、客户的有格式要求,还比较复杂
问题一参见:
POI操作Excel文档-基础篇 http://llyzq.iteye.com/admin/blogs/1354542
POI操作Excel文档-中级篇 http://llyzq.iteye.com/admin/blogs/1354545
Apache POI http://llyzq.iteye.com/admin/blogs/729521
问题二的解决思路:
1、将客户提供的excel作为样式模板
2、读取该excel创建HSSFWorkbook对象
3、填充数据
4、对于需要纵向扩充的表格数据,使用sheet.shiftRows方法实现,同时复杂样式和合并单元格
/**
* 复制一行的单元格样式
*/
private void copyCellStyleOfRow(HSSFSheet sheet,HSSFRow fromRow,HSSFRow toRow,int count){
for (int i = 0; i < count; i++) {
if(toRow.getCell((short)i) != null)
toRow.getCell((short)i).setCellStyle(fromRow.getCell((short)i).getCellStyle());
else{
toRow.createCell((short)i).setCellStyle(fromRow.getCell((short)i).getCellStyle());
}
for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
Region r = sheet.getMergedRegionAt(j);
//判断是否包含在合并表格中
if (r.contains(fromRow.getRowNum(), (short)i)) {
sheet.addMergedRegion(new Region(toRow.getRowNum(), r.getColumnFrom(), toRow.getRowNum(), r.getColumnTo()));
}
}
}
}
其他主要代码:
protected ActionForward doPrev(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
ITenpayQuetyService tenpayQueryServiceImpl=(ITenpayQuetyService)ApplicationContextUtil.getInstance().getBean("tenpayQueryServiceImpl");
try {
String begDate = request.getParameter("begDate").replaceAll("-", "");
String endDate = request.getParameter("endDate").replaceAll("-", "");
Map params = new HashMap();
params.put("begDate", begDate);
params.put("endDate", endDate);
params.put("broke", "TENPAY");
//读取excel模板
String urlpath = request.getRealPath("");
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(urlpath + "\\template\\" + HsDictionary.getInstance().getSysParameter("SALE", "SYSTEMPLATE")+"\\custom\\tenpaydemo.xls"));
HSSFSheet s = wb.getSheetAt(0);
DecimalFormat format = new DecimalFormat("###,##0.00");
//--------------汇总报表
//统计周期
String tjzq = request.getParameter("begDate").replaceAll("-", ".")+" -- "
+request.getParameter("endDate").replaceAll("-", ".");
int hs = 0;//户数
int cggmkhs = 0;//成功购买客户数
double zb = 100;//占比
int csgmkhs = 0;//尝试购买客户数
int cssgbs = 0;//尝试申购笔数
int cgsgbs = 0;//成功申购笔数
double cgsgje = 0;//成功申购金额
int csrgbs = 0;//尝试认购笔数
int cgrgbs = 0;//成功认购笔数
double cgrgje = 0;//成功认购金额
int dtcskkbs = 0;//定投尝试扣款笔数
int dtcgkkbs = 0;//定投成功扣款笔数
double dtcgkkje = 0;//定投成功扣款金额
HsRowSet rowSet = tenpayQueryServiceImpl.getTenpayNewOpenSuccessTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
cggmkhs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
rowSet = tenpayQueryServiceImpl.getTenpaySuccessOpenStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
hs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
rowSet = tenpayQueryServiceImpl.getTenpayNewOpenTryTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
csgmkhs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
if(hs != 0)
zb = cggmkhs/hs;
//申购
params.put("businflag", "022");
rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
cssgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0){
cgsgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
cgsgje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue();
}
//认购
params.put("businflag", "020");
rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
csrgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0){
cgrgbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
cgrgje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue();
}
//定投
params.put("businflag", "039");
rowSet = tenpayQueryServiceImpl.getTenpayTryTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0)
dtcskkbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
rowSet = tenpayQueryServiceImpl.getTenpaySuccessTradeStatic(this.getPkgId(request), params);
if(rowSet.getReturnRecords() > 0){
dtcgkkbs = Integer.valueOf(((String)rowSet.getInterfaceProperty(0,"count"))).intValue();
dtcgkkje = Double.valueOf(((String)rowSet.getInterfaceProperty(0,"confirmbala"))).doubleValue();
}
//填充excel
HSSFRow row = s.getRow(6);
row.getCell((short)0).setCellValue(new HSSFRichTextString(tjzq));
row.getCell((short)1).setCellValue(new HSSFRichTextString(String.valueOf(hs)));
row.getCell((short)2).setCellValue(new HSSFRichTextString(String.valueOf(cggmkhs)));
row.getCell((short)3).setCellValue(new HSSFRichTextString(String.valueOf(zb)+"%"));
row.getCell((short)4).setCellValue(new HSSFRichTextString(String.valueOf(csgmkhs)));
row.getCell((short)5).setCellValue(new HSSFRichTextString(String.valueOf(cssgbs)));
row.getCell((short)6).setCellValue(new HSSFRichTextString(String.valueOf(cgsgbs)));
row.getCell((short)7).setCellValue(new HSSFRichTextString(format.format(cgsgje)));
row.getCell((short)8).setCellValue(new HSSFRichTextString(String.valueOf(csrgbs)));
row.getCell((short)9).setCellValue(new HSSFRichTextString(String.valueOf(cgrgbs)));
row.getCell((short)10).setCellValue(new HSSFRichTextString(format.format(cgrgje)));
row.getCell((short)11).setCellValue(new HSSFRichTextString(String.valueOf(dtcskkbs)));
row.getCell((short)12).setCellValue(new HSSFRichTextString(String.valueOf(dtcgkkbs)));
row.getCell((short)13).setCellValue(new HSSFRichTextString(format.format(dtcgkkje)));
//------------新开户报表---------------
HsRowSet newOpenList = tenpayQueryServiceImpl.getTenpayNewOpenList(this.getPkgId(request),params);
request.setAttribute("begDate",begDate);
request.setAttribute("endDate",endDate);
request.setAttribute("newOpenList",newOpenList);
//填充excel
row = s.getRow(10);
for (int i = 0; i < newOpenList.getReturnRecords(); i++) {
row.getCell((short)0).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"date")));
row.getCell((short)1).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"partneraccoid")));
row.getCell((short)5).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"time")));
row.getCell((short)6).setCellValue(new HSSFRichTextString(newOpenList.getInterfaceProperty(i,"type")));
if(i != newOpenList.getReturnRecords()-1){
s.shiftRows(row.getRowNum()+1, row.getRowNum()+8, 1);
this.copyCellStyleOfRow(s,row, s.getRow(row.getRowNum()+1), 7);
row=s.getRow(row.getRowNum()+1);
}
}
row=s.getRow(row.getRowNum()+1);
row.getCell((short)0).setCellValue(new HSSFRichTextString("共"+newOpenList.getReturnRecords()+"条记录"));
//---------------交易详细报表------------------------
HsRowSet dealDetailList = tenpayQueryServiceImpl.getTenpayDealDetailList(this.getPkgId(request),params);
//填充excel
row=s.getRow(row.getRowNum()+4);
for (int i = 0; i < dealDetailList.getReturnRecords(); i++) {
row.getCell((short)0).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"partneraccoid")));
row.getCell((short)1).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"date")));
row.getCell((short)3).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"time")));
row.getCell((short)5).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"fundcode")));
row.getCell((short)7).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"fundtype")));
row.getCell((short)8).setCellValue(new HSSFRichTextString(HsDictionary.getInstance().getCaption("业务名称", dealDetailList.getInterfaceProperty(i,"businflag"))));
row.getCell((short)9).setCellValue(new HSSFRichTextString(format.format(Double.valueOf(dealDetailList.getInterfaceProperty(i,"confirmbala")))));
row.getCell((short)10).setCellValue(new HSSFRichTextString(dealDetailList.getInterfaceProperty(i,"status")));
if(i != dealDetailList.getReturnRecords() -1){
s.shiftRows(row.getRowNum()+1, row.getRowNum()+2, 1);
this.copyCellStyleOfRow(s,row, s.getRow(row.getRowNum()+1), 11);
row=s.getRow(row.getRowNum()+1);
}
}
row=s.getRow(row.getRowNum()+1);
row.getCell((short)0).setCellValue(new HSSFRichTextString("共"+dealDetailList.getReturnRecords()+"条记录"));
//-----------生成报表excel文件-------------
ByteArrayOutputStream fos = new ByteArrayOutputStream();
wb.write(fos);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "inline; filename=income_\"" + begDate + "-" + endDate +".xls \"");
//确保IE识别本次为下载文件,解决https IE下载无法保存的问题
response.setHeader("Content-Transfer-Encoding","binary");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
ByteArrayInputStream inputStream = new ByteArrayInputStream(fos.toByteArray());
PrintWriter out = response.getWriter();
int i;
while ( (i = inputStream.read()) != -1) {
out.write(i);
}
out.flush();
inputStream.close();
out.close();
fos.close();
} catch (Exception ex) {
SysLogUtils.error("下载对账数据发生错误:", ex);
throw new BusinessException("ETS-1BT27","下载对账数据发生错误!");
}
return null;
}