java Apache.poi导出Excel,导出合并单元格，增加样式导出

最新推荐文章于 2024-07-29 18:00:00 发布

小码哥哥哥

最新推荐文章于 2024-07-29 18:00:00 发布

阅读量557

点赞数 9

文章标签： java apache excel

本文链接：https://blog.csdn.net/qq_44430410/article/details/138303164

版权

一. 业务场景，Excel导出合并单元格，效果如下！
预计效果
二。导入所需依赖
Apache POI是一个开源的Java库，用于读取和写入Microsoft Office格式的文件，如Word文档（.docx）、Excel表格（.xlsx）和PowerPoint幻灯片（.pptx）等。要在项目中使用Apache POI，你通常需要添加相应的依赖。

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>你的版本号</version>  
</dependency>  
  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>你的版本号</version>  
</dependency>

三。解决思路
1.获取到需要导出的数据，对需要合并的数据进行分组，需要合并的数据根据开始行，到分组后的数据作为结束行，进行合并！
1.1需要则创建表头，不需要则直接在本地获取模板进行写入！

   /**
     * 创建表头信息
     */
    public void createHeader(Sheet sheet, Workbook workbook) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(50);
        for (int i = 0; i < headList.size(); i++) {
            CellStyle cellStyle = WorkbookUtil.addExcelStyle(workbook, IndexedColors.GREY_25_PERCENT.index);
            Cell cell = row.createCell(i);
            cell.setCellValue(headList.get(i));
            cell.setCellStyle(cellStyle);
            sheet.setColumnWidth(i, (headList.get(i).length() + 10) * 256);
        }
        // 自动调整列宽以适应内容 setColumnWidth(int columnIndex, int width)
        sheet.setColumnWidth(2, 40 * 256);  //每个字符*256
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(9, 30 * 256);
        sheet.autoSizeColumn(0);
    }


    /**
     * 以下是我自己的一个导入案例
     */
@Override
    public void exportExcel(YwCostTask costTask, HttpServletResponse response) {
        String year = costTask.getYear();
        try {
            List<YwCostTask> list = this.list(Condition.getLikeQueryWrapper(costTask));
            // 创建一个新的工作簿
            Workbook workbook = new XSSFWorkbook();
            //配置样式
            CellStyle cellStyle = WorkbookUtil.addCellStyle(workbook);
            Font font = WorkbookUtil.addFontStyle(workbook);
            Map<String, Map<String, List<YwCostTask>>> collect = list.stream().collect(Collectors.groupingBy(YwCostTask::getYear, Collectors.groupingBy(YwCostTask::getContractNo)));
            List<String> contractNos = collect.values().stream().flatMap(map -> map.keySet().stream()).collect(Collectors.toList()); //获取出所有的合同编号
            //通过合同编号查询其他年份的子项目
            Map<String, List<YwCostTask>> otherData = this.list(Wrappers.<YwCostTask>lambdaQuery().in(YwCostTask::getContractNo, contractNos).notIn(YwCostTask::getYear, year))
                    .stream().collect(Collectors.groupingBy(YwCostTask::getContractNo));
            for (Map.Entry<String, Map<String, List<YwCostTask>>> entry : collect.entrySet()) {
                // 创建一个新的工作表
                Sheet sheet = workbook.createSheet(entry.getKey());
                //创建表头
                createHeader(sheet, workbook);
                int i = 1;
                int serial = 1; //序号
                for (Map.Entry<String, List<YwCostTask>> listEntry : entry.getValue().entrySet()) {
                    // 创建行和单元格样式
                    List<YwCostTask> value = listEntry.getValue();
                    List<YwCostTask> taskList = otherData.get(listEntry.getKey());
                    if (ObjectUtil.isNotEmpty(taskList)) {
                        value.addAll(taskList);
                    }
                    for (int j = 0; j < value.size(); j++) {
                        YwCostTask task = value.get(j);
                        Row row = sheet.createRow(i);
                        row.createCell(0).setCellValue(serial);
                        row.createCell(1).setCellValue(task.getContractNo());
                        row.createCell(2).setCellValue(task.getProjectName());
                        row.createCell(3).setCellValue(task.getProjectOwner());
                        row.createCell(4).setCellValue(task.getTaskType());
                        row.createCell(5).setCellValue(WorkbookUtil.convertDateToStringWith(task.getSelectBookDate()));
                        row.createCell(6).setCellValue(WorkbookUtil.convertDateToStringWith(task.getContractPrintDate()));
                        row.createCell(7).setCellValue(WorkbookUtil.convertDateToStringWith(task.getContractSignDate()));
                        row.createCell(8).setCellValue(task.getIsSave().equals("1") ? "是" : "否");
                        row.createCell(9).setCellValue(task.getIntermediary());
                        row.createCell(10).setCellValue(task.getFinalCost());
                        row.createCell(11).setCellValue(WorkbookUtil.convertDateToStringWith(task.getProjectPrintDate()));
                        row.createCell(12).setCellValue(task.getAuditorName());
                        row.createCell(13).setCellValue(task.getIsReport().equals("1") ? "是" : "否");
                        row.createCell(14).setCellValue(task.getRemark());
                        row.createCell(15).setCellValue(String.valueOf(Optional.ofNullable(task.getAgencyAmount()).orElse(BigDecimal.ZERO)));
                        row.createCell(16).setCellValue(String.valueOf(Optional.ofNullable(task.getBidderAmount()).orElse(BigDecimal.ZERO)));
                        row.createCell(17).setCellValue(String.valueOf(Optional.ofNullable(task.getPayout()).orElse(BigDecimal.ZERO)));
                        row.createCell(18).setCellValue(WorkbookUtil.convertDateToStringWith(task.getPayDate()));
                        row.createCell(19).setCellValue(String.valueOf(Optional.ofNullable(task.getServiceAmount()).orElse(BigDecimal.ZERO)));
                        row.createCell(20).setCellValue(task.getRatio());
                        row.createCell(21).setCellValue(WorkbookUtil.convertDateToStringWith(task.getInterPayDate()));
                        row.createCell(22).setCellValue(WorkbookUtil.convertDateToStringWith(task.getSelectDate()));
                        row.createCell(23).setCellValue(WorkbookUtil.convertDateToStringWith(task.getValidDate()));
                        row.createCell(24).setCellValue(WorkbookUtil.convertDateToStringWith(task.getServerEndDate()));
                        row.createCell(25).setCellValue(task.getProjectScore());
                        row.createCell(26).setCellValue(task.getSupermarket());
                        row.createCell(27).setCellValue(task.getOtherRemark());
                        row.createCell(28).setCellValue(task.getSource().equals("1") ? "招投标" : "中介超市");
                        i = i + 1;
                        WorkbookUtil.addProjectNameStyle(row, font, cellStyle); //配置样式和加边框、字体
                    }
                    //判断list<T>长度大于1的就需要进行合并单元格
                    if (value.size() > 1) {
                        //处理需要合并的单元格
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 0, 0);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 1, 1);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 2, 2);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 3, 3);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 4, 4);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 5, 5);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 6, 6);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 7, 7);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 8, 8);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 15, 15);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 16, 16);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 17, 17);
                        WorkbookUtil.setMergeCell(sheet, i - value.size(), i - 1, 18, 18);
                    }
                    serial++;
                }
                sheet.autoSizeColumn(0);
            }
            //设置导出浏览器响应信息
            WorkbookUtil.createExportExcelResponse(response, workbook);
        } catch (Exception e) {
            e.printStackTrace();
        }




