Dim tb1 As Table = Tables(e.Form.Name & "_Table01")
Dim tb6 As Table = Tables(e.Form.Name & "_Table06") '*****
'Dim zj As String = e.Form.Controls("cmb_zj").Value
'If zj IsNot Nothing Then
Dim jb As New SQLJoinTableBuilder("标录明细汇总表","标录明细")
jb.C
jb.AddTable("标录明细","系统编号","招标信息","系统编号")
jb.AddTable("标录明细","系统编号","开标记录","系统编号")
jb.AddCols("{开标记录}.开标日期","{开标记录}.开标时间","{标录明细}.系统编号","{标录明细}.项目名称","本期概算","投标家数","中标单位","中标报价","中标球号","{开标记录}.评标基准价", _
"收费计费额","收费基价","预算审核价","招标控制价","风险控制价","最高限价","最低限价","投标单位","中标候选人","投标球号","投标报价")
jb.AddExp("下浮率_球号比例","Case When 投标球号 > 0 And 投标家数 > 0 Then 投标球号 / 投标家数 Else 0 End")
jb.AddExp("下浮率_收费基价","Case When 收费基价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 收费基价 Else 0 End")
jb.AddExp("下浮率_预算审核价","Case When 预算审核价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 预算审核价 Else 0 End")
jb.AddExp("下浮率_招标控制价","Case When 招标控制价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 招标控制价 Else 0 End")
jb.AddExp("下浮率_风险控制价","Case When 风险控制价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 风险控制价 Else 0 End")
jb.AddExp("下浮率_最高限价","Case When 最高限价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 最高限价 Else 0 End")
jb.AddExp("下浮率_最低限价","Case When 最低限价 > 0 And 投标报价 > 0 Then 1 - 投标报价 / 最低限价 Else 0 End")
''-----根据tb1数据生成系统编号集合-------
Dim lst As New List(Of String)
For Each cr As Row In tb1.Rows
If lst.Contains(cr("系统编号")) = False Then
lst.Add(cr("系统编号"))
End If
Next
Dim nids As String = String.Join("|",lst.ToArray)
jb.Filter = "{标录明细}.系统编号 In ('" & nids.Replace("|","','") & "') And (投标球号 Is Not Null Or 投标报价 Is Not Null)" '隐藏行
''jb.Build()
tb6.DataSource = jb.BuildDataSource
''======窗口设置========
tb6.DataTable.GlobalHandler.DrawCell = True '开启DrawCell事件
For Each r As Row In tb6.Rows
For Each cl As Col In tb6.Cols
If cl.DataCol.IsNumeric Then
If r(cl.Name) = 0 Then
r(cl.Name) = Nothing
End If
End If
Next
Next
With tb6
.Cols("开标时间").SetDateTimeFormat(DateTimeFormatEnum.Time)
' .Filter = "投标球号 Is Not Null or 投标报价 Is Not Null" '隐藏行
'.Sort = "开标日期,开标时间"
.SetHeaderRowHeight(25,25) '设置标题行高度
.DefaultRowHeight = 25 '设置行高度
If .Rows.Count > 0 Then
.Select(0,0) '光标在第一行第一列位置
End If
End With
'自动取消窗口表中空值列的隐藏
With tb6
For Each cl As Col In .Cols
cl.TextAlign = TextAlignEnum.Center '单元格内容居中
Dim p As Integer = .FindRow(cl.Name & " Is Not Null or " & cl.Name & " <> 0")
If p = -1 Then
cl.Visible = False
End If
If cl.Name.Contains("下浮率") Then
cl.DataCol.SetFormat("0.00%")
End If
Next
End With
结果代码：
Dim tb5 As Table = Tables(e.Form.Name & "_Table05")
Dim tb6 As Table = Tables(e.Form.Name & "_Table06")
'=======增加临时代理开标记录数据表=========
Dim dtb As New DataTableBuilder("标录明细水平表")
dtb.AddDef("系统编号", Gettype(String), 30)
dtb.AddDef("项目名称", Gettype(String), 200)
dtb.AddDef("开标日期", Gettype(Date))
dtb.AddDef("开标时间", Gettype(Date))
dtb.AddDef("中标基准价", Gettype(Double))
For Each tbdw As String In tb6.DataTable.GetValues("投标单位")
dtb.AddDef(tbdw, Gettype(Double))
Next
tb5.DataSource = dtb.BuildDataSource()
''==============加载数据到临时表===============
Dim str As String = e.Form.Controls("cmb_zj").Text
For Each lm As String() In tb6.DataTable.GetValues("系统编号|项目名称|开标日期|开标时间")
'For Each lm As String() In tb6.DataTable.GetValues("系统编号|项目名称|开标日期")
Dim ndr As DataRow = tb5.DataTable.AddNew()
ndr("系统编号") = lm(0)
ndr("项目名称") = lm(1)
ndr("开标日期") = lm(2)
ndr("开标时间") = lm(3)
For Each dc As DataCol In tb6.DataTable.DataCols
If dc.Name = str Then
For Each dr1 As DataRow In tb6.DataTable.Select("系统编号 = '" & lm(0) & "'")
ndr(dr1("投标单位")) = dr1(dc.Name)
Dim fdr As DataRow = DataTables("开标记录").Find("系统编号 = '" & lm(0) & "' And 中标单位 = '" & dr1("投标单位") & "'")
If fdr IsNot Nothing Then
ndr("中标基准价") = dr1(dc.Name)
ndr("开标时间") = dr1("开标时间")
End If
'msgbox(dr1("开标时间"))
Next
End If
Next
Next
''======临时表清零代码========
tb5.DataTable.GlobalHandler.DrawCell = True '开启DrawCell事件
For Each r As Row In tb5.Rows
For Each cl As Col In tb5.Cols
If cl.DataCol.IsNumeric Then
If r(cl.Name) = 0 Then
r(cl.Name) = Nothing
End If
End If
Next
Next
tb5.DataTable.DataCols("开标时间").SetDateTimeFormat(DateTimeFormatEnum.Time)
tb5.DefaultRowHeight = 28
tb5.Sort = "开标日期 " ' ,开标时间 DESC"
tb5.Select(0,0) '光标在第一行第一列位置
For Each c As Col In tb5.Cols '单元格内容居中
c.TextAlign = TextAlignEnum.Center
If c.IsNumeric Then '如果是数值型的列
c.DataCol.SetFormat("0.00%")
End If
Next
''自动取消窗口表中空值列的隐藏
With tb5
For Each cl As Col In .Cols
cl.Visible = True
Next
For Each c2 As Col In .Cols
Dim p As Integer = .FindRow(c2.Name & " Is Not Null")
If P = -1 Then
c2.Visible = False
End If
Next
End With
加好友 
发短信
Post By：2019/3/21 13:04:00 [只看该作者]
此主题相关图片如下：qq截图20190321152103.png