如何在 printpreview 和直接打印中以 HTML 打印数据表的内容，包括页眉/设置纸张/方向/适合页面/总计 VB.NET

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

提问人：siwa86 提问时间：10/27/2023 最后编辑：siwa86 更新时间：10/31/2023 访问量：122

问：

我正在尝试在 printpreview 和直接打印中打印 HTML 中数据表的内容，包括标题/设置纸张/方向/适合 VB.NET 页面。

也许在 rdlc 报告中很容易做到，但由于不允许它的字符名称属性问题，我无法使用它。

所以我通过转换为html来采用此解决方案，或者是否有任何其他解决方案，请给我建议

我有下面的代码，但这仍然是错误的。

有没有其他方法请指导我

谢谢

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

以上代码的结果：

下面包括我要设置的内容：

文件大小： A5
缩小/放大文档：适合页面
朝向：横向
总计
标题标题
Invono 标头

示例数据：

表 TableProduct

代码产品	颜色	大小	数量
一个	白	S	15
一个	黑	M	20
一个	白	L	10
一个		-	20
一个		超大/2升	15
B	蓝	S	20
B	白	XL系列	15

台面产品

尺寸产品	序列
-	1
S	2
M	3
L	4
XL系列	5
超大/2升	6

期望的结果

对于代码Product = A

            SAMPLE

Invono ： 1000

代码产品	颜色	-	S	M	L	XL系列	总
一个		20				10	35
一个	黑			20			20
一个	白		15		10		25

                       Grandtotal : 80

对于代码Product = B

            SAMPLE

Invono ： 1000

代码产品	颜色	S	XL系列	总
B	蓝	20		20
B	白		15	10

               Grandtotal : 30

打印代码

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

HTML vb.net 打印数据表报告

答：

1赞 dr.null 10/29/2023 #1

交叉表查询

考虑问题中表格的设计。和表之间没有数据库关系。它们有一个重复的字段，只有该字段并不意味着这两个表是相关的。这意味着，查询中的部分根本没有意义。您不需要联接表中的任何内容，因为您已经拥有了透视表字段。因此，查询应如下所示：TableProductSizeProductJOINTableProduct

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;

要按大小升序选择透视字段，请执行以下操作：

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

如果包含一个外键字段，该字段保留了表中相关的唯一主键值，则查询将有意义。然后我们可以说两个表之间存在关系，并且从表中获取数据透视字段是必要的。TableProductSizeProductJOINSizeProduct

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;

有关详细信息，请阅读：
如何定义 Access 数据库中表之间的关系。

DataTable 到 HTML 表格

现在，我们应该有一个运行交叉表查询的方法，填写并返回如下：DataTable

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

或者这个版本来排除空列：

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

用于创建包含总计值的 HTML 输出的方法。

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

像这样实现：

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

您没有显示打印部分。但是，无论您在打印对话框中指定哪种页面大小、边距和方向，此处的 html 表都将填充页面边界的宽度。

0赞 siwa86 10/29/2023

谢谢你的完美回答。它应该从HTML表中得出你的第一个顺序大小。我尝试进行printpreview，但printpreview不是作为html表格读取，而是填写我的代码出了什么问题，并且我还更新了帖子中的代码，请从您那里获得指导。Example for codeproduct A : Codeproduct,Color,-,S,M,XL,XXL/2L,TOTALCode For print

0赞 dr.null 10/30/2023

@siwa86 不，这不是针对 .这是为了生成 HTML 内容，使用默认浏览器（即）运行文件，或者预览应用中的 or 控件中的内容。然后您可以使用它将文档/内容发送到打印机。当您要求 HTML 内容时，没有人会期望使用 to 打印。否则，您将拥有这样的解决方案。PrintDocumentProcess.Start(htmlFilePathWebBrowserWebView2PrintDocument

0赞 dr.null 10/30/2023

请参阅如何向 Windows 窗体应用程序添加 Web 浏览器功能和 WinForms 应用中的 WebView2 入门。

0赞 siwa86 10/30/2023

感谢您的回复。为此，如果我从数据表中安排可以应用可能性的顺序，并且对于 html 中的结果行，我想将位置设置为中间中心，我该怎么做，请指导我。为了打印和预览，我使用了解决方案PIVOT Tableproduct.Size IN ('-', 'S', 'M', 'L', 'XL', 'XXL/2L');WebBrowser

0赞 dr.null 10/30/2023

@siwa86 什么可能性！？它将按照数组的相同顺序对大小列进行排序。改变你需要的位置。

上一个：我在 VB.NET msaccess中插入我的listview项时遇到问题

下一个：命名空间不能直接包含字段或方法等成员，并且 T4 运行时文本模板中未定义“ReportTemplate”vb.net

如何在 printpreview 和直接打印中以 HTML 打印数据表的内容，包括页眉/设置纸张/方向/适合页面/总计 VB.NET

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

评论

评论