How to export dataset to excel?

Question

Visual Studio 2019 ;ASP.NET ;VB Code

I fill the dataset from SQL , but how to export to Excel ?

   Dim temp_string As String
            temp_string = "SELECT  *  FROM[T - Staff] WHERE join >'2020' ORDER BY CName"    
            KY_HRM.Open()
            Dim daTT As New SqlDataAdapter(temp_string, KY_HRM)
            Dim dsTT As New Data.DataSet
            dsTT.Clear()
            daTT.Fill(dsTT, "dw")

It would be nice to have code or examples. I am new.
Thank you very much.

Answer 1

Hi @Kerry Ou ,
As far as I think,you could use Openxml to export dataset to excel.Just like this:

Protected Sub ExportExcel(sender As Object, e As EventArgs)  
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString  
    Using con As New SqlConnection(constr)  
        Using cmd As New SqlCommand("SELECT  *  FROM[T - Staff] WHERE join >'2020' ORDER BY CName")  
            Using sda As New SqlDataAdapter()  
                cmd.Connection = con  
                sda.SelectCommand = cmd  
                Using dt As New DataSet()  
                    sda.Fill(dt)  
                    Using wb As New XLWorkbook()  
                        wb.Worksheets.Add(dt.Table(0), "[T - Staff]")  
                        Response.Clear()  
                        Response.Buffer = True  
                        Response.Charset = ""  
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"  
                        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx")  
                        Using MyMemoryStream As New MemoryStream()  
                            wb.SaveAs(MyMemoryStream)  
                            MyMemoryStream.WriteTo(Response.OutputStream)  
                            Response.Flush()  
                            Response.End()  
                        End Using  
                    End Using  
                End Using  
            End Using  
        End Using  
    End Using  
End Sub  

More details,you could refer to below article:
https://www.aspsnippets.com/Articles/Export-data-from-SQL-Server-to-Excel-in-ASPNet-using-C-and-VBNet.aspx

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Yijing Sun

Answer 2

There is a free library, SpreadSheetLight that does not require Excel to be installed. SpreadSheetLight works with desktop and web projects. To try it locally before trying in your web project, see the following project on GitHub.

Note, using Excel automation rather than SpreadSheetLight can be problematic on a server while not the case with SpreadSheetLight. Also, if the version of Excel changes on the server it can break your code.

• NuGet package for SpreadSheetLight, use version 3.4.9 for .NET Framework, for .NET Core use the current version..

Simple example

Public Shared Sub Export(
     pFileName As String, 
     pSheetName As String, 
     pDataSet As DataSet, 
     pTableName As String, 
     pColumnHeaders As Boolean)

    Using doc As New SLDocument()

        doc.SelectWorksheet(pSheetName)
        doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataSet.Tables(pTableName), pColumnHeaders)
        doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)
        doc.SaveAs(pFileName)

    End Using

End Sub

Styling code sample

Public Shared Sub SimpleExportRaw(pFileName As String, pSheetName As String, pDataTable As DataTable, pColumnHeaders As Boolean)

    Using doc As New SLDocument()

        doc.SelectWorksheet(pSheetName)

        doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)


        Dim style As New SLStyle With {.FormatCode = "MM/dd//yyyy"}

        doc.SetColumnStyle(11, style)

        style.Font.Bold = True
        doc.SetRowStyle(1, 1, style)

        Dim stats = doc.GetWorksheetStatistics()

        doc.AutoFitColumn(1, stats.EndColumnIndex)

        ' original default name is Sheet1, change it to the name in pSheetName
        doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)

        doc.SaveAs(pFileName)

    End Using

End Sub