How to import Excel files from static resources using Loadscript in LWC?

Question

I am trying to import an Excel file from static resources. But it's not working. The same method is working for JS files. Note the excel file is in xlsm format. Please help.

JS:

import { LightningElement, wire } from 'lwc';
import { loadScript } from "lightning/platformResourceLoader";
import workbook from "@salesforce/resourceUrl/xlsx";
import excelTemplate from "@salesforce/resourceUrl/ExcelTemplate";
import getaccounts from '@salesforce/apex/AccountController.getAccountLists';
import CV from '@salesforce/apex/ContentVersionController.getContentVersion';

export default class SheetJSLearning extends LightningElement {

    CV;
    XLSX;
    ExcelJS;
    tempFile;
    librariesLoaded = false;
    accountdata = [];

    connectedCallback() {
        if (this.librariesLoaded) return;
        this.librariesLoaded = true;

        Promise.all([loadScript(this, workbook + "/xlsx/xlsx.full.min.js")])
            .then(() => {
                this.XLSX = window.XLSX;
            })
            .catch(error => {
                console.log(error);
            });
    }

    async handleClick() {

        try {

            // sfdxfox code
            
            let response = await fetch(excelTemplate);
            let excelTemplateData = await response.blob();
            
            // sfdxfox code

            let returnVal = await CV();
            let accdata = await getaccounts();

            //In the below line, replaced returnVal with excelTemplateData
            var wb = this.XLSX.read(returnVal, { type: 'base64', WTF: true, bookVBA: true, cellStyles: true });

            var ws = wb.Sheets[wb.SheetNames[0]];

            XLSX.utils.sheet_add_aoa(ws, accdata.map(obj => [obj.Id, obj.Name, obj.CleanStatus, '=' + obj.Name]), { origin: "A2" });

            this.XLSX.writeFile(wb, "myFile.xlsm");

        } catch (err) {
            alert(err.message);
        }

    }

}

Answer 1

First of all, to load the entire file, you just use the resourceUrl without anything beyond the path. Secondly, you can only load JavaScript files with loadScript. If you want to use the file for other purposes, just fetch it:

async myMethod() {
  let response = await fetch(excelTemplate);
  let excelTemplateData = await response.blob();
}

Note that xlsx files are ZIP files, so you'll need a method to decompress the file before you can use it programmatically. If you just want to have the user download the file, you can use the excelTemplateData as a createObjectURL parameter, then use it as the src of an a tag to download the contents.