我用TDBGRID控件显示查询结果,但将TDBGRID中的内容导出到EXCEL后汉字出现乱码,表格中的数字、字母都可以正常显示。(VB6_SP5 TDBGRID7.0 WIN-XP)我用的是artoksxb(风桦林) 朋友建议使用的方法:
TDBGrid1.ExportToFile App.Path & "\kk.xls", False
不知道哪位高手知道问题出在哪儿了?不会是TDBGRID7.0的版本问题吧,这个是英文版的,有没有中文版的?
TDBGrid1.ExportToFile App.Path & "\kk.xls", False
不知道哪位高手知道问题出在哪儿了?不会是TDBGRID7.0的版本问题吧,这个是英文版的,有没有中文版的?
首先引用microsft execl 9.0 object libary
导出按钮的代码:
Private Sub outexecl_Click()
Dim excelApp As Excel.Application
Set excelApp = New Excel.Application
On Error Resume Next
If excelApp Is Nothing Then
Set excelApp = CreateObject("Excel.application")
If excelApp Is Nothing Then
Exit Sub
End If
End If
excelApp.Visible = True
Me.MousePointer = vbHourglass
excelApp.Workbooks.Add
With excelApp.ActiveSheet
Dim i As Integer, j As Integer
For i = 1 To MSGrid.rows
For j = 1 To MSGrid.Cols
.Cells(i, j).Value = MSGrid.TextMatrix((i - 1), (j - 1))
Next j
DoEvents
Next i
End With
Me.MousePointer = vbDefault
Set excelApp = NothingEnd Sub
TDBGrid1.ExportToFile App.Path & "\kk.xls", False
private Sub GridTOexcel(mGrid As TDBGrid)
Dim ColCount, i, k As Integer
Dim xlApp As New Excel.Application, xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet, sRange As String
ColCount = mGrid.Columns.Count
xlApp.Visible = False Set xlBook = xlApp.Workbooks.Add
Set xlsheet = xlBook.Worksheets(3)
xlsheet.Visible = xlSheetHidden
Set xlsheet = xlBook.Worksheets(2)
xlsheet.Visible = xlSheetHidden '//隐藏sheet2
Set xlsheet = xlBook.Worksheets(1)
xlsheet.Name = "导出数据"
VB.Screen.MousePointer = vbHourglass
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, ColCount)).Merge
xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(2, ColCount)).Font.Size = 10
'//设置标题 For i = 0 To ColCount - 1
xlsheet.Columns(i + 1).ColumnWidth = mGrid.Columns(i).Width / 120
If mGrid.Columns(i).Visible = True Then
xlsheet.Cells(2, i + 1) = mGrid.Columns(i).Caption
End If
Next
mGrid.MoveFirst
i = 0
'//从网格到excel
While Not mGrid.EOF
xlsheet.Range(xlsheet.Cells(i + 3, 1), xlsheet.Cells(i + 3, ColCount)).Font.Size = 10
For k = 0 To ColCount - 1
If Not IsNull(mGrid.Columns(k).Value) Then
If mGrid.Columns(k).Visible = True Then
xlsheet.Cells(i + 3, k + 1) = CStr(mGrid.Columns(k).Value)
End If
End If
Next
mGrid.MoveNext
i = i + 1
Wend
'//关闭操作台
xlBook.SaveAs "D:\kk.xls"
xlBook.Close False
xlApp.Quit
Set xlApp = Nothing
VB.Screen.MousePointer = vbDefault
Msgbox "数据导出完毕!"
End Sub
使用ExportToDelimitedFile方法:使用TDBGrid1.ExportToDelimitedFile App.Path & "\kk.xls" 即可解决乱码问题!!!!
例如(也要随意改动):1、建立一个excel模块(*.xlt)
2、在此模块第一行设置标题
3、在此模块第二行设置其它数据
4、在此模块第三,四行(上下合并)设置导入字段的标题
5、在此模块第五,六行分别设置每行空行
-------------------
Private Sub TableToExcel(mouldName As String) ''如mouldName="kk.xlt"
Dim ColCount, i, k As Integer
Dim rs As New ADODB.Recordset
Dim select1 As String
Dim xlApp As New Excel.Application, xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlApp = CreateObject("excel.application")
rs.CursorLocation = adUseClient
cn.CursorLocation = adUseClient
If Dir(App.Path + "\Execl\" + mouldName) = "" Then
MsgBox "不存在对应模板,无法建立!"
Exit Sub
End If
Set xlBook = xlApp.Workbooks.Open(App.Path & "\execl\" & mouldName)
select1 = "select * from table1"
If rs.State = 1 Then rs.Close
rs.Open select1, cn, adOpenKeyset, adLockReadOnly
If rs.EOF Or rs.BOF Then MsgBox "无数据记录!", vbCritical: Exit Sub
'//
ColCount = rs.Fields.Count
Set xlsheet = xlBook.Worksheets(3)
xlsheet.Visible = xlSheetHidden
Set xlsheet = xlBook.Worksheets(2)
xlsheet.Visible = xlSheetHidden
Set xlsheet = xlBook.Worksheets(1)
xlsheet.Name = "导出数据"
VB.Screen.MousePointer = vbHourglass
xlsheet.Cells(2, 1) = "还没有想到"
rs.MoveFirst
i = 0
While Not rs.EOF '//读入数据
xlApp.Rows(i + 6).Select
xlApp.Selection.Insert Shift:=xlUp
For k = 0 To ColCount - 1
xlsheet.Cells(i + 5, k + 1) = CStr(IIf(IsNull(rs.Fields(k)) = True, "-", rs.Fields(k)))
Next
rs.MoveNext
i = i + 1
Wend
xlApp.Visible = True
xlBook.SaveCopyAs xlBook.Worksheets(1).Name
VB.Screen.MousePointer = vbDefaultEnd Sub
'----------------也可自行修改
TDBGrid1.ExportToFile App.Path & "\kk.xls", False 使用这种方法可以保持数据表的格式,但有乱码。TDBGrid1.ExportToDelimitedFile App.Path & "\kk.xls 使用这种方法时所有的记录都连在一起了,看不出数据表的格式了,就像这样的:1苹果汁11每箱24瓶1839010-1
2牛奶11每箱24瓶191740250
3蕃茄酱12每箱12瓶101370250
4盐22每箱12瓶2253000
5麻油22每箱12瓶21.35000-1