B4J Tutorial Creating Excel Reports with jPOI - Part I

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:



The following code is used:

#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:



Kind regards,
Dennis