Skip to main content

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 ~~








Comments

Popular posts from this blog

Cannot alter the login 'sa', because it does not exist or you do not have permission.

Working on projects, it can happen that 'sa' account gets locked. If it is on local machine OR development boxes, onus would be on you to fix it. If scripts and SQL steps are not working, this might help you fixing the issue.

Steps to unlock 'sa' account and resetting the password.

1. Open SQL Server Configuration Manager

2. Select SQL Server Services -> 'SQL Server' service.
3. Right click on 'SQL Server' service and click on "Startup Parameters". For 2008, server "Startup Parameters" are inside Advanced tab.


4. Add '-m' in startup parameters as shown above and click on 'Add'. This will put SQL server into 'Single User Mode' and local admin will have 'Super User' rights. For 2008, server you have to add ':-m' in the last of the existing query.
5. Save the settings and Restart the service.
6. Now open the SQL Server Management Studio and connect to database using 'Windows Authentication&…

Could not load file or assembly 'Microsoft.Web.Infrastructure'

Could not load file or assembly 'Microsoft.Web.Infrastructure, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.



What 'Micorosoft.Web.Infrastructure' does? This dll lets HTTP modules register at run time.
Solution to above problem: Copy 'Micorosoft.Web.Infrastructure' dll in bin folder of your project and this problem should be resolved. If you have .Net framework installed on machine, this dll should be present on it. You can search for this dll and copy it in your active project folder.  
Alternatively,  you can install this dll using nuget package manager PM> Install-Package Microsoft.Web.Infrastructure -Version 1.0.0
Happy coding!!

AJAX Progrraming

Ajax, shorthand for Asynchronous JavaScript and XML, is a web development technique for creating interactive web applications. The intent is to make web pages feel more responsive by exchanging small amounts of data with the server behind the scenes, so that the entire web page does not have to be reloaded each time the user requests a change. This is meant to increase the web page's interactivity, speed, and usability. The Ajax technique uses a combination of: XHTML (or HTML) and CSS, for marking up and styling information.The DOM accessed with a client-side scripting language, especially JavaScript and JScript, to dynamically display and interact with the information presented. The XMLHttpRequest object is used to exchange data asynchronously with the web server. In some Ajax frameworks and in certain situations, an IFrame object is used instead of the XMLHttpRequest object to exchange data with the web server, and in other implementations, dynamically added tags may be used. XM…