Apa itu Excel Pivot?
MS Excel menawarkan kelebihan laporan yang fleksibel dalam tabel Pivot. Postingan ini menunjukkan sebuah prosedur umum dapat ditulis dalam Visual Basic untuk menghasilkan laporan berupa pivot tabel Excel menggunakan COM. Contoh data ini bersumber dari database SQL Server 2000 menggunakan ADO.
Prosedur ini digunakan untuk komponen reusable untuk mengisi syarat-syarat laporan Excel dalam form
VB
, yang memberikan fleksibilitas yang besar dalam pengelompokan dan meringkas data dalam segala bidang laporan.Bagaimana Membuat Laporan Excel Pivot dengan VB
Pertama, saya menulis source code untuk prosedur umum.* Ini contoh data dari .db atau dapat data dari sumber apapun yang pelaporan yang harus dilakukan. Data dalam potongan ini berupa bentuk ADODB recordset yang terputus. Query yang digunakan dalam menghasilkan suatu data yang tidak perlu menggunakan grup dengan klausa tertentu, singsingan, atau operator kubus karena pengelompokan dan meringkas akan dijaga oleh pivot tabel itu sendiri.
* form dalam recordset, akan digunakan sebagai pengelompokan Tingkatan halaman, pengelompokan tingkat Row, dan Data.
Sekarang saya akan menunjukkan kepada kamu, bagaimana prosedur tersebut dapat dipanggil, juga melampirkan contoh laporan Excel yang dihasilkannya.
Contoh kode ini membutuhkan dua komponen:
* MICROSOFT ACTIVEX DATA OBJECTS LIBRARY(ANY VERSION ABOVE 2.0)
* MICROSOFT EXCEL 9.0 OBJECT LIBRARY
Public Sub GenerateReport(prmTemp As ADODB.Recordset, _
prmPage As String, prmCol As String, _
prmRow As String, prmData As String, _
prmFile As String)
On Error GoTo Err_GenerateReport
'Variable awal
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheet1 As Excel.Worksheet
Dim rstemp As ADODB.Recordset
Dim intX As Integer
Dim intY As Integer
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Pivot"
Set rstemp = prmTemp
'Mengirim Recorset ke ms Excel
For intY = 0 To rstemp.Fields.Count - 1
xlSheet.Cells(intX + 1, intY + 1).Value = _
rstemp.Fields(intY).Name
Next intY
intX = intX + 1
While Not rstemp.EOF
For intY = 0 To rstemp.Fields.Count - 1
xlSheet.Cells(intX + 1, intY + 1).Value = _
rstemp.Fields(intY).Value
Next intY
rstemp.MoveNext
intX = intX + 1
Wend
'Melakukan pembuangan data pivot
'Menambahkan lembar kerja ke pivot excel
Set xlSheet1 = xlBook.Worksheets.Add
xlSheet1.Name = "Report"
'Membuat Pivot Table-nya
xlBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C1:R" & rstemp.RecordCount + 1 _
& "C" & rstemp.Fields.Count).CreatePivotTable _
TableDestination:=xlSheet1.Range("A9"), _
TableName:="PivotTable1"
xlSheet1.PivotTables("PivotTable1").SmallGrid = False
'Mensetting level halaman
If Len(prmPage) > 0 Then
For intX = 1 To Len(prmPage)
With xlSheet1.PivotTables("PivotTable1").PivotFields( _
rstemp.Fields(CInt(Mid$(prmPage, intX, 1))).Name)
.Orientation = xlPageField
.Position = intX
End With
Next intX
End If
'Mensetting Col Levelnya
If Len(prmCol) > 0 Then
For intX = 1 To Len(prmCol)
With xlSheet1.PivotTables("PivotTable1").PivotFields( _
rstemp.Fields(CInt(Mid$(prmCol, intX, 1))).Name)
.Orientation = xlColumnField
.Position = intX
End With
Next intX
End If
'Mensetting bidang row didalam bidang bertingkat
If Len(prmRow) > 0 Then
For intX = 1 To Len(prmRow)
With xlSheet1.PivotTables("PivotTable1").PivotFields( _
rstemp.Fields(CInt(Mid$(prmRow, intX, 1))).Name)
.Orientation = xlRowField
.Position = intX
End With
Next intX
End If
'Mensetting data fields
If Len(prmData) > 0 Then
For intX = 1 To Len(prmData)
With xlSheet1.PivotTables("PivotTable1").PivotFields( _
rstemp.Fields(CInt(Mid$(prmData, intX, 1))).Name)
.Orientation = xlDataField
.Position = 1
End With
Next intX
End If
'Sembunyikan Pivot command bar
xlApp.CommandBars("PivotTable").Visible = False
xlSheet1.Cells.EntireColumn.AutoFit
xlSheet1.Range("A1").Select
xlApp.DisplayAlerts = False
'menghapus sheet data
xlSheet.Delete
xlApp.DisplayAlerts = True
xlSheet1.Range("A1").Select
'Menyimpan lembaran Excel
xlBook.SaveAs prmFile
xlApp.Visible = True
Exit_GenerateReport:
'Mengclose lembar sheet ms excel
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlSheet1 = Nothing
Set rstemp = Nothing
Exit Sub
Err_GenerateReport:
xlBook.Close
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlSheet1 = Nothing
Set rstemp = Nothing
Err.Raise vbObjectError + 1500, "modReport.GenerateReport", _
Err.Description
End Sub
Baca selengkapnya dan dapatkan contoh programnya di Sample Code Laporan Pivot
