Introduction:
In the dynamic realm of web development, the ability to seamlessly import and export data is a crucial skill. In this article, we will explore the process of loading data from an Excel sheet into a GridView in ASP.NET, utilizing the power of the OLEDB class in C#.
Excel to GridView - A Two-Step Process:
Our journey begins with a step-by-step walkthrough of the process. In a previous article, we covered how to create an Excel sheet with header columns through coding. Now, we will delve into the steps required to upload Excel file data into a GridView through C# coding and subsequently export GridView data into an Excel sheet file.
HTML Markup: Design Page
The HTML markup plays a vital role in creating the design page, complete with essential controls like FileUpload, Button, RadioButtonList, and GridView. The structured markup ensures a user-friendly interface for seamless data handling.
<div>
<asp:FileUpload ID="FileUpload1" runat="server" BorderColor="#999999"
BorderStyle="Solid" BorderWidth="1px" Height="25px" Width="250px" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text="Has Header ?" /><br /><br />
<asp:RadioButtonList ID="rbHDR" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >
</asp:ListItem>
<asp:ListItem Text = "No" Value = "No"></asp:ListItem>
</asp:RadioButtonList><br />
<asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging"
AllowPaging = "True" BackColor="White" BorderColor="#3366CC" BorderStyle="None"
BorderWidth="1px" CellPadding="4">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />
</asp:GridView>
</div>
C# Coding: Namespace and Upload Button Click Event:
The C# coding section commences with the inclusion of necessary namespaces and the implementation of the btnUpload_Click event. This event is triggered when the user uploads an Excel file, initiating the process of filling and displaying data in the GridView.
// C# Coding: Namespace
using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
// C# Coding: Upload Button Click Event
public partial class ExceltoGridview : 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);
FillGrid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
}
C# Coding: BindGridview and PageIndexChanging Events:
The heart of the data manipulation lies in the FillGrid method, responsible for reading data from the Excel sheet and binding it to the GridView. Additionally, the PageIndexChanging event ensures smooth paging functionality.
// C# Coding: BindGridview
private void FillGrid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
DataTable dt = new DataTable();
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}
// C# Coding: GridView PageIndexChanging
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);
FillGrid(FilePath, Extension, rbHDR.SelectedItem.Text);
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
}
Download Source Code:
Conclusion:
In conclusion, this article has guided you through the intricacies of importing data from an Excel sheet into a GridView and exporting GridView data into an Excel sheet in ASP.NET using C#. Armed with this knowledge, you can enhance your web applications with efficient data handling, providing users with a seamless and interactive experience. Feel free to download the project and explore the capabilities firsthand. Happy coding!
0 Comments