1
Answer

Reading Data from excel

Anu

Anu

3y
1.5k
1
Hello,
 
I have an application which is reading data from excel. I am using the below connection strings:
  1. <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>  
  2. <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'"/>  
When a column is having data like below(some cells with only numeric value and one cell with alphanumeric), the data which is having alphanumeric(ie, data in the 3rd row-23A) is not reading from the excel.The data coming to the datatable is shown in the image image.
 
Value in the excel file:
 
Size
34
23A
45
 
Data coming in the datatable
 
 
 
Below is the code used to read data from excel. Please check and suggest a way to fix the issue:
  1. string path = Server.MapPath("~/Upload/");  
  2. string filePath = string.Empty;  
  3. string extension = string.Empty;  
  4. DataTable dtsheet = new DataTable();  
  5. DataSet ExcelData = new DataSet();  
  6. if (postedfile != null)  
  7. {  
  8. if (Directory.Exists(path))  
  9. {  
  10. Directory.CreateDirectory(path);  
  11. }  
  12. filePath = path + Path.GetFileName(postedfile.FileName);  
  13. extension = Path.GetExtension(postedfile.FileName);  
  14. postedfile.SaveAs(filePath);  
  15. }  
  16. string connectionstring = string.Empty;  
  17. switch (extension)  
  18. {  
  19. case ".xls"://for 97-03  
  20. connectionstring = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;  
  21. //connectionstring = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;  
  22. break;  
  23. case ".xlsx"://07 and above  
  24. connectionstring = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;  
  25. break;  
  26. }  
  27. connectionstring = string.Format(connectionstring, filePath);  
  28. using (OleDbConnection connExcel = new OleDbConnection(connectionstring))  
  29. {  
  30. using (OleDbCommand cmdExcel = new OleDbCommand())  
  31. {  
  32. using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())  
  33. {  
  34. cmdExcel.Connection = connExcel;  
  35. //firstsheetname  
  36. connExcel.Open();  
  37. DataTable dtExcelSchema;  
  38. dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  39. sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();  
  40. connExcel.Close();  
  41. //read data  
  42. connExcel.Open();  
  43. cmdExcel.CommandText = "SELECT * FROM [" + sheetName + "]";  
  44. odaExcel.SelectCommand = cmdExcel;  
  45. odaExcel.Fill(dtsheet);  
  46. connExcel.Close();  
  47. }  
  48. }  
  49. }  
Thanks,
Hanusha
Answers (1)
Next Recommended Forum