Poi 导出带复杂表头多个sheet的内容

最新推荐文章于 2023-09-15 17:25:10 发布

天狼1222

最新推荐文章于 2023-09-15 17:25:10 发布

阅读量561

点赞数

分类专栏：工具文章标签： poi 导出excel 复杂表头多个sheet

本文链接：https://blog.csdn.net/qq_35461948/article/details/114384038

版权

工具专栏收录该内容

18 篇文章 2 订阅

订阅专栏

Poi 导出带复杂表头多个sheet的内容

代码：

Contrller

Service:

导出工具类：ExcelExportUtil

知道了如何导出单个sheet复杂表头的内容？如何导出多个的呢？结和导出简单表头多个sheet的内容，整理下内容。

目标：

直接上代码：

代码：

Contrller

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

    @GetMapping("/exportMulSheetMulHeader")
    @ApiOperation(value="导出多个sheet多表头数据接口")
    public void exportMulSheetMulHeader(HttpServletResponse response) {
        excelService.exportMulSheetMulHeader(response);
    }
}

Service:

@Service

public class ExcelService {

    public void exportMulSheetMulHeader(HttpServletResponse response) {
        String tableName = "exportMulSheetMulHeader " + LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue() + "-"
                + LocalDate.now().getDayOfMonth();
        // 获取需要导出的数据
        List<Map<String, Object>> dataList = getResultData();
        List<List<List<String>>> titleList = new ArrayList<>();
        List<List<List<String>>> contentList = new ArrayList<>();
        List<List<CellRangeAddress>> cellRangeAddressList = new ArrayList<>();
        // 需要展示的列
        List<String> fieldList1 = Lists.newArrayList("orderTime","total","except","overTime","successRate");
        List<String> fieldList2 = Lists.newArrayList("orderTime","total","except","timelyRate","successRate");
        // 匹配数据
        List<List<String>> results1 = matchFieldData(dataList, fieldList1);
        List<List<String>> results2 = matchFieldData(dataList, fieldList2);
        contentList.add(results1);
        contentList.add(results2);
        // 设置表题
        titleList.add(getTitleOne());
        titleList.add(getTitleTwo());
        // 设置表头合并
        cellRangeAddressList.add(addMergeThree());
        cellRangeAddressList.add(addMergeFour());
        try {
            ExcelExportUtil.exportExcelMulHeader(tableName, titleList, contentList,cellRangeAddressList,response);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private List<List<String>> getTitleOne(){
        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<List<String>> getTitleTwo(){
        List<List<String>> titleList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList("统计","","","","");
        List<String> titleTwo = Lists.newArrayList("日期","综合","","","");
        List<String> titleThree = Lists.newArrayList("","数值","","比率","");
        List<String> titleFour = Lists.newArrayList("","订单总量","异常量","及时率","成功率");
        titleList.add(titleOne);
        titleList.add(titleTwo);
        titleList.add(titleThree);
        titleList.add(titleFour);
        return titleList;
    }

    private List<CellRangeAddress> addMergeThree() {
        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<CellRangeAddress> addMergeFour() {
        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(2, 2, 1, 2));
        cellRangeAddressList.add(new CellRangeAddress(2, 2, 3, 4));
        cellRangeAddressList.add(new CellRangeAddress(1, 3, 0, 0));
        return cellRangeAddressList;
    }


    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>> 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());
    }

}

导出工具类：ExcelExportUtil

@Component
public class ExcelExportUtil {
    private static Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtil.class);

    public static void exportSingleSheetMulHeader(String fileName, List<List<String>> titles, List<List<String>> result,List<CellRangeAddress> cellRangeAddressList,
                                                       HttpServletResponse response) {
        List<List<List<String>>> titleList = Lists.newArrayList();
        List<List<List<String>>> contentList = Lists.newArrayList();
        List<List<CellRangeAddress>> rangeList = Lists.newArrayList();
        titleList.add(titles);
        contentList.add(result);
        rangeList.add(cellRangeAddressList);
        exportExcelMulHeader(fileName, titleList, contentList,rangeList, response);
    }

    public static void exportExcelMulHeader(String fileName, List<List<List<String>>> titleList, List<List<List<String>>> result,List<List<CellRangeAddress>> cellRangeAddressList,
                                            HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            new PoiExcelExporter().exportMulHeader(response, fileName, titleList, result,cellRangeAddressList);
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
    }


    public static void exportExcel(String fileName, String[] titles, List<List<String>> result,
                                         HttpServletResponse response) {
        List<String[]> titleList = Lists.newArrayList();
        List<List<List<String>>> contentList = Lists.newArrayList();
        titleList.add(titles);
        contentList.add(result);
        exportExcel(fileName, titleList, contentList, response);
    }

    public static void exportExcel(String fileName, List<String[]> titleList, List<List<List<String>>> result,
            HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            new PoiExcelExporter().export(response, fileName, changeStrArrToList(titleList), result);
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
    }

    private static List<List<String>> changeStrArrToList(List<String[]> titleList){
        return ListUtils.emptyIfNull(titleList).stream().map(Arrays::asList).collect(Collectors.toList());
    }

}

