POI导出案例
- 引用POI依赖
<!-- 导出为Excel所需依赖 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
2. 具体的实现
/** * POI方式导出 * @param list 数据 * @param exportFields 绑定列数组,0: 表头,1: 数据key * @param response */ public static void exportExcel(List list,String[] exportFields,HttpServletResponse response) { Workbook wb = new XSSFWorkbook(); //设置单元格式 Font font = wb.createFont(); font.setColor(Font.COLOR_NORMAL); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(font); Sheet sheet = wb.createSheet("测试数据"); // 首行冻结 sheet.createFreezePane( 0, 1, 0, 1 ); String[]headers = exportFields[0].split(","); // 创建标题行 Row titleRow = sheet.createRow(0); for (int i = 0; i < headers.length; i++) { Cell cell = titleRow.createCell(i); cell.setCellValue(headers[i]); } String[]keys = exportFields[1].split(","); for (int i = 0; i < list.size(); i++) { Row currentRow = sheet.createRow(i+1); // 获取当前行的数据 Object o = list.get(i); for (int j = 0; j < keys.length; j++) { // 设置列宽 // sheet.setColumnWidth(j, 4100); Cell cell = currentRow.createCell(j); cell.setCellStyle(cellStyle); //每个单元格的值目前做 String 处理 // cell.setCellType(CellType.STRING); // cell.setCellValue(ReflectUtil.getFieldValue(o, keys[j]).toString()); Object fieldValue = ReflectUtil.getFieldValue(o, keys[j]); if (ObjectUtil.isNotEmpty(fieldValue)){ cell.setCellValue(fieldValue.toString()); } } } // writeToExcel(response, wb, "导出数据列表"); // 生成Excel文件 try (FileOutputStream outputStream = new FileOutputStream("E:\\项目\\work\\宁波\\工作\\2024\\7\\导出\\data.xlsx")) { wb.write(outputStream); outputStream.flush(); }catch (Exception e) { logger.error(e); } } public void writeToExcel(HttpServletResponse response, Workbook wb, String fileName) { OutputStream os = null; try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("UTF-8"); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); response.setHeader("Content-Disposition", String.format("attachment; filename=%s", new String(URLEncoder.encode(fileName + ".xlsx" , "UTF-8") .getBytes("UTF-8"), "UTF-8"))); os = response.getOutputStream(); wb.write(os); os.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (os != null) { try { os.close(); } catch (IOException e) { e.printStackTrace(); } } } } public static void main(String[] args) { List<RegulatorySettlementReport> items = new ArrayList<>(); // 表头+数据key对应,利用反射赋值 String[] exportFields = { "结单状态,监管结算单号,结算发起时间,入库日期,出库日期,在库状态,仓库,货主,客户方,入库重量,入库金额,出库重量,出库金额,采销差价,铝拓服务费率,铝拓服务收益,采购付款日期,销售回款日期,入库码单号,出库单号,入库批号,品名,牌号,规格,厂家,重量,数量单位,重量单位,货物编号", "goodsAlledName,regulatorySettlementNo,settlementLaunchDate,inWhsDate,outWhsDate,stockStatusName,whsName,goodsOwnerName,customerName,inWhsAmount,inWhsMoney,outAmount,invyOutMoney,purchaseSalesPriceDiff,serviceRateName,serviceRevenue,purchasePaymentDate,salesCollectionDate,inWhsMdNo,outWhsNo,inWhsLotNo,brandName,texture,specification,producingName,invyOutAmount,quantityUnitName,weightUnitName,packageNo" }; RegulatorySettlementReport report1 = new RegulatorySettlementReport(); report1.setGoodsAlledName("未结单"); report1.setRegulatorySettlementNo("001"); items.add(report1); RegulatorySettlementReport report2 = new RegulatorySettlementReport(); report2.setGoodsAlledName("已结单"); report2.setRegulatorySettlementNo("002"); items.add(report2); exportExcel(items,exportFields,null); }