我正在try 在VB.NET中以打印预览和直接打印的方式以HTML格式打印DataTable的内容,包括页眉/设置纸张/方向/适合页面.
也许在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页眉
样本数据:
表格表产品
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 |
桌子大小产品
Sizeproduct | Sequence |
---|---|
- | 1 |
S | 2 |
M | 3 |
L | 4 |
XL | 5 |
XXL/2L | 6 |
期望的结果
对于代码产品=A
SAMPLE
调用:1000
CodeProduct | Colour | - | S | M | L | XL | TOTAL |
---|---|---|---|---|---|---|---|
A | 20 | 10 | 35 | ||||
A | Black | 20 | 20 | ||||
A | White | 15 | 10 | 25 |
总和 : 80
对于代码产品=B
SAMPLE
调用:1000
CodeProduct | Colour | S | XL | TOTAL |
---|---|---|---|---|
B | Blue | 20 | 20 | |
B | White | 15 | 10 |
总和 : 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