Java导出Excel使用POI之SXSSFWorkbook方式
这里用来自我总结和参考，所以东西看起来可能会有些乱。
<a class="easyui-linkbutton" id='export' iconCls="icon-export" plain="false" onclick="mesExceptionBill.exportServerData(1);" href="javascript:void(0);">导出Excel(带回复)</a> <a class="easyui-linkbutton" id='exportNoReply' iconCls="icon-export" plain="false" onclick="mesExceptionBill.exportServerData(2);" href="javascript:void(0);">导出Excel</a>
//Excel服务器端导出 MesExceptionBill.prototype.exportServerData = function(exportType) { $('#exportType').val(exportType); var param = JSON.stringify(serializeObject($('#mesExceptionBill').form())); var url = "platform/mms/mes/mesexceptionbillz/mesExceptionBillController/operation/sub/" + "exportServer?param=" + param;// 服务器请求地址 var a = document.getElementById("export"); a.setAttribute("href", url); a.setAttribute("target", "_blank"); var b = document.getElementById("exportNoReply"); b.setAttribute("href", url); b.setAttribute("target", "_blank"); };
import org.apache.poi.xssf.streaming.SXSSFWorkbook; /** * Excel服务端导出 * * @param pageParameter * @param request * @param map * @return * @throws Exception */ @RequestMapping(value = "/operation/sub/exportServer") public void exportNoHeadExcel(PageParameter pageParameter, HttpServletRequest request, HttpServletResponse response) { String fileName = ""; ByteArrayOutputStream out = new ByteArrayOutputStream(); try { // 先获取所有前台传递的参数 String json = ServletRequestUtils.getStringParameter(request, "param", ""); String sfnConditions = ServletRequestUtils.getStringParameter(request, "sdfConditons", "");// 自定义查询条件 // 再依次梳理所有参数 QueryReqBean<MesExceptionBillzDTO> queryReqBean = new QueryReqBean<MesExceptionBillzDTO>(); queryReqBean.setPageParameter(pageParameter); MesExceptionBillzDTO param = null; if (!StringUtils.isEmpty(json)) { param = JsonHelper.getInstance().readValue(json, new TypeReference<MesExceptionBillzDTO>() { }); queryReqBean.setSearchParams(param); } else { param = new MesExceptionBillzDTO(); queryReqBean.setSearchParams(param); } // 声明当前方法的返回值 QueryRespBean<MesExceptionBillzDTO> result = null; String exportType = param.getExportType(); try { if (!StringUtils.isEmpty(exportType) && "1".equals(exportType)) { result = mesExceptionBillzService.searchMesExceptionBillReplyInfo(queryReqBean, sfnConditions); } else { result = mesExceptionBillzService.searchMesExceptionBillInfo(queryReqBean, sfnConditions); } } catch (Exception ex) { return; } // 质量问题信息报送 List<MesExceptionBillzDTO> showList = result.getResult(); SXSSFWorkbook workbook = new SXSSFWorkbook(); String[] headersStr = null; if (!StringUtils.isEmpty(exportType) && "1".equals(exportType)) { mesExceptionBillzService.exportData(workbook, 0, "exportExcel", headersStr, showList); } else { mesExceptionBillzService.exportDataNoReply(workbook, 0, "exportExcel", headersStr, showList); } // 导出数据 workbook.write(out); // 弹出保存框方式 fileName = "异常问题导出"; // 解决IE文件名乱码 if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { fileName = URLEncoder.encode((fileName + ".xlsx"), "UTF-8"); } else { fileName = new String((fileName + ".xlsx").getBytes(), "ISO8859-1"); } byte[] content = out.toByteArray(); InputStream ins = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); ServletOutputStream outputStream = response.getOutputStream(); BufferedInputStream bins = new BufferedInputStream(ins); BufferedOutputStream bouts = new BufferedOutputStream(outputStream); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bins.read(buff, 0, buff.length))) { bouts.write(buff, 0, bytesRead); } // 关闭流 bins.close(); bouts.close(); } catch (Exception ex) { ex.printStackTrace(); LOGGER.debug(ex.getMessage()); } }
package avicit.mms.mes.mesexceptionbillz.service; import java.io.Serializable; import java.text.SimpleDateFormat; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import avicit.mms.mes.mesexceptionbillz.dao.MesExceptionBillzDao; import avicit.mms.mes.mesexceptionbillz.dto.MesExceptionBillzDTO; import avicit.platform6.api.sysuser.SysUserAPI; import avicit.platform6.core.exception.DaoException; import avicit.platform6.core.mybatis.pagehelper.Page; import avicit.platform6.core.mybatis.pagehelper.PageHelper; import avicit.platform6.core.rest.msg.QueryReqBean; import avicit.platform6.core.rest.msg.QueryRespBean; import avicit.platform6.core.sfn.intercept.SelfDefinedQuery; @Service public class MesExceptionBillzService implements Serializable { private static final Logger LOGGER = LoggerFactory.getLogger(MesExceptionBillzService.class); private static final long serialVersionUID = 1L; @Autowired private MesExceptionBillzDao mesExceptionBillDao; @Autowired private SysUserAPI sysUserAPI; /** * 异常问题 导出,带回复 * @param workbook * @param sheetNum * @param sheetTitle * @param headers * @param result * @param resultSend * @throws Exception */ public void exportData(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List<MesExceptionBillzDTO> resultSend) throws Exception { // 生成一个表格 Sheet sheet = workbook.createSheet(); /* *********** ***********第一行标题start*************************** */ // 产生表格标题行 单元格范围 参数（int firstRow, int lastRow, int firstCol, intlastCol) //这里设置标题单元格的合并范围，0,0,4,6代表第0行的4-6列合并为一列，0,1,0,0代表第0列的第0行到第1行合并为一行 CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6); CellRangeAddress region2 = new CellRangeAddress(0, 1, 7, 7); CellRangeAddress region3 = new CellRangeAddress(0, 1, 8, 8); CellRangeAddress region4 = new CellRangeAddress(0, 1, 9, 9); CellRangeAddress region5 = new CellRangeAddress(0, 1, 10, 10); CellRangeAddress region6 = new CellRangeAddress(0, 1, 11, 11); CellRangeAddress region7 = new CellRangeAddress(0, 1, 12, 12); CellRangeAddress region8 = new CellRangeAddress(0, 1, 0, 0); CellRangeAddress region9 = new CellRangeAddress(0, 1, 1, 1); CellRangeAddress region10 = new CellRangeAddress(0, 1, 2, 2); CellRangeAddress region11 = new CellRangeAddress(0, 1, 3, 3); // 在sheet里将表头单元格合并 sheet.addMergedRegion(region1); sheet.addMergedRegion(region2); sheet.addMergedRegion(region3); sheet.addMergedRegion(region4); sheet.addMergedRegion(region5); sheet.addMergedRegion(region6); sheet.addMergedRegion(region7); sheet.addMergedRegion(region8); sheet.addMergedRegion(region9); sheet.addMergedRegion(region10); sheet.addMergedRegion(region11); // 设置sheet名字 workbook.setSheetName(sheetNum, "异常问题导出"); /* **********************第二行标题start*************************** */ // 表格第二行标题 Row headTitleSecond = sheet.createRow(0); // 设置样式 CellStyle secondRowcellStyle = workbook.createCellStyle(); secondRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色 // secondRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 secondRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); secondRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); secondRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); secondRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); secondRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); secondRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); Font font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeight((short) 220); font.setBoldweight((short) 700); secondRowcellStyle.setFont(font); Cell cel = headTitleSecond.createCell(0); cel.setCellStyle(secondRowcellStyle); cel.setCellValue("异常类型"); // 设置样式 Cell cel1 = headTitleSecond.createCell(1); cel1.setCellStyle(secondRowcellStyle); cel1.setCellValue("产品项目"); Cell ce2 = headTitleSecond.createCell(2); ce2.setCellStyle(secondRowcellStyle); ce2.setCellValue("异常标题"); Cell cel3 = headTitleSecond.createCell(3); cel3.setCellStyle(secondRowcellStyle); cel3.setCellValue("异常内容"); Cell cel4 = headTitleSecond.createCell(4); cel4.setCellStyle(secondRowcellStyle); cel4.setCellValue("异常回复"); Cell cel5 = headTitleSecond.createCell(7); cel5.setCellStyle(secondRowcellStyle); cel5.setCellValue("是否归零"); Cell cel6 = headTitleSecond.createCell(8); cel6.setCellStyle(secondRowcellStyle); cel6.setCellValue("发布人"); Cell cel7 = headTitleSecond.createCell(9); cel7.setCellStyle(secondRowcellStyle); cel7.setCellValue("发布时间"); Cell cel8 = headTitleSecond.createCell(10); cel8.setCellStyle(secondRowcellStyle); cel8.setCellValue("负责人"); Cell cel9 = headTitleSecond.createCell(11); cel9.setCellStyle(secondRowcellStyle); cel9.setCellValue("设计人员"); Cell cel10 = headTitleSecond.createCell(12); cel10.setCellStyle(secondRowcellStyle); cel10.setCellValue("是否有附件"); // 表格第二行标题 Row headTitle = sheet.createRow(1); Cell cel116 = headTitle.createCell(4); cel116.setCellStyle(secondRowcellStyle); cel116.setCellValue("回复内容"); Cell cel117 = headTitle.createCell(5); cel117.setCellStyle(secondRowcellStyle); cel117.setCellValue("回复人"); Cell cel118 = headTitle.createCell(6); cel118.setCellStyle(secondRowcellStyle); cel118.setCellValue("回复时间"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Row row = null; String curid = ""; String lastid = ""; // 遍历集合数据，产生数据行 if (resultSend != null) { int index = 2; int begin = 2; int end = 2; for (int i = 0; i < resultSend.size(); i++) { row = sheet.createRow(index); MesExceptionBillzDTO dto = resultSend.get(i); curid = dto.getId(); if (i != 0) { lastid = resultSend.get(i - 1).getId(); } else { lastid = curid; } row.setHeightInPoints(22.0f); row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : ""); row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : ""); row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : ""); row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : ""); row.createCell(4).setCellValue((dto.getExReplyInfo() != null) ? dto.getExReplyInfo() : ""); row.createCell(5).setCellValue((dto.getReplierName() != null) ? dto.getReplierName() : ""); row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? sdf.format(dto.getLastUpdateDate()) : ""); row.createCell(7).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : ""); row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : "")); row.createCell(9).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : "")); row.createCell(10).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : ""); row.createCell(11).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : "")); row.createCell(12).setCellValue(dto.getCountAttachment()>0?"是":"否"); if (lastid.equals(curid)) { if (i != 0) { end += 1; } } else { for (int a = 0; a < 13; a++) { if (a < 4 || a > 6) { // 合并 CellRangeAddress callRange = new CellRangeAddress(begin, end, a, a); sheet.addMergedRegion(callRange); } } end += 1; begin = end; } if (i % 100 == 0) { ((SXSSFSheet) sheet).flushRows(); } index++; } for (int i = 0; i < 13; i++) { if (i < 4 || i > 6) { // 合并 CellRangeAddress callRange1 = new CellRangeAddress(begin, end, i, i); sheet.addMergedRegion(callRange1); } } } } /** * 异常问题 导出 * @param workbook * @param sheetNum * @param sheetTitle * @param headers * @param result * @param resultSend * @throws Exception */ public void exportDataNoReply(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers, List<MesExceptionBillzDTO> resultSend) throws Exception { // 生成一个表格 Sheet sheet = workbook.createSheet(); // 设置sheet名字 workbook.setSheetName(sheetNum, "异常问题导出"); /* **********************第二行标题start*************************** */ // 表格第二行标题 Row headTitleSecond = sheet.createRow(0); // 设置样式 CellStyle firstRowcellStyle = workbook.createCellStyle(); firstRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色 firstRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 firstRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); firstRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); firstRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); firstRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); firstRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); firstRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); Font font = workbook.createFont(); font.setFontName("宋体"); font.setFontHeight((short) 220); font.setBoldweight((short) 700); firstRowcellStyle.setFont(font); Cell cel = headTitleSecond.createCell(0); cel.setCellStyle(firstRowcellStyle); cel.setCellValue("异常类型"); Cell cel1 = headTitleSecond.createCell(1); cel1.setCellStyle(firstRowcellStyle); cel1.setCellValue("产品项目"); Cell ce2 = headTitleSecond.createCell(2); ce2.setCellStyle(firstRowcellStyle); ce2.setCellValue("异常标题"); Cell cel3 = headTitleSecond.createCell(3); cel3.setCellStyle(firstRowcellStyle); cel3.setCellValue("异常内容"); Cell cel5 = headTitleSecond.createCell(4); cel5.setCellStyle(firstRowcellStyle); cel5.setCellValue("是否归零"); Cell cel6 = headTitleSecond.createCell(5); cel6.setCellStyle(firstRowcellStyle); cel6.setCellValue("发布人"); Cell cel7 = headTitleSecond.createCell(6); cel7.setCellStyle(firstRowcellStyle); cel7.setCellValue("发布时间"); Cell cel8 = headTitleSecond.createCell(7); cel8.setCellStyle(firstRowcellStyle); cel8.setCellValue("负责人"); Cell cel9 = headTitleSecond.createCell(8); cel9.setCellStyle(firstRowcellStyle); cel9.setCellValue("设计人员"); Cell cel10 = headTitleSecond.createCell(9); cel10.setCellStyle(firstRowcellStyle); cel10.setCellValue("是否有附件"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Row row = null; // 遍历集合数据，产生数据行 if (resultSend != null) { int index = 1; for (int i = 0; i < resultSend.size(); i++) { row = sheet.createRow(index); MesExceptionBillzDTO dto = resultSend.get(i); row.setHeightInPoints(22.0f); row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : ""); row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : ""); row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : ""); row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : ""); row.createCell(4).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : ""); row.createCell(5).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : "")); row.createCell(6).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : "")); row.createCell(7).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : ""); row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : "")); row.createCell(9).setCellValue(dto.getCountAttachment()>0?"是":"否"); if (i % 100 == 0) { ((SXSSFSheet) sheet).flushRows(); } index++; } } } }
exportDataNoReply方法导出结果图：
exportData方法导出结果图：
