XSSFWorkbook制定复杂Excel表头、解决格式失效问题

最新推荐文章于 2023-11-16 22:40:26 发布

长萌的小店

最新推荐文章于 2023-11-16 22:40:26 发布

阅读量2.6k

点赞数

文章标签： excel java 开发语言

本文链接：https://blog.csdn.net/asfarmer/article/details/128563310

版权

文章目录

一、背景说明
二、具体实现
三、结语

一、背景说明

最近公司让我导出一个复杂表头的Excel，长这样
在这里插入图片描述

遇到这种第一时间就是想到用阿里的EasyExcel用模板的方式去导出，报了java.lang.NoSuchMethodError的错误，后面发现项目以前也做过导出功能，引用的是apache的poi，我顺着包点进去一看，跟EasyExcel的jar包名一模一样，估计是引起包冲突了。
引起java.lang.NoSuchMethodError的原因

阿里	poi

其实这种情况可以用 exclusions解决，这样就不会加载标签内的包了，不过我这边项目的poi包版本太老了不适用于阿里的方法，我也不敢去改原来的版本，涉及的东西太多了，所以还是决定用poi的XSSFWorkbook
HSSFworkbook,XSSFworkbook,SXSSFworkbook区别

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.ehcache</groupId>
                    <artifactId>ehcache</artifactId>
                </exclusion>
            </exclusions>
</dependency>

二、具体实现

这里有几点很坑
①设置完颜色一定要setFillPattern，不写的话颜色填充会失效，下面设置
的是前景色，不会覆盖文字。
②颜色设置完需要设置边框，因为自带的边框会被颜色覆盖掉，像这样
在这里插入图片描述
③表格行列数是从0开始的，如果合并错了会出现内容被“吃”掉的情况

public void exportTemplateData(HttpServletResponse response) throws IOException {
        //创建HSSFWorkbook对象
        XSSFWorkbook wb = new XSSFWorkbook();
        //建立sheet对象
        XSSFSheet sheet = wb.createSheet("评分模板");
        XSSFCellStyle style = wb.createCellStyle();
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        //填充模式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //自动换行
        style.setWrapText(true);
        // 顶边栏
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        // 右边栏
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 底边栏
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 左边栏
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        
        //在sheet里创建第一行，参数为行索引,这里是0开始的
        int rowNum =0;
        XSSFRow row1 = sheet.createRow(rowNum++);
        //第一行内容，要合并的各种设置为""
        String[] firstRow={"","1","2","3","4","5","6","7","8","9","10","11","序号","SN","HW_ID","NAME","批次","班组","人员分组","质检机构"
                            ,"质检人","通话时长","录音点评","业务类型","质检成绩","1.欢迎辞(1.5%)","","","","","",""
                            ,"2.基本要求(15%)","","","","","","","","","","3.服务素质(12%)","","","4.了解及沟通技巧(20%)","","",""
                            ,"5.产品/服务知识(18%)","","6.确认顾客获取(2%)","7.异议/困难处理(18%)","","","","8.关键词使用(2%)"
                            ,"9.专业销售(10%)","","10.结尾辞(1.5%)","","","","","11.违反工序(-3.5%)","","","",""};

        //设置单元格内容
        for (int i=0;i<firstRow.length;i++){
            //创建单元格
            XSSFCell cell = row1.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(firstRow[i]);
        }


        //在sheet里创建第二行
        XSSFRow row2 = sheet.createRow(rowNum++);
        String[] secondRow={"","1.5","15","12","20","18","2","18","2","10","1.5","减分合计","","","","","","","","","","","","",""
                            ,"4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4","4"
                            ,"4","4","4","4","4","4","4","4","4","4","4","4","4","4","0.5","0.5","0.5","0.5","1.5"};
        //创建单元格并设置单元格内容
        for (int i=0;i<secondRow.length;i++){
            //创建单元格
            XSSFCell cell = row2.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(secondRow[i]);

        }

        //在sheet里创建第三行
        XSSFRow row3 = sheet.createRow(rowNum++);
        String[] thirdRow={"计分区域","","","","","","","","","","","","","","","","","","","","","","","",""
                                     ,"x","","","","","","","","","","","","","","","","","","","","","",""
                                     ,"","","","","","","","","","","","","","","","","","","","X","Z"};
        //创建单元格并设置单元格内容
        for (int i=0;i<thirdRow.length;i++){
            //创建单元格
            XSSFCell cell = row3.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(thirdRow[i]);

        }

        //合并单元格，参数依次为起始行，结束行，起始列，结束列 （从0开始）
        sheet.addMergedRegion(new CellRangeAddress(0,0,25,31));
        sheet.addMergedRegion(new CellRangeAddress(0,0,32,41));
        sheet.addMergedRegion(new CellRangeAddress(0,0,42,44));
        sheet.addMergedRegion(new CellRangeAddress(0,0,45,48));
        sheet.addMergedRegion(new CellRangeAddress(0,0,49,50));
        sheet.addMergedRegion(new CellRangeAddress(0,0,52,55));
        sheet.addMergedRegion(new CellRangeAddress(0,0,57,58));
        sheet.addMergedRegion(new CellRangeAddress(0,0,59,63));
        sheet.addMergedRegion(new CellRangeAddress(0,0,64,68));

        sheet.addMergedRegion(new CellRangeAddress(2,2,0,11));
        sheet.addMergedRegion(new CellRangeAddress(0,2,12,12));
        sheet.addMergedRegion(new CellRangeAddress(0,2,13,13));
        sheet.addMergedRegion(new CellRangeAddress(0,2,14,14));
        sheet.addMergedRegion(new CellRangeAddress(0,2,15,15));
        sheet.addMergedRegion(new CellRangeAddress(0,2,16,16));
        sheet.addMergedRegion(new CellRangeAddress(0,2,17,17));
        sheet.addMergedRegion(new CellRangeAddress(0,2,18,18));
        sheet.addMergedRegion(new CellRangeAddress(0,2,19,19));
        sheet.addMergedRegion(new CellRangeAddress(0,2,20,20));
        sheet.addMergedRegion(new CellRangeAddress(0,2,21,21));
        sheet.addMergedRegion(new CellRangeAddress(0,2,22,22));
        sheet.addMergedRegion(new CellRangeAddress(0,2,23,23));
        sheet.addMergedRegion(new CellRangeAddress(0,2,24,24));

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        //设置响应头，
        response.setHeader("Content-disposition", "attachment; filename=Student.xlsx");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }

最终结果呈现
在这里插入图片描述

在这里插入图片描述

三、结语

目前我只能给行上色，不会给部分的列上色，所以同一行颜色都是一样的。XSSFWorkbook可以根据数据库数据不同生成对应的动态表头，EasyExcel只能是模板里的静态表头。总的来说XSSFWorkbook灵活但写起来相对复杂点。静态表头的话还是EasyExcel好，几行代码就可以了。
EasyExcel官方文档