Hướng dẫn tạo Multi Group Report tự động trong VB.NET

Hôm trước, mình đã có viết bài hướng dẫn các bạn tạo report tự động tham chiếu trực tiếp từ câu lệnh SQL server, hôm nay, mình tiếp tục hướng dẫn các bạn cách gom nhóm (tạo group) trong Xtra Report của Devexpress.

Các bạn có thể download phiên bản Devexpress 15.2 ở đường link sau:

https://laptrinhvb.net/bai-viet/phan-mem-ung-dung/Bo-cong-cu-DevExpress-15-1-7-(Full)/e15403997c0a026d.html

Đồng thời mình cũng hướng các bạn cách chèn số thứ tự vào đầu câu lệnh truy vấn SQL Server.

Cú Pháp: 

select ROW_NUMBER()OVER(Partition By Country Order By Country DESC) AS [Số thứ tự],
 CustomerID, ContactName, Address, City, Country, PostalCode, Phone, Fax from Customers

- Demo dưới này mình hướng dẫn các bạn chi tiết group nhiều field name khác nhau, có thể linh động tùy biến.

Giao diện Demo:

- Các bạn có thể xem trước video demo ứng dụng của mình bên dưới.

Bây giờ mình bắt đầu code:

- Đầu tiên, mình cần import thư viện vào

Imports System
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports DevExpress
Imports DevExpress.Data
Imports DevExpress.XtraReports.UI
Imports System.Data.SqlClient
Imports DevExpress.XtraPrinting.Drawing
Imports DevExpress.XtraSplashScreen
Imports System.Threading

- Tạo hàm kết nối tới database, và tạo hàm trả về một dataset.

Dim con As New SqlConnection
    Public Sub Taoketnoi()
        Dim str As String = "Data Source=.;Initial Catalog=NORTHWND;Integrated Security=True"
        con.ConnectionString = str
        con.Open()
    End Sub
    Public Sub Dongketnoi()
        con.Close()
    End Sub
    Public Function LayDulieu(query As String) As DataSet
        Taoketnoi()
        Dim dt As New DataSet
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand(query, con)
        da.Fill(dt)
        Dongketnoi()
        Return dt
    End Function

- Viết sự kiện cho nút tạo Report

Private Sub btnCreateReport_Click(sender As Object, e As EventArgs) Handles btnCreateReport.Click
        SplashScreenManager.ShowForm(Me, GetType(loading), True, True, False)
        Dim report As New XtraReport()
        'cấu hình report
        report.PaperKind = System.Drawing.Printing.PaperKind.A4Rotated
        report.Margins = New System.Drawing.Printing.Margins(20, 20, 20, 20)
        'end cấu hình report
        Dim dataSet As DataSet = LayDulieu(txtQuery.Text)
        Dim dataTable As DataTable = dataSet.Tables(0)
        report.DataSource = dataSet
        'report.DataMember = "Customers"

        CreateReportHeader(report, txtTieude.Text, Color.PowderBlue, 32)
        CreateDetailBand(report, dataTable, "", Color.Silver, False)
        If txtFiled1.Text <> "" And txtField2.Text <> "" Then
            CreateGrouping(report, txtField2.Text, txtField2.Text)
            CreateGrouping(report, txtFiled1.Text, txtFiled1.Text)
        ElseIf txtField2.Text <> "" Then
            CreateGrouping(report, txtField2.Text, txtField2.Text)
        ElseIf txtField3.Text <> "" Then
            CreateGrouping(report, txtField3.Text, txtField3.Text)
        ElseIf txtFiled1.Text <> "" Then
            CreateGrouping(report, txtFiled1.Text, txtFiled1.Text)

        End If
        If rdoIsgroup.Text = 0 Then
            SetTextWatermark(report)
        Else
            SetPictureWatermark(report)
        End If
        CreateDetailReportBasedOnSubreport(report)
        report.ShowRibbonPreviewDialog()
        SplashScreenManager.CloseForm(False)
    End Sub

- Viết hàm tạo Report Header (tiêu đề của report)

