导出数据
虽然Foxtable已经有导出Excel的功能,但是不够灵活。
如果你有更多的要求,可以用Excel报表来实现导出功能。
示例一
例如下面的代码,导出订单表,用红色背景标出折扣超过0.15的单元格:
Dim
dt As Table = Tables("订单")在命令窗口执行上面的代码,得到:
你如果细心的话,你会发现生成的Excel文件中,日期列内容全变成了数字。
你可以在Excel中手工将日期列的格式设为日期型,但这不是一个好方法。
最好是修改上面的导出代码,在打开工作簿的代码之前插入下面几行:
Dim
St2 As XLS.Style = Book.NewStyle工作表Sheet由行和列组成,Rows返回其行的集合,Cols返回其列的集合,行和列都可以设置样式(Style),行和列将在后面详细介绍。
示例二
如果要导出部分列,或者要自定义列宽,或者要自定义列标题,可以参考下面的代码:
Dim
dt As
Table = Tables("订单")
Dim
nms() As
String = {"产品","客户","数量","单价","金额","日期"}
'要导出的列名
Dim
caps() As
String = {"产品名称","用户名称","数量","单价","金额","日期"}
'对应的列标题
Dim
szs() As
Integer = {100,100,80,80,80,120}
'对应的列宽
Dim
Book As
New XLS.Book
'定义一个Excel工作簿
Dim
Sheet As
XLS.Sheet =
Book.Sheets(0)
'引用工作簿的第一个工作表
Dim
st As
XLS.Style =
Book.NewStyle
'日期列的显示格式
st.Format
= "yyyy-MM-dd"
For
c As
Integer = 0
To nms.length
-1
Sheet(0,
c).Value =
caps(c)
'指定列标题
Sheet.Cols(c).Width
= szs(c)
'指定列宽
If dt.Cols(nms(c)).IsDate
Then
'如果是日期列
Sheet.Cols(c).Style
= st
'设置显示格式
End
If
Next
For
r As
Integer = 0
To dt.Rows.Count
- 1
'填入数据
For c
As Integer =
0 To
nms.length -1
Sheet(r
+1, c).Value
= dt.rows(r)(nms(c))
Next
Next
Dim
dlg As
New SaveFileDialog
'定义一个新的SaveFileDialog
dlg.Filter=
"Excel文件|*.xls"
'设置筛选器
If
dlg.ShowDialog
= DialogResult.Ok
Then
Book.Save(dlg.FileName)
Dim Proc
As New
Process
Proc.File
= dlg.FileName
Proc.Start()
End
If
示例三
我们也可以直接从后台提取数据生成一个Excel文件,例如:
Dim
cmd As
New
SQLCommand
'cmd.ConnectionName = "数据源名称"
Dim
nms() As
String = {"产品","数量","单价","金额","日期"}
cmd.CommandText
= "Select 产品,数量,单价, 数量 * 单价 As
金额,日期 From {订单} Where 客户 = 'CS01' "
Dim
dt As
DataTable = cmd.ExecuteReader()
Dim
Book As
New XLS.Book
Dim
Sheet As
XLS.Sheet =
Book.Sheets(0)
Dim
Style As
Xls.Style =
Book.NewStyle
Style.BackColor
= Color.Red
For
c As
Integer = 0
To nms.Count
-1
Sheet(0,
c).Value =
nms(c)
Next
For
r As
Integer = 0
To dt.DataRows.Count
- 1
For c
As Integer =
0 To
nms.Count -1
Sheet(r
+1, c).Value
= dt.DataRows(r)(nms(c))
Next
Next
'打开工作簿
Book.Save("c:\reports\test.xls")
Dim
Proc As
New
Process
Proc.File
= "c:\reports\test.xls"
Proc.Start()