B4J Tutorial Creating Excel Reports with jPOI - Part II

Discussion in 'B4J Tutorials' started by DMW, Jan 24, 2017.

  1. DMW

    DMW Member Licensed User

    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:

    pivotcharttemplate.png

    Here is the code to populate the data table:
    Code:
    #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.

    pivotchartreport.png

    Kind regards,
    Dennis
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice