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:

Wolli013

Well-Known Member
Licensed User
Longtime User
Letzte Frage:
Kann man damit überhaupt Uhrzeiten berechnen wie z.B.

Anfang = 07:00
Pause = 00:45
Ende = 15:45
Ergebniss = 08:00 Std.

Wenn nein brauche ich mir garnicht weiter den Kopf zerbrechen!
 

ilan

Expert
Licensed User
Longtime User
Letzte Frage:
Kann man damit überhaupt Uhrzeiten berechnen wie z.B.

Anfang = 07:00
Pause = 00:45
Ende = 15:45
Ergebniss = 08:00 Std.

Wenn nein brauche ich mir garnicht weiter den Kopf zerbrechen!

Meinst du mit Excel?
Sollte moeglich sein.

Also was du hier versuchst zu machen ist eine Excel Datei erstellen mit alle Funktionen und Formeln?

Du koennstest auch alles mit B4A berrechnen und die Ergebnise in die Excel Datei schreiben.
 

Wolli013

Well-Known Member
Licensed User
Longtime User
Also was du hier versuchst zu machen ist eine Excel Datei erstellen mit alle Funktionen und Formeln?
Ja genau das will ich. Die Datei muss nachher auf dem Pc bearbeitbar sein, also wenn eine neue Anfangszeit eingegeben wird das es neu und richtig berechnet wird.
Ich kriege es im moment nur mit plus minus usw. hin aber nicht mit Uhrzeiten. Vielleicht geht es ja auch nicht.
Die erstellte Excel Datei soll per Mail übergeben werden und dann auf den PC bearbeitbar bleiben. Sonst hat die Exceldatei keine Funktion, nur zur Übertragung.
 
Last edited:

ilan

Expert
Licensed User
Longtime User
also das sollte funktionieren jedoch gibt es ein Problem.
du musst Cell D1 manuell auf Time Format setzten in Excel. Dann wirst das richtige Ergebniss kriegen.

B4X:
Sub TestEmail
    Dim Testbook1 As WritableWorkbook
    Testbook1.Initialize(File.DirRootExternal, "Excel.xls")

    Dim TestSheet As WritableSheet
    TestSheet = Testbook1.AddSheet("Test", 0)

    Dim Anfang As WritableCell
    Anfang.InitializeText(0,0,"07:00")
    TestSheet.AddCell(Anfang)

    Dim Ende As WritableCell
    Ende.InitializeText(1,0,"15:45")
    TestSheet.AddCell(Ende)

    Dim Pause As WritableCell
    Pause.InitializeNumber(2,0,45)
    TestSheet.AddCell(Pause)

    Dim Summe As WritableCell
    Summe.InitializeFormula(3,0,"((B1-A1)*24*60-C1)/1440")
    TestSheet.AddCell(Summe)

    Testbook1.Write
    Testbook1.Close
End Sub

https://www.dropbox.com/s/8ybm4ms11yols0o/excel.gif?dl=0
 

OliverA

Expert
Licensed User
Longtime User
TestCell.InitializeFormula(5, 4,"SUM(B1:d1)") 'Geht nicht
This should have worked. The function (name) used (SUM, AVERAGE, etc) has to be capitalized, otherwise it will not work (at least for Excel 365 1706 build and LibreOffice Calc 5.2.4.2 . The capitalization of the actual cell names (B1, b1) does not seem to matter.

Das hätte funktionieren müssen. Alle Funktionen müssen großgeschrieben werden. Zellen Namen müssen nicht unbedingt großgeschrieben sein.
 

Wolli013

Well-Known Member
Licensed User
Longtime User
Hi, what ist wrong?
B4X:
Summe.InitializeFormula(7,4,NumberFormat("(E5-B5)*24", 2 ,2))
 

Wolli013

Well-Known Member
Licensed User
Longtime User
How one is able to do then the number
allow to round?
B4X:
    Summe.InitializeFormula(7,4,"(E5-B5)*24")
    TestSheet.AddCell(Summe)
Do not do it.
 

OliverA

Expert
Licensed User
Longtime User
Hi, what ist wrong?
B4X:
Summe.InitializeFormula(7,4,NumberFormat("(E5-B5)*24", 2 ,2))
NumberFormat is a B4X function, not an Excel function. So in this case, NumberFormat would execute before Summe.InitializeFormula places the cell contents into the Excel workbook and before Excel does any calculations. I don't think this is what you were trying to accomplish. This is before we get to @DonManfred 's correct answer as to why NumberFormat failed.

How one is able to do then the number
allow to round?
B4X:
    Summe.InitializeFormula(7,4,"(E5-B5)*24")
    TestSheet.AddCell(Summe)
Do not do it.
Try the Excel function ROUND() (I'm not in front of my developing machine and therefore cannot verify):
B4X:
    Summe.InitializeFormula(7,4,"ROUND((E5-B5)*24,2)")
    TestSheet.AddCell(Summe)
Please note that this library (jexcelapi) helps you create excel workbooks, but you still need to have some knowledge of Excel if you are trying to create proper workbooks or workbooks with various calculations (via Excel).
 

ilan

Expert
Licensed User
Longtime User

universengo

Member
Thank you for this topic. It is very helpfull for me to start my study about B4A.
But, When I use this example, I see an error. Please help me.
When I run this program. I click in the list then the value change to XXX. THen I choose Save Table. I see error as "Unfortunately, Excel Example has stopped.". Please help me. Thanks.
 

universengo

Member
You need to check the logs and post the error message.
Here is the logs:

Logger connected to: samsung SM-G9550
--------- beginning of main
--------- beginning of crash
Fatal signal 11 (SIGSEGV), code 1, fault addr 0x2e in tid 27905 (b4a.example)
 

OliverA

Expert
Licensed User
Longtime User
What version of B4A are you using? 7.01 gave me issues, but everything seems to work fine under 7.30.
 
Status
Not open for further replies.
Top