In this blog we will know how to upload a excel file and
display the contents of the file into a Gridview.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Display_excel_files_in_Gridview._Default"
%>
<!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>Untitled Page</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<asp:Table
ID="TableButtons"
runat="server">
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell ID="TableCell1" runat="server">
<asp:Button ID="ButtonUpload" runat="server"
Text="Upload Excel
Files" OnClick="ButtonUpload_Click" />
</asp:TableCell>
<asp:TableCell ID="TableCell2" runat="server">
<asp:Button ID="ButtonView" runat="server"
Text="View Excel Data" OnClick="ButtonView_Click" />
</asp:TableCell>
</asp:TableRow>
</asp:Table>
<asp:Panel
ID="PanelUpload"
runat="server"
Visible="False">
<asp:FileUpload ID="FileUploadExcel" runat="server" />
<br />
Please select an Excel file to import:<br />
<asp:Button ID="ButtonUploadFile" runat="server"
Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />
<asp:Label ID="LabelUpload"
runat="server"
Text=""></asp:Label>
</asp:Panel>
<asp:Panel
ID="PanelView"
runat="server"
Visible="False">
<asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Panel
ID="PanelImport"
runat="server"
Visible="False">
<asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Label
ID="lblError"
runat="server"
Text=""></asp:Label>
</div>
</form>
</body>
</html>
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace
Display_excel_files_in_Gridview
{
public partial
class _Default
: System.Web.UI.Page
{
protected void ButtonUploadFile_Click(object
sender, EventArgs e)
{
if
(FileUploadExcel.HasFile)
{
try
{
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
LabelUpload.Text = "Upload
File Name: " +
FileUploadExcel.PostedFile.FileName + "<br>" +
"Type:
" + FileUploadExcel.PostedFile.ContentType +
" File
Size: " + FileUploadExcel.PostedFile.ContentLength +
"
kb<br>";
}
catch (System.NullReferenceException ex)
{
LabelUpload.Text = "Error:
" + ex.Message;
}
}
else
{
LabelUpload.Text = "Please
select a file to upload.";
}
}
protected OleDbCommand ExcelConnection()
{
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended
Properties=Excel 8.0;";
OleDbConnection Con
= new OleDbConnection(ConnStr);
Con.Open();
OleDbCommand com = new
OleDbCommand("SELECT
* FROM [sheet1$]", Con);
return com;
}
protected void ButtonView_Click(object
sender, System.EventArgs e)
{
PanelUpload.Visible = false;
PanelView.Visible = true;
PanelImport.Visible = false;
OleDbDataAdapter
oledbda = new OleDbDataAdapter();
oledbda.SelectCommand = ExcelConnection();
DataSet ds = new DataSet();
oledbda.Fill(ds);
GridViewExcel.DataSource = ds.Tables[0].DefaultView;
GridViewExcel.DataBind();
}
protected void ButtonUpload_Click(object
sender, System.EventArgs e)
{
PanelUpload.Visible = true;
PanelView.Visible = false;
PanelImport.Visible = false;
}
}
}