EasyExcel读取数据

接口读取

package com.qiangesoft.easyexcel.controller;

import com.qiangesoft.easyexcel.read.ReadData;
import com.qiangesoft.easyexcel.read.ExcelRead;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.util.List;

/**
 * easyExcel 控制器
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
@RestController
@RequestMapping("/easyExcel")
public class EasyExcelController {

    private static final String fileName = "demo/demo.xlsx";

    @GetMapping("/read")
    public List<ReadData> read() throws IOException {
        ExcelRead.read(fileName);
        ExcelRead.read(fileName, 0);
        ExcelRead.read(fileName, 0, 1);
        ExcelRead.readExtra(fileName);
        ExcelRead.readBuiltIn(fileName);
        ExcelRead.readAnonymous(fileName);
        ExcelRead.readAllSheet(fileName);
        ExcelRead.readManySheet(fileName);
        List<ReadData> readDataList = ExcelRead.readSync(fileName);
        return readDataList;
    }

}

读取工具类

package com.qiangesoft.easyexcel.read;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.qiangesoft.easyexcel.common.ExcelStringConverter;
import com.qiangesoft.easyexcel.util.ResourceFileUtil;
import lombok.extern.slf4j.Slf4j;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * excel读取工具类
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
@Slf4j
public class ExcelRead {

    /**
     * 读数据-自定义监听器
     *
     * @param fileName
     * @throws IOException
     */
    public static void read(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener())
                .sheet()
                .doRead();
    }

    /**
     * 读数据-自定义监听器，指定sheetNo
     *
     * @param fileName
     * @param sheetNo
     * @throws IOException
     */
    public static void read(String fileName, int sheetNo) throws IOException {
        read(fileName, sheetNo, 1);
    }

    /**
     * 读数据-自定义监听器，指定sheetNo,headRowNumber
     *
     * @param fileName
     * @param sheetNo
     * @throws IOException
     */
    public static void read(String fileName, int sheetNo, int headRowNumber) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        try (com.alibaba.excel.ExcelReader excelReader = EasyExcel.read(inputStream, ReadData.class, new ReadDataListener()).build()) {
            // 构建一个sheet
            ReadSheet readSheet = EasyExcel.readSheet(sheetNo).headRowNumber(headRowNumber).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        }
    }

    /**
     * 读数据-多个相同的sheet
     *
     * @param fileName
     * @throws IOException
     */
    public static void readAllSheet(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        // 读取全部sheet，所有sheet页数据格式相同
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener()).doReadAll();
    }

    /**
     * 读数据-多个不同的sheet
     *
     * @param fileName
     * @throws IOException
     */
    public static void readManySheet(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        try (com.alibaba.excel.ExcelReader excelReader = EasyExcel.read(inputStream).build()) {
            // 方便测试
            ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ReadData.class).registerReadListener(new ReadDataListener()).build();
            ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ReadData.class).registerReadListener(new ReadDataListener()).build();
            excelReader.read(readSheet1, readSheet2);
        }
    }

    /**
     * 读数据-额外配置
     *
     * @param fileName
     * @throws IOException
     */
    public static void readExtra(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        EasyExcel.read(inputStream, ReadData.class, new ReadDataListener())
                // 注册转换器
                .registerConverter(new ExcelStringConverter())
                // 需要读取批注 默认不读取
                .extraRead(CellExtraTypeEnum.COMMENT)
                // 需要读取超链接 默认不读取
                .extraRead(CellExtraTypeEnum.HYPERLINK)
                // 需要读取合并单元格信息 默认不读取
                .extraRead(CellExtraTypeEnum.MERGE)
                .sheet()
                .doRead();
    }

    /**
     * 读数据-同步返回
     *
     * @param fileName
     * @throws IOException
     */
    public static List<ReadData> readSync(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);
        return EasyExcel.read(inputStream)
                .head(ReadData.class)
                .sheet()
                .doReadSync();
    }

    /**
     * 读数据-内置监听器
     *
     * @param fileName
     * @throws IOException
     */
    public static void readBuiltIn(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);

        // 默认每次会读取100条数据
        EasyExcel.read(inputStream, ReadData.class, new PageReadListener<ReadData>(dataList -> {
            // todo 存储数据
            log.info("{}条数据存储数据库成功！", dataList.size());
        }, 100)).sheet().doRead();
    }

    /**
     * 读数据-匿名函数
     *
     * @param fileName
     * @throws IOException
     */
    public static void readAnonymous(String fileName) throws IOException {
        InputStream inputStream = ResourceFileUtil.getResourceByClassPathResource(fileName);

        EasyExcel.read(inputStream, ReadData.class, new ReadListener<ReadData>() {
            /**
             * 每隔100条存储数据库，然后清理list
             */
            private static final int BATCH_COUNT = 100;

            /**
             * 缓存的数据
             */
            private List<ReadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            /**
             * 每一条数据解析都会调用
             *
             * @param data
             * @param context
             */
            @Override
            public void invoke(ReadData data, AnalysisContext context) {
                cachedDataList.add(data);
                // 达到BATCH_COUNT了，需要去存储一次数据库，防止几万条数据在内存，造成OOM
                if (cachedDataList.size() >= BATCH_COUNT) {

                    this.saveData();

                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            /**
             * 所有数据解析完成调用
             *
             * @param context
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 确保最后遗留的数据也存储到数据库
                this.saveData();

                log.info("所有数据解析完成！");
            }

            /**
             * 存储数据
             */
            private void saveData() {
                log.info("{}条数据存储数据库成功！", cachedDataList.size());
            }
        }).sheet().doRead();
    }

}

读取监听器

package com.qiangesoft.easyexcel.read;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

/**
 * excel读取监听类
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
@Slf4j
public class ReadDataListener implements ReadListener<ReadData> {

    /**
     * 每隔100条存储数据库，然后清理list
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据
     */
    private List<ReadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 构造方法
     */
    public ReadDataListener() {
    }

    /**
     * 异常处理：默认为抛出异常
     *
     * @param exception
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(), excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
        }
    }

    /**
     * 解析头部
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        log.info("{}条头部解析完成！", headMap.size());
    }

    /**
     * 每一条数据解析都会调用
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(ReadData data, AnalysisContext context) {
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止几万条数据在内存，造成OOM
        if (cachedDataList.size() >= BATCH_COUNT) {

            this.saveData();

            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 确保最后遗留的数据也存储到数据库
        this.saveData();

        log.info("所有数据解析完成！");
    }

    /**
     * 存储数据
     */
    private void saveData() {
        log.info("{}条数据存储数据库成功！", cachedDataList.size());
    }
}

