将DataGrid输出到Excel文件--踢西工作室

将DataGrid输出到Excel文件

时间：2006/5/25 20:55:01 阅读：4116

在Web From上输出数据到Excel有两种方法，一个是有数据库直接导出；另外一个方法是由DataGrid直接输出到Excel文件。下面得代码实现了这两个功能。注意：在使用时要引用Microsoft Office Web Components 9.0 COM组件，另外注意设置要保存文件得目录具有匿名可修改的权限。

DataGridToExcel.aspx

 1<%...@ Page Language="vb" EnableViewState="False" AutoEventWireup="false" Codebehind="DataGridToExcel.aspx.vb"
 2 Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
 3DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
 4<HTML>
 5  <HEAD>
 6    <title id="mengxianhui" runat="server">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  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">asp:Label>
15      <asp:TextBox ID="xlfile" Runat="server">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">ItemStyle>
24        <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000">HeaderStyle>
25        <Columns>
26          <asp:BoundColumn DataField="Title">asp:BoundColumn>
27          <asp:BoundColumn DataField="Author">asp:BoundColumn>
28        Columns>
29      asp:DataGrid>
30    form>
31  body>
32HTML>

DataGridToExcel.aspx.vb

  1Imports System
  2Imports System.Data
  3Imports System.Data.OleDb
  4Imports OWC
  5
  6Public 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
 19Web 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
136End Class

Re:将DataGrid输出到Excel文件 (2006/6/12 12:28:53) by 王丹
版主，给您个建议，您发文章当有代码的时候，请不要把行号放在上面，否则我们在copy的时候好麻烦哦。
Re:将DataGrid输出到Excel文件 (2006/6/12 16:13:34) by 踢西
谢谢提醒，下次发时去掉行号

标题：

姓名：

主页：

验证码：

评论：

记住我

Because of the cache,you may see your comments several minutes later.

web3.cn

Re:将DataGrid输出到Excel文件 (2006/6/12 12:28:53) by 王丹

Re:将DataGrid输出到Excel文件 (2006/6/12 16:13:34) by 踢西