import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.testng.annotations.Test;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;
import static tools.ContentFormat.*;
public class MetaCombine {
//检索文件类型
private static final String scanType = "xlsx";
//检索目录
private static final String sourceDir = "data/BusinessPlatform/MetaDataFill/";
//合并目录
private static final String targetDir = "data/BusinessPlatform/MetaDataCombine/";
//合并文件名
private static final String targetFileName = "meta_data_combine.xlsx";
//合并sheet名
private static final String targetSheetName = "combine_result";
private static int orderNo = 0;
public static List<Standard> listStandard = new ArrayList<Standard>();
@Test
public static void combineMethod() throws Exception {
String targetFile = targetDir + targetFileName;
FileDelete(targetFile);
List<String> listPath = FolderSearch(sourceDir, scanType);
System.out.println("\n找到【" + scanType + "】文件数量：" + listPath.size());
for (String line : listPath) {
System.out.println("\t" + line);
insertList(line);
}
System.out.println("\n读取总行数：" + listStandard.size());
File file = new File(targetFile);
InitializeUtil easyExcelUtil = new InitializeUtil();
easyExcelUtil.init(file.getAbsolutePath(), targetSheetName, Title.class);
List<List<String>> sumDataList = new ArrayList<>();
listStandard.forEach(new Consumer<Standard>() {
@Override
public void accept(Standard standard) {
//自定义数据格式Class
List<String> dataList = new ArrayList<>();
dataList.add(standard.getId());
dataList.add(standard.getIndustry_name());
dataList.add(standard.getIndustry_code());
dataList.add(standard.getTheme());
dataList.add(standard.getTheme_code());
dataList.add(standard.getComponent_name());
dataList.add(standard.getModule_name());
dataList.add(standard.getCname());
dataList.add(standard.getEname());
dataList.add(standard.getStandard_alias());
dataList.add(standard.getBiz_defined());
dataList.add(standard.getBiz_rule());
dataList.add(standard.getCode_rule());
dataList.add(standard.getValue_range());
dataList.add(standard.getUnit_measure());
dataList.add(standard.getSafe_level());
dataList.add(standard.getTech_standard());
dataList.add(standard.getStandard_relation());
dataList.add(standard.getData_type());
dataList.add(standard.getData_form());
dataList.add(standard.getSource_database());
dataList.add(standard.getSource_table());
dataList.add(standard.getSub_table());
dataList.add(standard.getSource_ename());
dataList.add(standard.getDefault_value());
dataList.add(standard.getStatistic_period());
dataList.add(standard.getStandard_code());
dataList.add(standard.getCreate_user());
dataList.add(standard.getCreate_dept());
dataList.add(standard.getManager_user());
dataList.add(standard.getData_user());
dataList.add(standard.getBiz_use_area());
dataList.add(standard.getUse_system());
dataList.add(standard.getStandard_type());
dataList.add(standard.getStandard_sub_type());
dataList.add(standard.getStandard_status());
dataList.add(standard.getVersion());
dataList.add(standard.getCreate_date());
dataList.add(standard.getUpdate_date());
dataList.add(standard.getEffective_date());
dataList.add(standard.getDisable_date());
sumDataList.add(dataList);
}
});
easyExcelUtil.doExportExcel(sumDataList);
easyExcelUtil.finish();
}
public static void insertList(String listPath) throws Exception {
FileInputStream fileInputStreams = new FileInputStream(new File(listPath));
ExcelReader build = EasyExcel.read(fileInputStreams).build();
List<ReadSheet> readSheets = build.excelExecutor().sheetList();
for (int j = 1; j < readSheets.size() - 3; j++) {
FileInputStream fileInputStream = new FileInputStream(new File(listPath));
List<Object> list = EasyExcelFactory.read(fileInputStream, new Sheet(3 + j));
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)));
if (rowData.get(1).equals("行业")) {
continue;
} else {
orderNo++;
Standard standard = new Standard(String.valueOf(orderNo), (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), (String) rowData.get(14), (String) rowData.get(15), (String) rowData.get(16), (String) rowData.get(17), (String) rowData.get(18), (String) rowData.get(19), (String) rowData.get(20), (String) rowData.get(21), (String) rowData.get(22), (String) rowData.get(23), (String) rowData.get(24), (String) rowData.get(25), (String) rowData.get(26), (String) rowData.get(27), (String) rowData.get(28), (String) rowData.get(29), (String) rowData.get(30), (String) rowData.get(31), (String) rowData.get(32), (String) rowData.get(33), (String) rowData.get(34), (String) rowData.get(35), (String) rowData.get(36), (String) rowData.get(37), (String) rowData.get(38), (String) rowData.get(39), (String) rowData.get(40));
listStandard.add(standard);
}
}
}
}
}
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.util.List;
public class InitializeUtil {
private ExcelWriter excelWriter = null;
private WriteSheet writeSheet = null;
/**
* ===========================================================
* Purpose : EasyExcel工具类 初始化(最先调用)
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2022-06-22 yangxk-b Creation
* ===========================================================
*
* @params: absFilePath 绝对路径
* @params: sheetName 标签页名字
* @params: titleList 标题头(第一行)
*/
//表头传List
// 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);
// excelWriter = EasyExcelFactory.write(absFilePath).head(heads).build();
// writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
// }
// }
//表头传Class类
public void init(String absFilePath, String sheetName, Class title) {
if (excelWriter == null && writeSheet == null) {
excelWriter = EasyExcelFactory.write(absFilePath).head(title).build();
writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
}
}
/**
* ===========================================================
* Purpose : EasyExcel工具类 写入excel写内容
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2022-06-22 yangxk-b Creation
* ===========================================================
*
* @params: dataList 要插入的数据(多行插入)
*/
public void doExportExcel(List<List<String>> dataList) {
try {
excelWriter.write(dataList, writeSheet);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* ===========================================================
* Purpose : EasyExcel工具类 关闭(最后调用 关闭流)
* Version Date Name Description
* -------- --------------- -------------- --------------------
* V1.0 2022-06-22 yangxk-b Creation
* ===========================================================
*/
public void finish() {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
这是测试文本，单击 “编辑” 按钮更改此文本。