Microsoft® Excel File Merging via Java

Combine two or more Excel files in a single spreadsheet using Java code

 

Java Excel Library provides multiple ways to combine workbooks with various types of content like formulas, images, data, charts etc into a single spreadsheet document. Supported file formats include XLS, XLSX, XLSB, XLT, XLTX, XLTM, ODS, CSV, TSV and more.

Combine Excel Files with Images and Charts

The simplest way to combine two Excel files having images & charts is by calling the Workbook.combine method. It allows to merge Excel files of similar type into a single spreadsheet.

Java Code to Combine Excel Files
// load first Excel file
var book1 = new Workbook("with-charts.xlsx");
// load second Excel file into a separate instance
var book2 = new Workbook("with-images.xlsx");

// merge two workbooks
book1.combine(book2);
// save the target workbook 
book1.save("combined.xlsx");

Merge Multiple Excel Files

CellsHelper.mergeFiles method supports merging data, style and formulas of an Excel file to a new spreadsheet of same format. It is an efficient way to merge several files while using caching.

Java Code to Merge Several Excel Files
// create an Array (length=2)
String[] files = new String[2];
// specify file paths to be merged
files[0] = "Book1.xls";
files[1] = "Book2.xls";
// merge the files to save the result
CellsHelper.mergeFiles(files, "cache", "merged.xls");

Merge Excel Files by Copying Worksheets

Worksheet.copy can used to copy data and formatting from a source worksheet to another worksheet within or between workbooks. The method takes the source worksheet object as a parameter.

Java Code to Copy Worksheets between Workbooks
// Create a Workbook.
Workbook excelWorkbook0 = new Workbook(dataDir + "book1.xls");

// Create another Workbook.
Workbook excelWorkbook1 = new Workbook();

// Copy the first sheet of the first book into second book.
excelWorkbook1.getWorksheets().get(0).copy(excelWorkbook0.getWorksheets().get(0));

// Save the file.
excelWorkbook1.save(dataDir + "out.xls", FileFormatType.EXCEL_97_TO_2003);

Other Supported Merging Formats

Using Java, One can also merge many other file formats including..

CSV (Comma Separated Values)
HTML (Hyper Text Markup Language)
MHTML (Web Page Archive Format)
ODS (OpenDocument Spreadsheet File)
TSV (Tab-Separated Values)
TXT (Text Document)
XLS (Excel Binary Format)
XLSB (Binary Excel Workbook File)
XLSM (Spreadsheet File)
XLSX (OOXML Excel File)
XLT (Microsoft Excel Template)
XLTM (Excel Macro-enabled Template)