<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
<version>4.6.1</version>
</dependency>
package com.example.demo.utils2;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import java.io.*;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
public class ExcelMergeUtils {
private static final int XLS_MAX_ROWS = 65535;
private static final int XLSX_MAX_ROWS = 1048575;
public static void main(String[] args) throws IOException {
mergeExcel(new File("E:\\file\\excelAll\\33.xls"), Arrays.asList(Objects.requireNonNull(new File("E:\\file\\testAddRowGJ").listFiles())), false);
if (1 == 1) {
return;
}
File deleteFile = new File("G:\\1\\23");
String savePath = "E:\\file\\testAddRowJF2";
File[] deleteFiles = deleteFile.listFiles();
for (File deleteFile1 : deleteFiles) {
System.err.println(deleteFile1.getName());
if (deleteFile1.isDirectory()) {
File[] deleteFiles2 = deleteFile1.listFiles();
for (File file : deleteFiles2) {
if (file.getName().toLowerCase().indexOf("xls") != -1 || file.getName().toLowerCase().indexOf("xlsx") != -1) {
addRow(file.getCanonicalPath(), savePath, false);
}
}
}
}
}
private static int id = 1;
public static void addRow(String filepath, String saveFilePathDir, boolean isXlsx) {
int id2 = id++;
OutputStream out = null;
try {
File file = new File(filepath);
Workbook workBook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file));
Sheet sheet = workBook.getSheetAt(1);
int i = 0;
int j = 0;
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
i += 1;
Row row = sheet.getRow(rowNum);
Cell cell = row.getCell(0);
if (null == cell || StringUtils.isEmpty(cell.getStringCellValue())) {
System.out.println("null====第" + i + "行");
break;
}
Cell cell5 = row.createCell(4);
Cell cell6 = row.createCell(5);
Cell cell7 = row.createCell(6);
if (rowNum == 0) {
cell5.setCellValue("小区");
cell6.setCellValue("图片目录");
cell7.setCellValue("id");
} else {
cell5.setCellValue("解放");
cell6.setCellValue(file.getParentFile().getName());
cell7.setCellValue(id2);
}
}
out = new FileOutputStream(saveFilePathDir + "//" + file.getName());
workBook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void mergeExcel(File mergedFile, List<File> files, boolean isXlsx) throws IOException {
if (mergedFile == null || files == null) {
return;
}
try (Workbook mergedWorkbook = isXlsx ? new SXSSFWorkbook() : new HSSFWorkbook();
FileOutputStream out = new FileOutputStream(mergedFile)) {
Sheet newSheet = mergedWorkbook.createSheet();
int start = 0;
for (File file : files) {
if (file == null) {
continue;
}
try (Workbook oldWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file))) {
int oldSheetSize = oldWorkbook.getNumberOfSheets();
for (int i = 0; i < 1; i++) {
Sheet oldSheet = oldWorkbook.getSheetAt(i);
int oldRowSize = oldSheet.getLastRowNum();
for (int j = 0; j <= oldRowSize; j++) {
if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {
newSheet = mergedWorkbook.createSheet();
start = newSheet.getLastRowNum();
}
Row oldRow = oldSheet.getRow(j);
Row newRow = newSheet.createRow(start);
copyRow(oldRow, newRow);
start++;
}
}
}
}
mergedWorkbook.write(out);
}
}
private static void copyRow(Row oldRow, Row newRow) {
newRow.setHeight(oldRow.getHeight());
for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
Cell oldCell = oldRow.getCell(i);
if (null != oldCell) {
copyCell(oldCell, newRow.createCell(i));
}
}
}
private static void copyCell(Cell oldCell, Cell newCell) {
switch (oldCell.getCellType()) {
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(oldCell)) {
newCell.setCellValue(cn.hutool.core.date.DateUtil.formatDate(oldCell.getDateCellValue()));
} else {
newCell.setCellValue(oldCell.getNumericCellValue());
}
break;
case BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
default:
break;
}
}
}