DataGrid导入EXCEL中关于时间类型转换的问题

[lp370480]

当我把从DataGrid中显示出来的数据导入到EXCEL中，但是导到EXCEL时的时间字段值全都是一些乱七八糟的数字，我数据库的时间字段都是smalldatetime类型，请问我怎么做才能使导入到EXCEL中的时间字段值能正确显示。以下是我从DataGrid中显示出来的数据导入到EXCEL的代码。
Set xlapp = CreateObject("excel.application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.Worksheets(1)
xlapp.Visible = True '设置EXCEL可见
On Error Resume Next
If Err.Number <> 0 Then Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.ActiveSheet
For k = 1 To DataGrid1.Columns.Count
xlSHEET.Cells(1, k) = DataGrid1.Columns(k - 1).Caption
Next k
For i = 1 To Adodc1.Recordset.RecordCount + 1
For j = 0 To DataGrid1.Columns.Count
xlSHEET.Cells(i + 1, j + 1) = Adodc1.Recordset(j) '
Next j
Adodc1.Recordset.MoveNext

Next i

[jieweibin]

'加个字符处理试试
Public Function IntToChr(iRow1 As Integer, iCol1 As Integer, iRow2 As Integer, iCol2 As Integer) As String
Dim i As Integer, j As Integer, tmpi As Integer
Dim Tmpstr(1 To 2) As String
If iCol1 < 1 Or iCol1 > 256 Or iCol2 < 1 Or iCol2 > 256 Then
IntToChr = ""
Exit Function
End If

j = iCol1 Mod 26
If j = 0 Then
i = (iCol1 \ 26) - 1
j = 26
Else
i = (iCol1 \ 26)
End If

If i > 0 Then
Tmpstr(1) = Chr(64 + i) & Chr(64 + j)
Else
Tmpstr(1) = Chr(64 + j)
End If

j = iCol2 Mod 26
If j = 0 Then
i = (iCol2 \ 26) - 1
j = 26
Else
i = (iCol2 \ 26)
End If

If i > 0 Then
Tmpstr(2) = Chr(64 + i) & Chr(64 + j)
Else
Tmpstr(2) = Chr(64 + j)
End If

IntToChr = Tmpstr(1) & iRow1 & ":" & Tmpstr(2) & iRow2
End Function

tmpChr = IntToChr(1, 1, Adodc1.Recordset.RecordCount + 1, DataGrid1.Columns.Count-1 )
xlSHEET.Range(tmpChr).Select
xlSHEET.Selection.Columns.AutoFit '自动调整列宽
xlSHEET.Selection.Font.Size = 10 '字体大小

[jhone99]

Private Sub Command1_Click()
Set xlapp = CreateObject("excel.application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.Worksheets(1)
xlapp.Visible = True '设置EXCEL可见
On Error Resume Next
If Err.Number <> 0 Then Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.ActiveSheet
For k = 1 To DataGrid1.Columns.Count
xlSHEET.Cells(1, k) = DataGrid1.Columns(k - 1).Caption
Next k
xlSHEET.Range("c2", "c20").NumberFormatLocal = "yyyy-m-d" For i = 1 To Adodc1.Recordset.RecordCount + 1
For J = 0 To DataGrid1.Columns.Count
' If Adodc1.Recordset(J).Type = adDate Then
' End If

xlSHEET.Cells(i + 1, J + 1) = Adodc1.Recordset(J) '
Next J
Adodc1.Recordset.MoveNext

Next i
End Sub

[jhone99]

Private Sub Command1_Click()
Set xlapp = CreateObject("excel.application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.Worksheets(1)
xlapp.Visible = True '设置EXCEL可见
On Error Resume Next
If Err.Number <> 0 Then Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Add
Set xlSHEET = xlBook.ActiveSheet
For k = 1 To DataGrid1.Columns.Count
xlSHEET.Cells(1, k) = DataGrid1.Columns(k - 1).Caption
Next k
xlSHEET.Range("c2", "c20").NumberFormatLocal = "yyyy-m-d"
For i = 1 To Adodc1.Recordset.RecordCount + 1
For J = 0 To DataGrid1.Columns.Count
' If Adodc1.Recordset(J).Type = adDate Then
' End If

xlSHEET.Cells(i + 1, J + 1) = Adodc1.Recordset(J) '
Next J
Adodc1.Recordset.MoveNext

Next i
End Sub

[lp370480]

你这好像不是VB语言吧？我有人能把我的代码修改一下

[jhone99]

加
xlSHEET.Range("c2", "c20").NumberFormatLocal = "yyyy-m-d"
就可以了

[lp370480]

我加上了这句还是没有效果 请问那加上的那句是什么意思？请详细的解释一下，谢谢！！！再谢！！！

[lp370480]

苦等！！！

[mokton]

转换成字符串

[jhone99]

我试验时c列是日期，你的是哪一列改一下

[lp370480]

我想问你，这个("c2", "c20")是什么意思啊 ？我的导入到EXCEL的时间列是F列，我把他改为xlSHEET.Range("f2", "f20").NumberFormatLocal = "yyyy-m-d" 后只有想2008-9-4像这样的月，天是一位数的才能显示，像2008-8-24这样的就是显示不出来了，后来我改为"yyyy-m-dd"全都显示不出来了。请你帮我修改一下既能显示2008-11-24，也能显示2008-8-12，还能显示2008-9-4这三种不同格式

[jhone99]

("c2", "c20")
是c列2到20行

"yyyy-mm-dd"

[lp370480]

我现在弄懂了你的（"c2", "c20")代表什么意思，"c2"代表时间转换列的起始行，"c20"代表时间转换列的终止行 但NumberFormatLocal = "yyyy-m-d 却只能转换日期像：2008-9-4这样的格式，像2008-11-4，2008-8-23 2008-11-14都不能转换