B4J Tutorial Creating Excel Reports with jPOI - Part I

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

  1. DMW

    DMW Member Licensed User

    This tutorial should be viewed as an additional resource to the already published tutorials by Erel.

    Introduction
    The real power for using MS Excel workbooks as reports lays in the fact that we can create templates. A template in MS Excel is saved with the file extension *.xltx (*.xlt for older versions, 2007 and earlier) where the “t” stands for template. The key feature with it is that whenever we open it via code we create a copy of the template and the copy is also created with the file extension *.xlsx, i e the standard file format.

    For me, professional MS Excel reports, starts with templates and ends with templates. So when I approached the objective to use the library jPOI to create MS Excel reports I started out with a template. It quickly turned out to not be doable. JPOI open the *.xltx file as expected but when upon saving the file a corrupted *.xlsx is created. Although I see this as a major limitation in jPOI we can overcome it :)

    So instead of using a true template we are forced to use the native file format *.xlsx. It means that we must save the template file, in the code, under a different unique name.

    Reports are usually built with one or more charts. JPOI does not support MS Excel’s chart object model. When diving into the Java library Apache POI, jPOI is based on it, I became quite confused and never managed to find a solution.

    Advanced reports in MS Excel usually include Pivot table(s) and Pivot chart(s). These are two powerful built-in tools that can give us a holistic view of the information. Unfortunately JPOI does not support these tools.

    So in order to use charts, Pivot tables and Pivot charts we must built them manually in the templates files. Not a difficult task itself as long as we know what we are doing.

    For me it was difficult to accept that I in code must create and initialize every single cell in use. That convinced me even more to use templates in order to reduce the developing process and the code writing.

    I have created two reports, each of them based on their own template file. Given the process the code mainly focus on retrieving data and adding data to the template files. Both templates present various techniques to work with cells and to populate chart Pivot table and Pivot chart.

    Because the Pivot table and chart are updated when the file is opened in MS Excel the worksheet protection cannot be turned on. For me it’s more of a limitation with MS Excel then with jPOI.

    Report Table - Chart
    The attached screen shot - chartreport.png - shows the template file in use:

    charttemplate.png

    The following code is used:
    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 xlChartButton_Action
         
        
    Dim stDateTime As String
        
    Dim stMonth As String
        
    Dim sqlReport As String =  _
        
    "SELECT Month, Result AS Amount FROM Prod_Output Order By Month;"
     
        
    Dim inRow As Int
        
    Dim inCounter As Int = 3
         
        
    Try
     
            
    If gSQL.IsInitialized=False Then
                gSQL.InitializeSQLite(
    File.DirApp, "Production.db"True
            
    End If
         
            wbData.InitializeExisting(
    File.DirApp,"ChartReport.xlsx","")
            wsData = wbData.GetSheet(
    0)
         
            
    Cursor = gSQL.ExecQuery(sqlReport)
         
            
    Do While Cursor.NextRow     
                stMonth = 
    Cursor.GetString("Month")
                 
                
    Select Case stMonth
                    
    Case "January"    :inRow = 3
                    
    Case "February"    :inRow = 4
                    
    Case "Mars"        :inRow = 5
                    
    Case "April"    :inRow = 7
                    
    Case "May"        :inRow = 8
                    
    Case "June"        :inRow = 9
                    
    Case "July"        :inRow = 11
                    
    Case "Augusti"    :inRow = 12
                    
    Case "September":inRow = 13
                    
    Case "October"    :inRow = 15
                    
    Case "November"    :inRow = 16
                    
    Case "December"    :inRow = 17             
                
    End Select             
         
                
    If inCounter = 8 Then inCounter = 3
                 
                row = wsData.GetRow(inRow)
                row.IsInitialized
             
                rnCell = row.GetCell(inCounter)
                rnCell.IsInitialized
                rnCell.ValueNumeric = 
    Cursor.GetInt("Amount")

                inCounter = inCounter + 
    1
            
    Loop

            For i = 6 To 18 Step 4
                row = wsData.GetRow(i)
                row.IsInitialized
                
    For j = 3 To 7
                    row.GetCell(j).IsInitialized
                    row.GetCell(j).ValueFormula = row.GetCell(j).ValueFormula
                
    Next
            
    Next
     
            row = wsData.GetRow(
    19)
            row.IsInitialized
            
    For j = 3 To 7
                row.GetCell(j).IsInitialized
                row.GetCell(j).ValueFormula = row.GetCell(j).ValueFormula
            
    Next
     
            
    For i = 3 To 19
                row = wsData.GetRow(i)
                row.IsInitialized
                row.GetCell(
    8).IsInitialized
                row.GetCell(
    8).ValueFormula = row.GetCell(8).ValueFormula
            
    Next
         
            
    Dim stSecret As String = "TopSecret"
            
    Dim jo As JavaObject = wsData
            jo.RunMethod(
    "protectSheet",Array As Object(stSecret))
    '     
            '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,"Chart 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
    Finally, the attached screen shot - chartreport.png - shows the created report:

    chartreport.png

    Kind regards,
    Dennis
     
    Last edited: Jan 24, 2017
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