Merge more than one excel files into one excel file using Apache POI Java

Question

I am having 100 excel files and I want to merge all of them into one excel file. Here in my example I am having 2 excel files and I want to merge them into one. I can't do it. I am using Apache POI API. In one excel workbook there can be more than one sheets also so I want to iterate through sheets of each workbook also. I tried and researched but I got this link and it's not working for me https://dev.to/eiceblue/merge-excel-files-in-java-2lo2#:~:text=A%20quick%20way%20to%20merge,data%20table%20into%20another%20worksheet.

Please help me out here.

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");
        }
    }
}

My Excel files:

Test2.xlsx

Test3.xlsx

Here some columns are extra in Test3.xlsx and in both files as you can see in the heading row its all string but after that it has numeric values.

Answer 1

Here you have an approximation of the code you need, format it, extract functionalities to methods and check the naming of sheets.

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);
}

Function to get the first data row (necessary to avoid having to enter by hand where the header of each excel ends):

/**
 * 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;
}

Create the sheet with header in the method:

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

    //Implement the header
    [...]

    return sheet;
}