Android Excel File Reader Application using Basic4Android - Tutorial Part 1

[oatzaz]

Today, i will introduce a powered application in Android that views an Excel File. Some of the Android Phones today is not reliable to open and read an Excel File in their Android Phones. But, reading this tutorial makes you realize that you can program the Android to open an Excel File.

The first thing that you will consider is to copy an Excel File to the File Folder of your Basic4Android Application. You need to create an excel file like this one below and named it "book1.xls".

1. Sub
   
   Globals
   
2. Dim
   
   table1 As
   
   Table
   
3. End
   
   Sub
   

- The code above is the initialization of reading the table in an Excel file. The table1 here is the variable in my table. Note: Don't Forget to add the Library of Excel in the Library of Basic4Android.

1. Sub
   
   Activity_Create(FirstTime As
   
   Boolean
   
   )
   
2. Activity.Title = "Excel Example - Lyndon Bermoy"
   
3. Activity.AddMenuItem("Load Table"
   
   , "LoadTable"
   
   )
   
4. Activity.AddMenuItem("Save Table"
   
   , "SaveTable"
   
   )
   
5. LoadTable(File.DirAssets, "Book1.xls"
   
   )
   
6. End
   
   Sub
   

- Given the code above is the same with Form_Load in Visual Basic. In our activity it have two Menu Item, the load table and saving table.
The LoadTable(File.DirAssets, "Book1.xls"

)

syntax is getting the information of our table created in Excel.

Next, we should have a procedure of clicking our load table in the excel. Write this code:

1. Sub
   
   LoadTable_Click
   
2. If
   
   File.Exists(File.DirRootExternal, "book1.xls"
   
   ) = False
   
   Then
   
3. ToastMessageShow("Unable to open file."
   
   ,True
   
   )
   
4. Else
   
5. LoadTable(File.DirRootExternal, "book1.xls"
   
   )
   
6. End
   
   If
   
7. End
   
   Sub
   

- I the code above had not found the book1.xls then it will prompt the user "Unable to open file". Otherwise, it will load the table of book1.xls.

Here is the partial code in our Main Program in Basic4Android:

1. Sub
   
   Process_Globals
   
2. End
   
   Sub
   
3. 
   
4. Sub
   
   Globals
   
5. Dim
   
   table1 As
   
   Table
   
6. End
   
   Sub
   
7. 
   
8. Sub
   
   Activity_Create(FirstTime As
   
   Boolean
   
   )
   
9. Activity.Title = "Excel Example - Lyndon Bermoy"
   
10. Activity.AddMenuItem("Load Table"
    
    , "LoadTable"
    
    )
    
11. Activity.AddMenuItem("Save Table"
    
    , "SaveTable"
    
    )
    
12. LoadTable(File.DirAssets, "Book1.xls"
    
    )
    
13. End
    
    Sub
    
14. 
    
15. Sub
    
    LoadTable_Click
    
16. If
    
    File.Exists(File.DirRootExternal, "book1.xls"
    
    ) = False
    
    Then
    
17. ToastMessageShow("Unable to open file."
    
    , True
    
    )
    
18. Else
    
19. LoadTable(File.DirRootExternal, "book1.xls"
    
    )
    
20. End
    
    If
    
21. End
    
    Sub
    
22. 
    
23. Sub
    
    LoadTable(Dir As
    
    String
    
    , FileName As
    
    String
    
    )
    
24. Dim
    
    workbook1 As
    
    ReadableWorkbook
    
25. Dim
    
    moviesSheet As
    
    ReadableSheet
    
26. workbook1.Initialize(Dir, FileName)
    
27. moviesSheet = workbook1.GetSheet(0)
    
28. If
    
    table1.IsInitialized Then
    
29. Activity.RemoveAllViews 'remove the current table
    
30. End
    
    If
    
31. table1.Initialize(Me, "Table1"
    
    , moviesSheet.ColumnsCount)
    
32. table1.AddToActivity(Activity, 0, 0, 100%x, 100%y)
    
33. For
    
    row = 0 To
    
    moviesSheet.RowsCount - 1
    
34. Dim
    
    values(moviesSheet.ColumnsCount) As
    
    String
    
35. For
    
    i = 0 To
    
    values.Length -1
    
