I’ve spent the past few months trying to get a C# program written which uses the SDK to read cells from a Calc spreadsheet and read/write/modify images “in” cells. I got a lot of help in the LibreOffice forums for which I’m very grateful. At first I was told that it wasn’t possible, but that just made me determined to figure out how to do it, and I finally did. It took many times the amount of time that the program will end up saving me, but hopefully it will be helpful to others trying to do similar things.
Background: The Original Task
This task arose as a result of my mother passing away and leaving me and my siblings with a large condo full of things that needed to be disposed of. We sorted out the things of sentimental or monetary value, assigned item numbers to them, and put them in a spreadsheet to facilitate distribution to us siblings as well as disposal of things that none of us wants. The spreadsheet includes pictures of the items, and adding and maintaining those pictures ended up being a lot of work.
I have another post on this website (link to be added when I write it) which describes the task in detail, and which is intended for people who have a similar task (disposing of an estate) and wish to use ADDPIX as is or with minor modifications.
The ADDPIX Program
The program ADDPIX works with the spreadsheet format that we used for the task of maintaining those pictures. It can add new pictures, delete pictures, refresh pictures with new copies, and adjust the size and position of images so they fit and are centered in their cells. As is, it is very specific to this task and spreadsheet format. It would only be useful in its current state to someone facing a similar task of inventorying, documenting, and disposing of a large number of items and willing to use the same spreadsheet format that I used. However, it could be very useful as a source of code samples to someone writing a program to work with Calc spreadsheets using the LibreOffice SDK.
ADDPIX is a command-line program and displays messages in the console window, but does not have a GUI or display the LibreOffice window.
The ADDPIX program is written in C# and is easy to understand, modify, and compile. Detailed instructions for doing so are in the ADDPIX package (“addpix.zip”), in a file named “addpix.txt”. You’ll have to modify the program if you want to make changes in how it works, such as adding columns to the spreadsheet or changing the naming convention for items and images.
Using the Code in the ADDPIX Program
ADDPIX is a complete, working program that opens a LibreOffice ODS file containing a Calc spreadsheet, and:
- reads cell data
- inserts, deletes and modifies images “in” cells
- saves the modified spreadsheet
- closes it and LibreOffice
Note that images are not located “in” cells but are placed on a graphic overlay (a “DrawPage”) that is associated with a spreadsheet sheet. To the user, it appears that images are inserted into cells, but internally that is accomplished with a lot of complexity. This makes working with images programmatically quite complex, which is why ADDPIX is a valuable contribution to the relevant sample code base. It also contributes to a number of bugs in LibreOffice itself involving the manipulation of spreadsheets that contain images.
The various tasks that ADDPIX does to manipulate the spreadsheet are delineated in the code with comments to make them easy to find and copy into your code. The C# code itself is linear and simple so it is easy to understand and so the parts that do various things are pretty much independent.
Included in the ADDPIX package (“addpix.zip”) is the source and executable, a sample spreadsheet with associated sample images, and a text file (“addpix.txt”) with a detailed description of the program: what it does, how to use it, how it works, and how to use it in your own code.
Download the ADDPIX package from addpix.zip. Unzip it into “C:\ods-addpix” so everything will work as supplied.
I hope this proves useful to others.