How To Read Excel Data Using LWC Salesforce?

How To Read Excel Data Using LWC Salesforce?

We all know that Salesforce supports parsing the CSV file, but sometimes the client insists on uploading and parsing the data in the form of an excel file. The standard way this cannot be achieved.

Currently, we can use third-party plug-ins to meet this requirement.

1. Upload Static Resource

ūüĒó https://github.com/darkacer/myBatCave/blob/master/force-app/main/default/staticresources/sheetjs/sheetjs/sheetmin.js

Download the file from the above link and upload it under a static resource.

Import the static resource in the JS to read the content of the excel file.

2. Code Implementation

ExcelReader.html

<lightning-layout-item

size=”12″

small-device-size=”12″

large-device-size=”3″

medium-device-size=”4″

¬†¬†¬†¬†padding=”around-small”>

    <lightning-input

type=”file”

label=”Upload

XLSX” name=”fileUploader”

        accept={strAcceptedFormats}

onchange={handleUploadFinished}>

    </lightning-input>

    <p>{strUploadFileName}</p>

</lightning-layout-item>

The indentation is missing

<lightning-layout-item size=”12″ small-device-size=”12″ large-device-size=”3″ medium-device-size=”4″

¬†¬†¬†¬†¬†¬†¬†¬†padding=”around-small”>

¬†¬†¬†¬†¬†¬†¬†¬†<lightning-input type=”file” label=”Upload XLSX” name=”fileUploader” accept={strAcceptedFormats}

            onchange={handleUploadFinished}>

        </lightning-input>

        <p>{strUploadFileName}</p>

</lightning-layout-item>

ExcelReader.js

import { LightningElement } from “lwc”;

/*Load the script to the component*/

import { loadScript } from “lightning/platformResourceLoader”;

/*Read the excel uploaded data*/

import excelFileReader from “@salesforce/resourceUrl/ExcelReaderPlugin”;

let XLS = {};

export default class ExcelFileReader extends LightningElement {

  /*Accepted formats for the excel file*/

¬†¬†strAcceptedFormats = [“.xls”, “.xlsx”];

  strUploadFileName; //Store the name of the selected file.

  objExcelToJSON; //Javascript object to store the content of the file

  connectedCallback() {

    Promise.all([loadScript(this, excelFileReader)])

      .then(() => {

        XLS = XLSX;

      })

      .catch((error) => {

¬†¬†¬†¬†¬†¬†¬†¬†console.log(“An error occurred while processing the file”);

      });

  }

  handleUploadFinished(event) {

    const strUploadedFile = event.detail.files;

¬†¬†¬†¬†if (strUploadedFile.length && strUploadedFile != “”) {

      this.strUploadFileName = strUploadedFile[0].name;

      this.handleProcessExcelFile(strUploadedFile[0]);

    }

  }

  handleProcessExcelFile(file) {

    let objFileReader = new FileReader();

    objFileReader.onload = (event) => {

      let objFiledata = event.target.result;

      let objFileWorkbook = XLS.read(objFiledata, {

¬†¬†¬†¬†¬†¬†¬†¬†type: “binary”

      });

      this.objExcelToJSON = XLS.utils.sheet_to_row_object_array(

¬†¬†¬†¬†¬†¬†¬†¬†objFileWorkbook.Sheets[“Sheet1”]

      );

      //Verify if the file content is not blank

      if (this.objExcelToJSON.length === 0) {

¬†¬†¬†¬†¬†¬†¬†¬†this.strUploadFileName = “”;

        this.dispatchEvent(

          new ShowToastEvent({

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†title: “Error”,

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†message: “Kindly upload the file with data”,

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†variant: “error”

          })

        );

      }

      if (this.objExcelToJSON.length > 0) {

        //Remove the whitespaces from the javascript object

        Object.keys(this.objExcelToJSON).forEach((key) => {

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†const replacedKey = key.trim().toUpperCase().replace(/ss+/g, “_”);

          if (key !== replacedKey) {

            this.objExcelToJSON[replacedKey] = this.objExcelToJSON[key];

            delete this.objExcelToJSON[key];

          }

        });

¬†¬†¬†¬†¬†¬†¬†¬†console.log(‘objExcelToJSON’+objExcelToJSON);

      }

    };

    objFileReader.onerror = function (error) {

      this.dispatchEvent(

        new ShowToastEvent({

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†title: “Error while reading the file”,

          message: error.message,

¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†variant: “error”

        })

      );

    };

    objFileReader.readAsBinaryString(file);

  }

}

The JS code

3. Testing

For testing, upload any excel file to the file uploader.

The sample file is mentioned below after uploading on the console panel the data of the excel file will be visible in the form of JSON Object.

Region Manager SalesMan Item Units Unit_price Sale_amt
East Martha Alexander Television 95 1,198.00 1,13,810.00
Central Hermann Shelli Home 50 500 25,000.00
Central Hermann Luis Television 36 1,198.00 43,128.00
Central Timothy David Cell Phone 27 225 6,075.00
West Timothy Stephen Television 56 1,198.00 67,088.00
East Martha Alexander Home Theater 60 500.00 30,000.00
Central Martha Steven Television 75 1,198.00 89,850.00
Central Hermann Luis Television 90 1,198.00 1,07,820.00
West Douglas Michael Television 32 1,198.00 38,336.00

Note:- 

To create a record or manipulation of the JSON Object in Apex, desterilize the JSON object in Apex using a Wrapper Class.

For more updates regarding Salesforce or if you are looking for any Salesforce consultancy services, Contact ABSYZ Inc. at +1.415.364.8055 or +91 79979 66174. Visit our website at https://absyz.com/

Recent Posts

devops introduction seamless development and operations
DevOps Introduction: Seamless Development and Operations
salesforce for small businesses success
Salesforce for Small Businesses Success
salesforce communities building stronger customer relationships
Salesforce Communities: Building Stronger Customer Relationships
salesforce app cloud what exactly is it and its advantages
Salesforce App Cloud: What Exactly is it & its Advantages?
chatbot testing a guide to best practices and tools
Chatbot Testing - A Guide to Best Practices and Tools
Scroll to Top