Display Excel file data into Gridview in asp.net

Question

0.00/5 (No votes)

See more:

I am importing an excel file data into a gridview.I have one fileupload control where I need to select an excel file and I have a gridview and button where user need to click after choosing one excel file so that I can show my excel file data into gridview

some sites are having code which are not working becuase they are passing the path but where the path is coming from!!!! I read some article that fileupoad doesn't keep path due to security reasons so whatever file I am trying to see that is flying, So what I did after, I saved that excel file in my root directory and delete that file after completed its work.

So I just want to know that it is the correct way to do???

Please suggest me

Thanks.

Posted 7-Jun-13 1:41am

Prafulla Sahu

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kunal_Patel · Answer 1 · 2013-09-08T21:58:00

Try This Code

Put Client Side

VB

<asp:Button ID="btnExportToExcel" runat="server" Text="Export To Excel" CssClass="btn btn-primary" OnClick="btnExportToExcel_Click" />

In Code Behind File

C#

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
 Response.Clear();
 Response.AddHeader("content-disposition", "attachment;filename=SurveyDetail.xls");
 Response.Charset = "";
 Response.ContentType = "application/vnd.xls";
 System.IO.StringWriter stringWrite = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DataTable dt = (DataTable)ViewState["s"];
// when generate xml that time remove  some field in xml sheet.
 dt.Columns.RemoveAt(0);
 dt.Columns.RemoveAt(1);
 dt.Columns.RemoveAt(dt.Columns.Count - 1);
 GridView gv = new GridView();
 gv.DataSource = dt;
 gv.AllowPaging = false;
 gv.DataBind();
gv.RenderControl(htmlWrite);
 Response.Write(stringWrite.ToString());
Response.End();
}

when i have bind the gridview that time i taking VIewstate["s"] for generate perticular data

Happy Coding :-)

Emad Al Hawary · Answer 2 · 2013-09-08T00:08:00

Try this

http://www.aspdotnet-suresh.com/2012/12/how-

to-import-data-from-excel-to-aspnet.html[^]

XML

Import/Upload Excel Data to Asp.net Gridview in C#, VB.NET
By: Suresh Dasari Dec 17, 2012
Categories: Asp.net , C#.Net , ExcelSheet , Gridview , VB.NET
Introduction:

Here I will explain how to import data from excel to gridview in asp.net using OLEDB in C#, VB.NET.
Description:

In previous article I explained How to Export gridview data to excel/word document, Import Excel data to sql database and some other articles relating to export gridview data. Now I will explain how to import data from excel to gridview in asp.net.

To implement this concept first we need to create one excel file like as shown below

Once excel creation done we need to create new website and write the following code in your aspx page

<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();
}
VB.NET Code:


Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim connString As String = ""
Dim strFileType As String = Path.GetExtension(fileuploadExcel.FileName).ToLower()
Dim path__1 As String = fileuploadExcel.PostedFile.FileName
'Connection String to Excel Workbook
If strFileType.Trim() = ".xls" Then
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path__1 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
ElseIf strFileType.Trim() = ".xlsx" Then
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path__1 & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
End If
Dim query As String = "SELECT [UserName],[Education],[Location] FROM [Sheet1$]"
Dim conn As New OleDbConnection(connString)
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Dim cmd As New OleDbCommand(query, conn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
grvExcelData.DataSource = ds.Tables(0)
grvExcelData.DataBind()
da.Dispose()
conn.Close()
conn.Dispose()
End Sub
End Class

Member 11240670 · Answer 3 · 2015-08-11T08:22:00

.aspx file

XML

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="Trial._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<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>

</asp:Content>

.aspx.cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Trial
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnImport_Click(object sender, EventArgs e)
{
string connString = "";
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
string path1 = fileuploadExcel.PostedFile.FileName;
string path = System.IO.Path.GetFullPath(Server.MapPath(path1));
//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();
}
}
}

Raju Bollam · Answer 4 · 2013-06-07T01:56:00

C#

protected void btnDetails_Click(object sender, EventArgs e)
        {

            string path = Server.MapPath("~/Upload/" + fluDocUpload.FileName);
// fluDocUpload.FileName is fileupload control name
            if (!File.Exists(path.ToString().ToUpper()))
            {
                fluDocUpload.SaveAs(path);
            }

            /* connection string  to work with excel file. HDR=Yes - indicates 
               that the first row contains columnnames, not data. HDR=No - indicates 
               the opposite. "IMEX=1;" tells the driver to always read "intermixed" 
               (numbers, dates, strings etc) data columns as text. 
            Note that this option might affect excel sheet write access negative. */

            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
              path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand(); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            // selecting distict list of EmpNo 
            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [Sheet1$]";
//[Sheet1$] is Excel sheet name in your file
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds, "dsSlno");
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                grvData.DataSource = ds.Tables[0].DefaultView;
                grvData.DataBind();
            }
            else
            {
                grvData.DataSource = null;
                grvData.DataBind();
            }

        }

Jubayer Ahmed · Answer 5 · 2014-02-08T23:03:00

Solution 4

You can try this link:

http://jubayerdevs.blogspot.hk/2013/02/import-data-from-excel-to-sqlserver.html[^]

http://jubayerdevs.blogspot.hk/2013/10/store-excel-data-to-datatable-and-copy.html[^]

Posted 8-Feb-14 23:03pm

Jubayer Ahmed

Updated 8-Feb-14 23:05pm

v2

Display Excel file data into Gridview in asp.net

5 solutions

Solution 3

Solution 2

Solution 5

Solution 1

Solution 4

Add your solution here

Preview 0