Wednesday, March 15, 2017

Pivot charts in LibreOffice: Part 1


Pivot tables are a powerful tool to reorganise, manipulate and summarise the data set in spreadsheets to get the valuable information from it. To get a quick visual representation of the information, pivot charts can be used. A pivot chart can be created from the output of the pivot tables, and if the pivot table gets changed, so does the pivot chart.

Support for pivot tables in LibreOffice is available for a long time, but there was no support for pivot charts until now. For the past week I was working on pivot charts in a feature branch (feature/pivotcharts) and I got to a first milestone. Pivot charts will be released in LibreOffice 5.4.

Pivot chart data provider

From development point of view, pivot charts are just like normal charts but with a different data provider (source of data), so this was the task with which I started. Normal charts use a data provider which is based around reading from cell ranges, but for pivot charts I created a new data provider, which reads the output data from the pivot table and prepares it for the chart. The data columns are mapped to data series and the data rows become the number of data series in chart (See Figure 1).

Figure1: Pivot table to pivot chart data mapping
Now what is left is naming of each axis and data series in chart. The y-axis categories are mapped to row field names in the pivot table and the data series names, which are shown in the chart are combined names of all column field names of the pivot table.

Each data point and row or column field name also has an associated number format, which needs to be assign to chart data, otherwise the the number format would not the values correctly as in pivot table (this is especially important with date and time).

Updating a pivot chart

Once I managed to do the mapping correctly, the pivot chart showed up as expected, but the pivot chart wasn't updated when I update the pivot table. So to solve this, I had to implement a listener of pivot table updates in the pivot chart data provider, and for every update send the signal to chart to update the data again (which it gets from the pivot chart data provider). The whole update procedure sounds like a ping-pong play between components, but it works quite well.


In the following video you can see the current status of development:


One of the real privileges here is working on LibreOffice for a Collabora Productivity customer who funds significant feature work. Many thanks to Nantes Métropole and Ville de Nantes for their investment here, and making this feature available to all LibreOffice users. You can read more about Nantes deployment here.

To be continued...

Monday, April 13, 2015


On March 12 (wow - that was 1 month ago) I went to FOSSASIA 2015 conference in Singapore. This is my first time visiting Singapore and a conference in Asia. Singapore is located very near the equator so it has quite a constant weather all around the year - which means hot, sometimes humid, and almost daily rainfall. I arrived a day earlier, so I could enjoy one day walking around and exploring Singapore, the very diverse people and food.

I stayed in Chinatown which is the place that is quite attractive for tourists as it has many bars and shops with Chinese merchandise.  Really convenient after a long day to get out for a beer and relax.
Pagoda street, China town in Singapore, early in the morning

The following day the conference started. On the first day there was only one track, with various and very interesting presentations. For me the most interesting were the talks about systemd, mariadb, Firefox OS and others. I also learned that knitting machines are the next big thing after 3D printing. I'm not a hardware guy but after seeing what some people make I wish I learn more about hardware in my youth.
I was quite impressed by the talk of Dr. Vivian Balakrishnan (Singapore’s Environment Minister) about open data and why it is important for a government (transparency). After the conference there was an organised event at Labrador park, where we enjoyed the barbecue while socializing.

On the second day of the conference, there were 3 or 4 specialized tracks. I mostly hanged around the "OpenTech" track which still had very diverse talks like: web development, developing methodologies, community, computer vision, etc. Interesting.

On the last day of the conference I was presenting about LibreOffice on Android (LibreOffice on Android, a development update). I made a quick introduction to the LibreOffice Viewer which is available in Google play, and after that in more detail about editing functionality we are working on currently and is sponsored by TDF.
My presentation, picture by Michael Cannon (CC BY 4.0) 
Slides can be downloaded here.

After I finished my talk I visited other tracks I did not visit before but sadly the conference concluded quite soon. I'm looking forward to next year.

Thanks to FOSSASIA organizers to organize such a wonderful conference and thanks to TDF and Collabora Productivity to make it possible for me to visit the conference.

Tuesday, August 19, 2014

LibreOffice on Android #2 - Draw and Impress documents

I am continuing working on LibreOffice on Android and now I had achieved a next milestone - support for Draw and Impress documents.

After some painful debugging experience, where an unavailable service in the Android build prevented the documents to be successfully opened, I finally managed to get a slide rendered on screen:
Impress slide rendered in the LibreOffice Android viewer
Draw and Impress documents share most of the code so if you get one working then you most likely get the other for free:
Draw page in LibreOffice Android viewer
Draw and Impress documents differ a little bit from Writer documents, as they are a collection of document parts - slides and pages (and in case of Calc - sheets). To be able to show different parts of the document I implemented a sidebar (using Android's DrawerLayout) which shows all available parts in a list. Clicking on a part in the list re-renders that part on the screen. The sidebar is hidden and can be activated using a left-to-right swipe on the left side of the screen. When activated it looks like this:
Sidebar to switch between slides/pages
In the future the static icons will be replaced with actual thumbnails of pages.

Additional to this I have worked on increasing the performance of tile rendering by auditing the life cycle of tiles and removing corner case bugs. There was a particular bug that removed corner tiles and right after that rendered the same (now missing) tiles for every redraw call (inconsistency in determining which tiles are still valid). Interestingly the impact on the performance was not so big as I thought. I am still not happy with the speed of tile rendering especially when faster scrolling a page.

All the work so far is summed up in this video.

Thanks again goes to Smoose for continuing the sponsoring of LibreOffice on Android work and my employer Collabora to make this project possible.


Wednesday, July 16, 2014

LibreOffice on Android

Thanks to Smoose, we are now able to do some real progress with the Android version of LibreOffice. The idea is to first build a LibreOffice document viewer, which is able to display any type of document that is supported by LibreOffice. Afterwards build on that and provide more features and eventually editing. The application itself should be a Android native application and use LibreOffice (interfacing through LibreOfficeKit) to provide tiles of a rendered documents and other needed pieces.

In the last couple of weeks I have been working on making this plan a reality. The first goal is to prepare the base of the application so I used the Fennec (Firefox for Android) source code. Fennec already solves many of the problems that we would need to solve - especially concerning drawing of tiles, touch handling, scrolling, tools and removed the rest, that will not be needed or needed later in development.

The calls to Gecko (rendering engine in Firefox) were replaced with our own implementation either in Java or a facade to LibreOfficeKit. By using a mock document tiles (tiles of the document that are part of the application's assets)  I was able to make the application work without actually interfacing with LibreOffice yet. With this the application  looked something like this:

At this stage the application was able to show a mock document with working scrolling and touch handling. The next big step was integration of LibreOffice and writing a JNI facade to LibreOfficeKit so that a real document rendered by LibreOffice could be show on a screen. With a big help from kendy we managed to integrate LibreOffice and correctly initialize LibreOfficeKit. After that the a real LibreOffice rendered document appeared:

See application in action video here.

Finally some results! There are some issues at the tile borders but this will be eventually resolved. The application uses OpenGL ES 2 for rendering so the user experience is smooth for the most parts (there are still things to optimize). This is the current state of the application but it is still far from complete however a lot of quite difficult technical challenges have been resolved and true development and polishing can now start.

Next steps are cleaning up and refactor a lot of code, integrate useful parts of previous attempt (LibreOffice4Android), tune tile loading and invalidation (when to load or remove which tile), making parts asynchronous to reduce blocking and improve the user experience, text selection and copy/paste, ...

I am really excited with what we have achieved and really looking forward to see where we go from here. By the time of LibreOffice 4.4 we should have a working and polished document viewer application ready. Thanks again to Smoose for funding for the work on this important step!