Sunday, January 11, 2015

Import Excel Data to Datatable or Dataset or Gridview in C#


Here I will explain how to Import excel data into datatable or dataset in ASP.NET C#, or Import data from excel to gridview in using OLEDB in C#

The below code in your aspx page
<html xmlns="">
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>
<form id="form1" runat="server">
<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" />
Open your Code behind file and add the following namespaces

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

 C#.NET Code for Export all Excel data into GridView.

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*  FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
grvExcelData.DataSource = ds.Tables[0];

No comments:

Post a Comment