B4J Tutorial [ABMaterial] Using MS Excel as your reporting Engine for Database Records

Hi there...

This is a continuation of my previous posts about dynamic charts and dynamic sidebaritems. My next challenge was to use the already existing MS Excel templates for reporting, inside my ABMaterial WebApp.

I had managed to load dynamic ABMSideBarItems so that I can choose what record should be read and displayed here. Also I managed to get my charts working based on a selected record, selected from the sidebaritem, this was discussed here.

I had read some posts about the jPOI library from this forum, so I thought perhaps I could give it a try to use Ms Excel as my reporting engine instead. @DMW wrote a very nice article here about the jPOI library taking from @Erel initial post here.

So I headed on and downloaded the libraries. Well, they are huge.

I had already designed my templates, put sparklines, formulars etc to make them simple enough, but then just needed this to be accessible from my ABMaterial WebApp and also downloadable.

Figure 1: Output

So did some brain storming..

Figure 2.

The jPOI library came handy just for me to be able to do this. As a first attempt I wrote a simple method to generate a report and it worked from my B4J app. This however needed to be flexible. So I decided to write a single method that I can pass the following:

Making the Ms Excel Engine Work... inside {}

1. Report title (used to differentiate the file name)
2. SQL Query to run to get the records to be reported on.
3. The password if I want to protect the workbook and whether you want to protect the workbook.
4. Whether the workbook has formulars.
5. Which columns have formulars
6. What is the ColumnName, ColumnType of each column being reported on and the default value
7. The StartRow, I'm indexing mine from 1
8. The StartCell/Column, this is indexed from 1

My ABMButton in my navigation bar calls this method for this particular report in this particular page.

