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:

Paolo SD

Member
Licensed User
Longtime User
V1.00 was uploaded to the first post. You can create number and formula cells.

Thank you. It works perfectly!!!
I lost a little time to understand that to insert a formla I have to write:
"SUM (E1: E3)" and not "=SUM (E1: E3)"
 

alienhunter

Active Member
Licensed User
Longtime User
Hi Erel ,

i got a error "jxl.common.AssertionFailed " , dont know what that is \

the excel sheet that i use is in compatibilty mode saved since
xlsx is not supported

thanks AH
 

Attachments

  • error.jpg
    error.jpg
    128.5 KB · Views: 586
  • help.jpg
    help.jpg
    19.5 KB · Views: 542

gawie007

Member
Licensed User
Longtime User
InitializeFormula

At the end of a For...Next loop of numbers, I am trying to reference the last value written so that I do not get a spike in my Calculated data.

In Excel this works: =OFFSET(INDIRECT("RC",FALSE),-1,0) - it gets the Cell value above it.
To insert this from code, I use:
B4X:
cell.InitializeFormula(3, i+4, "=OFFSET(INDIRECT(""RC"",FALSE),-1,0)")
editSheet.AddCell(cell)
Note the double quotes around RC - to result in single, double quotes in final string.
I also tried substituting the string for a string variable:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT(" &QUOTE & "RC" &QUOTE & ",FALSE),-1,0)"

The following error occurs when executing editSheet.AddCell(cell)

java.util.EmptyStackException
at java.util.Stack.pop(Stack.java:73)...


The same problem applies to:
B4X:
cell.InitializeFormula(3, i+4, "=SUM(B5+B6)")
cell.InitializeFormula(3, i+5, "=SUM(D6:D8)")

When using this (single quotes) it goes past .AddCell(cell) but fails in the spreadsheet when opened:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT('RC',FALSE),-1,0)"

When using cell.InitializeNumber there are no problems (where LastCausticLevel is an integer variable):
B4X:
cell.InitializeNumber(3, i+4, LastCausticLevel)

Excel Library Ver 1.00

Any help would be appreciated.
 
Last edited:

gawie007

Member
Licensed User
Longtime User
Hi Erel,

Code used to generate error:
B4X:
Dim FormulaString As String = "=OFFSET(INDIRECT(" &QUOTE & "RC" &QUOTE & ",FALSE),-1,0)"
cell.InitializeFormula(3, i+4, FormulaString)
editSheet.AddCell(cell)

sendgraph_writedata (B4A line: 91)


editSheet.AddCell(cell)
java.util.EmptyStackException


at java.util.Stack.pop(Stack.java:73)
at jxl.biff.formula.BinaryOperator.getOperands(BinaryOperator.java:61)
at jxl.biff.formula.StringFormulaParser.parseCurrent(StringFormulaParser.java:240)
at jxl.biff.formula.StringFormulaParser.parse(StringFormulaParser.java:113)
at jxl.biff.formula.FormulaParser.parse(FormulaParser.java:161)
at jxl.write.biff.FormulaRecord.initialize(FormulaRecord.java:160)
at jxl.write.biff.FormulaRecord.setCellDetails(FormulaRecord.java:243)
at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:1199)
at anywheresoftware.b4a.objects.WorkbookWrapper$WritableSheetWrapper.AddCell(WorkbookWrapper.java:199)
at com.imf.gsm1401.sendgraph._writedata(sendgraph.java:215)
at com.imf.gsm1401.main._btnsendgraph_click(main.java:518)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:170)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:158)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:154)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:64)
at android.view.View.performClick(View.java:4162)
at android.view.View$PerformClick.run(View.java:17082)
at android.os.Handler.handleCallback(Handler.java:615)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4867)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1007)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:774)
at dalvik.system.NativeStart.main(Native Method)

Thank you for your time!
 

gawie007

Member
Licensed User
Longtime User

Thank you Erel,
Removed the "=" sign.
That gets the formula (it looks exactly as it should, including the = which it automatically appends) into the cell which now raises some other issues.
It gives me an arbitrary result of 4 which is not in the column (or spreadsheet for that matter)and a circular reference error.
I physically entered the SAME formula in the SAME position and the expected value was displayed.

What I have observed is that when I open the spreadsheet, the original file's data is displayed and then all the new data that I have written (via Excel lib) to the new file writes over it as it is opening. (Refresh?)
I removed protected view from Excel, thinking that could be the problem but without any success.

I found this so far:
When you add a formula, this formula will be computed, but only in Excel, when you open the generated file in Excel. The cell is not computed by JExcel.
On this page: http://stackoverflow.com/questions/10872112/jexcel-formula-calculation-error?rq=1

Conclusion:
I eventually just wrote a blank string over the existing cell's calculation. This got rid of the spike at the end of the graph.

Thank you for your help!
 
Last edited:

qsrtech

Active Member
Licensed User
Longtime User
Does this library allow importing HTML into a new sheet? For example I grab a report that is formatted in html (table) and I want to put it in excel format so user can so whatever they want with it.
 

gawie007

Member
Licensed User
Longtime User
Does this library allow importing HTML into a new sheet? For example I grab a report that is formatted in html (table) and I want to put it in excel format so user can so whatever they want with it.
As far as I know, you can only put numbers(double), strings and formulae(Excel) into a spreadsheet.

I use a sheet called "data" where I write all my values to in b4a.
I then refer to the "data" from other sheets, or even the same sheet in the spreadsheet to display the data in a meaningful way i.e. graphs /different formats / calculations etc.

Even though the library is limited, a lot can be achieved using Excel - you could even use VBA or a macro to automate tasks after acquiring the data.

if you want to extract the data from the HTML first, then copy it to the spreadsheet, start here:
http://www.b4x.com/android/forum/th...web-page-into-a-text-variable.8024/#post45261
or better still:
http://www.b4x.com/android/forum/threads/parse-html-code.13629/
 
Last edited:

tdocs2

Well-Known Member
Licensed User
Longtime User
Greetings, all.

Would this be the best way to print an xls file?

B4X:
Try
  Dim i As Intent
  i.Initialize(i.ACTION_VIEW, "file://" & File.Combine(File.DirRootExternal, "1.xls"))
  i.SetType("text/xls")
  StartActivity(i)
Catch
  ToastMessageShow("No matching application.", True)
  Log(LastException)
End Try

Is the SetType - i.SetType("text/xls") - correct?

I presume the user then would get choices to open the xls with another app or print with whatever printing app they have?

Second question:

In 4.4, Print Services are included - like Cloud and HP Plugin. What happens then?

Any and all help welcomed.

Thank you in advance.
 
Status
Not open for further replies.
Top