Upload Excel File And Display in GridView Control in Webpage
First Create web Page and
File Upload control and gridview
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelFile.aspx.cs"
Inherits="ExcelFile"
%>
<!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:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload"
runat="server"
Height="21px" Text="Upload"
Width="92px" onclick="btnUpload_Click"/>
</div>
<div>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView></div>
</form>
</body>
</html>
Now Apply code in .cs file
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.IO;
public
partial class ExcelFile : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//string Path;
string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/File/" + fileName);
FileUpload1.SaveAs(fileLocation);
//Check whether file
extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd =
new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter
= new OleDbDataAdapter(cmd);
DataTable dtExcelRecords
= new DataTable();
con.Open();
DataTable dtExcelSheetName
= con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName
+ "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();
}
}
}
No comments:
Post a Comment
Comment Here