Android Excel File Reader Application using Basic4Android - Tutorial Part 1

Submitted by donbermoy on Thursday, January 16, 2014 - 00:58.
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". Excel File
  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
Tags
Android Excel File Reader
Android
Basic4android
Android Excel

Comments