B4J: Generate Excel Reports

By | 2017-01-24

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 following screen shot shows the template in use:

The following code is used to populate the data table in the template:

#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

The following screen shot shows the final report:


Report Pivot Table – Pivot Chart
The following screen shot shows the template in use:

The following code populates the data table in the template:

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

The below screen shot shows the final report:

Despite the jPOI library’s limitations we still can achieve a professional outcome. Mainly because of the use of template files.

Kind regards,
Dennis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.