autopoi-web 导出 excel 自定义样式（含前端示例）

以你之姓@ 2023-09-24 13:51 50阅读 0赞

#### 文章目录 ####

*   *   *   *  一、简介
             *  二、导入 autopoi-web 包
             *  三、自定义导出样式类
             *  四、定义实体类
             *  五、导出 excel 接口
             *  六、测试结果
             *  七、前端示例

##### 一、简介 #####

实现导出重复列头，自定义 excel 样式，先看最终导出效果，导出含有特殊情况二级列头重复  
![在这里插入图片描述][378ca30bfd4740e5a688f170d3d22195.png]  
导入可参考我另一篇文章：[https://yixiu.blog.csdn.net/article/details/127564203][https_yixiu.blog.csdn.net_article_details_127564203]

autopoi-web 官方文档：[http://doc.autopoi.jeecg.com/1623954][http_doc.autopoi.jeecg.com_1623954]

##### 二、导入 autopoi-web 包 #####

autopoi-web 为 jeecg-boot 开源的包，用来方便的导入导出 excel

<dependency>
                <groupId>org.jeecgframework</groupId>
                <artifactId>autopoi-web</artifactId>
                <version>1.4.3</version>
            </dependency>

##### 三、自定义导出样式类 #####

自定义 excel 导出样式类 DivCellStyleConfig

*  getHeaderStyle：设置标题样式
 *  getTitleStyle：设置列头样式

public class DivCellStyleConfig extends AbstractExcelExportStyler implements IExcelExportStyler {
        
    
        public DivCellStyleConfig(Workbook workbook) {
        
            super.createStyles(workbook);
        }
    
        /**
         * descroption: 设置标题样式
         * @author: Ye
         * @date @time 2022/10/27 16:45
         * @params: [color]
         * @return: org.apache.poi.ss.usermodel.CellStyle
         */
        @Override
        public CellStyle getHeaderStyle(short color) {
        
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 18);
            font.setBold(true);
            font.setColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());
            font.setFontName("Courier New");
            titleStyle.setFont(font);
            titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 双击单元格的背景色
            // titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            // 填充色需要结合 titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND)
            titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
            // 填充背景颜色
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            return titleStyle;
        }
    
        /**
         * descroption: 设置列头样式
         * @author: Ye
         * @date @time 2022/10/27 15:44
         * @params: [color]
         * @return: org.apache.poi.ss.usermodel.CellStyle
         */
        @Override
        public CellStyle getTitleStyle(short color) {
        
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBold(true);
            font.setColor(HSSFColor.HSSFColorPredefined.PLUM.getIndex());
            font.setFontName("宋体");
            titleStyle.setFont(font);
            titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle.setBorderRight(BorderStyle.THIN); // 右边框
            // titleStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            titleStyle.setBorderTop(BorderStyle.THIN); // 上边框
            titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
            titleStyle.setWrapText(true); // 允许换行
            titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
            // 填充背景颜色
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            return titleStyle;
        }
    
        /**
         * descroption: 设置循环行有样式的一行（来实现换行变色效果）
         * @author: Ye
         * @date @time 2022/10/27 15:48
         * @param workbook
         * @param isWarp
         * @return
         */
        @Override
        public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        
            CellStyle style = workbook.createCellStyle();
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            //设置填充前景色   LIGHT_TURQUOISE  浅绿松石
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setDataFormat(STRING_FORMAT);
            if (isWarp) {
        
                style.setWrapText(true);
            }
            return style;
        }
    
        /**
         * descroption: 这里设置循环行，没有样式的一行
         * @author: Ye
         * @date @time 2022/10/27 15:46
         * @param workbook
         * @param isWarp
         * @return
         */
        @Override
        public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        
            CellStyle style = workbook.createCellStyle();
    
            //四个边的边框
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
    
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setDataFormat(STRING_FORMAT);
            if (isWarp) {
        
                style.setWrapText(true);
            }
            return style;
        }
    }

##### 四、定义实体类 #####

