程式師世界 >> 編程語言 >> .NET網頁編程 >> .NET實例教程 >> 使用asp.net讀取並顯示excel數據

使用asp.net讀取並顯示excel數據

編輯：.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 align="center" border="0">
<tbody>
<tr>
<td>
<strong>Please Select Excel file containing job details…</strong>
</td>
</tr>
<tr>
<td>
<div >
<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

VB.Net CODE：

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;
}
}