Java动态行动态列复杂excel表格导出
1.背景
要求导出一个表格，如下图，其中箱数为动态的（不确定有多少箱，需要从数据库获取）【动态列】，货件中商品数量也为了动态的【动态行】；其中总箱重、总体积、发货量三个量是通过excel函数生成（要求能联动，所以要用函数；否则程序直接计算后进行填充更方便），其余数据都是从数据库进行获取。
2.分析
由于这个表格比较复杂，直接操作单元格工作量巨大；后选择easyexcel模板填充模式进行数据导出；大致思路为两次导出；第一次根据原始模板将箱数获取到处理动态列，处理结果作为第二次导出的模板；第二次导出根据上次导出的模板获取行数据，处理动态行；最后将第一次导出的临时模板删除。
3.环境
easyexcel：2.2.9 ；poi:3.17
*选择更高版本也是可以的，但是要注意easyexcel于poi对应的兼容版本，其他maven用到什么自己补充哈
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.9</version>
</dependency>
4.编写模板
将数据分区,data1区的数据值均为单个所以我们在第一次临时模板时就可以将data1区的数据值填充好;data2以及data3区的数据我们导出临时模板时先不进行处理,但是需要注意,我们仍需要将填充对象进行保留,导出临时模板主要处理box区数据,生成好对应的箱列以及箱列下面对应的填充对象;
5.代码实现
public void compositeFill() {
//模板路径
String templateFileName = "D:\\template.xlsx";
//临时模板存放路径
String fileName = "D:\\"+ System.currentTimeMillis() + ".xlsx";
try{
//第一次导出
ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
//默认为垂直处理[VERTICAL],处理box区需要水平处理定义为[HORIZONTAL]
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//处理box区箱信息以及箱对应的填充对象,
excelWriter.fill(new FillWrapper("box", box()), fillConfig, writeSheet);
//处理data2区填充对象,将填充对象原样保留,用于第二次导出时使用
excelWriter.fill(new FillWrapper("data2", data2()), writeSheet);
//处理data3区填充对象,将填充对象原样保留,用于第二次导出时使用方式合上面相同,如果要运行可以注掉本行和data3()方法
excelWriter.fill(new FillWrapper("data3", data3()), writeSheet);
//填充data1区数据
excelWriter.fill(new FillWrapper("data1", data1()), writeSheet);
excelWriter.finish();
//第二次导出[写数据],此时临时模板已经被写在fileName路径下,第二次导出使用该临时模板作为模板进行填充数据
//导出文件所在路径
String fileName2 = "D:\\" + "目标文件.xlsx";
ExcelWriter excel = EasyExcel.write(fileName2).withTemplate(fileName).build();
//sheet处理添加监听,用于处理发货量列的sum函数
WriteSheet Sheet = EasyExcel.writerSheet().registerWriteHandler(new EasyExcelCellWriteHandler()).build();
FillConfig config = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//填充data2区数据,从数据库获取,处理为List<JSONObject>,各箱数量通过反射进行处理,给原对象添加字段并赋值然后转为JSONObject添加进list
excel.fill(new FillWrapper("data2",data2List()), Sheet);
//从数据库查询data3区数据,放入List中,将list替换下面data3List;这里就不在模拟数据了,运行时将本行注了
excel.fill(new FillWrapper("data3",data3List()), config, Sheet);
//设置强制计算公式：不然公式会以字符串的形式显示在excel中
Workbook workbook = excel.writeContext().writeWorkbookHolder().getWorkbook();
workbook.setForceFormulaRecalculation(true);
excel.finish();
}finally {
//删除临时模板
File file = new File(fileName);
if(file.exists() && file.isFile()){
file.delete();
}
}
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public static class Box {
private String boxId;
private String boxNum;
}
private static List<Box> box() {
List<Box> list = new ArrayList<>();
for (int i = 1; i < 20; i++) {
Box data = new Box();
data.setBoxId("箱" + i);
data.setBoxNum(String.format("{data2.temolate_%d}", i));
list.add(data);
}
return list;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public static class ExportFBAShipmentTemplate {
private String sellerSku;
private String asin;
private String sellerSkuName;
private String fnsku;
private String specNo;
private String declareNum;
}
private static List<ExportFBAShipmentTemplate> data2() {
List<ExportFBAShipmentTemplate> list = new ArrayList<>();
ExportFBAShipmentTemplate template = new ExportFBAShipmentTemplate();
list.add(template);
template.setSellerSku("{data2.sellerSku}");
template.setAsin("{data2.asin}");
template.setSellerSkuName("{data2.sellerSkuName}");
template.setFnsku("{data2.fnsku}");
template.setSpecNo("{data2.specNo}");
template.setDeclareNum("{data2.declareNum}");
return list;
}
//data3()方法与data2()方法类似,这里就不在重复书写了
private static List<xxx> data3() {
List<xxx> list = new ArrayList<>();
xxx template = new xxx();
list.add(template);
...
return list;
}
private static List<Map<String, Object>> data1() {
List<Map<String, Object>> list = new ArrayList<>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("amazonShipmentId", "编码");
map.put("shipmentName", "名称");
//其余数据也时一样的模式put进map
...
list.add(map);
return list;
}
private List<Map<String, Object>> data2List() {
List<Map<String, Object>> list = ListUtils.newArrayList();
Map<String, Object> map = new HashMap<>();
map.put("sellerSku", "123");
map.put("asin", 123);
map.put("sellerSkuName", "sellerSkuName");
map.put("fnsku", "fnsku");
map.put("declareNum", 3);
map.put("temolate_1", 12);
map.put("temolate_2", 12);
map.put("temolate_3", 12);
Map<String, Object> map1 = new HashMap<>();
map1.put("sellerSku", "123");
map1.put("asin", 123);
map1.put("sellerSkuName", "sellerSkuName");
map1.put("fnsku", "fnsku");
map1.put("declareNum", 3);
map1.put("temolate_1", 12);
map1.put("temolate_2", 12);
map1.put("temolate_3", 12);
Map<String, Object> map2 = new HashMap<>();
map2.put("sellerSku", "123");
map2.put("asin", 123);
map2.put("sellerSkuName", "sellerSkuName");
map2.put("fnsku", "fnsku");
map2.put("declareNum", 3);
map2.put("temolate_1", 12);
map2.put("temolate_2", 12);
map2.put("temolate_3", 12);
list.add(map);
list.add(map1);
list.add(map2);
return list;
}
第一次导出生成的临时模板如下图
第二次导出目标文件如下图
6.监听器填充公式类
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import groovy.util.logging.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import java.util.List;
@Slf4j
public class EasyExcelCellWriteHandler implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//sheet页码
Integer sheetNo = writeSheetHolder.getSheetNo();
//第一页
if (sheetNo == 0) {
//行
int rowNum = cell.getRowIndex() + 1;
//列
int columnNum = cell.getColumnIndex();
if(columnNum == 6 && rowNum > 10){
cell.setCellFormula("SUM(I"+rowNum+":ZZ"+rowNum+")");
}
}
}
}
7.java为实体动态添加字段工具类
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.collect.Maps;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.springframework.cglib.beans.BeanGenerator;
import org.springframework.cglib.beans.BeanMap;
import java.beans.PropertyDescriptor;
import java.util.Map;
/**
* @author: jack
* @Date: 2019/5/15 21:30
* @Description: 动态添加类的属性
*/
public class ReflectUtil {
public static Object getTarget(Object dest, Map<String, Object> addProperties) {
PropertyUtilsBean propertyUtilsBean =new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(dest);
Map<String, Class> propertyMap = Maps.newHashMap();
for(PropertyDescriptor d : descriptors) {
if(!"class".equalsIgnoreCase(d.getName())) {
propertyMap.put(d.getName(), d.getPropertyType());
}
}
// add extra properties
addProperties.forEach((k, v) -> propertyMap.put(k, v.getClass()));
// new dynamic bean
DynamicBean dynamicBean =new DynamicBean(dest.getClass(), propertyMap);
// add old value
propertyMap.forEach((k, v) -> {
try{
// filter extra properties
if(!addProperties.containsKey(k)) {
dynamicBean.setValue(k, propertyUtilsBean.getNestedProperty(dest, k));
}
}catch (Exception e) {
e.printStackTrace();
}
});
// add extra value
addProperties.forEach((k, v) -> {
try{
dynamicBean.setValue(k, v);
}catch (Exception e) {
e.printStackTrace();
}
});
Object target = dynamicBean.getTarget();
return target;
}
public static class DynamicBean {
/**
* 目标对象
*/
private Object target;
/**
* 属性集合
*/
private BeanMap beanMap;
public DynamicBean(Class superclass, Map<String, Class> propertyMap) {
this.target = generateBean(superclass, propertyMap);
this.beanMap = BeanMap.create(this.target);
}
/**
* bean 添加属性和值
*
* @param property
* @param value
*/
public void setValue(String property, Object value) {
beanMap.put(property, value);
}
/**
* 获取属性值
*
* @param property
* @return
*/
public Object getValue(String property) {
return beanMap.get(property);
}
/**
* 获取对象
*
* @return
*/
public Object getTarget() {
return this.target;
}
/**
* 根据属性生成对象
*
* @param superclass
* @param propertyMap
* @return
*/
private Object generateBean(Class superclass, Map<String, Class> propertyMap) {
BeanGenerator generator =new BeanGenerator();
if(null != superclass) {
generator.setSuperclass(superclass);
}
BeanGenerator.addProperties(generator, propertyMap);
return generator.create();
}
}
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public static class Student {
private String name;
private String email;
}
public static void main(String[] args) throws Exception{
Student student = Student.builder().name("jack").email("xy123zk@163.com").build();
System.out.println(student.toString());
Map<String,Object> properties = Maps.newHashMap();
properties.put("address","浙江杭州");
properties.put("age",26);
ObjectMapper mapper = new ObjectMapper();
String json = mapper.writeValueAsString(getTarget(student,properties));
System.out.println(json);
}
}
8.中间踩过的坑
使用监听器填充完公式后需要强制刷新,否则不生效,在excel中展示的是字符串
模板文件放在本地或者在本地运行时放项目resources\file\templates目录下没有问题,到项目上运行报错找不到文件[通过绝对路径,相对路径都不可以,可能是部署的jar包的问题]; 解决:通过相对路径获取到流[InputStream]然后处理
原代码:ExcelWriter excelWriter = EasyExcel.write(路径).withTemplate(路径).build();
更新后的代码:ExcelWriter excelWriter = EasyExcel.write(路径).withTemplate(InputStream).build();
- 报错:Can not found file.
- cannot be resolved to absolute file path because it does not reside in the file system: jar:file:/opt/项目!/BOOT-INF/classes!/file/templates/template.xlsx
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
