<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>easyExcel</artifactId>
<name>easyExcel</name>
<version>1.0</version>
<groupId>chenwc</groupId>
<packaging>jar</packaging>
<properties>
<java.version>1.8</java.version>
<maven.compiler.plugin.version>3.6.1</maven.compiler.plugin.version>
<slf4j.version>1.7.36</slf4j.version>
<log4j.version>1.2.17</log4j.version>
<commons-io.version>2.11.0</commons-io.version>
<easyexcel.version>3.1.3</easyexcel.version>
</properties>
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
<exclusions>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>${commons-io.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-reload4j</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
<type>pom</type>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>${maven.compiler.plugin.version}</version>
</plugin>
</plugins>
</build>
</project>
import java.util.*;
/**
* 表格数据实体类
*/
public class SheetData {
/**
* 表头
*/
private List<String> header = new ArrayList<>();
/**
* 表格数据
*/
private List<List<String>> datas = new ArrayList<>();
/**
* 转换为 List<Map<String, String>> 的表格数据 datas
*/
private List<Map<String, String>> dataMapList = new ArrayList<>();
/**
* 转为excel表头
* @return excel表头
*/
public List<List<String>> toExcelHeader(){
List<List<String>> headList = new ArrayList<>();
for(String row : header){
List<String> h = new ArrayList<>();
h.add(row);
headList.add(h);
}
return headList;
}
/**
* 把 List<Map<String, String>> 转换为可以写入excel的List<List<String>>数据
* @return List<List<String>>
*/
public List<List<String>> toExcelData(){
List<List<String>> list = new ArrayList<>();
for (Map<String, String> dataMap : this.dataMapList) {
List<String> data = new ArrayList<>();
for (String s : this.header) {
data.add(dataMap.get(s));
}
list.add(data);
}
return list;
}
/**
* 获取转换为 List<Map<String, String>> 的表格数据
* @return 表格数据
*/
public List<Map<String, String>> getDataMapList() {
for (List<String> list : this.datas) {
Map<String, String> row = new HashMap<>();
for (int i = 0; i < this.header.size(); i++) {
row.put(this.header.get(i), list.get(i));
}
this.dataMapList.add(row);
}
return dataMapList;
}
public void setDataMapList(List<Map<String, String>> dataMapList) {
this.dataMapList = dataMapList;
}
public List<String> getHeader() {
return header;
}
public void setHeader(List<String> header) {
this.header = header;
}
public List<List<String>> getDatas() {
return datas;
}
public void setDatas(List<List<String>> datas) {
this.datas = datas;
}
}
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.util.StringUtils;
import com.chenwc.easyexcel.entity.SheetData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 读取监听器
*/
public class ArrayDataListener extends AnalysisEventListener<Map<Integer, String>> {
private SheetData data = new SheetData();
private static final Logger log = LoggerFactory.getLogger(ArrayDataListener.class);
/**
* 读取表头
*
* @param headMap 表头map
* @param context 上下文1
*/
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
super.invokeHead(headMap, context);
for (Integer key : headMap.keySet()) {
ReadCellData<?> cellData = headMap.get(key);
String value = cellData.getStringValue();
if (StringUtils.isEmpty(value)) {
value = "";
}
data.getHeader().add(value);
}
log.info("读取到的表头为: {}", data.getHeader());
}
/**
* 读取行数据
*
* @param dataMap 数据map
* @param analysisContext 上下文
*/
@Override
public void invoke(Map<Integer, String> dataMap, AnalysisContext analysisContext) {
List<String> line = new ArrayList<>();
for (Integer key : dataMap.keySet()) {
String value = dataMap.get(key);
if (StringUtils.isEmpty(value)) {
value = "";
}
line.add(value);
}
data.getDatas().add(line);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public SheetData getData() {
return data;
}
public void setData(SheetData data) {
this.data = data;
}
}
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.chenwc.easyexcel.entity.SheetData;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
import java.io.InputStream;
/**
* EasyExcel工具类
*/
public class ExcelTool {
private static final Logger log = LoggerFactory.getLogger(ExcelTool.class);
/**
* 根据流读取excel文件指定下标的sheet
* @param is 文件流
* @param sheetIndex sheet下标
* @return 表格数据
*/
public static SheetData read(InputStream is, int sheetIndex) {
ArrayDataListener arrayDataListener = new ArrayDataListener();
EasyExcel.read(is, arrayDataListener).sheet(sheetIndex).doRead();
return arrayDataListener.getData();
}
/**
* 读取 excel文件 sheet 0
* @param path 文件路径
* @return 表格数据
*/
public static SheetData read(String path) {
return read(path, 0);
}
/**
* 读取指定sheet页的excel文件
* @param path 文件路径
* @param sheetIndex sheet下标
* @return 表格数据
*/
public static SheetData read(String path, int sheetIndex){
log.info("开始读取文件...........");
long start = System.currentTimeMillis();
log.info("读取表格文件: {} 第 {} 个 sheet 页内容", path, sheetIndex);
FileInputStream fis = null;
SheetData data = null;
try {
fis = new FileInputStream(path);
data = read(fis, sheetIndex);
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if (null != fis){
fis.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
if (null != data){
log.info("读取数据行数为: {}", data.getDatas().size());
}
log.info("读取文件结束...........");
long end = System.currentTimeMillis();
log.info("读取耗时: {} ms", (end - start));
return data;
}
/**
* 往excel文件的sheet0写入数据
* @param path 文件路径
* @param data 待写入数据
*/
public static void write(String path, SheetData data) {
write(path, data, 0, null);
}
/**
* 往excel文件的sheet0写入数据
* @param path 文件路径
* @param sheetName sheet页名称
* @param data 待写入数据
*/
public static void write(String path, String sheetName, SheetData data) {
write(path, data, 0, sheetName);
}
/**
* 指定sheet下标往excel文件写入数据
* @param path 文件路径
* @param data 待写入数据
* @param sheetIndex sheet页下标
* @param sheetName sheet页名称
*/
public static void write(String path, SheetData data, int sheetIndex, String sheetName) {
log.info("开始写入文件...........");
long start = System.currentTimeMillis();
log.info("往表格文件: {} 第 {} 个 sheet 页写入内容", path, sheetIndex);
ExcelWriterBuilder excelWriter = EasyExcel.write(path);
excelWriter.head(data.toExcelHeader());
if (StringUtils.isNotEmpty(sheetName)){
excelWriter.sheet(sheetIndex, sheetName).doWrite(data.toExcelData());
}
else {
excelWriter.sheet(sheetIndex).doWrite(data.toExcelData());
}
log.info("写入文件结束...........");
long end = System.currentTimeMillis();
log.info("写入耗时: {} ms", (end - start));
}
}
import com.chenwc.easyexcel.entity.SheetData;
import com.chenwc.easyexcel.utils.ExcelTool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.*;
public class Main {
private static final Logger log = LoggerFactory.getLogger(Main.class);
public static void main(String[] args) {
//读数据
String path = "D:\\Desktop\\新建文件夹\\工作簿1.xlsx";
SheetData data = ExcelTool.read(path, 0);
List<Map<String, String>> dataMapList = data.getDataMapList();
for (Map<String, String> row : dataMapList) {
log.info("供应商ID： {}\t供应商名: {}", row.get("供应商ID"), row.get("供应商名"));
}
//写数据
String writePath = "D:\\Desktop\\新建文件夹\\测试写入Excel.xlsx";
List<String> header = new ArrayList<>();
header.add("A列");
header.add("B列");
header.add("C列");
List<Map<String, String>> writeDataMapList = new ArrayList<>();
for (int i = 0; i < 100; i++){
Map<String, String> row = new HashMap<>();
row.put("A列", String.valueOf(System.currentTimeMillis()));
row.put("B列", "aaaaaasasa" + UUID.randomUUID());
row.put("C列", String.valueOf(new Random(1)));
writeDataMapList.add(row);
}
SheetData writeData = new SheetData();
writeData.setHeader(header);
writeData.setDataMapList(writeDataMapList);
ExcelTool.write(writePath, "测试sheet", writeData);
}
}
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
public class ExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger log = LoggerFactory.getLogger(ExcelDataListener.class);
/**
* 每隔100条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
//行数
private int rows = 0;
//表头
private List<String> header = new ArrayList<String>();
//缓存的数据
private List<Map<String, String>> list = new ArrayList<Map<String, String>>();
/**
* 这里会返回表头
*
* @param headMap 表头map
* @param context 上下文
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
this.rows++;
for (Integer key : headMap.keySet()) {
this.header.add(headMap.get(key));
}
log.info("表头: {}", this.header);
}
/**
* 这个每一行数据解析都会来调用
*
* @param data 一行数据
* @param context 上下文
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
this.rows++;
Map<String, String> row = new HashMap<>();
for (int i = 0; i < this.header.size(); i++) {
String key = this.header.get(i);
row.put(key, data.get(i));
}
this.list.add(row);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (this.list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
this.list = new ArrayList<>(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context 上下文
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 存储数据
*/
private void saveData() {
log.info("待处理数据: {}", list);
log.info("保存 {} 条数据。", list.size());
}
}
package com.chenwc.easyexcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.chenwc.easyexcel.utils.ExcelDataListener;
public class Main2 {
public static void main(String[] args) {
// TODO 自动生成的方法存根
String path = "D:\\Downloads\\BA417F37CA3342AE8DBA26C0A1C7AF09.xlsx";
// 一个文件一个reader
ExcelReader excelReader = null;
try {
ExcelDataListener listener = new ExcelDataListener();
excelReader = EasyExcel.read(path, listener).build();
// 构建一个sheet 这里可以指定名字或者no
ReadSheet readSheet = EasyExcel.readSheet(0).build();
// 读取一个sheet
excelReader.read(readSheet);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
}
}
log4j.rootLogger=info,stdout log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target=System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d %-5p %-5L --- [%-5t] %-10c : %m %n
commons-codec-1.13.jar
commons-collections4-4.4.jar
commons-compress-1.19.jar
commons-csv-1.8.jar
commons-io-2.11.0.jar
commons-lang3-3.12.0.jar
commons-math3-3.6.1.jar
curvesapi-1.06.jar
easyexcel-3.1.3.jar
easyexcel-core-3.1.3.jar
easyexcel-support-3.1.3.jar
ehcache-3.9.9.jar
poi-4.1.2.jar
poi-ooxml-4.1.2.jar
poi-ooxml-schemas-4.1.2.jar
SparseBitSet-1.2.jar
xmlbeans-3.1.0.jar
log4j-1.2.17.jar
slf4j-api-1.7.36.jar
slf4j-log4j12-1.7.7.jar