Howto output XLSX files from C++

The title of this post should really be: Howto output Office Open XML (OpenXML) spreadsheet files from native C++ with as little hassle and unwanted dependencies as possible.

We will not discuss the many other options available, both commercial and not. What we want is a low-level (no .NET) C++ (no C#) API to write (no read) XLSX files, with support for:

• multiple sheets
• sparse sheets (one cell here, one cell there)
• UTF-8 encoded strings into cells
• split panes (horizontal, vertical and both horizontal and vertical)
• basic formatting (plain, bold, “0.00” and bold dates “yyyy/mm/dd”)
• setting column widths.

The starting point was the excellent libOPC open source library by Florian Reuter, a cross-platform, open source, standard C99-based implementation of Part II (OPC) and Part III (MCE) of the ISO/IEC 29500 specification (OOXML).
As mentioned here, the library does not expose a specialized API to create Excel files yet, however there is an opc_generate tool which will create all the boilerplate code for you. Starting with one such boilerplate source file, we produced a 600-SLOC C++ API that only requires libOPC and boost::variant (include only) and allows you to write code such as:

#include "Xlsx.h"

int main() {
  Xlsx xlsx("test.xlsx");
  xlsx.setAppVersion("00.0001");
  xlsx.setAppName("Test producer");

  xlsx.addWorksheet("one");
  xlsx.addWorksheet("two");

  xlsx.sheet().set(20, 10, std::string("(20,10)"), 3); // plain format

  xlsx.sheet(1).set(, , 1.2345, 4); //  "0.00"
  xlsx.sheet(1).set(, 1, std::string("(0,1)"), 1); // bold
  xlsx.sheet(1).set(, 2, std::string("(0,2)"), 1); // bold
  xlsx.sheet(3).set(, 4, 45000, 2); // bold "yyyy/mm/dd"
  xlsx.sheet(1).set(2, 3, std::string("(2,3)"), 1); // bold
  xlsx.sheet(1).set(2, 4, std::string("012345678901234567890"), 3); // plain

  xlsx.sheet(1).setColumnWidth(5); // for all columns
  xlsx.sheet(1).setColumnWidth(, 10); // override for column 0

  xlsx.sheet(1).setSplit(1, 1);

  return ;
} // main

This archive contains the source code for the XLSX API and prebuilt libOPC (based on libopc-20526) for Debian Wheezy i386. To build and run on this platform (on other platforms you will have to configure and build libOPC first):

cd xlsx/src
bjam
../bin/src/gcc-4.7/debug/XlsxTest

The produced files can be opened fine with LibreOffice 3.5 and Microsoft Excel 2013 preview. They pass validation with this validator with one minor error (_it claims http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties should have been http://schemas.openxmlformats.org/officedocument/2006/relationships/metadata/core-properties_); the Open XML SDK 2.5 Productivity Tool provided by Microsoft reports “no validation error”.

A sample produced file can be downloaded here.

One weak point of the XLSX API (apart form missing all the exciting stuff like pivot tables, shared strings, charts, named ranges, shapes, protection, encryption, data validation, conditional formatting … you name it) is the format support. Sorry, these 4 are the formats WE needed ! If you need more formats, create a sample XLSX file with all your required formats, generate the boilerplate code with the opc_generate tool then copy the content of the create_xl_styles_xml function. You will then have access (via those funny integer handles) to all your styles.