Excel files are files created by Microsoft Excel or other spreadsheet programs. They enable users to easily store, organize and analyze data in tabular form. Today, Excel files are widely used in various industries for data management, budgeting, accounting, statistical data analysis, and more. In this article, you will see how to create or edit Excel files using C++.
C++ Library to Create or Edit Excel Files
In order to create or edit Excel files in C++ applications, this article uses a third-party library — Spire.XLS for C++. Spire.XLS for C++ is a professional Excel API that can be applied to create, write, edit, convert, and read Excel files in C++ applications.
NuGet is the easiest way to integrate Spire.XLS for C++ into your application, and there are two ways to install Spire.XLS for C++ through NuGet.
1. Install Spire.XLS for C++ using Manage NuGet Packages.
- Create or open your project in Visual Studio.
- Click Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution…
- Search for “Spire.XLS.Cpp” and click install.
2. Install Spire.XLS for C++ using Package Manager Console.
- Create or open your project in Visual Studio.
- Click Tools -> NuGet Package Manager -> Package Manager Console.
- Type the command “Install-Package Spire.XLS.Cpp” and press enter.
If you can’t install the library through NuGet, you can download the package from the official site and manually import it into your project. For more details, you can check the documentation — How to Integrate Spire.XLS for C++ in a C++ Application.
Create Excel XLS or XLSX Files using C++
You can create Excel files with a wide variety of elements using Spire.XLS for C++, such as text, numeric data, images, formulas, hyperlinks, comments, shapes, and charts. The following steps explain how to create a simple Excel file with text and numeric data:
- Initialize an instance of the Workbook class.
- Get a specific worksheet by its index (zero-based) using Workbook->GetWorksheets()->Get(int) method (by default, a newly created workbook has 3 worksheets).
- Add some text and numeric data to specific cells of the worksheet using Worksheet->GetRange(int, int)->SetText() and Worksheet->GetRange(int, int)->SetNumberValue() methods.
- Set style for the header row and the remaining rows respectively.
- Autofit column width using Worksheet->GetAllocatedRange()->AutoFitColumns() method.
- Save the file to an XLS or XLSX file using Workbook->SaveToFile (LPCWSTR_S, ExcelVersion) method.
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
int main()
{
//Initialize an instance of the Workbook class
Workbook* workbook = new Workbook();
//Get the first worksheet (by default, a newly created workbook has 3 worksheets)
Worksheet* sheet = workbook->GetWorksheets()->Get(0);
//Add some text and numeric data to specific cells of the first worksheet
sheet->GetRange(1, 1)->SetText(L"Department");
sheet->GetRange(2, 1)->SetText(L"Accounting and Finance");
sheet->GetRange(3, 1)->SetText(L"Research and Development");
sheet->GetRange(4, 1)->SetText(L"Human Resources");
sheet->GetRange(5, 1)->SetText(L"Sales");
sheet->GetRange(1, 2)->SetText(L"Name");
sheet->GetRange(2, 2)->SetText(L"Martin");
sheet->GetRange(3, 2)->SetText(L"Patrick");
sheet->GetRange(4, 2)->SetText(L"Karen");
sheet->GetRange(5, 2)->SetText(L"Anthony");
sheet->GetRange(1, 3)->SetText(L"Salary");
sheet->GetRange(2, 3)->SetNumberValue(6100);
sheet->GetRange(3, 3)->SetNumberValue(7800);
sheet->GetRange(4, 3)->SetNumberValue(5400);
sheet->GetRange(5, 3)->SetNumberValue(7400);
//Get row count
int rowCount = sheet->GetRows()->GetCount();
//Get column count
int colCount = sheet->GetColumns()->GetCount();
//Iterate through the rows and columns in the worksheet
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{ //Set style for cells of the header row
sheet->GetRange(1, col)->GetStyle()->GetFont()->SetFontName(L"Calibri");
sheet->GetRange(1, col)->GetStyle()->GetFont()->SetSize(12);
sheet->GetRange(1, col)->GetStyle()->GetFont()->SetIsBold(true);
sheet->GetRange(1, col)->GetStyle()->SetHorizontalAlignment(HorizontalAlignType::Center);
sheet->GetRange(1, col)->GetStyle()->SetVerticalAlignment(VerticalAlignType::Center);
//Set style for cells of the remaining rows
sheet->GetRange(row + 1, col)->GetStyle()->GetFont()->SetFontName(L"Calibri");
sheet->GetRange(row + 1, col)->GetStyle()->GetFont()->SetSize(11);
sheet->GetRange(row + 1, col)->GetStyle()->SetHorizontalAlignment(HorizontalAlignType::Center);
sheet->GetRange(row + 1, col)->GetStyle()->SetVerticalAlignment(VerticalAlignType::Center);
}
}
//Autofit column width
sheet->GetAllocatedRange()->AutoFitColumns();
//Save the file to an XLSX file
workbook->SaveToFile(L"CreateExcel.xlsx", ExcelVersion::Version2013);
//Save the file to an XLS file
//workbook->SaveToFile(L"CreateExcel.xls", ExcelVersion::Version97to2003);
workbook->Dispose();
delete workbook;
}
Edit Excel XLS or XLSX Files using C++
You can also edit or update existing Excel files using Spire.XLS for C++. The steps below explain how to update the text of a specific cell and set a background color for it:
- Initialize an instance of the Workbook class.
- Load an XLS or XLSX File using Workbook->LoadFromFile() method.
- Get a specific worksheet by its index (zero-based) using Workbook->GetWorksheets()->Get(int) method.
- Edit the text of a specific cell using Worksheet->GetRange(int, int)->SetText() method.
- Set a background color for the cell using Worksheet->GetRange(int, int)-> GetStyle()->SetColor() method.
- Save the file to an XLS or XLSX file using Workbook->SaveToFile (LPCWSTR_S, ExcelVersion) method.
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
using namespace Spire::Common;
int main()
{
//Initialize an instance of the Workbook class
Workbook* workbook = new Workbook();
//Load an XLSX or XLS file
workbook->LoadFromFile(L"CreateExcel.xlsx");
//workbook->LoadFromFile(L"CreateExcel.xls");
//Get the first worksheet
Worksheet* sheet = workbook->GetWorksheets()->Get(0);
//Update the text of the cell [2, 1]
sheet->GetRange(2, 1)->SetText(L"Updated Cell");
//Set a background color for the cell [2, 1]
sheet->GetRange(2, 1)->GetStyle()->SetColor(Color::GetMediumSpringGreen());
//Save the file to an XLSX file
workbook->SaveToFile(L"EditExcel.xlsx", ExcelVersion::Version2013);
//Save the file to an XLS file
//workbook->SaveToFile(L"EditExcel.xls", ExcelVersion::Version97to2003);
workbook->Dispose();
delete workbook;
}
Office Dev Blog 