Upload And Read Excel File Into DataTable DataSet Asp.Net

This example explains How To Upload And Read Excel File Data Into DataTable DataSet Using FileUpload and display in gridview using C# and VB.NET in Asp.Net.

First of all put a fileUpload control, and a GridView in design view of aspx page to upload and display excel file data.

Now place a button on the page, in click even of this button we will be uploading the excel file in a folder on server and read it's content.

HTML SOURCE OF PAGE

<form id="form1" runat="server">
<div>
    
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" 
            Height="21px" Text="Upload" 
            Width="92px" onclick="btnUpload_Click"/>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>

Add these namespaces in code behind of page

using System.IO;
using System.Data.OleDb;
using System.Data;

Write below mentioned code in Click Event of Upload Button

C# CODE

protected void btnUpload_Click(object sender, EventArgs e)
    {
        string connectionString ="";
        if (FileUpload1.HasFile)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/App_Data/" + fileName);
            FileUpload1.SaveAs(fileLocation);
            
            //Check whether file extension is xls or xslx

            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 
            }
            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }

            //Create OleDB Connection and OleDb Command

            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            con.Close();
            GridView1.DataSource = dtExcelRecords;
            GridView1.DataBind();
        }
    }

VB.NET CODE

Protected Sub btnUpload_Click(sender As Object, e As EventArgs)
 Dim connectionString As String = ""
 If FileUpload1.HasFile Then
  Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
  Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
  Dim fileLocation As String = Server.MapPath("~/App_Data/" & fileName)
  FileUpload1.SaveAs(fileLocation)

  'Check whether file extension is xls or xslx

  If fileExtension = ".xls" Then
   connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
  ElseIf fileExtension = ".xlsx" Then
   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
  End If

  'Create OleDB Connection and OleDb Command

  Dim con As New OleDbConnection(connectionString)
  Dim cmd As New OleDbCommand()
  cmd.CommandType = System.Data.CommandType.Text
  cmd.Connection = con
  Dim dAdapter As New OleDbDataAdapter(cmd)
  Dim dtExcelRecords As New DataTable()
  con.Open()
  Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
  Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()
  cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
  dAdapter.SelectCommand = cmd
  dAdapter.Fill(dtExcelRecords)
  con.Close()
  GridView1.DataSource = dtExcelRecords
  GridView1.DataBind()
 End If
End Sub

Build and run the application.

In this code if the excel sheet contains text characters or special characters in numeric field like EmpID, then it's not read by C# or VB.NET and display blank in gridview as shown in Image.

The reason for this is excel doesn't handle mixed data format very well, entry like 1A or 1-A etc doesn't get read by this code.

To fix this error we need to make some changes in connection string of excel, and need to add some extended properties, change the connection string as shown below.

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();

Now it will read the excel sheet without any errors.

Download Sample Code

Tweet

If you like this post than join us or share