服务器之家:专注于服务器技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|JAVA教程|ASP教程|

asp.net 读取并显示excel数据的实现代码

2019-07-08 12:32IT同学会 ASP.NET教程

Microsoft Office Excel是一个很好的电子表格应用程序,在本文中,it同学会将教给你看到如何使用ASP.NET从Excel电子表格读取并显示显示数据。

我们的ASP页面将在远程服务器上,来读取我们的桌面Excel文件。首先,我们必须把它上传到远程服务器,然后retrive数据。因此,我们首先设计一个表格,上传到服务器。我们必须从文件retrive数据,再一次,所以我们将重新命名Excel,然后上传。
复制代码代码如下:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title> 
<style type="text/css"> 
tr.sectiontableentry1 td, 
tr.sectiontableentry2 td { 
padding: 4px; 

tr.sectiontableentry1 td { 
padding: 8px 5px; 
background: url(hline.gif) repeat-x bottom; 

tr.sectiontableentry2 td { 
padding: 8px 5px; 
background: url(hline.gif) repeat-x bottom #F2F2F2; 

</style> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div> 
<table style="padding: 5px; font-size: 11px;" align="center" border="0"> 
<tbody> 
<tr> 
<td> 
<strong>Please Select Excel file containing job details…</strong> 
</td> 
</tr> 
<tr> 
<td> 
<div style="background: url(hline.gif) repeat-x bottom #F2F2F2;padding: 8px 5px;border-bottom: 1px solid #ccc;"> 
<asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>   
<asp:Button ID="btnUpload" runat="server" Text="Upload" /><br /> 
<asp:Label ID="lblMessage" runat="server" Visible="False" Font-Bold="True" 
ForeColor="#009933"></asp:Label> 
</div> 
</td> 
</tr> 
<tr> 
<td> 
<asp:GridView ID="dtgJobs" runat="server"> 
<RowStyle CssClass="sectiontableentry2" /> 
<AlternatingRowStyle CssClass="sectiontableentry1" /> 
</asp:GridView> 
</td> 
</tr> 
</tbody> 
</table> 
</div> 
</form> 
</body> 
</html> 

连接使用Microsoft OLE DB提供的Excel jet 
在Microsoft OLE DB提供用于Jet(联合发动机技术站是一个数据库引擎)提供的OLE DB接口,Microsoft Access数据库,并允许SQL Server 2005和更高分布式查询来查询Access数据库和Excel电子表格。我们将连接到Microsoft Excel工作簿使用Jet 4.0的Microsoft OLE DB提供, 
读取数据,然后显示在GridView中的数据。 
xlsx(Excel 2007年)载有提供者Microsoft.ACE.OLEDB.12.0。这是新的Access数据库引擎的OLE DB驱动程序,也是阅读Excel 2003的能力。我们将用它来阅读xlsx(Excel 2007年)的数据。 
我们有一个Excel文件,其内容如下所示。注意:此表名称必须相同,意味着,如果想读的Sheet1的数据。你必须小心,同时书面方式的SQL查询,因为选择*从[Sheet1的$]和SELECT *从[Sheet1的$]是两个不同的查询。 
asp.net 读取并显示excel数据的实现代码
复制代码代码如下:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click 
If (txtFilePath.HasFile) Then 
Dim conn As OleDbConnection 
Dim cmd As OleDbCommand 
Dim da As OleDbDataAdapter 
Dim ds As DataSet 
Dim query As String 
Dim connString As String = "" 
Dim strFileName As String = DateTime.Now.ToString("ddMMyyyy_HHmmss") 
Dim strFileType As String = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower() 
‘Check file type 
If strFileType.Trim = ".xls" Or strFileType.Trim = ".xlsx" Then 
txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" & strFileName & strFileType)) 
Else 
lblMessage.Text = "Only excel files allowed" 
lblMessage.ForeColor = Drawing.Color.Red 
lblMessage.Visible = True 
Exit Sub 
End If 
Dim strNewPath As String = Server.MapPath("~/UploadedExcel/" & strFileName & strFileType) 
‘Connection String to Excel Workbook 
If strFileType.Trim = ".xls" Then 
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""" 
ElseIf strFileType.Trim = ".xlsx" Then 
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2""" 
End If 
query = "SELECT * FROM [Sheet1$]" 
‘Create the connection object 
conn = New OleDbConnection(connString) 
‘Open connection 
If conn.State = ConnectionState.Closed Then conn.Open() 
‘Create the command object 
cmd = New OleDbCommand(query, conn) 
da = New OleDbDataAdapter(cmd) 
ds = New DataSet() 
da.Fill(ds) 
grvExcelData.DataSource = ds.Tables(0) 
grvExcelData.DataBind() 
da.Dispose() 
conn.Close() 
conn.Dispose() 
Else 
lblMessage.Text = "Please select an excel file first" 
lblMessage.ForeColor = Drawing.Color.Red 
lblMessage.Visible = True 
End If 
End Sub 

C#.NET Code 
复制代码代码如下:

protected void btnUpload_Click(object sender, EventArgs e) 

if ((txtFilePath.HasFile)) 

OleDbConnection conn = new OleDbConnection(); 
OleDbCommand cmd = new OleDbCommand(); 
OleDbDataAdapter da = new OleDbDataAdapter(); 
DataSet ds = new DataSet(); 
string query = null; 
string connString = ""; 
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss"); 
string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower(); 
//Check file type 
if (strFileType == ".xls" || strFileType == ".xlsx") 

txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType)); 

else 

lblMessage.Text = "Only excel files allowed"; 
lblMessage.ForeColor = System.Drawing.Color.Red; 
lblMessage.Visible = true; 
return; 

string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType); 
//Connection String to Excel Workbook 
if (strFileType.Trim() == ".xls") 

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; 

else if (strFileType.Trim() == ".xlsx") 

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; 

query = "SELECT * FROM [Sheet1$]"; 
//query = "SELECT [Country],[Capital] FROM [Sheet1$] WHERE [Currency]='Rupee'" 
//query = "SELECT [Country],[Capital] FROM [Sheet1$]" 
//Create the connection object 
conn = new OleDbConnection(connString); 
//Open connection 
if (conn.State == ConnectionState.Closed) conn.Open(); 
//Create the command object 
cmd = new OleDbCommand(query, conn); 
da = new OleDbDataAdapter(cmd); 
ds = new DataSet(); 
da.Fill(ds); 
grvExcelData.DataSource = ds.Tables[0]; 
grvExcelData.DataBind(); 
lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count; 
lblMessage.ForeColor = System.Drawing.Color.Green; 
lblMessage.Visible = true; 
da.Dispose(); 
conn.Close(); 
conn.Dispose(); 

else 

lblMessage.Text = "Please select an excel file first"; 
lblMessage.ForeColor = System.Drawing.Color.Red; 
lblMessage.Visible = true; 


使用上面的代码进行测试,得到的结果如下所示:
asp.net 读取并显示excel数据的实现代码
以上就是使用asp.net读取并显示excel数据

延伸 · 阅读

精彩推荐