how to display data from excel and display in gridview

Posted by Klbaiju under ASP.NET on 10/3/2013 | Points: 10 | Views : 2946 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
in my application i want to export data from excel sheet and display in gridview.

How it is possible
Regards
Baiju

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 10/3/2013 [Member] [MVP] Platinum | Points: 25

0	<html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</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"></asp:Label><br /> <asp:GridView ID="grvExcelData" runat="server"> <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /> </asp:GridView> </div> </form> </body> </html> Now open code behind file and add the following namespaces using System; using System.Data; using System.Data.OleDb; using System.IO; After that write the following code in code behind C#.NET Code protected void btnImport_Click(object sender, EventArgs e) { string connString = ""; string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower(); string path = fileuploadExcel.PostedFile.FileName; //Connection String to Excel Workbook 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\""; } string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]"; OleDbConnection conn = new OleDbConnection(connString); if (conn.State == ConnectionState.Closed) conn.Open(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); grvExcelData.DataSource = ds.Tables[0]; grvExcelData.DataBind(); da.Dispose(); conn.Close(); conn.Dispose(); } NOTE: To test above example, Create Excel file named as "TestExcel" with Sheet1 and columns UserName, Education, Location References: http://www.codeproject.com/Questions/629852/Import-Upload-Excel-Data-to-Asp-net-Gridview-in-Cs Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID="fileuploadExcel" runat="server" />&nbsp;&nbsp;
<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"></asp:Label><br />
<asp:GridView ID="grvExcelData" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>

Now open code behind file and add the following namespaces

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

After that write the following code in code behind

C#.NET Code


protected void btnImport_Click(object sender, EventArgs e)
{
string connString = "";
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
string path = fileuploadExcel.PostedFile.FileName;
//Connection String to Excel Workbook
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\"";
}
string query = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
grvExcelData.DataSource = ds.Tables[0];
grvExcelData.DataBind();
da.Dispose();
conn.Close();
conn.Dispose();
}

NOTE: To test above example, Create Excel file named as "TestExcel" with Sheet1 and columns UserName, Education, Location

References:
http://www.codeproject.com/Questions/629852/Import-Upload-Excel-Data-to-Asp-net-Gridview-in-Cs

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Katie on: 11/2/2021 [Member] Starter | Points: 25

0	Exporting data from Excel to DataTable or DataGridView is an easy task if you use Spire.XLS for .NET , which is a class library for processing Excel documents on .NET platform. Here is the code example for your reference: //Create a new workbook Workbook workbook = new Workbook(); //Load a file and imports its data workbook.LoadFromFile(@”..\FandH.xlsx”); //Initialize worksheet Worksheet sheet = workbook.Worksheets[0]; // get the data source that the grid is displaying data for this.dataGridView1.DataSource = sheet.ExportDataTable(); Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Exporting data from Excel to DataTable or DataGridView is an easy task if you use Spire.XLS for .NET , which is a class library for processing Excel documents on .NET platform.

Here is the code example for your reference:

//Create a new workbook
Workbook workbook = new Workbook();       
//Load a file and imports its data
workbook.LoadFromFile(@”..\FandH.xlsx”);          
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
// get the data source that the grid is displaying data for
this.dataGridView1.DataSource = sheet.ExportDataTable();

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts