+ Start a Discussion
Ayyagari RameshAyyagari Ramesh 

How can i create a VF page in form of excel tables from a given excel sheet of certain fields

NagendraNagendra (Salesforce Developers) 
Hi Ramesh,

Please find the below code to upload data in the excel sheet into visual force page as tables.

Visual Force 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>
Apex Controller:
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;
}
}
}
Hope this helps.

Kindly mark this as solved if the reply was helpful.

Thanks,
Nagendra

 
Ayyagari RameshAyyagari Ramesh
Hi Nagendra, 
Thanks for the solution.
Apologies my question might not be clear to you, my scenerio is different.
I have an excel file where textfields are given as r1.monday, r2.tuesday r3 wed...... friday. There and there are 3 columns with heading past,present,future.
so the user will give the input in the particular empty respective to row and column.

Thanks,
Ram