ASP.NET2.0 中讀取Excel 檔技巧(Read Excel) 使用c#,VB
在商用程式設計中，User使用Excel做為資料存取的中心是很
常見的，但在ASP.NET2.0 如何讀取Excel檔案的文件相對就少
了許多，讓edr888告訴你怎樣讀取Excel檔吧！
ASP.NET中讀取Excel資料檔有好幾個方法，edr888就針對最常
用的方法做說明：
a.要有一Excel檔案 Orz
b.在Excel檔中預設有三個Sheet分別為Sheet1、Sheet2、Sheet3
假設資料放置於Sheet1中(同理類推)
c.程式碼說明使用C#語法複製內容到剪貼板
代碼:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class MyPage0905 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable MyFirstTable;
string strExcelConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:test.xls;Extended Properties=EXCEL 8.0";
// 假設檔案放置於c: test.xls 請讀者自行更改位置
string strExcelSelect = "SELECT * FROM [Sheet1$]";
//需在Sheet1前後加 」[」 與 」]」 符號，並於Sheet1後加入」$」符號，否則會發生Microsoft Jet 資料庫引擎無法找到 'Sheet1' 物件的錯誤
DataSet ds = new DataSet();
OleDbConnection myExcelConn = null;
myExcelConn = new OleDbConnection(strExcelConn);
OleDbCommand myExcelCommand = new OleDbCommand(strExcelSelect, myExcelConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myExcelCommand);myExcelConn.Open();
myDataAdapter.Fill(ds, "ReadData");
myExcelConn.Close();
MyFirstTable = ds.Tables["ReadData"];GridView1.DataSource = MyFirstTable;GridView1.DataBind();
}
}
d.程式碼說明使用VB語法
Imports System.Data.OleDb
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim MyFirstTable As DataTable
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:book1.xls;Extended Properties=EXCEL 8.0"
'假設檔案放置於c:test.xls 請讀者自行更改位置
Dim strExcelSelect As String = "SELECT * FROM [Sheet1$]"
'需在Sheet1前後加 」[」 與 」]」 符號，並於Sheet1後加入」$」符號，否則會發生Microsoft Jet 資料庫引擎無法找到 'Sheet1' 物件的錯誤
Dim ds As DataSet = New DataSet
Dim myExcelConn As OleDbConnection = Nothing
myExcelConn = New OleDbConnection(strExcelConn)
Dim myExcelCommand As OleDbCommand = New OleDbCommand(strExcelSelect, myExcelConn)
Dim myDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(myExcelCommand)
myExcelConn.Open()
myDataAdapter.Fill(ds, "ReadData")
myExcelConn.Close()
MyFirstTable = ds.Tables("ReadData")
GridView1.DataSource = MyFirstTable
GridView1.DataBind()
End Sub
End Class
Just Try It!
