Connectivity of Excel 2013 to ASP.Net Web Application

Introduction

Thsi article describes how to access an Excel 2013 file in an ASP.NET Web Application. It will help you to access the Excel file information from your web application project.

Prerequisites

Please install the following Office drivers depending upon your Office file:

• Office System Driver for Office 2007
• Office System Driver for Office 2010 or later

Now we will proceed using the following sections:

• Excel file Creation
• Web Application

Excel File Creation

Now we will create an Excel 2013 using the following procedure.

Step 1: Open Excel 2013

Step 2: Create some records as shown below:

Web Application

Now we will create the ASP.NET Web Application with the following steps:

Step 1: Open Visual Studio.

Step 2: Create an ASP.NET Web Application with an Empty Project Template.

Step 3: Add a Web Form from the Solution Explorer and use a GridView in your web form.

Step 4: Add a class named DAL.CS and replace the boilerplate code with the following code:

 

using System.Data;
using System.Data.OleDb;
namespace ExcelApplication
{
    public class DAL
    {
        OleDbDataAdapter DbAdap;
        DataTable dt;
        public DataTable Get_ExcelSheet()
        {
OleDbConnection DbCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Nimit\\ExcelApplication.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"");
            DbAdap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",DbCon);
            dt = new DataTable();
            DbAdap.Fill(dt);
            return dt;
        }
    }
}

 

Step 5: In WebForm1.cs file, modify your code as in the following:

 

using System;
namespace ExcelApplication
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        DAL obj = new DAL();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack == true)
            {
                Get_Data();
            }
        }
        void Get_Data()
        {
            GridView1.DataSource = obj.Get_ExcelSheet();
            GridView1.DataBind();
        }
    }
}

 

Step 6: Open the Configuration Manager of your solution form the Solution Explorer.

Step 7: Select the x86 platform for the application.

Step 8: Debug the application.

Note: Please ensure that the Excel file is closed when you run the application.

Summary

This article will help you to access the Office features like Excel in your ASP.NET Web Application. Happy Coding, Thanks for reading.