vb.net(winform)查询出的datagrid数据怎么显示在excel,代码怎么写
如题

[解决办法]
这个是传入一个DataTable，ds.table( "表名也可以 ")
你试试吧,按照说明加一下引用

'过程名称：ExportToExcel
'功能描述：将传入的表格导出为Excel表
'接收参数：p_InfoTable 要导出的表格
'返回参数：
'创建人员及日期：zzz@2006-11-02
'注意事项:使用此函数需要引用Microsoft Excel 11.0 Object Library
' 并且附加上frmWaiting窗体
Public Sub ExportToExcel(ByVal p_InfoTable As Data.DataTable)
If p_InfoTable.Rows.Count > 0 Then
Dim NewfrmWaiting As New frmWaiting
NewfrmWaiting.Show()
System.Windows.Forms.Application.DoEvents()
Try
Dim datav As New DataView
datav = p_InfoTable.DefaultView
Dim i, j As Integer
Dim rows As Integer = datav.Table.Rows.Count
Dim cols As Integer = datav.Table.Columns.Count
Dim DataArray(rows - 1, cols - 1) As String
Dim myExcel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application

For i = 0 To rows - 1
For j = 0 To cols - 1
If datav.Table.Rows(i).Item(j) Is System.DBNull.Value Then
Else
'如果是数字类型的就保留两位小数点
If datav.Table.Rows(i).Item(j).GetType.ToString.ToLower = "system.int16 " Or _
datav.Table.Rows(i).Item(j).GetType.ToString.ToLower = "system.int32 " Or _
datav.Table.Rows(i).Item(j).GetType.ToString.ToLower = "system.single " Or _
datav.Table.Rows(i).Item(j).GetType.ToString.ToLower = "system.double " Then
DataArray(i, j) = Format(datav.Table.Rows(i).Item(j), "#########.00 ")
Else
DataArray(i, j) = datav.Table.Rows(i).Item(j)
End If

'DataArray(i, j) = datav.Table.Rows(i).Item(j)
End If
Next
Next
myExcel.Application.Workbooks.Add(True)
myExcel.Visible = True
For j = 0 To cols - 1
myExcel.Cells(1, j + 1) = datav.Table.Columns(j).ColumnName
Next
myExcel.Range( "A2 ").Resize(rows, cols).Value = DataArray
Catch exp As Exception
MsgBox( "数据导出失败!请查看是否已经安装了Excel ", MsgBoxStyle.Critical, "导出失败 ")
Finally
NewfrmWaiting.Close()
NewfrmWaiting.Dispose()
End Try
Else
MsgBox( "未发现有效数据！ ", MsgBoxStyle.Exclamation, "提示 ")
End If
End Sub
[解决办法]
Public Class F_Search
Inherits System.Windows.Forms.Form
Dim Info_DataSet As New DataSet
'创建数据集
Private MyDataSet As New DataSet
Private MyCommand As OleDbDataAdapter
Private MyDatatable As System.Data.DataTable


Public Function Output_Callinfo()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rowIndex, colIndex As Integer
rowIndex = 1
colIndex = 0
Try

xlApp = CreateObject( "Excel.application ")
xlBook = xlApp.Workbooks().Add
xlSheet = xlBook.Worksheets( "sheet1 ")

Dim Table As DataTable
Table = Info_DataSet.Tables( "数据表名 ")

'将所得到的表的列名,赋值给单元格
Dim Col As DataColumn
Dim Row As DataRow

xlSheet.Columns.ColumnWidth = 20

For Each Col In Table.Columns
colIndex = colIndex + 1
xlApp.Cells(1, colIndex) = Col.ColumnName
Next
xlApp.Cells(1, 1) = "a " \\excel列标题
xlApp.Cells(1, 2) = "b "
xlApp.Cells(1, 3) = "c "
xlApp.Cells(1, 4) = "d "
xlApp.Cells(1, 5) = "e "
xlApp.Cells(1, 6) = "f "
xlApp.Cells(1, 7) = "g "
xlApp.Cells(1, 8) = "h "

'得到表的所有行,赋值给单元格
For Each Row In Table.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each Col In Table.Columns
colIndex = colIndex + 1
If colIndex = 2 Then
xlApp.Cells(rowIndex, colIndex) = " ' " & Row(Col.ColumnName)
Else : xlApp.Cells(rowIndex, colIndex) = Row(Col.ColumnName)
xlApp.Cells(rowIndex, 9) = " "
End If
Next
Next

With xlSheet
.Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Name = "黑体 "
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).Borders.LineStyle = 1
'设表格边框样式
.Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).WrapText = 1
'自动换行
End With

If Not Datagrid.VisibleRowCount = 0 Then
'判断文件是否存在
If File.Exists(Directory.GetCurrentDirectory & "\Excel\文名名称 ".xls ") = True Then
File.Delete(Directory.GetCurrentDirectory & "\Excel\文件名称.xls ")
End If
xlBook.SaveAs(Directory.GetCurrentDirectory & "\Excel\文件名称.xls ")
MessageBox.Show( "您导出的文件路径是： " & Directory.GetCurrentDirectory & "\Excel\文件名称.xls ", "文件存放路径 ", MessageBoxButtons.OK, MessageBoxIcon.Information)
xlApp.Visible = True
Else
MessageBox.Show( "当前记录为0,无数据导出！！ ", "Excel ", MessageBoxButtons.OK, MessageBoxIcon.Information)
xlBook.SaveAs(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")
xlBook.Close()
File.Delete(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")
xlApp.Quit()
Exit Function
End If
Catch ex As SqlClient.SqlException
MessageBox.Show( "数据读取异常！ ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
Log.Error(ex.ToString)
MessageBox.Show( "导出Excel文件异常,请关闭重试！！ ", "错误 ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
xlBook.SaveAs(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")

xlBook.Close()
File.Delete(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")
xlApp.Quit()
Exit Function
Finally
End Try
xlBook.SaveAs(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")
xlBook.Close()
File.Delete(Directory.GetCurrentDirectory & "\Excel\Book1.xls ")
xlApp.Quit()
End Function