Upload And Read Excel File In Asp.Net

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

show source

1using System.IO;

2using System.Data.OleDb;

3using System.Data;

Write below mentioned code in Click Event of Upload Button

C# CODE

show source

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

show source

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.

show source

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();

Now it will read the excel sheet without any errors.