poi 导出带复杂表头的内容

Poi 导出带复杂表头的内容

目录

Poi 导出带复杂表头的内容

代码：

Contrller

Service:

导出类：PoiExcelExporter

测试用postman:

结果：

总结：

---

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

 

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

 

 

重要的引入：

maven 在pom.xml中引入；

    

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
 
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>

 

   gradle 在build.gradle：  

compile 'org.apache.poi:poi:3.15',
 compile 'org.apache.poi:poi-ooxml:3.15'

 

代码：

Contrller

@RestController
@RequestMapping("/excel")
public class ExcelController {
    @Resource
    private ExcelService excelService;

    @GetMapping("/exportOrder")
    @ApiOperation(value="导出数据接口")
    public void exportOrder(HttpServletResponse response) {
        excelService.exportOrder(response);
    }
}

 

Service:

@Service
public class ExcelService {

    @Resource
    private PoiExcelExporter poiExcelExporter;

     public void exportOrder(HttpServletResponse response) {
        String tableName = "order " + LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue() + "-"
                + LocalDate.now().getDayOfMonth();
        // 获取需要导出的数据
        List<Map<String, Object>> dataList = getResultData();
        // 需要展示的列
        List<String> fieldList = Lists.newArrayList("orderTime","total","except","overTime","timelyRate");
        // 匹配数据
        List<List<String>> results = matchFieldData(dataList, fieldList);
        // 设置表头
        List<List<String>> titleList = setTableTitle();
       // 设置表头合并
        List<CellRangeAddress> cellRangeAddressList = addMergeOrder();

        try {
            poiExcelExporter.exportMulTitle(tableName, titleList, results, cellRangeAddressList, response);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private List<Map<String, Object>> 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<List<String>> setTableTitle(){
        List<List<String>> titleList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList("统计","","","","");
        List<String> titleTwo = Lists.newArrayList("日期","综合","","","");
        List<String> titleThree = Lists.newArrayList("","订单总量","异常量","超时量","及时率");
        titleList.add(titleOne);
        titleList.add(titleTwo);
        titleList.add(titleThree);
        return titleList;
    }

    private List<CellRangeAddress> addMergeOrder() {
        List<CellRangeAddress> 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<List<String>> matchFieldData(List<Map<String, Object>> dataList, List<String> 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<List<String>> titleList, List<List<String>> contentList,
            List<CellRangeAddress> 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<String> title : ListUtils.emptyIfNull(titleList)) {
                setSheetTitle(hssfSheet, title, style, rowNum);
                rowNum++;
                System.out.println("title rowNum " + rowNum);
            }
            // 再处理 cellRangeAddressList
            if(CollectionUtils.isNotEmpty(cellRangeAddressList)){
                cellRangeAddressList.forEach(hssfSheet::addMergedRegion);
            }

            if (CollectionUtils.isNotEmpty(contentList)) {
                // 再处理内容
                for (List<String> 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<String> 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<String> 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-disposition
        String fileName = URLEncoder.encode(tableName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
    }

}

 

测试用postman:

 

结果：

 

总结：

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

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