Click here to Skip to main content
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 3: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 OriginalGriff 299
1 Maciej Los 295
2 Sergey Alexandrovich Kryukov 230
3 Aajmot Sk 191
4 Sinisa Hajnal 166
0 OriginalGriff 7,760
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,785


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
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