C++: Add, Move or Delete Worksheets in Excel

When creating or editing Excel files, users may need to manipulate worksheets to make the files fit their specific needs. For instance, they might need to add new worksheets to record data for different categories, move or rearrange worksheets to ensure that the data is displayed in a logical order, or delete worksheets containing unnecessary information or errors. In this article, we will explain how to add, move or delete worksheets in Excel in C++ using Spire.XLS for C++.

• Add a Worksheet to an Excel File in C++
• Move a Worksheet to Another Location in an Excel File in C++
• Delete a Worksheet from an Excel File 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

Add a Worksheet to an Excel File in C++

You can use the Workbook->GetWorksheets()->Add(LPCWSTR_S name) method to add a new worksheet to an Excel file. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Add a new worksheet with a specific name to the Excel file using the Workbook->GetWorksheets()->Add(LPCWSTR_S name) method.
• Add text to a specific cell of the worksheet using the Worksheet->GetCellRange(LPCWSTR_S name)->SetText(LPCWSTR_S value) method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;

int main()
{
	//Initialize an instance of the Workbook class
	intrusive_ptr<Workbook> workbook = new Workbook();
	//Load an Excel file
	workbook->LoadFromFile(L"Sample.xlsx");

	//Add a new worksheet with a specific name to the file
	intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Add(L"New Sheet"));
	//Add text to the worksheet
	dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B1"))->SetText(L"This is a new sheet.");
	//Autofit the width of the second column
	sheet->AutoFitColumn(2);

	//Save the result file to a specific location
	workbook->SaveToFile(L"AddWorksheet.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
}

Move a Worksheet to Another Location in an Excel File in C++

Spire.XLS for C++ provides the XlsWorksheet->MoveWorksheet(int destIndex) method which allows you to move a worksheet to from one location to another in an Excel file with ease. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Get the worksheet that you want to move using the Workbook->GetWorksheets()->Get(int index) method.
• Move the worksheet to a specific position in the Excel file using the XlsWorksheet->MoveWorksheet(int destIndex) method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;

int main()
{
	//Initialize an instance of the Workbook class
	intrusive_ptr<Workbook> workbook = new Workbook();
	//Load an Excel file
	workbook->LoadFromFile(L"AddWorksheet.xlsx");

	//Get the second worksheet
	intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(1));

	//Move the worksheet to the first position in the Excel file
	sheet->MoveWorksheet(0);

	//Save the result file to a specific location
	workbook->SaveToFile(L"MoveWorksheet.xlsx", ExcelVersion::Version2013);
	workbook->Dispose();
}

Delete a Worksheet from an Excel File in C++

Deleting a worksheet from an Excel file is also very simple, you just need to call the Workbook->GetWorksheets()->RemoveAt(int index) method. The detailed steps are as follows:

• Initialize an instance of the Workbook class.
• Load an Excel file using the Workbook->LoadFromFile(LPCWSTR_S fileName) method.
• Delete a specific worksheet from the Excel file by its index using the Workbook->GetWorksheets()->RemoveAt(int index) method.
• Save the result file to a specific location using the Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.

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

using namespace Spire::Xls;

int main()
{
	//Initialize an instance of the Workbook class
	intrusive_ptr<Workbook> workbook = new Workbook();
	//Load an Excel file
	workbook->LoadFromFile(L"AddWorksheet.xlsx");

	//Delete the second worksheet from the Excel file using its sheet index
	workbook->GetWorksheets()->RemoveAt(1);

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

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.