专家坐堂-交叉统计表如何定义字体颜色Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

If e.Form.Controls("DateTimePicker1").value = Nothing Then

MessageBox.Show("请先选择查询时间 !")

e.Cancel = True

Else If e.Form.Controls("TextBox1").value = Nothing Then

MessageBox.Show("请先写入超期库存定义天数 !")

e.Cancel = True

Else

StatusBar.Message1= "正在进行数据统计,请稍等!"

StatusBar.Message3 = Date.Today & " " & User.Name

StatusBar.Refresh() \'刷新状态栏

Dim d As Date = e.Form.Controls("DateTimePicker1").value

Dim KCTS As Integer = e.Form.Controls("TextBox1").value \'取数超期库存定义的天数

Dim y As Integer = d.Year

Dim m As Integer = d.Month

Dim Days As Integer = Date.DaysInMonth(y,m)

Dim FD As Date = New Date(y,m,1) \'获得该月的第一天

Dim LD As Date = New Date(y,m,Days) \'获得该月的最后一天

Dim RKRQ As Date = FD.AddDays(-KCTS) \'定义超期库存的库存时间

Dim bd1 As New GroupTableBuilder("统计表1",DataTables("商品车库存基础表"))

Dim dt1 As fxDataSource

Bd1.Groups.AddDef("关联索引列",DateGroupEnum.Day.None) \'根据所属公司分组

Bd1.Groups.AddDef("车型")

Bd1.Groups.AddDef("配置")

Bd1.Groups.AddDef("市场指导价")

Bd1.Groups.AddDef("外观颜色")

Bd1.Groups.AddDef("内饰颜色")

Bd1.Groups.AddDef("入库日期",DateGroupEnum.Day.None,"入库日期")

Bd1.Groups.AddDef("车款支付方式","融资方式")

Bd1.Groups.AddDef("进货类型")

Bd1.Filter ="[已定] = False and [进货类型] <> \'大客户订单\'"

dt1 = bd1.BuildDataSource()

Tables("查询日报表_Table8").DataSource = dt1

With DataTables("查询日报表_Table8").DataCols \'用表达式列计算库存数据

.Add("颜色",Gettype(String), "Substring([外观颜色],len([外观颜色])-0,1)")

.Add("判断",Gettype(String), "IIF([入库日期] Is null,\'在途\',IIF([入库日期] > #" & RKRQ & "# ,\'在库\',\'超期\'))")

.Add("指导价",Gettype(String), "IsNull([市场指导价],0)/100")

End With

Dim g As New CrossTableBuilder("统计表2", DataTables("查询日报表_Table8"))

Dim gt As fxDataSource

g.HGroups.AddDef("车型")

g.HGroups.AddDef("配置")

g.HGroups.AddDef("指导价")

g.VGroups.AddDef("颜色")

g.VGroups.AddDef("判断")

g.Totals.AddDef("判断", AggregateEnum.Count, "判断")

g.OrderByTotal = True

g.HorizontalTotal = True

g.VerticalTotal = True

g.Decimals = 0

gt = g.BuildDataSource()

Tables("查询日报表_Table7").DataSource = gt

Tables("查询日报表_Table7").AutoSizeCols() \'自动设置所有列的宽度

Tables("查询日报表_Table7").AutoSizeHeaderRow()

Tables("查询日报表_Table7").SetHeaderRowHeight(25)

Tables("查询日报表_Table7").DefaultRowHeight = 25

e.Form.Controls("Label1").text = "店端库存看板超期库存定义\'" & KCTS & "\'天"

Tables("查询日报表_Table7").SetHeaderCellForeColor("超期",Color.Red,2)

End If

StatusBar.Message1= "就绪"

StatusBar.Message3 = Date.Today & " " & User.Name

StatusBar.Refresh() \'刷新状态栏