Rouchie
asked on
Better way than this to export Datatable to Excel/Email?
In the code below, I retrieve a DataTable from SQL, then enumerate into a text file, attach it to an email and send it:
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?
Dim dt as DataTable = GetData()
Dim sb As New StringBuilder
sb.Append("<html>")
sb.Append("<body>")
sb.Append("<table>")
sb.Append("<tr>")
For Each dc As DataColumn In dt.Columns
sb.Append("<td>" & dc.ColumnName & "</td>")
Next
sb.AppendLine("</tr>")
For Each dr As DataRow In dt.Rows
sb.Append("<tr>")
For Each dc As DataColumn In dt.Columns
sb.Append("<td>" & dr.Item(dc.ColumnName).ToString & "</td>")
Next
sb.Append("</tr>")
Next
sb.Append("</table>")
sb.Append("</body>")
sb.Append("</html>")
Dim mm As New MailMessage
mm.To.Add(New MailAddress("Me@MyDomain.com", "Me"))
mm.From = New MailAddress("some@body.com")
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))
fs.Close()
fs.Dispose()
Dim writer As New StreamWriter((Environment.CurrentDirectory & "\" & Me._SpreadSheetFile), True)
writer.WriteLine(sb.ToString)
writer.Close()
writer.Dispose()
Try
Dim att As New Attachment(Environment.CurrentDirectory & "\" & Me._SpreadSheetFile)
att.ContentType = New Net.Mime.ContentType("application/vnd.ms-excel")
mm.Attachments.Add(att)
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
Try
sc.Send(mm)
Catch ex As Exception
Throw ex
Finally
sb = Nothing
mm = Nothing
sc = Nothing
End Try
dt.Dispose()
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?
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.
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.
ASKER
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.
>> 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Open in new window