引入依赖
3.9 3.9 3.9 org.apache.poi poi ${poi-version} org.apache.poi poi-ooxml ${poi-ooxml-version} org.apache.poi poi-ooxml-schemas ${poi-ooxml-schemas-version}
示例代码
DeliveryTradeQueryExportExcel.java
package com.yyw.coffee.excel;import com.yyw.coffee.framework.kit.DateKit;import com.yyw.coffee.model.DeliveryTradeQuery;import com.yyw.coffee.util.ExcelUtils;import org.apache.log4j.Logger;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.servlet.view.document.AbstractXlsxView;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.text.DecimalFormat;import java.util.List;import java.util.Map;public class DeliveryTradeQueryExportExcel extends AbstractXlsxView { private static final Logger LOGGER = Logger.getLogger(DeliveryTradeQueryExportExcel.class); @Override protected void buildExcelDocument(Map model, Workbook oldWorkbook, HttpServletRequest request, HttpServletResponse response) throws Exception { int rowMaxCache = 100; XSSFWorkbook xssfWb = new XSSFWorkbook(); SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWb, rowMaxCache); List list = (List ) model.get("data"); Boolean isWsAccount = (Boolean) model.get("isWsAccount"); int pageIndex = 0, pageSize = 30000; int pages = list.size() / pageSize; do { List subList; if (list.size() >= pageSize) { subList = list.subList(0, pageSize); } else { subList = list.subList(0, list.size()); } SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("销售出库记录" + (pageIndex * pageSize + 1)); Row sheetRow = sheet.createRow(0); int x = 0; sheetRow.createCell(x++).setCellValue("商家"); sheetRow.createCell(x++).setCellValue("产品编码"); sheetRow.createCell(x++).setCellValue("产品名称"); sheetRow.createCell(x++).setCellValue("类型"); sheetRow.createCell(x++).setCellValue("批准文号"); sheetRow.createCell(x++).setCellValue("规格"); sheetRow.createCell(x++).setCellValue("剂型"); sheetRow.createCell(x++).setCellValue("产地"); sheetRow.createCell(x++).setCellValue("生产厂家"); sheetRow.createCell(x++).setCellValue("批号"); sheetRow.createCell(x++).setCellValue("生产日期"); sheetRow.createCell(x++).setCellValue("有效期至"); sheetRow.createCell(x++).setCellValue("销售数量"); sheetRow.createCell(x++).setCellValue("单价"); sheetRow.createCell(x++).setCellValue("金额"); sheetRow.createCell(x++).setCellValue("复核员"); sheetRow.createCell(x++).setCellValue("销售日期"); sheetRow.createCell(x++).setCellValue("客户"); if (!isWsAccount) { sheetRow.createCell(x++).setCellValue("客户仓库地址"); } sheetRow.createCell(x++).setCellValue("电话"); sheetRow.createCell(x++).setCellValue("销售单号"); sheetRow.createCell(x++).setCellValue("流水号"); sheetRow.createCell(x++).setCellValue("上传状态"); sheetRow.createCell(x++).setCellValue("产品对码状态"); sheetRow.createCell(x++).setCellValue("企业对码状态"); sheetRow.createCell(x++).setCellValue("药品编码-药监"); sheetRow.createCell(x++).setCellValue("药品名称-药监"); sheetRow.createCell(x++).setCellValue("药品本位码-药监"); sheetRow.createCell(x++).setCellValue("包装规格-药监"); sheetRow.createCell(x++).setCellValue("批准文号-药监"); sheetRow.createCell(x).setCellValue("上传时间"); DeliveryTradeQuery base; for (int i = 0; i < subList.size(); i++) { base = subList.get(i); sheetRow = sheet.createRow(i + 1); if (null == base) { continue; } int y = 0; sheetRow.createCell(y++).setCellValue(base.getMerchantName() == null ? "" : base.getMerchantName()); sheetRow.createCell(y++).setCellValue(base.getProductCode() == null ? "" : base.getProductCode()); sheetRow.createCell(y++).setCellValue(base.getProductName() == null ? "" : base.getProductName()); String productType = ""; if (base.getProductType() != null) { if ("1".equals(base.getProductType())) { productType = "药品"; } else if ("2".equals(base.getProductType())) { productType = "医疗器械"; } else { productType = "其他"; } } sheetRow.createCell(y++).setCellValue(productType); sheetRow.createCell(y++).setCellValue(base.getApprovalNumber() == null ? "" : base.getApprovalNumber()); sheetRow.createCell(y++).setCellValue(base.getSpec() == null ? "" : base.getSpec()); sheetRow.createCell(y++).setCellValue(base.getDrugType() == null ? "" : base.getDrugType()); sheetRow.createCell(y++).setCellValue(base.getProductarea() == null ? "" : base.getProductarea()); sheetRow.createCell(y++).setCellValue(base.getManufactureName() == null ? "" : base.getManufactureName()); sheetRow.createCell(y++).setCellValue(base.getBatchNo() == null ? "" : base.getBatchNo()); sheetRow.createCell(y++).setCellValue(base.getProductionDate() == null ? "" : base.getProductionDate()); sheetRow.createCell(y++).setCellValue(base.getValidityDate() == null ? "" : base.getValidityDate()); sheetRow.createCell(y++).setCellValue(base.getSaleNum() == null ? 0 : base.getSaleNum()); sheetRow.createCell(y++).setCellValue(base.getSalePrice() == null ? "" : base.getSalePrice().toString()); double sumMoney = 0; DecimalFormat df = new DecimalFormat("#.000000"); if (base.getSaleNum() != null && base.getSalePrice() != null) { sumMoney = base.getSaleNum() * base.getSalePrice(); } if (sumMoney != 0) { sheetRow.createCell(y++).setCellValue(df.format(sumMoney)); } else { sheetRow.createCell(y++).setCellValue(sumMoney); } sheetRow.createCell(y++).setCellValue(base.getChecker() == null ? "" : base.getChecker()); sheetRow.createCell(y++).setCellValue(base.getSaleTime() == null ? "" : DateKit.dateFormat(base.getSaleTime(), "yyyy-MM-dd")); sheetRow.createCell(y++).setCellValue(base.getCustomerName() == null ? "" : base.getCustomerName()); if (!isWsAccount) { sheetRow.createCell(y++).setCellValue(base.getConsumerWarehouseAddr() == null ? "" : base.getConsumerWarehouseAddr()); } sheetRow.createCell(y++).setCellValue(base.getConsumerPhone() == null ? "" : base.getConsumerPhone()); sheetRow.createCell(y++).setCellValue(base.getSaleNo() == null ? "" : base.getSaleNo()); sheetRow.createCell(y++).setCellValue(base.getTransactionNo() == null ? "" : base.getTransactionNo()); String pushStatus = ""; if (base.getPushStatus() != null) { if (base.getPushStatus() == 0) { pushStatus = "初始化"; } else if (base.getPushStatus() == 1) { pushStatus = "上传成功"; } else if (base.getPushStatus() == 9) { pushStatus = "上传失败"; } } sheetRow.createCell(y++).setCellValue(pushStatus); sheetRow.createCell(y++).setCellValue(base.getProductConvertState() == null ? "未对码" : (base.getProductConvertState() == 0 ? "未对码" : "对码成功")); if ("10002".equals(base.getCfdaCode())) { sheetRow.createCell(y++).setCellValue(base.getEnterpriceConvertState() == null ? "对码成功" : (base.getEnterpriceConvertState() == 0 ? "未对码" : "对码成功")); } else { sheetRow.createCell(y++).setCellValue("对码成功"); } sheetRow.createCell(y++).setCellValue(base.getCfdaProductCode() == null ? "" : base.getCfdaProductCode()); sheetRow.createCell(y++).setCellValue(base.getCfdaProductName() == null ? "" : base.getCfdaProductName()); sheetRow.createCell(y++).setCellValue(base.getCfdaMedicineNo() == null ? "" : base.getCfdaMedicineNo()); sheetRow.createCell(y++).setCellValue(base.getCfdaBzgg() == null ? "" : base.getCfdaBzgg()); sheetRow.createCell(y++).setCellValue(base.getCfdaApprovalNumber() == null ? "" : base.getCfdaApprovalNumber()); sheetRow.createCell(y).setCellValue(base.getCreateTime() == null ? "" : DateKit.dateFormat(base.getCreateTime(), "yyyy-MM-dd")); } list.removeAll(subList); LOGGER.info("last length: " + list.size()); pageIndex++; } while (pageIndex <= pages); ExcelUtils.writeExcel("文件名在js中指定.xlsx", workbook, response); }} ExcelUtils.java
package com.yyw.coffee.util;import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.http.HttpServletResponse;import java.io.OutputStream;public class ExcelUtils { public static void writeExcel(String filename, Workbook workbook, HttpServletResponse response) throws Exception { OutputStream ouputStream = null; try { filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment;filename=" + filename); ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (null != ouputStream) { ouputStream.close(); } } }} Controller调用代码
@RequestMapping(value="/exportDeliveryTradeQuery") public ModelAndView exportDeliveryTradeQuery(HttpServletRequest request, HttpServletResponse response,@ModelAttribute DeliveryTradeQuery query,ModelMap model){ List list = deliveryTradeService.exportDeliveryTradeQuery(query); DeliveryTradeQueryExportExcel excel = new DeliveryTradeQueryExportExcel(); BasicUserInfo basicUserInfo = SSOUtil.getUserInfo(request, response, false); String userId = basicUserInfo == null ? "" : basicUserInfo.getUserId(); return new ModelAndView(excel,"data",list) .addObject("deliveryType", query.getDeliveryType()) .addObject("isWsAccount", merchantService.isWsAccount(userId)); } 前台脚本
exprotExcel: function () { var errorMsg = deliveryTrade.valiQryForm(); if (errorMsg != "") { showMessage("", errorMsg, "warning"); return; } $('input[type=text]:not(:disabled)').each(function () { $(this).val($.trim($(this).val())); }); showMessage("", "单次最多导出xx万条数据！", "success"); var page_url = '../../trademanage/exportDeliveryTradeQuery'; var req = new XMLHttpRequest(); var params = $('#deliveryTradeForm').serialize(); $("#exportBtn:button:not(:disabled)").attr("di", "di").attr("disabled", true) .css("border-color", "#c2c2c2").css("background-color", "#c2c2c2"); req.open("POST", page_url + "?" + params, true); req.responseType = "blob"; req.onreadystatechange = function () { if (req.readyState === 4 && req.status === 200) { var filename = "销售出库记录" + formatDate() + ".xlsx"; if (typeof window.chrome !== 'undefined') { // Chrome version var link = document.createElement('a'); link.href = window.URL.createObjectURL(req.response); link.download = filename; link.click(); } else if (typeof window.navigator.msSaveBlob !== 'undefined') { // IE version var blob = new Blob([req.response], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}); window.navigator.msSaveBlob(blob, filename); } else { // Firefox version var file = new File([req.response], filename, {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}); window.open(URL.createObjectURL(file)); } $("#exportBtn:button[di=di]").attr("disabled", false).removeAttr("di") .css("border-color", "#4cae4c").css("background-color", "#5cb85c"); } }; req.send(); },