Click here to Skip to main content
14,982,842 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,
Is there a way to write data to an Excel file in the memory stream and email it without saving it in Here's the scenario: I am working on a 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....

1 solution

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.

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

Attachment att = new Attachment(new MemoryStream(bytes), name);
toms.c 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
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

cmd.CommandTimeout = 200
cmd.CommandText = "zzTestReport_TC"
cmd.CommandType = CommandType.StoredProcedure

da = New SqlDataAdapter(cmd)

Dim filename As String
Dim col, row As Integer

Dim mystream As New MemoryStream()

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
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)
End If

'Export to Excel process

With Excel
.SheetsInNewWorkbook = 1

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
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
k += 1
filename = "c:\zTest\File_Exported" & Format(Now(), "dd-MM-yyyy_hh-mm-ss") & ".xls"

End With

BC @ CV 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)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900