using excelexport.Models; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Configuration; using System.IO; using System.Data; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Http; using OfficeOpenXml; using System.Data.SqlClient; using Microsoft.Extensions.Logging; using System.Diagnostics; namespace StudentRecordManagementSystem.Controllers { public class HomeController : Controller { private readonly ILogger _logger; private IWebHostEnvironment Environment; private IConfiguration Configuration; private string dbconnectionStr; private readonly IWebHostEnvironment _hostingEnvironment; private readonly IConfiguration _configuration; public HomeController(ILogger logger, IWebHostEnvironment hostingEnvironment, IConfiguration configuration) { _logger = logger; _hostingEnvironment = hostingEnvironment; _configuration = configuration; } 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"); } public IActionResult Privacy() { return View(); } [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)] public IActionResult Error() { return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier }); } } }