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.
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.
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.
B4X:
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 SubYes, 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: