合并Java中的Excel文件

最新推荐文章于 2025-02-14 13:33:31 发布

翻译最新推荐文章于 2025-02-14 13:33:31 发布 · 772 阅读

文章标签：

本文详细介绍了如何在Java中利用Free Spire.XLS库合并Excel文件，包括将多个工作表合并到一个工作表以及将多个Excel文件整合成一个文件。通过DataTable和CellRange.copy()方法，可以实现数据的合并并保留格式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在本文中，我们将介绍以下两种在Java中合并Excel文件的可能性：

将多个Excel工作表合并到一个工作表中将多个Excel文件合并为一个文件

我们使用的图书馆：

Java的免费Spire.XLS

Before getting started, please download Free Spire.XLS for Java package through this link, unzip the package and then import Spire.Xls.jar from lib folder into our application.

1.将多个Excel工作表合并到一个工作表中

将多个工作表中的数据合并到一个工作表中的快速方法是使用DataTable。 Java的免费Spire。XLS提供insertDataTable（）和exportDataTable（） methods that allow us to quickly export data from a worksheet into a data table和then insert the data table into another worksheet。 However, this method 将不保留格式。

import com.spire.data.table.DataTable;
import com.spire.xls.*;

public class MergeWorksheets {
    public static void main(String[] args){
        //Create a workbook
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(0);

        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);
        //Export data in the second worksheet into a data table
        DataTable dt2 = sheet2.exportDataTable();

        //Insert the data table into the first worksheet 
        sheet1.insertDataTable(dt2, true, sheet1.getLastRow() + 1,1);

        //Save the resultant file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}

如果我们想在工作表中保持格式，可以使用CellRange.copy（）方法如下例所示。

import com.spire.xls.*;

public class MergeWorksheets {
    public static void main(String[] args){
        //Create a workbook
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet1 = workbook.getWorksheets().get(0);

        //Get the second worksheet
        Worksheet sheet2 = workbook.getWorksheets().get(1);

        //Copy data along with formatting from sheet2 into sheet1
        sheet2.getAllocatedRange().copy(sheet1.getRange().get(sheet1.getLastRow() +1, 1));

        //Save the resultant file
        workbook.saveToFile("MergeWorksheets.xlsx", ExcelVersion.Version2013);
    }
}

2.将多个Excel文件合并为一个文件

免费的Spire.XLS for Java提供了addCopy（）我们将Excel文件中的一个工作表复制到另一个Excel文件的方法。

import com.spire.xls.*;

public class MergeExcels {
    public static void main(String[] args){
        //Input Excel files
        String[] inputFiles = new String[]{"file1.xlsx","file2.xlsx"};

        //Create a new workbook
        Workbook newBook = new Workbook();
        //Clear all worksheets
        newBook.getWorksheets().clear();

        //Create another workbook
        Workbook tempBook = new Workbook();

        //Loop through the Excel files, copy worksheets in each Excel file into the new workbook
        for (String file : inputFiles)
        {
            tempBook.loadFromFile(file);
            for (Worksheet sheet : (Iterable<Worksheet>)tempBook.getWorksheets())
            {
                newBook.getWorksheets().addCopy(sheet, WorksheetCopyType.CopyAll);
            }
        }

        //Save the resultant file
        newBook.saveToFile("MergeFiles.xlsx", ExcelVersion.Version2013);
    }
}