easyExcel的使用

最新推荐文章于 2025-01-27 11:00:21 发布

mameng1998

最新推荐文章于 2025-01-27 11:00:21 发布

阅读量1.3k

点赞数

分类专栏： java 文章标签： java 数学建模开发语言

本文链接：https://blog.youkuaiyun.com/mameng1988/article/details/131294415

版权

java 专栏收录该内容

45 篇文章

订阅专栏

该文展示了一个Java项目，利用Maven构建，集成EasyExcel库来读取和处理Excel数据。项目包含实体类、测试类和处理器，实现了Excel数据的批量读取和限制行数的解析，以及数据的进一步计算和写入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 项目结构

2 pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.jd</groupId>
    <artifactId>excel-process</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
    </dependencies>


</project>

3 代码结构

3.1 测试类

package com.jd;

import com.jd.util.ExcelUtils;
import java.io.FileNotFoundException;

public class Test {
    public static void main(String[] args) throws FileNotFoundException {
        ExcelUtils.readScheduling();
    }
}

3.2 实体类

package com.jd.entity.base;

import lombok.Data;

import java.io.Serializable;

@Data
public class ExcelParseBaseVo implements Serializable {
    /**行号*/
    private int rowIndex;
}

package com.jd.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.jd.entity.base.ExcelParseBaseVo;
import lombok.Data;

import java.io.Serializable;

@Data
public class Scheduling extends ExcelParseBaseVo implements Serializable {

    /**
     * 班次数量
     */
    @ExcelProperty(index = 0)
    private int shiftCode;

    /**
     * 小时
     */
    @ExcelProperty(index = 1)
    private int waitTime;

    /**
     * 天数
     */
    @ExcelProperty(index = 2)
    private int waitDay;

}

3.3 handler类

package com.jd.handler;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.excel.util.ListUtils;
import com.jd.entity.base.ExcelParseBaseVo;
import com.jd.util.ObjectsUtils;

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

/**
 * 分页处理结果数据
 */
public class ExcelParseLimitedHandler<DTO extends ExcelParseBaseVo> extends AnalysisEventListener<DTO> {
    /**
     * 最大的批处理跳出
     */
    private static final int MAX_COUNT = 20000;

    /**
     * 数据临时存储
     */
    private List<DTO> excelRowList = ListUtils.newArrayListWithExpectedSize(MAX_COUNT);

    /**
     * 解析结果
     */
    private ExcelParseResult<DTO> excelParseResult = new ExcelParseResult<>();

    /**
     * 当前解析excel处理的行号 跳过第一行的表头
     */
    private int currentRowIndex = 2;

    @Override
    public void invoke(DTO data, AnalysisContext context) {
        /*最多读取1000行*/
        if(currentRowIndex <= MAX_COUNT){
            /*设置行号*/
            ObjectsUtils.cast(data, ExcelParseBaseVo.class).setRowIndex(currentRowIndex++);
            excelRowList.add(data);
        }else{
            excelParseResult.setParseDtoList(excelRowList);
            throw new ExcelAnalysisStopException("超过解析行数限制，强制停止解析");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        excelParseResult.setParseDtoList(excelRowList);
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        excelParseResult.setHeadMap(headMap);
    }

    /**
     * 解析结果
     * @return
     */
    public ExcelParseResult<DTO> getExcelParseResult() {
        return excelParseResult;
    }

}

package com.jd.handler;

import com.jd.entity.base.ExcelParseBaseVo;
import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * excel解析结果
 */
@Setter
@Getter
public class ExcelParseResult<DTO extends ExcelParseBaseVo> implements Serializable {
    /**
     * 表头
     */
    private Map<Integer, String> headMap;

    /**
     * 解析结果
     */
    private List<DTO> parseDtoList;
}

3.4 工具类

package com.jd.util;


public class ObjectsUtils {
    private ObjectsUtils(){}

