POI自定义单元格导出excel

珍珠奶茶不加奶

于 2023-09-07 18:38:41 发布

阅读量114

点赞数

分类专栏： Java学习文章标签： java excel

本文链接：https://blog.csdn.net/weixin_49757953/article/details/132743505

版权

Java学习专栏收录该内容

4 篇文章 0 订阅

订阅专栏

POI自定义单元格导出excel

导出样式

导出样式

自定义颜色与合并单元格

测试数据

在这里插入图片描述

创建测试数据SQL

CREATE TABLE `test_export`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sort` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '排序',
  `goal_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '内容',
  `kr_sort` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'kr排序',
  `kr_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'kr内容',
  `cycle` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '结束时间',
  `level` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '优先级',
  `expect_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '信心指数',
  `block` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否阻塞(1正常推进、2需要帮助)',
  `last_block` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '上次复盘是否阻塞(1正常推进、2需要帮助)',
  `last_score` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '上次评分',
  `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '本次评分',
  `last_replay_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '上次复盘内容',
  `replay_content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '本次复盘内容',
  `duty_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '负责人',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test_export
-- ----------------------------
INSERT INTO `test_export` VALUES (1, '1', '目标1', '1', '目标1_KR1', '2022-08-01', '1', '5', '1', '2', '0.1', '0.5', '目标1_KR1_上次复盘内容', '目标1_KR1_本次复盘内容', '目标1_KR1_负责人');
INSERT INTO `test_export` VALUES (2, '1', '目标1', '2', '目标1_KR2', '2023-02-07', '2', '4', '1', '2', '0.2', '0.6', '目标1_KR2_上次复盘内容', '目标1_KR2_本次复盘内容', '目标1_KR2_负责人');
INSERT INTO `test_export` VALUES (3, '1', '目标1', '3', '目标1_KR3', '2023-04-13', '3', '3', '2', '2', '0.3', '0.7', '目标1_KR3_上次复盘内容', '目标1_KR3_本次复盘内容', '目标1_KR3_负责人');
INSERT INTO `test_export` VALUES (4, '1', '目标1', '4', '目标1_KR4', '2023-05-17', '4', '2', '2', '1', '0.4', '0.8', '目标1_KR4_上次复盘内容', '目标1_KR4_本次复盘内容', '目标1_KR4_负责人');
INSERT INTO `test_export` VALUES (5, '2', '目标2', '1', '目标2_KR1', '2023-06-01', '5', '1', '1', '1', '0.5', '0.9', '目标2_KR1_上次复盘内容', '目标2_KR1_本次复盘内容', '目标2_KR1_负责人');
INSERT INTO `test_export` VALUES (6, '2', '目标2', '2', '目标2_KR2', '2023-06-27', '1', '5', '1', '2', '0.6', '1', '目标2_KR2_上次复盘内容', '目标2_KR2_本次复盘内容', '目标2_KR2_负责人');
INSERT INTO `test_export` VALUES (7, '2', '目标2', '3', '目标2_KR3', '2023-06-28', '2', '4', '1', '2', '0.4', '0.7', '目标2_KR3_上次复盘内容', '目标2_KR3_本次复盘内容', '目标2_KR3_负责人');
INSERT INTO `test_export` VALUES (8, '2', '目标2', '4', '目标2_KR4', '2023-07-01', '3', '3', '2', '1', '0.6', '0.8', '目标2_KR4_上次复盘内容', '目标2_KR4_本次复盘内容', '目标2_KR4_负责人');
INSERT INTO `test_export` VALUES (9, '3', '目标3', '1', '目标3_KR1', '2023-07-31', '4', '2', '1', '1', '0.3', '0.9', '目标3_KR1_上次复盘内容', '目标3_KR1_本次复盘内容', '目标3_KR1_负责人');
INSERT INTO `test_export` VALUES (10, '3', '目标3', '2', '目标3_KR2', '2023-08-01', '5', '1', '1', '1', '0.7', '1', '目标3_KR2_上次复盘内容', '目标3_KR2_本次复盘内容', '目标3_KR2_负责人');
INSERT INTO `test_export` VALUES (11, '3', '目标3', '3', '目标3_KR3', '2023-08-01', '1', '5', '2', '1', '0.1', '0.5', '目标3_KR3_上次复盘内容', '目标3_KR3_本次复盘内容', '目标3_KR3_负责人');
INSERT INTO `test_export` VALUES (12, '4', '目标4', '1', '目标4_KR1', '2023-08-01', '2', '4', '2', '1', '0.2', '0.6', '目标4_KR1_上次复盘内容', '目标4_KR1_本次复盘内容', '目标4_KR1_负责人');
INSERT INTO `test_export` VALUES (13, '4', '目标4', '2', '目标4_KR2', '2023-08-01', '3', '3', '1', '2', '0.3', '0.7', '目标4_KR2_上次复盘内容', '目标4_KR2_本次复盘内容', '目标4_KR2_负责人');
INSERT INTO `test_export` VALUES (14, '4', '目标4', '3', '目标4_KR3', '2023-08-01', '4', '2', '1', '2', '0.4', '0.8', '目标4_KR3_上次复盘内容', '目标4_KR3_本次复盘内容', '目标4_KR3_负责人');
INSERT INTO `test_export` VALUES (15, '4', '目标4', '4', '目标4_KR4', '2023-08-01', '5', '1', '1', '1', '0.5', '0.9', '目标4_KR4_上次复盘内容', '目标4_KR4_本次复盘内容', '目标4_KR4_负责人');