最终导出的 excel 会去除 `语文成绩`, `数学成绩`, `英语成绩`, `语文排名`, `数学排名`, `英语排名` 字段的成绩和排名，但定义的实体类 @Excel 必须区别保证不重复，否则值映射不上

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student implements Serializable {
        
    
        private static final long serialVersionUID = 1L;
    
        /* 序号 */
        @Excel(name = "序号", width = 10)
        private String id;
    
        /* 姓名 */
        @Excel(name = "姓名", width = 10, orderNum = "1")
        private String name;
    
        /* 性别 */
        @Excel(name = "性别", width = 10, orderNum = "2")
        private String sex;
    
        /* 民族 */
        @Excel(name = "民族", width = 10, orderNum = "3")
        private String nation;
    
        /* 出生年月 */
        @Excel(name = "出生年月", width = 20, format = "yyyy-MM-dd", orderNum = "4")
        @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        private Date birthdate;
    
        /* 学科成绩 */
        /* 语文成绩 */
        @Excel(name = "语文成绩", groupName = "学科成绩", orderNum = "5", width = 15)
        private String chineseScore;
        /* 数学成绩 */
        @Excel(name = "数学成绩", groupName = "学科成绩", orderNum = "6", width = 15)
        private String mathScore;
        /* 英语成绩 */
        @Excel(name = "英语成绩", groupName = "学科成绩", orderNum = "7", width = 15)
        private String englishScore;
    
        /* 学科排名 */
        /* 语文排名 */
        @Excel(name = "语文排名", groupName = "学科排名", orderNum = "8", width = 15)
        private String chineseRank;
        /* 数学排名 */
        @Excel(name = "数学排名", groupName = "学科排名", orderNum = "9", width = 15)
        private String mathRank;
        /* 英语排名 */
        @Excel(name = "英语排名", groupName = "学科排名", orderNum = "10", width = 15)
        private String englishRank;
    }

##### 五、导出 excel 接口 #####

代码解释

*  如果直接使用 `Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);`当导出的 excel 行数大于1000 时，会使用 SXSSFWorkbook
 *  处理重复列头需要使用 getSheet(“xxx”).getRow(0) 但是会报空指针，必须使用 XSSFWorkbook 才可以处理重复列头
 *  如下代码，如果不使用 `SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);` 当 excel 数据量大时，会内存溢出，使用 SXSSFWorkbook 则不会 OOM

XSSFWorkbook workbook = new XSSFWorkbook();
            new ExcelExportServer().createSheet(workbook, exportParams, Student.class, list, null);
            //Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
            // 处理二级表头重复，去除语文成绩、语文排名后两个字
            Sheet sheet = sxssfWorkbook.getXSSFWorkbook().getSheet("学生信息");
            Row row = sheet.getRow(2);

excel 类型 ExcelType，包含 HSSF（.xls）, XSSF（.xlsx） 两种类型

*  HSSFWorkbook：支持 .xls 文件读写
 *  XSSFWorkbook：支持 .xlsx 文件读写
 *  SXSSFWorkbook：只能写不能读，默认内存中保留100行，超过的行将被刷新到磁盘  
    ![在这里插入图片描述][ecd1c2c7d679490089a343917a8ab4cc.png]

完整代码：导出 excel 含二级重复列头

@RestController
    @RequestMapping("/student")
    @Slf4j
    @Api(tags = "Student 管理")
    public class StudentController {
        
    
        /**
         * Description: student 导出
         * date: 2022/10/27 14:21
         *
         * @param response
         * @author: Ye
         * @return: void
         */
    
        @ApiOperation(notes = "student 导出", value = "student 导出")
        @PostMapping("/export")
        public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
        
            Student stu1 = new Student("1", "张三", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("2000-10-01"), "88", "99", "100", "3", "2", "1");
            Student stu2 = new Student("3", "李四", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("2001-10-01"), "88", "99", "100", "3", "2", "1");
            Student stu3 = new Student("5", "王五", "男", "汉", new SimpleDateFormat("yyyy-MM-dd").parse("2003-10-01"), "88", "99", "100", "3", "2", "1");
            List<Student> list = new ArrayList<>(Arrays.asList(stu1,stu2,stu3));
            ExportParams exportParams = new ExportParams();
            exportParams.setTitle("学生");
            exportParams.setSheetName("学生信息");
            //exportParams.setCreateHeadRows(true);
            exportParams.setType(ExcelType.XSSF);
            exportParams.setTitleHeight((short) 20);
            // 指定自定义 excel 样式类
            exportParams.setStyle(DivCellStyleConfig.class);
            //String[] exportFields = {"id","name","nation"}; 可以指定导出字段
            XSSFWorkbook workbook = new XSSFWorkbook();
            new ExcelExportServer().createSheet(workbook, exportParams, Student.class, list, null);
            //Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
            // 处理二级表头重复，去除语文成绩、语文排名后两个字
            Sheet sheet = sxssfWorkbook.getXSSFWorkbook().getSheet("学生信息");
            Row row = sheet.getRow(2);
            List<String> ls = Arrays.asList("语文成绩", "数学成绩", "英语成绩", "语文排名", "数学排名", "英语排名");
            for (Cell cell : row) {
        
                if (null != cell && cell.getCellType().toString().equals("STRING")) {
        
                    String stringCellValue = cell.getStringCellValue();
                    if (ls.contains(stringCellValue)) {
        
                        cell.setCellValue(stringCellValue.substring(0, stringCellValue.length() - 2));
                    }
                }
            }
            String fileName = "学生信息.xlsx";
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, String.valueOf(StandardCharsets.UTF_8)));
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            // 返回前端文件名需要添加
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            workbook.close();
        }
    }

