使用EasyExcel导出Excel

最新推荐文章于 2025-12-05 12:17:00 发布

原创最新推荐文章于 2025-12-05 12:17:00 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #java

需求

最近项目中因多个地方需要导出Excel文件，因此整理一下项目中的实现方案。

EasyExcel

依赖配置

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

导出配置类

import lombok.Data;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * 导出配置类
 */
@Data
public class ExportConfig {
    private HttpServletResponse response;
    private String fileName;
    private String filePath;
    private String sheetName;
    private List<?> dataList;
    private Map<String, List<?>> sheetDataMap;
    private Class<?> clazz;
    private List<Object> writeHandlers;

    // 构建器模式
    public static class Builder {
        private ExportConfig config = new ExportConfig();

        public Builder response(HttpServletResponse response) {
            config.response = response;
            return this;
        }

        public Builder fileName(String fileName) {
            config.fileName = fileName;
            return this;
        }

        public Builder filePath(String filePath) {
            config.filePath = filePath;
            return this;
        }

        public Builder sheetName(String sheetName) {
            config.sheetName = sheetName;
            return this;
        }

        public Builder dataList(List<?> dataList) {
            config.dataList = dataList;
            return this;
        }

        public Builder sheetDataMap(Map<String, List<?>> sheetDataMap) {
            config.sheetDataMap = sheetDataMap;
            return this;
        }

        public Builder clazz(Class<?> clazz) {
            config.clazz = clazz;
            return this;
        }

        public Builder writeHandlers(List<Object> writeHandlers) {
            config.writeHandlers = writeHandlers;
            return this;
        }

        public ExportConfig build() {
            return config;
        }
    }
}

导出工具类

导出过程中存在Long显示为科学计数法的问题，参看代码中registerConverter(new LongStringConverter())，如果不需要可以去掉。

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.http.HttpHeaders;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;

/**
 * EasyExcel通用导出工具类
 * @version 1.0
 * @description 支持单sheet、多sheet、自定义样式等导出功能
 */
public class EasyExcelExportUtil {

    /**
     * 基础数据导出 - 单个sheet
     * @param response  HttpServletResponse
     * @param fileName  文件名（不含后缀）
     * @param sheetName sheet名称
     * @param dataList  数据列表
     * @param clazz     数据类类型
     */
    public static void exportToResponse(HttpServletResponse response,
                                        String fileName,
                                        String sheetName,
                                        List<?> dataList,
                                        Class<?> clazz) throws IOException {
        setupResponse(response, fileName);

        EasyExcel.write(response.getOutputStream(), clazz)
                .sheet(sheetName)
                .doWrite(dataList);
    }

    /**
     * 带样式的数据导出
     * @param response           HttpServletResponse
     * @param fileName           文件名
     * @param sheetName          sheet名称
     * @param dataList           数据列表
     * @param clazz              数据类类型
     * @param includeHeaderStyle 是否包含表头样式
     */
    public static void exportWithStyle(HttpServletResponse response,
                                       String fileName,
                                       String sheetName,
                                       List<?> dataList,
                                       Class<?> clazz,
                                       boolean includeHeaderStyle) throws IOException {
        setupResponse(response, fileName);

        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽
                .registerWriteHandler(includeHeaderStyle ? createDefaultStyleStrategy() : null)
                .sheet(sheetName)
                .doWrite(dataList);
    }

    /**
     * 多sheet数据导出
     * @param response     HttpServletResponse
     * @param fileName     文件名
     * @param sheetDataMap sheet数据Map（key: sheet名称, value: 数据列表）
     * @param clazz        数据类类型
     */
    public static void exportMultipleSheets(HttpServletResponse response,
                                            String fileName,
                                            Map<String, List<?>> sheetDataMap,
                                            Class<?> clazz) throws IOException {
        setupResponse(response, fileName);

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).build();

