Click here to Skip to main content
11,637,662 members (87,494 online)
Rate this: bad
good
Please Sign up or sign in to vote.
Hi All,
Is there a way to write data to an Excel file in the memory stream and email it without saving it in vb.net? Here's the scenario: I am working on a vb.net console app that retrieves a dataset from SQL server. The requirement is that I have to email this dataset in Excel format without creating a file on the disc. Is there a way to do this?

Thanks in advance for your help....
Tom
Posted 4-Feb-13 2:43am
toms.c319

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You should be able to render any file as a byte array. I do this all the time when I render RDLCs as PDF or Excel files in the browser. I render them to a byte array then write this to the response stream. In your case you would just add the byte array into an Attachment object.


VB
Dim att As New Attachment(New MemoryStream(bytes), name)

C#
Attachment att = new Attachment(new MemoryStream(bytes), name);
  Permalink  
Comments
toms.c at 4-Feb-13 15:36pm
   
Thanks BC for your reply. Please see my code below. How do I convert the excel object I create to memory stream without saving it to the disc and attach it to email. (would be great if you can provide some sample codes)
I want to remove the
.ActiveCell.Worksheet.SaveAs(filename)
line and convert the work sheet to memory stream.


<pre lang="vb">Dim con As SqlConnection
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet

con = New SqlConnection(connectionstring)
cmd.Connection = con

con.Open()
cmd.CommandTimeout = 200
cmd.CommandText = "zzTestReport_TC"
cmd.CommandType = CommandType.StoredProcedure

da = New SqlDataAdapter(cmd)
da.Fill(ds)

Dim filename As String
Dim col, row As Integer

Dim mystream As New MemoryStream()

Try
ComDset.Reset()
da.Fill(ComDset, "TTbl")
If ComDset.Tables.Count < 0 Or ComDset.Tables(0).Rows.Count <= 0 Then
Exit Sub
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
Dim Excel As Object = CreateObject("Excel.Application")
If Excel Is Nothing Then
MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
Return
End If


'Export to Excel process

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

Dim i As Integer = 1
For col = 0 To ComDset.Tables(0).Columns.Count - 1
.cells(1, i).value = ComDset.Tables(0).Columns(col).ColumnName
.cells(1, i).EntireRow.Font.Bold = True
i += 1
Next
i = 2
Dim k As Integer = 1
For col = 0 To ComDset.Tables(0).Columns.Count - 1
i = 2
For row = 0 To ComDset.Tables(0).Rows.Count - 1
.Cells(i, k).Value = ComDset.Tables(0).Rows(row).ItemArray(col)
i += 1
Next
k += 1
Next
filename = "c:\zTest\File_Exported" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"
.ActiveCell.Worksheet.SaveAs(filename)


End With




cmd.Dispose()
con.Close()</pre>
BC @ CV at 4-Feb-13 15:51pm
   
Do you really need the formatting (bold, dd-MM-yyyy_hh-mm-ss, etc)? If not just write a CSV file.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 jyo.net 380
1 Sergey Alexandrovich Kryukov 309
2 stibee 308
3 OriginalGriff 230
4 sasanka sekhar panda 190
0 OriginalGriff 9,031
1 Sergey Alexandrovich Kryukov 8,763
2 Mika Wendelius 6,999
3 F-ES Sitecore 2,388
4 Suvendu Shekhar Giri 2,320


Advertise | Privacy | Mobile
Web01 | 2.8.150728.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100