在Web From上输出数据到Excel有两种方法,一个是有数据库直接导出;另外一个方法是由DataGrid直接输出到Excel文件。下面得代码实现了这两个功能。注意:在使用时要引用Microsoft Office Web Components 9.0 COM组件,另外注意设置要保存文件得目录具有匿名可修改的权限。
1
<%...@ Page Language="vb" EnableViewState="False" AutoEventWireup="false" Codebehind="DataGridToExcel.aspx.vb"
2
Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
3
DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
4
<HTML>
5
<HEAD>
6
<title id="mengxianhui" runat="server">< SPAN>title>
7
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
8
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
9
<meta name="vs_defaultClientScript" content="JavaScript">
10
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
11
< SPAN>HEAD>
12
<body MS_POSITIONING="GridLayout" style="FONT-SIZE:9pt">
13
<form id="Form1" method="post" runat="server">
14
<asp:Label id="Label1" runat="server">< SPAN>asp:Label>
15
<asp:TextBox ID="xlfile" Runat="server">< SPAN>asp:TextBox>
16
<br>
17
<br>
18
<asp:Button ID="ExportDataBase2Excel" Runat="server" />
19
<asp:Button ID="ExportDataGrid2Excel" Runat="server" />
20
<br>
21
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#CC9966"
22
BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
23
<ItemStyle ForeColor="#330099" BackColor="White">< SPAN>ItemStyle>
24
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000">< SPAN>HeaderStyle>
25
<Columns>
26
<asp:BoundColumn DataField="Title">< SPAN>asp:BoundColumn>
27
<asp:BoundColumn DataField="Author">< SPAN>asp:BoundColumn>
28
< SPAN>Columns>
29
< SPAN>asp:DataGrid>
30
< SPAN>form>
31
< SPAN>body>
32
< SPAN>HTML>
1
Imports System
2
Imports System.Data
3
Imports System.Data.OleDb
4
Imports OWC
5
6
Public Class DataGridToExcelClass DataGridToExcel
7
Inherits System.Web.UI.Page
8
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
9
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
10
Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
11
Protected WithEvents ExportDataBase2Excel As System.Web.UI.WebControls.Button
12
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
13
Protected mengxianhui As New HtmlGenericControl()
14
15
Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
16
+ Server.MapPath("Test.mdb"))
17
Private sql As OleDbCommand = New OleDbCommand("SELECT TOP 50 Title,Author FROM Document", cnn)
18
19
Web Form Designer Generated Code#Region " Web Form Designer Generated Code "
20
21
'This call is required by the Web Form Designer.
22
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()Sub InitializeComponent()
23
24
End Sub
25
26
Private Sub Page_Init()Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)_
27
Handles MyBase.Init
28
'CODEGEN: This method call is required by the Web Form Designer
29
'Do not modify it using the code editor.
30
InitializeComponent()
31
End Sub
32
33
#End Region
34
35
Private Sub Page_Load()Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
36
Handles MyBase.Load
37
Label1.Text = "请输入要保存得文件名字:"
38
ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件"
39
ExportDataBase2Excel.Text = "数据库直接生成Excel文件"
40
DataGrid1.Columns(0).HeaderStyle.HorizontalAlign = HorizontalAlign.Center
41
DataGrid1.Columns(0).HeaderText = "文章名称"
42
DataGrid1.Columns(1).HeaderText = "作者"
43
DataGrid1.Columns(0).HeaderStyle.Font.Bold = True
44
DataGrid1.Style.Add("font-size", "9pt")
45
mengxianhui.InnerText = "【踢西工作室】- 将DataGrid输出到Excel文件"
46
Me.BindDataGrid()
47
End Sub
48
49
Private Sub BindDataGrid()Sub BindDataGrid()
50
cnn.Open()
51
Dim reader As OleDbDataReader = sql.ExecuteReader()
52
Me.DataGrid1.DataSource = reader
53
Me.DataGrid1.DataBind()
54
reader.Close()
55
cnn.Close()
56
End Sub
57
58
Private Sub WriteDataGrid2Excel()Sub WriteDataGrid2Excel()
59
Dim xlsheet As New SpreadsheetClass()
60
cnn.Open()
61
Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
62
Dim numbercols As Integer = reader.FieldCount
63
Dim row As Integer = 2
64
Dim i As Integer = 0
65
' 输出标题
66
For i = 0 To numbercols - 1
67
xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
68
Next
69
70
' 输出字段内容
71
While (reader.Read())
72
For i = 0 To numbercols - 1
73
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
74
Next
75
row = row + 1
76
End While
77
reader.Close()
78
cnn.Close()
79
Try
80
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
81
OWC.SheetExportActionEnum.ssExportActionNone)
82
Catch e As System.Runtime.InteropServices.COMException
83
Response.Write("错误:" + e.Message)
84
End Try
85
End Sub
86
87
Private Sub WriteDataGrid2Excel2()Sub WriteDataGrid2Excel2()
88
Dim xlsheet As New SpreadsheetClass()
89
Dim i As Integer = 0
90
Dim j As Integer = 0
91
'Response.End()
92
' 输出标题
93
Dim oItem As DataGridColumn
94
For Each oItem In DataGrid1.Columns
95
xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
96
'xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),_
97
xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True
98
'设置格式
99
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
100
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
101
i = i + 1
102
Next
103
104
Dim numbercols As Integer = DataGrid1.Items.Item(0).Cells.Count
105
' 输出字段内容
106
For j = 0 To DataGrid1.Items.Count - 1
107
For i = 0 To numbercols - 1
108
xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
109
'xlsheet.Range("A2:B14").WrapText = True
110
xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
111
xlsheet.ActiveSheet.Cells(j + 2, i + 1) = DataGrid1.Items.Item(j).Cells(i).Text.Replace(" ", " ")
112
Next
113
Next
114
Try
115
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
116
OWC.SheetExportActionEnum.ssExportActionNone)
117
Catch e As System.Runtime.InteropServices.COMException
118
Response.Write("错误:" + e.Message)
119
End Try
120
End Sub
121
122
Private Sub ExportDataGrid2Excel_Click()Sub ExportDataGrid2Excel_Click(ByVal sender As Object,_
123
ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
124
If (Me.xlfile.Text.Trim() <> "") Then
125
Me.WriteDataGrid2Excel2()
126
End If
127
End Sub
128
129
Private Sub ExportDataBase2Excel_Click()Sub ExportDataBase2Excel_Click(ByVal sender As Object, _
130
ByVal e As System.EventArgs) Handles ExportDataBase2Excel.Click
131
If (Me.xlfile.Text.Trim() <> "") Then
132
Me.WriteDataGrid2Excel()
133
End If
134
End Sub
135
136
End Class