Writing Data to Excel Documents Using C++

I. Overview

In C++ development, programmatically generating and writing data to Excel documents is a common enterprise requirement, spanning report exports, data archiving, and automated report generation. Compared to solutions that depend on Microsoft Office automation components, dedicated Excel processing libraries offer distinct advantages, including independent deployment, stable performance, and no need for an installed Office environment.

This article provides a systematic guide to implementing Excel data writing with the Spire.XLS for C++ library—covering everything from environment setup to various data-writing techniques, including single-cell updates, batch data population, and style formatting.

---

II. Environment Setup

2.1 Obtaining and Integrating the Library

Spire.XLS for C++ can be integrated via NuGet: right-click the project in Solution Explorer, select Manage NuGet Packages, search for Spire.XLS.Cpp, and install it.

2.2 Including Header Files

After installation, include the main header file in your source code:

#include "Spire.Xls.o.h"
using namespace Spire::Xls;

---

III. Core Object Model

Spire.XLS for C++ uses an object model that mirrors the hierarchical structure of an Excel document:

• Workbook – Represents the entire Excel workbook and serves as the top-level entry point for operations.
• Worksheet – Represents a single worksheet within the workbook.
• CellRange – Represents a cell or a range of cells, used for reading/writing data and applying styles.

The following code demonstrates how to create a new workbook and retrieve its first worksheet:

// Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
// Get the first worksheet (index starts from 0)
intrusive_ptr<Worksheet> sheet = 
    dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));

---

IV. Writing Data to Cells

4.1 Writing Text Values

Use Worksheet->GetRange(int row, int column) to target a specific cell (both row and column indices start at 1), then call CellRange->SetText() to write text:

// Target row 1, columns 1–3 and write headers
sheet->GetRange(1, 1)->SetText(L"Name");
sheet->GetRange(1, 2)->SetText(L"Age");
sheet->GetRange(1, 3)->SetText(L"Department");

4.2 Writing Numeric Values

For numeric data, use CellRange->SetNumberValue():

sheet->GetRange(2, 2)->SetNumberValue(29);
sheet->GetRange(3, 2)->SetNumberValue(27);

4.3 Writing Date and Time Values

Date/time values can be written as text and combined with a custom number format:

sheet->GetRange(2, 4)->SetText(L"2021-02-26");
// Apply a date format
sheet->GetRange(2, 4)->SetNumberFormat(L"yyyy-mm-dd");

4.4 Complete Example: Employee Information Table

The following example creates a worksheet containing employee records with name, age, department, and hire date:

#include "Spire.Xls.o.h"
using namespace Spire::Xls;

int main() {
    // Specify the output file path
    std::wstring outputPath = L"Output\\";
    std::wstring outputFile = outputPath + L"EmployeeData.xlsx";

    // Create a Workbook object
    intrusive_ptr<Workbook> workbook = new Workbook();

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

    // Write header row
    sheet->GetRange(1, 1)->SetText(L"Name");
    sheet->GetRange(1, 2)->SetText(L"Age");
    sheet->GetRange(1, 3)->SetText(L"Department");
    sheet->GetRange(1, 4)->SetText(L"Hire Date");

    // Write data rows
    sheet->GetRange(2, 1)->SetText(L"Hazel");
    sheet->GetRange(2, 2)->SetNumberValue(29);
    sheet->GetRange(2, 3)->SetText(L"Marketing");
    sheet->GetRange(2, 4)->SetText(L"2021-02-26");

    sheet->GetRange(3, 1)->SetText(L"Tina");
    sheet->GetRange(3, 2)->SetNumberValue(27);
    sheet->GetRange(3, 3)->SetText(L"Human Resource");
    sheet->GetRange(3, 4)->SetText(L"2020-07-13");

    sheet->GetRange(4, 1)->SetText(L"Amy");
    sheet->GetRange(4, 2)->SetNumberValue(35);
    sheet->GetRange(4, 3)->SetText(L"Development");
    sheet->GetRange(4, 4)->SetText(L"2019-04-01");

    // Save the workbook
    workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2013);
    workbook->Dispose();

    return 0;
}

---

V. Batch Writing

Writing data cell by cell is inefficient for large datasets. Spire.XLS for C++ provides the Worksheet->InsertArray() method, which allows you to write a std::vector of data into a specified cell range in a single operation.

Basic usage example:

// Prepare a 2D data array
wstring twoDimensionalArray[4][3] = {
    { L"Region", L"Q1 Sales", L"Q2 Sales" },
    { L"East China", L"125000", L"138000" },
    { L"North China", L"98000", L"105000" },
    { L"South China", L"112000", L"120000" }
};

// Determine row and column counts
int rowNum = sizeof(twoDimensionalArray) / sizeof(twoDimensionalArray[0]);
int columnNum = sizeof(twoDimensionalArray[0]) / sizeof(twoDimensionalArray[0][0]);

// Convert each row of the 2D array into a vector and insert it
for (size_t i = 0; i < rowNum; i++)
{
    vector<LPCWSTR> vec_temp;
    for (size_t j = 0; j < columnNum; j++)
    {
        vec_temp.push_back(twoDimensionalArray[i][j].c_str());
    }
    // Insert the row vector starting at column 1 of the target row
    sheet->InsertArray(vec_temp, 4 + i, 1, false);
}

---

VI. Important Considerations

1. Indexing convention – Row and column indices are 1-based, matching the Excel interface.

2. Memory management – Always call Dispose() on Workbook and Worksheet objects after use to properly release resources.

3. Wide strings – All text parameters must be wide strings (L"..." or std::wstring), as the library uses Unicode encoding internally.

4. File format – When saving, use the ExcelVersion enumeration to specify the output format. Supported versions include Version97to2003 (.xls) and Version2013 (.xlsx), among others.

5. Exception handling – In production code, wrap file I/O and data operations in try-catch blocks to gracefully handle potential exceptions.

---

VII. Summary

Through the three core classes—Workbook, Worksheet, and CellRange—developers can seamlessly handle everything from individual cell updates to large-scale batch imports. The library's vector-based bulk insertion mechanism, combined with its rich styling capabilities, makes generating professionally formatted Excel reports in C++ applications both highly efficient and fully controllable.