Email a datatable in table format, not string

[lcfjoertoft]

First I tried getting a SQL query into an array, and then mail it. Made the data incomprehendable. So someone told me to use CSS to build a table format, filling it with the array. A LOT of hard work (since I had to learn css all the way from "what is a css".
Then I was told "Array? why use array when DATATABLES are soooo much easier"

So here we go again. "What is a datatable, and how does it work"

Now finally nailed it and am ready to place my nice and sorted datatable into the body of the mail.

Now "everyone" tells me that the best way to put the datatable into the mail is to read the datatable into a STRING !?! and past it into the mail body!!!

Am I back to sqare one?

I want to move my DataGridView1 from form1 into my mail.
How do I do that?

I thought of converting it to Rich Text Format and pasting it in the email so I went with that.

I started off looking at the DataTable.WriteXml method and did a search on converting xml to Rtf and found this thread that talks about converting a dataset to rtf. http://msdn.microsoft.com/en-us/library/aa537151(v=office.11).aspx . But looked like a little too much rtf to me.

I dealt with wpf a little and remembered converting flow documents that contained tables in them to rtf and being able to open them up with MS Word.

So here you go. DataTableRtfConverter. Converts the DataTable to a Wpf Table and uses a TextRange to save the Table in Rtf format.

You can specify the Column Name fonts and forecolors, the Row fonts and forecolors, whether the table should have grid lines or not, min and max column widths, and the cell padding.

An example use:

Public Class Form1

    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click

        'An example Data Table
        Dim DataTable As New DataTable
        Dim DataRow As DataRow

        DataTable.Columns.Add("ID", GetType(Integer))
        DataTable.Columns.Add("Name", GetType(String))

        DataRow = DataTable.NewRow
        DataRow("ID") = 1
        DataRow("Name") = "One"
        DataTable.Rows.Add(DataRow)

        DataRow = DataTable.NewRow
        DataRow("ID") = 2
        DataRow("Name") = "Two"
        DataTable.Rows.Add(DataRow)

        'Convert this Data Table to Rtf using Wpf 
        'Create an instance of the converter.  You set the fonts and colors
        'you want for the column names and the remaining rows.
        Dim ColumnFont As New Font("Times New Roman", 12, FontStyle.Bold)
        Dim RowFont As New Font("Times New Roman", 10, FontStyle.Regular Or FontStyle.Italic)

        Dim Converter As New DataTableRtfConverter(ColumnFont, Color.Navy, RowFont, Color.Black)

        ColumnFont.Dispose()
        RowFont.Dispose()

        'Get the data table as rtf and set it to the clipboard with the
        'rtf format. 
        'Pass in the data table and options
        'show gridlines, minimum and maximum column widths, and cell padding
        Clipboard.SetText(Converter.ConvertToRtf(DataTable, True, 25, 100, 5), TextDataFormat.Rtf)

        'You can now paste the table into MS Word or your email if it supports
        'rtf.

    End Sub

End Class

That creates the converter and copies the datatable to the clipboard in rtf format. I attached a screen shot of what it looked like when I hit paste in Ms Word. The same was true when pasting it into MS Word Pad and an Outlook email.

The class.

Add a class to your project and name it DataTableRtfConverter and paste the following code.

**You will need to add a reference to PresentationCore and PresentationFramework for it to work.

Imports System.Windows
Imports System.Windows.Documents
Imports System.Windows.Media
Imports System.Globalization

'Don't forget to add a reference to 
'PresentationCore and PresentationFramework

Public Class DataTableRtfConverter

    Private ColumnFont As WpfFont
    Private RowFont As WpfFont

    ''' <summary>
    ''' Converts System.Drawing Fonts and Colors to Wpf Data
    ''' </summary>
    Private Class WpfFont
        Private fpFontFamily As FontFamily
        Private fpForeColor As Brush
        Private fpFontWeight As FontWeight
        Private fpFontSize As Double
        Private fpFontStretch As FontStretch
        Private fpFontStyle As FontStyle

        ReadOnly Property FontFamily() As FontFamily
            Get
                Return fpFontFamily
            End Get
        End Property

        ReadOnly Property FontSize() As Double
            Get
                Return fpFontSize
            End Get
        End Property

        ReadOnly Property FontStretch() As FontStretch
            Get
                Return fpFontStretch
            End Get
        End Property

        ReadOnly Property FontStyle() As FontStyle
            Get
                Return fpFontStyle
            End Get
        End Property

        ReadOnly Property FontWeight() As FontWeight
            Get
                Return fpFontWeight
            End Get
        End Property

        ReadOnly Property ForeColor() As Brush
            Get
                Return fpForeColor
            End Get
        End Property

        Sub New(ByVal font As System.Drawing.Font, ByVal foreColor As System.Drawing.Color)
            'Convert the system.drawing variables into variables that
            'will work in wpf.
            fpFontFamily = New FontFamily(font.FontFamily.Name)
            fpForeColor = New SolidColorBrush(Color.FromArgb(foreColor.A, foreColor.R, foreColor.G, foreColor.B))
            fpFontSize = font.Size * (96 / 72)

            If (font.Style And System.Drawing.FontStyle.Bold) = System.Drawing.FontStyle.Bold Then
                fpFontWeight = FontWeights.Bold
            Else
                fpFontWeight = FontWeights.Regular
            End If

            If (font.Style And System.Drawing.FontStyle.Italic) = System.Drawing.FontStyle.Italic Then
                fpFontStyle = FontStyles.Italic
            Else
                fpFontStyle = FontStyles.Normal
            End If

            fpFontStretch = FontStretches.Normal
        End Sub

    End Class

    Sub New(ByVal columnFont As System.Drawing.Font, ByVal columnForeColor As System.Drawing.Color, ByVal rowFont As System.Drawing.Font, ByVal rowForeColor As System.Drawing.Color)
        'Convert the system drawing variables to wpf variables.
        Me.ColumnFont = New WpfFont(columnFont, columnForeColor)
        Me.RowFont = New WpfFont(rowFont, rowForeColor)
    End Sub

    ''' <summary>
    ''' Returns a DataTable in Rich Text Format.
    ''' </summary>
    Public Function ConvertToRtf(ByVal dataTable As DataTable, ByVal showGridLines As Boolean, ByVal minColumnWidth As Integer, ByVal maxColumnWidth As Integer, ByVal cellPadding As Integer) As String

        'Convert the DataTable to a wpf table with the specified settings
        'and fonts.
        Dim Table As Table = BuildWpfTable(dataTable, showGridLines, minColumnWidth, maxColumnWidth, cellPadding)
        Dim Section As New Section(Table)
        'Added the table to a Section because the TextRange.Save was not
        'working unless the table belonged to another Block or Document.

        Dim TextRange As New TextRange(Table.ContentStart, Table.ContentEnd)
        Dim TextMemoryStream As New IO.MemoryStream

        'Save the Table into the memory stream with the Rtf format.
        TextRange.Save(TextMemoryStream, DataFormats.Rtf)

        'Instead of saving it to a memory stream you could save it to a file
        'stream and attach it to the email.  It will open in word pad.
        'example
        'Dim FileStream As New IO.FileStream("C:\MyRtf.rtf", IO.FileMode.Create)
        'TextRange.Save(FileStream, DataFormats.Rtf)


        'Set the memory stream to the beginning and set up a 
        'stream reader so the rtf can be extracted.
        TextMemoryStream.Position = 0
        Dim StreamReader As New IO.StreamReader(TextMemoryStream)

        Try
            'Return the rtf string.
            Return StreamReader.ReadToEnd
        Finally

            'Clean up
            TextMemoryStream.Dispose()
            StreamReader.Dispose()

            For Each Row As TableRow In Table.RowGroups(0).Rows
                For Each Cell As TableCell In Row.Cells
                    Cell = Nothing
                Next
                Row.Cells.Clear()
                Row = Nothing
            Next
            Table.RowGroups(0).Rows.Clear()
            Table.RowGroups.Clear()
            Table = Nothing
            Section.Blocks.Clear()
            Section = Nothing
        End Try

    End Function

    Private Function BuildWpfTable(ByVal dataTable As DataTable, ByVal showGridLines As Boolean, ByVal minColumnWidth As Integer, ByVal maxColumnWidth As Integer, ByVal cellPadding As Integer) As Table

        Dim Table As New Table
        Dim Column As TableColumn
        Dim Row As TableRow
        Dim Paragraph As Paragraph
        Dim ColumnWidth As Double
        Dim ZeroThickness As New Thickness(0)
        Dim PaddingThickness As New Thickness(cellPadding)

        If showGridLines Then
            Table.BorderThickness = New Thickness(1)
        Else
            Table.BorderThickness = New Thickness(0)
        End If

        Table.CellSpacing = 0

        Table.RowGroups.Add(New TableRowGroup)

        'Add the columns to the new table
        For i = 0 To dataTable.Columns.Count - 1
            Column = New TableColumn

            'compute width and add the cellpadding.

            'if the min and max widths are the same then this
            'is a fixed column width so don't measure the columns
            If minColumnWidth = maxColumnWidth Then
                Column.Width = New System.Windows.GridLength(minColumnWidth + (cellPadding * 2))
            Else
                'calculate the max width for the column
                'and adjust if needed for min and max.
                ColumnWidth = GetColumnWidth(dataTable, i)
                If ColumnWidth < minColumnWidth Then
                    ColumnWidth = minColumnWidth
                ElseIf ColumnWidth > maxColumnWidth Then
                    ColumnWidth = maxColumnWidth
                End If
                Column.Width = New System.Windows.GridLength(ColumnWidth + (cellPadding * 2))
            End If

            Table.Columns.Add(Column)
        Next

        'Add the First Row. The column names
        Row = New TableRow

        For Each DataColum As DataColumn In dataTable.Columns
            Paragraph = New Paragraph(New Run(DataColum.ColumnName))
            Paragraph.FontFamily = ColumnFont.FontFamily
            Paragraph.FontSize = ColumnFont.FontSize
            Paragraph.FontStretch = ColumnFont.FontStretch
            Paragraph.FontStyle = ColumnFont.FontStyle
            Paragraph.FontWeight = ColumnFont.FontWeight
            Paragraph.Foreground = ColumnFont.ForeColor
            Paragraph.Margin = PaddingThickness
            Paragraph.Padding = ZeroThickness
            Row.Cells.Add(New TableCell(Paragraph))
        Next

        Table.RowGroups(0).Rows.Add(Row)

        'Add the Remaining Rows.
        For Each DataRow As DataRow In dataTable.Rows
            Row = New TableRow

            For Each FieldValue As Object In DataRow.ItemArray
                If FieldValue Is Nothing OrElse FieldValue Is DBNull.Value Then
                    Paragraph = New Paragraph(New Run(String.Empty))
                Else
                    Paragraph = New Paragraph(New Run(FieldValue.ToString))
                End If
                Paragraph.FontFamily = RowFont.FontFamily
                Paragraph.FontSize = RowFont.FontSize
                Paragraph.FontStretch = RowFont.FontStretch
                Paragraph.FontStyle = RowFont.FontStyle
                Paragraph.FontWeight = RowFont.FontWeight
                Paragraph.Foreground = RowFont.ForeColor
                Paragraph.Margin = PaddingThickness
                Paragraph.Padding = ZeroThickness
                Row.Cells.Add(New TableCell(Paragraph))
            Next

            Table.RowGroups(0).Rows.Add(Row)
        Next

        Return Table

    End Function

    ''' <summary>
    ''' Returns the longest length in the specified column
    ''' </summary>
    Private Function GetColumnWidth(ByVal dataTable As DataTable, ByVal index As Integer)
        Dim ColumnWidth As Double
        Dim TempWidth As Double

        'Get the measurement of the column name.
        ColumnWidth = MeasureTextWidth(dataTable.Columns(index).ColumnName, ColumnFont)

        'Get the measurement for each remaining row.
        'if its greater than the current width then
        'set it as the current width
        For Each DataRow As DataRow In dataTable.Rows
            If DataRow(index) IsNot Nothing AndAlso DataRow(index) IsNot DBNull.Value Then
                TempWidth = MeasureTextWidth(DataRow(index), RowFont)
            End If

            If TempWidth > ColumnWidth Then
                ColumnWidth = TempWidth
            End If
        Next

        '+2 beyond me.  MeasureText is always comming up around 
        '2 pixels short.  Something to do with the paragraph maybe.
        'Quick fix of adding 2.
        Return ColumnWidth + 2

    End Function

    Private Function MeasureTextWidth(ByVal text As String, ByVal fontData As WpfFont) As Double

        Dim TextMeasurer As New FormattedText(text, _
                                              CultureInfo.CurrentCulture, _
                                              FlowDirection.LeftToRight, _
                                              New Typeface(fontData.FontFamily, fontData.FontStyle, fontData.FontWeight, fontData.FontStretch), _
                                              fontData.FontSize, _
                                              fontData.ForeColor)
        Return TextMeasurer.Width

    End Function

End Class

Only tested on the example Table but should work.