How to display data from Excel file in to a GridView?
In this article , I will explains how to display data from Excel file in to a ASP.NET Gridview control using OLEDB data provider. Using this code you can import both .xls and .xlsx type of excel file into your GridView.
Let us start with creating a web application in VS 2010 and follow the below given steps to upload excel and display excel data into a GridView.
Here our goal is to upload Excel file into a folder and display the data in Gridview control using ASP.NET. There are couple of ways to read excel using Microsoft Interop assemblies and OLEDB. Here I have used OLEDB to connect to the excel.
To learn more about uploading files to web server, read Front end Design of application for displaying data from Excel file in to a GridView
First we will design the front end. Here we use one FileUpload control to select the Excel file, a Button control to upload and display the data and finally a GridView to display the excel data in a tabular format.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UploadExcel.aspx.cs"
Inherits="Practice2010.UploadExcel" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpdExcel" runat="server" />
<asp:Button ID="btnDisplay" runat="server" onclick="btnDisplay_Click"
Text="Display Data" />
<br />
<asp:GridView ID="grdExcel" runat="server">
</asp:GridView>
<br />
<asp:Label ID="lblMsg" runat="server"></asp:Label>
</div>
</form>
</body>
</html>Connection Strings used in the application to display data from Excel file in to a GridView
To upload the excel file using ASP.NET we will be using two connection strings in Web.config because Excel 2003 and Excel 2007 use different providers.
Connection string for Excel 2003 (.XLS):-
<add name ="ExcelCon2003" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>
Connection string for Excel 2007 (.XLSX):-
<add name ="ExcelCon2007" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'"/>
In the above connectionString I have used a place holder for DataSource because DataSource will be decided at run time when user selects the Excel file.
When user select the excel file we will be saving the same in a folder. Below web.config entry is used to define the folder name.
<appSettings>
<add key ="UploadFolderPath" value ="Upload/"/>
</appSettings >
Excel file sample used to upload and display in GridviewASP.NET code to upload Excel and display the data into Gridview
We have to add three namespaces,
using System.IO;
using System.Data;
using System.Data.OleDb;
Below is the complete code from code behind.
First we have to create a folder in our root folder based on the name we have mentioned in web.config file. As per the code we have to create a folder with the name "UploadFolderPath". Then on button click event we save the selected excel file in this folder.
To display the data from the uploaded excel we will be using oledb connection. Please make sure to close the oledb connection once you display the data in Gridview otherwise you may get error as "The process cannot access the file because it is being used by another process."
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace Practice2010
{
public partial class UploadExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnDisplay_Click(object sender, EventArgs e)
{
if (FileUpdExcel.HasFile)
{
string strExcelFileName =
Path.GetFileName(FileUpdExcel.PostedFile.FileName);
string strfileExt =
Path.GetExtension(FileUpdExcel.PostedFile.FileName);
string FolderPath =
System.Configuration.ConfigurationManager.
AppSettings["UploadFolderPath"];
string FilePath =
Server.MapPath(FolderPath + strExcelFileName);
FileUpdExcel.SaveAs(FilePath);
string strConString = string.Empty;
if (strfileExt == ".xls")
{
strConString =
System.Configuration.ConfigurationManager.
ConnectionStrings["ExcelCon2003"].ConnectionString;
}
else
{
strConString =
System.Configuration.ConfigurationManager.
ConnectionStrings["ExcelCon2007"].ConnectionString;
}
strConString =
String.Format(strConString, FilePath);
OleDbConnection oledbCon = new OleDbConnection(strConString);
try
{
oledbCon.Open();
OleDbCommand oledbCmd =
new OleDbCommand("select * from [Sheet1$]", oledbCon);
OleDbDataAdapter oledbDa = new OleDbDataAdapter();
oledbDa.SelectCommand = oledbCmd;
DataSet dsExcel = new DataSet();
oledbDa.Fill(dsExcel, "Employee");
grdExcel.DataSource = dsExcel.Tables[0].DefaultView;
grdExcel.DataBind();
}
catch (Exception ex)
{
string strError = ex.Message;
lblMsg.Text = strError;
}
finally
{
oledbCon.Close();
}
}
}
}
}
Excel uploaded on click of the button
Final output, after uploading and displaying the data in Gridview
Another common error you may get when you upload files in the server is "Access denied". In such cases you have to enable Impersonation.
You may get another error if the Excel sheet name is different than the "Sheet1". You need to make sure that you are using the correct sheet name.
For further detail you can visit
http://code.msdn.microsoft.com/office/CSOpenXmlExportImportExcel-cb196388.
Thank a lot..its working perfectly...:)