实现方法

   public void exportTest(HttpServletResponse response) {

        List<OkrSubDto> list = baseMapper.getTestList();
        InputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            // 假如以中文名下载的话转码，免得文件名中文乱码
            String fileName = URLEncoder.encode("OKR统计表.xlsx", "UTF-8");
            // 设置文件下载头
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            // 设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("application/vnd.ms-excel");
            //创建Excel文件的输入流对象，读取模板
//            inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/轨道巡检检测记录.xlsx");

            inputStream = this.getClass().getClassLoader().getResourceAsStream("templates/OKR统计表.xlsx");
            //根据模板创建Excel工作簿
            assert inputStream != null;
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            //获取创建工作簿的第一页
            XSSFSheet sheet = workbook.getSheetAt(0);
            //给指定的sheet命名
            workbook.setSheetName(0, "OKR报表");
            outputStream = response.getOutputStream();
            //设置字体
            XSSFFont font = workbook.createFont();
            font.setFontName("仿宋");
            font.setFontHeightInPoints((short) 12);

            //定义统一样式
            // 垂直居中
            XSSFCellStyle styleCenterVertically = workbook.createCellStyle();
            styleCenterVertically.setFont(font);
            styleCenterVertically.setVerticalAlignment(VerticalAlignment.CENTER);
            styleCenterVertically.setAlignment(HorizontalAlignment.CENTER);
            styleCenterVertically.setWrapText(false);// 设置单元格内容是否自动换行
            styleCenterVertically.setBorderBottom(BorderStyle.THIN); //下边框
            styleCenterVertically.setBorderLeft(BorderStyle.THIN);//左边框
            styleCenterVertically.setBorderTop(BorderStyle.THIN);//上边框
            styleCenterVertically.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直左对齐
            XSSFCellStyle styleLeftVertically = workbook.createCellStyle();
            styleLeftVertically.setFont(font);
            styleLeftVertically.setVerticalAlignment(VerticalAlignment.CENTER);
            styleLeftVertically.setAlignment(HorizontalAlignment.LEFT);
            styleLeftVertically.setWrapText(false);// 设置单元格内容是否自动换行
            styleLeftVertically.setBorderBottom(BorderStyle.THIN); //下边框
            styleLeftVertically.setBorderLeft(BorderStyle.THIN);//左边框
            styleLeftVertically.setBorderTop(BorderStyle.THIN);//上边框
            styleLeftVertically.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 0.0 尚未开始
            XSSFCellStyle styleGreyPercent = workbook.createCellStyle();
            styleGreyPercent.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            styleGreyPercent.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleGreyPercent.setFont(font);
            styleGreyPercent.setVerticalAlignment(VerticalAlignment.CENTER);
            styleGreyPercent.setAlignment(HorizontalAlignment.CENTER);
            styleGreyPercent.setWrapText(false);// 设置单元格内容是否自动换行
            styleGreyPercent.setBorderBottom(BorderStyle.THIN); //下边框
            styleGreyPercent.setBorderLeft(BorderStyle.THIN);//左边框
            styleGreyPercent.setBorderTop(BorderStyle.THIN);//上边框
            styleGreyPercent.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 01-0.3 有些进展
            XSSFCellStyle styleGold = workbook.createCellStyle();
            styleGold.setFillForegroundColor(IndexedColors.GOLD.getIndex());
            styleGold.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleGold.setFont(font);
            styleGold.setVerticalAlignment(VerticalAlignment.CENTER);
            styleGold.setAlignment(HorizontalAlignment.CENTER);
            styleGold.setWrapText(false);// 设置单元格内容是否自动换行
            styleGold.setBorderBottom(BorderStyle.THIN); //下边框
            styleGold.setBorderLeft(BorderStyle.THIN);//左边框
            styleGold.setBorderTop(BorderStyle.THIN);//上边框
            styleGold.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 0.4-0.6 进展顺利
            XSSFCellStyle styleYellow = workbook.createCellStyle();
            styleYellow.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            styleYellow.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleYellow.setFont(font);
            styleYellow.setVerticalAlignment(VerticalAlignment.CENTER);
            styleYellow.setAlignment(HorizontalAlignment.CENTER);
            styleYellow.setWrapText(false);// 设置单元格内容是否自动换行
            styleYellow.setBorderBottom(BorderStyle.THIN); //下边框
            styleYellow.setBorderLeft(BorderStyle.THIN);//左边框
            styleYellow.setBorderTop(BorderStyle.THIN);//上边框
            styleYellow.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 0.7 基本完成
            XSSFCellStyle styleLime = workbook.createCellStyle();
            styleLime.setFillForegroundColor(IndexedColors.LIME.getIndex());
            styleLime.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleLime.setFont(font);
            styleLime.setVerticalAlignment(VerticalAlignment.CENTER);
            styleLime.setAlignment(HorizontalAlignment.CENTER);
            styleLime.setWrapText(false);// 设置单元格内容是否自动换行
            styleLime.setBorderBottom(BorderStyle.THIN); //下边框
            styleLime.setBorderLeft(BorderStyle.THIN);//左边框
            styleLime.setBorderTop(BorderStyle.THIN);//上边框
            styleLime.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 0.8-1.0 超量完成
            XSSFCellStyle styleGreen = workbook.createCellStyle();
            styleGreen.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            styleGreen.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleGreen.setFont(font);
            styleGreen.setVerticalAlignment(VerticalAlignment.CENTER);
            styleGreen.setAlignment(HorizontalAlignment.CENTER);
            styleGreen.setWrapText(false);// 设置单元格内容是否自动换行
            styleGreen.setBorderBottom(BorderStyle.THIN); //下边框
            styleGreen.setBorderLeft(BorderStyle.THIN);//左边框
            styleGreen.setBorderTop(BorderStyle.THIN);//上边框
            styleGreen.setBorderRight(BorderStyle.THIN);//右边框

            // 垂直居中 阻塞
            XSSFCellStyle styleRed = workbook.createCellStyle();
            styleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
            styleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleRed.setFont(font);
            styleRed.setVerticalAlignment(VerticalAlignment.CENTER);
            styleRed.setAlignment(HorizontalAlignment.CENTER);
            styleRed.setWrapText(false);// 设置单元格内容是否自动换行
            styleRed.setBorderBottom(BorderStyle.THIN); //下边框
            styleRed.setBorderLeft(BorderStyle.THIN);//左边框
            styleRed.setBorderTop(BorderStyle.THIN);//上边框
            styleRed.setBorderRight(BorderStyle.THIN);//右边框


            String oldGoalId = "";
            String goalId = "";
            int index = 0;
            //获取当前sheet最后一行数据对应的行索引
            int currentLastRowIndex = sheet.getLastRowNum();
            for (int i = 0; i < list.size(); i++) {
                OkrSubDto item = list.get(i);

                //往excel里输入值
                int newRowIndex = currentLastRowIndex + 1 + i;
                XSSFRow newRow = sheet.createRow(newRowIndex);
                //开始创建并设置该行每一单元格的信息，该行单元格的索引从 0 开始
                int cellIndex = 0;
                //创建一个单元格，设置其内的数据格式为字符串，并填充内容，其余单元格类同
                // 序号
                XSSFCell cellRow = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);

                cellRow.setCellValue(item.getSort());
                cellRow.setCellStyle(styleCenterVertically);


                // 目标内容
                XSSFCell cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                if (i != 0) {
                    oldGoalId = list.get(i - 1).getSort();
                }
                goalId = item.getSort();

                // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
                if (goalId.equals(oldGoalId)) {
                    index++;
                } else {
                    if (index != 0) {
                        cellRow = newRow.createCell(0, Cell.CELL_TYPE_STRING);
                        // 序号列
                        sheet.addMergedRegion(new CellRangeAddress(i - index, i, 0, 0));
                        cellRow.setCellValue(item.getSort());
                        cellRow.setCellStyle(styleCenterVertically);
                        // 目标内容列
                        sheet.addMergedRegion(new CellRangeAddress(i - index, i, 1, 1));
                    }
                    index = 0;
                }
                // 最后一行
                if (i == list.size() - 1) {
                    if (index != 0) {
                        if (index > 1) {
                            int last = i - index + 1;
                            cellRow = newRow.createCell(0, Cell.CELL_TYPE_STRING);
                            // 序号列
                            sheet.addMergedRegion(new CellRangeAddress(last, i + 1, 0, 0));
                            cellRow.setCellValue(item.getSort());
                            cellRow.setCellStyle(styleCenterVertically);
                            // 目标内容列
                            sheet.addMergedRegion(new CellRangeAddress(last, i + 1, 1, 1));
                        } else {
                            cellRow = newRow.createCell(0, Cell.CELL_TYPE_STRING);
                            // 序号列
                            sheet.addMergedRegion(new CellRangeAddress(i, i + index, 0, 0));
                            cellRow.setCellValue(item.getSort());
                            cellRow.setCellStyle(styleCenterVertically);
                            // 目标内容列
                            sheet.addMergedRegion(new CellRangeAddress(i, i + index, 1, 1));
                        }

                    }
                }
                cell.setCellValue(item.getGoalContent());
                cell.setCellStyle(styleLeftVertically);


                // kr排序
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getKrSort());
                cell.setCellStyle(styleCenterVertically);
                // kr内容
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getKrContent());
                cell.setCellStyle(styleLeftVertically);
                // 完成时间
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getCycle());
                cell.setCellStyle(styleLeftVertically);
                // 优先级
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getLevel());
                cell.setCellStyle(styleCenterVertically);
                // 信心指数
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getExpectType());
                cell.setCellStyle(styleCenterVertically);
                // 上次评分
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                if (ObjectUtil.isNotEmpty(item.getLastScore())) {
                    cell.setCellValue(item.getLastScore());
                    if ("-".equals(item.getLastScore())) {
                        cell.setCellStyle(styleCenterVertically);
                    } else if (Double.parseDouble(item.getLastScore()) == 0) {
                        cell.setCellStyle(styleGreyPercent); // 尚未开始
                    } else if (Double.parseDouble(item.getLastScore()) <= 0.3) {
                        cell.setCellStyle(styleGold); // 有些进展
                    } else if (Double.parseDouble(item.getLastScore()) <= 0.6) {
                        cell.setCellStyle(styleYellow); // 进展顺利
                    } else if (Double.parseDouble(item.getLastScore()) == 0.7) {
                        cell.setCellStyle(styleLime); // 基本完成
                    } else {
                        cell.setCellStyle(styleGreen); // 超量完成
                    }
                    // 判断是否为阻塞状态
                    if (ObjectUtil.isNotEmpty(item.getLastBlock()) && item.getLastBlock() == 2) {
                        cell.setCellStyle(styleRed); // 阻塞
                    }
                } else {
                    cell.setCellValue("");
                    cell.setCellStyle(styleCenterVertically);
                }
                // 本次评分
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                if (ObjectUtil.isNotEmpty(item.getScore())) {
                    cell.setCellValue(item.getScore());
                    if ("-".equals(item.getScore())) {
                        cell.setCellStyle(styleCenterVertically);
                    } else if (Double.parseDouble(item.getScore()) == 0) {
                        cell.setCellStyle(styleGreyPercent); // 尚未开始
                    } else if (Double.parseDouble(item.getScore()) <= 0.3) {
                        cell.setCellStyle(styleGold); // 有些进展
                    } else if (Double.parseDouble(item.getScore()) <= 0.6) {
                        cell.setCellStyle(styleYellow); // 进展顺利
                    } else if (Double.parseDouble(item.getScore()) == 0.7) {
                        cell.setCellStyle(styleLime); // 基本完成
                    } else {
                        cell.setCellStyle(styleGreen); // 超量完成
                    }
                    // 判断是否为阻塞状态
                    if (ObjectUtil.isNotEmpty(item.getBlock()) && item.getBlock() == 2) {
                        cell.setCellStyle(styleRed); // 阻塞
                    }
                } else {
                    cell.setCellValue("");
                    cell.setCellStyle(styleCenterVertically);
                }

                // 上次复盘内容
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getLastReplayContent());
                cell.setCellStyle(styleLeftVertically);

                // 本次复盘内容
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getReplayContent());
                cell.setCellStyle(styleLeftVertically);

                // 负责人
                cell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(item.getDutyName());
                cell.setCellStyle(styleCenterVertically);
            }
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

