How to Export Dataset to Excel Using XML VB.Net & SQL Server

Hi Jack

How to Export Dataset to Excel Using XML VB.Net & SQL Server

In this post we will note how of database dataset exported into microsoft excel.

we will be using xml, and then on the next we ae going to use Microsoft Office Interop so we can compare the performance of both.


Prepare the data you want to export

Dataset 

Private Function load_data_into_dataset() As DataSet
        Dim value As New DataSet
        Using iKon = New SqlConnection(sql_connection_string)
            If iKon.State = ConnectionState.Open Then iKon.Close()
            iKon.Open()
            Using iCmd = New SqlCommand("Select * from cars", iKon)
                Using adapter = New SqlDataAdapter
                    adapter.SelectCommand = iCmd
                    adapter.Fill(value)
                End Using
            End Using
        End Using
        Return value
End Function

EmptyForm

Fill Datagridview 

Private Sub Btn_load_dataset_Click(sender As Object, e As EventArgs) Handles Btn_load_dataset.Click
        DataGridView1.DataSource = load_data_into_dataset.Tables(0)
End Sub

Form Filled

Export Dataset to Excel

 Private Sub export_dataset_to_excel(ByVal dataset As DataSet)
        Dim excel_export_file As String = Application.StartupPath & "\Export" & Format(DateTime.Now, "ddMMyyHHmmss") & ".xls"
        Dim excel As New IO.StreamWriter(excel_template, False)
        Dim rows As Integer = 0

        excel.WriteLine("")
        excel.WriteLine("")
        excel.WriteLine("")
        excel.WriteLine("    ")
        excel.WriteLine("        ")
        excel.WriteLine("           ")
        excel.WriteLine("        ")
        excel.WriteLine("    ")
        excel.WriteLine("    ")
        excel.WriteLine("        ")

        With dataset.Tables(0)
            rows = .Rows.Count
            excel.WriteLine("            ")
            'create columns name
            For col = 0 To .Columns.Count - 1
                excel.WriteLine("                ")
                excel.WriteLine(String.Format("{0}", .Columns(col).ColumnName))
                excel.WriteLine("                ")
            Next
            excel.WriteLine("            ")

            'insert rows
            For row = 0 To .Rows.Count - 1
                excel.WriteLine("            ")
                For col = 0 To .Columns.Count - 1
                    excel.WriteLine("                ")
                    excel.WriteLine(String.Format("{0}", .Rows(row).Item(col).ToString))
                    excel.WriteLine("                ")
                Next
                excel.WriteLine("            ")
            Next
        End With
        excel.WriteLine("        ")
        excel.WriteLine("    ")
        excel.WriteLine("")
        excel.Close()

        System.Diagnostics.Process.Start(excel_export_file)
 End Sub

Call method export_dataset_to_excel

 Private Sub btn_export_dataset_to_excel_Click(sender As Object, e As EventArgs) Handles btn_export_dataset_to_excel.Click
        export_dataset_to_excel(load_data_into_dataset)
 End Sub

Open The Excel File Export Result

Dataset successfully exported into excel without using any format.

if you want to use this article in your blog please also include this link as a source. thank you.