业务开发中经常会遇到导出excel的情况，我们大都会借助第三方工具如 Apache POI或alibaba/easyexcel
easy/excel 已经提供了模板填充excel的方法，但是如果遇到如下所示的复杂的横向平铺数据，简单的模板填充则不能达到效果
示例数据
表格中的轮数数据也是不确定的，其数据结构如下所示
[
{
"brand": "brand",
"buzzRound": 2,
"cost": 2.91,
"explain": "3",
"invoiceType": "增值税专用发票",
"materialCode": "60010300000001",
"materialName": "裁纸器",
"planQuantity": 1.0,
"price": 3.0,
"remark": "",
"specification": "B41",
"tax": 0.09,
"taxRate": 3,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 3.0,
"unit": "个"
},
{
"brand": "brand",
"buzzRound": 2,
"cost": 1.96,
"explain": "2",
"invoiceType": "增值税普通发票",
"materialCode": "60010300000002",
"materialName": "长尾夹",
"planQuantity": 1.0,
"price": 2.0,
"remark": "",
"specification": "32mm",
"tax": 0.04,
"taxRate": 2,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 2.0,
"unit": "盒"
},
{
"brand": "brand",
"buzzRound": 2,
"cost": 0.99,
"explain": "1",
"invoiceType": "增值税专用发票",
"materialCode": "60010300000003",
"materialName": "垃圾桶",
"planQuantity": 1.0,
"price": 1.0,
"remark": "",
"specification": "35*35*45cm",
"tax": 0.01,
"taxRate": 1,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 1.0,
"unit": "个"
},
{
"brand": "brand",
"buzzRound": 1,
"cost": 2079.21,
"explain": "备注",
"invoiceType": "增值税普通发票",
"materialCode": "60010300000001",
"materialName": "裁纸器",
"planQuantity": 1.0,
"price": 2100.0,
"remark": "",
"specification": "B41",
"tax": 20.79,
"taxRate": 1,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 2100.0,
"unit": "个"
},
{
"brand": "brand",
"buzzRound": 1,
"cost": 2079.21,
"explain": "备注",
"invoiceType": "增值税普通发票",
"materialCode": "60010300000002",
"materialName": "长尾夹",
"planQuantity": 1.0,
"price": 2100.0,
"remark": "",
"specification": "32mm",
"tax": 20.79,
"taxRate": 1,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 2100.0,
"unit": "盒"
},
{
"brand": "brand",
"buzzRound": 1,
"cost": 2079.21,
"explain": "备注",
"invoiceType": "增值税普通发票",
"materialCode": "60010300000003",
"materialName": "垃圾桶",
"planQuantity": 1.0,
"price": 2100.0,
"remark": "",
"specification": "35*35*45cm",
"tax": 20.79,
"taxRate": 1,
"tenderId": "b44464dbf0649ba59eba1b7387fec69a",
"totalPrice": 2100.0,
"unit": "个"
}
]
这里我们采用的方法是:首先拆分excel模板，使用POI操作excel复制局部模板，最后使用easyexcel的多列表组合填充填充,模板如下所示
序号 | 物料编码 | 物料名称 | 规格型号 | 品牌/厂家 | 计划备注 | 单位 | 数量 | 第#轮 | | | | | | |
发票类型 | 含税单价(元) | 含税总价(元) | 税率(%) | 总税金(元) | 不含税总价(元) | 备注 | ||||||||
| {data0.materialCode} | {data0.materialName} | {data0.specification} | {data0.brand} | {data0.remark} | {data0.unit} | {data0.planQuantity} | {data#.invoiceType} | {data#.price} | {data#.totalPrice} | {data#.taxRate} | {data#.tax} | {data#.cost} | {data#.explain} |
使用POI根据轮数复制data#处模板，并修改模板的数据，修改后的模板如下:
序号 | 物料编码 | 物料名称 | 规格型号 | 品牌/厂家 | 计划备注 | 单位 | 数量 | 第2轮 | | | | | | | 第1轮 | | | | | | |
类型 | 含税单价(元) | 含税总价(元) | 税率(%) | 总税金(元) | 不含税总价(元) | 报价备注 | 类型 | 含税单价(元) | 含税总价(元) | 税率(%) | 总税金(元) | 不含税总价(元) | 备注 | ||||||||
| {data0.materialCode} | {data0.materialName} | {data0.specification} | {data0.brand} | {data0.remark} | {data0.unit} | {data0.planQuantity} | {data2.invoiceType} | {data2.price} | {data2.totalPrice } | {data2.taxRate} | {data2.tax} | {data2.cost} | {data2.explain} | {data1.invoiceType} | {data1.price} | {data1.totalPrice } | {data1.taxRate} | {data1.tax} | {data1.cost} | {data1.explain} |
核心代码如下
try {
String templateFileName = "C:\\Users\\user\\Desktop\\template.xlsx";
File fi = new File(templateFileName);
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(fi));
Sheet sheet = wb.getSheetAt(0);
//起始列
int startColumn = 8;
//每轮列数
int step = 8;
//轮数
int round = 3;
//轮次
for (int i = 1; i < round; i++) {
//行
for (int j = 0; j < 3; j++) {
//列
for (int k = 0; k < step; k++) {
copyCell(sheet, j, startColumn + k, sheet, j, (i * startColumn) + step + k);
}
if (j == 0) {
continue;
}
}
}
//更新模板数字
for (int i = 1; i <= round; i++) {
//行
for (int j = 0; j < 3; j++) {
if (j == 0) {
Cell cell = getCell(sheet, j, startColumn + ((i - 1) * step));
cell.setCellValue(cell.getStringCellValue().replace("#", String.valueOf(round - i + 1)));
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(
j, //first row (0-based)
j, //last row (0-based)
startColumn + ((i - 1) * step), //first column (0-based)
startColumn + ((i - 1) * step) + (step - 1) //last column (0-based)
));
}
if (j == 2) {
//列
for (int k = 0; k < step; k++) {
Cell cell = getCell(sheet, j, startColumn + ((i - 1) * step) + k);
cell.setCellValue(cell.getStringCellValue().replace("#", String.valueOf(round - i + 1)));
}
}
}
}
FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\user\\Desktop\\" + System.currentTimeMillis() + ".xlsx");
wb.write(fileOutputStream);
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 从(s1,r1,c1)单元格复制内容到(s2,r2,c2)
*/
public static void copyCell(Sheet s1, int r1, int c1, Sheet s2, int r2, int c2) {
Cell cell = getCell(s2, r2, c2);
Object obj = getObj(s1, r1, c1);
if (null == obj) {
//为空不处理
} else if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Double) {
cell.setCellValue((double) obj);
} else {
System.out.println("未处理类型：" + obj.getClass());
}
}
private static Cell getCell(Sheet sheet, int r, int c) {
Row row = sheet.getRow(r);
if (row == null) {
row = sheet.createRow(r);
}
Cell cell = row.getCell(c);
if (cell == null) {
cell = row.createCell(c);
}
return cell;
}
private static Object getObj(Sheet sheet, int r, int c) {
Cell cell = getCell(sheet, r, c);
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BLANK) {
return null;
} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
if (cell.getCachedFormulaResultTypeEnum() == CellType.NUMERIC) {
return cell.getNumericCellValue();
} else {
return cell.getStringCellValue();
}
} else {
System.out.println("(" + r + "," + c + ")未处理类型：" + cell.getCellTypeEnum());
}
return "";
}以上使用POI工具将数据模板制作成功，还需要调整数据结构使其对应模板中的data#数据,调整后的数据结构如下：其中key标识轮数 ，数组则为需要渲染的模板数据， key=0 表示基本信息
{
"0": [
{
"brand": "brand",
"materialCode": "60010300000001",
"materialName": "裁纸器",
"planQuantity": 1.0,
"remark": "",
"specification": "B41",
"unit": "个"
},
{
"brand": "brand",
"materialCode": "60010300000002",
"materialName": "长尾夹",
"planQuantity": 1.0,
"remark": "",
"specification": "32mm",
"unit": "盒"
},
{
"brand": "brand",
"materialCode": "60010300000003",
"materialName": "垃圾桶",
"planQuantity": 1.0,
"remark": "",
"specification": "35*35*45cm",
"unit": "个"
}
],
"1": [
{
"buzzRound": 1,
"cost": 2079.21,
"explain": "报价备注",
"invoiceType": "增值税普通发票",
"price": 2100.0,
"tax": 20.79,
"taxRate": 1,
"totalPrice": 2100.0
},
{
"buzzRound": 1,
"cost": 2079.21,
"explain": "报价备注",
"invoiceType": "增值税普通发票",
"price": 2100.0,
"tax": 20.79,
"taxRate": 1,
"totalPrice": 2100.0
},
{
"buzzRound": 1,
"cost": 2079.21,
"explain": "报价备注",
"invoiceType": "增值税普通发票",
"price": 2100.0,
"tax": 20.79,
"taxRate": 1,
"totalPrice": 2100.0
}
],
"2": [
{
"buzzRound": 2,
"cost": 2.91,
"explain": "3",
"invoiceType": "增值税专用发票",
"price": 3.0,
"tax": 0.09,
"taxRate": 3,
"totalPrice": 3.0
},
{
"buzzRound": 2,
"cost": 1.96,
"explain": "2",
"invoiceType": "增值税普通发票",
"price": 2.0,
"tax": 0.04,
"taxRate": 2,
"totalPrice": 2.0
},
{
"buzzRound": 2,
"cost": 0.99,
"explain": "1",
"invoiceType": "增值税专用发票",
"price": 1.0,
"tax": 0.01,
"taxRate": 1,
"totalPrice": 1.0
}
]
}
最后根据easyexcel的 :多列表组合填充填充 填充数据 核心代码如下:
ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).withTemplate(inputStream).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
bidMaterialHistory.forEach((k, v) -> {
excelWriter.fill(new FillWrapper("data" + k, v), writeSheet);
});
excelWriter.finish();