OkrSubDto实体类

package com.ibuild.ti.business.entity;

import lombok.*;

import java.io.Serializable;

@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor

public class OkrSubDto implements Serializable {
    private static final long serialVersionUID = 1L;

    private String id;

    //排序 目标（O）
    private String sort;

    //内容 目标（O）
    private String goalContent;

    //kr排序 关键成果（KR）
    private String krSort;

    //kr内容 关键成果（KR）
    private String krContent;

    //结束时间
    private String cycle;

    //优先级(1,p1,2,p2,3,p3,4,p4,5,p5)")
    private String level;

    //信心指数(1,0%,2,25%,3,50%,4,75%,5,100%)")
    private String expectType;

    //是否阻塞(1正常推进、2需要帮助)
    private Integer block;

    //上次复盘是否阻塞(1正常推进、2需要帮助)
    private Integer lastBlock;

    //上次评分
    private String lastScore;

    //本次评分
    private String score;

    //上次复盘内容
    private String lastReplayContent;

    //本次复盘内容
    private String replayContent;

    //负责人
    private String dutyName;

}

getTestList查询SQL

select
            id as id,
            sort as sort,
            goal_content as goalContent,
            kr_sort as krSort,
            kr_content as krContent,
            cycle as cycle,
            level as level,
            expect_type as expectType,
            block as block,
            last_block as lastBlock,
            last_score as lastScore,
            score as score,
            last_replay_content as lastReplayContent,
            replay_content as replayContent,
            duty_name as dutyName
        from
            test_export
        order by id