package com.szsj.job.utils;
import com.google.gson.Gson;
import com.spire.xls.ExcelVersion;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author zhongyy
* @Des
* @date 2022/4/4 17:19
*/
public class ExcelTest {
static String basePath = "E:\\test\\数据字典整合.xls";
static String modelFile = "E:\\test\\业务模型模板.xls";
static String baseFile = "E:\\test\\业务模型0404.xls";
public static void main(String[] args) {
String type = "";
if (args.length == 2){
type = args[0];
baseFile = args[1];
fileJobType(type);
} else {
System.out.println("参数有误，请重新输入");
}
//
// System.out.println(basePath);
// System.out.println(modelFile);
// System.out.println(baseFile);
}
public static void fileJobType(String type) {
if (type.equalsIgnoreCase("copy")) {
String packageName = baseFile.substring(0, baseFile.lastIndexOf("\\"));
start(packageName);
} else if (type.equalsIgnoreCase("update")) {
update();
} else {
System.out.println("type类型输入有误，请重新输入");
}
}
public static void update() {
try {
// InputStream inpTask = new FileInputStream(baseFile);
// Workbook wbTask = WorkbookFactory.create(inpTask);
Workbook wbTask = findWorkbook(baseFile);
Sheet sheetTask1 = wbTask.getSheetAt(1);
int totalRow1 = sheetTask1.getPhysicalNumberOfRows();
String tableNameEn = "";
String tableNameCn = "";
List<ExcelEntity> excelEntityList = new ArrayList<>();
for (int i = 0; i < totalRow1; i++) {
Row row = sheetTask1.getRow(i);
String sourceType = row.getCell(13).getStringCellValue().trim();
if (sourceType.equals("待确认")) {
break;
}
String tableName = row.getCell(14).getStringCellValue();
if (i == 1 && sourceType.equalsIgnoreCase("T2.0")) {
tableNameEn = "ZTK_BA_" + tableName.substring(7, tableName.length());
tableNameCn = row.getCell(15).getStringCellValue();
}
String tableName2 = row.getCell(15).getStringCellValue();
ExcelEntity excelEntity = new ExcelEntity();
excelEntity.setCell3(sourceType);
excelEntity.setCell5(tableName);
excelEntity.setCell6(tableName2);
excelEntityList.add(excelEntity);
System.out.println(tableName + " : " + tableNameEn + " : " + tableNameCn);
}
Sheet sheetTask2 = wbTask.getSheetAt(2);
int totalRow = sheetTask2.getPhysicalNumberOfRows();
int totalColumn = sheetTask2.getRow(1).getPhysicalNumberOfCells();
System.out.println(totalRow + " : " + totalColumn);
List<DataEntity> dataEntityList = new ArrayList<>();
DataEntity dataEntity;
for (int i = 2; i < totalRow; i++) {
Row row = sheetTask2.getRow(i);
if (null == row) {
row = sheetTask2.createRow(i);
}
Cell cell1 = row.getCell(0);
Cell cell15 = row.getCell(14);
if (null != cell1 && cell1.getStringCellValue().equals("总计")) {
dataEntity = new DataEntity();
dataEntity.setFiled1(tableNameCn);
dataEntityList.add(dataEntity);
System.out.println("总共有" + (i + 1) + "行");
break;
}
if (null != cell15 && !cell15.getStringCellValue().equals("") && !cell15.getStringCellValue().equals("删除")) {
dataEntity = new DataEntity();
dataEntity.setFiled1(tableNameEn);
Cell cell10 = row.getCell(9);
if (null != cell10) {
cell10.setCellType(CellType.STRING);
dataEntity.setFiled2(cell10.getStringCellValue().trim());
}
dataEntity.setFiled3(row.getCell(0).getStringCellValue());
dataEntity.setFiled4(row.getCell(1).getStringCellValue());
dataEntity.setFiled5(row.getCell(2).getStringCellValue());
Cell cell4 = row.getCell(3);
if (null != cell4) {
cell4.setCellType(CellType.STRING);
String cell4Value = cell4.getStringCellValue().trim();
if (cell4Value.equals("(空白)")) {
dataEntity.setFiled6(cell4Value);
}
}
Cell cell5 = row.getCell(4);
if (null != cell5) {
cell5.setCellType(CellType.STRING);
String cell5Value = cell5.getStringCellValue().trim();
if (cell5Value.equals("(空白)")) {
dataEntity.setFiled7(cell5Value);
}
}
Cell cell6 = row.getCell(5);
if (null != cell6) {
cell6.setCellType(CellType.STRING);
String cell6Value = cell6.getStringCellValue().trim();
if (cell6Value.equals("(空白)")) {
dataEntity.setFiled8(cell6Value);
}
}
dataEntity.setFiled9(row.getCell(6).getStringCellValue());
Cell remarkCell = row.getCell(13);
if (null != remarkCell) {
dataEntity.setFiled10(remarkCell.getStringCellValue());
}
dataEntityList.add(dataEntity);
System.out.println(cell15.getStringCellValue());
}
}
Sheet sheetTask0 = wbTask.getSheetAt(0);
List<List<String>> allDataCompareList = new ArrayList<>();
if (excelEntityList.size() > 0) {
for (int k = 0; k < excelEntityList.size(); k++) {
List<String> dataList = new ArrayList<>();
Sheet sheetTaskType = wbTask.getSheetAt(3 + k);
int totalRowType = sheetTaskType.getPhysicalNumberOfRows();
for (int m = 0; m < totalRowType; m++) {
dataList.add(sheetTaskType.getRow(m).getCell(0).getStringCellValue());
}
allDataCompareList.add(dataList);
}
}
if (dataEntityList.size() > 0) {
for (int i = 0; i < dataEntityList.size(); i++) {
DataEntity baseDataEntity = dataEntityList.get(i);
if ((i + 1) == dataEntityList.size()) {
sheetTask0.getRow(1).getCell(1).setCellValue(baseDataEntity.getFiled1());
break;
}
Row row = sheetTask0.getRow(i + 1);
if (null == row) {
row = sheetTask0.createRow(i + 1);
}
row.createCell(0).setCellValue(baseDataEntity.getFiled1());
row.
评论0