无为清净楼资源网 Design By www.qnjia.com
我们的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的$]是两个不同的查询。
复制代码 代码如下:
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数据
复制代码 代码如下:
<%@ 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的$]是两个不同的查询。
复制代码 代码如下:
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数据
无为清净楼资源网 Design By www.qnjia.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com
暂无评论...
P70系列延期,华为新旗舰将在下月发布
3月20日消息,近期博主@数码闲聊站 透露,原定三月份发布的华为新旗舰P70系列延期发布,预计4月份上市。
而博主@定焦数码 爆料,华为的P70系列在定位上已经超过了Mate60,成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢?
根据目前爆料的消息来看,华为P70系列将推出三个版本,其中P70和P70 Pro采用了三角形的摄像头模组设计,而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智,但辨识度绝对拉满。
更新日志
2024年09月26日
2024年09月26日
- 纯音入心系列纯音乐《中央民族乐团-古筝传奇》1CD[MP3][253.1MB]
- 江玲.1989-这样你才爱我,是吗?【新作有声】【WAV+CUE】
- 郭美美.2010-我是郭美美【华纳】【WAV+CUE】
- 黄心懋1991-轻忧郁【滚石】【WAV+CUE】
- 群星.2000-大地金曲世纪回顾2CD【大地】【WAV+CUE】
- 王菲.1996-浮躁(2024环球MQA-UHQCD限量版)【环球】【WAV+CUE】
- 群星.2022-传家电视剧原声带【东阳欢娱】【FLAC分轨】
- 《摇滚教父 伍佰黄金精选 2CD》 [WAV+CUE][1GB]
- 《凤凰传奇 我从草原来》[WAV/分轨][400MB]
- 《叶倩文 真心真意过一生》[WAV+CUE][400MB]
- 潘越云.1985-世间女子(滚石25周年经典复刻版)【滚石】【WAV+CUE】
- 陈柏宇.2009-CANT.BE.HALF【SONY】【WAV+CUE】
- 李明依.1989-小女生【滚石】【WAV+CUE】
- 《李荣浩 新时代创作新人王》[WAV分轨][310MB]
- 《黑鸭子与马头琴 史上最动听草原音乐》[WAV+CUE][320MB]