public IActionResult Index() { return View(); } [HttpPost] public IActionResult Index(IFormFile postedFile) { if (postedFile != null) { //Create a Folder. string path = Path.Combine(_hostingEnvironment.WebRootPath, "Uploads"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string fileName = Path.GetFileName(postedFile.FileName); string filePath = Path.Combine(path, fileName); using (FileStream stream = new FileStream(filePath, FileMode.Create)) { postedFile.CopyTo(stream); } using (var package = new ExcelPackage(new FileInfo(filePath))) { var worksheet = package.Workbook.Worksheets[0]; DataTable dt = new DataTable(); bool hasHeaderRow = true; foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.Columns]) { dt.Columns.Add(hasHeaderRow ? firstRowCell.Text : $"Column {firstRowCell.Start.Column}"); } int startRow = hasHeaderRow ? 2 : 1; for (int rowNum = startRow; rowNum <= worksheet.Dimension.Rows; rowNum++) { var worksheetRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.Columns]; var row = dt.Rows.Add(); foreach (var cell in worksheetRow) { row[cell.Start.Column - 1] = cell.Text; } } string connectionString = _configuration.GetConnectionString("DefaultConnection"); using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { sqlBulkCopy.DestinationTableName = "Product"; sqlBulkCopy.WriteToServer(dt); } con.Close(); } } } return View("~/Views/Product/product.cshtml"); } ------------------------------------------------------------------------------------
=================================================================== To understand the code, it's helpful to break it down into smaller parts and understand the purpose of each section. Here's a step-by-step explanation: 1. The `Index()` method is the default action for the view and simply returns the view without any additional logic. It is used to display the initial form or page. 2. The `[HttpPost]` attribute indicates that the following method will handle HTTP POST requests. This means it will be invoked when the form is submitted. 3. The `Index(IFormFile postedFile)` method takes an `IFormFile` parameter named `postedFile`, which represents the uploaded file. This parameter will contain the file data sent from the client. 4. The method checks if `postedFile` is not null, which means a file was uploaded. If a file exists, it proceeds with processing; otherwise, it simply returns the view. 5. It creates a folder named "Uploads" in the web root path if it doesn't already exist. This is the folder where the uploaded files will be stored. 6. It constructs the file path by combining the folder path and the original file name. This determines where the uploaded file will be saved on the server. 7. It creates a `FileStream` to save the uploaded file to the specified path. This enables reading and writing the file data. 8. It uses the `CopyTo` method to copy the uploaded file stream to the file stream created in the previous step. This saves the uploaded file to the server. 9. It uses the EPPlus library to open the uploaded Excel file for processing. EPPlus is a popular library for working with Excel files in .NET. 10. It accesses the first worksheet (index 0) in the Excel file. Worksheets are organized as collections, and you can select a specific worksheet using its index. 11. It creates a new `DataTable` to hold the data extracted from the Excel file. A `DataTable` is a data structure that represents a table of data in memory. 12. It determines whether the Excel file has a header row based on the `hasHeaderRow` boolean flag. If it has a header row, it adds the column names from the first row of the worksheet as column headers in the `DataTable`. If not, it generates default column names. 13. It sets the `startRow` variable based on the presence of a header row. If a header row exists, it starts from row 2 to skip the header; otherwise, it starts from row 1. 14. It iterates through each row in the worksheet, starting from the `startRow`. For each row, it adds a new row to the `DataTable` and assigns the cell values to the corresponding columns. 15. It retrieves the connection string from the configuration file. The connection string specifies the necessary information to connect to the SQL Server database. 16. It creates a new `SqlConnection` and opens the connection to the database. 17. It creates a `SqlBulkCopy` instance and sets the destination table name to "Product". `SqlBulkCopy` is a class that provides high-performance bulk copying of data from a `DataTable` to a SQL Server table. 18. It uses the `WriteToServer` method of `SqlBulkCopy` to bulk insert the data from the `DataTable` into the SQL Server table. This efficiently transfers the data in a batch operation. 19. It closes the SQL connection to release the resources. 20. Finally, it returns a view named "product.cshtml" to display the result of the upload and processing operation. By understanding each section of the code, you can see how it handles file uploads, reads Excel data, and inserts it into a SQL Server database.