.net将Excel文件导入到GridView中

最新推荐文章于 2012-02-15 20:36:23 发布

xue1225go

最新推荐文章于 2012-02-15 20:36:23 发布

阅读量3.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： .NET 文章标签： excel .net string dataset exception server

本文链接：https://blog.youkuaiyun.com/xue1225go/article/details/4076620

.NET 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个使用C#从Excel文件中读取数据并展示在网页上的示例。该示例包含BaseFunction类中的数据获取方法及ASP.NET页面的实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 新建BaseFunction.cs文件,如下:

using System;

using System.Data;

using System.Data.OleDb;

public class BaseFunction

{

/// <summary>

/// 取得上传文件的数据集

/// </summary>

/// <param name="strUpFileExtName">上传文件扩展名</param>

/// <param name="strUpFileName">上传文件名</param>

/// <param name="strUpPath">上传的路径</param>

/// <param name="strSplitChar">拆分字符或Excel表名</param>

/// <param name="aryField">要导入的数据字段数组</param>

/// <returns></returns>

public static DataSet GetOleDbData(string strUpFileExtName, string strUpFileName, string strUpPath, string strSplitChar, string[] aryField, bool preview)

{

string strDBDatabaseName;

string DBSQL;

if (preview)

{

DBSQL = "select top 10 * from {0}";

}

else

{

DBSQL = "select * from {0}";

}

string oleConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties={1}";

switch (strUpFileExtName)

{

case ".xls":

{

strDBDatabaseName = strUpPath + strUpFileName;//excel数据库完整文件名作为数据源

DBSQL = String.Format(DBSQL, "[" + strSplitChar + "$]");//"SELECT * FROM ["+tbExcel.Text+"＄]"; //硬性规定只能在Sheet1工作表中存放数据

oleConnStr = String.Format(oleConnStr, strDBDatabaseName, "Excel 8.0");

break;

}

//通过ole数据提供者获得数据

OleDbConnection oleConnection = new OleDbConnection(oleConnStr);

OleDbDataAdapter dbdat = new OleDbDataAdapter(DBSQL, oleConnection);

DataSet upDs = new DataSet();

try

{

dbdat.Fill(upDs, "UpTable");

if (upDs.Tables["UpTable"].Columns.Count > aryField.Length)

{

//上传的字段不合法

throw new Exception("上传的文件字段不符合规则!列数为" + upDs.Tables["UpTable"].Columns.Count.ToString());

}

catch (Exception ex)

{

throw new Exception("查询上传数据时出错！" + ex.Message);

}

finally

{

if (dbdat != null)

{

dbdat.Dispose();

dbdat = null;

}

if (oleConnection != null)

{

oleConnection.Dispose();

oleConnection = null;

}

return upDs;

}

2. Default.aspx代码如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<asp:FileUpload ID="fileUploadExcel" runat="server" />

<asp:Button ID="btnView" runat="server" onclick="btnView_Click" Text="查看" />

<asp:GridView ID="gvData" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>

3.Default.aspx.cs代码如下:

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.IO;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnView_Click(object sender, EventArgs e)

{

string[] aryField = new string[] { "Col1", "Col2", "Col3" };

//全路径

string path = fileUploadExcel.PostedFile.FileName;

//文件后缀名

string strUpFileExtName = Path.GetExtension(path);

//文件名

string strUpFileName = fileUploadExcel.FileName.Replace(".xls", "");

//去掉文件名后的路径

string strUpPath = path.Remove(path.IndexOf(strUpFileName));

//文件分隔符

string strSplitChar = "sheet1";

if (strUpFileExtName != ".xls")

{

}

else

{

DataSet objds = BaseFunction.GetOleDbData(strUpFileExtName, strUpFileName, strUpPath, strSplitChar, aryField, false);

DataView dv = new DataView(objds.Tables["UpTable"]);

gvData.DataSource = dv;

gvData.DataBind();

}

4. 新建Excel文件,里面内容如下: