Poi 导出带复杂表头的内容

目录

Poi 导出带复杂表头的内容

代码：

Contrller

Service:

导出类：PoiExcelExporter

测试用postman:

结果：

总结：

有时候遇到要导出复杂的表头

手动拼接复杂表头是难点之一

重要的引入：

maven 在pom.xml中引入；

org.apache.poipoi3.15
org.apache.poipoi-ooxml3.15



   gradle 在build.gradle：  

compile &＃39;org.apache.poi:poi:3.15&＃39;,compile &＃39;org.apache.poi:poi-ooxml:3.15&＃39;


代码：

Contrller

@RestController
@RequestMapping("/excel")
public class ExcelController {@Resourceprivate ExcelService excelService;@GetMapping("/exportOrder")@ApiOperation(value="导出数据接口")public void exportOrder(HttpServletResponse response) {excelService.exportOrder(response);}
}

Service:

@Service
public class ExcelService {@Resourceprivate PoiExcelExporter poiExcelExporter;public void exportOrder(HttpServletResponse response) {String tableName = "order " + LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue() + "-"+ LocalDate.now().getDayOfMonth();// 获取需要导出的数据List> dataList = getResultData();// 需要展示的列List fieldList = Lists.newArrayList("orderTime","total","except","overTime","timelyRate");// 匹配数据List> results = matchFieldData(dataList, fieldList);// 设置表头List> titleList = setTableTitle();// 设置表头合并List cellRangeAddressList = addMergeOrder();try {poiExcelExporter.exportMulTitle(tableName, titleList, results, cellRangeAddressList, response);} catch (Exception e) {e.printStackTrace();}}private List> getResultData(){String data = "[{\"orderTime\":\"2021-03-03 10:10:10\",\"total\":55,\"except\":12,\"overTime\":8,\"timelyRate\":\"88%\",\"successRate\":\"77\"},{\"orderTime\":\"2021-03-03 10:15:10\",\"total\":155,\"except\":44,\"overTime\":20,\"timelyRate\":\"78%\",\"successRate\":\"65\"},{\"orderTime\":\"2021-03-03 10:20:10\",\"total\":85,\"except\":6,\"overTime\":5,\"timelyRate\":\"98%\",\"successRate\":\"97\"}]";return GsonUtils.changeJsonToList(data);}private List> setTableTitle(){List> titleList = new ArrayList<>();List titleOne= Lists.newArrayList("统计","","","","");List titleTwo = Lists.newArrayList("日期","综合","","","");List titleThree = Lists.newArrayList("","订单总量","异常量","超时量","及时率");titleList.add(titleOne);titleList.add(titleTwo);titleList.add(titleThree);return titleList;}private List addMergeOrder() {List cellRangeAddressList = new ArrayList<>();cellRangeAddressList.add(new CellRangeAddress(0, 0, 0, 4));cellRangeAddressList.add(new CellRangeAddress(1, 1, 1, 4));cellRangeAddressList.add(new CellRangeAddress(1, 2, 0, 0));return cellRangeAddressList;}private List> matchFieldData(List> dataList, List fieldList) {return ListUtils.emptyIfNull(dataList).stream().filter(Objects::nonNull).map(e -> ListUtils.emptyIfNull(fieldList).stream().map(f -> MapUtils.getString(e, f)).collect(Collectors.toList())).collect(Collectors.toList());}}

GsonUtils 引用 看字符串转化为list

导出类：PoiExcelExporter

@Component
public class PoiExcelExporter {private static Logger LOGGER = LoggerFactory.getLogger(PoiExcelExporter.class);public void exportMulTitle(String tableName, List> titleList, List> contentList,List cellRangeAddressList, HttpServletResponse response) {LOGGER.info("------------excel export start-----------");try (HSSFWorkbook wb = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {// 行号 一行一行添加int rowNum = 0;CellStyle style = createTitleCellStyle(wb);HSSFSheet hssfSheet = wb.createSheet("sheet1");// 先处理标题头for (List title : ListUtils.emptyIfNull(titleList)) {setSheetTitle(hssfSheet, title, style, rowNum);rowNum++;System.out.println("title rowNum " + rowNum);}// 再处理 cellRangeAddressListif(CollectionUtils.isNotEmpty(cellRangeAddressList)){cellRangeAddressList.forEach(hssfSheet::addMergedRegion);}if (CollectionUtils.isNotEmpty(contentList)) {// 再处理内容for (List content : ListUtils.emptyIfNull(contentList)) {setSheetContent(hssfSheet, content, rowNum);rowNum++;System.out.println("content rowNum " + rowNum);}LOGGER.info("------------excel export complied-----------");}setResponseExcel(response, tableName);wb.write(output);output.flush();} catch (Exception e) {LOGGER.error("export wrong: ", e);throw new RuntimeException(e);}}private static void setSheetTitle(HSSFSheet sheet, List titleList, CellStyle style, int rownum) {HSSFRow row = sheet.createRow(rownum);AtomicInteger i = new AtomicInteger();for (String title : ListUtils.emptyIfNull(titleList)) {HSSFCell cell = row.createCell(i.getAndIncrement());cell.setCellValue(title);cell.setCellStyle(style);}}private static void setSheetContent(HSSFSheet sheet, List titleList, int rownum) {HSSFRow row = sheet.createRow(rownum);AtomicInteger i = new AtomicInteger();for (String title : ListUtils.emptyIfNull(titleList)) {HSSFCell cell = row.createCell(i.getAndIncrement());cell.setCellValue(title);}}private static CellStyle createTitleCellStyle(HSSFWorkbook wb) {CellStyle style = wb.createCellStyle();Font fOnt= wb.createFont();font.setBold(true);//粗体显示style.setFont(font);//单元格样式cell1.setCellStyle(style);//给cell1这个单元格设置样式style.setFont(font);style.setAlignment(HorizontalAlignment.CENTER); // 居中return style;}private static void setResponseExcel(HttpServletResponse response, String tableName)throws UnsupportedEncodingException {//获取输出流response.reset();//设置响应的编码response.setContentType("application/x-download");//下面三行是关键代码，处理乱码问题response.setCharacterEncoding("utf-8");//设置浏览器响应头对应的Content-dispositionString fileName = URLEncoder.encode(tableName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");}}

测试用postman:

结果：

总结：

    导出带复杂表头的时候，重点是如何拼接复杂的表头。导出的时候，先处理表题，再进行合并，然后处理具体的数据。 多表头需要这样处理， 简单表头呢？

   知道了导出，如何读取呢？    如何读取本地文件excel