EasyExcel 在实际项目中的使用

 一、生成模板,下载文件

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);
}

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值