Search code examples
androiddatabaseexcelupdating

How to update an excel sheet from Android App

I am trying to establish a simple method for retrieving reference-information from a few cells of an existing excel spreadsheet from an Android app (which is located on a secure company network drive - but could be moved to a secure cloud). Then I wish to add information to that "record" or row of the same spreadsheet.

Is a SQLite database required to do this? or can I pull the reference info directly from the existing excel sheet into a table/list in the app, and then choose to add info to the same sheet in cells relative to the selected reference info/record?

I have looked through the forum and the web and found lots of info on SQLite but nothing that quite fits my requirements.

The idea of this is to be able to find and select a record on the app and then add details (inc. photo's) to that record. So updating the excel sheet with the additional information.

The existing sheet is populated with reference information on a PC and saved to the network drive, to be later added to via the android app as above.

Any help and guidance is much appreciated.

Solution

  • There are two different versions of Excel Sheet file formats used today.

    1. Is the old fashion OLE based file ending: xls
    2. Is the new style XML based file ending: xlsx

    Both of them can be processed by java and also on android. Best practice to work with office docs in Java is the Apache POI project, which can handle both kinds of office formats.

    Her is the link to Apache POI for Excel: http://poi.apache.org/spreadsheet/index.html

    What you can do:

    If it is OLE based your only choice is to use POI. But POI will always load the hole file into RAM which can be a problem on Android Deviecs with less RAM.

    If it is XML based you can directly manipulate the data on the sheet in xml. The file format xlsx or any office doc ending on "x" is a zip file including XML Data and alot of meta informations etc. A file of this kind can be decompressed in a temp folder, you can than read the xml data and manipulate them. After this compress the temp folder into a xlsx file and you are fine. Or you can also use poi to work with the xlsx file, but also here it loads always the hole file. (I have running applications which push several thousand of rows in excelsheet using the xml technic)

    Hope that helps.

    P.S. I never tried to use Apache POI on Android, should work but I am not sure. Search on google for examples.

    Here are some links regarding XML and Zip technic:

    1. ZipFile in Android SDK: http://developer.android.com/reference/java/util/zip/ZipFile.html
    2. Offical XML Android Docs: http://developer.android.com/training/basics/network-ops/xml.html
    3. Good tutorial about xml parsing using library XMLPullParser: http://developer.android.com/training/basics/network-ops/xml.html
    4. Official XLSX Specs from Microsoft: https://msdn.microsoft.com/en-us/library/dd922181%28v=office.12%29.aspx
    5. Exmple for big Sheets directly manipulating the xml data: https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java