快捷導(dǎo)航

springboot之excel工具類(lèi)使用及說(shuō)明

更新時(shí)間：2026年04月07日 09:10:28 作者：enjoy囂士

本文介紹了一個(gè)批量處理Excel數(shù)據(jù)的小工具,提供導(dǎo)入導(dǎo)出功能,通過(guò)定義數(shù)據(jù)類(lèi)和處理函數(shù)即可實(shí)現(xiàn)數(shù)據(jù)處理,最后進(jìn)行模擬請(qǐng)求和文件數(shù)據(jù)生成,以測(cè)試工具的正確性

背景

后臺(tái)需要批量處理excel數(shù)據(jù)，常見(jiàn)的就是導(dǎo)入導(dǎo)出了。故自己寫(xiě)了一個(gè)小工具。

依賴(lài)

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

工具類(lèi)

package org.test.exceldemo.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Function;

/**
 * Excel 導(dǎo)入導(dǎo)出工具類(lèi)
 * 包含只讀寫(xiě)、只寫(xiě)、流式讀、流式寫(xiě)
 */
public class EasyExcelPipelineUtil {
    /**
     * ??? 同步讀取（小文件用）
     */
    public static <T> List<T> readSync(InputStream inputStream, Class<T> clazz) {
        return EasyExcel.read(inputStream)
                .head(clazz)
                .sheet()
                .doReadSync();
    }

    /**
     * ??? 分頁(yè)讀?。ㄍ扑]）
     */
    public static <T> void readByPage(InputStream inputStream,
                                      Class<T> clazz,
                                      Consumer<List<T>> consumer,
                                      int pageSize) {

        EasyExcel.read(inputStream, clazz,
                        new PageReadListener<T>(consumer, pageSize))
                .sheet()
                .doRead();
    }

    /**
     * ??? MultipartFile 分頁(yè)讀取
     */
    public static <T> void readByMultipart(MultipartFile file,
                                           Class<T> clazz,
                                           Consumer<List<T>> consumer,
                                           int pageSize) throws IOException {

        readByPage(file.getInputStream(), clazz, consumer, pageSize);
    }

    /**
     * ??? URL 分頁(yè)讀取
     */
    public static <T> void readByUrl(String url,
                                     Class<T> clazz,
                                     Consumer<List<T>> consumer,
                                     int pageSize) throws Exception {

        try (InputStream in = new URL(url).openStream()) {
            readByPage(in, clazz, consumer, pageSize);
        }
    }

    /**
     * ====================================
     * ????? 直接寫(xiě) Excel（最常用導(dǎo)出）
     * ====================================
     */
    public static <T> void write(String filePath,
                                 Class<T> clazz,
                                 List<T> data, String sheetName) {

        EasyExcel.write(filePath, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new ExcelCellWriteWidthConfig())
                .sheet(sheetName)
                .doWrite(data);
    }

    /**
     * ? 寫(xiě)到 OutputStream（瀏覽器下載用）
     */
    public static <T> void write(OutputStream outputStream,
                                 Class<T> clazz,
                                 List<T> data, String sheetName) {

        EasyExcel.write(outputStream, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new ExcelCellWriteWidthConfig())
                .sheet(sheetName)
                .doWrite(data);
    }

    /**
     * ???? 超大數(shù)據(jù)流式寫(xiě)（百萬(wàn)行推薦）
     */
    public static <T> void writeStream(String filePath,
                                       Class<T> clazz,
                                       Consumer<ExcelWriter> writerConsumer) {

        ExcelWriter writer = EasyExcel.write(filePath, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new ExcelCellWriteWidthConfig())
                .build();

        try {
            writerConsumer.accept(writer);
        } finally {
            writer.finish();
        }
    }
// 調(diào)用樣例
//    EasyExcelUtil.writeStream(
//            "D:/big.xlsx",
//    UserExportVO.class,
//    writer -> {
//
//        for (int i = 0; i < 100; i++) {
//
//            List<UserExportVO> page =
//                    userService.queryPage(i);
//
//            EasyExcelUtil.writeBatchSheet(
//                    writer,
//                    page,
//                    0,
//                    "用戶(hù)表"
//            );
//        }
//    }
//);

    /**
     * ? 分批寫(xiě) sheet
     */
    public static <T> void writeBatchSheet(ExcelWriter writer,
                                           List<T> data,
                                           int sheetNo,
                                           String sheetName) {

        WriteSheet sheet = EasyExcel.writerSheet(sheetNo, sheetName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new ExcelCellWriteWidthConfig())
                .build();
        writer.write(data, sheet);
    }

    /**
     * ??? 核心流式分頁(yè)處理（真正生產(chǎn)級(jí)）
     * ??? 不關(guān)心讀寫(xiě)，只需要處理數(shù)據(jù)即可，包含excel文件的讀寫(xiě)
     */
    private static <IN, OUT> void processStream(
            InputStream inputStream,
            OutputStream outputStream,
            Class<IN> inClass,
            Class<OUT> outClass,
            Function<List<IN>, List<OUT>> mapper,
            int pageSize) {

        ExcelWriter writer = EasyExcel.write(outputStream, outClass)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .registerWriteHandler(new ExcelCellWriteWidthConfig())
                        .build();
        WriteSheet sheet = EasyExcel.writerSheet("result").build();

        EasyExcel.read(inputStream, inClass,
                        new PageReadListener<IN>(pageData -> {
                            List<OUT> result = mapper.apply(pageData);
                            writer.write(result, sheet);
                        }, pageSize))
                .sheet()
                .doRead();

        writer.finish();
    }