    /**
     * 对象转换，不能直接使用强转
     * @param obj
     * @param cls
     * @param <R>
     * @return
     */
    public static <R> R cast(Object obj, Class<R> cls) {
        if (obj == null) {
            return null;
        }
        if (cls == null) {
            throw new RuntimeException("转换对象类型不能为空 ");
        }
        if(cls.isAssignableFrom(obj.getClass())){
            return (R) obj;
        }

        throw new UnsupportedOperationException("类型转换失败");
    }
}

package com.jd.util;

import com.alibaba.excel.EasyExcel;
import com.jd.entity.base.ExcelParseBaseVo;
import com.jd.handler.ExcelParseLimitedHandler;
import com.jd.handler.ExcelParseResult;

import java.io.InputStream;

public class EasyExcelReader {
    /**
     * 解析excel，并指定处理器
     * @param inputStream excel文件输入流
     * @param <DTO> 行对应实体类 <R> 用户自定义处理结构
     */
    public static <DTO extends ExcelParseBaseVo> ExcelParseResult<DTO> read(InputStream inputStream, Class<DTO> rCls){
        ExcelParseLimitedHandler<DTO> excelParseLimitedHandler = new ExcelParseLimitedHandler<>();
        EasyExcel.read(inputStream, rCls, excelParseLimitedHandler).sheet().doRead();
        return excelParseLimitedHandler.getExcelParseResult();
    }
}

package com.jd.util;

import com.alibaba.excel.EasyExcel;
import com.jd.entity.Scheduling;
import com.jd.handler.ExcelParseResult;
import org.apache.commons.collections4.MapUtils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

public class ExcelUtils {
    /**
     * easyexcel解析excel
     * @param fileUrl
     * @return
     */
    private static List<Scheduling> parseExcel2(String fileUrl) throws FileNotFoundException {
        InputStream inputStream = new FileInputStream(fileUrl);
        if(inputStream == null){
            return null;
        }
        ExcelParseResult<Scheduling> parseResult = EasyExcelReader.read(inputStream, Scheduling.class);
        Map<Integer, String> headMap = parseResult.getHeadMap();
        if(MapUtils.isEmpty(headMap)){
            throw new RuntimeException("导入文件的表头为空");
        }

        List<Scheduling> excelRowList = parseResult.getParseDtoList();
        /*过滤空行*/
        return excelRowList;
    }

    public static void readScheduling() throws FileNotFoundException {
        String fileUrl = "D:\\mazhen3\\Documents\\JD\\ME\\data\\ee\\mazhen3\\file\\排班数据_统计时长_1.xlsx";
        List<Scheduling> list  = parseExcel2(fileUrl);
        Map<Integer, Integer> map = new HashMap<Integer, Integer>(list.size());
        for (Scheduling scheduling : list) {
            if (Objects.isNull(scheduling)) {
                continue;
            }
            int waitDay = scheduling.getWaitDay();
            int waitTime = scheduling.getWaitTime();
            int shiftCode = scheduling.getShiftCode();
            if (waitTime <= 24) {
                if (map.containsKey(waitTime)) {
                    map.put(waitTime, map.get(waitTime) + shiftCode);
                } else {
                    map.put(waitTime, shiftCode);
                }
            }
        }
        //map转list
        List<Scheduling> schedulingList = map.entrySet().stream()
                .sorted(Map.Entry.comparingByKey())
                .map(x -> {
                    Scheduling scheduling = new Scheduling();
                    scheduling.setShiftCode(x.getValue());
                    scheduling.setWaitTime(x.getKey());
                    return scheduling;
                }).collect(Collectors.toList());
        String fileUrlw = "D:\\mazhen3\\Documents\\JD\\ME\\data\\ee\\mazhen3\\file\\排班24小时统计test.xlsx";
        EasyExcel.write(fileUrlw,Scheduling.class).sheet("排班24小时统计").doWrite(schedulingList);
    }
}