Uploading an Excel and Importing the data in database

This is very common requirement to read the data from uploaded excel and import it in database. Earlier it was a tedious task but not now, thanks to Nuget packages where you get so many utilities to make your life easy. In this post, we will also use Nuget package and perform the task of uploading excel and importing the data in database.

There are two steps to perform this,
1. To upload the excel file on server
2. Read the uploaded file and import the data to database

Step1: To upload the excel file on server

   <asp:FileUpload ID="excel_upload" runat="server"/>

    
    if (excel_upload.HasFile)
    {
       bool upload_file = true;
       string file_upload = Path.GetExtension(excel_upload.FileName.ToString());
       if (file_upload.Trim().ToLower() == ".xls" | 
            file_upload.Trim().ToLower() == ".xlsx")
       {
          // Save excel file onto Server
          xlsUpload.SaveAs(Server.MapPath("~/Uploads/" + excel_upload.FileName.ToString());
       }
    }

Step2: Read the uploaded file and import the data to database

To perform excel read/write, we will use ClosedXML (Nuget package). Install the package using package manager.



Once installed, include the namespace in your code file   using ClosedXML.Excel;

Now the code starts to get data from uploaded excel file.

      FileInfo fi = new FileInfo(filePath);

      //Open uploaded workbook
            var workBook = new XLWorkbook(fi.FullName);
      //Get the first sheet of workbook
      var worksheet = workBook.Worksheet(1);

      var firstRowUsed = worksheet.FirstRowUsed();
      var categoryRow = firstRowUsed.RowUsed();

      int coCategoryId = 1;

      //Get the column names from first row of excel
      Dictionary<int, string> keyValues = new Dictionary<int, string>();
      for (int cell = 1; cell <= categoryRow.CellCount(); cell++)
      {
        keyValues.Add(cell, categoryRow.Cell(cell).GetString());
      }

      //Get the next row
      categoryRow = categoryRow.RowBelow();
      while (!categoryRow.Cell(coCategoryId).IsEmpty())
      {
         int count = 1;
         var pc = new ExpandoObject();
         while (count <= categoryRow.CellCount())
         {
          // let this go through-if the data is bad, it will be rejected by SQL
           var data = categoryRow.Cell(count).Value;

           ((IDictionary<string, object>)pc).Add(keyValues[count], data);           

           count++;

         }
         categoryRow = categoryRow.RowBelow();
      }
      //Insert the data in SQL which is captured in pc object.

Reference: To get more detailed scenarios for excel data manipulation, click here.

~~ Happy Coding ~~