Write Data into Excel Sheets in C++

Data is information stored in cells, and in MS Excel, you can use different tools to calculate or analyze them. This article will share how to create an Excel document and write data into specified cells with C++.

Below are two examples covered:

• Write Text or Number Values to Cells in C++
• Write Arrays to Specified Cell Ranges in C++

Install the C++ Library for Processing Excel

Spire.XLS for C++ is a professional Excel C++ API that can be used to create, read, write and convert Excel files in any type of C++ application. It offers object model Excel API for speeding up Excel programming in C++ platform — create new Excel documents from template, edit existing Excel documents and convert Excel files.

Click on the link below and follow the tutorial to install it.
https://www.e-iceblue.com/Tutorials/C++/Spire.XLS-for-C++/Getting-Started/How-to-Integrate-Spire.XLS-for-C++-in-a-C++-Application.html

Write Text or Number Values to Cells in C++

To assign a text value or a number value to specified cells, you can use the Worksheet->GetRange()->SetText() or Worksheet->GetRange()->SetNumberValue() method.

Sample C++ code:

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

using namespace Spire::Xls;

int main() {

    //Specify output file path and name
    std::wstring outputPath = L"Output\\";
    std::wstring outputFile = outputPath + L"WriteIndividualValuesToCells.xlsx";

    //Create a Workbook object
    Workbook* workbook = new Workbook();

    //Get the first sheet
    Worksheet* sheet = workbook->GetWorksheets()->Get(0);

    //Write text and numbers to the specified cells
    sheet->GetRange(1, 1)->SetText(L"Date");
    sheet->GetRange(1, 2)->SetText(L"Name");
    sheet->GetRange(1, 3)->SetText(L"Department");
    sheet->GetRange(1, 4)->SetText(L"Emp ID");

    sheet->GetRange(2, 1)->SetText(L"2022-11-01");
    sheet->GetRange(2, 2)->SetText(L"Smith");
    sheet->GetRange(2, 3)->SetText(L"Marketing");
    sheet->GetRange(2, 4)->SetNumberValue(1101);

    sheet->GetRange(3, 1)->SetText(L"2022-11-22");
    sheet->GetRange(3, 2)->SetText(L"Thompson");
    sheet->GetRange(3, 3)->SetText(L"Human Resource");
    sheet->GetRange(3, 4)->SetNumberValue(1102);

    sheet->GetRange(4, 1)->SetText(L"2022-12-10");
    sheet->GetRange(4, 2)->SetText(L"Russell");
    sheet->GetRange(4, 3)->SetText(L"Development");
    sheet->GetRange(4, 4)->SetNumberValue(1103);

    //Autofit column width 
    sheet->GetAllocatedRange()->AutoFitColumns();

    //Apply style to the first row
    CellStyle* style = workbook->GetStyles()->Add(L"newStyle");
    style->GetFont()->SetIsBold(true);
    sheet->GetRange(1, 1, 1, 4)->SetStyle(style);

    //Save to file
    workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
    workbook->Dispose();
}

Output:

Write data to Excel cells in C++

Write Arrays to Specified Cell Ranges in C++

The Worksheet->InsertArray() method provided by Spire.XLS for C++ allows programmers to write vectors into the specified cell range of a worksheet. Before writing arrays to worksheets, you need to convert them to vectors.

Sample C++ code:

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

using namespace Spire::Xls;
using namespace std;

int main() {

    //Specify output file path and name
    wstring outputPath = L"Output\\";
    wstring outputFile = outputPath + L"WriteArraysToCellRanges.xlsx";

    //Create a Workbook object
    Workbook* workbook = new Workbook();

    //Get the first sheet
    Worksheet* sheet = workbook->GetWorksheets()->Get(0);

    //Create a one-dimensional array
    wstring oneDimensionalArray[6] = { L"January", L"February", L"March", L"April", L"May", L"June" };

    //Convert array to vector
    vector<LPCWSTR> vec;
    for (size_t i = 0; i < sizeof(oneDimensionalArray) / sizeof(oneDimensionalArray[0]); i++)
    {
        vec.push_back(oneDimensionalArray[i].c_str());
    }

    //Insert vector to worksheet
    sheet->InsertArray(vec, 1, 1, false);

    //Create a two-dimensional array
    wstring twoDimensionalArray[4][5] = {
        {L"Name", L"Age", L"Sex", L"Dept.", L"Tel."},
        {L"John", L"25", L"Male", L"Puchase", L"654214"},
        {L"Albert", L"24", L"Male", L"Support", L"624847"},
        {L"Amy", L"26", L"Female", L"Sales", L"624758"}
    };

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

    //Split 2D array into multiple 1D vectors
    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 vector into worksheet
        sheet->InsertArray(vec_temp, 4 + i, 1, false);
    }

    //Autofit column width 
    sheet->GetAllocatedRange()->AutoFitColumns();

    //Apply style to the first row
    CellStyle* style = workbook->GetStyles()->Add(L"newStyle");
    style->GetFont()->SetIsBold(true);
    sheet->GetRange(1, 1, 1, 6)->SetStyle(style);
    sheet->GetRange(4, 1, 4, 5)->SetStyle(style);

    //Save to file
    workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
    workbook->Dispose();
}

Output:

Write arrays to Excel cells in C++

Want to learn more about the skills of working with Excel XLS or XLSX files using C++? Check out this tutorial:

Spire.XLS for C++ Program Guide Content