XML tables with C++

• Download XLSX.zip - 4 MB

Introduction

Professionally I tend to work on physical "things" you can buy / sell / use. One of the tricks I've found to making development faster and easier is to design for test and calibration. For about 10 years or so I've used code like this to output arrays of values to a file I could pull into Office's Excel, or OpenOffice's Calc to save time. 

The code allows you to create an object, add "sheet" objects to it, add "table" objects to sheets and "cell" objects to tables. It also supports just enough formatting to make the resulting spreadsheets "nice enough" to be usable.

I typically then open a template spreadsheet and append my raw data sheets to the end, and let the template sheet pull the data and perform the calculations if I need graphs, etc.

Background

The code presented here is a simple implimentation that captures the "hard part" of dealing with the file format that requires trial and error. If you want to make use of it in a simple way (like I usually do) it's ready to go. If you want to grow it into a more powerful sollution the code isn't complex, and meant to be extended to do custom things. I've run this code on a PIC, streamed the file out a serial port, used it under Qt, ported to C#, run over massive real time data on Linux with the RT patch for robotics work. It's highly extendable and captures the file formating solution nicely.

Using the code

This is a simple example of pushing the data to create a single worksheet inside the document:

XmlWorkBook x;
x.Author = "Me";
XmlStyle n; // Default style.
    
x.Styles.push_back(n);

XMLWorkSheet ws;

std::string colNames [] = {
    std::string("X"), std::string("Y")
};

double pData [] = { 0, 1.1, 2, 3.2, 4, 5.1, 6, 7, 8, 9 };
ws.addTable(colNames, &s1.Name, &s3.Name, 5, 2, pData, true, 1, 1);
ws.Name = "addTableTest";

x.Sheets.push_back(ws);

std::ofstream f;
f.open("test.xml");
f << x.xml();
f.close();

In this example it creates a set of sheets with formatting:

// Make a work book.
XmlWorkBook x;
x.Author = "Me";
    
XmlStyle n; // Default style.

XmlStyle s1("s1");
s1.setBorders(1, 0, 1, 0);

XmlStyle s2("s2");
s2.setFont("Calibri", "Swiss", 11, 0xFFFF00);
s2.setInterior(0x00FFFF);

XmlStyle s3("s3");
s3.setFont("Calibri", "Swiss", 14, 0xF00F00);
s3.appendFontBold();
s3.appendFontDoubleUnderline();
    
x.Styles.push_back(n);
x.Styles.push_back(s1);
x.Styles.push_back(s2);
x.Styles.push_back(s3);

// Make sheets.
for (size_t sheets = 0; sheets < 3; sheets++)
{ 
    XMLWorkSheet s;
    std::stringstream sheetStr;
    sheetStr << "Sheet " << sheets;
    s.Name = sheetStr.str();
    
    // Make a table.
    XMLTable t;
        
    for (size_t r = 0; r < 5; r++)
    {
        XMLRow rw;
            
    
        for (size_t i = 0; i < 4; i++)
        {
            XMLCell c;
            if (i == 1) c.Style = "s1";
            if (i == 2) c.Style = "s2";
            if (i == 3) c.Style = "s3";

            std::stringstream strStream;
            strStream << (((double)i) * 1.7);
            c.Value = strStream.str();
            rw.Cells.push_back(c);
        }
        t.Rows.push_back(rw);
    }
    s.Tables.push_back(t);
    x.Sheets.push_back(s);
}

std::ofstream f;
f.open("test.xml");
f << x.xml();
f.close();

I typically wrap the API when I do a bunch of things and use << to push style characteristics to a style, << to append cells, rows, etc. This is meant to be very vanilla code just to capture the formatting problems.

History

First version