B4J Code Snippet Export B4XTable to Excel File

Hello everyone, I want to share a method to be able to export a table from B4XTable to an Excel file.

This code is very basic and can be adjusted for any need.

You are free to play with its possibilities.

In this case you will need the following libraries:
-B4XTable 1.21+
-jPoi v5.1+
-XLUtils 2.6+

Public Sub ExportTableToExcel(Table as B4XTable)
    Dim xl As XLUtils : xl.Initialize
    Dim Workbook As XLWorkbookWriter = xl.CreateWriterBlank
    Dim sheet1 As XLSheetWriter = Workbook.CreateSheetWriterByName("Excel Sheet Name")
   
    Dim iColumn As Int = 0

    For Each Column As B4XTableColumn In Table.Columns
        sheet1.putString(xl.AddressZero(iColumn,0),Column.Title)
        iColumn = iColumn + 1
    Next
   
    For i = 1 To Table.Size
        Dim cInt As Int = 0
        For Each Column As B4XTableColumn In Table.Columns
            Dim Row As Object = Table.GetRow(i).Get(Column.Title)
            sheet1.putString(xl.AddressZero(cInt,i),Row)
            cInt = cInt + 1
        Next
    Next  
   
    Dim FileDialog As FileChooser : FileDialog.initialize
   
    FileDialog.InitialFileName = "FileName.xlsx"
    FileDialog.setExtensionFilter("Excel File", Array As String("*.xlsx"))
    FileDialog.Title = "Select where you want to save"
   
    Dim CompleteDir As String = FileDialog.ShowSave(MainForm)
    Dim f As String = Workbook.SaveAs(File.GetFileParent(CompleteDir), File.GetName(CompleteDir), True)
    Wait For (xl.OpenExcel(f)) Complete (Success As Boolean)
   
    End Sub

How To Use:

It's very easy, once you create the table and load the data, you can add a button to generate the excel file. It's just assigning the table you want to export to the function.

PS: The code has a FileChooser to be able to select where you want to save the file.

ExportTableToExcel(TableObj)

Thanks for viewing this post, and any tips or other ways to do this code can be shared below.

 

[yaqoob]

Thank you Brain for the excellent code. I have a problem, once the excel file starts my application closes down. Is there a way to keep the B4j application running so the user can come back for it?

 

[DonManfred]

once the excel file starts my application closes down

With which error?

 

[yaqoob]

It does not give any error. It is like normal closing

 

[LucaMs]

It does not give any error. It is like normal closing

That's just a routine, it can't affect the closure of your project. Rather it will bring Excel to foreground.
Note that it is a Resumable Sub, as it contains a "Wait For".

 

[yaqoob]

Even if I comment the "Wait For" the results will be the same it closes my application.

 

[LucaMs]

Even if I comment the "Wait For" the results will be the same it closes my application.

Can you attach the project? Otherwise attach a similar one that contains only the relevant part.

 

[LucaMs]

Even if I comment the "Wait For" the results will be the same it closes my application.

Try to run this one.

(Change
#AdditionalJar: sqlite-jdbc-3.7.2
if needed)

 

[yaqoob]

Thank you LucMs for your support. I found the problem. The issue was calling the excel button from a layout that is in Pan

Dim Pan1 As Pane
pan1.Initialize("")
pan1.LoadLayout("datalayout")

I changed the location of the Excel button to the main layout as below and it worked

MainForm.RootPane.LoadLayout("layoutMain")

Thank you