这边处理单个的时候，就没办法用同一个方法名了，内容都是List<T>类型，被视为同一类型，单个和多个方法命名就得不一样了。不能像 exportExcel一样

导出类：PoiExcelExporter

public class PoiExcelExporter {

    PoiExcelExporter() {}

    /*多个sheet页 单表头*/
    public void export(HttpServletResponse response, String fileName, List<List<String>> titleList,
                       List<List<List<String>>> contentList) {
        HSSFSheet sheet;
        try (HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {
            setResponse(response, fileName);
            int sheetNum = titleList.size();
            for (int i = 0; i < sheetNum; i++) {
                sheet = workbook.createSheet("Sheet" + (i + 1));
                int rowNum = 0;
                createHeader(workbook, sheet, titleList.get(i), rowNum);
                rowNum++;
                createContent(sheet, contentList.get(i), rowNum);
            }
            workbook.write(output);
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*多个sheet页  复杂表头*/
    public void exportMulHeader(HttpServletResponse response, String fileName, List<List<List<String>>> titleList,
                       List<List<List<String>>> contentList,List<List<CellRangeAddress>> cellRangeAddressList) {
        HSSFSheet sheet;
        try (HSSFWorkbook workbook = new HSSFWorkbook(); OutputStream output = response.getOutputStream()) {
            setResponse(response, fileName);
            int sheetNum = titleList.size();
            for (int i = 0; i < sheetNum; i++) {
                sheet = workbook.createSheet("Sheet" + (i + 1));
                int rowNum = 0;
                // 添加完表头后，返回对应的行数
                rowNum = createMulHeader(workbook, sheet, titleList.get(i), rowNum);
                mergeCellRange(sheet,cellRangeAddressList.get(i));
                createContent(sheet, contentList.get(i), rowNum);
            }
            workbook.write(output);
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private int createMulHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<List<String>> titles, int rowNum) {
        setHeaderStyle(workbook);
        for (List<String> title : ListUtils.emptyIfNull(titles)) {
            setSheetContent(sheet, title, rowNum);
            rowNum++;
            System.out.println("title rowNum " + rowNum);
        }
        return rowNum;
    }

    private void mergeCellRange(HSSFSheet sheet, List<CellRangeAddress> cellRangeAddressList){
        //处理 cellRangeAddressList 合并内容
        if(CollectionUtils.isNotEmpty(cellRangeAddressList)){
            cellRangeAddressList.forEach(sheet::addMergedRegion);
        }
    }

    private void createHeader(HSSFWorkbook workbook, HSSFSheet sheet, List<String> titles, int rowNum) {
        setHeaderStyle(workbook);
        setSheetContent(sheet, titles, rowNum);
    }


    private static void setSheetContent(HSSFSheet sheet, List<String> contentList, int rownum) {
        HSSFRow row = sheet.createRow(rownum);
        AtomicInteger i = new AtomicInteger();
        for (String title : ListUtils.emptyIfNull(contentList)) {
            HSSFCell cell = row.createCell(i.getAndIncrement());
            cell.setCellValue(title);
        }
    }

    private void createContent(HSSFSheet sheet, List<List<String>> content, int rownum) throws Exception {
        for (List<String> lineData : content) {
            setSheetContent(sheet,lineData,rownum);
            rownum++;
        }
    }

    private void setResponse(HttpServletResponse response, String fileName) {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", getResponseHeader(fileName));
    }

    private String getResponseHeader(String fileName) {
        return "attachment; filename=" + gbToUtf8(fileName) + ".xls";
    }

    private static String gbToUtf8(String src) {
        byte[] b = src.getBytes();
        char[] c = new char[b.length];
        for (int x = 0; x < b.length; x++) {
            c[x] = (char) (b[x] & 0x00FF);
        }
        return new String(c);
    }

    private static void setHeaderStyle(HSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();//设置样式
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);//设置字体大小
        font.setBold(true);//字体加粗
        style.setFont(font);//设置的字体
        style.setBorderTop(BorderStyle.DASHED);//上边框
        style.setBorderBottom(BorderStyle.DASHED); //下边框
        style.setBorderBottom(BorderStyle.DASHED);//左边框
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());//右边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());//上边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //下边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); //左边框颜色
        style.setBorderBottom(BorderStyle.DASHED);//右边框
        style.setAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐
        style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐
    }

}

结合了单表头，多表头，单个sheet,多个sheet的内容，内容比较完整了。

测试用postman:

结果：

结果和目标保持一致，任务完成。

总结：

导出带复杂表头，多个sheet的内容时候，除了如何拼接复杂的表头。先导出表题的时候，要注意行号，处理完表头后返回对应的行数，当然也可以直接获取表头的行数，直接赋值。简单表头是因为表头就一行，而复杂表头有多行。

天狼1222

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
1
评论
Poi 导出带复杂表头多个sheet的内容

Poi 导出带复杂表头多个sheet的内容目录Poi 导出带复杂表头多个sheet的内容代码：ContrllerService:导出工具类：ExcelExportUtil导出类：PoiExcelExporter测试用postman:结果：总结：知道了如何导出单个sheet复杂表头的内容？如何导出多个的呢？结和导出简单表头多个sheet的内容，整理下内容。目标：直接上代码：代码：Contrller@RestContr...
复制链接

扫一扫