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.
Hoặc có thể xem video demo ứng dụng bên dưới.
Bây Giờ chúng ta bắt đầu code từng bước để tạo ra ứng dụng nhé.
Đầu tiên các bạn Import những thư viện này 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
Tiếp theo 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 mọi người thành công với thủ thuật trên.Theo LapTrinhVB.Net
0 Comments
Đăng nhận xét