- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- excel报表列宽 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=161361)
-- 发布时间：2021/3/15 10:16:00
-- excel报表列宽
需求：按照内容显示列宽，下面打印报表怎么改？特别是尺码列数量不固定，要怎么改？
此主题相关图片如下：1212.png
Dim dtb As New DataTableBuilder("横向销售明细")
If DataTables.Contains("横向销售明细") = False Then \'如果表C没有加载
DataTables.Load("横向销售明细") \'加载表C
End If
dtb.AddDef("订单号", Gettype(String),65)
dtb.AddDef("制单号", Gettype(String),65)
dtb.AddDef("发货日期", Gettype(Date))
dtb.AddDef("客户", Gettype(String), 60)
dtb.AddDef("款号", Gettype(String), 60)
dtb.AddDef("颜色", Gettype(String),60)
dtb.AddDef("销售单价", Gettype(Decimal),25)
dtb.AddDef("销售金额", Gettype(Decimal),25)
\'dtb.AddDef("通码", Gettype(Decimal),25)
dtb.AddDef("发货数量", Gettype(Decimal),25)
dtb.AddDef("折扣", Gettype(Decimal),25)
dtb.AddDef("销售发货单号", Gettype(String), 60)
For Each v As String In DataTables("销售发货明细表").GetValues("尺码","[销售发货单号] = \'" & Tables("销售发货_table1").current("销售发货单号") & "\'")
dtb.AddDef(v, Gettype(String))
\'"尺码_" &
Next
Dim br As DataRow
br = DataTables("销售发货明细表").Find("[销售发货单号] = \'" & Tables("销售发货_Table1").Current("销售发货单号") & "\'")
Dim ta As Table = Forms("销售发货").Controls("Table3").Table
ta.datasource = dtb.BuildDataSource
Tables("销售发货_table3").DataTable.StopRedraw()
For Each v As String() In DataTables("销售发货明细表").GetValues("订单号|制单号|发货日期|客户|款号|颜色|销售单价|折扣|销售发货单号","[销售发货单号] = \'" & Tables("销售发货_table1").current("销售发货单号") & "\'")
Dim dr1 As Row = ta.AddNew()
dr1("订单号") = v(0)
dr1("制单号") = v(1)
dr1("发货日期") = v(2)
dr1("客户") = v(3)
dr1("款号") = v(4)
dr1("颜色") = v(5)
dr1("销售单价") = v(6)
dr1("折扣") = v(7)
dr1("销售发货单号") = v(8)
dr1("销售金额") = DataTables("销售发货明细表").Compute("Sum(销售金额)", "客户 = \'" & dr1("客户") & "\' and 订单号 = \'" & dr1("订单号") & "\' and 颜色 = \'" & dr1("颜色") & "\' and 款号 = \'" & dr1("款号") & "\' and 销售发货单号 = \'" & dr1("销售发货单号") & "\'")
dr1("发货数量") = DataTables("销售发货明细表").Compute("Sum(发货数量)", "客户 = \'" & dr1("客户") & "\' and 订单号 = \'" & dr1("订单号") & "\' and 颜色 = \'" & dr1("颜色") & "\' and 款号 = \'" & dr1("款号") & "\' and 销售发货单号 = \'" & dr1("销售发货单号") & "\'")
For Each dr2 As DataRow In DataTables("销售发货明细表").Select("客户 = \'" & v(3) & "\' And 订单号 = \'" & v(0) & "\'and 制单号 = \'" & v(1) & "\'and 款号 = \'" & v(4) & "\' and 颜色 = \'" & v(5) & "\' and 销售发货单号 = \'" & v(8) & "\' ")
dr1( dr2("通码")) = dr2("发货数量")
Next
Tables("销售发货_table3").DataTable.ResumeRedraw()
Next
Dim dt As Table = Tables("销售发货_table3")
Dim Book As New XLS.Book \'定义一个Excel工作簿
Dim Sheet As XLS.Sheet = Book.Sheets(0)
For c As Integer = 3 To dt.Cols.Count -1 \'添加列标题
Sheet(2, c).Value = dt.Cols(c).Name
Next
For r As Integer = 0 To dt.Rows.Count - 1 \'填入数据
For c As Integer = 3 To dt.Cols.Count -1
Sheet(r +3, c).Value = dt.rows(r)(c)
Next
Next
Sheet(1,9).Value = "销售单"
Sheet(1,11).Value = "销售金额"
Sheet(1,12).Value = DataTables("销售发货明细表").Compute("Sum(销售金额)","销售发货单号=\'" & Tables("销售发货_table1").current("销售发货单号") & "\'")
Book.Save("c:\\reports\\test.xls")
Dim Proc As New Process
Proc.File = "c:\\reports\\test.xls"
Proc.Start()
-- 发布时间：2021/3/15 10:20:00
--
这个要试用vba设置：http://www.foxtable.com/webhelp/topics/2121.htm
如果反过来，希望自动调整单元格大小，使单元格能够显示全部数据。这需要用到AutoFit方法。如：
Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open("D:\\Report.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range = Ws.Range("A1")
Rg.Value = "Foxtable操控Excel全攻略"
Rg.Font.Size = 50 \'把字体放大
Rg.EntireColumn.AutoFit \'自动调整列宽
Rg.EntireRow.AutoFit \'自动调整行高
App.Visible = True
Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open("D:\\Report.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range = Ws.Range("A1")
Rg.Value = "Foxtable操控Excel全攻略"
Rg.Font.Size = 50 \'把字体放大
Rg.EntireColumn.AutoFit \'自动调整列宽
Rg.EntireRow.AutoFit \'自动调整行高
App.Visible = True
-- 发布时间：2021/3/15 10:37:00
--
不想搞这么复杂，只要能满足调整不固定列数的列宽就行了，有没有变通的方式，
-- 发布时间：2021/3/15 10:43:00
--
没有的。