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;
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:
XmlWorkBook x;
x.Author = "Me";
XmlStyle n;
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);
for (size_t sheets = 0; sheets < 3; sheets++)
{
XMLWorkSheet s;
std::stringstream sheetStr;
sheetStr << "Sheet " << sheets;
s.Name = sheetStr.str();
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
Phil is a Principal Software developer focusing on weird yet practical algorithms that run the gamut of embedded and desktop (PID loops, Kalman filters, FFTs, client-server SOAP bindings, ASIC design, communication protocols, game engines, robotics).
In his personal life he is a part time mad scientist, full time dad, and studies small circle jujitsu, plays guitar and piano.