导出数据

虽然Foxtable已经有导出Excel的功能,但是不够灵活。
如果你有更多的要求,可以用Excel报表来实现导出功能。

示例一

例如下面的代码,导出订单表,用红色背景标出折扣超过0.15的单元格:

Dim dt As Table = Tables("订单")
Dim
Book As New XLS.Book '定义一个Excel工作簿
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 dt.Cols.Count -1 '添加列标题
    Sheet(0, c).Value = dt.Cols(c).Name
Next
For
r As Integer = 0 To dt.Rows.Count - 1 '填入数据
   
For c As Integer = 0 To dt.Cols.Count -1
        Sheet(r +1, c).Value = dt.rows(r)(c)
    Next
    If
dt.rows(r)("折扣") >= 0.15 Then '如果折扣大于等于0.15
        Sheet(r + 1,dt.Cols("折扣").Index).Style = Style '设置折扣单元格的样式
   
End If
Next

'打开工作簿

Book.Save("c:\reports\test.xls")
Dim
Proc As New Process
Proc.File = "c:\reports\test.xls"
Proc.Start()

在命令窗口执行上面的代码,得到:

你如果细心的话,你会发现生成的Excel文件中,日期列内容全变成了数字。
你可以在Excel中手工将日期列的格式设为日期型,但这不是一个好方法。
最好是修改上面的导出代码,在打开工作簿的代码之前插入下面几行:

Dim St2 As XLS.Style = Book.NewStyle
St2.Format = "yyyy-MM-dd"
Sheet.Cols(dt.Cols("日期").Index).Style = st2

工作表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()

 

 

 

 


本页地址:http://www.foxtable.com/webhelp/topics/1148.htm