Water for asp.net 之十:Excel报表生成原理
source code address：water source
demo address:water demo
blogs address:water bolgs
water中生成Excel报表非常简单，原理图如下：
其实就是利用VB Script生成的Excel报表，利用VBA中的QueryTables得到外部网站的数据，实际上在Excel本身就有这个功能，如：
Excel报表生成原理具体描述如下：（假设我们要开发的报表文件名为Excel.aspx）
Excel.aspx是个一个普通的页面文件，我们可以在这里添加报表控件，设计报表，可以添加查询的数据源和查询条件,添加一个按钮，用以导出Excel，整个报表的设计就这一个文件。
当点击按钮时生成创建创建报表的页面（VB script脚本）和数据页面，页面分别为Excel.aspx?_state=excelreport和Excel.aspx?_state=gettextdata,Water Framewor正是通过_state参数来决定页面的行为的，然后通过得到的数据和脚本生成报表。
Excel.aspx页面
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Excel.aspx.cs" Inherits="demo.Pages.Excel" %>
2
3 <%@ Register Assembly="Water" Namespace="Water.Web.Controls.Office.Excel" TagPrefix="excel" %>
4 <%@ Register Assembly="Water" Namespace="Water.Web.Controls" TagPrefix="page" %>
5 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6 <html xmlns="http://www.w3.org/1999/xhtml">
7 <head runat="server">
8 <title></title>
9 </head>
10 <body>
11 <form id="form1" runat="server">
12 <div>
13 <br />
14 <page:ObjectDataSource ID="ObjectDataSource1" runat="server" CanSelect="true" TypeName="demo.Objects.BLL.Score" QueryControlID="Container1">
15 </page:ObjectDataSource>
16
17 <page:Container ID="Container1" runat="server" TitleText="Excel Report" ContainStyle="height:80px" Width="50%" ViewMode="Query">
18 <QueryTemplate>
19 <page:ComboBox ID="ComboBox1" runat="server" TypeName="demo.Objects.BLL.Student" ReturnTextField="studentname" ReturnValueField="studentid"
20 MatchField="studentname" Prompt="student.studentname" QueryField="studentid" QueryProperty="Value" QueryOperator="Equals">
21 </page:ComboBox>
22 <page:ComboBox ID="ComboBox2" runat="server" TypeName="demo.Objects.BLL.Course" ReturnTextField="Coursename" ReturnValueField="Courseid"
23 MatchField="Coursename" Prompt="course.coursename" QueryField="Courseid" QueryProperty="Value" QueryOperator="Equals">
24 </page:ComboBox>
25 </QueryTemplate>
26 <FooterTemplate>
27 <page:Layout ID="layout2" runat="server">
28 <page:SuperButton ID="SuperButton1" runat="server" ButtonDefaultMode="Button" CommandName="excel" ReportId="Report1" Prompt="sys.excel"
29 IsCallBack="false" />
30 <page:SuperButton ID="SuperButton2" runat="server" ButtonDefaultMode="Button" CommandName="excel" ReportId="Report2" Prompt="导出到Excel(透视表)"
31 IsCallBack="false" />
32 </page:Layout>
33 </FooterTemplate>
34 </page:Container>
35
36 <excel:Report ID="Report1" runat="server">
37 <excel:Sheet ID="sheet1" runat="server" TableType="Table" DataSourceID="ObjectDataSource1" Tilte="学生成绩表" Row="2" Col="1" TableFooterStyle="footer">
38 <Columns>
39 <excel:Column DataField="studentname" HeaderText="student.studentname" />
40 <excel:Column DataField="studentid" HeaderText="student.studentid" />
41 <excel:Column DataField="coursename" HeaderText="course.coursename" />
42 <excel:Column DataField="score" HeaderText="score.score" SummaryFunction="Sum" Style="cell2"/>
43 </Columns>
44 <Labels>
45 <excel:Label Row="1" Col="2" Tilte="学生成绩表" Style="title" />
46 <excel:Label OffsetRow="2" Col="1" Tilte="页脚" Style="footer" />
47 </Labels>
48 </excel:Sheet>
49 </excel:Report>
50
51 <excel:Report ID="Report2" runat="server">
52 <excel:Sheet ID="sheet2" runat="server" TableType="PivotTable" DataSourceID="ObjectDataSource1" >
53 <Columns>
54 <excel:Column DataField="studentname" HeaderText="student.studentname" Orientation="1" Position="1" />
55 <excel:Column DataField="coursename" HeaderText="course.coursename" Orientation="2" Position="1" />
56 <excel:Column DataField="score" HeaderText="score.score" Orientation="4" />
57 </Columns>
58 </excel:Sheet>
59 </excel:Report>
60 </div>
61 </form>
62
63 </body>
64 </html>
65
2
3 <%@ Register Assembly="Water" Namespace="Water.Web.Controls.Office.Excel" TagPrefix="excel" %>
4 <%@ Register Assembly="Water" Namespace="Water.Web.Controls" TagPrefix="page" %>
5 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6 <html xmlns="http://www.w3.org/1999/xhtml">
7 <head runat="server">
8 <title></title>
9 </head>
10 <body>
11 <form id="form1" runat="server">
12 <div>
13 <br />
14 <page:ObjectDataSource ID="ObjectDataSource1" runat="server" CanSelect="true" TypeName="demo.Objects.BLL.Score" QueryControlID="Container1">
15 </page:ObjectDataSource>
16
17 <page:Container ID="Container1" runat="server" TitleText="Excel Report" ContainStyle="height:80px" Width="50%" ViewMode="Query">
18 <QueryTemplate>
19 <page:ComboBox ID="ComboBox1" runat="server" TypeName="demo.Objects.BLL.Student" ReturnTextField="studentname" ReturnValueField="studentid"
20 MatchField="studentname" Prompt="student.studentname" QueryField="studentid" QueryProperty="Value" QueryOperator="Equals">
21 </page:ComboBox>
22 <page:ComboBox ID="ComboBox2" runat="server" TypeName="demo.Objects.BLL.Course" ReturnTextField="Coursename" ReturnValueField="Courseid"
23 MatchField="Coursename" Prompt="course.coursename" QueryField="Courseid" QueryProperty="Value" QueryOperator="Equals">
24 </page:ComboBox>
25 </QueryTemplate>
26 <FooterTemplate>
27 <page:Layout ID="layout2" runat="server">
28 <page:SuperButton ID="SuperButton1" runat="server" ButtonDefaultMode="Button" CommandName="excel" ReportId="Report1" Prompt="sys.excel"
29 IsCallBack="false" />
30 <page:SuperButton ID="SuperButton2" runat="server" ButtonDefaultMode="Button" CommandName="excel" ReportId="Report2" Prompt="导出到Excel(透视表)"
31 IsCallBack="false" />
32 </page:Layout>
33 </FooterTemplate>
34 </page:Container>
35
36 <excel:Report ID="Report1" runat="server">
37 <excel:Sheet ID="sheet1" runat="server" TableType="Table" DataSourceID="ObjectDataSource1" Tilte="学生成绩表" Row="2" Col="1" TableFooterStyle="footer">
38 <Columns>
39 <excel:Column DataField="studentname" HeaderText="student.studentname" />
40 <excel:Column DataField="studentid" HeaderText="student.studentid" />
41 <excel:Column DataField="coursename" HeaderText="course.coursename" />
42 <excel:Column DataField="score" HeaderText="score.score" SummaryFunction="Sum" Style="cell2"/>
43 </Columns>
44 <Labels>
45 <excel:Label Row="1" Col="2" Tilte="学生成绩表" Style="title" />
46 <excel:Label OffsetRow="2" Col="1" Tilte="页脚" Style="footer" />
47 </Labels>
48 </excel:Sheet>
49 </excel:Report>
50
51 <excel:Report ID="Report2" runat="server">
52 <excel:Sheet ID="sheet2" runat="server" TableType="PivotTable" DataSourceID="ObjectDataSource1" >
53 <Columns>
54 <excel:Column DataField="studentname" HeaderText="student.studentname" Orientation="1" Position="1" />
55 <excel:Column DataField="coursename" HeaderText="course.coursename" Orientation="2" Position="1" />
56 <excel:Column DataField="score" HeaderText="score.score" Orientation="4" />
57 </Columns>
58 </excel:Sheet>
59 </excel:Report>
60 </div>
61 </form>
62
63 </body>
64 </html>
65
生成的页面如下：
点击导出到Excel按钮后：
生成的脚本
1 <script language="VBScript">
2
3 window.resizeTo 0,0
4 set Excel = CreateObject("Excel.Application")
5 Excel.Visible = true
6 Excel.WorkBooks.Add("http://localhost:80/demo/App_Foundtion/Templates/default.xlt")
7
8 If Excel.Sheets.Count <1 Then
9 Excel.Sheets.Add
10 Else
11 Excel.Sheets(1).Select
12 End If
13
14 With Excel.ActiveSheet
15 .Select
16 .Name="学生成绩表"
17 End With
18
19 version = CDbl(Excel.Version)
20 Set qt1= Excel.ActiveSheet.QueryTables.Add("TEXT;http://localhost/demo/Pages/Excel.aspx?_state=gettextdata&reportid=Report1&datasessionid=28CC74827C874E2CB3438A40E989BA32",Excel.ActiveSheet.Cells(2,1))
21 With qt1
22 .FieldNames = True
23 .RowNumbers = False
24 .FillAdjacentFormulas = False
25 .PreserveFormatting = True
26 .RefreshOnFileOpen = False
27 .RefreshStyle = 1
28 .SavePassword = False
29 .SaveData = True
30 .AdjustColumnWidth = True
31 .RefreshPeriod = 0
32 .TextFilePromptOnRefresh = False
33 If Version<10 then
34 .TextFilePlatform = 2
35 else
36 .TextFilePlatform = 936
37 end if
38 .TextFileStartRow = 1
39 .TextFileParseType = 1
40 .TextFileTextQualifier = 1
41 .TextFileConsecutiveDelimiter = False
42 .TextFileTabDelimiter = True
43 .TextFileSemicolonDelimiter = False
44 .TextFileCommaDelimiter = False
45 .TextFileSpaceDelimiter = False
46 .TextFileColumnDataTypes = Array(1,1,1,1)
47 .Refresh
48 End With
49 count1=qt1.ResultRange.Rows.Count
50 qt1.ResultRange.Style="cell"
51 qt1.ResultRange.Rows(1).Style="header"
52 qt1.ResultRange.Rows(count1+1).Style="footer"
53
54 With Excel.ActiveSheet
55
56 .Range("D" & count1+2).Formula="=SUM(D3:D" & count1+1 & ")"
57 .Range("D" & count1+2).EntireColumn.AutoFit
58
59 .Range("D3:D" & count1+1).Style="cell2"
60 .Range("D3:D" & count1+1).EntireColumn.AutoFit
61
62 End With
63
64 Excel.ActiveSheet.Cells(1,2).Formula="学生成绩表"
65 Excel.ActiveSheet.Cells(1,2).Style = "title"
66 Excel.ActiveSheet.Cells(1,2).EntireRow.AutoFit
67
68 Excel.ActiveSheet.Cells(2+count1+2-1,1).Formula="页脚"
69 Excel.ActiveSheet.Cells(2+count1+2-1,1).Style = "footer"
70 Excel.ActiveSheet.Cells(2+count1+2-1,1).EntireRow.AutoFit
71
72 window.close
73
74 </script>
75
2
3 window.resizeTo 0,0
4 set Excel = CreateObject("Excel.Application")
5 Excel.Visible = true
6 Excel.WorkBooks.Add("http://localhost:80/demo/App_Foundtion/Templates/default.xlt")
7
8 If Excel.Sheets.Count <1 Then
9 Excel.Sheets.Add
10 Else
11 Excel.Sheets(1).Select
12 End If
13
14 With Excel.ActiveSheet
15 .Select
16 .Name="学生成绩表"
17 End With
18
19 version = CDbl(Excel.Version)
20 Set qt1= Excel.ActiveSheet.QueryTables.Add("TEXT;http://localhost/demo/Pages/Excel.aspx?_state=gettextdata&reportid=Report1&datasessionid=28CC74827C874E2CB3438A40E989BA32",Excel.ActiveSheet.Cells(2,1))
21 With qt1
22 .FieldNames = True
23 .RowNumbers = False
24 .FillAdjacentFormulas = False
25 .PreserveFormatting = True
26 .RefreshOnFileOpen = False
27 .RefreshStyle = 1
28 .SavePassword = False
29 .SaveData = True
30 .AdjustColumnWidth = True
31 .RefreshPeriod = 0
32 .TextFilePromptOnRefresh = False
33 If Version<10 then
34 .TextFilePlatform = 2
35 else
36 .TextFilePlatform = 936
37 end if
38 .TextFileStartRow = 1
39 .TextFileParseType = 1
40 .TextFileTextQualifier = 1
41 .TextFileConsecutiveDelimiter = False
42 .TextFileTabDelimiter = True
43 .TextFileSemicolonDelimiter = False
44 .TextFileCommaDelimiter = False
45 .TextFileSpaceDelimiter = False
46 .TextFileColumnDataTypes = Array(1,1,1,1)
47 .Refresh
48 End With
49 count1=qt1.ResultRange.Rows.Count
50 qt1.ResultRange.Style="cell"
51 qt1.ResultRange.Rows(1).Style="header"
52 qt1.ResultRange.Rows(count1+1).Style="footer"
53
54 With Excel.ActiveSheet
55
56 .Range("D" & count1+2).Formula="=SUM(D3:D" & count1+1 & ")"
57 .Range("D" & count1+2).EntireColumn.AutoFit
58
59 .Range("D3:D" & count1+1).Style="cell2"
60 .Range("D3:D" & count1+1).EntireColumn.AutoFit
61
62 End With
63
64 Excel.ActiveSheet.Cells(1,2).Formula="学生成绩表"
65 Excel.ActiveSheet.Cells(1,2).Style = "title"
66 Excel.ActiveSheet.Cells(1,2).EntireRow.AutoFit
67
68 Excel.ActiveSheet.Cells(2+count1+2-1,1).Formula="页脚"
69 Excel.ActiveSheet.Cells(2+count1+2-1,1).Style = "footer"
70 Excel.ActiveSheet.Cells(2+count1+2-1,1).EntireRow.AutoFit
71
72 window.close
73
74 </script>
75
posted on 2010-04-02 13:17 guoqiang.liu 阅读(2023) 评论(0) 编辑 收藏 举报
