Thursday, February 20, 2014

HTTP Error 500.19 - Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.

<staticContent>
     <mimeMap fileExtension=".woff" mimeType="application/font-woff"/>
</staticContent>

This error occurs when there is a problem reading the configuration file for the Web server or Web application. In some cases, the event logs may contain more information about what caused this error.

Solution:
1. Open IIS and click on hosted website
2. On the Featured View tab, double click on MIME type icon and check if .woff extension is present. If on click it gives error not to worry.
3. Open web.config file of project solution and check if .woff mimemap entry is present. If yes, remove that entry.
4. Go back to IIS and follow Step 1 and Step 2. It should show .woff extension now.
5. Check your website and it should work now.

Author: Sumit Bajaj

Thanks for Reading. Have a great time.




Tuesday, February 11, 2014

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