Monday, July 23, 2012

Export excel data to sharepoint list in sharepoint 2010

Here i will given a simple way to export the data to sharepoint list from excel file which is in documnet library in sharepoint.The data table"Exceldata" has used to get the excel data then bind this data to SPlist.Here first we get the path of the excel file in documnet library ,then using this path in connection string.
string ExcelfileName = Path.GetFileName("http://url/Test.xls");
string ExcelileLocation = Server.MapPath(Excelfilename);                
String Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ ExcelfileLocation +;Extended Properties=Excel 8.0;";  
OleDbConnection objCn = new OleDbConnection(con);  
DataTable ExcelData= new DataTable();
oleDbDataAdapter Oda = new OleDbDataAdapter("select * from[Sheet1$]", Con);   
oda.Fill(ExcelData); 
SPList ExcelDataList = w.Lists["ExcelDataList"];
for (int j = 0; j <= ExcelData.Rows.Count - 1; j++)
{
SPListItem ExcelDataistItem = ExcelDataList.Items.Add();
for (int i = 0; i <= ExcelData.Columns.Count - 1; i++)
{
string ColName = ExcelData.Rows[0][i].ToString();
ExcelDataistItem[ColName] = ExcelData.Rows[j][i].ToString();
}
ExcelDataistItem.Update();      

2 comments:

sharmila said...

string ExcelfileName = Path.GetFileName("http:/server/salesreport/Book2.xlsx");
string ExcelileLocation = HttpContext.Current.Request.MapPath(ExcelfileName);

String connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", ExcelileLocation);


The above code gives error because ExcelileLocation is not returning the correct path of the excel file. Can you tell me what is the correct way of getting the correct location of the excel file stored in Sharepoint document libray?

Unknown said...

We can access excel file by using UNC path

so we have to change the file path from
"http://server/salesreport/Book2.xlsx" to
"\\servername\salesreport\Book2.xlsx"

here sales report is the document library

Bel