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
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/
