使用asp.net读取并显示excel数据
来源:互联网 发布:淘宝钻展要多少钱 编辑:程序博客网 时间:2024/06/06 02:32
核心提示:Microsoft Office Excel是一个很好的电子表格应用程序,在本文中,it同学会将教给你看到如何使用ASP.NET从Excel电子表格读取并显示显示数据。
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的$]是两个不同的查询。
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;
- }
- }
ittxh.cn使用上面的代码进行测试,得到的结果如下所示:
以上就是使用asp.net读取并显示excel数据
- 使用asp.net读取并显示excel数据
- 使用asp.net读取并显示excel数据
- asp.net 读取并显示excel数据的实现代码
- asp.net读取Excel表并显示在页面中
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- ASP.NET中上传并读取Excel文件数据
- net读取excel中数据并显示到gridview中
- ASP.NET中上传并读取Excel文件数据,附后生成EXCEL及杀掉EXCEL进程。
- ASP.NET中上传并读取Excel文件数据,附后生成EXCEL及杀掉EXCEL进程
- 如何读取EXCEL 表格文件并使他在WEB网页上显示(使用ASP.NET)
- CentOS 5.4下基于Jabber/XMPP协议的Openfire服务器配置笔记
- 无线帮帮忙 笔记本之间如何共享WiFi
- Android 适配不同分辨率&hdpi、mdpi、ldpi&横屏竖屏
- ASP.NET MVC2, MVC3 & VS2010学习
- 【Catalan数】【同余】【SCOI2010】字符串
- 使用asp.net读取并显示excel数据
- Struts中报错——404 input.jsp
- http://www.th7.cn/Program/java/2011/10/19/44335.shtml
- 数据库情结之三范式
- linux下 单网卡配置多Ip
- CFS调度的总结 - (单rq vs 多rq)
- JAVA的垃圾回收机制、异常处理机制
- linux内核阅读总结
- 设计模式学习