Private Sub CreateReportHeader(ByVal report As XtraReportBase, ByVal caption As String, ByVal labelColor As Color, ByVal fontSize As Integer)
        'Creating a Report header
        Dim header As New ReportHeaderBand()
        report.Bands.Add(header)
        header.HeightF = 0

        Dim label As New XRLabel()
        header.Controls.Add(label)

        label.ForeColor = Color.Green
        label.Font = New Font("Tahoma", 25, FontStyle.Bold)
        label.Text = caption
        label.TextAlignment = DevExpress.XtraPrinting.TextAlignment.TopCenter
        label.LocationF = New PointF(0, 0)

        Dim rep As XtraReport = report.RootReport
        label.WidthF = rep.PageWidth - rep.Margins.Right - rep.Margins.Left
    End Sub

- Viết hàm chi tiết nhánh data dữ liệu load vào (table bạn load từ datase lên)

Private Sub CreateDetailBand(ByVal report As XtraReportBase, ByVal templateTable As DataTable, ByVal dataMember As String, ByVal backColor As Color, ByVal useStyles As Boolean)
        Dim detail As New DetailBand()
        report.Bands.Add(detail)

        detail.HeightF = 0

        ' Creating Header and Detail Tables
        Dim headerTable As New XRTable()

        report.Bands(BandKind.ReportHeader).Controls.Add(headerTable)
        headerTable.Height = 40
        Dim detailTable As New XRTable()
        detail.Controls.Add(detailTable)

        detailTable.BeginInit()
        headerTable.BeginInit()

        Dim headerRow As New XRTableRow()
        headerTable.Rows.Add(headerRow)


        Dim detailRow As New XRTableRow()
        detailTable.Rows.Add(detailRow)

        Dim colCount As Integer = templateTable.Columns.Count
        Dim rootReport As XtraReport = report.RootReport
        Dim pageWidth As Integer = (rootReport.PageWidth - (rootReport.Margins.Left + rootReport.Margins.Right))
        Dim colWidth As Single = pageWidth  colCount
        'Creating an XRTableCell for each column in the corresponding DataTable
        For i As Integer = 0 To colCount - 1
            Dim headerCell As New XRTableCell()
            headerRow.Cells.Add(headerCell)
            headerCell.WidthF = colWidth
            headerCell.BackColor = Color.Orange
            headerCell.ForeColor = Color.White
            headerCell.Height = 40
            headerCell.Text = templateTable.Columns(i).Caption
            headerCell.TextAlignment = DevExpress.XtraPrinting.TextAlignment.MiddleLeft
            headerCell.Borders = DevExpress.XtraPrinting.BorderSide.All
            headerCell.Font = New Font("Tahoma", 12, FontStyle.Bold, GraphicsUnit.Pixel)
            headerCell.Padding = New DevExpress.XtraPrinting.PaddingInfo(5, 5, 5, 5, 100.0F)

            Dim detailCell As New XRTableCell()
            detailRow.Cells.Add(detailCell)
            detailCell.WidthF = colWidth
            detailCell.Height = 20
            detailCell.Font = New Font("Tahoma", 12, FontStyle.Regular, GraphicsUnit.Pixel)
            detailCell.Padding = New DevExpress.XtraPrinting.PaddingInfo(5, 5, 5, 5, 100.0F)
            Dim actualDM As String = String.Empty
            If dataMember = String.Empty Then
                actualDM = templateTable.Columns(i).Caption
            Else
                actualDM = String.Format("{0}.{1}", dataMember, templateTable.Columns(i).Caption)
            End If
            detailCell.DataBindings.Add("Text", Nothing, actualDM)
            detailCell.Borders = DevExpress.XtraPrinting.BorderSide.Left Or DevExpress.XtraPrinting.BorderSide.Bottom Or DevExpress.XtraPrinting.BorderSide.Right
        Next i

        headerTable.EndInit()
        detailTable.EndInit()

        headerTable.LocationF = New PointF(0, report.Bands(BandKind.ReportHeader).HeightF)
        headerTable.WidthF = pageWidth
        headerTable.Font = New Font(headerTable.Font, FontStyle.Bold)

        detailTable.LocationF = New PointF(0, 0)
        detailTable.WidthF = pageWidth
    End Sub

