|
-
Jul 12th, 2012, 11:01 AM
#1
Thread Starter
Member
 Export Dataset to Excel Attachment
Hi all,
I need to give users of my asp.net/vb.net web application the ability to click a button which will then send them an e-mail with the contents of a SQL query attached as an Excel e-mail attachment.
I can manage all of this apart from getting the contents of a VB dataset object into an Excel file on my server.
I know how to export a gridview to Excel at the client end, but my boss has been adamant that he wants the button to send an e-mail with the results attached - I have no need to display a gridview at all.
Can anyone help me in exporting the contents of a datareader or dataset directly into an Excel file at the server side?
-
Jul 12th, 2012, 01:09 PM
#2
Re: Export Dataset to Excel Attachment
I'm not sure about the serverside, but over in the .NET codebank I have a class that exports datatables and possible datareaders (or maybe it was dataviews) to Excel. It would require that the server have the PIAs on it, though. I don't know that there is any way around that, though, since the PIA's are where you get the Excel types from.
My usual boring signature: Nothing
 
-
Jul 12th, 2012, 02:18 PM
#3
Re: Export Dataset to Excel Attachment
I think you would be better off asking your question in the ASP.NET section.
-
Jul 12th, 2012, 02:24 PM
#4
Re: Export Dataset to Excel Attachment
 Originally Posted by Shaggy Hiker
