import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
public class CarrierConvertImport {
public static void main(String[] args) throws IOException{
List<String> filePath = getFilePath("C:\\Users\\\\\\Desktop\\New folder (2)");
List<DeliveryModel> deliveryModels = readModel();
for(int i=0; i<filePath.size(); i++) {
System.out.println(filePath.get(i));
List<TestAO> lists = readFile(filePath.get(i));
writeFile(lists,deliveryModels,filePath.get(i));
}
//xls to xlsx
// List<String> filePath = getFilePath("C:\\Users\\\\\Desktop\\Domestic Purchase Carrier Rate 20220303");
// for(int i=0; i<(); i++) {
// ((i));
// xlsToXlsx((i));
// }
//Total number of query
// (getFilePath(filePath));
//xlsx to the same header empty file
// List<String> filePath = getFilePath("C:\\Users\\\\\Desktop\\New folder (2)");
// for(int i=0; i<(); i++) {
// ((i));
// xlsxToNewHeadXlsx((i));
// }
// List<TestAO> testAOS = readFile("C:\\Users\\\\Desktop\\New folder (2)\\Carrier freight rules (delivery)_SF Express-Beijing Distribution Center_Beijing RDC-Heijiliao_20220323.xlsx");
// (testAOS);
// (());
}
/**
* Query the total number of data in all files
*/
public static Integer getFilePath(List<String> filePath) {
Integer j = 0;
for(int i=0; i<filePath.size(); i++) {
List<TestAO> lists = readFile(filePath.get(i));
String s = filePath.get(i);
if(s.contains("SF Express") && !s.contains("Pickup Point") && !s.contains("Door delivery")) {
j += lists.size() * 2;
} else {
j += lists.size();
}
}
return j;
}
/**
* Get all file paths in a folder
*/
public static List<String> getFilePath(String folderPath) {
File folder = new File(folderPath);
List<String> filePathList = new ArrayList<>();
String rootPath;
if (folder.exists()) {
String[] fileNameList = folder.list();
if (null != fileNameList && fileNameList.length > 0) {
if (folder.getPath().endsWith(File.separator)) {
rootPath = folder.getPath();
} else {
rootPath = folder.getPath() + File.separator;
}
for (String fileName : fileNameList) {
filePathList.add(rootPath + fileName);
}
}
}
return filePathList;
}
public static List<TestAO> readFile(String str) {
List<TestAO> lists = new ArrayList<>();
File file = new File(str);
try {
FileInputStream stream = new FileInputStream(file);//Create the input stream to change the file
XSSFWorkbook workbook = new XSSFWorkbook(stream);//Create a workbook
XSSFSheet sheet = workbook.getSheetAt(0);//Get a worksheet, the subscript starts from 0
XSSFRow headRow = sheet.getRow(0);
Map<Integer, String> headMap = new HashMap<>();
for(int i=0; i<headRow.getLastCellNum(); i++) {
XSSFCell cell = headRow.getCell(i);
cell.setCellType(CellType.STRING);
String cellValue = cell.toString();
headMap.put(i, cellValue);
}
for (int i=1; i < sheet.getLastRowNum()+1; i++) { //Fetch each row of data in the table row by row, getLastRowNum() starts from 0
XSSFRow row = sheet.getRow(i);// Get the line
TestAO ao = new TestAO();
for (int j = 0; j < headRow.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING); //Set the cell to String type uniformly, otherwise the number will be transferred to double type
String valuej = cell.toString();
String cellValue = headMap.get(j);
switch (cellValue) {
case "*Carrier Id":
if ("41".equals(valuej)) {
ao.setId("47");
} else {
ao.setId(valuej);
}
break;
case "*Shipping Warehouse Code":
ao.setCode(valuej);
break;
case "*Freight Template Name":
XSSFCell cel = row.getCell(0);
cel.setCellType(CellType.STRING);
String value2 = cel.toString();
//Discern whether the value of the first column is the carrier name
if(value2.length() > 2) {
ao.setExpressCompanyName(value2);
} else {
ao.setExpressCompanyName(valuej);
}
break;
case "ExpressCompanyName":
ao.setExpressCompanyName(valuej);
break;
case "*Delivery method (self-pickup/delivery)":
ao.setStyle(valuej);
break;
case "*Province/Municipal":
case "Province":
ao.setProvince(valuej);
break;
case "*city":
case "City":
ao.setCity(valuej);
break;
case "* County/district":
case "District":
ao.setDistrict(valuej);
break;
case "*First weight (KG)":
case "FirstWeight":
ao.setFirstWeight(valuej);
break;
case "*First price (yuan)":
case "FirstPrice":
ao.setFirstPrice(valuej);
break;
case "*Minimum charge (yuan)":
case "BasePrice":
ao.setBasePrice(valuej);
break;
case "*Continued price (yuan)":
case "RenewalPrice":
if(valuej != null && !valuej.equals("")) {
DecimalFormat d = new DecimalFormat("0.0");
String format = d.format(Double.parseDouble(valuej));
ao.setRenewalPrice(format);
} else {
ao.setRenewalPrice(null);
}
break;
case "Number of days required":
case "LeadTime":
ao.setLeadTime(valuej);
break;
}
}
lists.add(ao);
}
} catch (Exception e) {
e.printStackTrace();
}
lists.removeIf( a -> {
if("".equals(a.getDistrict()))
return true;
return false;
});
return lists;
}
/**
* @param lists Original file content
* @param model Carrier template
* @param url Original file path
*/
public static void writeFile(List<TestAO> lists, List<DeliveryModel> model, String url) throws IOException {
File file1 = new File("C:\\Users\\\\\\Desktop\\Carrier.xlsx");
FileInputStream streamRead = new FileInputStream(file1);//Create the input stream to change the file
XSSFWorkbook workbook = new XSSFWorkbook(streamRead);//Create a workbook
XSSFSheet xssfSheet = workbook.getSheetAt(0);//Get a worksheet, the subscript starts from 0
int rowNum = xssfSheet.getLastRowNum()+1;
int rowZ = rowNum;
int num = 0;
boolean numN = true;
//Judge whether you need to write double lines
boolean shunFengFlag = (url.contains("Pickup & Home Delivery") || (url.contains("SF Express") && !url.contains("(self-pickup)") && !url.contains("(Delivery)")))
&& (lists.get(0).getStyle() == null || lists.get(0).getStyle().equals(""));
int sumLine = shunFengFlag ? lists.size() * 2 : lists.size();
for(int i=rowNum; i<sumLine+rowNum; i++) {
TestAO testAO = new TestAO();
if(!shunFengFlag) {
testAO = lists.get(num++);
} else {
if(numN) {
testAO = lists.get(num);
numN = false;
} else {
testAO = lists.get(num);
num++;
numN = true;
}
}
//Match the carrier name
AtomicReference<DeliveryModel> deliveryModel = new AtomicReference<>(new DeliveryModel());
TestAO finalTestAO = testAO;
Map<String, DeliveryModel> collectName = model.stream().collect(Collectors.toMap(DeliveryModel::getName, a -> a));
Map<String, DeliveryModel> collectId = model.stream().collect(Collectors.toMap(DeliveryModel::getId, a -> a));
if(finalTestAO.getExpressCompanyName() != null && !finalTestAO.getExpressCompanyName().equals("null") && !finalTestAO.getExpressCompanyName().equals("")) {
collectName.forEach((a, b)->{
if(Objects.equals(a.trim(), finalTestAO.getExpressCompanyName().trim())) {
deliveryModel.set(b);
}
});
} else {
collectId.forEach((a, b)->{
if(Objects.equals(a.trim(), finalTestAO.getId().trim())) {
deliveryModel.set(b);
}
});
}
DeliveryModel dModel = deliveryModel.get();
XSSFRow row = xssfSheet.createRow(rowZ++);
for(int z=0; z<14; z++) {
XSSFCell cell = row.createCell(z);
switch (z) {
case 0 :
cell.setCellValue(dModel.getId());
break;
case 1 :
cell.setCellValue(dModel.getCode());
break;
case 2 :
cell.setCellValue(dModel.getName());
break;
case 3 :
if(testAO.getStyle()!=null&&!testAO.getStyle().equals("")) {
cell.setCellValue(testAO.getStyle());
break;
}
if (shunFengFlag) {
if(numN) {
cell.setCellValue("Pick up");
} else {
cell.setCellValue("Delivery");
}
} else {
if(url.contains("Pickup Point")) {
cell.setCellValue("Pick up");
} else if(url.contains("Door delivery")) {
cell.setCellValue("Delivery");
}
}
break;
case 4 :
cell.setCellValue(testAO.getProvince());
break;
case 5 :
cell.setCellValue(testAO.getCity());
break;
case 6 :
cell.setCellValue(testAO.getDistrict());
break;
case 7 :
cell.setCellValue(testAO.getFirstWeight());
break;
case 8 :
cell.setCellValue(testAO.getFirstPrice());
break;
case 9 :
cell.setCellValue(testAO.getBasePrice());
break;
case 10 :
cell.setCellValue(testAO.getRenewalPrice());
break;
case 11 :
cell.setCellValue(testAO.getLeadTime());
break;
case 12 :
cell.setCellValue("2022-4-26 00:00:00");
break;
case 13 :
cell.setCellValue("2024-08-31 23:59:59");
break;
}
}
}
OutputStream stream2 = new FileOutputStream(file1);//Create the input stream to change the file
workbook.write(stream2);
streamRead.close();
stream2.flush();
stream2.close();
}
/**
* Read template
*/
public static List<DeliveryModel> readModel() {
List<DeliveryModel> lists = new ArrayList<>();
File file = new File("C:\\Users\\\\\\ Herbalife BMP\\ Carrier freight import (tester)\\ Carrier ID, warehouse code correspondence (1).xlsx");
try {
FileInputStream stream = new FileInputStream(file);//Create the input stream to change the file
XSSFWorkbook workbook = new XSSFWorkbook(stream);//Create a workbook
XSSFSheet sheet = workbook.getSheetAt(0);//Get a worksheet, the subscript starts from 0
for (int i=1; i < sheet.getLastRowNum()+1; i++) { //Fetch each row of data in the table row by row, getLastRowNum() starts from 0
XSSFRow row = sheet.getRow(i);// Get the line
DeliveryModel ao = new DeliveryModel();
for(int j=0; j<3; j++){
XSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING); //Set the cell to String type uniformly, otherwise the number will be transferred to double type
String valuej = cell.toString();
switch (j) {
case 0 :
ao.setId(valuej);
break;
case 1 :
ao.setCode(valuej);
break;
case 2 :
ao.setName(valuej);
break;
}
}
lists.add(ao);
}
// (lists);
} catch (Exception e) {
e.printStackTrace();
}
return lists;
}
/**
* xls to Xlsx
*/
public static void xlsToXlsx(String str) throws FileNotFoundException {
File file = new File(str);
InputStream in = new BufferedInputStream(new FileInputStream(file));
try {
Workbook wbIn = new HSSFWorkbook(in);
File outF = new File(file.getPath().substring(0,file.getPath().indexOf("xls"))+"xlsx");
if (outF.exists())
outF.delete();
Workbook wbOut = new XSSFWorkbook();
int sheetCnt = wbIn.getNumberOfSheets();
for (int i = 0; i < sheetCnt; i++) {
Sheet sIn = wbIn.getSheetAt(0);
if(wbOut.getSheet(sIn.getSheetName())!=null) {
wbOut = new XSSFWorkbook();
}
Sheet sOut = wbOut.createSheet(sIn.getSheetName());
Iterator<Row> rowIt = sIn.rowIterator();
while (rowIt.hasNext()) {
Row rowIn = rowIt.next();
Row rowOut = sOut.createRow(rowIn.getRowNum());
Iterator<Cell> cellIt = rowIn.cellIterator();
while (cellIt.hasNext()) {
Cell cellIn = cellIt.next();
Cell cellOut = rowOut.createCell(
cellIn.getColumnIndex(), cellIn.getCellType());
switch (cellIn.getCellType()) {
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_BOOLEAN:
cellOut.setCellValue(cellIn.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
cellOut.setCellValue(cellIn.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellOut.setCellFormula(cellIn.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
cellOut.setCellValue(cellIn.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
cellOut.setCellValue(cellIn.getStringCellValue());
break;
}
{
CellStyle styleIn = cellIn.getCellStyle();
CellStyle styleOut = cellOut.getCellStyle();
styleOut.setDataFormat(styleIn.getDataFormat());
}
cellOut.setCellComment(cellIn.getCellComment());
// HSSFCellStyle cannot be cast to XSSFCellStyle
// (());
}
}
}
OutputStream out = new BufferedOutputStream(new FileOutputStream(outF));
try {
wbOut.write(out);
} finally {
out.close();
wbOut.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* Convert Xlsx file to an empty xlsx file with the same name and only the first column
*/
public static void xlsxToNewHeadXlsx(String str) throws FileNotFoundException {
File file = new File(str);
InputStream in = new BufferedInputStream(new FileInputStream(file));
try {
Workbook wbIn = WorkbookFactory.create(in);
File outF = new File(file.getPath().substring(0,file.getPath().lastIndexOf("\\")+1)+"new"+file.getName());
System.out.println(file.getPath().substring(0,file.getPath().lastIndexOf("\\")+1)+"new"+file.getName());
if (outF.exists())
outF.delete();
Workbook wbOut = new XSSFWorkbook();
int sheetCnt = wbIn.getNumberOfSheets();
for (int i = 0; i < sheetCnt; i++) {
Sheet sIn = wbIn.getSheetAt(0);
if(wbOut.getSheet(sIn.getSheetName())!=null) {
wbOut = new XSSFWorkbook();
}
Sheet sOut = wbOut.createSheet(sIn.getSheetName());
Row rowOut = sOut.createRow(0);
Row rowIn = sIn.getRow(0);
Iterator<Cell> cellIt = rowIn.cellIterator();
while (cellIt.hasNext()) {
Cell cellIn = cellIt.next();
Cell cellOut = rowOut.createCell(
cellIn.getColumnIndex(), cellIn.getCellType());
switch (cellIn.getCellType()) {
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_BOOLEAN:
cellOut.setCellValue(cellIn.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
cellOut.setCellValue(cellIn.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellOut.setCellFormula(cellIn.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
cellOut.setCellValue(cellIn.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
cellOut.setCellValue(cellIn.getStringCellValue());
break;
}
{
CellStyle styleIn = cellIn.getCellStyle();
CellStyle styleOut = cellOut.getCellStyle();
styleOut.setDataFormat(styleIn.getDataFormat());
}
cellOut.setCellComment(cellIn.getCellComment());
// HSSFCellStyle cannot be cast to XSSFCellStyle
// (());
}
}
OutputStream out = new BufferedOutputStream(new FileOutputStream(outF));
try {
wbOut.write(out);
} finally {
out.close();
wbOut.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
}
