VB: Parsing and Writing Excel Files

DISCLAIMER: The views expressed in this story are my own and do not necessarily reflect the views of Oracle.

Overview

This article describes how to parse and write Excel files using SheetJS in Oracle Visual Builder. We will implement the following features in this example:

• JSON to Excel: Parses JSON and generates Excel (.xlsx) file
• Excel to JSON: Generates JSON based on Excel (.xlsx) file

This tool is specifically useful to import and export data into VB more efficiently by using Excel files.

Setup

Data

• First, we need sample data to test our implementation. Create a contacts.json file under the resources/data directory with sample data shown below.

[
  {
    "name": "John Smith",
    "title": "CEO",
    "company": "Example",
    "email": "john.smith@example.com"
  },
  {
    "name": "Lisa Jones",
    "title": "EVP",
    "company": "Example",
    "email": "lisa.jones@example.com"
  },
  {
    "name": "Joshua Baker",
    "title": "CTO",
    "company": "Example",
    "email": "joshua.baker@example.com"
  }
]

SheetJS

• Next, we need to import xlsx.js (from SheetJS) into the resources/js directory as shown below.

JSON

• The Metadata below includes all variables, types, action chains, and events required for this web application. Copy the following into the main-start-page.json file:

{
  "title": "",
  "description": "",
  "variables": {},
  "metadata": {},
  "types": {},
  "chains": {},
  "eventListeners": {
    "ojButtonOjAction": {
      "chains": [
        {
          "chainId": "exportExcelActionChain"
        }
      ]
    },
    "ojFilePickerOjSelect": {
      "chains": [
        {
          "chainId": "importExcelActionChain",
          "parameters": {
            "files": "[[ Array.prototype.slice.call($event.detail.files) ]]"
          }
        }
      ]
    }
  },
  "imports": {
    "components": {
      "oj-button": {
        "path": "ojs/ojbutton"
      },
      "oj-file-picker": {
        "path": "ojs/ojfilepicker"
      }
    }
  },
  "events": {}
}

HTML

• The HTML below includes diagram component with its templates. Copy the following into main-start-page.html file:

<div class="oj-flex oj-sm-flex-direction-column oj-sm-align-items-flex-start">
 <h4>JSON to Excel</h4>
 <oj-button chroming="callToAction" on-oj-action="[[$listeners.ojButtonOjAction]]" style="margin-right: 10px;">Export
    Excel (.xlsx)</oj-button>
 <h4>Excel to JSON</h4>
 <oj-file-picker select-on="click" selection-mode="single"
    accept='["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]'
    on-oj-select="[[$listeners.ojFilePickerOjSelect]]">
  <oj-button id="oj-button-1225781865-1-import" slot='trigger' chroming="callToAction">Import Excel (.xlsx)
    </oj-button>
 </oj-file-picker>
</div>

JS

• The JS below includes functions needed to initialize the diagram component. This includes functions which will be assigned to variables that are referenced by the component. Copy the following into main-start-page.js file:

define(['resources/js/xlsx', 'text!resources/data/contacts.json'], function (XLSX, jsonAsText) {
'use strict';
// Reference: SheetJS https://github.com/sheetjs/sheetjs
const EXCEL_FILE_NAME = 'contacts.xlsx';
 const SHEET_NAME = 'Sheet1';
var PageModule = function PageModule() {};
PageModule.prototype.exportExcel = function() {
  let dataArray = JSON.parse(jsonAsText);
// Create a new workbook
  var wb = XLSX.utils.book_new();
// Assign workbook properties
  wb.Props = {
   Title: "Example",
   Subject: "",
   Author: "Example",
   CreatedDate: new Date()
  };
// Create worksheet
  wb.SheetNames.push(SHEET_NAME);
// Convert JSON to worksheet
  var ws = XLSX.utils.json_to_sheet(dataArray);
// Assign sheet to workbook
  wb.Sheets[SHEET_NAME] = ws;
// Create Excel file
  XLSX.writeFile(wb, EXCEL_FILE_NAME);
 };
PageModule.prototype.importExcel = function(fileSet) {
  var readDataPromise = new Promise(function(resolve) {
   if (fileSet.length > 0) {
    var excelFile = fileSet[0];
    if (excelFile.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
     var fileReader = new FileReader();
     fileReader.onload = function(fileReadEvent) {
      var data = fileReadEvent.target.result;
      parseData(data);
     };
     fileReader.readAsArrayBuffer(excelFile);
    }
   }
  });
return readDataPromise;
 };
function parseData(arrayBuffer) {
  let dataArray = [];
// Get workbook
  var wb = XLSX.read(arrayBuffer, {
   type: 'array'
  });
// Get worksheet
  // NOTE: This assumes the default sheet name is "Sheet1"
  var ws = wb.Sheets[SHEET_NAME];
if (ws) {
   // Convert worksheet to JSON
   dataArray = XLSX.utils.sheet_to_json(ws);
  }
console.log(dataArray);
  return dataArray;
 }
return PageModule;
});

Test

• Run the application and you should see the following:

• Click on ‘Export Excel’ button. This will parse the JSON data and generate contacts.xlsx file.

• Click on ‘Import Excel’ button and select contacts.xlsx (downloaded in previous step). This will read the contacts.xlsx file and convert to JSON.

References

SheetJS