Android Tutorial Read / Write Excel files on Android

Status
Not open for further replies.
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:
B4X:
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.
B4X:
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.
B4X:
Dim cell As WritableCell
cell.InitializeText(1, 0, "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:

SS-2013-01-25_08.01.57.png


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.
 

Attachments

  • ExcelExample.zip
    12.8 KB · Views: 9,531
  • ExcelLibrary.zip
    13.1 KB · Views: 8,802
Last edited:

texwillerx

Member
Licensed User
Longtime User
Dear Erel,

Would you please give me an example on how to use cellformat/getpattern method of the jar (as well as the other methods).

Thanks in advance.


After some research, I came to the following point:

Dim cellformat As Object
Dim cell,range,pattern As Object
Dim r As Reflector
r.Target=moviesSheet
cell=r.RunMethod2("getCell","B3","java.lang.String")
r.Target=cell
cellformat=r.RunMethod("getCellFormat")
r.Target=cellformat
pattern=r.RunMethod("getPattern")

Now, pattern is of type
jxl.format.Pattern

How can I refer to its properties?

OK! Problem is solved.

Just use the above code with the folowing code:

r.Target=pattern
a=r.RunMethod("getDescription")

Now a contains the textual description of the pattern.

Thanks for everybody who created Reflection library and JXL package.
 
Last edited:

texwillerx

Member
Licensed User
Longtime User
While running the example program, in the watch window, the moviessheet variable as well as the other variables that I have defined (cell, cellformat, etc.) shows several members.

When I try to use access these members (e.g. cell.cellFormat) I get some errors.

For example, if I use moviesSheet.cells(1,1) then I get "Unkown member cells".

If I use cell.cellFormat, I get "Unknown type: object".

How can I access the members of these variables which are shown in the watch window?
 

texwillerx

Member
Licensed User
Longtime User
Erel, thank you very much for your reply. I think JavaObject is more appropriate to access the internal members. When used as follows, I achived perfect results:

Dim cellformat As JavaObject
Dim cell,range,pattern As JavaObject
Dim r As Reflector,a As String

cellformat.InitializeStatic("jxl.format.CellFormat")
pattern.InitializeStatic("jxl.format.Pattern")

...

r.Target=moviesSheet
a=alpha(i)& (row+1)
cell=r.RunMethod2("getCell",a,"java.lang.String")
cellformat=cell.RunMethodJO("getCellFormat",Null)
pattern=cellformat.RunMethodJO("getPattern",Null)
a=pattern.RunMethod("getDescription",Null)
 

laiberica

Member
Licensed User
Longtime User
Hello, i have a little big problem.
I upload two pics with the problem.
When i save the new workbook (copy of original) it changes the page adjust of the original, and when you print it the format printed is different.
Can you help me with this? thanks!
 

Attachments

  • Before.jpg
    Before.jpg
    55.3 KB · Views: 628
  • After.jpg
    After.jpg
    55.5 KB · Views: 599

Erel

B4X founder
Staff member
Licensed User
Longtime User

laiberica

Member
Licensed User
Longtime User
You can access SheetSettings with JavaObject and modify the sheet settings.

The API is described here: http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/jxl/SheetSettings.html

You can get a reference to the settings with:
B4X:
Dim settings As JavaObject = Sheet1
settings.RunMethod("setOrientation", Array("LANDSCAPE"))

I have an error that says:

java.lang.runtimeException:Method:setOrientation not found in:
jxl.write.biff.WritableSheetImpl

jxl.jar is in the libraries folder
note: i dont see the reference in Libs tab B4A but it is in the folder
 
Last edited:

Erel

B4X founder
Staff member
Licensed User
Longtime User
jxl.jar is in the libraries folder
note: i dont see the reference in Libs tab B4A but it is in the folder
It is referenced automatically when you add a reference to Excel library.

About the error. My code above was wrong. It should be:
B4X:
Dim jo As JavaObject = Sheet1
Dim settings As JavaObject = jo.RunMethod("getSettings", null)
settings.RunMethod("setOrientation", Array("LANDSCAPE"))
 

laiberica

Member
Licensed User
Longtime User
It is referenced automatically when you add a reference to Excel library.

About the error. My code above was wrong. It should be:
B4X:
Dim jo As JavaObject = Sheet1
Dim settings As JavaObject = jo.RunMethod("getSettings", null)
settings.RunMethod("setOrientation", Array("LANDSCAPE"))

Thanks, i understand it now.

Respect to the error now it's another whit that code

"java.lang.RuntimeException: Method: setOrientation not matched."
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I assumed that PageOrientation is an enum. This is incorrect. However it is just an example. You need to find the settings that you are looking for.

The correct code for setOrientation is:
B4X:
Dim o As JavaObject
o.InitializeStatic("jxl.format.PageOrientation")
settings.RunMethod("setOrientation", Array(o.GetField("LANDSCAPE"))
 

tdocs2

Well-Known Member
Licensed User
Longtime User
Greetings, all. Thank you in advance for answering my question.

Thank you, Erel for a great, easy to use, and very useful library.

Question:
I would like to add a description(multi-line) to the xls Worksheet. For instance, in Erel's example, the description would be "Movies by Category - dated 08/17/2014".
How could I accomplish this? I would intend to print the xls from the Android tablet.

Alternative:
If that is not possible, could the name of the sheet print?

Alternative:
Insert a row with the description above the header containing the column names???

Best regards.

Sandy
 

tdocs2

Well-Known Member
Licensed User
Longtime User
Thank you, Erel.

I suspected as much. The key to my question was adding a single or multi-line description such as "Movies by Category - dated 08/17/2014" on top of the xls or a work-around of some sort.

Best regards.

Sandy
 

msfactory

Member
Licensed User
Longtime User
I'm sorry English is poor.
In this excelexample, image as (google quickoffice), please tell me how to highlight the selected cells.
Thank you very much in advance.
 

Attachments

  • Screenshot_2014-09-25-21-45-31.png
    Screenshot_2014-09-25-21-45-31.png
    83.6 KB · Views: 599
  • Screenshot_2014-09-25-21-45-31.png
    Screenshot_2014-09-25-21-45-31.png
    83.6 KB · Views: 533

msfactory

Member
Licensed User
Longtime User
Thank you, Erel
I was able to achieve the goal by using this code.

lbls = visibleRows.Get (rows)
lbls (COL) .Color = Colors.xxx

B4A is really the best.
 

agus mulyana

Member
Licensed User
Longtime User
Execuse me

Hi all, if I want to Open an excell file, from my SD Card, anyone can help me ? thank you
 
Status
Not open for further replies.
Top