Importing excel file into db (Takes a lot of time)

Question

I am using MVC5 and I am importing my excel file using epplus into sql database. However I am able to import files which are smaller in size like less than 1000kb but any file size larger then that is taking hours to import. I am looking for a efficient way to import my excel file in a more quicker

public ActionResult Application(FormCollection formCollection)
    {
        var usersList = new List<bomApplicationImportTgt>();
        if (Request != null)
        {
            HttpPostedFileBase file = Request.Files["UploadedFile"];
            if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
            {
                string fileName = file.FileName;
                string fileContentType = file.ContentType;
                byte[] fileBytes = new byte[file.ContentLength];
                var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
                using (var package = new ExcelPackage(file.InputStream))
                {
                    var currentSheet = package.Workbook.Worksheets;
                    var workSheet = currentSheet.First();
                    var noOfCol = workSheet.Dimension.End.Column;
                    var noOfRow = workSheet.Dimension.End.Row;
                    for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                    {
                        var user = new bomApplicationImportTgt();
                        user.date = Convert.ToDateTime(workSheet.Cells[rowIterator, 1].Value);
                        user.Description = workSheet.Cells[rowIterator, 2].Value?.ToString();
                        user.SequenceNumber = Convert.ToInt32(workSheet.Cells[rowIterator, 3].Value);
                        user.PartNumber = workSheet.Cells[rowIterator, 4].Value?.ToString();
                        user.PartsName = workSheet.Cells[rowIterator, 5].Value?.ToString();
                        user.SP = workSheet.Cells[rowIterator, 6].Value?.ToString();
                        user.INT = workSheet.Cells[rowIterator, 7].Value?.ToString();
                        user.SN = workSheet.Cells[rowIterator, 8].Value?.ToString();
                        user.SZ = workSheet.Cells[rowIterator, 9].Value?.ToString();
                        user.C = workSheet.Cells[rowIterator, 10].Value?.ToString();
                        user.E_F = workSheet.Cells[rowIterator, 11].Value?.ToString();
                        user.Block = workSheet.Cells[rowIterator, 12].Value?.ToString();
                        user.SEC = workSheet.Cells[rowIterator, 13].Value?.ToString();
                        user.Item = workSheet.Cells[rowIterator, 14].Value?.ToString();
                        user.SUF = workSheet.Cells[rowIterator, 15].Value?.ToString();
                        user.Model = workSheet.Cells[rowIterator, 16].Value?.ToString();
                        user.M_E_F = workSheet.Cells[rowIterator, 17].Value?.ToString();
                        user.OP = workSheet.Cells[rowIterator, 18].Value?.ToString();
                        user.Type = workSheet.Cells[rowIterator, 19].Value?.ToString();
                        user.Quantity = workSheet.Cells[rowIterator, 20].Value?.ToString();
                        user.PLGRPCD = workSheet.Cells[rowIterator, 21].Value?.ToString();
                        user.PL1 = workSheet.Cells[rowIterator, 22].Value?.ToString();
                        user.ATC1 = workSheet.Cells[rowIterator, 23].Value?.ToString();
                        user.PL2 = workSheet.Cells[rowIterator, 24].Value?.ToString();
                        user.ATC2 = workSheet.Cells[rowIterator, 25].Value?.ToString();
                        user.PL3 = workSheet.Cells[rowIterator, 26].Value?.ToString();
                        user.ATC3 = workSheet.Cells[rowIterator, 27].Value?.ToString();
                        user.Plant = workSheet.Cells[rowIterator, 28].Value?.ToString();
                        user.SHR = workSheet.Cells[rowIterator, 29].Value?.ToString();
                        user.DC_Number = workSheet.Cells[rowIterator, 30].Value?.ToString();
                        user.FileName = fileName;
                        usersList.Add(user);
                    }
                }
            }
        }
        using (Dev_Purchasing_New_ModelEntities excelImportDBEntities = new Dev_Purchasing_New_ModelEntities())
        {
            foreach (var item in usersList)
            {
                excelImportDBEntities.bomApplicationImportTgts.Add(item);
            }
            excelImportDBEntities.SaveChanges();
        }
        return View("Application");
    }

Answer 1

I'd recommend using the ExcelDataReader library, you can add it through Nuget. On your model you just need a byte array to hold the spreadsheet data and the path of the spreadsheet. To add it right click on your solution and select Manage Nuget Packages. Then search for ExcelDataReader and install it.

Here's the model you're uploading:

public class SpreadsheetUploadModel
{
public byte[] Payload {get; set;}
public string FileName {get; set;}
}

Here's the basic idea of your action, I don't have a return in there because I'm not sure what you want to return.

    public IActionResult UploadSpreadsheet(SpreadsheetUploadModel mySheet)
    {
        IExcelDataReader rdr = null;
                    try
                    {
                            using (MemoryStream ms = new MemoryStream(mySheet.Payload)) //Payload is the byte array
                            {
                                ms.Position = 0;
                                string ext = Path.GetExtension(mySheet.FileName); //filename is the filename

                                switch (ext)
                                {
                                    case ".xls": //Old style (2003 or less)
                                        rdr = ExcelReaderFactory.CreateBinaryReader(ms);
                                        break;
                                    case ".xlsx": //New style (2007 or higher)
                                        rdr = ExcelReaderFactory.CreateOpenXmlReader(ms);
                                        break;
                                    default:
                                        throw new ArgumentOutOfRangeException(String.Format("File extension {0} is not recognized as valid", ext));
                                }

                                using (DataSet ds = rdr.AsDataSet())
                                {
                                    int rowNumber = 2;
                                    DataTable dt = ds.Tables[0];
                                    DataView dv = ds.DefaultViewManager.CreateDataView(ds.Tables[0]);

                                    foreach (DataRow row in rows)
                                    { //... logic in here - you can iterate the rows which will map the spreadsheet rows, so you can insert each row individually, or build a different model that your database function is expecting, or whatever else you're trying to do ... 
}
                                 }
                            }
    }
    catch(Exception ex)
    {
    }

    }

Answer 2

Probably inserting to the database is what is slowing you down. Use SqlBulkCopy to speed things up. (You need to ass a reference to FastMember for the ObjectReader)

public class BulkWriter
{
        private static readonly ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]> ColumnMapping =
            new ConcurrentDictionary<Type, SqlBulkCopyColumnMapping[]>();

        public async Task InsertAsync<T>(IEnumerable<T> items, string tableName, SqlConnection connection,
            CancellationToken cancellationToken)
        {
            using (var bulk = new SqlBulkCopy(connection))
            using (var reader = ObjectReader.Create(items))
            {
                bulk.DestinationTableName = tableName;
                foreach (var colMap in GetColumnMappings<T>())
                    bulk.ColumnMappings.Add(colMap);
                await bulk.WriteToServerAsync(reader, cancellationToken);
            }
        }

        private static IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings<T>() =>
            ColumnMapping.GetOrAdd(typeof(T),
                type => 
                    type.GetProperties()
                        .Select(p => new SqlBulkCopyColumnMapping(p.Name, p.Name)).ToArray());
}

Usage:

await new BulkWriter().InsertAsync(usersList, "TheNameOfTheTable", excelImportDBEntities.Database.Connection, CancellationToken.None);

Answer 3

I am using Import in my project like below:

  using Excel;
  var importFile = Request.Files["files"];
  if (importFile.ContentLength == 0)
  {
       ViewBag.errorFileMessage = "Please select file";
       return View();
  }
  Stream stream = importFile.InputStream;
  IExcelDataReader reader = null;
  reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
  reader.IsFirstRowAsColumnNames = true;
  DataSet result = reader.AsDataSet();
  DataTable dataRecords = new DataTable();
  //You can also remove empty rows 
  if (result.Tables.Count > 0)
  {
      dataRecords = result.Tables[0];
      for (int i = dataRecords.Rows.Count - 1; i >= 0; i--)
      {
          if (dataRecords.Rows[i][0] == DBNull.Value)
          {
              dataRecords.Rows[i].Delete();
          }
          dataRecords.AcceptChanges();
      }
   }
   if (dataRecords.Rows.Count > 0)
   {
        List<MyExcelListModel> reqData = new List<MyExcelListModel>();
        foreach (DataRow row in dataRecords.Rows)
        {
            MyExcelModel exceldata = new MyExcelModel();
            exceldata.Name= row[0].ToString();
            //Add according to your requirements
            reqData.Add(exceldata);
        }     
   } 

Now you can save the data into database.