easypoi导出动态表头excel

1: springBoot项目maven依赖：

 <dependency>
         <groupId>cn.afterturn</groupId>
         <artifactId>easypoi-spring-boot-starter</artifactId>
         <version>4.1.2</version>
  </dependency>

 根据自己的poi版本选择

 <dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>3.3.0</version>
 </dependency>

测试导出(数据组装如下)：

@Test
public void dynaCol() {
try {
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity = new ExcelExportEntity("商品名称", "title");
colEntity.setNeedMerge(true);
colList.add(colEntity);

colEntity = new ExcelExportEntity("供应商", "supplier");
colEntity.setNeedMerge(true);
colList.add(colEntity);

ExcelExportEntity deliColGroup = new ExcelExportEntity("得力", "deli");
List<ExcelExportEntity> deliColList = new ArrayList<ExcelExportEntity>();
deliColList.add(new ExcelExportEntity("市场价", "orgPrice"));
deliColList.add(new ExcelExportEntity("专区价", "salePrice"));
deliColGroup.setList(deliColList);
colList.add(deliColGroup);

ExcelExportEntity jdColGroup = new ExcelExportEntity("京东", "jd");
List<ExcelExportEntity> jdColList = new ArrayList<ExcelExportEntity>();
jdColList.add(new ExcelExportEntity("市场价", "orgPrice"));
jdColList.add(new ExcelExportEntity("专区价", "salePrice"));
jdColGroup.setList(jdColList);
colList.add(jdColGroup);

List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int i = 0; i < 10; i++) {
Map<String, Object> valMap = new HashMap<String, Object>();
valMap.put("title", "名称." + i);
valMap.put("supplier", "供应商." + i);

List<Map<String, Object>> deliDetailList = new ArrayList<Map<String, Object>>();
for (int j = 0; j < 3; j++) {
Map<String, Object> deliValMap = new HashMap<String, Object>();
deliValMap.put("orgPrice", "得力.市场价." + j);
deliValMap.put("salePrice", "得力.专区价." + j);
deliDetailList.add(deliValMap);
}
valMap.put("deli", deliDetailList);

List<Map<String, Object>> jdDetailList = new ArrayList<Map<String, Object>>();
for (int j = 0; j < 2; j++) {
Map<String, Object> jdValMap = new HashMap<String, Object>();
jdValMap.put("orgPrice", "京东.市场价." + j);
jdValMap.put("salePrice", "京东.专区价." + j);
jdDetailList.add(jdValMap);
}
valMap.put("jd", jdDetailList);

list.add(valMap);
}

Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("价格分析表", "数据"), colList,
list);
FileOutputStream fos = new FileOutputStream("D:/价格分析表.tt.xls");
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

导出结果图：

多sheet导出（数据组装）

public String export(){

Workbook workBook = null;
try {
List<DeptUtil> exportList = exportService.exportList();
System.err.println(JSONArray.toJSONString(exportList));

// 创建参数对象（用来设定excel得sheet得内容等信息）
ExportParams deptExportParams = new ExportParams();
// 设置sheet得名称
deptExportParams.setSheetName("员工报表1");
// 创建sheet1使用得map
Map<String, Object> deptExportMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
deptExportMap.put("title", deptExportParams);
// 模版导出对应得实体类型
deptExportMap.put("entity", DeptUtil.class);
// sheet中要填充得数据
deptExportMap.put("data", exportList);

ExportParams empExportParams = new ExportParams();
empExportParams.setSheetName("员工报表2");
// 创建sheet2使用得map
Map<String, Object> empExportMap = new HashMap<>();
empExportMap.put("title", empExportParams);
empExportMap.put("entity", DeptUtil.class);
empExportMap.put("data", exportList);

// 将sheet1、sheet2、sheet3使用得map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(deptExportMap);
sheetsList.add(empExportMap);
// 执行方法
workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
fileName = URLEncoder.encode("员工报表导出", "UTF-8");
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workBook.write(outputStream);
outputStream.flush();
byte[] byteArray = outputStream.toByteArray();
excelStream = new ByteArrayInputStream(byteArray,0,byteArray.length);
outputStream.close();

}catch (Exception e){
e.printStackTrace();
}finally {
if(workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return "success";
}

导出结果图：

什么场景该用哪个方法?

– 导出
1.正规excel导出 (格式简单,数据量可以,5W以内吧)
注解方式: ExcelExportUtil.exportExcel(ExportParams entity, Class<?> pojoClass,Collection<?> dataSet)
2.不定多少列,但是格式依然简单数据库不大
自定义方式: ExcelExportUtil.exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,Collection<?> dataSet)
3.数据量大超过5W,还在100W以内
注解方式 ExcelExportUtil.exportBigExcel(ExportParams entity, Class<?> pojoClass,IExcelExportServer server, Object queryParams)
自定义方式: ExcelExportUtil.exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,IExcelExportServer server, Object queryParams)
4.样式复杂,数据量尽量别大
模板导出 ExcelExportUtil.exportExcel(TemplateExportParams params, Map<String, Object> map)
5.一次导出多个风格不一致的sheet
模板导出 ExcelExportUtil.exportExcel(Map<Integer, Map<String, Object>> map,TemplateExportParams params)
6.一个模板但是要导出非常多份
模板导出 ExcelExportUtil.exportExcelClone(Map<Integer, List<Map<String, Object>>> map,TemplateExportParams params)
7.模板无法满足你的自定义,试试html
自己构造html,然后我给你转成excel ExcelXorHtmlUtil.htmlToExcel(String html, ExcelType type)
8.数据量过百万级了.放弃excel吧,csv导出
注解方式: CsvExportUtil.exportCsv(CsvExportParams params, Class<?> pojoClass, OutputStream outputStream)
自定义方式: CsvExportUtil.exportCsv(CsvExportParams params, List<ExcelExportEntity> entityList, OutputStream outputStream)
9.word导出
模板导出: WordExportUtil.exportWord07(String url, Map<String, Object> map)
10.PDF导出
模板导出: TODO
– 导入
如果想提高性能 ImportParams 的concurrentTask 可以帮助并发导入,仅单行,最小1000
excel有单个的那种特殊读取,readSingleCell 参数可以支持
1. 不需要检验,数据量不大(5W以内)
注解或者MAP: ExcelImportUtil.importExcel(File file, Class<?> pojoClass, ImportParams params)
2. 需要导入,数据量不大
注解或者MAP: ExcelImportUtil.importExcelMore(InputStream inputstream, Class<?> pojoClass, ImportParams params)
3. 数据量大了,或者你有特别多的导入操作,内存比较少,仅支持单行
SAX方式 ExcelImportUtil.importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params, IReadHandler handler)
4. 数据量超过EXCEL限制,CSV读取
小数据量: CsvImportUtil.importCsv(InputStream inputstream, Class<?> pojoClass,CsvImportParams params)
大数据量: CsvImportUtil.importCsv(InputStream inputstream, Class<?> pojoClass,CsvImportParams params, IReadHandler readHandler)