- Tiếp tục, lập trình cho hàm tạo Group theo điều kiện mình lựa chọn

Private Sub CreateGrouping(ByVal report As XtraReport, fieldname As String, tieude As String)
        'Add a group to the RootReport
        Dim groupband As New GroupHeaderBand()
        report.Bands.Add(groupband)
        groupband.Height = 30
        groupband.WidthF = 40
        groupband.GroupUnion = GroupUnion.WithFirstDetail


        groupband.GroupFields.Add(New GroupField(fieldname))

        Dim label As New XRLabel()
        groupband.Controls.Add(label)

        label.DataBindings.Add("Text", Nothing, fieldname, tieude & ": {0}")
        label.TextAlignment = DevExpress.XtraPrinting.TextAlignment.MiddleLeft
        label.LocationF = New PointF(0, 0)
        label.WidthF = report.PageWidth - report.Margins.Right - report.Margins.Left
        label.Height = 30
        label.BackColor = Color.Gray
        label.ForeColor = Color.White
        label.Font = New Font("Tahoma", 12, FontStyle.Regular, GraphicsUnit.Pixel)
        label.Padding = New DevExpress.XtraPrinting.PaddingInfo(5, 5, 5, 5, 100.0F)
        label.Borders = DevExpress.XtraPrinting.BorderSide.Left Or DevExpress.XtraPrinting.BorderSide.Right Or DevExpress.XtraPrinting.BorderSide.Bottom
    End Sub

- Viết hàm tạo subReport 

Private Sub CreateDetailReportBasedOnSubreport(ByVal report As XtraReport)
        'Create a Subreport
        Dim subreport As New XRSubreport()
        Dim detailBand As DetailBand = TryCast(report.Bands(BandKind.Detail), DetailBand)
        detailBand.Controls.Add(subreport)

        subreport.LocationF = New PointF(0, detailBand.HeightF)
        subreport.WidthF = report.PageWidth - report.Margins.Right - report.Margins.Left
        'Create a detail Report
        Dim detailReport As New XtraReport() With {.DataSource = report.DataSource, .DataMember = "Orders"}
        subreport.ReportSource = detailReport
    End Sub

- Tiếp tục viết 2 hàm tạo Water mask dạng Text và dạng Picture

Sub SetTextWatermark(ps As XtraReport)
        ' Create the text watermark.
        Dim textWatermark As New Watermark()

        ' Set watermark options.
        textWatermark.Text = "HTTP://LAPTRINHVB.NET"
        textWatermark.TextDirection = DirectionMode.ForwardDiagonal
        textWatermark.Font = New Font(textWatermark.Font.FontFamily, 40)
        textWatermark.ForeColor = Color.DodgerBlue
        textWatermark.TextTransparency = 150
        textWatermark.ShowBehind = False
        textWatermark.PageRange = "1,3-5"

        ' Add the watermark to a document.
        ps.Watermark.CopyFrom(textWatermark)
    End Sub

    Public Sub SetPictureWatermark(ps As XtraReport)
        ' Create the picture watermark.
        Dim pictureWatermark As New Watermark()

        ' Set watermark options.
        pictureWatermark.Image = Bitmap.FromFile("logo.png")
        pictureWatermark.ImageAlign = ContentAlignment.MiddleCenter
        pictureWatermark.ImageTiling = False
        pictureWatermark.ImageViewMode = ImageViewMode.Zoom
        pictureWatermark.ImageTransparency = 150
        pictureWatermark.ShowBehind = True
        'pictureWatermark.PageRange = "2,4"

        ' Add the watermark to a document.
        ps.Watermark.CopyFrom(pictureWatermark)
    End Sub

Chúc các bạn thành công. Mọi câu hỏi thắc mắc đến bài viết xin truy cập http://hoidap.laptrinhvb.net để được support.

ĐỪNG QUÊN LIKE AND SHARE NHA CÁC BẠN. 

CÁM ƠN CÁC BẠN ĐÃ THEO DÕI.

LINK DOWNLOAD