【EasyExcel】 easyExcel 3.2.1 生成多sheet的xlsx文件

 

pom依赖：

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.2.1</version>
            </dependency>

 

核心util写法：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.List;

/**
 * xlsx工具类
 * @date 2025/02/05
 */
@Slf4j
public class XlsxUtil {


    public static String createMultiSheetXlsx(String fileName, List<XlsxSheetVO> multiSheetList){
        String tempFilePath = "/" + fileName + ".xlsx";

        ExcelWriter excelWriter = EasyExcel.write(tempFilePath).build();
        for (XlsxSheetVO xlsxSheetVO : multiSheetList) {
            WriteSheet writeSheet = EasyExcel.writerSheet(xlsxSheetVO.getSheetName()).head(xlsxSheetVO.getClazz()).build();
            excelWriter.write(xlsxSheetVO.getContext(), writeSheet);
        }
        // 关闭writer，释放内存
        excelWriter.finish();

        log.info("create createMultiSheetXlsx  file success, file path: {}", tempFilePath);
        return tempFilePath;
    }



    public static void deleteFile(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            if (file.isDirectory()) {
                // 如果是目录，递归删除目录下的所有文件和子目录
                deleteDirectory(file);
            } else {
                // 如果是文件，直接删除
                file.delete();
            }
            log.info("delete file success, file path: {}", filePath);
        } else {
            log.info("file not exist, file path: {}", filePath);
        }
    }

    private static void deleteDirectory(File directory) {
        File[] files = directory.listFiles();
        if (files != null) {
            for (File file : files) {
                if (file.isDirectory()) {
                    deleteDirectory(file); // 递归删除子目录
                } else {
                    file.delete(); // 删除文件
                }
            }
        }
        directory.delete(); // 删除空目录
    }
}

 

支持多个sheet的dto:

import lombok.Data;

import java.util.List;

/**
 * 支持多个sheet的xlsxSheetVO
 */
@Data
public class XlsxSheetVO {

    private String sheetName;
    private List<?> context;
    private Class<?> clazz;

}

 

入口大概这样：

List<StampInventoryReportVo> stampInventoryReportVos = new ArrayList<>();
List<StampInventorySimpleReportVo> simpleReportVos = new ArrayList<>();


XlsxSheetVO sheet1 = new XlsxSheetVO();
sheet1.setSheetName("ALL");
sheet1.setContext(stampInventoryReportVos);
sheet1.setClazz(StampInventoryReportVo.class);

XlsxSheetVO sheet2 = new XlsxSheetVO();
sheet2.setSheetName("差異數據");
sheet2.setContext(simpleReportVos);
sheet2.setClazz(StampInventorySimpleReportVo.class);

List<XlsxSheetVO> multiSheetList = Lists.newArrayList();
multiSheetList.add(sheet1);
multiSheetList.add(sheet2);

String tempXlsxPath = XlsxUtil.createMultiSheetXlsx(fileName, multiSheetList);

 

 

 

生成文件如下：