Skip to main content

Salesforce Lightning Component to insert records from Excel
Hey guys, I have posted the video at bottom of same steps mentioned in the blog. Today in this post we are going to learn that how we can insert records in custom object from Salesforce Lightning Component. also we will learn that how to call/ invoke server side apex method from lightning component (Aura)

Step 1/4:

Creating Object & Fields:

Create Custom Object: 'Employee__c' 
Create Field 'Name' & Datatype: 'Text'
Create Field 'First_Name__c' & Datatype: 'Text'
Create Field 'Email_Id__c' & Datatype: 'Text'
Create Field 'Joining_Date__c' & Datatype: 'Date'
Create Field 'Last_Name__c' & Datatype: 'Text'
Create Field 'Phone_Number__c' & Datatype: 'Text'

Add Static Resource:

Add Static Resource: XLSX
Download JS Library from: JS Library
Now Edit it: Just add one line in this JS

window.XLSX = XLSX;
Now it should look like:


Step 2/4:

Create Apex Class:

ExcelImportController.apxc

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
public with sharing class ExcelImportController { 

public virtual class BulkEmployeeException extends Exception {}     
/*
* @param base64Data : Content of the File in JSON format 
* @param fileName : File Name 
* @param contentType : Type of the File (Excel)
*/
@AuraEnabled
public static void parseFile(String base64Data, String fileName) {
 System.debug('In ParseFile');
 try {
  System.debug('JSONfromJS::'+base64Data);
  System.debug('fileNamefromJS::'+fileName);
  
  Blob blobData = Blob.valueOf(base64Data);
  System.debug('blobData::'+blobData);

  createEmployeeBatchRecords(blobData);

 }catch(BulkEmployeeException e){
  System.debug('BulkEmployeeException::::'+e.getMessage());
  
 }catch (Exception e) {
  System.debug('Exception::::'+e);
 }
}
/**
* @description: This method is used to create the Claim Batch Stage records based on the CSV Data as Input
* @param blobData: Content of the File Uploaded by the User in Blob format 
*/
public static void createEmployeeBatchRecords(Blob blobData)
{
 String csvAsString;
 String[] csvFileLines;
 System.debug('blobData:::'+blobData);
 csvAsString = blobData.toString();
 system.debug('Before JSON Formator csvAsString::' + csvAsString);
 
 String csvAsString1 = jsonFormator(csvAsString);
 
 System.debug('After JSON Formator csvAsString1::'+csvAsString1);

 List<JSON2ApexController> objList = JSON2ApexController.parse(csvAsString1);
 System.debug('After JSON deserialize objList::'+objList);
 System.debug('objList[0]::'+objList[0]);
 
 List<Employee__c> batchEmployeeList = new List<Employee__c>();
 
 for(JSON2ApexController item: objList) {
  
  Employee__c batchEmployeeRecord = new Employee__c();

  batchEmployeeRecord.Name = item.EmployeeName;  
  batchEmployeeRecord.Email_Id__c = item.EmailId;
  batchEmployeeRecord.First_Name__c = item.FirstName;  
  batchEmployeeRecord.Last_Name__c = item.LastName;
  batchEmployeeRecord.Phone_Number__c = item.PhoneNumber;
  
  Date standardDate = convertDateToStandardDate(item.JoiningDate);
  batchEmployeeRecord.Joining_Date__c = standardDate;
    
  batchEmployeeList.add(batchEmployeeRecord);
 }
 
 try{
  database.insert(batchEmployeeList);
 } catch(Exception e) {
  System.debug('Insert Exception::::'+e.getMessage());
 }        
}

public static List<JSON2ApexController> parse(String jsonString) {
 return (List<JSON2ApexController>) System.JSON.deserialize(jsonString, List<JSON2ApexController>.class);
} 

public class JSON2ApexController {
 
 public String EmployeeName;
 public String EmailId;
 public String FirstName;
 public String LastName;
 public String PhoneNumber;
 public String JoiningDate;
 
}

public static String jsonFormator(String jsonString) {
 String target = 'Employee Name';
 String replacement = 'EmployeeName';
 String jsonString1 = jsonString.replace(target, replacement);
 target = 'Email Id';
 replacement = 'EmailId';
 jsonString1 = jsonString1.replace(target, replacement);
 target = 'First Name';
 replacement = 'FirstName';
 jsonString1 = jsonString1.replace(target, replacement);
 target = 'Last Name';
 replacement = 'LastName';
 jsonString1 = jsonString1.replace(target, replacement);
 target = 'Phone Number';
 replacement = 'PhoneNumber';
 jsonString1 = jsonString1.replace(target, replacement);
 target = 'Joining Date (DD-MMM-YY)';
 replacement = 'JoiningDate';
 jsonString1 = jsonString1.replace(target, replacement);
 
 return jsonString1;
}

public static Date convertDateToStandardDate(String inputDate){
 // input Date in format : 10-Feb-2019
 // inputDateArray (10,Feb,2019)
 try{
  String[] inputDateArray = inputDate.split('-');
  String monthInUpperCase = inputDateArray[1].toUpperCase(); 
  Integer month = 0;
  switch on monthInUpperCase {
   when 'JAN' { month = 1; } when 'FEB' { month = 2; } when 'MAR' { month = 3; } when 'APR' { month = 4; } when 'MAY' { month = 5; }
   when 'JUN' { month = 6; } when 'JUL' { month = 7; } when 'AUG' { month = 8; } when 'SEP' { month = 9; } when 'OCT' { month = 10; }
   when 'NOV' { month = 11; } when 'DEC' { month = 12; } when else { month = 0; }
  }
  Date todayDate = System.today();
  Date standardDate;
  String currentCentury = String.valueOf(todayDate.year()).substring(0,2);
  String finalYear = currentCentury+inputDateArray[2];
  if(month == 0){  throw new BulkEmployeeException('Invalid Date');
       }else{
        standardDate = Date.newInstance(Integer.valueOf(finalYear),month,Integer.valueOf(inputDateArray[0]));
       }
  return standardDate;
 }catch(Exception e){
  throw new BulkEmployeeException('Exception:: '+inputDate);
 }
}
}

