【案例实战】SpringBoot整合EasyExcel实现列表导出功能

原创

互联网小阿祥

发布于 2023-05-28 21:24:00

5640

发布于 2023-05-28 21:24:00

文章被收录于专栏：小阿祥架构专栏小阿祥架构专栏

这篇文章会给大家实操一个关于列表导出成excel表格的功能，相信大家在日常工作中也会遇到列表导出的需求，看完本篇文章那么你就可以轻松的去整合列表导出的功能。

本次使用的导出工具是阿里巴巴开源的EasyExcel，关于EasyExcel我这里简单的介绍一下：

我们知道Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。

easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

ok，下面我们就开始实操整合一下EasyExcel，实现导出功能。

第一步：我们先创建Maven项目，整合成SpringBoot的项目。

加入依赖，创建测试接口，确保项目能够运行。
pom.xml加入依赖

		<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
        <relativePath/>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
        </dependency>
    </dependencies>

定义主类

/**
 * @author lixiang
 * @date 2023/5/26 16:02
 */
@SpringBootApplication
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class, args);
    }
}

创建控制器

/**
 * @author lixiang
 * @date 2023/5/26 16:00
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

    @GetMapping("/create")
    public String create(){
        String msg = "SUCCESS";
        return msg;
    }

}

测试运行

第二步：引入EasyExcel依赖，封装工具类

        <dependency>
            <groupId>com.pig4cloud.excel</groupId>
            <artifactId>excel-spring-boot-starter</artifactId>
            <version>0.5.0</version>
        </dependency>

package com.lixiang.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * 导出excel 表格 工具类
 * @author lixiang
 * @date 2023/5/26 10:18 
 */
public class EasyExcelUtil implements RowWriteHandler {

    private int mergeRowIndex;//从哪一行开始合并
    private int[] mergeColumnIndex;//excel合并的列
    private int[] signNum;//合并的唯一标识
    private int total;//总行数
    private int lastRow;
    private int firstCol;
    private int lastCol;
    private int firstRow;
    private int mergeCount = 1;

    private EasyExcelUtil(){}

    private EasyExcelUtil(int mergeRowIndex, int[] mergeColumnIndex, int[] signNum, int total) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.signNum = signNum;
        this.total = total;
    }

    /**
     * 导出excel
     * @param response
     * @param fileName 文件名称
     * @param exportList 导出数据
     * @param clazz 导出实体bean class对象
     * @param <T>
     * @throws IOException
     */
    public static <T> void createExcel(HttpServletResponse response, String fileName, List<T> exportList,
      Class<T> clazz)
      throws IOException {
        createExcel(response,fileName,exportList,clazz,null);
    }

    /**
     * 导出excel
     * @param response
     * @param fileName 文件名称
     * @param exportList 导出数据
     * @param clazz 导出实体bean class对象
     * @param cellMerge 单元格合并规则
     * @param <T>
     * @throws IOException
     */
    public static <T> void createExcel(HttpServletResponse response, String fileName, List<T> exportList,
      Class<T> clazz, CellMerge cellMerge)
      throws IOException {
        // 设置下载信息
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        //定义ExcelWriterSheetBuilder
        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel
          .write(response.getOutputStream(), clazz)
          .sheet(fileName);

        //合并单元格
        if (cellMerge != null) {
            // 从那一行开始合并
            int mergeRowIndex = 1;
            EasyExcelUtil
              excelMergeRowByRowStrategy = new EasyExcelUtil(mergeRowIndex, cellMerge.getMergeColumIndex(),
              cellMerge.getMergeRuleColumIndex(), exportList.size());
            excelWriterSheetBuilder.registerWriteHandler(excelMergeRowByRowStrategy);
        }

        //设置头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置内容格式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
          new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        //设计内容居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容自动换行
        contentWriteCellStyle.setWrapped(true);
        excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);

        //调用doWrite方法
        excelWriterSheetBuilder.doWrite(exportList);
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
      Integer relativeRowIndex, Boolean isHead) {
        //当前行
        int curRowIndex = row.getRowNum();
        //每一行的最大列数
        short lastCellNum = row.getLastCellNum();

        if (curRowIndex == 1) {
            //赋初值 第一行
            firstRow = curRowIndex;
        }
        //开始合并位置
        if (curRowIndex > mergeRowIndex && !row.getCell(0).getStringCellValue().equals("")) {
            for (int i = 0; i < lastCellNum; i++) {
                if (i == mergeColumnIndex[i]) {
                    //当前行号 当前行对象 合并的标识位
                    mergeWithPrevAnyRow(writeSheetHolder.getSheet(), curRowIndex, row, signNum);
                    break;//已经进入到合并单元格操作里面了，执行一次就行
                }
            }
        }
    }

    public void mergeWithPrevAnyRow(Sheet sheet, int curRowIndex, Row row, int[] signNum) {
        Row preRow = row.getSheet().getRow(curRowIndex - 1);
        List<String> rowDataList = new ArrayList<>();
        List<String> preDataList = new ArrayList<>();

        for (int i : signNum) {
            Object currentData =
              row.getCell(i).getCellTypeEnum() == CellType.STRING ? row.getCell(i).getStringCellValue() :
                row.getCell(i).getNumericCellValue();
            Object preData =
              preRow.getCell(i).getCellTypeEnum() == CellType.STRING ? preRow.getCell(i).getStringCellValue() :
                preRow.getCell(i).getNumericCellValue();
            rowDataList.add(String.valueOf(currentData));
            preDataList.add(String.valueOf(preData));
        }

        String rowDataStr = String.join(",", rowDataList);
        String preDataStr = String.join(",", preDataList);

        //判断是否合并单元格
        boolean curEqualsPre = rowDataStr.equals(preDataStr);
        //判断前一个和后一个相同 并且 标识位相同
        if (curEqualsPre) {
            lastRow = curRowIndex;
            mergeCount++;
        }
        //excel过程中合并
        if (!curEqualsPre && mergeCount > 1) {
            mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
            mergeCount = 1;
        }

        //excel结尾处合并
        if (mergeCount > 1 && total == curRowIndex) {
            mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
            mergeCount = 1;
        }

        if (!curEqualsPre) {
            firstRow = curRowIndex;
        }

    }

    private void mergeSheet(int firstRow, int lastRow, int[] mergeColumnIndex, Sheet sheet) {
        for (int colNum : mergeColumnIndex) {
            firstCol = colNum;
            lastCol = colNum;
            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(cellRangeAddress);
        }
    }

    /**
     * 单元格合并类
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class CellMerge {
        private int[] mergeColumIndex;
        private int[] mergeRuleColumIndex;
    }

    /**
     * 设置单元格合并规则
     * @param mergeColumIndex
     * @param mergeRuleColumIndex
     * @return
     */
    public static CellMerge setCellMerge(int[] mergeColumIndex,int[] mergeRuleColumIndex){
        EasyExcelUtil excelUtil = new EasyExcelUtil();
        CellMerge cellMerge = excelUtil.new CellMerge();
        cellMerge.setMergeColumIndex(mergeColumIndex);
        cellMerge.setMergeRuleColumIndex(mergeRuleColumIndex);
        return cellMerge;
    }
}

第三步：模拟测试数据

创建学生实体类

/**
 * @ColumnWidth(20) 这个是设置单元格长度的
 * @ExcelProperty("")  这个是设置表格头部的
 * @author lixiang
 * @date 2023/5/26 16:30
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Student {

    @ExcelProperty("年级")
    private String gradeName;

    @ExcelProperty("班级")
    private String className;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("性别")
    private String sex;

}

模拟学生数据

/**
 * @author lixiang
 * @date 2023/5/26 16:13
 */
@Service
public class StudentService {

    List<String> classNames;

    List<String> gradeNames;

    {
        classNames = new ArrayList<>();
        classNames.add("一班");
        classNames.add("二班");
        classNames.add("三班");
        gradeNames = new ArrayList<>();
        gradeNames.add("2017级");
        gradeNames.add("2018级");
        gradeNames.add("2019级");
    }

    public List<Student> getStudentData(){
        List<Student> list = new ArrayList<>();
        for (int i = 1; i < 20; i++) {
            Student student = new Student();
            student.setName("李祥"+i);
            student.setClassName(getClassName());
            student.setGradeName(getGradeName());
            student.setSex("男");
            student.setAge(18);
            list.add(student);
        }

        return list;
    }

    /**
     * 获取班级
     * @return
     */
    private String getClassName(){
        Random rand = new Random();
        return classNames.get(rand.nextInt(classNames.size()));
    }

    /**
     * 获取年级
     * @return
     */
    private String getGradeName(){
        Random rand = new Random();
        return gradeNames.get(rand.nextInt(gradeNames.size()));
    }
}

/**
 * @author lixiang
 * @date 2023/5/26 16:00
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private StudentService  studentService;

    @GetMapping("/create")
    public List<Student> create(){

        List<Student> studentData = studentService.getStudentData();

        return  studentData;
    }

}

第四步：基于这个数据我们进行导出成excel

/**
 * @author lixiang
 * @date 2023/5/26 16:00
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

    @Autowired
    private StudentService  studentService;

    @GetMapping("/create")
    public void create(HttpServletResponse response) throws Exception{

        List<Student> studentData = studentService.getStudentData();
        String fileName  =  "学生列表";
        /**
         * 第一个参数：HttpServletResponse
         * 第二个参数：文件名称
         * 第三个参数：数据集
         * 第四个参数：数据集实体class对象
         */
        EasyExcelUtil.createExcel(response,fileName,studentData, Student.class);
    }

}

Ok，那么现在我们想要合并单元格，将同年级的同班级的单元格进行合并。

    @GetMapping("/create")
    public void create(HttpServletResponse response) throws Exception{

        List<Student> studentData = studentService.getStudentData();
        String fileName  =  "学生列表";
        /**
         * 第一个参数：HttpServletResponse
         * 第二个参数：文件名称
         * 第三个参数：数据集
         * 第四个参数：数据集实体class对象
         * 第五个参数：合并单元格的规则
         *  EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
         *  第一个参数 是 我们要合并的那些列，第二个是合并的规则。
         */
        int[] meargeColl = new int[]{0,1};
        EasyExcelUtil.CellMerge cellMerge = EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
        EasyExcelUtil.createExcel(response,fileName,studentData, Student.class,cellMerge);
    }

注意合并的时候，需要将数据进行排序，确保年级，班级相同的数据都在一起。现在我们先进行数据排序。

定义中文排序工具

/**
 * @author lixiang
 * @date 2023/5/26 17:14
 */
public class ChineseNumberUtil {
    public static int chineseNumber2Int(String chineseNumber){
        int result = 0;
        int temp = 1;//存放一个单位的数字如：十万
        int count = 0;//判断是否有chArr
        char[] cnArr = new char[]{'一','二','三','四','五','六','七','八','九'};
        char[] chArr = new char[]{'十','百','千','万','亿'};
        for (int i = 0; i < chineseNumber.length(); i++) {
            boolean b = true;//判断是否是chArr
            char c = chineseNumber.charAt(i);
            for (int j = 0; j < cnArr.length; j++) {//非单位，即数字
                if (c == cnArr[j]) {
                    if(0 != count){//添加下一个单位之前，先把上一个单位值添加到结果中
                        result += temp;
                        temp = 1;
                        count = 0;
                    }
                    // 下标+1，就是对应的值
                    temp = j + 1;
                    b = false;
                    break;
                }
            }
            if(b){//单位{'十','百','千','万','亿'}
                for (int j = 0; j < chArr.length; j++) {
                    if (c == chArr[j]) {
                        switch (j) {
                            case 0:
                                temp *= 10;
                                break;
                            case 1:
                                temp *= 100;
                                break;
                            case 2:
                                temp *= 1000;
                                break;
                            case 3:
                                temp *= 10000;
                                break;
                            case 4:
                                temp *= 100000000;
                                break;
                            default:
                                break;
                        }
                        count++;
                    }
                }
            }
            if (i == chineseNumber.length() - 1) {//遍历到最后一个字符
                result += temp;
            }
        }
        return result;
    }
}

service进行排序

public List<Student> getStudentData(){
        List<Student> list = new ArrayList<>();
        for (int i = 1; i < 20; i++) {
            Student student = new Student();
            student.setName("李祥"+i);
            student.setClassName(getClassName());
            student.setGradeName(getGradeName());
            student.setSex("男");
            student.setAge(18);
            list.add(student);
        }
  			//排序逻辑
        list.sort((s1,s2)->{
            Integer flag = Integer.parseInt(s1.getGradeName().substring(0,4)) - Integer.parseInt(s2.getGradeName().substring(0,4));
            if(flag == 0){
                return Integer.compare(ChineseNumberUtil.chineseNumber2Int(s1.getClassName().substring(0,2)),
                        ChineseNumberUtil.chineseNumber2Int(s2.getClassName().substring(0,2)));
            }
            return flag;
        });
        return list;
    }

OK，我们排序完成就可以进行合并啦。

				int[] meargeColl = new int[]{0,1};
        EasyExcelUtil.CellMerge cellMerge = EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
        EasyExcelUtil.createExcel(response,fileName,studentData, Student.class,cellMerge);