36. values(i) = moviesSheet.GetCellValue(i, row)
    
37. Next
    
38. If
    
    row = 0 Then
    
39. table1.SetHeader(values)
    
40. Else
    
41. table1.AddRow(values)
    
42. End
    
    If
    
43. Next
    
44. End
    
    Sub
    
45. 
    
46. Sub
    
    SaveTable_Click
    
47. 'first we create a writable workbook.
    
48. 'the target File should be a NEW File.
    
49. Dim
    
    newWorkbook As
    
    WritableWorkbook
    
50. newWorkbook.Initialize(File.DirRootExternal, "1.xls"
    
    )
    
51. Dim
    
    sheet1 As
    
    WritableSheet
    
52. sheet1 = newWorkbook.AddSheet("Movies"
    
    , 0)
    
53. 'add the headers To the sheet
    
54. 'we create a special format For the headers
    
55. Dim
    
    cellFormat As
    
    WritableCellFormat
    
56. 
    
57. cellFormat.Initialize2(cellFormat.FONT_ARIAL, 12, True
    
    , False
    
    , False
    
    , _
    
58. cellFormat.COLOR_GREEN)
    
59. cellFormat.HorizontalAlignment = cellFormat.HALIGN_CENTRE
    
60. cellFormat.SetBorder(cellFormat.BORDER_ALL, _
    
61. cellFormat.BORDER_STYLE_MEDIUM, cellFormat.COLOR_BLACK)
    
62. cellFormat.SetBorder(cellFormat.BORDER_BOTTOM, cellFormat.BORDER_STYLE_THICK, _
    
63. cellFormat.COLOR_BLUE)
    
64. cellFormat.VertivalAlignment = cellFormat.VALIGN_CENTRE
    
65. cellFormat.BackgroundColor = cellFormat.COLOR_GREY_25_PERCENT
    
66. Dim
    
    col As
    
    Int = 0
    
67. For
    
    Each
    
    lbl As
    
    Label In
    
    table1.Header
    
68. Dim
    
    cell As
    
    WritableCell
    
69. cell.InitializeText(col, 0, lbl.Text)
    
70. cell.SetCellFormat(cellFormat)
    
71. sheet1.AddCell(cell)
    
72. sheet1.SetColumnWidth(col, 15)
    
73. col = col + 1
    
74. Next
    
75. sheet1.SetColumnWidth(1, 40)
    
76. sheet1.SetRowHeight(0, 15)
    
77. 'add the data
    
78. Dim
    
    rowsFormat As
    
    WritableCellFormat
    
79. rowsFormat.Initialize
    
80. rowsFormat.HorizontalAlignment = rowsFormat.HALIGN_CENTRE
    
81. For
    
    col = 0 To
    
    table1.NumberOfColumns - 1
    
82. For
    
    row = 0 To
    
    table1.Size - 1
    
83. Dim
    
    cell As
    
    WritableCell
    
84. cell.InitializeText(col, row + 1, table1.GetValue(col, row))
    
85. cell.SetCellFormat(rowsFormat)
    
86. sheet1.AddCell(cell)
    
87. Next
    
88. Next
    
89. 'Must call write AND close To save the data.
    
90. newWorkbook.Write
    
91. newWorkbook.Close
    
92. End
    
    Sub
    
93. Sub
    
    Table1_CellClick(Col As
    
    Int, Row As
    
    Int)
    
94. table1.SetValue(Col, Row, "xxx"
    
    )
    
95. End
    
    Sub
    
96. Sub
    
    Activity_Resume
    
97. 
    
98. End
    
    Sub
    
99. 
    
100. Sub
     
     Activity_Pause (UserClosed As
     
     Boolean
     
     )
     
101. 
     
102. End
     
     Sub
     

Tomorrow I will post the complete code for this Excel File Reader Application in Android considering on how to have a formatting in Excel like TableSettings, SetColumnsWidths, ScrollChanged, AddRow, AddColumns, RemoveRow, HideRows, and many more functions to create an Excel File Application.

Best regards,

Engr. Lyndon R. Bermoy
IT Instructor/System Developer/Mobile Developer
09126450702
[email protected]

Follow and add me in my Facebook Account: www.facebook.com/donzzsky

Visit my page on Facebook at: https://www.facebook.com/BermzISware

Download