asked on

I need help exporting info from a dataset to Excel in my VB.Net application

Hi Experts,
I want to export the records from a dataset to Excel. How can I do this without using Excel Interop?
Is this possible?

Here is the code I use to populate my dataset:

Me.Sp_SELECT_ImportedTableAdapter.Fill(Me.TimeTrackingDataSet1.sp_SELECT_Imported, dtpStartDate.Text, dtpEndDate.Text)
	With dgvTfsRecs
		.DataSource = SpSELECTImportedBindingSource
		.Refresh()
	End With

Open in new window

Thank you in advance for your help,
mrotor

Eric Greene

You can, if you are willing to delve into the OpenXML format. I use ExcelPackage Plus to output to Excel. You may find it helpful. Otherwise, you are stuck with Interop and managing Excel versions.

Scott McDaniel (EE MVE )

Can you export to a standard CSV file, which Excel can open and read?

The code below exports from a DataGridView, but you could modify it to export from a Datatable fairly easily:

 Using sr As System.IO.StreamWriter = System.IO.File.CreateText(path)
                Dim rowdata As String = ""
                '/ get the "header" columns
                For Each col As DataGridViewColumn In dgPO.Columns
                    rowdata += col.HeaderText & ","                    
                Next

                Dim colcnt As Integer = dgPO.Columns.Count

                sr.WriteLine(rowdata)

                Using con As New SqlConnection(JBConnection)
                    con.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = con

                        For Each dgr As DataGridViewRow In dgPO.Rows
                            Dim header As String = ""
                            rowdata = ""
                            For Each col As DataGridViewColumn In dgPO.Columns
                                If Not col.DataPropertyName = "Material_Req" And Not col.DataPropertyName = "PO_Detail" Then
                                    rowdata = rowdata & ControlChars.Quote & dgr.Cells(col.Index).Value.ToString.Replace(ControlChars.Quote, ControlChars.Quote & ControlChars.Quote) & ControlChars.Quote & ","                                    
                                End If
							Next
							
							sr.WriteLine(rowdata)
                            
                        Next
                    End Using
                End Using

                sr.Flush()
                sr.Close()
            End Using

Open in new window

You'd obviously have to change the names of the Objects and such to match your own

Fernando Soto

Hi mrotor;

One way to do it without any dependencies on Microsoft Excel is using a NuGet package called GemBox.Spreadsheet. This is a commercial product but does have a free version which can be used. To use the free version you will need to execute the following line of code before instantiating an instance of the GemBox object as follows

// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense ( "FREE-LIMITED-KEY" );

Open in new window

else if you buy the software this is where you place the License key. Please read software license agreement because you will need to give credit to GemBox for using their free version of the software.

Here is a demo of what you are trying to do, GemBox demo Import or Insert DataTable into Excel file in C# and VB.NET.

mainrotor

ASKER

Fernando,
I have heard of GemBox.spreadsheet, but never used it. I don't know if that will work for this project but I will definitely give it a try.

Thanks

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mainrotor

ASKER

Eric,
A .csv was my original plan, but management said no. Therefore, I decided to bite the bullet and use INTEROP. Now that I have finished coding my function using INTEROP, they are saying maybe we should use CSV. That being the case, I will award you the points.

Thank you all.
mrotor

Éric Moreau

if you ever need to deal with Excel files without using interops, I suggest you look at http://www.aspose.com/products/cells/net