Read excel file and bind to Gridview in C#

Feb 25 2017 10:20AM

Micheal

1 Comments

2650 Views

Here Michael provided a short description with example for reading excel file content and bind to gridview.

Excel File:

File Details:

Excel File Name: Employee.xls

Excel Sheet Name: Employee

Here, I have used OleDbConnection, OleDbCommand and OleDbDataAdapter to read .xls and .xlsx files.

Using OleDbConnection we can read the excel faster and efficiently.

Supportable extensions .xls and .xlsx

HTML Code:

Copy the HTML code and paste it in your design page.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style>
        .grdExcelContent
        {
            width: 80%;
            border: solid 2px black;
            min-width: 80%;
        }
        .header
        {
            background-color: #646464;
            font-family: Arial;
            color: White;
            border: none 0px transparent;
            height: 25px;
            text-align: center;
            font-size: 16px;
        }
        .rows
        {
            background-color: #fff;
            font-family: Arial;
            font-size: 14px;
            color: #000;
            min-height: 25px;
            text-align: left;
            border: none 0px transparent;
        }
        .rows:hover
        {
            background-color: #ff8000;
            font-family: Arial;
            color: #fff;
            text-align: left;
        }
        .selectedrow
        {
            background-color: #ff8000;
            font-family: Arial;
            color: #fff;
            font-weight: bold;
            text-align: left;
        }
        .mydatagrid a /** FOR THE PAGING ICONS  **/
        {
            background-color: Transparent;
            padding: 5px 5px 5px 5px;
            color: #fff;
            text-decoration: none;
            font-weight: bold;
        }
        .mydatagrid a:hover /** FOR THE PAGING ICONS  HOVER STYLES**/
        {
            background-color: #000;
            color: #fff;
        }
        .mydatagrid span /** FOR THE PAGING ICONS CURRENT PAGE INDICATOR **/
        {
            background-color: #c9c9c9;
            color: #000;
            padding: 5px 5px 5px 5px;
        }
        .pager
        {
            background-color: #646464;
            font-family: Arial;
            color: White;
            height: 30px;
            text-align: left;
        }
        .mydatagrid td
        {
            padding: 5px;
        }
        .mydatagrid th
        {
            padding: 5px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div align="center">
            <h4>
                The Developer Tips - Beyond the Knowledge</h4>
            <h2>
                ExcelSheet content in GridView</h2>
            <asp:GridView ID="grdExcelContent" runat="server" Width="50%" CssClass="mydatagrid" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" RowStyle-CssClass="rows"
            AllowPaging="True">
            </asp:GridView>
        </div>
    </div>
    </form>
</body>
</html>

Namespace:

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

C# Code:

We have to create a class with the properties. Properties are columns or fields in the excel sheet.

Employee Class:

public class Employee
    {
        public string Name { get; set; }

        public string Designation { get; set; }

        public int? Experience { get; set; }
    }

OleDb Connection Open:

  /// <summary>
        /// Open Connection
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private OleDbConnection OpenConnection(string path)
        {
             OleDbConnection oledbConn = null;
             try
             {
                 if (Path.GetExtension(path) == ".xls")
                    oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path +

"; Extended Properties= \"Excel 8.0;HDR=Yes;IMEX=2\"");
                else if (Path.GetExtension(path) == ".xlsx")
                    oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" +

path + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");

                oledbConn.Open();
            }
            catch (Exception ex)
            {
                //Error
            }
            return oledbConn;
        }

Extract Excel File:

/// <summary>
        /// Extract 'Service Information' sheet
        /// </summary>
        /// <param name="oledbConn"></param>
        private IList<Employee> ExtractEmployeeExcel(OleDbConnection oledbConn)
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet dsEmployeeInfo = new DataSet ();

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Employee$]"; //Excel Sheet Name ( Employee )
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill (dsEmployeeInfo, "Employee");

            var dsEm ployeeInfoList = dsEmployeeInfo.Tables[0].AsEnumerable().Select (s => new Employee
            {
                Name = Convert.ToString(s["Name"] != DBNull.Value ? s["Name"] : ""),
                Designation = Convert.ToString(s["Designation"] != DBNull.Value ? s["Designation"] : ""),
                Experience = Convert.ToInt32(s["Experience"])
            }).ToList ();

            return dsEmployeeInfoList;
        }

To Read and Convert to List:

/// <summary>
        /// Read excel sheet with the user path
        /// </summary>
        /// <param name="path"></param>
        public IList<Employee> ReadExcel(string path)
        {
            IList<Employee> objEmployeeInfo = new List<Employee>();
            try
             {
                OleDbConnection oledbConn = OpenConnection(path);
                if (oledbConn.State == ConnectionState.Open)
                {
                    objEmployeeInfo = ExtractEmployeeExcel (oledbConn);
                    oledbConn.Close();
                }
            }
            catch (Exception ex)
            {
                // Error
            }
            return objEmployeeInfo;
        }

The above method will return the excel content in the list format.

Use of Code:

Here calling the ReadExcel() method to get the excel content and bind it to Gridview.

protected void Page_Load(object sender, EventArgs e)
        {
            string path = @"D:\Employee.xls";
            IList<Employee> objExcelCon =ExcelProcessor.ReadExcel(path);
            grdExcelContent.DataSource = objExcelCon;
            grdExcelContent.DataBind();
        }

Sample Result:

Here I gave simple example about read excel file and bind it to gridview. I hope it will helps. Thanks.

BackToTop

Comments

Zz Lorreta
May 4 2016 6:59PM

rasteredge can provide youc# add comments to pdf reader, and download it to try it free on rasteredge page http://www.rasteredge.com/how-to/csharp-imaging/pdf-html5-feature-annotate/