In this article, we will introduce the following two possibilities to merge Excel files in Java:
- Merge multiple Excel worksheets into a single worksheet
- Merge multiple Excel files into a single file
The library we used:
Free Spire.XLS for Java
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.Merge multiple Excel worksheets into a single worksheet
A quick way to merge data in multiple worksheets into a single worksheet is using DataTable. Free Spire.XLS for Java provides insertDataTable() and exportDataTable() methods that allow us to quickly export data from a worksheet into a data table and then insert the data table into another worksheet. However, this method will not maintain formatting.
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);
}
}
If we want to keep formatting in worksheets, we can use CellRange.copy() method as shown in the below example.
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.Merge multiple Excel files into a single file
Free Spire.XLS for Java offers an addCopy() method for us to copy one worksheet in an Excel file to another Excel file.
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);
}
}
Top comments (2)
Having issue
String[] inputfiles = new String[]{"C:\Users\harsh\OneDrive\Desktop\may purchase.xlsx","C:\Users\harsh\OneDrive\Desktop\may sale.xlsx"};
Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: dif Version
Have installed as said in manual in eclipse still the libraries are not fetched >>