1
Answer

How to import excel multiples sheet in to SQL server

Nerak Seven

Nerak Seven
6y
878
1
I have about 20 files , all with 20 or more sheets. I need to import specific data from all sheets in tables (as the same columns as the table). My code only import one sheet. How to import multiple sheets? and How Import specific cells from Excel?.I have read many questions but none do what I need and the most are old
 
Database SQL SERver
 
I hope ur help me. Im newbie.
 
  1. public ActionResult Index()  
  2. {  
  3. return View();  
  4. }  
  5. [HttpPost]  
  6. public ActionResult Index(HttpPostedFileBase postedFile)  
  7. {  
  8. string filePath = string.Empty;  
  9. if (postedFile != null)  
  10. {  
  11. string path = Server.MapPath("~/Uploads/");  
  12. if (!Directory.Exists(path))  
  13. {  
  14. Directory.CreateDirectory(path);  
  15. }  
  16. filePath = path + Path.GetFileName(postedFile.FileName);  
  17. string extension = Path.GetExtension(postedFile.FileName);  
  18. postedFile.SaveAs(filePath);  
  19. string conString = string.Empty;  
  20. switch (extension)  
  21. {  
  22. case ".xls": //Excel 97-03. c  
  23. onString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;  
  24. break;  
  25. case ".xlsx": //Excel 07 and above.  
  26. conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;  
  27. break;  
  28. }  
  29. DataTable dt = new DataTable();  
  30. conString = string.Format(conString, filePath);  
  31. using (OleDbConnection connExcel = new OleDbConnection(conString))  
  32. {  
  33. using (OleDbCommand cmdExcel = new OleDbCommand())  
  34. {  
  35. using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())  
  36. {  
  37. cmdExcel.Connection = connExcel;  
  38. //Get the name of First Sheet.  
  39. connExcel.Open();  
  40. DataTable dtExcelSchema;  
  41. dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  42. string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();  
  43. connExcel.Close(); //Read Data from First Sheet.  
  44. connExcel.Open();  
  45. cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";  
  46. odaExcel.SelectCommand = cmdExcel;  
  47. odaExcel.Fill(dt);  
  48. connExcel.Close();  
  49. }  
  50. }  
  51. }  
  52. conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;  
  53. using (SqlConnection con = new SqlConnection(conString))  
  54. {  
  55. using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))  
  56. {  
  57. //Set the database table name.  
  58. sqlBulkCopy.DestinationTableName = "dbo.Table_1";  
  59. //[OPTIONAL]: Map the Excel columns with that of the database table  
  60. sqlBulkCopy.ColumnMappings.Add("Rut", "Rut");  
  61. sqlBulkCopy.ColumnMappings.Add("Nombres", "Nombres");  
  62. sqlBulkCopy.ColumnMappings.Add("Malla", "Malla"); con.Open();  
  63. sqlBulkCopy.WriteToServer(dt); con.Close(); } } } return View();  
  64. }  
  65. }  

Attachment: HomeController.rar

Answers (1)
Next Recommended Forum