VB.NET导出的XLS为什么是乱码???

mosenyou 2010-07-01 09:33:26

请高手指点，我在运行VB.NET时导出的XLS文件，为什么在EXCEL 2003打开是乱码，里面是乱七八糟的符号。而用EXCEL 2007打开就没问题请问这是怎么回事？代码如下，该怎么改才不会用EXCEL2003打开是乱码?

Imports System.Data.sqlclient
Imports System.IO
Imports System.Text
Imports System
Public Class Form1
Dim aconnection As SqlConnection
Dim atable As DataTable
Dim adapter1 As SqlDataAdapter
Dim acommand As SqlCommand
Public Function ExportXLsD(ByVal datagrid As DataGrid) As Boolean
', ByVal Title As String)
'Dim atable As New DataTable
'atable = CType(datagrid.DataSource, DataTable)
If atable Is Nothing Then
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Function
End If
If atable.Rows.Count > 0 Then
Dim MyFileName As String
Dim FileName As String = ""
With SaveFileDialog1
.AddExtension = True '如果用户忘记添加扩展名，将自动家上
.DefaultExt = "xls" '默认扩展名
.Filter = "Excel文件(*.xls)|*.xls"
.Title = "文件保存到"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
FileName = .FileName
End If
End With
MyFileName = Microsoft.VisualBasic.Right(FileName, 4)
If MyFileName = "" Then
Exit Function
End If
If MyFileName = ".xls" Or MyFileName = ".XLS" Then
Dim FS As FileStream = New FileStream(FileName, FileMode.Create)
Dim sw As StreamWriter = New StreamWriter(FS, System.Text.Encoding.Default)
sw.WriteLine(vbTab & FileName & vbTab & Date.Now)
Dim i, j As Integer
Dim str As String = ""
For i = 0 To atable.Columns.Count - 1
str = atable.Columns(i).Caption
sw.Write(str & vbTab)
Next
sw.Write(vbCrLf)
For j = 0 To atable.Rows.Count - 1
For i = 0 To atable.Columns.Count - 1
Dim strRow As String
strRow = IIf(atable.Rows(j).Item(i) Is DBNull.Value, "", atable.Rows(j).Item(i))
sw.Write(strRow & vbTab)
Next
sw.Write(vbLf)
Next
sw.Close()
FS.Close()
MessageBox.Show("数据导出成功！", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Exit Function
End If
Else
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
aconnection = New SqlConnection("data source=erpserver;initial catalog=DLCSYS;user id=sa;pwd=;")
acommand = New SqlCommand("select * from DSCMB WHERE MB001 LIKE '%" & TextBox1.Text & "%'", aconnection)
adapter1 = New SqlDataAdapter
adapter1.SelectCommand = acommand
atable = New DataTable
adapter1.Fill(atable)
DataGridView1.DataSource = atable
End Sub

Private Sub OK_Button_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
Dim saveExcel As SaveFileDialog
saveExcel = New SaveFileDialog
saveExcel.Filter = "Excel文件(.xls)|*.xls"
Dim filename As String
If saveExcel.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Sub
filename = saveExcel.FileName
Dim excel As Excel.Application
excel = New Excel.Application
excel.DisplayAlerts = False
excel.Workbooks.Add(True)
excel.Visible = False
Dim i As Integer
For i = 0 To DataGridView1.Columns.Count - 1
excel.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText
Next
'设置标题
Dim j As Integer
For i = 0 To DataGridView1.Rows.Count - 1 '填充数据
For j = 0 To DataGridView1.Columns.Count - 1
excel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value
Next
Next
excel.Workbooks(1).SaveCopyAs(filename) '保存
Me.Close()
End Sub
End Class

...全文