News
IBM to offer mobile security as a service IBM will start delivering mobile security-as-a-service (MaaS) through its own cloud infrastructure in India, a move that is expected to better address in-country data requirements... IBM Spectrum Computing: IBM has enlarged its portfolio of software-defined infrastructure solutions with cognitive features for workload management.  * LiFi internet technology has been introduced, the new type of wireless internet connection that gives 100 times faster than traditional WiFi -- Invented by Professor Harald Haas from the University of Edinburgh. * Sci. Rachid Yazami has developed a smart chip that charges smartphones in less than 10 minutes. BenQ has launched BlueCore projector - Consumer electronics major BenQ has launched its first BlueCore laser light source projector. For those unaware, devices with BlueCore laser technology have a high contrast output of 80000:1 with an extended lamp life and efficiency. ***
  Feb 25 2016 10:20AM     Micheal
  1 Comments    730 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 NameEmployee


            

Here, I have used OleDbConnectionOleDbCommand 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 { getset; }

        public string Designation { getset; }

        public int?  Experience { getset; }
    }

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/

 
Search
1&1 Hosting
Recent Posts
Bind Gridview from CSV file in Asp.Net C#
Jun 18 2016 11:38PM Posted By Michael
Call WebService method from jQuery in every 1 minute
Feb 27 2016 11:51AM Posted By John
Gridview custom CSS in ASP.Net
Feb 25 2016 10:59AM Posted By Micheal Ryan
Read excel file and bind to Gridview in C#
Feb 25 2016 10:20AM Posted By Micheal
Read excel file and convert to List in C#
Feb 25 2016 6:26AM Posted By Michael
Encryption and Decryption for Password in C#
Feb 17 2016 3:52AM Posted By Michael
How to create Chart in ASP.Net C#
Feb 16 2016 8:03AM Posted By Michael Ryan
Tags
Follow us on Facebook
Follow us on Google +
Recent post in your Email inbox.
Enter your email address: