EasyExcel遍历目录下表格+数据合并
| 文章目录 |
|---|
| 参考 - easyexcel写、追加操作 - 知乎 (zhihu.com) |
概述
需求描述
在一个文件夹内有很多子文件夹，里面有许多excel，我需要把里面的excel数据全部取出来合并到一个新的excel中，这些表格的数据格式是一样的。
我的表格数据是这个样子的
前置工作准备
导入依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.79</version>
</dependency>
向上面那位知乎博主拿一下工具类
package com.ljm.tools;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelUtil2 {
private ExcelWriter excelWriter = null;
private WriteSheet writeSheet = null;
/**
* ===========================================================
* Purpose : EasyExcel工具类 初始化(最先调用)
* @return Author : lzt
* Created Date : 2021-12-28
* Update History
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2021-12-28 lzt Creation
* ===========================================================
* @params: absFilePath 绝对路径
* @params: sheetName 标签页名字
* @params: titleList 标题头(第一行)
*/
public void init(String absFilePath, String sheetName, List<String> titleList) {
if (excelWriter == null && writeSheet == null) {
List<List<String>> heads = new ArrayList<>(1);
//表格头标题
heads.add(titleList);
// 这里 需要指定写用哪个标题头去写 可以用class 也可以不用
excelWriter = EasyExcelFactory.write(absFilePath).head(heads).build();
// 这里注意 如果同一个sheet只要创建一次
writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
}
}
/**
* ===========================================================
* Purpose : EasyExcel工具类 写入excel写内容
* @return Author : lzt
* Created Date : 2021-12-28
* Update History
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2021-12-28 lzt Creation
* ===========================================================
* @params: dataList 要插入的数据(多行插入)
*/
public void doExportExcel(List<List<String>> dataList) {
try {
excelWriter.write(dataList, writeSheet);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* ===========================================================
* Purpose : EasyExcel工具类 关闭(最后调用 关闭流)
* @return Author : lzt
* Created Date : 2021-12-28
* Update History
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2021-12-28 lzt Creation
* ===========================================================
*/
public void finish() {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
准备好实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author lijiamin
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Device {
@ExcelProperty(index = 0, value = "ElementID")
private String ElementID;
@ExcelProperty(index = 1, value = "CODE")
private String CODE;
@ExcelProperty(index = 2, value = "Version")
private String Version;
@ExcelProperty(index = 3, value = "Model")
private String Model;
@ExcelProperty(index = 4, value = "Type")
private String Type;
@ExcelProperty(index = 5, value = "FileName")
private String FileName;
@ExcelProperty(index = 6, value = "Locked")
private String Locked;
@ExcelProperty(index = 7, value = "Modified")
private String Modified;
@ExcelProperty(index = 8, value = "ModifiedTime")
private String ModifiedTime;
@ExcelProperty(index = 9, value = "New")
private String New;
@ExcelProperty(index = 10, value = "Level")
private String Level;
@ExcelProperty(index = 11, value = "Range")
private String Range;
@ExcelProperty(index = 12, value = "Description")
private String Description;
@ExcelProperty(index = 13, value = "Notes")
private String Notes;
}
第一步：获取文件夹内所有excel表格文件的路径
这一步是将所有的路径信息以字符串数据类型存入到listPath集合里
/**
* @author lijiamin
*/
public class DemoApplication {
/** xlsx文件路径 */
public static List<String> listPath = new ArrayList<String>();
public static void main(String[] args) throws FileNotFoundException {
// 文件路径
String path_dir = "C:\\Users\\SZBY-lijiamin\\Desktop\\data";
folderMethod2(path_dir);
System.out.println("目前listPath集合有数据量：" + listPath.size());
}
/**
* 查找所有xlsx后缀的文件
* @param path
*/
public static void folderMethod2(String path) {
File file = new File(path);
if (file.exists()) {
File[] files = file.listFiles();
if (null != files) {
for (File file2 : files) {
if (file2.isDirectory()) {
folderMethod2(file2.getAbsolutePath());
} else {
String file2AbsolutePath = file2.getAbsolutePath();
int length = file2AbsolutePath.length();
if (file2AbsolutePath.substring(file2AbsolutePath.lastIndexOf("."), length).equals(".xlsx")) {
listPath.add(file2AbsolutePath);
}
}
}
}
} else {
}
}
}
最终能获取到的路径是这些
第二步：将数据提取到本机内存集合中
通过上述的路径信息，将数据提取到我们的静态集合里
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ljm.entity.Device;
import com.ljm.entity.Device;
import com.ljm.tools.EasyExcelUtil2;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
/**
* @author lijiamin
*/
public class DemoApplication {
/** xlsx文件路径 */
public static List<String> listPath = new ArrayList<String>();
/** 现有的数据量 */
public static List<Device> listDeviceOver = new ArrayList<Device>();
public static void main(String[] args) throws FileNotFoundException {
// 文件路径
String path_dir = "C:\\Users\\SZBY-lijiamin\\Desktop\\data";
folderMethod2(path_dir);
System.out.println("目前listPath集合有数据量：" + listPath.size());
// 插入集合
for (String path : listPath
) {
insertList(path);
}
// 检查数据
System.out.println("目前listDeviceOver集合有数据量：" + listDeviceOver.size());
}
/**
* excel拿到的数据插入内存集合
* @param path_dir
* @throws FileNotFoundException
*/
public static void insertList(String path_dir) throws FileNotFoundException {
FileInputStream fileInputStream = new FileInputStream(new File(path_dir));
// run
List<Object> list = EasyExcelFactory.read(fileInputStream, new Sheet(1));
String listString = JSONObject.toJSONString(list);
JSONArray arryList = JSONObject.parseArray(listString);
// 处理数据
for (int i = 1; i < arryList.size(); i++) {
JSONArray rowData = JSONObject.parseArray(JSONObject.toJSONString(arryList.get(i)));
Device Device = new Device((String) rowData.get(0), (String) rowData.get(1), (String) rowData.get(2),
(String) rowData.get(3), (String) rowData.get(4), (String) rowData.get(5),
(String) rowData.get(6), (String) rowData.get(7), (String) rowData.get(8),
(String) rowData.get(9), (String) rowData.get(10), (String) rowData.get(11), (String) rowData.get(12),
(String) rowData.get(13)
);
listDeviceOver.add(Device);
}
}
/**
* 查找所有xlsx后缀的文件
* @param path
*/
public static void folderMethod2(String path) {
File file = new File(path);
if (file.exists()) {
File[] files = file.listFiles();
if (null != files) {
for (File file2 : files) {
if (file2.isDirectory()) {
folderMethod2(file2.getAbsolutePath());
} else {
String file2AbsolutePath = file2.getAbsolutePath();
int length = file2AbsolutePath.length();
if (file2AbsolutePath.substring(file2AbsolutePath.lastIndexOf("."), length).equals(".xlsx")) {
listPath.add(file2AbsolutePath);
}
}
}
}
} else {
}
}
}
最后我们集合内的数据有这些
第三步：数据写入新表格
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ljm.entity.Device;
import com.ljm.entity.Device;
import com.ljm.tools.EasyExcelUtil2;
import lombok.extern.slf4j.Slf4j;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
/**
* @author lijiamin
*/
public class DemoApplication {
/** xlsx文件路径 */
public static List<String> listPath = new ArrayList<String>();
/** 现有的数据量 */
public static List<Device> listDeviceOver = new ArrayList<Device>();
public static void main(String[] args) throws FileNotFoundException {
// 文件路径
String path_dir = "C:\\Users\\SZBY-lijiamin\\Desktop\\data";
folderMethod2(path_dir);
System.out.println("目前listPath集合有数据量：" + listPath.size());
// 插入集合
for (String path : listPath
) {
insertList(path);
}
// 检查数据
System.out.println("目前listDeviceOver集合有数据量：" + listDeviceOver.size());
// 初始化
String resultPath = "C:\\Users\\SZBY-lijiamin\\Desktop\\hello.xlsx";
File file = new File(resultPath);
EasyExcelUtil2 easyExcelUtil = new EasyExcelUtil2();
List<String> stringTitle = new ArrayList<>(1);
easyExcelUtil.init(file.getAbsolutePath(), "牛逼", stringTitle);
// 数据写入
List<List<String>> sumDataList = new ArrayList<>(3);
// 数据处理
listDeviceOver.forEach(new Consumer<Device>() {
@Override
public void accept(Device device) {
List<String> dataList = new ArrayList<>(15);
dataList.add("NULL");
dataList.add(device.getElementID());
dataList.add(device.getCODE());
dataList.add(device.getVersion());
dataList.add(device.getModel());
dataList.add(device.getType());
dataList.add(device.getFileName());
dataList.add(device.getLocked());
dataList.add(device.getModified());
dataList.add(device.getModifiedTime());
dataList.add(device.getNew());
dataList.add(device.getLevel());
dataList.add(device.getRange());
dataList.add(device.getDescription());
dataList.add(device.getNotes());
sumDataList.add(dataList);
}
});
easyExcelUtil.doExportExcel(sumDataList);
// 关闭流
easyExcelUtil.finish();
}
/**
* excel拿到的数据插入内存集合
* @param path_dir
* @throws FileNotFoundException
*/
public static void insertList(String path_dir) throws FileNotFoundException {
FileInputStream fileInputStream = new FileInputStream(new File(path_dir));
// run
List<Object> list = EasyExcelFactory.read(fileInputStream, new Sheet(1));
String listString = JSONObject.toJSONString(list);
JSONArray arryList = JSONObject.parseArray(listString);
// 处理数据
for (int i = 1; i < arryList.size(); i++) {
JSONArray rowData = JSONObject.parseArray(JSONObject.toJSONString(arryList.get(i)));
Device Device = new Device((String) rowData.get(0), (String) rowData.get(1), (String) rowData.get(2),
(String) rowData.get(3), (String) rowData.get(4), (String) rowData.get(5),
(String) rowData.get(6), (String) rowData.get(7), (String) rowData.get(8),
(String) rowData.get(9), (String) rowData.get(10), (String) rowData.get(11), (String) rowData.get(12),
(String) rowData.get(13)
);
listDeviceOver.add(Device);
}
}
/**
* 查找所有xlsx后缀的文件
* @param path
*/
public static void folderMethod2(String path) {
File file = new File(path);
if (file.exists()) {
File[] files = file.listFiles();
if (null != files) {
for (File file2 : files) {
if (file2.isDirectory()) {
folderMethod2(file2.getAbsolutePath());
} else {
String file2AbsolutePath = file2.getAbsolutePath();
int length = file2AbsolutePath.length();
if (file2AbsolutePath.substring(file2AbsolutePath.lastIndexOf("."), length).equals(".xlsx")) {
listPath.add(file2AbsolutePath);
}
}
}
}
} else {
}
}
}
你学废了吗
