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)='A'))
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
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 |
| A |
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
