Open Source on IBM i

libxlsxwriter : Creating spreadsheets natively

A short while ago I stumbled over the C library libxlsxwriter from John McNamara on github. It is a C library for creating XLSX documents. More specifically it creates files in the Open Document spreadsheet format. Which can be used by Microsoft Excel but also by OpenOffice and every application which supports the Open Document standard.

Technically the file is a ZIP archive which contains various XML files and may contain some embedded resources like images.

This library makes it so easy to create such a document.

This whole project would not have interested me so much if it wasn't for the very strict ANSI C standard which has been used and the very clean API and overall design and implementation. It also has very minimal requirements and dependencies.

The only third party dependency which is not shipped with the library is zlib which can be compiled on the platform very nicely. zlib even had an upgrade to a nicer compile script. Thanks for making things easier.

The other dependency (minizip) can also be compiled to an ILE C module.

And thanks to the great coding the whole libxlsxwriter project can be compiled to ILE C modules and bound to a service program. This means that now every ILE programming language is able to create spreadsheets. =)

The character encoding was a little problematic because everything is written with the default locale which results in most cases in files with an EBCDIC CCSID. I have extend the code of the library to convert the data to UTF-8.

Also the formatting of floating point values was a little problematic as Excel expects the values to have a point as a decimal point. But many locales have a comma as a decimal point sign. To overcome this problem the environment variable LC_NUMERIC can be set to an English locale.

ADDENVVAR ENVVAR(LC_NUMERIC) VALUE('/QSYS.LIB/EN_US.LOCALE')

Note: The DECFMT job value does not change the decimal point sign in this case as the function snprintf() does not take the job value DECFMT into account but the environment variable LC_NUMERIC.

The RPG prototypes for C functions can be found in the include directory of the project.

Creating a spreadsheet looks like this:

/include 'xlsxwriter_h.rpgle'

dcl-proc main;
  dcl-s workbook pointer;
  dcl-s worksheet pointer;

  workbook = workbook_new('/home/schmidtm/test1.xlsx');

  worksheet = workbook_add_worksheet(workbook : 'Testsheet 1');

  worksheet_write_number(worksheet : 0 : 0 : 358 : *null);
  worksheet_write_number(worksheet : 1 : 0 : 35,8 : *null);
  worksheet_write_string(worksheet : 0 : 1 : 'Hello World!' : *null);
  worksheet_write_string(worksheet : 1 : 1 : 'Umlaute: äöüÄÖÜ and ß' : *null);

  workbook_close(workbook);
end-proc;


The library can be installed by entering QShell (qsh) and using the Makefile.ibm script like this

make -f Makefile.ibm

This compiles all necessary modules and binds them to a service program. You should adjust the values at the top of the Makefile.ibm to your environment.

On systems configured with EBCDIC 37 this should work out-of-the-box. On other systems you may need to convert the source file packager.c to your CCSID as there are characters in the code which will be used in filenames (specifically brackets [ and ] ) and these may get trashed in the compile step.

The library has many features. I plan to add the prototypes step by step to the project. Currently I added only the rudimentary features to the RPG prototypes.

Images are currently also not supported in my fork of the project (but are supported in the original code on github).

The project is currently hosted on Bitbucket.

Happy spreadsheeting!

Mihael

Tags : libxlsxwriter