In .aspx page
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Import Data from Excel</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933" Text="Please Wait...."></asp:Label><br />
<asp:GridView ID="grvExcelData" runat="server" CellPadding="4"
ForeColor="#333333" GridLines="None">
<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<div align="center">
<asp:Button ID="btn_Back" runat="server" Text="Back To Home" PostBackUrl="Default.aspx"/>
</div>
</div>
</form>
</body>
</html>
In .aspx.cs page
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Research_BAL;
namespace Research2012
{
public partial class ImportFromExcel : System.Web.UI.Page
{
ImportExcelToGrid oImportExcelToGrid = new ImportExcelToGrid();
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnImport_Click(object sender, EventArgs e)
{
lblMessage.Visible = true;
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
string path = Server.MapPath("~/Upload/") + fileuploadExcel.FileName.ToString();
fileuploadExcel.SaveAs(path);
string query = "SELECT [UserName],[Education],[Location],[Gender] FROM [Sheet1$]";
if (!strFileType.Contains(".xls"))
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "Alert", "alert('Please Upload Excel file');", true);
fileuploadExcel = new FileUpload();
fileuploadExcel.Attributes.Clear();
}
else
{
var dt = new DataTable();
dt = (DataTable)oImportExcelToGrid.ImportToGridView(strFileType, path, query);
if (dt != null)
{
grvExcelData.DataSource = dt;
grvExcelData.DataBind();
}
else
{
ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "Alert", "alert('Please check excel file you upload');", true);
}
}
lblMessage.Visible = false;
}
}
}
Here I am using tier concept. So I mentioned the below code in BAL Class file in the name of ImportExcelToGrid.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.IO;
using System.Data;
namespace Research_BAL
{
public class ImportExcelToGrid
{
public object ImportToGridView(string strFileType, string path, string query)
{
//Connection String to Excel Workbook
string connString = "";
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
OleDbConnection conn = new OleDbConnection();
using (conn = new OleDbConnection(connString))
{
//if (conn.State == ConnectionState.Closed)
// conn.Open();
OleDbCommand cmd = new OleDbCommand();
object res;
using (cmd = new OleDbCommand(query, conn))
{
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
res = ds.Tables[0];
}
catch (Exception ex)
{
res = null;
}
finally
{
da.Dispose();
conn.Close();
conn.Dispose();
if (File.Exists(path))
File.Delete(path);
}
return res;
}
}
}
}
}
No comments:
Post a Comment