Reading Excel File Code in C#

In your ASP.NET website you can easily read any excel file using C# code and show its contents on a div.

For this first include the namespaces in your page:

using System.Web;

using System.Data.OleDb;

using System.Text;

Then add the Code to Read Excel File:

string path = Server.MapPath("~/Upload/myexcel.xlsx");

string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'";

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

excelConnection.Open();

string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();

OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection);

StringBuilder sb = new StringBuilder();

sb.Append("<table>");

OleDbDataReader dReader = cmd.ExecuteReader();

do

{

    int count = dReader.FieldCount;

    while (dReader.Read())

    {

        sb.Append("<tr>");

        for (int i = 0; i < count; i++)

        {

            sb.Append("<td>" + dReader.GetValue(i) + "</td>");

        }

        sb.Append("</tr>");

    }

} while (dReader.NextResult());

sb.Append("</table>");

excelConnection.Close();

Explanation: In the above read excel code you can see the variable “path” contains the path of excel file in the website directory. In my case it is in the “Upload” directory which is located on the website root folder.

I used “OLEDB” drivers to read the excel file, make sure to add “HDR=NO” in the connection string extended properties so that excel file’s header columns are also read.

Finally I used the “OleDbDataReader” class to loop though every row and their columns, adding them to my “StringBuilder” object named “sb”.

The “sb” variable contains the excel records in html table format, you can show its value inside a div.

In ASP.NET Web Forms you can do like:

dataDiv.InnerHtml=sb.ToString();

In ASP.NET MVC you can do like:

First saving the sb.ToString() in ViewBag variable in Controller:

ViewBag.Result = sb.ToString();

Then in the view:

<div id="dataDiv">

    @(new HtmlString(ViewBag.Result))

</div>

This is how the excel data will be shown in the div: