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)
参考：
使用教程：
https://opensource.afterturn.cn/doc/easypoi.html#4
http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8
链接：https://pan.baidu.com/s/1gBHBI4Lx-roEXrVwvzaBxQ
提取码：dbht
测试项目:
http://git.oschina.net/lemur/easypoi-test
.......