html vb.net printing datatable reporting

How to Print contents of datatable in HTML in printpreview and direct print including headers/Set paper/Orientation/Fit to Page/Grandtotal in VB.NET

I'm trying to to Print contents of datatable in HTML in printpreview and direct print including headers/Set paper/Orientation/Fit to Page in VB.NET.

Maybe it's easy to do in the rdlc report but I can't use it because of a character name property problem that doesn't allow it.

so I took this solution by converting to html or is there any other solution please give me suggestions

I have the code below, but this is still wrong.

is there any other method please guide me

Thanks

Private dt As New DataTable
    Private Function CreateConnection() As OleDbConnection
        Return New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\transposerowtocolumnsqlmsaccessvbnet.accdb;Persist Security Info=False;")
    End Function
    Private Function LoadData() As DataTable
        Dim dt As New DataTable()

        Using con = CreateConnection(), cmd = con.CreateCommand(),
        ta = New OleDbDataAdapter(cmd)
            Dim sql = <sql>
                          TRANSFORM Sum(Tableproduct.Qty) AS SumOfQty
SELECT Tableproduct.Codeproduct AS CodeProduct, Tableproduct.Colour AS Colour, Sum(Tableproduct.Qty) AS Total
FROM Tableproduct INNER JOIN SizeProduct ON Tableproduct.Size = SizeProduct.Size
WHERE (((Tableproduct.Codeproduct)='B'))
GROUP BY Tableproduct.Codeproduct, Tableproduct.Colour
PIVOT SizeProduct.Size;
                      </sql>.Value
            cmd.CommandText = sql
            ta.Fill(dt)
        End Using
        Return dt
    End Function
 Private Function ExportDatatableToHtml(ByVal dt As DataTable) As String
        Dim stringBuilder As New StringBuilder()
        stringBuilder.Append("<html >")
        stringBuilder.Append("<head>")
        stringBuilder.Append("<meta charset='utf-8'>")
        stringBuilder.Append("</head>")
        stringBuilder.Append("<link rel='stylesheet' href='https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css' integrity='sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk' crossorigin='anonymous'>")
        stringBuilder.Append("<script src='https://code.jquery.com/jquery-3.3.1.slim.min.js' integrity='sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo' crossorigin='anonymous'></script>")
        stringBuilder.Append("<script src='https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js' integrity='sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy' crossorigin='anonymous'></script>")
        stringBuilder.Append("<body>")
        stringBuilder.Append("<table class='table table-sm table-hover' style='margin: 20px;'>")
        stringBuilder.Append("<thead>")
        stringBuilder.Append("<tr class='bg-primary' style='color: white; text-align: left;'>")
        For Each column As DataColumn In dt.Columns
            stringBuilder.Append("<th class='border border-secondary'>")
            stringBuilder.Append(column.ColumnName)
            stringBuilder.Append("</th>")
        Next column
        stringBuilder.Append("</tr>")
        stringBuilder.Append("</thead>")
        For Each row As DataRow In dt.Rows
            stringBuilder.Append("<tr>")
            For Each column As DataColumn In dt.Columns
                stringBuilder.Append("<td class='border border-secondary'>")
                stringBuilder.Append(row(column.ColumnName).ToString())
                stringBuilder.Append("</td>")
            Next column
            stringBuilder.Append("</tr>")
        Next row
        stringBuilder.Append("</table>")
        stringBuilder.Append("</body>")
        stringBuilder.Append("</html>")
        Dim html = stringBuilder.ToString()
        Return html
    End Function

    Private Sub BRNCONVERT_Click(sender As Object, e As EventArgs) Handles BRNCONVERT.Click
        Using saveFileDialog As New SaveFileDialog() With {.Filter = "Html files|*.html"}
            If saveFileDialog.ShowDialog() = DialogResult.OK Then
                Dim html As String = ExportDatatableToHtml(LoadData())
                System.IO.File.WriteAllText(saveFileDialog.FileName, html)
            End If
        End Using

    End Sub

Result from code above :

below includes what I want to set :

Document Size : A5
Reduce/Enlarge Document : Fit to Page
Orientation : Landscape
Grandtotal
Title Header
Invono Header

Sample Data :

Table TableProduct

CodeProduct	Colour	Size	Qty
A	White	S	15
A	Black	M	20
A	White	L	10
A		-	20
A		XXL/2L	15
B	Blue	S	20
B	White	XL	15

Table Sizeproduct

Sizeproduct	Sequence
-	1
S	2
M	3
L	4
XL	5
XXL/2L	6

