如何将datareader输出到excel

[player_1]

我用的是vs2008 这是我vb代码 已经添加了 microfoft excel 11.0 object 对象

Imports System.Data.SqlClient

Public Class Form5
'定义excel应用对象
Dim eapp As New Excel.Application
'定义excel工作对象
Dim eworksheet As New Excel.Worksheet

’按钮事件
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'设置连接字符串
Dim scon As New SqlConnection("Data source=(local);initial catalog=mytestdata1;integrated security=sspi")
’设置command对象
Dim scom As New SqlCommand("select * from 学生信息表 ", scon)
' 定义一个Datareader对象
Dim sdreader As SqlDataReader
Try
'打开连接
scon.Open()

sdreader = scom.ExecuteReader

eworksheet = eapp.Workbooks.Add.Worksheets

eapp.Visible = True

Dim i, n As Integer
For n = 0 To sdreader.FieldCount - 1
eworksheet.Cells(1, n + 1) = sdreader.GetName(n)

Next
i = 2
Do While sdreader.Read
For n = 0 To sdreader.FieldCount - 1
eworksheet.Cells(i, n + 1) = "'" & sdreader.GetValue(n)
Next
i += 1
Loop
eworksheet.Columns.AutoFit()

Catch ex As Exception
MsgBox(ex.Message)

Finally
sdreader.Close()
scon.Close()

End Try
End Sub
End Class
运行出现错误提示:无法将com对象进行强制转换为excel.worksheet
菜鸟问题
谢谢高手解答

[player_1]

顶一下~急等答案~

[想飞的狼]

Public Function ExportXLsD(ByVal datagrid As DataGridView) ', ByVal Title As String)
Dim Mytable As New DataTable
Mytable = view.ToTable 'CType(DataGridView1.DataSource, DataTable)

If Mytable Is Nothing Then
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
' Exit Function
End If

If Mytable.Rows.Count > 0 Then
Dim MyFileName As String

With SaveFileDialog1
.AddExtension = True '如果用户忘记添加扩展名，将自动家上
.DefaultExt = "xls" '默认扩展名
.Filter = "Excel文件(*.xls)|*.xls"
.Title = "文件保存到"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
FileName = SaveFileDialog1.FileName
End If
End With
MyFileName = Microsoft.VisualBasic.Right(FileName, 4)
If MyFileName = "" Then
MessageBox.Show("文件名为空")
End If
If MyFileName = ".xls" Or MyFileName = ".XLS" Then

Dim FS As IO.FileStream = New IO.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 Mytable.Columns.Count - 1
str = Mytable.Columns(i).Caption
sw.Write(str & vbTab)
Next
sw.Write(vbCrLf)

For j = 0 To Mytable.Rows.Count - 1
For i = 0 To Mytable.Columns.Count - 1
' Dim strColName As String
Dim strRow As String
strRow = IIf(Mytable.Rows(j).Item(i) Is DBNull.Value, "", Mytable.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
MessageBox.Show("怎么回事")
End If
Else
MessageBox.Show("没有记录不能导出数据", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

End Function

[wangjuenhui520]

楼组不一定要使用SqlDataReader 很多功能受到了现在. 应该使用dataset可以的...
效果没什么变化

[wangjuenhui520]

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As SqlConnection = New SqlConnection("Data Source=mis;Initial Catalog=test;User ID=sa;Password=Xutai123")
Dim com As SqlCommand = New SqlCommand("select * From students", con)
Dim da As SqlDataReader
con.Open()
da = com.ExecuteReader
Dim exl As New Excel.Application
Dim work As Excel.Workbook
Dim sh As Excel.Worksheet
exl.Visible = True
work = exl.Workbooks.Add()
sh = work.Sheets(1)
sh.Name = "学员信息"
For i As Integer = 0 To da.FieldCount - 1
sh.Cells(1, i + 1).value = da.GetName(i)
Next
Dim a = 2
Do While da.Read
For n As Integer = 0 To da.FieldCount - 1
sh.Cells(a, n + 1).value = da.GetValue(n)
Next
a += 1
Loop
da.Close()
con.Close()
End Sub

以上的代码实验过没有任何问题...

[bamboo_jaing]

楼上正解