可以参考下如下代码: 应该是少了这句吧: Imports Microsoft.Office.Interop
Imports System.Data.sqlclient
Imports System.IO
Imports System.Text
Imports System
Imports Microsoft.Office.Interop
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 Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
aconnection = New SqlConnection("data source=erpserver;initial catalog=JINGYI;user id=sa;pwd=;")
acommand = New SqlCommand("select RTRIM(B.TC006) 厂商,RTRIM(B.CREATOR) 创建者,RTRIM(B.CREATE_DATE) 发料日期,RTRIM(A.TE001) 领料单别,RTRIM(A.TE002) 领料单号,RTRIM(A.TE004) 料号,RTRIM(A.TE017) 料名,RTRIM(A.TE005) 领料数量,RTRIM(A.TE006) 单位,RTRIM(A.TE008) 库别,RTRIM(A.TE011) 指令单别,RTRIM(A.TE012) 指令单号,RTRIM(C.TA015) 指令数量,RTRIM(C.TA017) 返回数量 from MOCTE A,MOCTC B,MOCTA C WHERE A.TE001=B.TC001 AND A.TE002=B.TC002 AND B.TC006='" & TextBox1.Text & "' AND A.TE011=C.TA001 AND A.TE012=C.TA002", aconnection)
adapter1 = New SqlDataAdapter
adapter1.SelectCommand = acommand
acommand.CommandTimeout = 0
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) '保存
End Sub
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
If Asc(e.KeyChar) = 13 Then
Call Button1_Click(sender, e)
End If
End Sub
End Class