Note: Add Folder Named (Files) in the project
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="_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>Import Excel Data into GridView</title></head><body><form id="form1" runat="server"><div><asp:FileUpload ID="FileUpload1" runat="server" /><asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" /><br /><asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label><asp:RadioButtonList ID="rbHDR" runat="server"><asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" ></asp:ListItem><asp:ListItem Text = "No" Value = "No"></asp:ListItem></asp:RadioButtonList><asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true" ></asp:GridView></div></form></body></html>
CS.aspx.cs-----------using System;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data;using System.Data.OleDb;using System.IO;using System.Configuration;public partial class _Default : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){}protected void btnUpload_Click(object sender, EventArgs e){if (FileUpload1.HasFile){string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);string FolderPath = ConfigurationManager.AppSettings["FolderPath"];string FilePath = Server.MapPath(FolderPath + FileName);FileUpload1.SaveAs(FilePath);Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);}}private void Import_To_Grid(string FilePath, string Extension, string isHDR){string conStr="";switch (Extension){case ".xls": //Excel 97-03conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;break;case ".xlsx": //Excel 07conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;break;}conStr = String.Format(conStr, FilePath, isHDR);OleDbConnection connExcel = new OleDbConnection(conStr);OleDbCommand cmdExcel = new OleDbCommand();OleDbDataAdapter oda = new OleDbDataAdapter();DataTable dt = new DataTable();cmdExcel.Connection = connExcel;//Get the name of First SheetconnExcel.Open();DataTable dtExcelSchema;dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();connExcel.Close();//Read Data from First SheetconnExcel.Open();cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";oda.SelectCommand = cmdExcel;oda.Fill(dt);connExcel.Close();//Bind Data to GridViewGridView1.Caption = Path.GetFileName(FilePath);GridView1.DataSource = dt;GridView1.DataBind();}protected void PageIndexChanging(object sender, GridViewPageEventArgs e){string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;string FileName = GridView1.Caption;string Extension = Path.GetExtension(FileName);string FilePath = Server.MapPath(FolderPath + FileName);Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);GridView1.PageIndex = e.NewPageIndex;GridView1.DataBind();}}
Web.Config----------------<appSettings><add key="FolderPath" value="Files/"/></appSettings><connectionStrings><add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/><add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/></connectionStrings>
if you get the following Exception :
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Read this Thread:-------------------http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64trying to connect to an Access database in visual studio but keep getting this error?try installing this first: http://www.microsoft.com/download/en/details.aspx?id=13255however if, like me, that doesnt work for you, try the following method:NOTE: this DOES work for office 2010 even though it is for 2007 office, dont ask me why it just does :)1. download and install this: http://www.microsoft.com/download/en/confirmation.aspx?id=237342. in VS click add data source, follow the wizard and enjoy! :)P.S thanks to everyone that helped me at my original thread: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ebb05600-055a-44c4-8cd3-d33a79878b23
0 comments:
Post a Comment