使用Apache POI库的Java代码将多个Excel文件合并成一个Excel文件。

huangapple
go评论70阅读模式
英文:

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

问题

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

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[] {&quot;Test2.xlsx&quot;,&quot;Test3.xlsx&quot;};
XSSFWorkbook workbook = new XSSFWorkbook();     
try {
for (int f = 0; f &lt; 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 &lt; numOfSheets; i++) {
XSSFSheet tempSheet = tempWorkbook.getSheetAt(i);
String newSheetName = &quot;&quot;+f+&quot;&quot;+tempSheet.getSheetName();
XSSFSheet sheet = workbook.createSheet(newSheetName);
Iterator&lt;Row&gt; itRow = tempSheet.rowIterator();
while(itRow.hasNext()) {
Row tempRow = itRow.next();
XSSFRow row = sheet.createRow(tempRow.getRowNum());
Iterator&lt;Cell&gt; 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(&quot;Error reading file&quot;);
ex1.printStackTrace();
}
try (FileOutputStream outputStream = new FileOutputStream(&quot;result.xlsx&quot;)) {
workbook.write(outputStream);
}
catch(Exception ex) {
System.out.println(&quot;Something went wrong&quot;);
}
}
}

> 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.

答案1

得分: 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());

                        switch (tempCell.getCellType()) {
                            case NUMERIC:
                                cell.setCellValue(tempCell.getNumericCellValue());
                                break;
                            case STRING:
                                cell.setCellValue(tempCell.getStringCellValue());
                                break;
                            // Add other cell types here
                        }
                    }
                }
            }
        }
    }
} 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
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
public static XSSFSheet createSheetWithHeader(XSSFWorkbook workbook) {
    XSSFSheet sheet = workbook.createSheet("NEW_SHEET_NAME");

    // Implement the header
    [...]

    return sheet;
}

请注意，代码中的注释部分（[...]）需要您根据实际情况填充。

英文:

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[] {&quot;Test2.xlsx&quot;,&quot;Test3.xlsx&quot;};
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = createSheetWithHeader(workbook);
try {
for (int f = 0; f &lt; 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 &lt; numOfSheets; i++) {
XSSFSheet tempSheet = tempWorkbook.getSheetAt(i);
int indexLastDataInserted = sheet.getLastRowNum();
int firstDataRow = getFirstDataRow(tempSheet);
Iterator&lt;Row&gt; itRow = tempSheet.rowIterator();
while(itRow.hasNext()) {
Row tempRow = itRow.next();
if (tempRow.getRowNum() &gt;= firstDataRow) {
XSSFRow row = sheet.createRow(indexLastDataInserted + 1);
Iterator&lt;Cell&gt; 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(&quot;Error reading file&quot;);
ex1.printStackTrace();
}
try (FileOutputStream outputStream = new FileOutputStream(&quot;result.xlsx&quot;)) {
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(&quot;:&quot;))).getRow() + 1;
}
return result;
}

Create the sheet with header in the method:

public static XSSFSheet createSheetWithHeader(XSSFWorkbook workbook){
XSSFSheet sheet = workbook.createSheet(&quot;NEW_SHEET_NAME&quot;);
//Implement the header
[...]
return sheet;
}

huangapple
  • 本文由 发表于 2020年8月27日 15:14:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/63610950.html
  • apache-poi
  • bulkinsert
  • excel
  • java
  • merge
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定