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(0).set(20, 10, std::string("(20,10)"), 3); // plain format

  xlsx.sheet(1).set(0, 0, 1.2345, 4); //  "0.00"
  xlsx.sheet(1).set(0, 1, std::string("(0,1)"), 1); // bold
  xlsx.sheet(1).set(0, 2, std::string("(0,2)"), 1); // bold
  xlsx.sheet(3).set(0, 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(0, 10); // override for column 0

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

  return 0;
} // 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.

Our internal coding standard for C++ source files dictates that 7-bit US-ASCII should be used for file encoding.

This decision is based on the fact that the current C++ standard (2003) limits characters that can be used in variable and type identifiers to ASCII letters. Although some compilers and the new (2011) C++ standard allow most Unicode code points in identifiers (basically whatever can be called a “letter” in the various scripts), the “same-glyph, different Unicode code-point syndrome” described here advises against that.

One could still allow non-ASCII characters in string constants and in comments, and this is tolerated by most modern compilers. But the decision was to be quite conservative in the current standard; in the future, as C++ 2011 is fully implemented, we might revise it.

The trouble is that sometimes non-ASCII characters sneak in, for example the euro sign €, the degree symbol ° and the dash – which looks so similar to the minus sign -.

Long story short, we needed an utility to detect non-ASCII characters in a collection of text (source) files. This utility is called checkAscii, and the C++ source code is:

/*
   @file checkAscii.cc
   @brief Detect non-ASCII characters in a text file
   @author (C) Copyright 2012 Paolo Greppi libpf.com
   @date 20120525
   @version 0.1

   no warranties whatsoever
   distribute freely and free of charge citing this:

http://wp.libpf.com/?p=626

*/

#include <iostream>
#include <fstream>
#include <cstdio>

int main(int argc, char *argv[]) {
  std::istream *in = NULL;
  std::ifstream inf;
  if (argc == 1) {
    in = &std::cin;
    std::cout << "Now checking stdin" << std::endl;
  } else if (argc == 2) {
    inf.open(argv[1]);
    if(!inf) {
      std::cerr << "Error opening input file !" << std::endl;
      return -1;
    }
    in = &inf;
    std::cout << "Now checking file " << argv[1] << std::endl;
  } else {
    std::cerr << "Only 0 or 1 argument !" << std::endl;
    return -1;
  }

  char c, bit8 = (1 << 7);
  int line(0), column(0), count(0);
  while ((c = in->get()) && (c != EOF)) {
    if (c == '\n') {
      ++line;
      column = 0;
    }
    if ((c & bit8) == bit8) {
      std::cout << "line: " << line + 1 << " column: " << column << " nonascii " << c << std::endl;       count++;     }     ++column;   }      if (argc > 1) {
    inf.close();
  }
  return count;
}

Usage is as follows:

cat mySourceFile.cc | checkAscii

or:

checkAscii mySourceFile.cc

It will print this if non-ASCII characters are found (and return the number of found non-ASCII characters):

Now checking file mySourceFile.h
line: 53 column: 26 nonascii °
line: 54 column: 27 nonascii €

or will print this (and return 0) if only ASCII characters are found:

Now checking file mySourceFile.h

We use it on large sets of files using bash and xargs as follows:

ls -1 include/*.h | xargs -d '\n' -n 1 checkAscii
ls -1 src/*.cc | xargs -d '\n' -n 1 checkAscii

Enjoy !