Recently I had come across a requirement where there was a need to export the data into multiple sheets of an Excel File.

So generally VBCS has a sample custom component to export the data from a DataProvider into CSV called oj-sample-export-data . This takes care of most of the requirements but in cases where there is a need to export data into XLSX OR to create multiple sheets, we might need to go with a different approach.

This article takes reference from a blog post by Soomedh . If the export is from an SDP then check that blog out. But in our case, it's two arrays or ADP's which need to be exported into multiple sheets.

Note: If the volume records are more, then it's a good idea to not load the data onto the client, but rather do it on the server side.

We will use the popular SheetJS library to write an Excel File.

Import the library in Visual Builder

Since we need to use a third-party library, we need to import it first.
It can be either a CDN-based import, or we can place it in our resources/js folder.

Configure requirejs paths

At the Application level, open the JSON file and add a new entry for the library as shown.

  "requirejs": {
    "paths": {
      "xlsx": "https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min"
    }
  },

**OR ** (if the library is downloaded and placed under resources/js)

 "requirejs": {
    "paths": {
      "xlsx": "resources/js/xlsx.full.min"
    }
  },

Use it in the application

So far, we have only configured from where we need to load our library. Now we will load and use it. In our page module define block, we need to make an entry for the library

define(['xlsx'], (XLSX) => { ... })

Let us assume we have two variables data1 and data2 which contains the JSON data that needs to be written into different sheets. We can pass that as an input to a JS function shown below which would take care of the rest.

generateExcel(data1, data2) {
      let wb = XLSX.utils.book_new();
      wb.Props = {
        Title: "Export as Multiple Sheets",
        Subject: "Demo",
        Author: "Satish Kumar",
        CreatedDate: new Date()
      };

      //First Sheet
      wb.SheetNames.push("data1");
      let ws1 = XLSX.utils.json_to_sheet(data1.Employees);
      wb.Sheets["data1"] = ws1;

      //Second Sheet
      wb.SheetNames.push("data2");
      let ws2 = XLSX.utils.json_to_sheet(data2.Departments);
      wb.Sheets["data2"] = ws2;

      let wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary' });

      function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i < s.length; i++) view[i] = s
          .charCodeAt(i) & 0xFF;
        return buf;
      }
      this.download(s2ab(wbout), '', 'demo.xlsx');
    };

 preview(blobData, contentType) {
      if (contentType === undefined || contentType.length === 0) {
        contentType = "application/octet-stream";
      }
      var newBlob = new Blob([blobData], {
        type: contentType
      });
      return URL.createObjectURL(newBlob);
    };

 download(blobData, contentType, fileName) {
      var element = document.createElement('a');
      element.setAttribute('href', this.preview(blobData, contentType));
      element.setAttribute('download', fileName);
      element.style.display = 'none';
      document.body.appendChild(element);
      element.click();
      document.body.removeChild(element);
    };

The complete application code is my GIT repository. Just download the ZIP and import it.