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

Discussion in 'B4J Tutorials' started by Mashiane, Feb 13, 2017.

  1. Mashiane

    Mashiane Expert Licensed User

    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

    Output.png

    So did some brain storming..

    Figure 2.

    reporting.png

    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.

    Code:
    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.
     
    Last edited: Feb 13, 2017
  2. Don Oso

    Don Oso Member Licensed User

    I found this today, its awesome , thanks Mashiane.
     
    Mashiane and joulongleu like this.
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