Introduction
IBM HATS provides the ability to transform an application on the host (I-series or Z-series) into a modern web application. Building a web application involves using various out-of-the-box solutions provided by the different libraries and frameworks on both the front-end and back-end (Java-based).
This article describes the usage of one such front-end JavaScript-based library that works with Microsoft Excel files from a web application. One of the example requirements is to scrape the data from the HOST screen and export the data to an Excel sheet.
ExcelJS vs SheetJS
Multiple libraries are available to do a similar job, with the most popular ones being ExcelJS and SheetJS.
Both ExcelJS and SheetJS have a free community version and paid versions of the libraries. Below we weigh out the advantages and disadvantages when comparing the community/free versions of the libraries.
|
ExcelJS
|
SheetJS
|
|
JSON/Array of Arrays/Array of Objects ó Basic Excel Formats like XLSX, CSV, etc.
|
JSON/Array of Arrays/Array of Objects ó More excel formats, like XLSM, XLSB, etc., are supported along with Basic Excel Formats
|
|
It consists of very good documentation
(https://github.com/exceljs/exceljs#contents)
|
It consists of very good documentation
(https://github.com/SheetJS/sheetjs#table-of-contents)
|
|
It does not support VBA macros
|
It supports VBA macros
|
|
It supports the styling of excel
|
It does not support styling (in the community version)
|
|
It supports the background image setting
|
It does not support background image setting (in the community version)
|
Based on the above comparison, the developers can choose the libraries.
ExcelJS
As the documentation claims, the library –
“Read, manipulate and write spreadsheet data and styles to XLSX and JSON.”
Reverse engineered from Excel spreadsheet files as a project.”
One of the major advantages of ExcelJS over SheetJS [community versions] is the support for the styling of excel sheets.
Below we take a simple example where
- We have a host screen with a table extending beyond the standard host display of (24/80), as shown in Picture 1 and Picture 2.
Picture 1: Screenshot of a table on the HOST screen (part 1)
Picture 2: Screenshot of a table on the HOST screen (part 2)
2. We need to generate/download an excel sheet with the same data as seen in the excel.
3. The title needs to be bold and change the text color to red, as shown in Picture 3.
Picture 3: Screenshot of the ‘customer Number’ column being extracted/Scraped from the mainframe screen
The screen scraping capability of the IBM HATS tool can be used to extract the data on the HOST and convert it to JSON format.
The data extracted from the mainframe using the IBM HATS tool can now be processed to get a JSON in the below format.
[
{
"serialNo" : "000001",
"customerNumber" : "655,938",
"lastName" : "Enrique",
"init" : "PM",
"gender" : "M",
"street" : "106 E Main St",
"city" : "Llano",
"state" : "TX",
"zipcode" : "1,234",
"creditLimit" : "100",
"chgCode" : "2",
"balanceDue" : "300.00",
"creditDue" : "999.00",
"startDate" : "03/01/2021",
"startTime" : "15:20:04",
"dueDate" : "3/29/2021"
},
{
"serialNo" : "000002",
"customerNumber" : "839,283",
"lastName" : "Jones",
"init" : "AB",
"gender" : "M",
"street" : "21B NW 135 St",
"city" : "NewYor",
"state" : "NY",
"zipcode" : "13,041",
"creditLimit" : "100",
"chgCode" : "2",
"balanceDue" : "1.00",
"creditDue" : "0.00",
"startDate" : "03/29/2021",
"startTime" : "15:24:45",
"dueDate" : "3/29/2021"
},
This data in the JSON format can be fed as input to the ExcelJS to write to an Excel file.
Picture 4: Screenshot of the generated excel sheet
Since the requirement involves styling the headers in the excel sheet, it can be easily achieved with the community version of ExcelJS.
Working with ExcelJS is very simple once we have the extracted content in JSON format. A new worksheet can be created using the below snippet
const workbook = new ExcelJS.Workbook();
let worksheet = workbook.addWorksheet(sheetName);
for (let rowIndex = 0; rowIndex < fileData.length; rowIndex++) {
worksheet.addRow(fileData[rowIndex]);
}
Styles can be applied by using one of the below options
Option 1: Row-level styling
worksheet.getRow(1).font = {
color: {
argb: '00FF0000'
},
bold: true
};
Option 2: Cell level styling
worksheet.getCell('A1').font = {
color: {
argb: '00FF0000'
},
bold: true
};
The well-documented ExcelJS is very handy even for working with complex excel sheets with formulae and styling.
References
https://github.com/exceljs/exceljs
https://github.com/SheetJS/sheetjs
https://www.ibm.com/docs/en/hats/9.7.0
Contact us
For further information on Automation, Services offerings, or Technical details in IBM HATS/HACP, please write to: ZIO@hcl.com.