Create Lightning Event:

ExcelImportEvent.evt

1
2
3
4
5
6
<aura:event type="COMPONENT" description="ExcelImportEvent">
    <aura:attribute type="String" name="type" description="Type of the event: SUCCESS, ERROR" required="true"/>
    <aura:attribute type="String" name="message" description="Message" required="false"/>
    <aura:attribute type="String" name="fileName" description="File name" required="false"/>
    <aura:attribute type="Object" name="table" description="Parsed Excel table" required="false"/>
</aura:event>

Create Lightning Component:

ExcelImport.cmp

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<aura:component controller="ExcelImportController" implements="force:appHostable,flexipage:availableForAllPageTypes,flexipage:availableForRecordHome,force:hasRecordId,forceCommunity:availableForAllPageTypes" access="global" description="Component to import .xlsx files in Lightning">

<ltng:require scripts="{!$Resource.XLSX}"/>
<aura:registerEvent name="onImport" type="c:ExcelImportEvent"/>
<aura:attribute name="content" type="String" />
<aura:attribute name="file" type="Object" />
<aura:attribute name="recordTypeMap" type="Map" default="" />
<aura:attribute name="label" type="String" description="Label for input" default="Import Excel File" required="false"/>
<aura:attribute name="class" type="String" description="Additional styles" required="false"/>
<aura:attribute name="variant" type="String" description="Input variant" required="false" default="standard"/>
<aura:attribute name="required" type="Boolean" description="Shows if input is mandatory" default="false"/>
<aura:attribute name="disabled" type="Boolean" description="Displays input disabled" default="false"/>
<aura:attribute name="accept" type="String" default=".xls, .xlsx"/>

<aura:attribute name="stretchedDropzone" type="Boolean" description="Makes dropzone stretchable" default="false"/>
<aura:attribute name="isLoading" type="Boolean" default="false" access="private"/>

<aura:attribute name="fileSizeThreshold" type="Integer" description="Max file size in bytes, default 10mb" default="10000000" required="false"/>
<aura:attribute name="messageFileSizeExceeded" type="String" default="File size exceeded" required="false"/>
<aura:attribute name="messageNoFileSpecified" type="String" default="No file specified" required="false"/>

<lightning:input type="file"
     label="{!v.label}"
     class="{!v.class
     + (v.stretchedDropzone ? ' bigger-drop-zone' : '')
     + (v.isLoading ? ' drop-zone-loading' : '')}"
     variant="{!v.variant}"
     required="{!v.required}"
     disabled="{!v.disabled}"
     onchange="{!c.onTableImport}"
     multiple="false"
     accept="{!v.accept}"/>
</aura:component>

Add Controller JS:

ExcelImportController.js

1
2
3
4
5
6
({
    onTableImport: function (cmp, evt, helper) {
        helper.disableExcelInput(cmp);
        helper.importTableAndThrowEvent(cmp, evt, helper);
    }
})

Add Helper JS:


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
({
disableExcelInput: function(cmp) {
 cmp.set("v.disabled", true);
 cmp.set("v.isLoading", true);
},

enableExcelInput: function(cmp) {
 cmp.set("v.disabled", false);
 cmp.set("v.isLoading", false);
},

importTableAndThrowEvent: function(cmp, evt, helper) {
 evt.stopPropagation();
 evt.preventDefault();
 try {
  const file = helper.validateFile(cmp, evt);
  cmp.set("v.file",file);
  helper.readExcelFile(cmp, file, helper)
  .then($A.getCallback(excelFile => {
   helper.throwSuccessEvent(cmp, excelFile);
  }))
   .catch($A.getCallback(exceptionMessage => {
   helper.throwExceptionEvent(cmp, exceptionMessage);
   
  }))
   .finally($A.getCallback(() => {
   helper.enableExcelInput(cmp);
  }))
  } catch (exceptionMessage) {
   helper.throwExceptionEvent(cmp, exceptionMessage);
   helper.enableExcelInput(cmp);
  }
  },
   
   validateFile: function(cmp, evt) {
    const files = evt.getSource().get("v.files");
    if (!files || files.length === 0 || $A.util.isUndefinedOrNull(files[0])) {
     throw cmp.get("v.messageNoFileSpecified");
    }
    
    const file = files[0];
    const fileSizeThreshold = cmp.get("v.fileSizeThreshold");
    if (file.size > fileSizeThreshold) {
     throw (cmp.get("v.messageFileSizeExceeded") + ': ' + fileSizeThreshold + 'b');
    }
    return file;
   },
   
   readExcelFile: function(cmp, file, helper) {
    return new Promise(function (resolve, reject) {
     const fileReader = new FileReader();
     fileReader.onload = event => {
      let filename = file.name;
      let binary = "";
      new Uint8Array(event.target.result).forEach(function (byte) {
      binary += String.fromCharCode(byte);
     });
     
     try {
      resolve({
       "fileName": filename,
       "xlsx": XLSX.read(binary, {type: 'binary', header: 1})
      });
      console.log('XLSX.read::'+XLSX.read(binary, {type: 'binary', header: 1}));
      console.log('END1::');
      var workbook = XLSX.read(binary, { type: 'binary' }); 
      
      
      var sheet_name_list = workbook.SheetNames;
      console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]));
      var XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
      console.log('XL_row_object::'+XL_row_object); 
      var json_object = JSON.stringify(XL_row_object);
      cmp.set("v.content", json_object);
      console.log('After content set::'+cmp.get("v.content"));
      helper.parseFile1(cmp);  
      
      console.log('END2::');
     } catch (error) {
      reject(error);
     }
    };
           console.log('After content set::'+cmp.get("v.content"));
    fileReader.readAsArrayBuffer(file);
    
    
   });
   
  },
        
        throwExceptionEvent: function(component, message) {
     const errorEvent = component.getEvent("onImport");
  errorEvent.setParams({
   "type": "ERROR",
   "message": message
  });
  errorEvent.fire();
 },
  
  throwSuccessEvent: function(component, parsedFile) {
   const successEvent = component.getEvent("onImport");
   successEvent.setParams({
    "type": "SUCCESS",
    "fileName": parsedFile.fileName,
    "table": parsedFile.xlsx
   });
   successEvent.fire();
  },
   
   parseFile1: function (cmp) {
    console.log('json_object::');
    console.log('KInside::'+cmp.get("v.content"));
    var file = cmp.get("v.file");
    var action = cmp.get("c.parseFile");
    action.setParams({
     base64Data: cmp.get("v.content"),
     fileName: file.name,
    });
    action.setCallback(this, function (response) {
     if (response.getState() == "SUCCESS") {  
      console.log('Successful');
     }
     else {
      console.log('errorMsg');
     }
    });
    $A.enqueueAction(action);
   }
})

