[b]
需求描述：
1.导出数据量约30-50W
2.提供手动即时生成数据和自动生成数据(直接提供下载)
[/b]
页面
一个“手动导出”按钮，点击后台运行代码生成数据并通过outputstream返回给浏览器。
缺点在于生成数据非常耗时（查询数据库，过滤不合法的数据，汇率转换，查询关联表信息等），最耗时的地方：比如，查询到10W条数据，过滤掉2W条，之后会循环剩下的8W条数据，每次循环都去查询数据库获取某些信息，效率很低。【一次性将数据都查询出来放内存中？分几次查询数据，第一次查询一批，需要时从这批从获取，如果没有再查询一批数据出来，这样能够提高效率呢？】
一个“自动导出”按钮，点击将从数据库中查询出已经由定时任务生成好的数据，直接下载。
涉及到几个知识点：定时任务采用quartZ实现任务的促发。经过数据查询、过滤、计算等操作后，将数据写入Sheet，此时Workbook对象已经算是完成数据填充了。这是将Workbook对象写入到一个流中，再将此流转换为字节数组，再将字节数组转换为字符串，这时就需要注意了，操作字符串难免不涉及编码的问题。最开始没有对字符串进行编码，直接存入数据库，下载的时候直接从数据库中查询出来，转换为流，结果下载的数据使用excel无法打开。最后，通过设置字符串的编码解决了问题。存入数据库时转为UTF-8存入，读取后再使用UTF-8解码，再转为ISO8859-1编码，再转为流，向浏览器输出，这样才解决了excel的读取问题。【为什么不直接存对象呢？由于数据库中某张日志表有一个大文本字段足够存储数据，所以没有使用BLOB来存放大数据，也没有以对象的形式保存Workbook对象。其实在第一次下载后excel无法打开后，就考虑存储对象的方式完成数据存储，但是没成功，取出来的对象和本地对象的sid总是不一致，于是放弃了存对象的实现方式，项目时间紧，只好以最能想到的办法解决问题】
项目中遇到的另一个问题时，项目原来使用的是poi3.1-jar,版本较老了，而且不支持xlsx格式，而当前需求是数据量大，excel必须能容纳足够大的数据，所以引入了poi3.9版本。
这里想说的是，当项目中出现新老jar包的替换时，必须小心！引入poi3.9后发现，虽然能实现大数据的存储(50W)，约20M，但是原来的excel导出全部用的poi3.1实现的，由于3.1中某些方法在3.9中根本就没有了，只好回去修改原来的代码，项目中一共10多个导出，改得我痛苦。。。
而且，一旦确定要进行jar包的升级，选择一个既能支持当前需求，又能最大限度兼容老版本的版本是最合适的，不然，将陡然增加工作量！
以下是POI3.9导出的部分代码和quartz的配置
<bean name="quartzScheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="triggers">
<list>
<ref bean="cronTrigger_07"/>
</list>
</property>
<property name="configLocation" value="classpath:config/quartz.properties"/>
</bean>
<bean id="cronTrigger_07" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail" ref="jobDetail_07"/>
<!--每月1号0点开始每隔5分钟触发一次任务-->
<property name="cronExpression" value="0 0/5 0 1 * ?"/>
</bean>
<bean id="jobDetail_07" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
<property name="targetObject" ref="xxxService"/>
<property name="targetMethod" value="generateExcelDatas"/>
</bean>
<bean id="xxxService" class="xxx.xxx.serviceImpl"
autowire="byName" lazy-init="true">
</bean>
创建Workbook对象，要导出xlsx格式，需要使用XSSFWorkbook,而数据量大又不想让内存溢出的话，就用Workbook workbook = new SXSSFWorkbook(1000);指定一定大小的缓冲区，当容量慢时，将清除一些对象出去，始终维持一定的内存暂用空间，不会发生溢出。
//**创建工作薄，工作表*//*
Workbook workbook = new SXSSFWorkbook(1000);
Sheet sheet = createSheet(workbook,businessType);
public Sheet createSheet(Workbook workbook,String businessType) {
Sheet sheet = workbook.createSheet();
if("0".equals(businessType) || "1".equals(businessType)){
if("0".equals(businessType)) {
workbook.setSheetName(0, "XXX清单");
} else {
workbook.setSheetName(0, "YYY清单");
}
sheet.setColumnWidth((short) 0, (short) (35.7*50));//
sheet.setColumnWidth((short) 1, (short) (35.7*120));//
sheet.setColumnWidth((short) 2, (short) (35.7*120));//
sheet.setColumnWidth((short) 3, (short) (35.7*120));//
sheet.setColumnWidth((short) 4, (short) (35.7*160));//
sheet.setColumnWidth((short) 5, (short) (35.7*160));//
sheet.setColumnWidth((short) 6, (short) (35.7*180));//
sheet.setColumnWidth((short) 7, (short) (35.7*120));//
sheet.setColumnWidth((short) 8, (short) (35.7*180));//
sheet.setColumnWidth((short) 9, (short) (35.7*90));//
sheet.setColumnWidth((short) 10, (short) (35.7*100));//
sheet.setColumnWidth((short) 11, (short) (35.7*100));//
sheet.setColumnWidth((short) 12, (short) (35.7*100));//
}
else if("9".equals(businessType)){
workbook.setSheetName(0, "ZZZ清单");
sheet.setColumnWidth((short) 0, (short) (35.7*50));//
sheet.setColumnWidth((short) 1, (short) (35.7*280));//
sheet.setColumnWidth((short) 2, (short) (35.7*120));//
sheet.setColumnWidth((short) 3, (short) (35.7*120));//
sheet.setColumnWidth((short) 4, (short) (35.7*100));//
sheet.setColumnWidth((short) 5, (short) (35.7*180));//
sheet.setColumnWidth((short) 6, (short) (35.7*120));//
sheet.setColumnWidth((short) 7, (short) (35.7*180));//
sheet.setColumnWidth((short) 8, (short) (35.7*90));//
sheet.setColumnWidth((short) 9, (short) (35.7*100));//
sheet.setColumnWidth((short) 10, (short) (35.7*100));//
sheet.setColumnWidth((short) 11, (short) (35.7*100));//
}
return sheet;
}
[b]表格样式[/b]
Map<String,CellStyle> styleMap = getPaymentDatasStyles(workbook);
public Map<String, CellStyle> getPaymentDatasStyles(Workbook workbook) {
Map<String,CellStyle> styles = new HashMap<String,CellStyle>();
Font fontHead = workbook.createFont();
fontHead.setFontName("宋体"); // 字体
fontHead.setFontHeightInPoints((short)16); //字号
fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗
Font fontBody = workbook.createFont();
fontBody.setFontName("宋体"); // 字体
fontBody.setFontHeightInPoints((short)10); //字号
Font fontMark = workbook.createFont();
fontMark.setFontName("宋体"); // 字体
fontMark.setFontHeightInPoints((short)12); //字号
/**首行样式*/
CellStyle cellStyleHead = workbook.createCellStyle();
cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyleHead.setFont(fontHead);
/**第2行样式*/
CellStyle cellStyleUnit = workbook.createCellStyle();
cellStyleUnit.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleUnit.setBorderBottom(CellStyle.BORDER_MEDIUM);
cellStyleUnit.setFont(fontBody);
/**列标题样式*/
CellStyle cellStyleTitle = workbook.createCellStyle();
cellStyleTitle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleTitle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyleTitle.setBorderRight(CellStyle.BORDER_THIN);
cellStyleTitle.setBorderTop(CellStyle.BORDER_THIN);
cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyleTitle.setFont(fontBody);
cellStyleTitle.setWrapText(true);
/**正文样式*/
CellStyle cellStyleBody = workbook.createCellStyle();
cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
cellStyleBody.setAlignment(CellStyle.ALIGN_CENTER);
cellStyleBody.setFont(fontBody);
/**正文样式二*/
CellStyle cellStyleBodyDecimal = workbook.createCellStyle();
cellStyleBodyDecimal.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleBodyDecimal.setBorderLeft(CellStyle.BORDER_THIN);
cellStyleBodyDecimal.setBorderRight(CellStyle.BORDER_THIN);
cellStyleBodyDecimal.setBorderTop(CellStyle.BORDER_THIN);
cellStyleBodyDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
cellStyleBodyDecimal.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cellStyleBodyDecimal.setFont(fontBody);
/**正文样式三*/
CellStyle cellStyleBodyInt = workbook.createCellStyle();
cellStyleBodyInt.setBorderBottom(CellStyle.BORDER_THIN);
cellStyleBodyInt.setBorderLeft(CellStyle.BORDER_THIN);
cellStyleBodyInt.setBorderRight(CellStyle.BORDER_THIN);
cellStyleBodyInt.setBorderTop(CellStyle.BORDER_THIN);
cellStyleBodyInt.setAlignment(CellStyle.ALIGN_RIGHT);
cellStyleBodyInt.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
cellStyleBodyInt.setFont(fontBody);
/**备注*/
CellStyle cellStyleMark = workbook.createCellStyle();
cellStyleMark.setFont(fontMark);
styles.put("HEAD", cellStyleHead);
styles.put("UNIT", cellStyleUnit);
styles.put("TITLE", cellStyleTitle);
styles.put("BODY", cellStyleBody);
styles.put("BODY_DECIMAL", cellStyleBodyDecimal);
styles.put("BODY_INT", cellStyleBodyInt);
styles.put("MARK", cellStyleMark);
return styles;
}
[b]将数据往sheet中写[/b]
putDatas2Xlsx(sheet,styleMap,list,type,locations,allExchRate);
public void putDatas2Xlsx(Sheet sheet, Map<String, CellStyle> styleMap,List<ScmsDocFeeDetailVo> detailList,String businessType,
Map<String,String> locations,Map<String,BigDecimal> allExchRate) throws Exception {
final String agentCodeForGD = "aaa";
//**********************列标题***************************//*
String headName = "";
String [] headList = null;
headName = "XXX清单（03）";
if("0".equals(businessType)){
headList = new String[]{};
}else
if("1".equals(businessType)){
headName = "XXX清单（02）";
headList = new String[]{};
} else
if("9".equals(businessType)){
headName = "XXX清单（01）";
headList = new String[]{};
}
ScmsDocFeeDetailVo vo = null;
String risk = "";
String handler1CodeName = "";
Row row = null;
Cell cell = null;
CellStyle cellStyleHead = styleMap.get("HEAD");
CellStyle cellStyleUnit = styleMap.get("UNIT");
CellStyle cellStyleTitle = styleMap.get("TITLE");
CellStyle cellStyleBody = styleMap.get("BODY");
CellStyle cellStyleBodyDecimal = styleMap.get("BODY_DECIMAL");
CellStyle cellStyleBodyInt = styleMap.get("BODY_INT");
CellStyle cellStyleMark = styleMap.get("MARK");
//创建第一行 标题
int rowNum = 0;
row = sheet.createRow(rowNum++);//创建行
cell = row.createCell(0);
cell.setCellValue(headName);
cell.setCellStyle(cellStyleHead);
//合并单元格
if("0".equals(businessType) || "1".equals(businessType)) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
} else {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
}
//创建第二行 单位
row = sheet.createRow(rowNum++);
row.setHeight((short)400);
for(int i=0;i<headList.length;i++){
cell = row.createCell(i);
cell.setCellStyle(cellStyleUnit);
if(("0".equals(businessType) && i==10) || ("1".equals(businessType) && i==10) ||("9".equals(businessType) && i==9)){
cell.setCellValue("单位：元");
}
}
if("0".equals(businessType)) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
} else if("1".equals(businessType)) {
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));
}
//第三行 列标题
row = sheet.createRow(rowNum++);
for(int i=0;i<headList.length;i++){
cell = row.createCell(i);
cell.setCellStyle(cellStyleTitle);
cell.setCellValue(headList[i]);
}
row.setHeightInPoints((short)33);//行高
//** 打印主体信息列表------------------------------------------*//*
int detailCount = detailList.size();
for(int i = 0;i<detailCount;i++){
vo = detailList.get(i);
if("0".equals(businessType) || "1".equals(businessType)) {
handler1CodeName =codeService.translateCode("UserCode", vo.getHandler1Code(), "", "");
vo.setInsuredName(handler1CodeName);
vo.setAgentCode(agentCodeForGD);
}
/**汇率转换*/
if(vo.getCurrency()!=null && !"".equals(vo.getCurrency().trim())) {
BigDecimal exchRate = allExchRate.get(vo.getCurrency().trim());
vo.setCostFee(vo.getCostFee().multiply(exchRate).setScale(2, RoundingMode.HALF_UP));
}
if(vo.getPaidPremium()==null || vo.getPaidPremium().equals("") || vo.getPaidPremium().compareTo(BigDecimal.ZERO)==0) {
vo.setCostRate(BigDecimal.ZERO);
} else {
vo.setCostRate(vo.getCostFee().divide(vo.getPaidPremium(), 2, RoundingMode.HALF_UP));
}
//开始循环生成EXCEL行数据
short colNum = 0;//初始化CELL下标
row = sheet.createRow(rowNum++);//创建行
row.setHeight((short)400);
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyInt);
cell.setCellValue((i+1));
//清单
if("0".equals(businessType) || "1".equals(businessType)){
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
String preComCode = vo.getComCode().substring(0, 4);
String addressName = locations.get(preComCode);
if(addressName!=null) {
cell.setCellValue(addressName);
} else {
cell.setCellValue(preComCode);
}
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getComCode());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
handler1CodeName=codeService.translateCode("UserCode", vo.getHandler1Code(), "PUB", "");
cell.setCellValue(handler1CodeName);
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(agentCodeForGD);
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getSellerNo());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getCertiNo());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
if(vo.getStartDate()!=null && !vo.getStartDate().equals("")) {
cell.setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(vo.getStartDate()));
} else {
cell.setCellValue("");
}
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
risk=codeService.translateCode("RiskCode", vo.getRiskCode(), "", "");
cell.setCellValue(risk);
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getRiskCode());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getPaidPremium().doubleValue());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getCostFee().doubleValue());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getCostRate().doubleValue());
}else
//手续费清单
if("9".equals(businessType)){
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getAgentName());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getAgentCode());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
String preComCode = vo.getComCode().substring(0, 4);
String addressName = locations.get(preComCode);
if(addressName!=null) {
cell.setCellValue(addressName);
} else {
cell.setCellValue(preComCode);
}
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getComCode());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getCertiNo());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
if(vo.getStartDate()!=null) {
cell.setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(vo.getStartDate()));
} else{
cell.setCellValue("");
}
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
//调用服务转换代码为姓名【在循环中每次调用服务进行查询，导致性能下降很多，这里必须进行优化处理。可以一次性查询所有需要转换的数据到内存中！！！】
risk=codeService.translateCode("RiskCode", vo.getRiskCode(), "", "");
cell.setCellValue(risk);
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyleBody);
cell.setCellValue(vo.getRiskCode());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getPaidPremium().doubleValue());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getCostFee().doubleValue());
cell = row.createCell(colNum++);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(cellStyleBodyDecimal);
cell.setCellValue(vo.getCostRate().doubleValue());
}
}
//备注
row = sheet.createRow(rowNum++);//创建行
row.setHeight((short)400);
cell = row.createCell(0);
cell.setCellStyle(cellStyleMark);
cell.setCellValue("备注："); //序号
}
[b]保存数据到数据库[/b]
OutputStream os = null;
ByteArrayOutputStream baos = null;
try{
os = new ByteArrayOutputStream();
workbook.write(os);
baos = (ByteArrayOutputStream) os;
//编码转换-非常必须一定要这样做！
String encodeStr = baos.toString("ISO-8859-1");
encodeStr = java.net.URLEncoder.encode(encodeStr, "UTF-8");
//现在可以直接存储encodeStr 字符串到数据库中了！
//.......
[b]从数据库中取数据并返回给浏览器下载[/b]
public void exportXlsxFromDB()throws Exception {
/**基于页面输入信息查询数据*/
String comCode = ServletActionContext.getRequest().getParameter("comCode");
String paymentYear = ServletActionContext.getRequest().getParameter("paymentYear");
String paymentMonth = ServletActionContext.getRequest().getParameter("paymentMonth");
String paymentDate = paymentYear+"-"+paymentMonth;
String riskType = ServletActionContext.getRequest().getParameter("risk");
String businessType = ServletActionContext.getRequest().getParameter("businessType");
long start = System.currentTimeMillis();
logger.info("开始导出机构"+comCode+"及其下级"+paymentDate+"月的实收数据，导出险类:"+riskType+",业务类型："+businessType);
//查询数据
Table table = tableService.getExcelDataFromDB(paymentDate, comCode, riskType, businessType);
if(table==null) {
//数据库无数据
throw new BusinessException(paymentDate+"数据尚未定时生成，请手动生成本次数据。【提示：生成数据时，如果数据量较大，将比较耗时，请耐心等待...】");
}
String sheetName = interfaceLog.getResponsetype();
String excelDatas = interfaceLog.getResponsexml();
//还原数据---非常关键&&重要加必须
excelDatas = java.net.URLDecoder.decode(excelDatas, "UTF-8");
ByteArrayOutputStream baos = new ByteArrayOutputStream();
baos.write(excelDatas.getBytes("ISO-8859-1"));
//设置返回内容类型
this.getResponse().reset();
this.getResponse().setContentType("application/x-msdownload");
this.getResponse().setHeader("Content-Disposition","attachment; filename="+new String(sheetName.getBytes("gb2312"),"ISO-8859-1")+paymentDate+".xlsx");
OutputStream outStream = null;
try{
outStream = this.getResponse().getOutputStream();
baos.writeTo(outStream);
outStream.flush();
}catch(Exception e){
log.debug(e.getMessage());
e.printStackTrace();
throw new ScmsBusinessException("导出异常，请联系管理员！",e);
}finally{
if(outStream!=null) {
outStream.close();
}
if(baos!=null) {
baos.close();
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
