Merge Multiple Worksheets into a Single Worksheet

Sometimes, you need to combine multiple worksheets into a single worksheet. This can easily be achieved using Aspose.Cells API. This article will show you a code example that reads a source workbook and combines the data of all source worksheets into a single worksheet inside a destination workbook.

Combining Worksheets

The sample below uses the Range.copy() method to copy all source worksheets into a single sheet inside a destination workbook.

Source Workbook

You can use any source workbook. For this example, we are using a source workbook which has three worksheets.

Worksheet 1

Worksheet 2

Worksheet 3

Output Workbook

Running the following code provides a workbook with a single worksheet containing the data of all three worksheets.

The output worksheet now contains the data of all 3 source worksheets

Download Source Workbook and Output Workbook

• Source Workbook
• Output Workbook

Code Example

The following code snippet shows how to combine multiple worksheets into a single worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(CombineMultipleWorksheets.class);

Workbook workbook = new Workbook(dataDir + "source.xlsx");

Workbook destWorkbook = new Workbook();

Worksheet destSheet = destWorkbook.getWorksheets().get(0);

int TotalRowCount = 0;

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
	Worksheet sourceSheet = workbook.getWorksheets().get(i);

	Range sourceRange = sourceSheet.getCells().getMaxDisplayRange();

	Range destRange = destSheet.getCells().createRange(sourceRange.getFirstRow() + TotalRowCount,
			sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

	destRange.copy(sourceRange);

	TotalRowCount = sourceRange.getRowCount() + TotalRowCount;
}

destWorkbook.save(dataDir + "output.xlsx");

Additional Resources

You may find the Combine Multiple Workbooks into a Single Workbook article useful for more information.