利用ASP.NET來讀取Excel(.xls)或Access(.mdb)檔,並且以GridView來顯示結果
最近剛好有網友問到這個問題..所以就寫個範例來解答網友...
首先要先準備好Excel或Access檔....完整程式碼如下(C#範例):
ReadExcel_CS.aspx
01 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ReadExcel_CS.aspx.cs" Inherits="ReadExcel_CS" %>
02
03 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
04 <html xmlns="http://www.w3.org/1999/xhtml">
05 <head id="Head1" runat="server">
06 <title>未命名頁面</title>
07 </head>
08 <body>
09 <form id="form1" runat="server">
10 <div>
11 <asp:GridView ID="GridView1" runat="server">
12 </asp:GridView>
13 <asp:GridView ID="GridView2" runat="server">
14 </asp:GridView>
15 </div>
16 </form>
17 </body>
18 </html>
19
02
03 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
04 <html xmlns="http://www.w3.org/1999/xhtml">
05 <head id="Head1" runat="server">
06 <title>未命名頁面</title>
07 </head>
08 <body>
09 <form id="form1" runat="server">
10 <div>
11 <asp:GridView ID="GridView1" runat="server">
12 </asp:GridView>
13 <asp:GridView ID="GridView2" runat="server">
14 </asp:GridView>
15 </div>
16 </form>
17 </body>
18 </html>
19
ReadExcel_CS.aspx.cs
01
using System;
02
using System.Data;
03
using System.Configuration;
04
using System.Collections;
05
using System.Web;
06
using System.Web.Security;
07
using System.Web.UI;
08
using System.Web.UI.WebControls;
09
using System.Web.UI.WebControls.WebParts;
10
using System.Web.UI.HtmlControls;
11
using System.Data.OleDb;
12
13
public partial class ReadExcel_CS : System.Web.UI.Page
14
{
15
protected void Page_Load(object sender, EventArgs e)
16
{
17
//read excel file
18
string strConn;
19
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
20
"Data Source=C:\\test.xls;" +
21
"Extended Properties=Excel 8.0;";
22
23 OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
24
25 DataSet myDataSet = new DataSet();
26
myCommand.Fill(myDataSet, "ExcelInfo");
27
this.GridView1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
28
this.GridView1.DataBind();
29
30 //read access file
31
string strConn2;
32
strConn2 = "Provider=Microsoft.Jet.OLEDB.4.0;" +
33
"Data Source=c:\\test.mdb;" +
34
"Persist Security Info=True;";
35
36 OleDbDataAdapter myCommand2 = new OleDbDataAdapter("select * from test", strConn2);
37
38 DataSet myDataSet2 = new DataSet();
39
myCommand2.Fill(myDataSet2, "AccessInfo");
40
this.GridView2.DataSource = myDataSet2.Tables["AccessInfo"].DefaultView;
41
this.GridView2.DataBind();
42
43
}
44
}
45

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

21

22

23 OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
24

25 DataSet myDataSet = new DataSet();
26

27

28

29

30 //read access file
31

32

33

34

35

36 OleDbDataAdapter myCommand2 = new OleDbDataAdapter("select * from test", strConn2);
37

38 DataSet myDataSet2 = new DataSet();
39

40

41

42

43

44

45