Step 3/4:

Create one Excel with extension .xls & Columns:
Employee Name
Email Id
First Name
Last Name
Phone Number
Joining Date (DD-MMM-YY)
Excel should look like this:

Step 4/4:

Now add this component to Home Page & upload your Excel File.

You can add Exception Handing & Toast Messages!
Please contact for any Ideas/Suggestions/Collaborations: krishna1096@gmail.com | 9921034154

Location: Pune, Maharashtra, India

Comments

  1. SFDC MAster HUntSeptember 29, 2019 at 11:10 AM

    Great....

    ReplyDelete
  2. PriyankaAugust 25, 2020 at 9:57 PM

    Can you please post the test class as well

    ReplyDelete
    Replies
    1. Peter Felix RajMay 5, 2023 at 3:56 AM

      Could you please provide the test class? @krishna kulkarni

      Delete
  3. Shriya DeoJanuary 8, 2021 at 12:25 AM

    This comment has been removed by the author.

    ReplyDelete
  4. Shriya DeoJanuary 8, 2021 at 1:45 AM

    Hello ,
    Thanks a lot for the code !
    Could implement as per the blog instructions but records are not created in SF.

    ReplyDelete
  5. MauricioJune 16, 2021 at 10:44 AM

    hello, good solution, I have a question, How many rows this solution supports?

    ReplyDelete
  6. RhysOctober 15, 2021 at 12:18 AM

    Hello,
    Thankyou for posting this, May I ask a question?

    I am very new to Salesforce, and may have done something wrong following the instructions, but cant work out what?

    On upload of the file, get the following error:
    [NoErrorObjectAvailable] Script error.
    a()@https://static.lightning.force.com/aus2s.sfdc-vwfla6/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:1010:196
    {anonymous}()@https://static.lightning.force.com/aus2s.sfdc-vwfla6/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:1010:389
    dispatchEvent()@https://static.lightning.force.com/aus2s.sfdc-vwfla6/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:21:19540
    _e.dispatchEvent()@https://static.lightning.force.com/aus2s.sfdc-vwfla6/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:13:14057
    _e._dispatchChangeEventWithDetail()@https://picnic--staging.lightning.force.com/components/lightning/input.js:1:36147
    _e._dispatchChangeEvent()@https://picnic--staging.lightning.force.com/components/lightning/input.js:1:37721
    _e.handleChange()@https://picnic--staging.lightning.force.com/components/lightning/input.js:1:34942

    Any ideas on how to diagnose this??
    Winter '22

    ReplyDelete
  7. SarikaOctober 16, 2021 at 6:51 AM

    Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.We are also providing the best services click on below links to visit our website.
    Oracle Fusion HCM Training
    Workday Training
    Okta Training
    Palo Alto Training
    Adobe Analytics Training

    ReplyDelete
  8. AnkurJanuary 27, 2022 at 9:48 AM

    Hey if we want to read record from column number 3 in excel what will be the change.

    ReplyDelete
  9. AnonymousJuly 1, 2022 at 11:15 PM

    How to get response back from apex to excel I performing Database.upsert functionality if there any error Database.error is capturing and it displaying in debug log but i want to get thos errr in aura component how can i Do that ? I want get response back from server after exection completed in Lighting component

    ReplyDelete
  10. Peter Felix RajMay 8, 2023 at 11:46 PM

    Could you please provide the test class? @krishna kulkarni

    ReplyDelete

Post a Comment

Popular posts from this blog

Salesforce Lightning Web Component - Dual List Box with Search Today in this post, we are going to learn that how we can use Search Functionality in Dual List Box from Salesforce Lightning Web Component . The use case could be, I have 2 custom objects. One is Job Posting Site OR Company & another is Position . Now as per the request of HR department, we have created one Junction Object Posting , which will have Lookup relation with Job Posting Site as well as Position. So, one Job Posting Site can have multiple Open Positions. We can easily add or remove mapping/junction based on Job Position is open or closed. Step 1/5: Create Custom Objects & Fields 1. Create Custom Object: ' Job_Posting_Site__c '     Create Field ' Name ' &  Datatype: ' Text ' 2. Create Custom Object: ' Position__c '       Create Field ' Name ' &  Datatype: ' Text '     You can add multiple fields based on requirements s...
2 comments
Read more