Hi friends,in this article I will explain about how to upload Excel File and then read and import its data into DataSet or DataTable using C# and VB.Net in ASP.Net Web Application.
I already explained in the previous articles about Creating Dynamic CSS Menu From Database SQL Server in ASP.Net Using C#.Net/VB.NET, JQuery: Send (Pass) Data (Values) from one page to another using Form Post and Search GridView records (data) on TextBox KeyPress using jQuery in ASP.NET using C#/VB.NET
The imported Excel File data is then displayed in ASP.Net GridView control.
I already explained in the previous articles about Creating Dynamic CSS Menu From Database SQL Server in ASP.Net Using C#.Net/VB.NET, JQuery: Send (Pass) Data (Values) from one page to another using Form Post and Search GridView records (data) on TextBox KeyPress using jQuery in ASP.NET using C#/VB.NET
The imported Excel File data is then displayed in ASP.Net GridView control.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Upload
And Read Excel Data/File into DataSet in Asp.Net using C#/VB.NET</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="White"
BorderColor="#CC9966"
AutoGenerateColumns="false"
BorderStyle="Solid"
BorderWidth="1px"
CellPadding="4"
Font-Names="Georgia"
Font-Size="Small"
Width="475px">
<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="Tan"
/>
<AlternatingRowStyle
BackColor="#E6E6E1"
/>
<FooterStyle BackColor="#FFFFCC"
ForeColor="#330099"
/>
<HeaderStyle BackColor="#990000"
Font-Bold="True"
ForeColor="#FFFFCC"
/>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
|
C#:
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 ReadDataFromExcel
: 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;
}
}
|
VB.NET:
Imports
System.Collections.Generic
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Configuration
Imports System.Data
Imports System.Data.Odbc
Partial Public
Class ReadDataFromExcelVB
Inherits
System.Web.UI.Page
Protected
Sub Button_Upload_Click(sender As Object, e As EventArgs)
If (FileUpload1.PostedFile IsNot
Nothing) Then
If Not String.IsNullOrEmpty(FileUpload1.PostedFile.FileName)
Then
Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Dim FolderPath As String = "ExcelFiles"
Dim FilePath As String = Server.MapPath(Convert.ToString((Convert.ToString("~/")
& FolderPath) + "\") &
FileName)
FileUpload1.SaveAs(FilePath)
Dim ds_ExcelData As
DataSet = ReadExcelData(FilePath)
gvData.DataSource = ds_ExcelData.Tables(1)
gvData.DataBind()
End If
End If
End
Sub
Public
Function ReadExcelData(fileName As String) As DataSet
Dim strCon As String = Convert.ToString("Driver={Microsoft Excel Driver
(*.xls)};DBQ=") & fileName
Dim con As New OdbcConnection(strCon)
Dim ds As New DataSet()
con.Open()
Dim dt_SheetNames As
DataTable = con.GetSchema("Tables")
ds.Tables.Add(dt_SheetNames)
For i As Integer = 0 To
dt_SheetNames.Rows.Count - 1
Dim cmd As New OdbcCommand("SELECT * FROM [" +
dt_SheetNames.Rows(i)("TABLE_NAME").ToString()
+ "]")
cmd.Connection =
con
Dim da As New OdbcDataAdapter(cmd)
da.Fill(ds,
dt_SheetNames.Rows(i)("TABLE_NAME").ToString())
Next
con.Close()
Return ds
End
Function
End Class
|
When you choose excel file and click on Upload Template then excel data will be shown in GridView.
You can download the code by clicking on the below Download image.
No comments:
Post a Comment