Android Question Extract a sqlite database as an excel table.

[ssonap]

Hello, sorry for my english
I am a novice in programming

I have an application that uses a sqlite database and i want to extract a sqlite database as an excel table.
Do you have an example to help me?

Thank you

 

[DonManfred]

1. The forumsearch IS working. Why not use it to find example or Tutorial?
2. https://www.b4x.com/android/forum/threads/read-write-excel-files-on-android.25632/#content

 

[ssonap]

Hi Manfred,
I did it but I did not really find it. (or so I failed to understand )
That's why I ask directly.
I can not find a simple example of exporting sqlite to excel.
Thanks for your help

regards,

 

[DonManfred]

I do not remember seeing one.
It totally depends on your dbstructure so there can not be any example.

See the tutorial and learn how to create a sheet, add values and so on.

Read the data from your db and add the values to the sheet.

 

[moster67]

Why not extract into an CSV file (which Excel should be able to read without any problems)?
See this thread for some ideas:
https://www.b4x.com/android/forum/threads/sqlite-table-to-csv-file.58613/#post-376638

 

[ssonap]

Hello,

I tried this.
But the excel table does not appear ...
Where is the problem ?

Tanks for your help and sorry for my english

Sub CopyTable1_Click
    CopyTable
   
End Sub

Sub CopyTable
    Dim Sel As Cursor
    Dim ro As Int
    Dim d1 As Int
    Dim d2 As Int
    Dim col As Int
   
    SQL1.Initialize(File.DirInternal, "BaseTest2.db", True)
    Sel = SQL1.ExecQuery("SELECT * FROM User")
    col = Sel.ColumnCount
   
    'first we create a writable workbook.
    'the target file should be a NEW file.
    Dim newWorkbook As WritableWorkbook
   
    newWorkbook.Initialize(File.DirInternal, "test.xls")
    'newWorkbook.Initialize(File.DirRootExternal, "test.xls")
    Dim sheet1 As WritableSheet
    sheet1 = newWorkbook.AddSheet("Page1", 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
   
   
    sheet1.SetColumnWidth(1, 40)
    sheet1.SetRowHeight(0, 25)
    '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
   
    ToastMessageShow("Données exportées", False)   
    LoadTable(File.DirAssets, "test.xls")'charger le tableur excel
End Sub
Sub LoadTable(Dir As String, FileName As String)
    Dim workbook1 As ReadableWorkbook
    Dim moviesSheet As ReadableSheet
    workbook1.Initialize(Dir, FileName)
    moviesSheet = workbook1.GetSheet(0)
    If table1.IsInitialized Then
        Activity.RemoveAllViews 'remove the current table
    End If
    table1.Initialize(Me, "Table1", moviesSheet.ColumnsCount)
    table1.AddToActivity(Activity, 0, 0, 100%x, 100%y)
    For row = 0 To moviesSheet.RowsCount - 1
        Dim values(moviesSheet.ColumnsCount) As String
        For i = 0 To values.Length -1
            values(i) = moviesSheet.GetCellValue(i, row)
        Next
        If row = 0 Then
            table1.SetHeader(values)
        Else
            table1.AddRow(values)
        End If
    Next
End Sub

[/ code]

Regards