这是网上的一个在Vb.net中将数据库中的数据导出到Excel的例子，经过了小小的修改，这种方法的好处是不需要另外的引用，代码如下：
ASP/Visual Basic Code复制内容到剪贴板
- Dim Mytable As DataTable = myDataSet("select * from JL").Tables(0) '红色的是自定函数，可见最下面代码
- If Mytable Is Nothing Then
- MessageBox.Show("没有记录不能导出数据", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
- Exit Sub
- End If
- Dim SaveFileDialog As FileDialog = New SaveFileDialog
- If Mytable.Rows.Count > 0 Then
- Dim MyFileName As String
- Dim FileName As String = ""
- With SaveFileDialog
- .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 Sub
- 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 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 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(vbCrLf)
- Next
- sw.Close()
- fs.Close()
- MessageBox.Show("数据导出成功！", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
- Else
- Exit Sub
- End If
- Else
- MessageBox.Show("没有记录不能导出数据", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
- End If
折叠C/C++ Code复制内容到剪贴板
- Public Function myDataSet(ByVal SQLStr As String) As DataSet
- dbName = My.Settings.dbNamer
- Try
- ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\" & dbName & ".mdb"
- Conn = New OleDbConnection(ConnStr)
- Conn.Open()
- Dim da As OleDbDataAdapter = New OleDbDataAdapter(SQLStr, Conn)
- Dim ds As DataSet = New DataSet
- da.Fill(ds)
- Return ds
- Catch ex As Exception
- MsgBox(ex.Message.ToString, MsgBoxStyle.Information, "提示")
- ' MsgBox("请检查数据库名称是否正确！！", MsgBoxStyle.Information, "找不到数据库")
- 'frmSet.txtDbName.Text = "data2003"
- dbName = My.Settings.dbNamer = "site"
- 'frmMain.toolReReadData.PerformClick()
- ' frmSet.Show()
- Finally
- Conn.Close()
- End Try
- Return Nothing '为去无返回警告
- End Function