java - 使用 Apache POI Java 将多个 excel 文件合并为一个 excel 文件

Question

我有 100 个 excel 文件，我想将它们全部合并到一个 excel 文件中。在我的示例中，我有 2 个 excel 文件，我想将它们合并为一个。我做不到。我正在使用 Apache POI API。在一个 excel 工作簿中也可以有多个工作表，所以我也想遍历每个工作簿的工作表。我尝试并研究过，但我得到了这个链接，它对我不起作用 https://dev.to/eiceblue/merge-excel-files-in-java-2lo2#:~:text=A%20quick%20way%20to% 20 合并，数据%20table%20into%20another%20worksheet。

请帮帮我。

package com.cas.ExcelTest;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
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;

public class Combine {

    public static void main(String args[]) {
        
        String[] files = new String[] {"Test2.xlsx","Test3.xlsx"};
        XSSFWorkbook workbook = new XSSFWorkbook();     
        try {
            for (int f = 0; f < files.length; f++) {    
                String file = files[f];
                FileInputStream inputStream = new FileInputStream(file);
                XSSFWorkbook tempWorkbook = new XSSFWorkbook(inputStream);
                          
                int numOfSheets = tempWorkbook.getNumberOfSheets();
                    
                for (int i = 0; i < numOfSheets; i++) {
                    XSSFSheet tempSheet = tempWorkbook.getSheetAt(i);
                    String newSheetName = ""+f+""+tempSheet.getSheetName();
                    XSSFSheet sheet = workbook.createSheet(newSheetName);
                    Iterator<Row> itRow = tempSheet.rowIterator();
                
                    while(itRow.hasNext()) {
                        Row tempRow = itRow.next();
                        XSSFRow row = sheet.createRow(tempRow.getRowNum());
                        Iterator<Cell> itCell = tempRow.cellIterator();
                    
                        while(itCell.hasNext()) {
                            Cell tempCell = itCell.next();
                            XSSFCell cell = row.createCell(tempCell.getColumnIndex());
                        
                        switch (tempCell.getCellType()) {
                        case NUMERIC:
                            cell.setCellValue(tempCell.getNumericCellValue());
                            break;
                        case STRING:
                            cell.setCellValue(tempCell.getStringCellValue());
                            break;
                        case BLANK:
                            break;
                        case BOOLEAN:
                            break;
                        case ERROR:
                            break;
                        case FORMULA:
                            cell.setCellValue(tempCell.getNumericCellValue());
                            break;
                        case _NONE:
                            break;
                        default:
                            break;
                    }
                        }       
                    }       
                }
            }
        } catch (IOException ex1) {
            System.out.println("Error reading file");
            ex1.printStackTrace();
        }
            
        try (FileOutputStream outputStream = new FileOutputStream("result.xlsx")) {
            workbook.write(outputStream);
        }
        catch(Exception ex) {
            System.out.println("Something went wrong");
        }
    }
}

我的 Excel 文件：

测试2.xlsx

测试3.xlsx

这里有些列在 Test3.xlsx 和两个文件中都是额外的，正如您在标题行中看到的那样，它的所有字符串都是字符串，但之后它具有数值。

标签： javaexcelmergeapache-poibulkinsert

Answer 1

在这里，您可以获得所需代码的近似值，对其进行格式化，将功能提取到方法并检查工作表的命名。

String[] files = new String[] {"Test2.xlsx","Test3.xlsx"};
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = createSheetWithHeader(workbook);
    
try {
    for (int f = 0; f < files.length; f++) {
        String file = files[f];
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook tempWorkbook = new XSSFWorkbook(inputStream);
            
        int numOfSheets = tempWorkbook.getNumberOfSheets();
            
        for (int i = 0; i < numOfSheets; i++) {
            XSSFSheet tempSheet = tempWorkbook.getSheetAt(i);
                
            int indexLastDataInserted = sheet.getLastRowNum();
            int firstDataRow = getFirstDataRow(tempSheet);
                                
            Iterator<Row> itRow = tempSheet.rowIterator();
            
            while(itRow.hasNext()) {
                Row tempRow = itRow.next();
                
                if (tempRow.getRowNum() >= firstDataRow) {
                    XSSFRow row = sheet.createRow(indexLastDataInserted + 1);
                    
                    Iterator<Cell> itCell = tempRow.cellIterator();
                    
                    while(itCell.hasNext()) {
                        Cell tempCell = itCell.next();
                        XSSFCell cell = row.createCell(tempCell.getColumnIndex());
                        //At this point you will have to set the value of the cell depending on the type of data it is
                        switch (tempCell.getCellType()) {
                            case NUMERIC:
                                cell.setCellValue(tempCell.getNumericCellValue());
                                break;
                            case STRING:
                                cell.setCellValue(tempCell.getStringCellValue());
                                break;
                            /**
                             * Add your other types, here is your problem!!!!!
                             */
                        }
                    }
                }                   
            }
        }
    } 
}catch (IOException ex1) {
    System.out.println("Error reading file");
    ex1.printStackTrace();
}
    
try (FileOutputStream outputStream = new FileOutputStream("result.xlsx")) {
    workbook.write(outputStream);
}

获取第一个数据行的函数（避免手动输入每个excel表头结束的地方）：

/**
 * If the tab has a filter, it returns the row index of the filter + 1, otherwise it returns 0
 * @param tempSheet
 * @return index of first data row
 */
public static Integer getFirstDataRow(XSSFSheet tempSheet) {
    Integer result = 0;
    Boolean isAutoFilter = tempSheet.getCTWorksheet().isSetAutoFilter();
    
    if (isAutoFilter) {
        String autoFilterRef = tempSheet.getCTWorksheet().getAutoFilter().getRef();
        
        result = new CellReference(autoFilterRef.substring(0, autoFilterRef.indexOf(":"))).getRow() + 1;
    }
    return result;
}

在方法中创建带有标题的工作表：

public static XSSFSheet createSheetWithHeader(XSSFWorkbook workbook){
    XSSFSheet sheet = workbook.createSheet("NEW_SHEET_NAME");

    //Implement the header
    [...]

    return sheet;
}