        try {
            int sheetNo = 0;
            for (Map.Entry<String, List<?>> entry : sheetDataMap.entrySet()) {
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, entry.getKey()).build();
                excelWriter.write(entry.getValue(), writeSheet);
                sheetNo++;
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 自定义表格导出（支持复杂表头）
     * @param response  HttpServletResponse
     * @param fileName  文件名
     * @param sheetName sheet名称
     * @param dataList  数据列表
     * @param head      表头数据
     */
    public static void exportWithCustomHead(HttpServletResponse response,
                                            String fileName,
                                            String sheetName,
                                            List<List<Object>> dataList,
                                            List<List<String>> head) throws IOException {
        setupResponse(response, fileName);

        EasyExcel.write(response.getOutputStream())
                .head(head)
                .sheet(sheetName)
                .doWrite(dataList);
    }

    /**
     * 导出到本地文件
     * @param filePath  文件完整路径
     * @param sheetName sheet名称
     * @param dataList  数据列表
     * @param clazz     数据类类型
     */
    public static void exportToFile(String filePath,
                                    String sheetName,
                                    List<?> dataList,
                                    Class<?> clazz) {
        EasyExcel.write(filePath, clazz)
                .sheet(sheetName)
                .doWrite(dataList);
    }

    /**
     * 高级导出 - 支持完整配置
     * @param config 导出配置
     */
    public static void advancedExport(ExportConfig config) throws IOException {
        if (config.getResponse() != null) {
            setupResponse(config.getResponse(), config.getFileName());
        }

        ExcelWriter excelWriter = null;
        try {
            // 构建ExcelWriter
            if (config.getResponse() != null) {
                excelWriter = EasyExcel.write(config.getResponse().getOutputStream(), config.getClazz()).registerConverter(new LongStringConverter()).build();
            } else {
                excelWriter = EasyExcel.write(config.getFilePath(), config.getClazz()).registerConverter(new LongStringConverter()).build();
            }

            // 注册自定义处理器
            if (config.getWriteHandlers() != null) {
                for (Object handler : config.getWriteHandlers()) {
                    excelWriter = EasyExcel.write(
                            config.getResponse() != null ?
                                    config.getResponse().getOutputStream() : null,
                            config.getClazz()
                    ).registerWriteHandler((WriteHandler) handler).build();
                }
            }

            // 处理多sheet导出
            if (config.getSheetDataMap() != null && !config.getSheetDataMap().isEmpty()) {
                int sheetNo = 0;
                for (Map.Entry<String, List<?>> entry : config.getSheetDataMap().entrySet()) {
                    WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, entry.getKey()).build();
                    excelWriter.write(entry.getValue(), writeSheet);
                    sheetNo++;
                }
            } else if (config.getDataList() != null) {
                // 单sheet导出
                WriteSheet writeSheet = EasyExcel.writerSheet(config.getSheetName()).build();
                excelWriter.write(config.getDataList(), writeSheet);
            }

        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    // 响应头设置
    private static void setupResponse(HttpServletResponse response, String fileName) throws IOException {
        String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString())
                .replaceAll("\\+", "%20");

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION,
                "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
        response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
    }

    // 创建默认样式策略
    private static HorizontalCellStyleStrategy createDefaultStyleStrategy() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

定义导出类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.util.Date;

/**
 * 用户数据导出实体
 */
@Data
public class UserExportVO {
    @ExcelProperty("用户ID")
    @ColumnWidth(15)
    private Long id;
    
    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String username;
    
    @ExcelProperty("邮箱")
    @ColumnWidth(25)
    private String email;
    
    @ExcelProperty("手机号")
    @ColumnWidth(15)
    private String phone;
    
    @ExcelProperty("创建时间")
    @ColumnWidth(20)
    private Date createTime;
    
    @ExcelProperty("状态")
    @ColumnWidth(10)
    private String status;
}

使用示例

1、基础使用

@RestController
@RequestMapping("/export")
public class ExportController {
    
    @Autowired
    private UserService userService;
    
    @GetMapping("/users")
    public void exportUsers(HttpServletResponse response) throws IOException {
        List<UserExportVO> userList = userService.getUserExportData();
        
        EasyExcelExportUtil.exportToResponse(
            response, 
            "用户列表", 
            "用户数据", 
            userList, 
            UserExportVO.class
        );
    }
    
    @GetMapping("/users-with-style")
    public void exportUsersWithStyle(HttpServletResponse response) throws IOException {
        List<UserExportVO> userList = userService.getUserExportData();
        
        EasyExcelExportUtil.exportWithStyle(
            response,
            "用户列表（带样式）",
            "用户数据",
            userList,
            UserExportVO.class,
            true
        );
    }
}

2、多Sheet导出

@GetMapping("/report")
public void exportReport(HttpServletResponse response) throws IOException {
    Map<String, List<?>> sheetData = new HashMap<>();
    sheetData.put("用户数据", userService.getUserExportData());
    sheetData.put("订单数据", orderService.getOrderExportData());
    sheetData.put("商品数据", productService.getProductExportData());
    
    EasyExcelExportUtil.exportMultipleSheets(
        response,
        "综合数据报告",
        sheetData,
        Object.class  // 使用Object.class或具体的VO类
    );
}

3、高级配置使用

@GetMapping("/advanced")
public void advancedExport(HttpServletResponse response) throws IOException {
    ExportConfig config = new ExportConfig.Builder()
        .response(response)
        .fileName("高级导出示例")
        .sheetName("数据页")
        .dataList(userService.getUserExportData())
        .clazz(UserExportVO.class)
        .build();
    
    EasyExcelExportUtil.advancedExport(config);
}