question

KerryOu-8610 avatar image
0 Votes"
KerryOu-8610 asked KerryOu-8610 commented

How to export dataset to excel?

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.

dotnet-visual-basicdotnet-aspnet-webpages
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
1 Vote"
YijingSun-MSFT answered KerryOu-8610 commented

Hi @KerryOu-8610 ,
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

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer.
I run your code showing this Error, any idea?

81375-1.jpg


0 Votes 0 ·
1.jpg (57.4 KiB)

Hi @KerryOu-8610 ,
What's your type of your file?I'm guessing that your ContentType have wrong.You could change your ContentType. More details,you could refer to below article:
https://stackoverflow.com/questions/51875581/c-download-file-badresponse-unexpected-token#comment90702538_51875581
Best regards,
Yijing Sun

0 Votes 0 ·

What is that meaning? Same type whit your code. Export to excel. my office is 2010.


  Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

What should I change to ?
Thank you.






0 Votes 0 ·
Show more comments

It is work on a new page. Thank you so much!

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered KerryOu-8610 commented

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



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer.
"use version 3.4.9 for .NET Framework," I need to use 4.7 .net Framework for the RDLC report.
So, I can't use that way , right?

0 Votes 0 ·

Sure you can use it, I started demonstrating this back in .NET Framework 4.5. And the reason why I selected an older version of SpreadSheetLight is because some of the newer versions are for .NET Core. So go with what I provided.

Also, see the following post for a similar code sample which I just posted.


0 Votes 0 ·

Thank you so much.

0 Votes 0 ·