Export from SQLite Data to Excel

[junaidahmed]

How do I Export from SQLite Data to Excel in Basic4Android.pls advise and send me sample code

 

[peacemaker]

The OfficeWorker template lib allows very simple format Excel 97-2003 export - so your task is possible, it depend on SQLite requests that can be saved in simple .XLS file.

 

[fanfalveto]

i do that and runs ok

Sub Button2_Click
sq.Initialize(File.DirRootExternal&"/xxx", "data.db", False)
    sel=sq.ExecQuery("SELECT * FROM evolucion")
    Dim col As Int
    col=sel.ColumnCount
    Dim ro As Int
        'first we create a writable workbook.
    'the target file should be a NEW file.
Dim newWorkbook As WritableWorkbook
newWorkbook.Initialize(File.DirRootExternal&"/bebe/xls/","1.xls")
Dim sheet1 As WritableSheet
sheet1 = newWorkbook.AddSheet("Movies", 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

'    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
   
        ro=sel.RowCount-1
            Dim col As Int = sel.ColumnCount-1
            Log (sel)

        For row = 0 To ro
        sel.Position=row
        For co = 0 To col
            Dim cell As WritableCell
            d1=sel.GetInt2(co)
            d2=sel.GetInt2(row)
   
            cell.InitializeText(co, row , sel.GetString2(co))
            cell.SetCellFormat(rowsFormat)
            sheet1.AddCell(cell)
        Next
    Next
    'Must call write and close to save the data.
    newWorkbook.Write
    newWorkbook.Close
End Sub

with excel library

 

[ValDog]

fanfalveto, would you mind posting or sending me a copy of the OfficeWorker library files? Seems like the link is broken...

 

[fanfalveto]

read this

 

[eps]

Alternatively you can just save the data in a csv as Excel will work with this.

 

[gareththomasnz]

what library is ColumnCount from?
hmm

ColumnCount As Int [read only]
Gets the number of columns available in the result set.

Its in SQL library but I am getting unknown member

    Dim sel AS String
    sel=sq.ExecQuery("SELECT * FROM evolucion")
    Dim col As Int
    col=sel.ColumnCount
    Dim ro As Int

OK its Cursor

https://www.b4x.com/android/forum/threads/selected-row-in-webview.47901/