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: 8,617
  • ExcelLibrary.zip
    13.1 KB · Views: 7,832
Last edited:

Pilar-JLSineriz

Active Member
Licensed User
It's possible to change the header name of each column?? For example, change "A", "B".. by "Type 1", "Type 2" since the code? Thanks
 

Bimal Gautam

New Member
Hi! I'm new in b4a. I've download the sample and try to run. I've got following error message.

B4A version: 5.80
Parsing code. Error
Error parsing program.
Error description: Unknown type: readableworkbook
Are you missing a library reference?
Occurred on line: 37 (Main)
Dim workbook1 As ReadableWorkbook

I've download ExcelLibrary.zip and copied both file to library folder.
I also copied the jxl.jar into library folder.

please help me.
 

sailorgg

New Member
Licensed User
Dear Erel
Title line item number for a long time, can not fully display the content, how to add about worksheet scroll bar, thank you!
 

joop

Active Member
Licensed User
I tried the example with book1.xls file , see image excel_on_PC.jpg
every column is adjusted to the right column-width on the PC screen.

Excel_on_PC.jpg



When I try this on a tab, the screen is like the image excel_on_tab.jpg
Now all the columns widths are changed and not adjusted to the column widths.

excel_on_tab.jpg

When I try to change the column widths in the source nothing happens .
(first save and then load)
cellFormat.HorizontalAlignment = cellFormat.HALIGN_FILL
or sheet1.SetColumnWidth(1, Width) .

The only thing I can change is the cell value.

How can I change the columnwidth for each column ?.
 

joop

Active Member
Licensed User
Ah now I understand why nothing happened .

Yes I got it ,found the TableExample ,thanks.
 
Last edited:

ilan

Expert
Licensed User

Wolli013

Active Member
Licensed User
Sorry for my English
What is wrong?

B4X:
Sub TestEmail

    Dim Testbook1 As WritableWorkbook
    Testbook1.Initialize(File.DirRootExternal, "Excel.xls")

    Dim TestSheet As WritableSheet
    TestSheet = Testbook1.AddSheet("Test", 0)
   
    Dim TestCell As WritableCell
   
    TestCell.InitializeNumber(0, 0, 1)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeNumber(1, 0, 2)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeNumber(2, 0, 3)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeNumber(3, 0, 4)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeNumber(4, 0, 5)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeNumber(5, 0, 6)
    TestSheet.AddCell(TestCell)
    TestCell.InitializeFormula(5, 2, "8+5") 'Geht
    TestCell.InitializeFormula(5, 2,"sum(B1+D1)") 'Geht nicht
    TestSheet.AddCell(TestCell)
    Testbook1.Write
    Testbook1.Close

End Sub
 

rboeck

Well-Known Member
Licensed User
Ohne es zu prüfen: entweder du verwendest B1+D1 oder sum(B1:d1); das Innere der Summenformel sollte eine Bereichsangabe sein und nicht selbst eine Summe bilden...
 

Wolli013

Active Member
Licensed User
Danke, das erste geht B1+D1
TestCell.InitializeFormula(5, 3,"B1+D1") 'Geht

das zweite leider nicht sum(B1:d1)
TestCell.InitializeFormula(5, 4,"SUM(B1:d1)") 'Geht nicht
 

rboeck

Well-Known Member
Licensed User
Falls man es in Excel direkt schreibt, müsste man es so schreiben: "=summe(B1:d1)". Vielleicht klappt das.
 
Status
Not open for further replies.
Top