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:
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?
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
Post a Comment