Android, Linux, FLOSS etc.

My code

Subscribe to a syndicated RSS feed of my blog.


Sat, 27 Apr 2019

Spreadsheet Android app

By June 17, 2011, I had published two Android apps - an app to verify US driver's license (the idea was suggested to me), and an app that could open Microsoft Access databases on Android. The database app had successfully leveraged an existing FOSS Java library, so when I was casting about for my next app, I saw that there was a library (POI) that could handle Microsoft spreadsheets. With what I now realize was enormous hubris, I decided that a spreadsheet would be my next Android app.

I wanted to make sure the library worked, so I did a simple app where I load an XLS spreadsheet and displayed some information in the console. It worked! Great! I assume XLSX will work, so I spend the next two weeks completely focused on building out the Android UI etc.

After two weeks of doing UI work, I decide to try to load some XLSX files. Uh-oh. Including all the libraries needed for that makes the Android Dalvik Executable file exceed 2^16 (65536) methods. And Android Dex files only have a 16 bit identifier for methods at that time. I do not know this, I find out the hard way (and the error message at the time was pretty obscure).

I spend some time trying to get around this and read about it, and finally throw in the towel, post the code on Github, and move on. Which was not a bad idea - I was new to Android, Android had not developed as a platform, and I released other apps which made me tens of thousands of dollars in profit.

In November 2014 Android replaces Dalvik with ART, and suddenly the 2^32 app method becomes more manageable. One thing Android programmers had been saying was that Google was suggesting use of the support library, and THAT had grown to over 10,000 methods. So Google suggested libraries that were slowly approaching a big percentage of that limit. Any how, they fixed it before it became a problem.

I was too busy for a few months to look into it, but then I jumped back into the app again. Loading XLSX now worked, cool! I spent a few months working on the spreadsheet. I was playing around with content providers at the time, so I modified it to allow loading of Android system SMS messages, contacts, call logs, calendars and such. I also tinkered with hooking it up to the Jackcess library, as my old database app UI was becoming obsolete.

But then I ran into another barrier - layout. I was laying out the spreadsheet from cell A1 and calculating width and height from there. Which means jumping to say cell AAA9999 would mean a lot of calculation before the jump.

So I put the app aside for another three years and change. Then on November 24, 2018 I take a look at it again. I start from scratch working on just the layout manager part. I rewrite the app in Kotlin, and pull in what is needed from the old Java app. I work until December 14th and get stuck again on the layout manager that I have already spent so much time on. It does is now scroll to the top or left smoothly. If I scroll fast, cells do not get filled in. I also am skipping rows and columns when scrolling.

I have some time on March 19th. I know the smooth scrolling to the top or left is probably easy to fix. So I do fix it, great. Now left is skipping cell layouts, and skipping ahead for whole rows and columns, going from P and Q to say T and U, with no R or S column. I figure those are difficult, but I have the easy one done. So I make an effort to not skip rows and columns. It takes an effort - but I do it! Now just one more left - the cells not filled in. I decide to clean up the code now some though, as it is getting unwieldy. I combine functions, I name things clearly, I create enum names for clarity.

I debug it and see what is happening. This takes a while as well. Then - it works! It had been a one-off error, it only happened when the number of pixels I scrolled was exactly how many pixels of laid out cells were off-screen. Cleaning up the code helped make it easier to find the problem. Great! Wow, I thought I was just taking another stab at it, but between March 19th and April 3rd, I fixed all three errors, including the two big ones.

So from April 3rd to April 23th I pull in the features from the 2015 code, translating into Kotlin, and sometimes improving them. Then I pushed the code to Github. From April 23rd to April 27th I put in code to handle jumps, some ODS spreadsheets, ability to search, multiple sheets over multiple tabs, ability to handle incoming spreadsheet intents, and then handling XLS/XLSX row heights and column widths, as well as some other things. Some of this code was pulled (sometimes rewritten significantly from the 2015 code).

Aside from the old XLSX and Dex problem, the layout manager had been the other hurdle. Which seems solved now. Lots of the little things needed can be put in.

Right now I am concentrating on proper spreadsheet viewing. Editing and saving I am not dealing with yet. The viewing features are the priority for the project right now, not editing and saving. I thought of doing an SMS backup app, but I think Google is getting strict about that stuff now. I already have the code written to do it. I have a git branch on my workstation not pushed up that has basic save functionality, but I want it to be done right. And if there's so many viewing features to do beforehand, those take priority. Editing and saving are way on the back burner, all other features (viewing spreadsheets) are what I am doing now.

I might check out the Jackcess library again and consider putting out a modern Android app that can load Microsoft Access databases in this framework. That might make sense as the first release of this current framework of code. Right now I am busy with my day job though, so that might be a bit.

Other than editing and saving, pull requests and patches are welcome. Please read the README with regards to that.

[/android] permanent link