I'm not sure about the serverside, but over in the .NET codebank I have a class that exports datatables and possible datareaders (or maybe it was dataviews) to Excel. It would require that the server have the PIAs on it, though. I don't know that there is any way around that, though, since the PIA's are where you get the Excel types from.
You would also actually need excel on the server, since the PIAs are just the bridge between the actual excel app and the web app.
An alternative if this is not possible, would be to output them as CSV files, which can be opened in excel. This can be done with no additional references.
-
Jul 12th, 2012, 03:01 PM
#5
Re: Export Dataset to Excel Attachment
Going in the direction of what kleinma suggested take a look at the following method.
http://blogs.msdn.com/b/bethmassi/ar...nq-to-xml.aspx
You create an xml version of an excel file which is your template. Once you have this template open it in notepad, copy the contents to your form (see example below) Make changes as per the article above. Beth uses SQL-Server as a data source while the example below obtains data from MS-Access or a text file to build the xml representation of an Excel 2007 file. If you need a working example I can down grade my VS2010 example to VS2008, let me know.
-
Jul 12th, 2012, 03:02 PM
#6
Re: Export Dataset to Excel Attachment
Code:
Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:html="http://www.w3.org/TR/REC-html40">
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Lines = _
( _
From line In IO.File.ReadAllLines("NamesByComma.txt") _
Where line.Length > 0 _
Let Items = line.Split(","c) _
Select New With {.Name = Items(0), .State = Items(1)} _
).ToList
Dim Persons = From P In Lines Select _
<Row>
<Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
<Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
</Row>
Dim sheet = _
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>kevininstructor</Author>
<LastAuthor>kevininstructor</LastAuthor>
<Created>2011-06-22T02:26:45Z</Created>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12300</WindowHeight>
<WindowWidth>21075</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="People">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
</Row>
<%= Persons %>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>4</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
sheet.Save("Persons.xml")
End Sub
-
Jul 12th, 2012, 03:03 PM
#7
Re: Export Dataset to Excel Attachment
Code:
''' <summary>
''' Build Excel worksheet from tab delimited text file
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim Lines = _
( _
From line In IO.File.ReadAllLines("NamesByTab.txt") _
Where line.Length > 0 _
Let Items = line.Split(CChar(System.Text.RegularExpressions.Regex.Unescape("\t"))) _
Select New With {.Name = Items(0), .State = Items(1)} _
).ToList
Dim Persons = From P In Lines Select _
<Row>
<Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
<Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
</Row>
Dim sheet = _
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>kevininstructor</Author>
<LastAuthor>kevininstructor</LastAuthor>
<Created>2011-06-22T02:26:45Z</Created>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12300</WindowHeight>
<WindowWidth>21075</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="People">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
</Row>
<%= Persons %>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>4</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
sheet.Save("Persons1.xml")
End Sub
Private Sub cmdDataTableSource_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataTableSource.Click
Dim Persons = From P In CType(DataGridView1.DataSource, DataTable).AsEnumerable Select _
<Row>
<Cell><Data ss:Type="String"><%= P.Field(Of String)("FirstName") %></Data></Cell>
<Cell><Data ss:Type="String"><%= P.Field(Of String)("LastName") %></Data></Cell>
</Row>
Dim sheet = _
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>kevininstructor</Author>
<LastAuthor>kevininstructor</LastAuthor>
<Created>2011-06-22T02:26:45Z</Created>
<Version>12.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12300</WindowHeight>
<WindowWidth>21075</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>75</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="People">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell ss:StyleID="s62"><Data ss:Type="String">First</Data></Cell>
<Cell ss:StyleID="s62"><Data ss:Type="String">Last</Data></Cell>
</Row>
<%= Persons %>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>4</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
sheet.Save("Persons_dt.xml")
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Using cn As New OleDb.OleDbConnection With
{
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=People.accdb"
}
Using cmd As New OleDb.OleDbCommand With
{
.Connection = cn,
.CommandText = "SELECT TOP 10 * FROM Person"
}
Dim dt As New DataTable
cn.Open()
dt.Load(cmd.ExecuteReader)
TextBox1.Text = dt.Rows.Count.ToString
DataGridView1.SuspendLayout()
Try
DataGridView1.DataSource = dt
Finally
DataGridView1.ResumeLayout()
End Try
End Using
End Using
End Sub
End Class
-
Jul 13th, 2012, 01:50 AM
#8
Re: Export Dataset to Excel Attachment
It is always weird to me to see XML written directly into the code like that 
Gary
-
Jul 13th, 2012, 04:14 AM
#9
Re: Export Dataset to Excel Attachment
I had to do the same thing but ftp the excell file from server to server. I didn't want to install excel on the server so the way I hacked around that was to have a blank excell file setup the way I needed in a folder, did a file.copy, dumped the data in the columns, saved it and ftp it.
I'd have to dig around to find that code but can do if you need it.
The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.
-
Jul 13th, 2012, 04:30 AM
#10
Thread Starter
Member
Re: Export Dataset to Excel Attachment
Originally Posted by brin351
I had to do the same thing but ftp the excell file from server to server. I didn't want to install excel on the server so the way I hacked around that was to have a blank excell file setup the way I needed in a folder, did a file.copy, dumped the data in the columns, saved it and ftp it.
I'd have to dig around to find that code but can do if you need it.
That sounds like it could be very helpful. If you wouldn't mind digging out the code, that'd be good! 
-
Jul 13th, 2012, 11:47 PM
#11
Re: Export Dataset to Excel Attachment
I had a quick hunt for it but I think it's on another computer. It's the weekend and am about to go out so I'll get it for you tomorrow.
The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.
-
Jul 16th, 2012, 02:55 AM
#12
Re: Export Dataset to Excel Attachment
Here is the code, I edited it to remove stuff that is irrelevant to you so hopefully it makes sence. The sub expects that your excel file (copied) is ready to go so just pass in your dataReader.
The query (strSQL) expects the excell file has a single sheet with the first row has column names. Because excel determines the dataType from the first few rows of data entered I populated the 2nd row of the excell sheet with appropate data and therefore the 1st row read from the dataReader does an update of this row/data, subsequent rows do inserts.... > long text field could throw errors if the first few rows where dataTyped as short text, so the first row of data in the excel file (second row after column names) had a few hundred chars of text in that field/column.
That's about it, any questions I'm happy to help...
Code:
Sub writeToExcel(ByVal reader As OleDbDataReader)
Dim connStr As String = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & _
Server.MapPath("pathToExcelFile.xls") & ";Extended Properties=""Excel 8.0;HDR=YES"""
Dim strSQL As String
Dim dbConn As New OleDbConnection(connStr)
Try
dbConn.Open()
Dim i As Integer = 0
While reader.Read
If i = 0 Then
'this is first record so update first row in excel with this record ---- Note WHERE clause > First column in excel file is "Photo_No", first record of this column : value is "deleteThis"
strSQL = "UPDATE [sheet1$] SET [Photo_No] = @photo, [Category (refer Catergory Index)] = @category, " & _
" [SellingPrice ($)] = @price, [Qualifier (each, ono)] = @priceAdd, [Ad Heading] = @heading, [Ad Text] = @text " & _
" WHERE [Photo_No] = 'deleteThis'"
Dim cmd2 As New OleDbCommand(strSQL, dbConn)
cmd2.Parameters.AddWithValue("@photo", reader.Item("ID").ToString & "_" & newPhotoName)
cmd2.Parameters.AddWithValue("@category", category)
cmd2.Parameters.AddWithValue("@price", reader.Item("price").ToString)
cmd2.Parameters.AddWithValue("@priceAdd", reader.Item("priceAdd").ToString)
cmd2.Parameters.AddWithValue("@heading", head)
cmd2.Parameters.AddWithValue("@text", txt)
cmd2.ExecuteNonQuery()
Else
strSQL = "INSERT INTO [sheet1$] ([Photo_No], [Category (refer Catergory Index)], [SellingPrice ($)], [Qualifier (each, ono)], [Ad Heading], [Ad Text])" & _
" values (@photo, @category, @price, @priceAdd, @heading, @text) "
Dim cmd As New OleDbCommand(strSQL, dbConn)
cmd.Parameters.AddWithValue("@photo", reader.Item("ID").ToString & "_" & newPhotoName)
cmd.Parameters.AddWithValue("@category", category)
cmd.Parameters.AddWithValue("@price", reader.Item("price").ToString)
cmd.Parameters.AddWithValue("@priceAdd", reader.Item("priceAdd").ToString)
cmd.Parameters.AddWithValue("@heading", head)
cmd.Parameters.AddWithValue("@text", txt)
cmd.ExecuteNonQuery()
End If
i += 1
End While
Catch ex As Exception
End Try
dbConn.Close()
End Sub
Last edited by brin351; Jul 16th, 2012 at 03:05 AM.
The problem with computers is their nature is pure logic. Just once I'd like my computer to do something deluded.
-
Jul 16th, 2012, 04:19 AM
#13
Thread Starter
Member
Re: Export Dataset to Excel Attachment
Thanks Brin351. In the meanwhile, I've gone with dumping the results into a plain text csv file, but if the boss doesn't go for it, I'll be back to try out your code.
Thanks for your help, everyone.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
