以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  从后台直接导出excl的优化  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=163334)
--  作者：weibu
--  发布时间：2021/4/23 16:06:00
--  从后台直接导出excl的优化
Dim cmd As New SQLCommand
Dim txt As String = e.Form.Controls("TextBox1").Value
Dim pc As Row = Tables("conip").Rows(0)
cmd.C
Dim nms() As String = {"inno","proname","prodate","logcode","indate","supplier"} 
cmd.CommandText = "Select inno,proname,prodate,logcode,indate,supplier Fr o m {rs_sourcecode} Where inno = \'鸿运20210331\' "      老师我这是变量txt，怎修改代码？
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)) ’老师我这里面有日期格式的，导出excl显示不是日期怎么办？
    Next 
Next
\'打开工作簿
Book.Save("c:\\reports\\test.xls")
Dim Proc As New Process
Proc.File = "c:\\reports\\test.xls"
Proc.Start()                     \'老师我希望导入的excl名字加日期和精确到秒，规避覆盖怎么做？
--  作者：有点蓝
--  发布时间：2021/4/23 16:30:00
--  
1、参考：http://www.foxtable.com/webhelp/topics/1284.htm

cmd.CommandText = "Select inno,proname,prodate,logcode,indate,supplier Fr o m {rs_sourcecode} Where inno = \'" & txt & "\' " 

2、
Dim St2 As XLS.Style = Book.NewStyle
St2.Format = "yyyy-MM-dd"
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)) ’老师我这里面有日期格式的，导出excl显示不是日期怎么办？
if dt.datacols(nms(c)).IsDate then
Sheet(r +1, c).style = St2
end if
    Next 
Next

3、
dim name as string = "c:\\reports\\test" & format(date.now,"yyyyMMddHHmmss") & ".xls"
Book.Save(name )
--  作者：weibu
--  发布时间：2021/4/26 14:58:00
--  
Dim cmd As New SQLCommand
Dim name As String = "c:\\reports\\wlm" & format(Date.now,"yyyyMMddHHmmss") & ".xls"
Dim ks As Date = e.Form.Controls("DateTimePicker1").Value
Dim pc As Row = Tables("conip").Rows(0)
cmd.C
Dim nms() As String = {"inno","proname","prodate","logcode","indate","supplier"} 
Dim caps() As String = {"入库单号","商品名称","生产日期","物流码","入库日期","供应商"} \'对应的列标题 
Dim szs() As Integer = {120,130,100,130,100,180} \'对应的列宽
cmd.CommandText = "Select inno,proname,prodate,logcode,indate,supplier Fr o m {rs_sourcecode} Where indate = \'"& ks & "\'"   老师SQL数据库中的日期格式精确到秒2020-07-10 11:02:19。我想按2020-07-10判断是这一天的数据即可；如何修改代码？
--  作者：有点蓝
--  发布时间：2021/4/26 15:07:00
--  
cmd.CommandText = "Select inno,proname,prodate,logcode,indate,supplier Fr o m {rs_sourcecode} Where indate >= \'"& ks.date & "\' and indate < \'"& ks.date.adddays(1) & "\'"