OK I have a sqlite database that I must export as xls at clients request - file attached
I am aware of the threads:
https://www.b4x.com/android/forum/threads/read-write-excel-files-on-android.25632/
https://www.b4x.com/android/forum/threads/export-from-sqlite-data-to-excel.22295/
To begin, forgive my ignorance as this is my first B4A app.
Now I have added the libraries and that is fine.
I have added the table class module from the sample code
So what now? I have no idea
this is my entire Main module, export sub is the last one.
I have other modules that gather data from textfields then a module that inserts it all into the sqlite DB and display it in a table.
But this is going to insert it directly from the sqlite DB file is that correct?
I am aware of the threads:
https://www.b4x.com/android/forum/threads/read-write-excel-files-on-android.25632/
https://www.b4x.com/android/forum/threads/export-from-sqlite-data-to-excel.22295/
To begin, forgive my ignorance as this is my first B4A app.
Now I have added the libraries and that is fine.
I have added the table class module from the sample code
So what now? I have no idea
this is my entire Main module, export sub is the last one.
B4X:
#Region Project Attributes
#ApplicationLabel: B4A Example
#VersionCode: 1
#VersionName:
'SupportedOrientations possible values: unspecified, landscape or portrait.
#SupportedOrientations: landscape
#CanInstallToExternalStorage: False
#End Region
#Region Activity Attributes
#FullScreen: True
#IncludeTitle: False
#End Region
Sub Process_Globals
'These global variables will be declared once when the application starts.
'These variables can be accessed from all modules.
Dim SQL1 As SQL
Dim CurrentIndex = -1 As Int ' index of the current entry
Dim RowNumber = 0 As Int ' number of rows
Dim IDList As List ' list containing the IDs of the database
' we need it because the IDs can be different from the list indexes
' if we delete an entry its ID is lost
' Dim MR As Boolean
' Dim MRS As Boolean
' Dim MISS As Boolean
Dim ElectInsp As Boolean
Dim Sparky As Boolean
Dim Technician As Boolean
Dim Other As Boolean
Dim NN As Boolean
Dim CR As Boolean
' Dim FirstName As String
Dim MiddleName As String
Dim LastName As String
Dim PersEmail As String
Dim PerPhone As String
Dim PerCell As String
Dim PerWork As String
Dim SiteSafe As String
Dim Street As String
Dim Suburb As String
Dim City As String
Dim Rego As String
Dim EmpComp As String
Dim CompAd As String
Dim EmpTown As String
Dim Contact As String
Dim ComPhone As String
Dim ComEmail As String
End Sub
Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.
Dim FirstPanel As Panel
Dim SecondPanel As Panel
Dim ThirdPanel As Panel
Dim FourthPanel As Panel
Dim FifthPanel As Panel
Dim SixthPanel As Panel
Dim SeventhPanel As Panel
Dim EighthPanel As Panel
Dim NinthPanel As Panel
Dim PanelEdit As Panel
Dim PanelFilter As Panel
Dim btnNxt1 As Button
Private btnSave As Button
Private rdbMr As RadioButton
Private rdbMrs As RadioButton
Private rdbMiss As RadioButton
Private txtFirstName As EditText
Dim Table1 As Table
End Sub
Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
File.Delete(File.DirInternal, "persons.db") ' only for testing, removes the database
'check if the database already exists
If File.Exists(File.DirInternal, "electapp.db") = False Then
'if not, initialize it
SQL1.Initialize(File.DirInternal, "electapp.db", True)
'and create it
CreateDataBase
Else
'if yes, initialize it
SQL1.Initialize(File.DirInternal, "electapp.db", True)
End If
End If
'Do not forget to load the layout file created with the visual designer. For example:
Activity.LoadLayout("blackandwhite")
FirstPanel.Visible = True
SecondPanel.Visible = False
ThirdPanel.Visible = False
FourthPanel.Visible = False
FifthPanel.Visible = False
SixthPanel.Visible = False
SeventhPanel.Visible = False
EighthPanel.Visible = False
NinthPanel.Visible = False
PanelEdit.Visible = False
PanelFilter.Visible = False
End Sub
Sub Activity_Resume
If SQL1.IsInitialized = False Then
SQL1.Initialize(File.DirInternal, "electapp.db", True)
End If
End Sub
Sub Activity_Pause (UserClosed As Boolean)
If UserClosed Then
SQL1.Close 'if the user closes the program we close the database
End If
End Sub
Sub CreateDataBase
Dim Query As String
Query = "CREATE TABLE User (UserID INTEGER Not Null PRIMARY KEY AUTOINCREMENT," _
& " MR BOOLEAN, MRS BOOLEAN, MISS BOOLEAN, ElectInsp BOOLEAN, Sparky BOOLEAN, " _
& "ElectTech BOOLEAN, ElectOther BOOLEAN, FirstName TEXT(20), MiddleName TEXT(20), " _
& "LastName TEXT(20), PersEmail TEXT(20), PersLandLine TEXT(12), PersCellPhone TEXT(12), PersWkPhone TEXT(12), " _
& "StreetAddress TEXT(40), Suburb TEXT(20), City TEXT(20), ElecReg TEXT(20), ElecComp TEXT(30), EmpAddress TEXT(40), " _
& "EmpCity TEXT(20), EmpContactName TEXT(30), EmpCompPhone TEXT(20), EmpCompEmail TEXT(20), NN BOOLEAN, CR BOOLEAN, SiteSafe TEXT(20))"
SQL1.ExecNonQuery(Query)
End Sub
Sub ReadDataBase
Dim Row As Int
Dim Cursor1 As Cursor
'We read only the ID column and put them in a List
Cursor1 = SQL1.ExecQuery("SELECT UserID FROM User")
If Cursor1.RowCount > 0 Then 'check if entries exist
RowNumber = Cursor1.RowCount 'set the row count variable
IDList.Initialize 'initialize the ID list
For Row = 0 To RowNumber - 1
Cursor1.Position = Row 'set the Cursor to each row
IDList.Add(Cursor1.GetInt("UserID")) 'add the ID's to the ID list
Next
CurrentIndex = 0 'set the current index to 0
End If
Cursor1.Close 'close the cursor, we don't need it anymore
End Sub
'Sub BooleanToInt(Checked As Boolean) As Int
' If (Checked) Then
' Return 1
' Else
' Return 0
' End If
'End Sub
Sub AddEntry
' If (rdbMrs.Checked = True) Then
' rdbMrs.Checked = 1
' Else
' rdbMrs.Checked = 0
' End If
Dim Query As String
Query = "INSERT INTO User VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
SQL1.ExecNonQuery2(Query, Array As String(rdbMr.Checked , rdbMrs.Checked, rdbMiss.Checked, ElectInsp, Sparky, _
Technician, Other, txtFirstName.Text, MiddleName, LastName, PersEmail, _
PerPhone, PerCell, PerWork, Street, City, Suburb, Rego, EmpComp, _
CompAd, EmpTown, Contact, ComPhone, ComEmail, NN, CR, SiteSafe))
ToastMessageShow("Data Saved", False) ' confirmation for the user
End Sub
Sub btnNxt1_Click
StartActivity(PanelTwo)
End Sub
Sub ExportData
'first we create a writable workbook.
'the target file should be a NEW file.
Dim newWorkbook As WritableWorkbook
newWorkbook.Initialize(File.DirRootExternal, "1.xls")
Dim sheet1 As WritableSheet
sheet1 = newWorkbook.AddSheet("Registrees", 0)
'add the headers to the sheet
'we create a special format for the headers
Dim cellFormat As WritableCellFormat
cellFormat.Initialize2(cellFormat.FONT_ARIAL, 12, True, False, False, _
cellFormat.COLOR_GREEN)
cellFormat.HorizontalAlignment = cellFormat.HALIGN_CENTRE
cellFormat.SetBorder(cellFormat.BORDER_ALL, _
cellFormat.BORDER_STYLE_MEDIUM, cellFormat.COLOR_BLACK)
cellFormat.SetBorder(cellFormat.BORDER_BOTTOM, cellFormat.BORDER_STYLE_THICK, _
cellFormat.COLOR_BLUE)
cellFormat.VertivalAlignment = cellFormat.VALIGN_CENTRE
cellFormat.BackgroundColor = cellFormat.COLOR_GREY_25_PERCENT
Dim col As Int = 0
For Each lbl As Label In Table1.Header
Dim cell As WritableCell
cell.InitializeText(col, 0, lbl.Text)
cell.SetCellFormat(cellFormat)
sheet1.AddCell(cell)
sheet1.SetColumnWidth(col, 15)
col = col + 1
Next
sheet1.SetColumnWidth(1, 40)
sheet1.SetRowHeight(0, 15)
'add the data
Dim rowsFormat As WritableCellFormat
rowsFormat.Initialize
rowsFormat.HorizontalAlignment = rowsFormat.HALIGN_CENTRE
For col = 0 To Table1.NumberOfColumns - 1
For row = 0 To Table1.Size - 1
Dim cell As WritableCell
cell.InitializeText(col, row + 1, Table1.GetValue(col, row))
cell.SetCellFormat(rowsFormat)
sheet1.AddCell(cell)
Next
Next
'Must call write and close to save the data.
newWorkbook.Write
newWorkbook.Close
End SubI have other modules that gather data from textfields then a module that inserts it all into the sqlite DB and display it in a table.
But this is going to insert it directly from the sqlite DB file is that correct?