读取类

package com.qiangesoft.easyexcel.read;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.qiangesoft.easyexcel.common.ExcelStringConverter;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

/**
 * 读取读取类
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
@Getter
@Setter
@EqualsAndHashCode
public class ReadData {

    @ExcelProperty(index = 0, converter = ExcelStringConverter.class)
    private String string;

    @DateTimeFormat("yyyy年MM月dd日 HH时mm分ss秒")
    @ExcelProperty(index = 1)
    private Date date;

    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字")
    private Double doubleData;
}

资源文件工具类

package com.qiangesoft.easyexcel.util;

import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.util.ClassUtils;

import java.io.IOException;
import java.io.InputStream;

/**
 * 文件工具类
 *
 * @author qiangesoft
 * @date 2024-04-10
 */
public class ResourceFileUtil {

    /**
     * 项目路径
     *
     * @return
     */
    public static String getProjectPath() {
        return System.getProperty("user.dir");
    }

    /**
     * 类文件路径
     *
     * @return
     */
    public static String getClassPath() {
        return ResourceFileUtil.class.getClass().getResource("/").getPath();
    }

    /**
     * 类文件路径
     *
     * @return
     */
    public static String getClassPathByClassLoader() {
        return ClassUtils.getDefaultClassLoader().getResource("").getPath();
    }

    /**
     * 获取类路径下资源文件
     *
     * @return
     */
    public static InputStream getResourceByClassPathResource(String fileName) throws IOException {
        ClassPathResource classPathResource = new ClassPathResource(fileName);
        return classPathResource.getInputStream();
    }

    /**
     * 获取类路径下资源文件
     *
     * @return
     */
    public static InputStream getResourceByClassLoader(String fileName) {
        return ResourceFileUtil.class.getClassLoader().getResourceAsStream(fileName);
    }

    /**
     * 获取类路径下资源文件
     *
     * @return
     */
    public static InputStream getResourceByResourceLoader(String fileName) throws IOException {
        Resource resource = new DefaultResourceLoader().getResource(fileName);
        return resource.getInputStream();
    }

    public static void main(String[] args) throws IOException {
//        getResourceByResourceLoader("com/qiangesoft/easyexcel/EasyexcelApplication.class");
        System.out.println(getResourceByResourceLoader("com/qiangesoft/easyexcel/EasyexcelApplication.class"));
    }
}