当我把从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.MoveNextNext i
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.MoveNextNext i
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 '字体大小
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.MoveNextNext i
End Sub
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.MoveNextNext i
End Sub
xlSHEET.Range("c2", "c20").NumberFormatLocal = "yyyy-m-d"
就可以了
是c列2到20行"yyyy-mm-dd"