在asp.net中将Excel文件数据显示到Gridview中 [英] Display Excel file data into Gridview in asp.net

问题描述

我正在将一个excel文件数据导入gridview。我有一个fileupload控件，我需要选择一个excel文件，我有一个gridview和按钮，用户需要在选择一个excel文件后点击这样我才能显示我的将文件数据excel到gridview



有些网站的代码无法正常运行，因为它们正在通过路径，但路径来自哪里!!!!我读了一些文章，因为安全原因，fileupoad没有保留路径所以无论我试图看到哪个文件都在飞，所以我之后做了什么，我将excel文件保存在我的根目录中并在完成其工作后删除该文件。



所以我只是想知道这是正确的做法???



请建议我





谢谢。

解决方案

试用此代码



放置客户端

< asp：按钮ID =   btnExportToExcel runat =   server Text =  导出到Excel CssClass = < span class =code-string>  btn btn-primary OnClick =   btnExportToExcel_Click /> 

在代码隐藏文件中

 受保护  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]; 
  //  生成xml时，删除xml工作表中的某些字段。 
 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（）; 
} 

当我绑定gridview的那段时间我 VIewstate [s] 用于生成特定数据



快乐编码： - ）

试试这个





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



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

使用C＃，VB.NET将Excel数据导入/上传到Asp.net Gridview 
作者：Suresh Dasari 2012年12月17日
类别：Asp.net，C＃.Net，ExcelSheet，Gridview， VB.NET 
简介：
 
这里我将解释如何使用C＃，VB.NET中的OLEDB将数据从excel导入到grid.net中的gridview。 
描述：
 
在前一篇文章中，我解释了如何将gridview数据导出到excel / word文档，将Excel数据导入到sql数据库以及与导出gridview数据相关的其他一些文章。现在我将解释如何在asp.net中将数据从excel导入gridview。 
 
首先要实现这个概念我们需要创建一个excel文件，如下所示
 
一旦excel创建完成，我们需要创建新的网站并在你的aspx中编写以下代码page 
 
 <   html     xmlns   =  http：// www .w3.org / 1999 / xhtml >  
 <  < span class =code-leadattribute> head  >  
 <   title  > 从ASP.NET中的Excel文件（.xsl或.xlsx）读取和显示数据<   / title  >  
 <   / head  >  
 <  正文 >  
 < ;  表格    id  < span class =code-keyword> =  form1    runat   =  server >  
 <   div  >  
 <   b  > 请选择Excel文件：<   / b  >  
 <   asp：FileUpload     ID   =  fileuploadExcel    runat   =  server    /  > & nbsp;& nbsp; 
 <   asp：按钮    ID   =  btnImport    runat   = 服务器   文字  =  导入数据    OnClick   =  btnImport_Click    /  >  
 <   br     /  >  
 <   asp：Label     ID   =  lblMessage    runat   =  server   可见  = 错误   < span class =code-attribute> Font-Bold   =  True    ForeColor   = ＃009933 >  <   / asp：Label  >  <   br     /  > ;  
 <   asp：GridView     ID   =  grvExcelData    runat   =  server >  
 <   HeaderStyle     BackColor   = ＃df5015    Font-Bold   =  true    ForeColor   = 白色    /  >  
 <   / asp：GridView  >  
 <   / div  >  
 <   / form  < span class =code-keyword>>  
 <   / body  >  
 <   / html < /跨度> <温泉n class =code-keyword>>  
现在打开代码隐藏文件并使用System添加以下命名空间
 
 
; 
使用System.Data; 
使用System.Data.OleDb; 
使用System.IO; 
之后在代码中编写以下代码
 
 C＃.NET代码
 
 
 protected void btnImport_Click（object sender，EventArgs e）
 {
 string connString =; 
 string strFileType = Path.GetExtension（fileuploadExcel.FileName）。ToLower（）; 
 string path = fileuploadExcel.PostedFile.FileName; 
 // Excel工作簿的连接字符串
 if（strFileType.Trim（）==。xls）
 {
 connString =Provider = Microsoft.Jet.OLEDB.4.0 ;数据源=+路径+;扩展属性= \Excel 8.0; HDR =是; IMEX = 2 \; 
} 
 else if（strFileType.Trim（）==。xlsx）
 {
 connString =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = + path +;扩展属性= \Excel 12.0; HDR =是; 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代码：


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
'到Excel工作簿的连接字符串
如果strFileType.Trim（）=。xls那么
connString =Provider = Microsoft.Jet.OLEDB。 4.0;数据来源=& path__1& ;扩展属性=Excel 8.0; HDR =是; IMEX = 2
ElseIf strFileType.Trim（）=。xlsx然后
connString =Provider = Microsoft.ACE.OLEDB .12.0;数据来源=& path__1& ;扩展属性=Excel 12.0; HDR =是; IMEX = 2
结束如果
Dim query As String =SELECT [UserName]，[Education]，[Location] FROM [ Sheet 1中

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.

解决方案

Try This Code

Put Client Side

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

In Code Behind File

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 :-)

Try this


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

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

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

这篇关于在asp.net中将Excel文件数据显示到Gridview中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！