C++: Merge Excel files into One

Merging Excel files is an essential task when you need to summarize data stored in multiple Excel files. For instance, if you have sales reports for each quarter of the year, you might need to merge them into one file to get a more comprehensive view of the data for the entire year. By merging Excel files, you are able to concentrate on a single organized workbook instead of switching between multiple files. This streamlines your work process and improves efficiency. In this article, you will learn how to merge Excel files into one in C++ using Spire.XLS for C++ library.

• Merge Multiple Excel Workbooks into One in C++
• Merge Multiple Excel Worksheets into One in C++

Install Spire.XLS for C++

There are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the package from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.

Integrate Spire.XLS for C++ in a C++ Application

Merge Multiple Excel Workbooks into One in C++

You can merge multiple Excel workbooks into one by creating a new workbook, then copying worksheets in the original workbooks to the new workbook. The detailed steps are as follows:

• Put the paths of the original workbooks into a vector.
• Initialize a Workbook object to create a new workbook and clear the default worksheets in it.
• Initialize a temporary Workbook object.
• Iterate through the workbooks in the vector.
• Load the workbook into the temporary Workbook object using Workbook->LoadFromFile() method.
• Iterate through the worksheets in the workbook, then copy each worksheet from the workbook to the new workbook using Workbook->GetWorksheets()->AddCopy() method.
• Save the result workbook to file using Workbook->SaveToFile() method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;
using namespace std;

int main()
{
	//Put the paths of the workbooks into a vector
	std::vector<std::wstring> files = { L"File1.xlsx", L"File2.xlsx", L"File3.xlsx" };;

	//Initialize a Workbook object to create a new workbook
	Workbook* newWorkbook = new Workbook();
	newWorkbook->SetVersion(ExcelVersion::Version2013);
	//Clear the default worksheets
	newWorkbook->GetWorksheets()->Clear();

	//Initialize a temporary Workbook object
	Workbook* tempWorkbook = new Workbook();

	//Iterate through the workbooks in the vector
	for (auto file : files)
	{
		//Load the current workbook
		tempWorkbook->LoadFromFile(file.c_str());
		//Iterate through all worksheets in the workbook
		for (int i = 0; i < tempWorkbook->GetWorksheets()->GetCount(); i++)
		{
			Worksheet* sheet = tempWorkbook->GetWorksheets()->Get(i);
			//Copy each worksheet from the workbook to the new workbook
			(dynamic_cast<XlsWorksheetsCollection*>(newWorkbook->GetWorksheets()))->AddCopy(sheet, WorksheetCopyType::CopyAll);
		}
	}

	//Save the result workbook to file
	newWorkbook->SaveToFile(L"MergeExcelFiles.xlsx", ExcelVersion::Version2013);
	newWorkbook->Dispose();
	tempWorkbook->Dispose();
	delete newWorkbook;
	delete tempWorkbook;
}

Merge Multiple Excel Worksheets into One in C++

You can merge multiple worksheets into one worksheet by copying the used data range in the original worksheets to the destination worksheet. The following steps show you how to merge two worksheets within the same workbook into one worksheet:

• Initialize a Workbook object and load an Excel workbook using Workbook->LoadFromFile() method.
• Get the two worksheets that need to be merged using Workbook->GetWorksheets()->Get(int index) method (the sheet index here is zero-based).
• Get the used range of the second worksheet using Worksheet->GetAllocatedRange() method.
• Specify the destination range in the first worksheet using Worksheet->GetRange(int row, int column) method (the row and column indexes here are 1-based).
• Copy the used range of the second worksheet to the destination range in the first worksheet using CellRange->Copy(CellRange destRange) method.
• Remove the second worksheet from the workbook using XlsWorksheet->Remove() method.
• Save the result workbook to file using Workbook->SaveToFile() method.

#include "Spire.Xls.o.h";

using namespace Spire::Xls;
using namespace std;


int main()
{
	//Initialize a Workbook object
	Workbook* workbook = new Workbook();
	//Load an Excel workbook
	workbook->LoadFromFile(L"Sample.xlsx");

	//Get the first worksheet
	Worksheet* sheet1 = workbook->GetWorksheets()->Get(0);
	//Get the second worksheet
	Worksheet* sheet2 = workbook->GetWorksheets()->Get(1);

	//Get the used range in the second worksheet
	CellRange* sourceRange = sheet2->GetAllocatedRange();
	//Specify the destination range in the first worksheet
	CellRange* destRange = sheet1->GetRange(sheet1->GetLastRow() + 1, 1); 

	//Copy the used range of the second worksheet to the destination range in the first worksheet
	sourceRange->Copy(destRange);

	//Remove the second worksheet
	sheet2->Remove();

	//Save the result workbook to file
	workbook->SaveToFile(L"MergeExcelWorksheets.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
	delete workbook;
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.