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. 具体的实现

100

101

102

103

104

105

/**
 * 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);
}