Private Sub ExcelReport_AMP()
    Dim sOriginalTitle As String = {Title}
    Dim sTitle As String = sOriginalTitle.Replace(" ","")
    page.Pause
    Dim startRow As Int = {startRow} - 1
    Dim startCell As Int = {startCell} - 1
    Dim endCell As Int = 0
    Dim hasFormula As Boolean = {hasFormular}
    Dim protectWB As Boolean = {ProtectWorkBook}
    Dim projectPWD As String = {ProtectPwd}
    Dim qry As String = {qry}
    Dim resTot As Int
    Dim sTemplate As String = {template}
    Dim resCnt As Int
    Dim results As List
    Dim resMap As Map
    'Read arguments from LocalStorage (if any)
    Dim Year As String = ABMShared.SessionStorageRead(page, "Year")
    '*** you can define your own fields to process here..."
    Dim sProgrammeName As String
    Dim sAprProjAcc As String
    Dim sMayProjAcc As String
    Dim sJunProjAcc As String
    Dim sJulProjAcc As String
    Dim sAugProjAcc As String
    Dim sSepProjAcc As String
    Dim sOctProjAcc As String
    Dim sNovProjAcc As String
    Dim sDecProjAcc As String
    Dim sJanProjAcc As String
    Dim sFebProjAcc As String
    Dim sMarProjAcc As String
    'The number of columns to generate
    endCell = {endCell}
    'start processing the workbook
    Dim wb As PoiWorkbook
    'initialize an existing workbook
    wb.InitializeExisting(File.DirApp,sTemplate,"")
    'get the firstsheet in the workbook
    Dim wSheet As PoiSheet = wb.GetSheet(0)
    Dim wsrow As PoiRow
    Dim wsCell As PoiCell
    Dim cellCnt As Int
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL, qry, Array As String(Year))
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    'Loop throught each record read and process it
    resTot = results.size - 1
    For resCnt = 0 To resTot
        'Get the record map
        resMap = results.get(resCnt)
        resMap = IYMAnalysisSet_Compute(resMap)
        sProgrammeName = resMap.GetDefault("programmename","")
        sAprProjAcc = resMap.GetDefault("aprprojacc","0")
        sMayProjAcc = resMap.GetDefault("mayprojacc","0")
        sJunProjAcc = resMap.GetDefault("junprojacc","0")
        sJulProjAcc = resMap.GetDefault("julprojacc","0")
        sAugProjAcc = resMap.GetDefault("augprojacc","0")
        sSepProjAcc = resMap.GetDefault("sepprojacc","0")
        sOctProjAcc = resMap.GetDefault("octprojacc","0")
        sNovProjAcc = resMap.GetDefault("novprojacc","0")
        sDecProjAcc = resMap.GetDefault("decprojacc","0")
        sJanProjAcc = resMap.GetDefault("janprojacc","0")
        sFebProjAcc = resMap.GetDefault("febprojacc","0")
        sMarProjAcc = resMap.GetDefault("marprojacc","0")
        Dim lstFlds As List
        lstFlds.Initialize
        lstFlds.Add(sProgrammeName)
        lstFlds.Add(sAprProjAcc)
        lstFlds.Add(sMayProjAcc)
        lstFlds.Add(sJunProjAcc)
        lstFlds.Add(sJulProjAcc)
        lstFlds.Add(sAugProjAcc)
        lstFlds.Add(sSepProjAcc)
        lstFlds.Add(sOctProjAcc)
        lstFlds.Add(sNovProjAcc)
        lstFlds.Add(sDecProjAcc)
        lstFlds.Add(sJanProjAcc)
        lstFlds.Add(sFebProjAcc)
        lstFlds.Add(sMarProjAcc)
        'get the excel row
        wsrow = wSheet.GetRow(startRow)
        'initialize it, there are existing rows in the file
        wsrow.IsInitialized'get the excel columns
        For cellCnt = startCell To endCell
            wsrow.GetCell(cellCnt).IsInitialized
            Select Case cellCnt
            Case 0
                wsrow.GetCell(cellCnt).ValueString = lstFlds.Get(cellCnt)
            Case 1
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 2
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 3
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 4
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 5
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 6
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 7
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 8
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 9
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 10
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 11
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            Case 12
                wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
            End Select
        Next
        startRow = startRow + 1
    Next
    If hasFormula = True Then
        'we have formulars
        Dim lastRow As Int = wSheet.LastRowNumber - 1
        'get the last row
        wsrow = wSheet.GetRow(lastRow)
        wsrow.IsInitialized
        For cellCnt = startCell To endCell
            Select Case cellCnt
            Case 1,2,3,4,5,6,7,8,9,10,11,12
                'get the cell in question
                wsCell = wsrow.GetCell(cellCnt)
                wsCell.IsInitialized
                wsCell.ValueFormula = wsCell.ValueFormula
            End Select
        Next
    End If
    'protect the sheet
    If protectWB = True Then
        Dim stSecret As String = projectPWD
        Dim jo As JavaObject = ws
        jo.RunMethod("protectSheet",Array As Object(stSecret))
    End If
    'Creating an unique date and time stamp as part of the filename.
    DateTime.DateFormat= "yyyy-MM-dd"
    DateTime.TimeFormat="HHmm"
    Dim stDateTime As String ="_" & DateTime.Date(DateTime.Now) & "_" & DateTime.Time(DateTime.now)
    Dim outFileName As String = sOriginalTitle & stDateTime & ".xlsx"
    'save the file in the reports folder
    Dim fPath As String = File.Combine(File.DirApp,"www/" & ABMShared.AppName.tolowercase & "/reports")
    wb.Save(fPath, outFileName)
    wb.Close
    page.resume
    ' we want to execute a download of the file, open the document in a new browser tab
    Dim pgLink As String = "../reports/" & outFileName
    If ws.Open Then
        'force a download of the document
        ws.Eval("window.open(arguments[0],'_blank');", Array As Object(pgLink))
        ws.Flush
    End If
End Sub

Yes, my case statements are rather cumbersome, but as the column types can be different, I had to go for the individualization of my case checks. For the next case where the formula is being checked, I'm still trying to figure out how to get a CellType from a cell so that I can remove the 1,2,3,4.... 12 there.

So this is what this method does...

1. Opens the template .xlsx file to be processed.
2. Gets the row to start at from the opened workbook
2. Reads the records to be reported on from the database.
3. For each record in the database, goes to the starting column until the end column and writes the respective data there after using the correct formatting for that column.
4. As the last row in the template has formulars, these are recalculated, only for the columns that have formulas.
5. If an option to protect the workbook is selected, this is protected using a specified password.
6. The report is saved using the title specified and the current system date.
7. This report is saved in the www\reports folder of the ABMaterial WebApp.
8. Then using the relative path of the report, the report is downloaded, the end user prompted, on a different browser tab!

That's all folks.

 

[Don Oso]

I found this today, its awesome , thanks Mashiane.