Category >
ASP.NET
|| Published on :
Tuesday, February 9, 2016 || Views:
9812
||
Upload And Read Excel File into DataSet in Asp.Net using C# Upload And Read Excel File
Introduction
Here Pawan Kumar will explain how to Upload And Read Excel File into DataSet in Asp.Net using C#
Description
In previous post I have explained
Asp.Net Serialization & Deserialization with C#.Net,
jQuery to Dynamically Change or Set Placeholder Text in Asp.Net TextBox,
jQuery to Validate File Type and Size before Uploading through Asp.Net FileUpload Control,
How to clear the file upload control value using jQuery / JavaScript,
Export GridView selected rows to Excel or word in ASP.NET using CSharp,
How to allow numbers, backspace, delete, left and right arrow and Tab Keys to the TextBox using Javascript or JQuery in ASP.NET, and many more articles.
Now I will explain How to Upload And Read Excel File into DataSet in Asp.Net using C#
So follow the steps to learn Upload And Read Excel File into DataSet in Asp.Net using C#
Upload And Read Excel File into DataSet in Asp.Net using C#
Step 1: Create a new website in Visual Studio 2010.
Step 2: Add a new asp.net web page to the website created eariler.
Step 3: Add the following codes to aspx web page.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Upload And Read Excel File into DataSet in Asp.Net using C#</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
<td>
<asp:Button ID="Button_Upload" runat="server" Text="Upload Template" OnClick="Button_Upload_Click"
Width="190px" OnClientClick="return confirm('Are you sure you want to upload');" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="gvData" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
AutoGenerateColumns="False" BorderStyle="None" BorderWidth="1px" CellPadding="3"
Font-Names="Georgia" Font-Size="Small" Width="475px" CellSpacing="2">
<Columns>
<asp:BoundField HeaderText="User ID" DataField="User_ID" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Gender" DataField="Gender" />
<asp:BoundField HeaderText="Country" DataField="Country" />
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Step 4: Add the below code in Code Behind file.
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
public partial class _Default : System.Web.UI.Page
{
protected void Button_Upload_Click(object sender, EventArgs e)
{
if ((FileUpload1.PostedFile != null))
{
if (!string.IsNullOrEmpty(FileUpload1.PostedFile.FileName))
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = "ExcelFiles";
string FilePath = Server.MapPath("~/" + FolderPath + "\\" + FileName);
FileUpload1.SaveAs(FilePath);
DataSet ds_ExcelData = ReadExcelData(FilePath);
gvData.DataSource = ds_ExcelData.Tables[1];
gvData.DataBind();
}
}
}
public DataSet ReadExcelData(string fileName)
{
string strCon = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + fileName;
OdbcConnection con = new OdbcConnection(strCon);
DataSet ds = new DataSet();
con.Open();
DataTable dt_SheetNames = con.GetSchema("Tables");
ds.Tables.Add(dt_SheetNames);
for (int i = 0; i < dt_SheetNames.Rows.Count; i++)
{
OdbcCommand cmd = new OdbcCommand(@"SELECT * FROM [" + dt_SheetNames.Rows[i]["TABLE_NAME"].ToString() + "]");
cmd.Connection = con;
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(ds, dt_SheetNames.Rows[i]["TABLE_NAME"].ToString());
}
con.Close();
return ds;
}
}
Step 5: Screenshots
Conclusion:
So, In this tutorial we have learned, Upload And Read Excel File into DataSet in Asp.Net using C#
Download Source Codes