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?

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.

I think you would be better off asking your question in the ASP.NET section.

Quote:

---

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.

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.

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

---

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

---

It is always weird to me to see XML written directly into the code like that :)

Gary

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.

Quote:

---

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! :thumb:

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.

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

---

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.