Desired Result

for codeProduct = A

            SAMPLE

Invono : 1000

CodeProduct	Colour	-	S	M	L	XL	TOTAL
A		20				10	35
A	Black			20			20
A	White		15		10		25

                       Grandtotal : 80

for codeProduct = B

            SAMPLE

Invono : 1000

CodeProduct	Colour	S	XL	TOTAL
B	Blue	20		20
B	White		15	10

               Grandtotal : 30

Code For print

Private stringtoPrint as string
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim htmlFile = "test.html"

        Using dt = LoadData("A")
            File.WriteAllText(htmlFile, DataTableToHtml(dt))
        End Using
End Sub
Private Sub ReadFile()
        Dim docName As String = "test.html"
        Dim docPath As String = CType(AppDomain.CurrentDomain.GetData("DataDirectory"), String)
        pd.DocumentName = docName
        Dim stream As New FileStream(docPath + docName, FileMode.Open)
        Try
            Dim reader As New StreamReader(stream)
            Try
                stringToPrint = reader.ReadToEnd()
            Finally
                reader.Dispose()
            End Try
        Finally
            stream.Dispose()
        End Try
    End Sub

 Private Sub Btnprint_Click(sender As Object, e As EventArgs) Handles Btnprint.Click
 Try
            ReadFile()
            Dim ps As New PrinterSettings()
            Dim paperSizes As IEnumerable(Of PaperSize) = ps.PaperSizes.Cast(Of PaperSize)()
            Dim sizeA5 As PaperSize = paperSizes.First(Function(size) size.Kind = PaperKind.A5) ' setting paper size to A4 size
            pd.DefaultPageSettings.PaperSize = sizeA5
            pd.DefaultPageSettings.Landscape = True
            Dim preview As New PrintPreviewDialog()
            preview.Document = pd
            preview.Show()
            'pd.Print()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

  Private Sub pd_PrintPage(ByVal sender As Object, ByVal e As PrintPageEventArgs) Handles pd.PrintPage
        Dim charactersOnPage As Integer = 0
        Dim linesPerPage As Integer = 0

        ' Sets the value of charactersOnPage to the number of characters 
        ' of stringToPrint that will fit within the bounds of the page.
        e.Graphics.MeasureString(stringToPrint, Me.Font, e.MarginBounds.Size,
            StringFormat.GenericTypographic, charactersOnPage, linesPerPage)

        ' Draws the string within the bounds of the page
        e.Graphics.DrawString(stringToPrint, Me.Font, Brushes.Black,
            e.MarginBounds, StringFormat.GenericTypographic)

        ' Remove the portion of the string that has been printed.
        stringToPrint = stringToPrint.Substring(charactersOnPage)

        ' Check to see if more pages are to be printed.
        e.HasMorePages = stringToPrint.Length > 0


    End Sub

Solution

The Crosstab Query

Considering the design of the tables in the question. There is no database relationship between the TableProduct and SizeProduct tables. They have a duplicate field and having that only doesn't mean the two tables are related. Which means, the JOIN part in the query makes no sense at all. You don't need anything from the joined table because you have the pivot field in the TableProduct already. So, the query should be like this:

TRANSFORM Sum(Tableproduct.Qty) AS SumOfQty
SELECT Tableproduct.Codeproduct AS CodeProduct, 
       Tableproduct.Color AS Color, 
       Sum(Tableproduct.Qty) AS Total
FROM Tableproduct
WHERE Tableproduct.Codeproduct = 'A'
GROUP BY Tableproduct.Codeproduct, Tableproduct.Color
PIVOT Tableproduct.Size;

To select the pivot field in ascending order of size:

PIVOT Tableproduct.Size IN ('-', 'S', 'M', 'L', 'XL', 'XXL/2L');

Your query would make sense if the TableProduct contains a foreign key field that keeps the related unique primary key values from the SizeProduct table. Then we can say there's a relationship between the two tables and the JOIN is necessary to get the pivot field from the SizeProduct table.

TRANSFORM Sum(Tableproduct.Qty) AS SumOfQty
SELECT Tableproduct.Codeproduct AS CodeProduct,
       Tableproduct.Color AS Color,
       Sum(Tableproduct.Qty) AS Total
FROM Tableproduct 
INNER JOIN SizeProduct ON Tableproduct.SizeId = SizeProduct.Id
WHERE Tableproduct.Codeproduct = 'A'
GROUP BY Tableproduct.Codeproduct, Tableproduct.Color
PIVOT SizeProduct.Size;

