Excel复杂表头构建

phearcy

已于 2022-07-12 08:59:56 修改

阅读量525

点赞数 1

分类专栏： easyExcel 文章标签： poi excel

于 2022-07-11 17:37:44 首次发布

本文链接：https://blog.csdn.net/pheary/article/details/125725363

版权

easyExcel 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

final String[] title2 = {"序号", "商品编码", "商品编码映射", "商品名称", "商品单位", "商品规格", "供应商", "订购总数量"};
final int[] width2 = {
        (int) ((3 + 0.72) * 256), (int) ((12.5 + 0.72) * 256),
        (int) ((11.5 + 0.72) * 256), (int) ((30 + 0.72) * 256),
        (int) ((13.5 + 0.72) * 256), (int) ((10.5 + 0.72) * 256),
        (int) ((7.5 + 0.72) * 256), (int) ((3.5 + 0.72) * 256)
};
header2.put("merge", 7);  //首行合并 可根据自己的需求设计 设计指定行合并
header2.put("alternate", 0);  //指定奇偶行变色
header2.put("title", title2);  //设置行头
header2.put("headerCell", width2); //设置指定行款
head.put("header2", header2);
列举部分代码

private void setTopHeader(List<ExportModel> list, XSSFWorkbook workbook, XSSFSheet sheet, String header) {
    Map<String, Object> map = (Map<String, Object>) head.get(header);
    final String[] headers = (String[]) map.get("title");
    int mg = (int) map.get("merge");
    XSSFCellStyle cs = workbook.createCellStyle();
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.THIN);
    cs.setBorderTop(BorderStyle.THIN);
    cs.setBorderLeft(BorderStyle.THIN);
    cs.setBorderRight(BorderStyle.THIN);

    XSSFFont font = workbook.createFont();
    font.setFontHeight(14);
    font.setBold(true);
    cs.setFont(font);
    XSSFRow row0 = sheet.createRow(0);
    XSSFCell xs = row0.createCell(0);
    xs.setCellValue(deliveryList.get(0).getDate() + "-sheet名称");
    xs.setCellStyle(cs);
    CellRangeAddress ca = new CellRangeAddress(0, 0, 0, mg + deliveist.size());
    sheet.addMergedRegion(ca);
    RegionUtil.setBorderBottom(BorderStyle.THIN, ca, sheet); // 下边框
    RegionUtil.setBorderLeft(BorderStyle.THIN, ca, sheet); // 左边框
    RegionUtil.setBorderRight(BorderStyle.THIN, ca, sheet); // 有边框
    RegionUtil.setBorderTop(BorderStyle.THIN, ca, sheet); // 上边框

    XSSFRow row1 = sheet.createRow(1);
    XSSFCellStyle cs1 = workbook.createCellStyle();
    cs1.setAlignment(HorizontalAlignment.CENTER);
    cs1.setVerticalAlignment(VerticalAlignment.CENTER);
    cs1.setBorderBottom(BorderStyle.THIN);
    cs1.setBorderTop(BorderStyle.THIN);
    cs1.setBorderLeft(BorderStyle.THIN);
    cs1.setBorderRight(BorderStyle.THIN);
    XSSFFont font1 = workbook.createFont();
    font1.setFontHeight(10);
    font1.setBold(true);
    cs1.setFont(font1);
    cs1.setWrapText(true);
    final int[] width = (int[]) map.get("headerCell");
    for (int i = 0; i < headers.length; i++) {
        sheet.setColumnWidth(i, width[i]);
        setCellValue(cs1, row1, i, headers[i]);
    }
    final XSSFColor grayXssfColor = new XSSFColor(new java.awt.Color(220, 220, 220));
    // 白色
    final XSSFColor whiteXssfColor = new XSSFColor(new java.awt.Color(255, 255, 255));
    XSSFCellStyle cs2 = workbook.createCellStyle();
    cs2.cloneStyleFrom(cs1);
    cs2.setRotation((short) 255);
    cs2.setBorderBottom(BorderStyle.THIN);
    cs2.setBorderTop(BorderStyle.THIN);
    cs2.setBorderLeft(BorderStyle.THIN);
    cs2.setBorderRight(BorderStyle.THIN);
    cs2.setFillForegroundColor(grayXssfColor);
    cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFCellStyle cs3 = workbook.createCellStyle();
    cs3.cloneStyleFrom(cs2);
    cs3.setFillForegroundColor(whiteXssfColor);
    cs3.setFillPattern(FillPatternType.NO_FILL);
    AtomicInteger ai = new AtomicInteger(headers.length);
    int alternate = (int) map.get("alternate");
    for (ExportModel x : list) {
        sheet.setColumnWidth(ai.get(), (int) ((3.0 + 0.72) * 256));
        if (ai.get() % 2 == alternate) {
            setCellValue(cs2, row1, ai.get(), x.getBName());
        } else {
            setCellValue(cs3, row1, ai.get(), x.getAName());
        }
        ai.addAndGet(1);
    }
}