/**
     * 设置合并单元格
     *
     * @param sheet            sheet页对象
     * @param startRowIndex    开始行号
     * @param endRowIndex      结束行号
     * @param startColumnIndex 开始列号
     * @param endColumnIndex   结束列号
     */
    public static void setMergeCell(Sheet sheet, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {
        //合并单元格区域只有一个单元格时，不合并
        if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {
            return;
        }
        if (startRowIndex > endRowIndex) {
            return;
        }
        //添加合并单元格区域
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
        sheet.addMergedRegionUnsafe(cellRangeAddress);
    }
    

 /**
     * 自定义导出Excel，设置浏览器响应信息
     */
    public static void createExportExcelResponse(HttpServletResponse response, Workbook workBook) {
//        response.setContentType("application/vnd.ms-excel");
//        response.setCharacterEncoding("utf-8");
        // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
        OutputStream output;
        try {
            output = response.getOutputStream();
            //清空缓存
            response.reset();
            //定义浏览器响应表头，顺带定义下载名，比如students(中文名需要转义)
            String s = "" + System.currentTimeMillis();
            response.setHeader("Content-disposition", "attachment;filename=" + new String(s.getBytes(), "iso-8859-1") + ".xls");
            //定义下载的类型，标明是excel文件
            response.setContentType("application/vnd.ms-excel");
            //这时候把创建好的excel写入到输出流
            workBook.write(output);
            //养成好习惯，出门记得随手关门
            output.close();
            workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

/**
*处理时间为空导出
*/
public static String convertDateToStringWith(Date date) {
        if (date == null) {
            // 如果Date是null，返回一个表示空值的字符串
            return "";
        } else {
            // 使用Java 8的日期时间API将Date转换为LocalDateTime
            Instant instant = Instant.ofEpochMilli(date.getTime());
            LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());

            // 定义一个日期格式
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd").withLocale(Locale.getDefault());

            // 将LocalDateTime格式化为字符串
            return localDateTime.format(formatter);
        }
    }

/**
     * 设置单元格样式
     * @param workbook
     * @param color
     * @return
     */
    public static CellStyle addExcelStyle(Workbook workbook, Short color) {
        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true);
        Font font = addFontStyle(workbook);
        font.setBold(true);
        style.setFont(font);
        // 设置背景色
        style.setFillForegroundColor(color);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置垂直对齐为居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);

        return style;
    }

/**
     * 设置Excel字体样式
     * 字体：黑体  大小：10
     */
    public static Font addFontStyle(Workbook workBook) {
        Font font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        return font;
    }