    /**
     * =========================
     * ? 本地文件路徑
     * =========================
     */
    public static <IN, OUT> void processByFilePath(
            String inputFile,
            String outputFile,
            Class<IN> inClass,
            Class<OUT> outClass,
            Function<List<IN>, List<OUT>> mapper,
            int pageSize) throws Exception {

        try (InputStream in = new FileInputStream(inputFile);
             OutputStream out = new FileOutputStream(outputFile)) {

            processStream(in, out, inClass, outClass, mapper, pageSize);
        }
    }

    /**
     * =========================
     * ? URL 網(wǎng)絡(luò)文件
     * =========================
     */
    public static <IN, OUT> void processByUrl(
            String fileUrl,
            String outputFile,
            Class<IN> inClass,
            Class<OUT> outClass,
            Function<List<IN>, List<OUT>> mapper,
            int pageSize) throws Exception {

        try (InputStream in = new URL(fileUrl).openStream();
             OutputStream out = new FileOutputStream(outputFile)) {

            processStream(in, out, inClass, outClass, mapper, pageSize);
        }
    }

    /**
     * =========================
     * ? MultipartFile（Web上傳）
     * =========================
     */
    public static <IN, OUT> void processByMultipart(
            MultipartFile file,
            String outputFile,
            Class<IN> inClass,
            Class<OUT> outClass,
            Function<List<IN>, List<OUT>> mapper,
            int pageSize) throws Exception {
        try (InputStream in = file.getInputStream();
             OutputStream out = new FileOutputStream(outputFile)) {

            processStream(in, out, inClass, outClass, mapper, pageSize);
        }
    }

    /**
     * =========================
     * ? InputStream 通用入口
     * =========================
     */
    public static <IN, OUT> void processByStream(
            InputStream inputStream,
            OutputStream outputStream,
            Class<IN> inClass,
            Class<OUT> outClass,
            Function<List<IN>, List<OUT>> mapper,
            int pageSize) {

        processStream(inputStream, outputStream, inClass, outClass, mapper, pageSize);
    }

    /**
     * 自動(dòng)計(jì)算列寬
     *
     */
    private static class ExcelCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {
        private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

                Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
                // 單元格文本長(zhǎng)度大于60換行
                if (columnWidth >= 0) {
                    if (columnWidth > 60) {
                        columnWidth = 60;
                    }
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        Sheet sheet = writeSheetHolder.getSheet();
                        sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 2 * 256);
                    }
                }
            }
        }

        /**
         * 計(jì)算長(zhǎng)度
         */
        private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                CellData<?> cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    switch (type) {
                        case STRING:
                            // 換行符（數(shù)據(jù)需要提前解析好）
                            int index = cellData.getStringValue().indexOf("\n");
                            return index != -1 ?
                                    cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                        case BOOLEAN:
                            return cellData.getBooleanValue().toString().getBytes().length;
                        case NUMBER:
                            return cellData.getNumberValue().toString().getBytes().length;
                        default:
                            return -1;
                    }
                }
            }
        }
    }
}

舉例

定義exel文件數(shù)據(jù)類(lèi)，定義數(shù)據(jù)處理函數(shù)即可。

導(dǎo)入類(lèi)

package org.test.exceldemo.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 *  導(dǎo)入數(shù)據(jù)類(lèi)，對(duì)應(yīng)excel表格字段
 */
@Data
public class UserImportVO {

    @ExcelProperty("User Name")
    private String name;

    @ExcelProperty("User Age")
    private Integer age;
}

導(dǎo)出類(lèi)

package org.test.exceldemo.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * 導(dǎo)入結(jié)果類(lèi)
 */
@Data
public class UserImportResultVO {

    @ExcelProperty("User Name")
    private String name;

    @ExcelProperty("User Age")
    private Integer age;

    @ExcelProperty("Import Result")
    private String result;
}

數(shù)據(jù)處理函數(shù)類(lèi)

package org.test.exceldemo.excel;

import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 *  
 */
@Service
public class TestExcelDemoService {
    /**
     * excel數(shù)據(jù)處理類(lèi)
     * @param list excel數(shù)據(jù)
     * @param globalUnique 全局唯一
     * @return 處理結(jié)果
     */
    public List<UserImportResultVO> convertPage(List<UserImportVO> list, Set<String> globalUnique) {

        List<UserImportResultVO> result = new ArrayList<>();

        for (UserImportVO in : list) {
            UserImportResultVO out = new UserImportResultVO();
            BeanUtils.copyProperties(in, out);
            // 判斷數(shù)據(jù)重復(fù)
            if (!globalUnique.add(in.getName())){
                out.setResult("重復(fù)數(shù)據(jù)");
                result.add(out);
                continue;
            }
            // 數(shù)據(jù)處理邏輯

            // 處理結(jié)果
            out.setResult("success");
            result.add(out);
        }
        return result;
    }
}

模擬請(qǐng)求處理類(lèi)

@RestController
@RequestMapping("/")
public class TestExcelController {
    @Resource
    private TestExcelDemoService testExcelDemoService;

    @PostMapping(path = "upload", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public String uploadFile(@RequestParam("file") MultipartFile file) throws Exception {
        //通過(guò)文件后綴名或文件魔數(shù)進(jìn)行更可靠的校驗(yàn)
        String originalFilename = file.getOriginalFilename();
        // 只處理excel文件
        if (originalFilename != null && !originalFilename.toLowerCase().endsWith(".xlsx")) {
            return "僅支持 EXCEL 文件";
        }

        Set<String> globalUnique = new HashSet<>();
        EasyExcelPipelineUtil.processByMultipart(file,
                "D:/output.xlsx",
                UserImportVO.class,
                UserImportResultVO.class,  list -> testExcelDemoService.convertPage(list, globalUnique), 1000);
        return "上傳成功";
    }
}