Open Source on IBM i

libxlsxwriter : Date/Time and Image Support

libxlsxwriter is a great library for creating documents in the Open Document spreadsheet format.

Even so its code is very strict (and good) ANSI C code some things does not work out-of-the-box for integration in RPG and must be tweaked a little bit.

This was also the case for image and date/time support which has now been added to the forked repository at Bitbucket.org.

Image support is pretty simple. Currently only support for PNGs has been successfully tested and it looks like this:

worksheet_insert_image(worksheet : 3 : 2 : 'logo.png');

But you can also add some options like scaling or positional offsets.

dcl-ds imageOptions likeds(lxw_image_options) inz;

imageOptions.xScale = 0.5;
imageOptions.yScale = 0.5;

worksheet_insert_image_opt(worksheet : 3 : 2 : '/home/mihael/logo512.png' : imageOptions);

To support date/time in RPG we need an additional data structure which is not part of the original libxlsxwriter project, lxw_datetime.

dcl-ds datetime likeds(lxw_datetime) inz;
dcl-s dateFormat pointer;

dateFormat = workbook_add_format(workbook);
format_set_num_format(dateFormat : 'dd.mm.yyyy');

datetime = lxw_datetime_create(%date());
worksheet_write_datetime(worksheet : x : 2 : datetime : dateFormat);

And when it is finished it looks like this:

You can find the code for the above document here.

If you compile the project you should state your CCSID on the make command like

make -f Makefile.ibm TARGET_CCSID=273 env compile bind

For easier installation a save file and an archive file with the copybooks are available in the download section.

Happy spreadsheeting!

Mihael

Tags : libxlsxwriter