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

1using System.IO;

2using System.Data.OleDb;

3using System.Data;

Write below mentioned code in Click Event of Upload Button

C# CODE

01protected void btnUpload_Click(object sender, EventArgs e)

02    {

03        string connectionString ="";

04        if (FileUpload1.HasFile)

05        {

06            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

07            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);

08            string fileLocation = Server.MapPath("~/App_Data/" + fileName);

09            FileUpload1.SaveAs(fileLocation);

10 

11            //Check whether file extension is xls or xslx

12 

13            if (fileExtension == ".xls")

14            {

15                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

16            }

17            else if (fileExtension == ".xlsx")

18            {

19                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

20            }

21 

22            //Create OleDB Connection and OleDb Command

23 

24            OleDbConnection con = new OleDbConnection(connectionString);

25            OleDbCommand cmd = new OleDbCommand();

26            cmd.CommandType = System.Data.CommandType.Text;

27            cmd.Connection = con;

28            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);

29            DataTable dtExcelRecords = new DataTable();

30            con.Open();

31            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

32            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();

33            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName +"]";

34            dAdapter.SelectCommand = cmd;

35            dAdapter.Fill(dtExcelRecords);

36            con.Close();

37            GridView1.DataSource = dtExcelRecords;

38            GridView1.DataBind();

39        }

40    }

VB.NET CODE

01Protected Sub btnUpload_Click(sender As Object, e As EventArgs)

02 Dim connectionString As String = ""

03 If FileUpload1.HasFile Then

04  Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

05  Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

06  Dim fileLocation As String = Server.MapPath("~/App_Data/" & fileName)

07  FileUpload1.SaveAs(fileLocation)

08 

09  'Check whether file extension is xls or xslx

10 

11  If fileExtension = ".xls" Then

12   connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

13  ElseIf fileExtension = ".xlsx" Then

14   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""

15  End If

16 

17  'Create OleDB Connection and OleDb Command

18 

19  Dim con As New OleDbConnection(connectionString)

20  Dim cmd As New OleDbCommand()

21  cmd.CommandType = System.Data.CommandType.Text

22  cmd.Connection = con

23  Dim dAdapter As New OleDbDataAdapter(cmd)

24  Dim dtExcelRecords As New DataTable()

25  con.Open()

26  Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

27  Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()

28  cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"

29  dAdapter.SelectCommand = cmd

30  dAdapter.Fill(dtExcelRecords)

31  con.Close()

32  GridView1.DataSource = dtExcelRecords

33  GridView1.DataBind()

34 End If

35End 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.

1connectionString = "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();