使用行转列的填充的处理实例,请参考EasyExcel行转列,不定长换行导出__easyexcel换行导出
在使用EasyExcel填充过程中,会遇到有部分列数据,看似丢失,但是填充的标记还存在,如{data.name},其实是EasyExcel内部机制导出这一列的部分数据没有被填。
经过反复的测试,向右填充时候,必须要有列头,是内部几部机制造成!
解决方案:
方案一、修改EasyExcel的Api重新打包,这种方式费时费力就不考虑.
方案二、使用POI,原生Excel工具写Excel。
生成代码如下:
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet1 = workbook.createSheet("1"); XSSFSheet sheet = workbook.createSheet("2"); XSSFRow row0 = sheet1.createRow(0); row0.createCell(0).setCellValue("aa单"); CellRangeAddress ca = new CellRangeAddress(0, 0, 0, 3 + skuList.size()); sheet1.addMergedRegion(ca); CellStyle cs = workbook.createCellStyle(); cs.setAlignment(HorizontalAlignment.CENTER); cs.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont font = workbook.createFont(); font.setFontHeight(14); font.setBold(true); cs.setFont(font); row0.getCell(0).setCellStyle(cs); XSSFRow row1 = sheet1.createRow(1); String[] headers = {"序号", "商品编码", "商品编码", "商品名称", "商品单位", "商品规格", "ab", "订购总数量", "{data2.deliveryName}"}; CellStyle cs1 = workbook.createCellStyle(); cs1.setAlignment(HorizontalAlignment.CENTER); cs1.setVerticalAlignment(VerticalAlignment.CENTER); XSSFFont font1 = workbook.createFont(); font1.setFontHeight(10); font1.setBold(true); cs1.setFont(font1); for (int i = 0; i < 9; i++) { sheet1.setColumnWidth(i, 13 * 256); XSSFCell cell = row1.createCell(i); cell.setCellValue(headers[i]); cell.setCellStyle(cs1); } AtomicInteger integer = new AtomicInteger(2); for (ExportSkuAndDeliveryInfo i : skuList) { XSSFRow row2 = sheet1.createRow(integer.get()); //4.创建一个单元格 row2.createCell(0).setCellValue(i.getSkuId()); row2.createCell(1).setCellValue(i.getSkuName()); row2.createCell(2).setCellValue(i.getTotalQuantity().toString()); row2.createCell(3).setCellValue(i.getRemark()); integer.addAndGet(1); }
FileOutputStream fos = new FileOutputStream(templateFileName); workbook.write(fos); workbook.close(); fos.close();
首次写入如下:
第二次填充的代码和效果如下:
final ExcelWriter writer = EasyExcel.write("d://2.xlsx").withTemplate(templateFileName).build(); FillConfig config = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build(); WriteSheet ws = EasyExcel.writerSheet().build(); writer.fill(new FillWrapper("data2", deliveryList), config, ws); tempMap.forEach((k, v) -> { writer.fill(new FillWrapper(k, v), config, ws); }); writer.finish();