Quote of the Day

Tuesday, December 06, 2005

VFP Cross Tab Query vs. Excel Pivot Table

Today I learned how to automate Excel from VFP to create pivot tables. This totally rocks and is a much more powerful option than trying to output VFP cross tab data (created by the cross tab wizard or foundation classes) to FRX reports.

I was able to show my client how to easily create charts from the same pivot table in a variety of ways.

A great resource for getting started in doing this is the book Microsoft Automation with Visual Foxpro. I used a combination of the examples in the book, recording Excel macros, etc.

The reason I needed a good cross tab solution was that a long time client of mine had used Foxfire for many years. However, I just recently upgraded them to VFP9 and SQL Server and was evaluating the effort required to move to the next version of Foxfire which is still in beta. Needless to say, I was not having much luck. So, long story short, they needed the ability to run several reports from their new app ASAP. Many of these were cross-tabs which Foxfire did a pretty good job of making easy (but not an option for now). I wanted to keep it just as simple and was looking for a more dynamic alternative to Foxpro reports (FRX) to handle the output.

My co-worker Andy Needham suggested I take a look automating Excel. Great idea! In a single day I had 5 cross-tabs created in Excel and the client loves it!

Here is some sample code from the class I created: (Note: VFP cursor already loaded in Excel using COPY TO TYPE XL5 before this method is called)


LOCAL llReturn, ;
loExcel, ;
loBook, ;
loPivot, ;
loSourceData, ;
loDestination, ;
lnLastLine

llReturn = .T.

lobook = GETOBJECT(This.cExcelfile)
loExcel = lobook.Application
loexcel.Visible = .T.
lobook.Windows[1].Activate()
loBook.Sheets[1].Range("A1").Select
loExcel.ActiveCell.FormulaR1C1 = "Customer Name"
loBook.Sheets[1].Range("A2").Select
lobook.Sheets[1].Range("A1:C"+TRANSFORM(this.nlastline)).columns.Autofit()
loSourceData = lobook.Sheets[1].Range("A1:C"+TRANSFORM(this.nlastline))
loBook.Worksheets.Add(,loBook.Sheets[1])
loDestination = loBook.Sheets[2].Range("A1")
loPivotTable = lobook.Sheets[1].PivotTableWizard(1,loSourceData,loDestination,This.cReporttitle,.T.,.T.)

WITH loPivotTable
.AddFields("Customer Name","order_month")
.PivotFields(this.ccountcolumn).Orientation = 4
.PivotFields("Order_Month").NumberFormat = "mmmm-yyyy"
.NullString = ""
.PageFieldOrder = 2
.PrintTitles = .F.
.RepeatItemsOnEachPrintedPage = .F.
.PrintTitles = .T.
.RepeatItemsOnEachPrintedPage = .T.
.PivotSelect("", 0)
.Format(16)
ENDWITH

WITH loBook.Sheets[2].Cells
.Select
.Range("A6").Activate
ENDWITH

loExcel.Selection.RowHeight = 16.5

WITH loBook.Sheets[2].PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$A"
.LeftHeader = "&D&T"
.CenterHeader = This.cReporttitle
.RightHeader = "Page &P"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = loExcel.InchesToPoints(0.25)
.RightMargin = loExcel.InchesToPoints(0.25)
.TopMargin = loExcel.InchesToPoints(.25)
.BottomMargin = loExcel.InchesToPoints(.25)
.HeaderMargin = loExcel.InchesToPoints(0.25)
.FooterMargin = loExcel.InchesToPoints(0.25)
.PrintHeadings = .F.
.PrintGridlines = .F.
.PrintComments = -4142
.PrintQuality = 600
.CenterHorizontally = .F.
.CenterVertically = .F.
.Orientation = 2
.Draft = .F.
.PaperSize = 1
.FirstPageNumber = -4105
.Order = 2
.BlackAndWhite = .F.
.Zoom = .F.
.FitToPagesWide = 1
.FitToPagesTall = .F.

ENDWITH

loBook.Sheets[2].PrintPreview

loPivotTable = null
loSourceData = null
loDestination = null
loExcel = null
loBook = null

RETURN llReturn



A couple things to note: the structure of all these particular cross tabs is the same except for the count column (cu_name, order_month, my_count), so I set that to the cCountColumn property before calling this execute method. nLastLine is set from the RECCOUNT() of my result set. I change the header column of cu_name to Customer Name to make it look nicer.

No comments: