EasyExcel 在实际项目中的使用

洛柒luo

已于 2024-01-21 16:16:14 修改

阅读量248

点赞数

分类专栏：实际项目中的文件处理文章标签： excel spring boot java

于 2023-10-20 17:44:10 首次发布

本文链接：https://blog.csdn.net/qq_50970746/article/details/131983927

版权

实际项目中的文件处理专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、生成模板，下载文件

1.固定数据生成表头

先弄一个导入数据的实体类

@Data
@ColumnWidth(20)
public class UserAccountBO {
    @ExcelProperty("学号")
    private String sid;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty(value = "性别",converter = SexConvert.class)
    private Integer sex;
    @ExcelProperty("生日")
    private String birthday;
    @ExcelProperty("备注")
    private String note;
}

@ExcelProperty：核心注解，value属性可用来设置表头名称，converter属性可以用来设置类型转换器；

@ColumnWidth：用于设置表格列的宽度；

@DateTimeFormat：用于设置日期转换格式。

在EasyExcel中，如果你想实现枚举类型到字符串的转换（比如gender属性中，0->男，1->女），需要自定义转换器，下面为自定义的SexConvert代码实现；

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

/**
 * @Desc excel性别数据转换
 **/
public class SexConvert implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "男".equals(cellData.getStringValue()) ? 1 : 2;
    }
}

2.根据动态数据生成表头

public void downLoadModel(@Valid @RequestBody DynamicDataTypeParam.Id param, HttpServletResponse response) {
    DynamicDataEntity dynamicDataEntity = dynamicDataTypeService.findById(param.getId());
    try (OutputStream outputStream = response.getOutputStream()) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(dynamicDataEntity.getName() + "导入模板"+ ".xlsx", "utf-8"); //用与转码
        response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
        // 导出的表头
        List<List<String>> nameList = new ArrayList<>(dynamicDataEntity.getDataOne().stream().map(map -> {
        List<String> name = new ArrayList<>();
        name.add(map.getName());
        return name;
        }).toList());
        dynamicDataEntity.getDataTwo().forEach(dataEntity->{
        List<String> name = new ArrayList<>();
        name.add(dataEntity.getName());
        nameList.add(name);
        });
        EasyExcel.write(outputStream)
        .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
        .head(nameList)
        .sheet(dynamicDataEntity.getName()).doWrite(Collections.EMPTY_LIST);
        //这种是读sheet页，可以用于多个sheet
        ExcelWriter excelWriter = EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
        WriteSheet sheet = EasyExcel.writerSheet("Sheet1").head(nameList).build();
    } catch (IOException e) {
        throw new RuntimeException("下载导入模板失败", e);
    }  
}

二、导入文件，读文件

1.读文件所需类MyAnalysisEventListener

可以自定义方法，把读到的数据变成想要的格式在进行处理

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.lang.reflect.Field;
import java.util.*;

public class MyAnalysisEventListener<T> extends AnalysisEventListener<T> {
    List<T> list = new ArrayList<>();
    Set<String> set = new HashSet<>();
    List<String> headerRow = new ArrayList<>();

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // 处理表头行数据
        for (int i = 0; i < headMap.size(); i++) {
            String header = headMap.get(i);
            headerRow.add(header);
        }
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        list.add(t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    public List<String> getHeaderRow(){
        return headerRow;
    }

    public List<T> getDataList(){
        //自定义方法
        List<T> resultList = new ArrayList<>();
        resultList.addAll(list);
        list.clear();
        set.clear();
        return resultList;
    }

}

2.校验表头和动态数据写入

public ApiResponse<?> importData(@RequestPart("id") String id, @RequestPart("file") MultipartFile file) {
    if (StringUtils.isSpace(id)) {
        return ApiResponse.fail("id不能为空");
    }
    //文件校验
    String filename = file.getOriginalFilename();
    if (file.isEmpty() || filename == null) {
        return ApiResponse.fail("请选择文件");
    }
    String suffix = filename.substring(filename.lastIndexOf(".") + 1);
    if (!suffix.equalsIgnoreCase("xls") && !suffix.equalsIgnoreCase("xlsx")) {
        return ApiResponse.fail("文件格式不正确");
    }
    List<DynamicDataEntity> dynamicDataEntityList = new ArrayList<>();
    try (InputStream inputStream = file.getInputStream()) {
        MyAnalysisEventListener<Object> listener = new MyAnalysisEventListener<>();
        EasyExcel.read(inputStream).sheet().registerReadListener(listener).doRead();
        DynamicDataTypeEntity dynamicDataTypeEntity = dynamicDataTypeService.findById(id);
        // 检验表头
        List<String> headerRow = listener.getHeaderRow();
        List<String> nameDataOnes = dynamicDataTypeEntity.getDataOne().stream().map(DataDataOneEntity::getName).toList();
        List<String> nameDataTwos = new ArrayList<>(dynamicDataTypeEntity.getDataTwo().stream().map(DataDataOneEntity::getName).toList());
        int i1 = nameDataTwos.size() - 1;
        nameDataTwos.remove(i1);
        List<String> nameDataOneHeadList = headerRow.subList(0, nameDataOnes.size());
        if (!nameDataOnes.equals(nameDataOneHeadList)) {
            return ApiResponse.fail("表头不匹配，请按照模板列名填写");
        }
        // DataTwo有几个 DataTwo是多个的形式
        int mulHead = (headerRow.size() - nameDataOnes.size()) / nameDataTwos.size();
        int f = nameDataOnes.size();
        int g = f;
        for (int i = 1; i <= mulHead; i++) {
            g += nameDataTwos.size();
            List<String> nameDataTwoHeadList = headerRow.subList(f, g);
            Set<String> nameDataTwoHeadSet = new HashSet<>(nameDataTwoHeadList);
            if (!nameDataTwoHeadSet.isEmpty() && nameDataTwoHeadSet.size() != 1 && !nameDataTwoHeadSet.contains(null)) {
                if (!nameDataTwos.equals(nameDataTwoHeadList)) {
                    return ApiResponse.fail("第" + i + "列不匹配，请按照模板列名填写");
                }
            }
            f = g;
        }
        // 数据处理
        List<Object> dataList = listener.getDataList();
        // 。。。。。
    } catch (IOException e) {
        return ApiResponse.fail("导入文件失败");
    }
    dynamicDataService.saveAll(dynamicDataEntityList);
    return ApiResponse.success("导入文件保存成功");
}

三、导出多个excel，导出压缩包

public void downLoadApplyTeacherData(@Valid @RequestBody DynamicDataTypeParam.Id param, HttpServletResponse response) throws UnsupportedEncodingException {
    List<DynamicDataEntity> dynamicDataEntityList = dynamicDataService.findById(param.getId());
    if (dynamicDataEntityList == null || dynamicDataEntityList.isEmpty()) {
        String errMessage = URLEncoder.encode("导出失败，没有数据！", "utf-8");
        response.setHeader("Access-Control-Expose-Headers", "X-Error-Message");
        response.setHeader("X-Error-Message", new String(errMessage.getBytes("UTF-8"), "ISO-8859-1"));
    }else {
        Set<DynamicDataEntity> dynamicDataEntitySet = new HashSet<>(dynamicDataEntityList);
        Map<DynamicDataTypeEntity, List<DynamicDataEntity>> categorizedData = dynamicDataEntitySet.stream()
                .collect(Collectors.groupingBy(DynamicDataEntity::getDynamicDataTypeEntity));
        File tempDir = new File(CommonUtils.getJarFilePath() + File.separator + "\\temp");
        tempDir.mkdirs();
        // zip导出响应处理
        response.setContentType("application/zip");
        String fileName = URLEncoder.encode("数据导出" + ".zip", "utf-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
        try (ServletOutputStream outputStream = response.getOutputStream();
             ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
            categorizedData.forEach((dynamicDataTypeEntity, dynamicDataEntitys1) -> {
                List<Object> dynamicDataEntitys = new ArrayList<>();
                // 处理数据 dynamicDataEntitys1 。。。。
                // 处理后数据dynamicDataEntitys
                try {
                    // 创建 Excel 文件，写入数据
                    File excelFile = new File(tempDir, dynamicDataTypeEntity.getName() + ".xlsx");
                    try (OutputStream outputStream1 = new FileOutputStream(excelFile)) {
                        // 使用 EasyExcel 写入数据到 outputStream
                        List<List<String>> nameList = new ArrayList<>(dynamicDataTypeEntity.getDataOne().stream().map(map -> {
                            List<String> name = new ArrayList<>();
                            name.add(map.getName());
                            return name;
                        }).toList());
                        EasyExcel.write(outputStream1)
                                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                                .head(nameList)
                                .sheet(dynamicDataTypeEntity.getName()).doWrite(dynamicDataEntitys);
                    }
                    FileInputStream fis = new FileInputStream(excelFile);
                    // 创建一个字节数组，大小等于文件的长度
                    byte[] excelData = new byte[(int) excelFile.length()];
                    // 读取文件内容到字节数组
                    fis.read(excelData);
                    fis.close();
                    ZipEntry zipEntry = new ZipEntry(dynamicDataTypeEntity.getName() + ".xlsx");
                    zipOutputStream.putNextEntry(zipEntry);
                    zipOutputStream.write(excelData);
                    zipOutputStream.closeEntry();
                } catch (IOException e) {
                    throw new RuntimeException("下载导入模板失败", e);
                }
            });
        } catch (Exception e) {
            throw new RuntimeException("下载导入模板失败", e);
        }
        // 删除临时文件
        deleteTempFiles(tempDir);
        File[] files = tempDir.listFiles();
        if (files != null) {
            for (File file : files) {
                if (file.isDirectory()) {
                    deleteTempFiles(file);
                } else {
                    file.delete();
                }
            }
        }
        tempDir.delete();
    }
}

四、导出表格需合并单元格

表头使用List<List<String>> 的类型，相当于二维数组

合并单元格使用的是POI的CellRangeAddress

合并策略有4个参数，分别是开始行，结束行，开始列，结束列。下标从0开始
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

最终效果

我只合并一行数据用了Collections.singletonList(new CellRangeAddress(0, 0, 0, dataList.get(0).size()-1)))转了一下

如果多个直接放List<CellRangeAddress>就行

List<List<String>> headList = new ArrayList<>();
headList.add(List.of(name,"序号"));
headList.add(List.of(name,"学号"));
headList.add(List.of(name,"姓名"));
headList.add(List.of(name,"总得分"));
// 数据 是动态所以用List<Object>
List<List<Object>> dataList = new ArrayList<>();
EasyExcel.write(outputStream)
         .registerWriteHandler(
       new CommonMergeStrategy(
       Collections.singletonList(new CellRangeAddress(0, 0, 0, dataList.get(0).size()-1))))
         .head(headList).sheet("比赛结果").doWrite(dataList);

CommonMergeStrategy 类

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * 公共的合并单元格类，支持自定义合并策略
 */
public class CommonMergeStrategy extends AbstractMergeStrategy {
    // 合并策略list
    private List<CellRangeAddress> cellRangeAddresssList;

    // 通过有参数构造方法，设置合并策略
    public CommonMergeStrategy(List<CellRangeAddress> list) {
        this.cellRangeAddresssList = list;
    }

    /**
     * 重写合并策略方法
     * @param sheet sheet
     * @param cell cell
     * @param head head
     * @param relativeRowIndex relativeRowIndex
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        // 将自定义合并策略假如excel
        if (CollectionUtils.isNotEmpty(cellRangeAddresssList)) {
//            if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            for (CellRangeAddress item : cellRangeAddresssList) {
                sheet.addMergedRegionUnsafe(item);
            }
//            }
        }
    }
}

五、本地写测试代码时读写数据

读

        String filePath = "C:\\Users\\AA\\Desktop\\数据.xlsx";
        MyAnalysisEventListener<Object> listener = new MyAnalysisEventListener<>();
        EasyExcel.read(filePath, listener).head(AA.class).doReadAll();
        // 数据处理
        List<Object> dataList = listener.getDataList();
        for (Object o : dataList) {}

写

 EasyExcel.write(fileTeacherPath1, DataTeacher.Teacher.class)
                .sheet("Sheet1") 
                .doWrite(dataList);

六、当写文件时，某条件不符合，不能给导出文件，返回给前端的处理方式

1.返回到响应头里

// 设置 HTTP 响应状态为 400 
// response.setStatus(HttpServletResponse.SC_BAD_REQUEST); 
String errMessage = URLEncoder.encode("导出失败，没有数据！", "utf-8");
response.setHeader("Access-Control-Expose-Headers", "X-Error-Message");
response.setHeader("X-Error-Message", new String(errMessage.getBytes("UTF-8"), "ISO-8859-1"));

2.给一个空文件

try (ServletOutputStream outputStream = response.getOutputStream();
     ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
    // zip导出响应处理
    response.setContentType("application/zip");
    String fileName = URLEncoder.encode(+"没有数据" + ".zip", "utf-8");
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
    response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
    // 创建一个 ByteArrayOutputStream 来存储空文件的内容
    ByteArrayOutputStream emptyFileContent = new ByteArrayOutputStream();
    // 创建一个空文件
    String emptyFileName = "没有数据.txt";
    emptyFileContent.write("没有数据".getBytes("UTF-8"));
    // 将空文件添加到zip中
    ZipEntry entry = new ZipEntry(emptyFileName);
    zipOutputStream.putNextEntry(entry);
    zipOutputStream.write(emptyFileContent.toByteArray());
    zipOutputStream.closeEntry();
    // 清理空文件内容
    emptyFileContent.close();
} catch (IOException e) {
    throw new RuntimeException("下载导入模板失败", e);
}