For more, please read:
How to define relationships between tables in an Access database.

DataTable to HTML Table

Now, we should have a method that runs the crosstab query, fill and return a DataTable like this:

Private Function LoadData(code As String) As DataTable
    Dim dt As New DataTable()
    Dim sql = <sql>
    TRANSFORM Sum(Tableproduct.Qty) AS SumOfQty
    SELECT Tableproduct.Codeproduct AS CodeProduct,
           Tableproduct.Color AS Color,
           Sum(Tableproduct.Qty) AS Total
    FROM Tableproduct 
    WHERE Tableproduct.Codeproduct = '<%= code %>'
    GROUP BY Tableproduct.Codeproduct, Tableproduct.Color
    PIVOT Tableproduct.Size IN ('-', 'S', 'M', 'L', 'XL', 'XXL/2L');
    </sql>.Value

    Using con = CreateConnection(),
          cmd = New OleDbCommand(sql, con),
          ta = New OleDbDataAdapter(cmd)
        ta.Fill(dt)
    End Using

    ' Make it the last column.
    dt.Columns("Total").SetOrdinal(dt.Columns.Count - 1)
    Return dt
End Function

Or this version to exclude the empty columns:

Private Function LoadData(code As String) As DataTable
    Using dt As New DataTable()
        Dim sql = <sql>
        TRANSFORM Sum(Tableproduct.Qty) AS SumOfQty
        SELECT Tableproduct.Codeproduct AS CodeProduct,
               Tableproduct.Color AS Color,
               Sum(Tableproduct.Qty) AS Total
        FROM Tableproduct 
        WHERE Tableproduct.Codeproduct = '<%= code %>'
        GROUP BY Tableproduct.Codeproduct, Tableproduct.Color
        PIVOT Tableproduct.Size IN ('-', 'S', 'M', 'L', 'XL', 'XXL/2L');
        </sql>.Value

        Using con = CreateConnection(),
              cmd = New OleDbCommand(sql, con),
              ta = New OleDbDataAdapter(cmd)
            ta.Fill(dt)
        End Using

        dt.Columns("Total").SetOrdinal(dt.Columns.Count - 1)

        Dim cols = dt.Columns.Cast(Of DataColumn).
        Where(Function(col) CInt(dt.Compute($"COUNT(`{col.ColumnName}`)", $"`{col.ColumnName}` IS NOT NULL")) > 0).
        Select(Function(col) col.ColumnName).ToArray()

        Return dt.DefaultView.ToTable(False, cols)
    End Using
End Function

A method to create the HTML output including the grand total value.

Private Function DataTableToHtml(dt As DataTable) As String
    Dim Invono = "ABC123"
    Dim html =
        <html>
            <head>
                <meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
                <style>
                table.tb { border-collapse: collapse; width: 100%; }
                .tb th, .tb td { padding: 5px; border: solid 1px; text-align: center; }
                .tb th { background-color: lightblue;}</style>
            </head>
            <body>
                <h2 style="margin-bottom: 0.3em">Some Title</h2>
                <p>
                    <b>Invono: </b><%= Invono %><br/>
                    <b>More Info:</b> Whatever...
                </p>
                <table class="tb">
                    <thead>
                        <tr>
                            <%= dt.Columns.Cast(Of DataColumn).
                                    Select(Function(col) <th><%= col.ColumnName %></th>) %>
                        </tr>
                    </thead>
                    <tbody>
                        <%= dt.AsEnumerable().Select(
                            Function(row)
                                Return _
                                <tr>
                                    <%=
                                        row.ItemArray.OfType(Of Object).Select(
                                    Function(cell) <td><%= cell.ToString() %></td>)
                                    %>
                                </tr>
                            End Function) %>
                        <tr>
                            <td style="border: 0px; text-align: right;" colspan=<%= dt.Columns.Count - 1 %>><strong>Grand Total</strong></td>
                            <td><strong><%= dt.Compute("SUM(Total)", Nothing) %></strong></td>
                        </tr>
                    </tbody>
                </table>
            </body>
        </html>

    Return html.ToString()
End Function

Implement like this:

Private Sub SomeButton_Click(sender As Object, e As EventArgs) Handles SomeButton.Click
    Dim htmlFile = "..."

    Using dt = LoadData("A")
        File.WriteAllText(htmlFile, DataTableToHtml(dt))
    End Using
End Sub

You didn't show the printing part. However, the html table here will fill the width of the page bounds regardless of which page size, margins, and orientation you specify in the print dialog.