B4J Tutorial [BANano] Using ExcelJS to create Excel Reports based on existing templates

Hi Fam

I wanted to generate an excel report based on an existing template. The template should have some existing structure in it, so I need to read it and then update the portions I need and then write it back by saving it.

I have talked about reading and creating Excel reports here.

1. https://www.b4x.com/android/forum/t...-side-excel-report-generation.110582/#content

This unfortunately does not support reading existing files and updating them, though you can create and style your new report in whatever way you want.

2. https://www.b4x.com/android/forum/t...el-file-and-return-json-array.117529/#content

This was to read the contents of an existing excel sheets. Usually when you want to read the csv contents and then process them further.

For this part, whilst you can create a new workbook & sheet, I wanted to explore reading an existing excel template. For this I found this github.

https://github.com/exceljs/exceljs

 

[Mashiane]

Step 1

After adding the JS file using the Files tab of your project, we need to add it to the project.

BANano.Header.AddJavascriptFile("exceljs.min.js")

This is done in Main > AppStart.

 

[Mashiane]

Step 2

In this step we need to read the excel file we will use for reporting. This file is saved in the Assets folder as we added it via the Files tab of our project.

You can fire this process when an event is fired like a button click

'get excel file as array buffer
    Dim xlTemplate As Object = banano.Await(banano.GetFileAsArrayBuffer("./assets/BMA_Month_Attendance.xlsx", Null))
    '
    Dim wb As BANanoObject
    wb.Initialize2("ExcelJS.Workbook", Null)
    '
    Dim xlFile As BANanoObject = banano.Await(wb.GetField("xlsx").RunMethod("load", xlTemplate))

 

[Mashiane]

Step 3

Now that the complete workbook is loaded and read, we need to access the worksheet we will process. This is case sensitive. You can access multiple sheets, using different variable names of course. The name of the worksheet we want is named "Participants"

Dim sheet As BANanoObject = banano.Await(xlFile.RunMethod("getWorksheet", "Participants"))

 

[Mashiane]

Step 4

4.1 Write cell value
We have the sheet, now lets write to it and style some cells

'name of entity
    sheet.RunMethod("getCell", "F8").SetField("value", "NTG Solutions")

4.2 Style a cell

We want the cell to have thin border around it. We will define a map object with the needed parameters, we will use this in all the cells we want to style. We want the border to be in each cell position, top, left, bottom, right. Our approach here is at cell level.

Dim borderAll As Map = CreateMap()
    Dim thinB As Map = CreateMap()
    thinB.Put("style", "thin")
    borderAll.Put("top", thinB)
    borderAll.Put("left", thinB)
    borderAll.Put("bottom", thinB)
    borderAll.Put("right", thinB)

Applying the style to multiple cells. rCnt below is an integer that we use to loop for example, 1..n, where n could be 20 rows)

sheet.RunMethod("getCell", $"A${rCnt}"$).SetField("border", borderAll)
        sheet.RunMethod("getCell", $"B${rCnt}"$).SetField("border", borderAll)
        sheet.RunMethod("getCell", $"C${rCnt}"$).SetField("border", borderAll)
        sheet.RunMethod("getCell", $"D${rCnt}"$).SetField("border", borderAll)
        sheet.RunMethod("getCell", $"E${rCnt}"$).SetField("border", borderAll)
        sheet.RunMethod("getCell", $"F${rCnt}"$).SetField("border", borderAll)

 

[Mashiane]

Font Name, Weight, Size & Color

At some stage I found myself wanting to have a red border around some cells. With this style I change the font size and weight (bold) and make the text red

Dim bFontRed As Map = CreateMap()
bFontRed.Put("name", "Arial")
bFontRed.Put("size", 10)
bFontRed.Put("bold", True)
Dim xcolor As Map = CreateMap("argb":"FFFF0000")
bFontRed.Put("color", xcolor)

sheet.RunMethod("getCell", $"E${npos}"$).SetField("font", bFontRed)

 

[Mashiane]

Adding Base64 images to Cell

I also wanted to add images stored in my pocketbase database. I had saved this as base64 string in the columns.

'get base64 image string
                Dim dSignature As String = beneficiary.Get(tDay)
                'define the options for the cell
                Dim imgOptions As Map = CreateMap()
                imgOptions.put("base64", dSignature)
                imgOptions.put("extension", "jpeg")
                'add the image to the worksheet
                Dim ximg As BANanoObject = xlFile.RunMethod("addImage", imgOptions)
                'add the image object to its respective position
                sheet.RunMethod("addImage", Array(ximg, $"${imgPos}${rCnt}:${imgPos}${rCnt}"$))

For example, lets say we want to add an image at "A12", imgPos will be "A" and rCnt will be 12. My code here is inside a loop as I need to have an image for each row in my excel sheet.

 

[Mashiane]

Updating the workbook / worksheet & downloading it.

Now that I have finished updating the report based on the template, I needed to ensure the details are saved back. To do this, we will create a buffer, then save this as a Blob and then download this excel file

    'save the updated worksheet
    Dim xlBuffer As BANanoObject = banano.Await(wb.GetField("xlsx").RunMethod("writeBuffer", Null))
    Dim nxlFile As String = SDUIShared.DateTimeNowBackUp & $" - Attendance During ${aDates} for "$ & provName & ".xlsx"
    Dim fc As List
    fc.Initialize
    fc.Add(xlBuffer)
    Dim blob As BANanoObject
    blob.Initialize2("Blob",Array(fc, CreateMap("type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8")))
    banano.RunJavascriptMethod("saveAs",Array(blob,nxlFile))

For downloading, you need the fileSaver.js file. You will have to add it in AppStart too with BANano.Header.AddJavascriptFile.

GitHub - eligrey/FileSaver.js: An HTML5 saveAs() FileSaver implementation

An HTML5 saveAs() FileSaver implementation. Contribute to eligrey/FileSaver.js development by creating an account on GitHub.

github.com

 

[Mashiane]

More details...

Guide to ExcelJS: Excel Workbook Manager | Built In

ExcelJS is a helpful JavaScript package that acts as Excel workbook manager. This guide will help you read, manipulate and write spreadsheet data to XLSX and JSON.

builtin.com