Sunday, October 13, 2019

Export to csv in lightning component

Today in this blog post we will see how to export data to csv in lightning component.

Object in Consideration: CASE
Filter on Field: Status

We will be displaying Case Status filter to user and we will be allowing user to select the Case Status value. Based on the value selected in Case Status filter we will be displaying data to user. User can now navigate across the displayed data. User can select record which user wants to export from the same or the different page while navigating from one page to another page as shown in the below image. User can select all the record at once as well if user wants to export all records being displayed.

Export to csv in lightning component



CaseExtractComp.cmp

<aura:component controller="caseExtractCompController" implements="force:appHostable,flexipage:availableForAllPageTypes,flexipage:availableForRecordHome,force:hasRecordId,force:lightningQuickAction" access="global" >
<aura:handler name="init" value="{!this}" action="{!c.doInit}"/>
    <aura:attribute name="navigateParameter" type="string" default="initialLoad"/>
    <aura:attribute name="pageNumber" type="integer" />
    <aura:attribute name="CaseList" type="list" />
    <aura:attribute name="caseListPaginateWise" type="list" />
    <aura:attribute name="totalRecords" type="integer" />
    <aura:attribute name="lastPageNumber" type="integer" />
    <aura:attribute name="finalListToAdd" type="list" />
    <aura:attribute name="statusValueSelected" type="String" default="Working"/>
    <div class="slds-box slds-theme_shade slds-theme_alert-texture">
        <lightning:select name="select" label="Select Case Status" onchange="{!c.handleStatusChange}" value="{!v.statusValueSelected}">
        <option value="Working">Working</option>
        <option value="Escalated">Escalated</option>
        <option value="Closed">Closed</option>
    </lightning:select>
    </div>
    <div class="slds-box slds-theme_shade">
    <div >
        <strong>Showing Results for status :</strong><b>{!v.statusValueSelected}</b>
    </div>
    </div>
    <div class="slds-box slds-theme_shade">
 <table class="slds-table slds-table_cell-buffer slds-table_bordered">
  <thead>
    <tr >
      <div class="slds-form-element">
          <div class="slds-form-element_control">
            <label class="slds-checkbox">
                <ui:inputCheckbox change="{!c.selectAllORDeselectAll}"/>
              <span class="slds-checkbox_faux"/>
              <span class="slds-form-element_label"/>
              </label>
          </div>
        </div>
      <th class="" scope="col">
        <div class="slds-truncate" title="Case Number">Case Number</div>
      </th>
      <th class="" scope="col">
        <div class="slds-truncate" title="Status">Status</div>
      </th>
      <th class="" scope="col">
        <div class="slds-truncate" title="OwnerName">Owner Name</div>
      </th>
        <th class="" scope="col">
        <div class="slds-truncate" title="AccountName">Account Name</div>
      </th>
        <th class="" scope="col">
        <div class="slds-truncate" title="Origin">Origin</div>
      </th>
        <th class="" scope="col">
        <div class="slds-truncate" title="Priority">Priority</div>
      </th>
         <th class="" scope="col">
        <div class="slds-truncate" title="Related object Field in Report">Related object Field</div>
      </th>
    </tr>
  </thead>
  <tbody>
      <aura:iteration items="{!v.caseListPaginateWise}" var="a">
    <tr >
      <th  scope="row">
       <div class="slds-form-element">
          <div class="slds-form-element_control">
            <label class="slds-checkbox">
                <ui:inputCheckbox value="{!a.check}"/>
                <span class="slds-checkbox_faux"/>
              <span class="slds-form-element_label"/>
            </label>
          </div>
        </div>
      </th>
     <th  scope="row">
        {!a.obj.CaseNumber}
      </th>
        <th  scope="row">
        {!a.obj.Status}
      </th>
        <th  scope="row">
        {!a.obj.Owner.Name}
      </th>
        <th  scope="row">
        {!a.obj.Account.Name}
      </th>
        <th  scope="row">
        {!a.obj.Origin}
      </th>
        <th  scope="row">
        {!a.obj.Priority}
      </th>
        <th  scope="row">
        {!a.obj.Testobject1__r.Status__c}
      </th>
    </tr>
     </aura:iteration> 
  </tbody>
</table>
    <aura:if isTrue="{!(v.pageNumber!=v.lastPageNumber)}">
     <lightning:button label="Next" onclick="{!c.goToNext}" class="slds-button slds-button_brand"/>
    </aura:if>
     <aura:if isTrue="{!v.pageNumber!=1}">
     <lightning:button label="Previous" onclick="{!c.goToPrevious}" class="slds-button slds-button_brand"/>
     </aura:if>
    <lightning:button label="Download Cases" onclick="{!c.downloadSelectedCase}" class="slds-button slds-button_brand"/>
     <div class="slds-align_absolute-center">
         <b>Displaying Page {!v.pageNumber} of {!v.lastPageNumber}</b>
     </div>
   
</div>

</aura:component>

CaseExtractCompController.js

({
doInit : function (component, event, helper) {
        var np=component.get("v.navigateParameter");
        component.set("v.pageNumber",1);
        helper.helperMethod(component,np);
     
    },
    handleStatusChange : function (component, event, helper) {
        component.set("v.pageNumber",1);
        helper.helperMethod(component,'initialLoad');
     
    },
 goToNext: function(component, event, helper) {
        var pgNumber=component.get("v.pageNumber");
        pgNumber=pgNumber+1;
        component.set("v.pageNumber",pgNumber);
        component.set("v.navigateParameter",'next');
        var ntType=component.get("v.navigateParameter");
        helper.helperMethod(component,ntType);
    },
    goToPrevious: function(component, event, helper) {
        var pgNumber=component.get("v.pageNumber");
        pgNumber=pgNumber-1;
        component.set("v.pageNumber",pgNumber);
        component.set("v.navigateParameter",'previous');
        var ntType=component.get("v.navigateParameter");
        helper.helperMethod(component,ntType);
    },
    selectAllORDeselectAll: function(component, event, helper){
        var trueFalseCheck=event.getSource().get("v.value");
        var csList=component.get("v.CaseList");
        var pagnitaList=component.get("v.caseListPaginateWise");
        var caseListUpd=[];
        var pagnitaListUpd=[];
        for(var i=0;i<csList.length;i++)
            {
                if(trueFalseCheck==true)
                {
                    csList[i].check=true;
                }
                else
                {
                    csList[i].check=false;
                }
                caseListUpd.push(csList[i]);
            }
        component.set("v.CaseList",caseListUpd);
        for(var i=0;i<pagnitaList.length;i++)
            {
                if(trueFalseCheck==true)
                {
                    pagnitaList[i].check=true;
                }
                else
                {
                    pagnitaList[i].check=false;
                }
                pagnitaListUpd.push(pagnitaList[i]);
            }
             component.set("v.caseListPaginateWise",pagnitaListUpd);
   
    },downloadSelectedCase :function(component, event, helper){
        var allSelectedCase=component.get("v.CaseList");
        var caseListAdd=[];
        for(var i=0;i < allSelectedCase.length;i++)
            {
                if(allSelectedCase[i].check==true)
                {
                 
                    caseListAdd.push(allSelectedCase[i].obj);
                }
             
            }
        component.set("v.finalListToAdd",caseListAdd);
        var finalListToDownload=component.get("v.finalListToAdd");
        var csv=helper.convertArrayOfObjectsToCSV(component,finalListToDownload); 
        if(csv==null)
        {
          return ;
        }                         
        var elementLink=document.createElement('a');
        elementLink.href='data:text/csv;charset=utf-8,'+encodeURI(csv);
        elementLink.target='_self';
        elementLink.download='CaseExportData.csv';
        document.body.appendChild(elementLink);
        elementLink.click();
        $A.get('e.force:refreshView').fire();
     
    }
})


CaseExtractCompHelper.js

({
 helperMethod : function(component,type) {
        if(type=='initialLoad')
        {
        var statusValueSelected=component.get("v.statusValueSelected");
            //alert('statusValueSelected'+statusValueSelected);
        var action=component.get('c.getAllCase');
            action.setParams({
                StatusValue:statusValueSelected
            });
       action.setCallback(this,function(response){   
            var state=response.getState();
            var resultData=response.getReturnValue();
            var recordLength=response.getReturnValue().length;
            component.set("v.totalRecords",recordLength);
            var paginateData=[];
            if(state==="SUCCESS")
            {
              component.set("v.CaseList",resultData);
              for(var i=0;i<5;i++)
                  {
                    if(recordLength > i)
                    {
                        paginateData.push(resultData[i]);
                   
                    }                 
                  }
                  component.set("v.caseListPaginateWise",paginateData);
                  component.set("v.lastPageNumber",Math.ceil(recordLength/5));

            }
     
        });
        $A.enqueueAction(action);
 }
        if(type=='next')
        {
            var pgNumber=component.get("v.pageNumber");
            var limit=5*pgNumber;
            var start=limit-5;
            var paginateData=[];
            var RequestList=[];
            RequestList=component.get("v.CaseList");
            var recordLength=component.get("v.totalRecords");
             for(var i=start;i<limit;i++)
                  {
                    if(recordLength > i)
                    {
                        paginateData.push(RequestList[i]);
                   
                    }                 
                  }
             component.set("v.caseListPaginateWise",paginateData);
        }
        if(type=='previous')
        {
            var pgNumber=component.get("v.pageNumber");
            var limit=5*pgNumber;
            var start=limit-5;
           // alert('limit'+limit);
            var paginateData=[];
            var RequestList=[];
            RequestList=component.get("v.CaseList");
            var recordLength=component.get("v.totalRecords");
             for(var i=start;i<limit;i++)
                  {
                    if(recordLength > i)
                    {
                        paginateData.push(RequestList[i]);
                   
                    }                 
                  }
             component.set("v.caseListPaginateWise",paginateData);
        }
    },
    convertArrayOfObjectsToCSV : function(component,objRecords) {
        var csvStringResult,counter,keys,lineDivider,columnDivider;
        if(objRecords==null || !objRecords.length)
        {
return null;         
        }
        columnDivider=',';
        lineDivider='\n';
        keys=['CaseNumber','Status','Owner','Account','Origin','Priority','Subject','Testobject1__r'];
        csvStringResult='';
        csvStringResult+=keys.join(columnDivider);
        csvStringResult+=lineDivider;
        for(var i=0;i<objRecords.length;i++)
            {
                counter=0;
                for(var tempKey in keys)
                    {
                        var skey=keys[tempKey];
                         if(counter>0)
                        {
                            csvStringResult+=columnDivider;
                        }
                        // Querying standard related object field
                        if(typeof objRecords[i][skey]==='object' && (skey==='Owner' || skey==='Account')){
                            csvStringResult+='"'+objRecords[i][skey].Name+'"';
                            counter ++;
                        }
                        // Querying custom related object field
                        else if(typeof objRecords[i][skey]==='object' &&  skey==='Testobject1__r'){
                            csvStringResult+='"'+objRecords[i][skey].Status__c+'"';
                            counter ++;
                        }
                        // Querying same object field
                        else{
                            csvStringResult+='"'+objRecords[i][skey]+'"';
                            counter ++;
                        }
                     
                    }
                csvStringResult+=lineDivider;
             
            }
     
        return csvStringResult
    }

})

caseExtractCompController.apxc

public class caseExtractCompController {
 @AuraEnabled
    public static list<wrapperClass> getAllCase(String StatusValue) {
        string caseStatus=StatusValue;
        list<wrapperClass> wrapperList=new list<wrapperClass>();
          for(Case req:[Select id, CaseNumber, Status,Account.Name,Origin,Priority,Subject,Owner.Name,Testobject1__r.Status__c from Case where Status=:caseStatus ])
            {
              wrapperList.add(new wrapperClass(false,req));
            }
        //wrapperClass wObj = new wrapperClass(false, conList);
        return wrapperList;
    }
 
    public class wrapperClass {
        @AuraEnabled
        public boolean check {
            get;
            set;
        }
        @AuraEnabled
        public Case obj{
            get;
            set;
        }
        public wrapperClass(boolean check, Case obj) {
            this.check = check;
            this.obj = obj;

        }


    }

}


Downloaded excel:


lightning component export to excel


7 comments:

  1. Can we change the header column names to anything else like Testobjectfield__c to Field?

    ReplyDelete
  2. Hi, I am trying to use the same use case in my development. But I can see 'undefined' in the excel file downloaded for the fields which has blank values. Can you tell me what should be done in order to show blank values?

    Looking for your reply on this. Thanks!

    ReplyDelete
  3. Hello !
    Can we have one spreadSheet but multiple sheets on it ?
    Thanks

    ReplyDelete
  4. I have a column with much large text data (greater than 255 chars) which is causing extra rows in excel to accomodate the exceeding text.
    How can a cell hold all these text lines?

    ReplyDelete
  5. what is the maximum rows this can accomudate?

    ReplyDelete
  6. Thank-you very much, this was really helpful!!!

    ReplyDelete