Add App_Code And App_Data oledb connectiuon...
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelData.aspx.cs" Inherits="ExcelData" %>
<!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>
Import Excel File:
<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<br />
<asp:Button ID="btnUpload" runat="server" Text="Upload"
onclick="btnUpload_Click" />
<br />
<br />
<asp:Label ID="Label1" runat="server"></asp:Label>
<br />
<asp:GridView ID="gvExcelFile" runat="server" CellPadding="2" ForeColor="Black"
GridLines="None" BackColor="LightGoldenrodYellow" BorderColor="Tan"
BorderWidth="1px">
<AlternatingRowStyle BackColor="PaleGoldenrod" />
<FooterStyle BackColor="Tan" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<SortedAscendingCellStyle BackColor="#FAFAE7" />
<SortedAscendingHeaderStyle BackColor="#DAC09E" />
<SortedDescendingCellStyle BackColor="#E1DB9C" />
<SortedDescendingHeaderStyle BackColor="#C2A47B" />
</asp:GridView>
<br />
<asp:Button ID="btnExport" runat="server" Text="Export to Excel" onclick="btnExport_Click1"
/>
</div>
</form>
</body>
</html>
.cs file...
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
public partial class ExcelData : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//Coneection String by default empty
string ConStr = "";
//Extantion of the file upload control saving into ext because
//there are two types of extation .xls and .xlsx of Excel
string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
//getting the path of the file
string path = Server.MapPath("~/MyFolder/" + FileUpload1.FileName);
//saving the file inside the MyFolder of the server
FileUpload1.SaveAs(path);
Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";
//checking that extantion is .xls or .xlsx
if (ext.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (ext.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the Excel data to data set
da.Fill(ds);
//set data source of the grid view
gvExcelFile.DataSource = ds.Tables[0];
//binding the gridview
gvExcelFile.DataBind();
//close the connection
conn.Close();
}
protected void btnExport_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvExcelFile.AllowPaging = false;
//BindGridview();
//Change the Header Row back to white color
gvExcelFile.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < gvExcelFile.HeaderRow.Cells.Count; i++)
{
gvExcelFile.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
}
gvExcelFile.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
protected void btnExport_Click1(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
gvExcelFile.AllowPaging = false;
//this.BindGrid();
gvExcelFile.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in gvExcelFile.HeaderRow.Cells)
{
cell.BackColor = gvExcelFile.HeaderStyle.BackColor;
}
foreach (GridViewRow row in gvExcelFile.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = gvExcelFile.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = gvExcelFile.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
gvExcelFile.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
}
------------------------------------------------------------------------------------------------------------