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();      
Post a Comment

Bel