第一种方法较慢，但是数据格式都比较好，需要引用excel的 Microsoft.Office.Interop.Excel.dll office.dll
#Region "导出excel函数"
Public Sub DcExcel(ByVal DGV As DataGridView)
'把datagridview中的数据导出到excel
Dim wapp As New Microsoft.Office.Interop.Excel.Application
Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim wbook As Microsoft.Office.Interop.Excel.Workbook
On Error Resume Next
'///是否预览显示///
wapp.Visible = True
wbook = wapp.Workbooks.Add()
wsheet = wbook.ActiveSheet
Dim iX As Integer
Dim iY As Integer
Dim iC As Integer
For iC = 0 To DGV.Columns.Count - 1
wsheet.Cells(1, iC + 1).Value = DGV.Columns(iC).HeaderText
wsheet.Cells(1, iC + 1).Font.Bold = True
wsheet.Cells(1, iC + 1).Font.Size = 14
Next
wsheet.Rows(2).select()
For iX = 0 To DGV.Rows.Count - 1
For iY = 0 To DGV.Columns.Count - 1
wsheet.Cells(iX + 2, iY + 1).value = DGV(iY, iX).Value.ToString
Next
Next
'///判断是否存储excel文件///
'Dim myval As Long
'Dim mystr As String
'myval = MessageBox.Show("是否保存该Excel表?", "提示窗口", MessageBoxButtons.YesNo)
'判断ok还是no
'Dim myvals As DialogResult
'If myvals = DialogResult.OK Then
'End If
'If myval = vbYes Then
' mystr = InputBox("请输入文件名称", "输入窗口")
' If Len(mystr) = 0 Then
' MsgBox("系统不允许文件名称为空！", 64, "提示窗口")
' Exit Sub
' End If
' wsheet.SaveAs(System.Windows.Forms.Application.StartupPath & mystr & ".xls")
' MsgBox("Excel文件保存成功，位置：" & System.Windows.Forms.Application.StartupPath & mystr & ".xls", 64, "提示窗口")
' wapp.Quit()
'End If
End Sub
#End Region
以下两种方法以流的方式无需引用excel dll，格式不是很稳定，有时候列里面的数据的错乱了，还是不喜欢它们，需要导入import system.io
#Region "导出excel，无需引用excel dll"
Public Function daochu(ByVal x As DataGridView) As Boolean '导出到Excel函数
Try
If x.Rows.Count <= 0 Then '判断记录数,如果没有记录就退出
MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
Else '如果有记录就导出到Excel
Dim xx As Object : Dim yy As Object
xx = CreateObject("Excel.Application") '创建Excel对象
yy = xx.workbooks.add()
Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
For i = 1 To x.Columns.Count '把表头写入Excel
yy.worksheets(1).cells(1, i) = x.Columns(i - 1).HeaderCell.Value
Next
Dim str(x.Rows.Count - 1, x.Columns.Count - 1) '定义一个二维数组
For u = 1 To x.Rows.Count '行循环
For v = 1 To x.Columns.Count '列循环
If x.Item(v - 1, u - 1).Value.GetType.ToString <> "System.Guid" Then
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value
Else
str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value.ToString
End If
Next
Next
yy.worksheets(1).range("A2").Resize(x.Rows.Count, x.Columns.Count).Value = str '把数组一起写入Excel
yy.worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
' yy.worksheets(1).name = x.TopLeftHeaderCell.Value.ToString '表标题写入作为Excel工作表名称
xx.visible = True '设置Excel可见
yy = Nothing '销毁组建释放资源
xx = Nothing '销毁组建释放资源
End If
Return True
Catch ex As Exception '错误处理
MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
Return False
End Try
End Function
#End Region
#Region "从DataGridView中导出数据到Excel"
Public Sub dataGridViewToExcel(ByVal dgv As DataGridView)
Dim saveFileDialog As New SaveFileDialog()
saveFileDialog.Filter = "Execl files (*.xls)|*.xls"
saveFileDialog.FilterIndex = 0
saveFileDialog.RestoreDirectory = True
saveFileDialog.CreatePrompt = True
saveFileDialog.Title = "导出 Excel 文件到"
saveFileDialog.ShowDialog()
If saveFileDialog.ShowDialog() = DialogResult.Cancel Then
'如果选择提醒导出
Return
End If
Dim myStream As Stream
myStream = saveFileDialog.OpenFile()
'StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
Dim sw As New StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0))
Dim str As String = ""
Try
'写标题
For i As Integer = 0 To dgv.ColumnCount - 1
If i > 0 Then
str += vbTab
End If
str += dgv.Columns(i).HeaderText
Next
sw.WriteLine(str)
'写内容
For j As Integer = 0 To dgv.Rows.Count - 1
Dim tempStr As String = ""
For k As Integer = 0 To dgv.Columns.Count - 1
If k > 0 Then
tempStr += vbTab
End If
tempStr += dgv.Rows(j).Cells(k).Value.ToString()
Next
sw.WriteLine(tempStr)
Next
sw.Close()
myStream.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
sw.Close()
myStream.Close()
End Try
End Sub
#End Region