生成Excel格式的订单
本节的任务是在订单编辑页面，加上一个按钮，用于生成Excel格式的订单：
整个设计逻辑很简单，假定当前订单编号为“161203001”，用户单击“生成Excel格式订单”按钮后，会向服务器发送访问请求：
order.xls?oid=161203001
HttpRequest事件收到这个访问请求后，将请求转给自定义函数(CreateXLS)处理，此函数提取出编号为161203001的订单和订单明细，据此生成Excel报表，并将生成的报表发送给用户。
所有的代码加起来只有18行：
设计步骤
1、首先要设计好一个Excel报表模板：
2、增加一个自定义函数CreateXls，用于生成Excel报表：
Dim
e As
RequestEventArgs =
args(0)
Dim
oid As
String
If
e.GetValues.ContainsKey("oid")
Then
oid = e.GetValues("oid")
Else
Return
""
End
If
Dim
Book As
New XLS.Book(ProjectPath
&
"Attachments\订单.xls")
Dim
fl As
String =
ProjectPath &
"Reports\订单.xls"
book.AddDataTable("订单","orders","Select
* from {订单}
where
订单编号=
'"
& oid
& "'")
'添加父表
book.AddDataTable("订单明细","orders","Select
*,数量
* 单价
As 金额
from {订单明细}
where
订单编号=
'"
& oid
& "'")
'添加子表
book.AddRelation("订单","订单编号","订单明细","订单编号")
'建立关联
e.WriteBook(book,"订单"
& oid
& ".xls",False)
代码逻辑很简单，根据传递过来的订单编号，找出对应的订单和订单明细，生成Excel报表。
参考：
根据后台数据生成Excel报表
提升Excel报表效率
3、修改HttpRequst事件代码，粗体部分是我们新增加的代码：
Select
Case e.Path
Case "list.htm"
Functions.Execute("List",e)
'分页显示
Case "edit.htm"
If e.PostValues.Count
> 0 Then
Functions.Execute("Save",e)
'保存表单数据
End If
Functions.Execute("Edit",e)
'生成订单编辑页面
Case "filter.htm"
Functions.Execute("Filter",e)
Case "order.xls"
Functions.Execute("CreateXLS",e)
End
Select
3、最后在Edit函数增3行代码（最后面的粗体部分），用于添加生成Excel报表的按钮：
Dim
e As
RequestEventArgs =
args(0)
Dim
wb As
New
weui
'删除订单明细
If
e.GetValues.ContainsKey("deldid")
Then
DataTables("订单明细").SQLDeleteFor("[_Identify]
= " & e.GetValues("deldid"))
End
If
'订单编辑
Dim
pr As
DataRow
'订单
Dim
srs As
List(of
DataRow)
'订单明细集合
Dim
Page As
Integer
'页码变量
If
e.GetValues.ContainsKey("page")
Then
'如果地址中有page参数
Integer.TryParse(e.GetValues("page"),
page)
'提取page参数
End
If
If
e.GetValues.ContainsKey("oid")
= False Then
'如果没有传递订单编号,则新增与一个订单
pr = DataTables("订单").SQLAddNew()
'
pr("订单编号")
= Functions.Execute("GetOrderID")
'利用自定义函数GetOrderID为新增订单生成编号.
pr("日期")
= Date.Today()
Else
'如果传递了订单编号,则找出此订单进行编辑
pr =
DataTables("订单").SQLFind("订单编号='"
& e.GetValues("oid")
& "'")
If pr
Is Nothing
Then
'多用户情况下,必须考虑其他用户删除订单的可能.
wb.InSertHtml("此订单已被其他用户删除!")
e.WriteString(wb.Build)
Return ""
'必须返回
End If
srs =
DataTables("订单明细").SQLSelect("订单编号='"
& pr("订单编号")
& "'")
'获取订单明细
End
If
Dim
url As
String = "edit.htm?page="
& page
& "&oid="
& pr("订单编号")
'传递页码和订单编号
wb.AddForm("","form1",url)
With
wb.AddInputGroup("form1","ipg1",iif(e.GetValues.ContainsKey("oid"),"编辑订单","新增订单"))
With .AddInput("订单编号","编号","text")
.Value = pr("订单编号")
.Readonly =
True
End With
.AddInput("客户","客户","text").Value
= pr("客户")
.AddInput("日期","日期","date").Value
= pr("日期")
If e.GetValues.ContainsKey("oid")
Then
'如果是旧订单,则汇总显示数量和金额
Dim qty
As Integer
Dim
amt As
Integer
For Each
sr As
DataRow In
srs
qty =
qty + sr("数量")
amt = amt
+ sr("数量")
* sr("单价")
Next
.AddInput("总数量","总数量","number").value
= qty
.AddInput("总金额","总金额","number").value
= amt
End
If
End
With
'订单明细编辑
Dim
mr As
DataRow
'要编辑的订单明细
Dim
IsNew As
Boolean
'此变量用于标记是否要新增明细
If
e.GetValues.ContainsKey("oid")
= False OrElse
e.GetValues.ContainsKey("addnext")
'如果是新增订单,或这包括addnext参数
IsNew = True
'将IsNew参数设置为True,表明需要新增订单明细
ElseIf
e.GetValues.ContainsKey("did")
Then
'如果传递了订单明细主键
mr = DataTables("订单明细").SQLFind("[_Identify]="
& e.GetValues("did"))
'找出此订单明细进行编辑
End
If
If
IsNew OrElse
mr IsNot
Nothing Then
With wb.AddInputGroup("form1","ipg2",iif(IsNew,"新增明细","编辑明细"))
.Attribute =
"onchange='calc()'" '调用js函数,自动计算金额
If IsNew
Then
'如果是新增订单明细
.AddInput("产品","产品","text")
.AddInput("数量","数量","number")
.AddInput("单价","单价","number").Step=
"0.01"
.AddInput("金额","金额","number")
Else
.AddHiddenValue("DetailID",mr("_Identify"))
'插入一个隐藏的订单明细主键,此值将随表单数据一并提交到服务器.
.AddInput("产品","产品","text").Value
= mr("产品")
.AddInput("数量","数量","number").value
= mr("数量")
With .AddInput("单价","单价","number")
.Step=
"0.01"
.value =
mr("单价")
End With
.AddInput("金额","金额","number").value
= mr("数量")
* mr("单价")
'后台没有金额列,要通过数量和单价计算得出
End If
End
With
End
If
'生成订单明细表格
If
e.GetValues.ContainsKey("oid")
AndAlso srs.count
> 0 Then
'如果不是新增订单,且订单明细行数大于0,则生成订单明细表格.
With wb.AddTable("form1","detailTable")
'为了区分,明细表的名字设为detailTable
.head.AddRow("产品","数量","单价","金额")
.ActiveSheet =
"menu"
'指定菜单
For Each
sr As
DataRow In
srs
With .Body.AddRow(sr("产品"),sr("数量"),sr("单价"))
.AddCell(sr("数量")
* sr("单价"))
.Primarykey =
sr("_Identify")
'为此行指定主键值
End
With
Next
End With
'设计菜单
With wb.AddActionSheet("","menu")
.Add("mnudAdd",
"增加明细").Attribute="onclick='addDetail()'"
'调用js函数
.Add("mnuEdit",
"编辑明细").Attribute
="onclick='editDetail()'"
.Add("mnuDelete",
"删除明细").Attribute
="onclick=""show('dlg1')"""
.Add("mnuCancel","取消","",True)
End
With
End
If
With
wb.AddDialog("","dlg1",
"删除确认","您确定要删除当前明细吗?")
.AddButton("btnCancel","取消").Kind
= 1
.AddButton("btnOK","确定").Attribute
= "onclick='delDetail()'"
End
With
With
wb.AddButtonGroup("form1","btg1",False)
.Add("btn1",
"增加明细",
"submit").FormAction
= url &
"&addnext=true"
'加上addnext参数,表示保存后进入增加明细状态
.Add("btn2",
"保存",
"submit")
'正常提交,保存后进入编辑状态
If e.GetValues.ContainsKey("oid")
= False Then
'如过是新增订单
.Add("btn3",
"取消",
"button","list.htm?page="
& page
& "&deloid="
& pr("订单编号"))
'删除新增订单后返回列表
ElseIf IsNew
OrElse mr
IsNot Nothing
Then
'如果在给旧订单新增或修改明细,则直接返回编辑状态
.Add("btn3",
"取消",
"button",url)
'返回编辑状态
Else
.Add("btn3",
"返回",
"button","list.htm?page="
& page)
'返回列表
End
If
End
With
With
wb.AddButtonGroup("form1","btg2",False)
.Add("btn4",
"生成Excel格式订单",
"button","order.xls?oid="
& pr("订单编号"))
End
With
pr.Save()
'必须保存,而且必须在最后保存,因为SQLAddNew增加的行,保存之后就会销毁,无法再调用
If
e.PostValues.Count
> 0 Then
'如果是通过提交按钮访问,则给一个已经保存的提示给用户,时长500毫秒
wb.AddToast("","t1",
"已经保存",0).Msec=
500
End
If
wb.AppendHTML("<script
src='./lib/order.js'></script>")
'引入脚本文件
e.WriteString(wb.Build)
'生成网页