java操作xlsx文件
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\\新建文件夹 (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转xlsx
// List<String> filePath = getFilePath("C:\\Users\\\\Desktop\\国内购承运商费率 20220303");
// for(int i=0; i<(); i++) {
// ((i));
// xlsToXlsx((i));
// }
//查询总数
// (getFilePath(filePath));
//xlsx转同头空文件
// List<String> filePath = getFilePath("C:\\Users\\\\Desktop\\新建文件夹 (2)");
// for(int i=0; i<(); i++) {
// ((i));
// xlsxToNewHeadXlsx((i));
// }
// List<TestAO> testAOS = readFile("C:\\Users\\\\Desktop\\新建文件夹 (2)\\承运商运费规则（配送）_顺丰-北京配送中心_北京RDC-黑吉辽_20220323.xlsx");
// (testAOS);
// (());
}
/**
* 查询所有文件数据总数
*/
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("顺丰") && !s.contains("自提点") && !s.contains("送货上门")) {
j += lists.size() * 2;
} else {
j += lists.size();
}
}
return j;
}
/**
* 获取一个文件夹的所有文件路径
*/
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);//创建改文件的输入流
XSSFWorkbook workbook = new XSSFWorkbook(stream);//创建工作簿
XSSFSheet sheet = workbook.getSheetAt(0);//获取一个工作表，下标从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++) { //逐行取出表中每行数据,getLastRowNum()从0开始
XSSFRow row = sheet.getRow(i);// 获取行
TestAO ao = new TestAO();
for (int j = 0; j < headRow.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING); //将单元格统一设置为String类型，否则数字转出为double类型
String valuej = cell.toString();
String cellValue = headMap.get(j);
switch (cellValue) {
case "*承运商Id":
if ("41".equals(valuej)) {
ao.setId("47");
} else {
ao.setId(valuej);
}
break;
case "*发货仓库Code":
ao.setCode(valuej);
break;
case "*运费模板名称":
XSSFCell cel = row.getCell(0);
cel.setCellType(CellType.STRING);
String value2 = cel.toString();
//判断第一列的值是否为承运商名称
if(value2.length() > 2) {
ao.setExpressCompanyName(value2);
} else {
ao.setExpressCompanyName(valuej);
}
break;
case "ExpressCompanyName":
ao.setExpressCompanyName(valuej);
break;
case "*配送方式(自提/配送)":
ao.setStyle(valuej);
break;
case "*省/直辖市":
case "Province":
ao.setProvince(valuej);
break;
case "*市":
case "City":
ao.setCity(valuej);
break;
case "*县/区":
case "District":
ao.setDistrict(valuej);
break;
case "*首重重量(KG)":
case "FirstWeight":
ao.setFirstWeight(valuej);
break;
case "*首重价格(元)":
case "FirstPrice":
ao.setFirstPrice(valuej);
break;
case "*最低收费(元)":
case "BasePrice":
ao.setBasePrice(valuej);
break;
case "*续重价格(元)":
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 "所需天数":
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 原文件内容
* @param model 承运商模板
* @param url 原文件路径
*/
public static void writeFile(List<TestAO> lists, List<DeliveryModel> model, String url) throws IOException {
File file1 = new File("C:\\Users\\\\Desktop\\承运商.xlsx");
FileInputStream streamRead = new FileInputStream(file1);//创建改文件的输入流
XSSFWorkbook workbook = new XSSFWorkbook(streamRead);//创建工作簿
XSSFSheet xssfSheet = workbook.getSheetAt(0);//获取一个工作表，下标从0开始
int rowNum = xssfSheet.getLastRowNum()+1;
int rowZ = rowNum;
int num = 0;
boolean numN = true;
//判断是否需要写双行
boolean shunFengFlag = (url.contains("自提&送货上门") || (url.contains("顺丰") && !url.contains("（自提）") && !url.contains("（配送）")))
&& (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;
}
}
//匹配承运商名称
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("自提");
} else {
cell.setCellValue("配送");
}
} else {
if(url.contains("自提点")) {
cell.setCellValue("自提");
} else if(url.contains("送货上门")) {
cell.setCellValue("配送");
}
}
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);//创建改文件的输入流
workbook.write(stream2);
streamRead.close();
stream2.flush();
stream2.close();
}
/**
* 读取模板
*/
public static List<DeliveryModel> readModel() {
List<DeliveryModel> lists = new ArrayList<>();
File file = new File("C:\\Users\\\\康宝莱BMP\\承运商运费导入（测试人员）\\承运商ID、仓库code对应关系(1).xlsx");
try {
FileInputStream stream = new FileInputStream(file);//创建改文件的输入流
XSSFWorkbook workbook = new XSSFWorkbook(stream);//创建工作簿
XSSFSheet sheet = workbook.getSheetAt(0);//获取一个工作表，下标从0开始
for (int i=1; i < sheet.getLastRowNum()+1; i++) { //逐行取出表中每行数据,getLastRowNum()从0开始
XSSFRow row = sheet.getRow(i);// 获取行
DeliveryModel ao = new DeliveryModel();
for(int j=0; j<3; j++){
XSSFCell cell = row.getCell(j);
cell.setCellType(CellType.STRING); //将单元格统一设置为String类型，否则数字转出为double类型
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转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();
}
}
/**
* Xlsx文件转成同名且只有头一列的空xlsx文件
*/
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();
}
}
}