若依使用easyexcel读取解析excel文件示例

结构化自定义列数和列标题的excel数据。包含异步excel解析、多行标题合并。

外码斯迪

4218人浏览 · 2022-05-26 08:37:34

外码斯迪 · 2022-05-26 08:37:34 发布

睿洛医疗

目标：结构化自定义列数和列标题的excel数据。

1，maven 模块

	<!-- excel工具 -->
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>easyexcel</artifactId>
		<version>3.1.0</version>
		<exclusions>
			<exclusion>
				<artifactId>poi-ooxml-schemas</artifactId>
				<groupId>org.apache.poi</groupId>
			</exclusion>
		</exclusions>
	</dependency>

	<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<version>1.18.22</version>
		<scope>provided</scope>
	</dependency>

2，同步无规则读取

    /** 同步-无规则 */
    public static void simpleRead(String fileName,int head) {
        List<Map<Integer, String>> listMap =           EasyExcel.read(fileName).sheet().headRowNumber(head).doReadSync();
        for (Map<Integer, String> data : listMap) {
            log.info(JSON.toJSONString(data));
        }
    }

3，异步-匹配列名读取

public static void byNameRead(String fileName,int head) {
		EasyExcel.read(fileName, ExcelReadData.class, new ExcelReadListener()).sheet().headRowNumber(head).doRead();
	}

创建数据类

package com.ruoyi.ibs.util;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

/**
*@author    created by Jerry
*@date  2022年5月25日---上午9:46:07
*@problem
*@answer
*@action
*/

@Getter
@Setter
@EqualsAndHashCode
public class ExcelReadData {
	@ExcelProperty("工号")
	private String s0;

	@ExcelProperty("姓名")
	private String s1;

	@ExcelProperty("身份证号")
	private String s2;

	@ExcelProperty("帐号")
	private String s3;

	@ExcelProperty("部门")
	private String s4;

	@ExcelProperty("实发工资")
	private String s5;

	**
	* 强制读取第6列这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
	*/
	@ExcelProperty(index = 5)
	private Double s6;

	/**
	* 这里用string 去接日期才能格式化。我想接收年月日格式
	*/
	@ExcelProperty(index = 6)
	@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
	private String s7;

	/**
	* 我想接收百分比的数字
	*/
	@ExcelProperty(index = 7)
	@NumberFormat("#.##%")
	private String s8;
}

创建监听类

package com.ruoyi.ibs.util;

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

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

/**
*@author    created by Jerry
*@date  2022年5月25日---上午9:55:06
*@problem
*@answer
*@action
*/

@Slf4j
public class ExcelReadListener extends AnalysisEventListener<ExcelReadData>{
	
	/**
     * 每隔500条存储数据库
     */
    private static final int BATCH_COUNT = 500;
    private List<ExcelReadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

	@Override
    public void invoke(ExcelReadData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

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

4，异步-匹配合并单元格表头

/** 异步-匹配合并单元格表头 */
    public static void test(String fileName,int head) {
    	EasyExcel.read(fileName, new ExcelReadListener2()).sheet().headRowNumber(head).doRead();
    }

创建数据类

package com.ruoyi.ibs.util;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

/**
*@author    created by Jerry
*@date  2022年5月25日---上午9:46:07
*@problem
*@answer
*@action
*/

@Getter
@Setter
@EqualsAndHashCode
public class ExcelReadData {
	private String e0;
	private String e1;
	private String e2;
	private String e3;
	private String e4;
	private String e5;
	private String e6;
	private String e7;
	private String e8;
	private String e9;
	private String e10;
	private String e11;
	private String e12;
	private String e13;
	private String e14;
	private String e15;
	private String e16;
	private String e17;
	private String e18;
	private String e19;
	private String e20;
	private String e21;
	private String e22;
	private String e23;
	private String e24;
	private String e25;
	private String e26;
	private String e27;
	private String e28;
	private String e29;
	private String e30;
	private String e31;
	private String e32;
	private String e33;
	private String e34;
	private String e35;
	private String e36;
	private String e37;
	private String e38;
	private String e39;
	
}

创建监听类

package com.ruoyi.ibs.util;

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

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

/**
*@author    created by Jerry
*@date  2022年5月25日---上午9:55:06
*@problem
*@answer
*@action
*/

@Slf4j
public class ExcelReadListener2 extends AnalysisEventListener<Map<Integer, String>>{
	private Map<Integer, String> m_headMap = null;
    private static final int BATCH_COUNT = 500;
    private List<Map<Integer, String>> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
	public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
		for (int i = 0; i < headMap.size(); i++) {
			String element = headMap.get(Integer.valueOf(i));
			if( element == null && m_headMap != null )
			{
				element = m_headMap.get(Integer.valueOf(i));
				headMap.replace(i, element);
			}
		}
		super.invokeHeadMap(headMap, context);
		m_headMap = headMap;
	}
    
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
    	log.info("表头:{}", JSON.toJSONString(m_headMap));
        log.info("数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成！");
    }

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