##### 六、测试结果 #####

调用 [http://127.0.0.1:8080/student/export][http_127.0.0.1_8080_student_export] 接口 excel 导出成功  
![在这里插入图片描述][b0221fc600fe42ae98e69615d024ad46.png]  
如果仅设置 setFillBackgroundColor 颜色

@Override
        public CellStyle getHeaderStyle(short color) {
        
            CellStyle titleStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 18);
            font.setBold(true);
            font.setColor(HSSFColor.HSSFColorPredefined.ORCHID.getIndex());
            font.setFontName("Courier New");
            titleStyle.setFont(font);
            titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 双击单元格的背景色
        	titleStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.RED.getIndex());;
            return titleStyle;
        }

效果如下  
![在这里插入图片描述][49aa17a5453f45efb4e04bb3e8549a0f.png]  
双击标题可看到 setFillBackgroundColor 设置的颜色  
![在这里插入图片描述][c9e9131bda8a4f83bd7e234e029fe0f0.png]

##### 七、前端示例 #####

前端导出 excel 示例

<!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Ajax 请求</title>
    </head>
    <body>
    <p>导出 excel </p>
    <button onclick="download()">导出</button>
    
    </body>
    </html>
    <script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.1/jquery.js"></script>
    <script type="text/javascript">
    
        function download() {
          
            $.ajax({
          
                type: "POST",
                url: "http://127.0.0.1:8080/student/export",
                // 自定义 header
                headers:{
          
                    TOKEN:"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJwYXNzd29yZCI6IjEyMzQ1NiIsInVzZXJOYW1lIjoidGVzdDMiLCJleHAiOjE2NjcyOTUwMzJ9.y2JdlHNSYtbzbcVlw6IvO_k4o8UAAkyzzWQKHl6hTfN8MZeTlMvfHR1bCL-xYYgHdr7psj_Lz3HXgLvyv0WnZA"
                },
                xhrFields: {
          
                    responseType: 'blob'
                },
                success: function(data,status,xhr){
          
    				// 获取后端指定的文件名
                    var contentDisposition=decodeURI(xhr.getResponseHeader("Content-Disposition"))
                    var fileName = contentDisposition.substring(contentDisposition.indexOf("fileName=") + 9)
                    console.log(fileName)
                    const url = window.URL.createObjectURL(new Blob([data]));
                    const a = document.createElement('a');
                    a.href = url
                    a.setAttribute("download", fileName)
                    a.click()
                    window.URL.revokeObjectURL(url)
                },
                error: function (data,status,xhr) {
          
                    alert("下载失败")
                }
            })
        }
    </script>

[378ca30bfd4740e5a688f170d3d22195.png]: https://img-blog.csdnimg.cn/378ca30bfd4740e5a688f170d3d22195.png
[https_yixiu.blog.csdn.net_article_details_127564203]: https://yixiu.blog.csdn.net/article/details/127564203
[http_doc.autopoi.jeecg.com_1623954]: http://doc.autopoi.jeecg.com/1623954
[ecd1c2c7d679490089a343917a8ab4cc.png]: https://img-blog.csdnimg.cn/ecd1c2c7d679490089a343917a8ab4cc.png
[http_127.0.0.1_8080_student_export]: http://127.0.0.1:8080/student/export
[b0221fc600fe42ae98e69615d024ad46.png]: https://img-blog.csdnimg.cn/b0221fc600fe42ae98e69615d024ad46.png
[49aa17a5453f45efb4e04bb3e8549a0f.png]: https://img-blog.csdnimg.cn/49aa17a5453f45efb4e04bb3e8549a0f.png
[c9e9131bda8a4f83bd7e234e029fe0f0.png]: https://img-blog.csdnimg.cn/c9e9131bda8a4f83bd7e234e029fe0f0.png