Reading Excel using Excel package and convert in to Datatable

A useful new feature in SharePoint 2013 is the ability to drag and drop files in to document libraries using browser. This feature is particularly useful for adding multiple to library, in previous versions of SharePoint we will open the document library in explorer view. explorer view is not a best practice. So drag and drop feature is very useful. Find the below screen shots for the same.

In my recent project i have a requirement to read an excel document, validate the data and upload the data in to database. We can achieve this functionality by using excel package or Epplus dll. Both are available in codeplex.

I have used excel package dll to achieve the functionality. You can download the dll by clicking here. 

As per my requirement i need to read the document which is uploaded through the file upload control, so i have written the following code snippet in the button click event.

Guid uniqid = System.Guid.NewGuid();  
string strFile = uniqid + "-" + excelFileUpload.FileName;  
var fileExt = System.IO.Path.GetExtension(excelFileUpload.FileName);  
if (fileExt == ".xlsx" || fileExt == ".xls")  
{  
   excelFileUpload.SaveAs("C:\\TempFolder\\" + strFile);  
   FileInfo existingFile = new FileInfo("C:\\TempFolder\\" + strFile);  
   using (ExcelPackage xlPackage = new ExcelPackage(existingFile))  
   {  
      ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];  
      dt = new DataTable();  
      //adding columns  
      int iCol = 1;  
      int iRow = 1;  
      bool CanRead = true;  
      while (CanRead)  
      {  
         if (worksheet.Cell(iRow, iCol).Value != null)  
         {  
            if (worksheet.Cell(iRow, iCol).Value != "")  
            {  
               dt.Columns.Add(worksheet.Cell(iRow, iCol).Value);  
               iCol++;  
            }  
            else  
            {  
               CanRead = false;  
            }  
         }  
         else  
         {  
            CanRead = false;  
         }  
      }  
      //adding rows  
      iRow = 2;  
      bool canRowRead = true;  
      while (canRowRead)  
      {  
          DataRow dr = dt.NewRow();  
          bool rowVal = true;  
          int colCount = 1;  
        while (colCount <= iCol)  
        {  
            if (worksheet.Cell(iRow, colCount).Value != "")  
            {  
                     dr[colCount - 1] = worksheet.Cell(iRow, colCount).Value;  
                     rowVal = false;  
            }  
              colCount++;  
          }  
          if (rowVal)  
          {  
             canRowRead = false;  
          }  
          else  
          {  
                   dt.Rows.Add(dr);  
                   iRow++;  
          }  
        }  
    }  

In the above code snippet, Initially we are checking the file extension whether it is an excel document or not. In next step i am saving the file in a temporary location and reading the same document using excel package.

After that i am reading the first worksheet from the excel document, and applying the while loop to read the columns here the logic is in first row of excel always should be header so i am reading the first row of excel and framing as data table columns. Remaining rows i am treating as rows in data table.

In the above code snippet i did not applied any validation of rows and columns. 

When reading the excel sheet i faced an issue for date columns, it will come as number not as date time i.e it will come as 38976 for the data 12-08-1986.

We need to convert the number in to date using the below code snippet.

DateTime doj = DateTime.FromOADate(Convert.ToDouble(dt.Rows[i][4].ToString().Trim()));
string doj2 = Convert.ToDateTime(doj).ToString("MM-dd-yyy");

I hope this post will be useful.