Activity Forums Salesforce® Discussions How to Upload excel sheet from Salesforce Visualforce pages?

  • Radhakrishna

    Member
    June 20, 2017 at 9:05 am

    Hello Vishant,

    You can use CSV parser for this..please refer the below example code where I have created one Visualforce page from where I am uplaoding the excel file .csv and extracting the records data and mapping with object columns

    please check and let me know if it helps you

    page:

    <apex:page sidebar="false" controller="UploadOpportunityScheduleLineItem">
    <apex:form >
    <apex:sectionHeader title="Upload data from CSV file"/>
    <apex:pagemessages />
    <apex:pageBlock >
    <center>
    <apex:inputFile value="{!contentFile}" filename="{!nameFile}" />
    <apex:commandButton action="{!ReadFile}" value="Upload File" id="theButton" style="width:70px;"/>
    <br/> <br/>
    </center>

    <apex:pageBlocktable value="{!mapUnmatchedSchedules}" var="mapID" title="Unmathed Lines of CSV file">
    <apex:column headervalue="Opportunity Id">
    {!mapUnmatchedSchedules[mapID][0]}
    </apex:column>
    <apex:column headervalue="Oportunnity Name">
    {!mapUnmatchedSchedules[mapID][1]}
    </apex:column>
    <apex:column headervalue="Month">
    {!mapUnmatchedSchedules[mapID][2]}
    </apex:column>
    <apex:column headervalue="Year">
    {!mapUnmatchedSchedules[mapID][3]}
    </apex:column>
    <apex:column headervalue="Actualized Amount">
    {!mapUnmatchedSchedules[mapID][4]}
    </apex:column>
    </apex:pageBlocktable>
    </apex:pageBlock>
    </apex:form>
    </apex:page>
    class

    public with sharing class UploadOpportunityScheduleLineItem
    {

    // Global variables
    public string nameFile{get;set;}
    public Blob contentFile{get;set;}
    List<Schedule__c> lstScheduleToUpdate = new List<Schedule__c>();
    public Schedule__C objSchedule{get;set;}
    //String array for taking csv data by line.
    String[] filelines = new String[]{};
    //string array for taking csv columns.
    String[] fileColumns = new String[]{};

    //set for storing all id's from csv.
    set<String> opptoupload{get;set;}
    //map for storing data from csv.
    public map<String,String> mapOppIDMonthYearToAccAmount{get;set;}
    public map<String, List<String>> mapUnmatchedSchedules{get;set;}

    //Main constructor
    public UploadOpportunityScheduleLineItem()
    {
    //Initalizing required objects.
    objSchedule = new Schedule__c();
    opptoupload = new set<String>();
    mapOppIDMonthYearToAccAmount = new map<String,String>();
    mapUnmatchedSchedules = new map<String, List<String>>();
    }
    //Method to read file content and check extension and file format.
    public Pagereference ReadFile()
    {
    //If without selecting csv file you clicked on upload it will give error message.
    if(nameFile == null)
    {
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'You should select csv file to upload');
    ApexPages.addMessage(errormsg);
    return null;
    }
    //Taking file extension.
    String extension = nameFile.substring(nameFile.lastIndexOf('.')+1);
    //Checking if file extension is .csv.
    if(extension == 'csv' ||extension == 'CSV')
    {
    nameFile=contentFile.toString();
    //Spliting by new line
    filelines = nameFile.split('\n');
    //Spliting values by (,) for checking coloumn size
    fileColumns = filelines[0].split(',');
    //If column size is 5 then only it will proceed.
    if(fileColumns.size() ==5)
    {
    for (Integer i=1;i<filelines.size();i++)
    {
    String[] inputvalues = new String[]{};
    inputvalues = filelines[i].split(',');
    opptoupload.add(inputvalues[0]);

    mapOppIDMonthYearToAccAmount.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues[4]);
    mapUnmatchedSchedules.put(inputvalues[0]+inputvalues[2]+inputvalues[3],inputvalues);
    lstScheduleToUpdate = new List<Schedule__c>();
    }
    for(Schedule__c objSchedule : [select Opportunity__r.Id ,Month__c,Year__c,
    Actualized_Amount__c from Schedule__c where
    Opportunity__c IN :opptoupload])
    {
    String str = objSchedule.Opportunity__r.Id;
    String str1;
    str1 = str.substring(0, 15);
    if(mapOppIDMonthYearToAccAmount.containsKey(str1 + objSchedule.Month__c +objSchedule.Year__c))
    {

    objSchedule.Actualized_Amount__c = decimal.valueOf(mapOppIDMonthYearToAccAmount.get(str1 + objSchedule.Month__c +objSchedule.Year__c).trim());
    mapUnmatchedSchedules.remove(str1 + objSchedule.Month__c +objSchedule.Year__c);
    lstScheduleToUpdate.add(objSchedule);
    }
    }
    //Checking if list is not empty then updating.
    if(lstScheduleToUpdate.Size()>0)
    {
    update lstScheduleToUpdate;
    }
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.info,'File uploaded successfully');
    ApexPages.addMessage(errormsg);

    return null;
    }
    else
    {
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'There should be 5 columns in csv file');
    ApexPages.addMessage(errormsg);
    return null;
    }
    return null;
    }
    //If file is not csv type then it will give error message.
    else
    {
    ApexPages.Message errormsg = new ApexPages.Message(ApexPages.severity.ERROR,'File type should be csv type');
    ApexPages.addMessage(errormsg);
    return null;
    }
    }
    }

    Also, please mark this as best answer if it solves your issue...let me know if anything else needed.

Log In to reply.

Popular Salesforce Blogs

Popular Salesforce Videos