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/

Leave a Comment

Your email address will not be published. Required fields are marked *

Recent Posts

Salesforce CRM Insights
From Data to Action: Tableau Agent’s Role in Shaping Salesforce CRM Insights
future-of-ai-powered-business-solutions
Dreamforce 2024: Unveiling the Future of AI-Powered Business Solutions
gitex 2024 worlds largest tech show
GITEX 2024 |  World's Largest Tech Show - ABSYZ
inclusive practices in women leadership equitable future
Inclusive Practices in Women’s Leadership: Equitable Future
salesforce dreamfest 2024 the highlights of dreamforce 2024
Dreamfest 2024: The highlights of Dreamforce 2024
Document

How can i help you? close button

powered     by   ABSYZ
Scroll to Top