Android Tutorial Read / Write Excel files on Android

Discussion in 'Tutorials & Examples' started by Erel, Jan 25, 2013.

  1. Erel

    Erel Administrator Staff Member Licensed User

    The Excel library is a new library that wraps the open source jexcel project and allows you to read or write Excel workbooks. This library supports XLS files. The new xml based format (xslx) is not supported.

    Setup

    - Download the attached library and copy both files to the libraries folder.
    - Download jexcel library: A Java library for reading/writing Excel - Browse /jexcelapi/2.6.12 at SourceForge.net (jexcelapi_2_6_12.zip).
    - Open the zip file and copy jxl.jar to the libraries folder.

    Reading data

    Reading data is quite simple. First we create a ReadableWorkbook object. The Initialize method opens the file and reads the data.
    The next step is to create a ReadableSheet object. Calling Workbook.GetSheet(Index) returns a sheet:
    Code:
    Dim workbook1 As ReadableWorkbook
    Dim moviesSheet As ReadableSheet
    workbook1.Initialize(Dir, FileName)
    moviesSheet = workbook1.GetSheet(
    0)
    ReadableSheet.GetCellValue(Col, Row) returns the value of a cell.

    Note that all the indices (sheets, cells, rows and columns) are zero based.

    Creating a new Workbook

    The data is always written to a new file. It is not possible to directly modify an existing file.
    WritableWorkbook.Initialize creates a new file.
    The next step is to add a sheet with WritableWorkbook.AddSheet (Name, Index). The Index parameter determines the index of the new sheet.
    Code:
    Dim newWorkbook As WritableWorkbook
    newWorkbook.Initialize(
    File.DirRootExternal, "1.xls")
    Dim sheet1 As WritableSheet
    sheet1 = newWorkbook.AddSheet(
    "Movies"0)
    Adding data is done by adding WritableCell objects to the sheet.
    Code:
    Dim cell As WritableCell
    cell.InitializeText(
    10"Hello")
    sheet1.AddCell(cell)
    The above code adds a cell at B0 with the value Hello.

    You can also set the cell format. This is done with a WritableCellFormat. Note that one object can be used with multiple cells (that share the same format).
    The attached example demonstrates the usage of this object.

    The attached example first loads a workbook from the assets folder and shows the data with the Table class. When you press Save it creates a new workbook. You can copy this file (1.xls) to the desktop and see how it is formatted:

    [​IMG]

    Modifying an existing workbook

    As noted above it is not possible to directly modify a workbook file. Instead you need to create a new file that will include a copy of the original workbook.
    This is done by first loading the existing workbook with ReadableWorkbook.

    Then you should create a WritableWorkbook and call Initialize2 instead of Initialize. Initialize2 expects a ReadableWorkbook as the last parameter. It creates a copy of the given ReadableWorkbook.

    V0.91:
    - The default encoding was changed to Cp1252.
    - ReadableWorkbook.Encoding and WritableWorkbook.Encoding fields were added. You can set these fields before initializing the object in order to change the encoding.

    V1.00:
    New initialize methods:
    WritableCell.InitializeNumber - Creates a number cell.
    WritableCell.InitializeFormula - Creates a formula cell.
     

    Attached Files:

    Last edited: Jul 28, 2013
  2. logemag

    logemag Member Licensed User

    Hello Erel, when i compile the ExelExample, there is an error :

    Parsing code. 0.06
    Compiling code. 0.18
    Compiling layouts code. 0.01
    Generating R file. 0.14
    Compiling generated Java code. Error
    B4A line: 69
    cellFormat.Initialize2(cellFormat.FONT_ARIAL, 12, True, False, False,cellFormat.COLOR_GREEN)
    javac 1.7.0_09
    src\b4a\example\main.java:471: error: cannot access WritableFont
    _cellformat.Initialize2(_cellformat.FONT_ARIAL,(int)(12),anywheresoftware.b4a.keywords.Common.True,anywheresoftware.b4a.keywords.Common.False,anywheresoftware.b4a.keywords.Common.False,_cellformat.COLOR_GREEN);
    ^
    class file for jxl.write.WritableFont not found
    1 error
     
  3. Mahares

    Mahares Well Known Member Licensed User

    I do not want to play Erel, but did you do steps 2 and 3 in his setup. It is not sufficent that you copy the 2 lib files in step 1.
    Setup
    1.Download the attached library and copy both files to the libraries folder.
    2. Download jexcel library: A Java library for reading/writing Excel - Browse /jexcelapi/2.6.12 at SourceForge.net (jexcelapi_2_6_12.zip).
    3.Open the zip file and copy jxl.jar to the libraries folder.
     
  4. logemag

    logemag Member Licensed User

    oops, with the jxl.jar it's works fine... Sorry... too fast !
     
  5. fdx12345

    fdx12345 Active Member Licensed User

    Sheet selector

    Can one add a sheet selector at the bottom the the spreadsheet thus allowing multiple spreadsheets - ie like a standard Excel spreadsheet?
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    You can create workbooks with any number of sheets.
    The Excel library doesn't include any UI features. It is only responsible for reading or writing Excel files.
     
    luke2012 likes this.
  7. fdx12345

    fdx12345 Active Member Licensed User

    thank you

    I thought it may be a Table Class application with an Excel read/write front end. Other than reading/writing Excel, nothing to gain over a straight Table.

    Now if someone could come up with a spreadsheet control!!
     
  8. rboeck

    rboeck Well-Known Member Licensed User

    Foreign language characters

    Hi,

    today i tried the first time to use this library. But when i got the first german umlaut, i stopped my work. I see each value, but i cannot use the textreader and writer to make the translation from windows codepage 1252 to unicode UTF8 code. Did someone has an fast and easy way to translate a string or a map?
    Greetings from austria
    Reinhard
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    Can you upload your project with the example file that fails?
     
  10. rboeck

    rboeck Well-Known Member Licensed User

    German Umlaute in Excel file

    Hi,

    i have only added one last line in the book.xls file. The name of the book in an german excel would look like: Ein Buch mit ß, öäü sowie ÖÄÜ
    On Android i get only question marks on a background.
    Thanks for your time!
    Reinhard
     

    Attached Files:

  11. Erel

    Erel Administrator Staff Member Licensed User

    I've updated the library. See the notes at the end of the first post. It should fix this issue.
     
  12. masterleous

    masterleous Member Licensed User

    By following above method what will happen, let say i want to edit / modify file1.xls (which have only one worksheet "Sheet1"), will it copy Sheet1 to another worksheet ? or it copy whole file1.xls to another file for editing?

    thanks
     
    Last edited: Mar 10, 2013
  13. Erel

    Erel Administrator Staff Member Licensed User

    The complete workbook will be copied. You can then either modify Sheet1 or create a new sheet.
     
  14. masterleous

    masterleous Member Licensed User

  15. Creaky

    Creaky Member Licensed User

    Dear Erel,

    Would it be possible to have the "WritableCellFormat.setWrap(true)" option added to this library? That way it should be possible to use newlines in cells and have the content in cells automatically wrap over multiple lines.
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    You can modify it with Reflection library:
    Code:
    Dim r As Reflector
    r.Target = wcf
    r.RunMethod2(
    "setWrap"True"java.lang.boolean")
     
  17. palmaf

    palmaf Member Licensed User

    xls

    Hi, having an existing file xls, wishing to modify only some cells is possible ? if yes, How? Thanks

    Francesco:sign0085:
     
  18. Creaky

    Creaky Member Licensed User

    Works like a charm! Thanks :)
     
  19. Erel

    Erel Administrator Staff Member Licensed User

    It is possible. You actually create a new file based on the original one. See the last section of the tutorial in the first post.
     
  20. Paolo SD

    Paolo SD Member Licensed User

    Hi, how I can write number, date or formula using Reflector library. I have try to write number, but in excel i see single quote in front of number and the cell is in string format.
    Thanks to all.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice