B4J Tutorial Creating Excel Reports with jPOI - Part II

Report Pivot Table - Pivot Chart
Here in the part II of the tutorial I will present a solution including Pivot table and Pivot chart.

The attached screen shot - pivotcharttemplate.png - shows the template in use:



Here is the code to populate the data table:

#Region Project Attributes 
    #MainFormWidth: -1
    #MainFormHeight: -1
    #AdditionalJar: sqlite-jdbc-3.7.2 
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Private xlChartButton As Button
    Private xlPivotButton As Button
    Private btnClose As Button
   
    Private stTitle As String

    Private cutils As ControlsUtils
   
    Private gSQL As SQL
    Private Cursor As ResultSet

    'POI Variables
    Private wbData As PoiWorkbook
    Private wsData As PoiSheet
    Private row As PoiRow
    Private rnCell As PoiCell
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.RootPane.LoadLayout("Start") 
    stTitle = "Result Report"
    MainForm.Title =stTitle   
    MainForm.Show   
End Sub


Sub xlPivotButton_Action
   
    Dim stDateTime As String
    Dim sqlReport As String =  _
    "SELECT Dept, Month, Result AS Amount FROM Prod_Output Order By Dept;"
   
    Dim inRow As Int = 3
    Dim inCounter As Int = 2
               
    Try
   
        If gSQL.IsInitialized=False Then
            gSQL.InitializeSQLite(File.DirApp, "Production.db", True)   
        End If
       
        wbData.InitializeExisting(File.DirApp,"PivotReport.xlsx","")
        wsData = wbData.GetSheet(0)
       
        Cursor = gSQL.ExecQuery(sqlReport)
       
        Do While Cursor.NextRow               
            row = wsData.GetRow(inRow)
            row.IsInitialized
            row.CreateCellString(inCounter,Cursor.GetString("Dept"))
            row.CreateCellString(inCounter+1,Cursor.GetString("Month"))
            row.CreateCellNumeric(inCounter+2,Cursor.GetInt("Amount"))
            inRow = inRow + 1           
        Loop
       
        'No protection is added because the pivot table must be updated
        'when the workbook opens in MS Excel.
   
        'Creating an unique date and time stamp as part of the filename.
        DateTime.DateFormat= "MMddyy"
        DateTime.TimeFormat="HHmmss"
        stDateTime="_" & DateTime.Date(DateTime.Now) & "_" & DateTime.Time(DateTime.now)
           
        wbData.Save(File.DirApp,"Pivot Report" & stDateTime & ".xlsx")
       
        wbData.Close
       
        cutils.ShowNotification3(stTitle, "Successfully created the report!", cutils.ICON_INFORMATION, _
        MainForm, "CENTER", 2000)
           
    Catch
        Log(LastException.Message)
        cutils.ShowNotification3(stTitle, "Unable to execute the process!", cutils.ICON_ERROR, MainForm, _
        "CENTER", 2000)
       
    End Try
   
End Sub

Sub btnClose_Action
    MainForm.Close
End Sub

Finally, the attached screen shot - pivotchartreport.png. - shows the report.



Kind regards,
Dennis