Better way than this to export Datatable to Excel/Email?

Posted on 2012-03-12
Last Modified: 2012-03-26
In the code below, I retrieve a DataTable from SQL, then enumerate into a text file, attach it to an email and send it:

Dim dt as DataTable = GetData()
Dim sb As New StringBuilder
For Each dc As DataColumn In dt.Columns
	sb.Append("<td>" & dc.ColumnName & "</td>")
For Each dr As DataRow In dt.Rows
	For Each dc As DataColumn In dt.Columns
		sb.Append("<td>" & dr.Item(dc.ColumnName).ToString & "</td>")
Dim mm As New MailMessage
mm.To.Add(New MailAddress("", "Me"))
mm.From = New MailAddress("")
mm.IsBodyHtml = False
mm.Body = "Your data is attached"
If File.Exists(Environment.CurrentDirectory & "\" & Me._SpreadSheetFile) Then
	File.Delete(Environment.CurrentDirectory & "\" & Me._SpreadSheetFile)
End If
Dim fs As FileStream = File.Create((Environment.CurrentDirectory & "\" & Me._SpreadSheetFile))
Dim writer As New StreamWriter((Environment.CurrentDirectory & "\" & Me._SpreadSheetFile), True)
	Dim att As New Attachment(Environment.CurrentDirectory & "\" & Me._SpreadSheetFile)
	att.ContentType = New Net.Mime.ContentType("application/")
Catch ex As ApplicationException
	Throw ex
End Try
Dim sc As New SmtpClient(System.Environment.MachineName, 25)
sc.DeliveryMethod = Net.Mail.SmtpDeliveryMethod.Network
sc.UseDefaultCredentials = False
sc.Credentials = Me._emailCredentials
Catch ex As Exception
	Throw ex
	sb = Nothing
	mm = Nothing
	sc = Nothing
End Try

Open in new window

I am a VB.NET newbie, so have the following questions:

1) The attachment should be a Microsoft Excel spreadsheet.  Because Excel opens basic HTML, I've simply create a text file with HTML inside, and saved as XLS.  Is there a more efficient/better recommended way to export to Excel?  I've read dozens of tutorials on this today already so please don't post any more...!

2) Is it possible to attach the file directly from memory, to save creating the file in the operating system, then attaching it, then deleting next time it is executed?

3) At the risk of scrapping all my code, is there a more efficient/faster way to complete my logic?
Question by:Rouchie
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 35

Expert Comment

ID: 37711011
this procedure will write your datatable to a specified excel file

Public Sub DataTableToExcel(ByVal fileName As String, ByVal dt As System.Data.DataTable)
	Dim ExlApp As Excel.Application
    	Dim aBook As Excel.Workbook
        Dim iCol, iRow, iColVal As Integer
        Dim missing As Object = System.Reflection.Missing.Value
        Dim i As Integer
        ' Open the document that was chosen by the dialog
        Dim aBook As Excel.Workbook
            ''re-initialize excel app
            ExlApp = New Excel.Application

            If ExlApp Is Nothing Then
                ''throw an exception
                Throw (New Exception("Unable to Start Microsoft Excel"))
                ''supresses overwrite warnings
                ExlApp.DisplayAlerts = False
                'aBook = New Excel.Workbook
                ''check if file exists
                If File.Exists(fileName) Then
                    aBook = ExlApp.Workbooks.Open(fileName)
                    aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
                End If
                With ExlApp
                    .SheetsInNewWorkbook = 1
                    'For displaying the column name in the the excel file.
                    For iCol = 0 To dt.Columns.Count - 1
                        ''clear column name before setting a new value
                        .Cells(1, iCol + 1).Value = ""
                        .Cells(1, iCol + 1).Value = dt.Columns(iCol).ColumnName.ToString
                    'For displaying the column value row-by-row in the the excel file.
                    For iRow = 0 To dt.Rows.Count - 1
                            For iColVal = 0 To dt.Columns.Count - 1
                                If TypeOf dt.Rows(iRow).ItemArray(iColVal) Is String Then
                                    .Cells(iRow + 2, iColVal + 1).Value = "'" & Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                                    .Cells(iRow + 2, iColVal + 1).Value = Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                                End If
                        Catch ex As Exception
                            err.WriteError("C:\ERRORS\", ex.Message, ex.StackTrace)
                            'err.WriteError(GetExecutingAssembly.Location.ToString() & "errorlog.txt", ex.Message, ex.StackTrace)
                            Console.Write("ERROR: " & ex.Message)
                        End Try
                    If File.Exists(fileName) Then
                        .ActiveWorkbook().Save() 'fileName)
                        .ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)
                    End If
                End With
                Console.Write("File exported sucessfully")
            End If
        Catch ex As Runtime.InteropServices.COMException
            Console.Write("ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
            aBook = Nothing
            ExlApp = Nothing

        End Try
    End Sub

Open in new window

LVL 40
ID: 37712227
Excel can connect directly to SQL Server and create a spreadsheet out of the result of a query.

You might be able to have Excel do all the job for your without having to go through a DataTable to retrieve the data.

Explore the Data...Import External Data menu entry in Excel 2003 or whatever ribbon entry that replaces that in newer versions.

Using that feature, your .NET code could simply open the Excel file, trigger a refresh of the data, save and the job is done. Simply send the result.

I have not worked with that feature in years, and used the scenario that I am suggesting from VB6 with old code I do not have anymore, so I cannot send you sample code. But I remember that it was very efficient and easy to code.
LVL 25

Author Comment

ID: 37713625
Thanks for your responses.  Please let me answer each in turn...

>> this procedure will write your datatable to a specified excel file

Yes that looks fine.  Some questions though:

1) I assume I need to create a Reference to Excel in the VB project for this to work?
2) Excel is not installed on the live server, only on development machines, so would this work in a live environment?
3) You appear to enumerate row-by-row, as in my example, so surely its not any more efficient?
4) Its also creating a file on the OS which I'm trying to avoid - can the file be passed straight from memory as an attachment?

>> Excel can connect directly to SQL Server and create a spreadsheet out of the result of a query.

That's a good idea, however, in this case Excel is being used as a third-party data transfer tool between two databases that cannot connect directly to one another.  Therefore the live machines won't have Excel, so .NET is doing the work of reading the data and converting to ADO.NET.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 35

Accepted Solution

YZlat earned 500 total points
ID: 37716878
1) You are correct
2) That's a problem, if you are using Interop, Excel must be installed
3) Do you have other suggestions?
4) I don't think so
LVL 35

Expert Comment

ID: 37716902
LVL 35

Expert Comment

ID: 37716988
LVL 25

Author Comment

ID: 37718733
Hey YZlat

Thanks for the follow up.

>>> 3) You appear to enumerate row-by-row, as in my example, so surely its not any more efficient?
>>  Do you have other suggestions?

Nope, but, being new to this type of thing I wondered if there was any method I wasn't aware of to 'push' the DataTable into a text representation without enumeration being necessary.
I tried getting SQL to export the results as XML, which worked really well, however, Excel 2003 (on the client server) could not open it natively without Importing.

From the links you posted, the first is very similar to what I've done initially, which gives me reassurance that I've not wasted my time, so thanks for that.

The second link involves using a webcontrol, which I do within my web apps (where my skills lie), however, I was researching to see if there was a 'better Forms method' than this.  Anyway I'm kind of glad there isn't because at least now I can move on.

Thanks for your help and clarification.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question