>I have managed to create a class that will allow me to instatiate Excel, add workbooks and sheet, populate the sheets, and format the data. Now I have been asked to generate a pivot table out of a VFP app. Is there a kind soul out there who would be willing to share Pivot Table code with me?
>
>Thank you.
>
>Todd Cottengim
>Alpine Land Information Services
>tcottengim@alpine-lis.com

* Pivottable sampling for VFP3-5 - Excel97
* Current wizard doesn't work for Excel97
* VFP6 users probably don't need this
* This is a simple sample and dummy with regard to data choosen for pivot table
* Pivot tables are kind of a crosstab tables
* Data choosen is testdata!orditems
* First a cursor is constructed having order_date, prod_name, employee_name, customer.company and quantity
* Cursor is copied to an XL5 file - faster then using ODBC from Excel or writing directly to Excel with datatoclip()
* (Except not sending data at all and using ADO-ODBC at Excel side for large datasets)
* XLS is opened
* employee set as page field
* Product set as column field
* Company and Dates are set as row field to demonstrate multipl fields setting
* It could be send as an array too - see commented part
* When you remove dates you get sum() grouped by company

Set safety off
Set century on
#Define DataPath _samples+"\data\"

Select b.emp_id ,a.company, b.order_date, d.prod_name, c.quantity  ;
  FROM  (DataPath+"customer") a;
  INNER JOIN (DataPath+"orders") b;
  INNER JOIN (DataPath+"orditems") c;
  INNER JOIN (DataPath+"products") d;
  ON  d.product_id = c.product_id ;
  ON  c.order_id = b.order_id ;
  ON  b.cust_id = a.cust_id;
  order by 1,2,3 ;
  into cursor myCursor ;
  nofilter

Select a.first_name + a.last_name as Employee, ;
  b.company, b.prod_name, b.quantity, b.order_date as "dates" ;
  from (DataPath+"employee") a;
  INNER join  myCursor b;
  on a.emp_id = b.emp_id ;
  into cursor pivotdata

Copy to pivotdata type xl5	&& Copied to xls file
lcRows = ltrim(str(reccount()+1)) && Including header line
lcCols = ltrim(str(fcount()))
* Pivot sample
oExcel = createobject("Excel.application")
#Define xlDatabase 1

#Define xlRowField 1
#Define xlColumnField 2
#Define xlPageField 3
#Define xlDataField 4
#define xlRangeAutoFormatColor2  8

With oExcel
  .workbooks.open(sys(5)+curdir()+"pivotdata.xls") && Open saved xls
  With .ActiveWorkBook
    .Sheets("Pivotdata").activate
    .ActiveSheet.PivotTableWizard(xlDatabase, ;
      "pivotdata!R1C1:R"+lcRows+"C"+lcCols,"", "PivotTable1") && Wizard sets Range and name
    With .ActiveSheet.PivotTables("PivotTable1")
* Add rowfields with an array
*!*	      Dimension flist[2]
*!*	      flist[1]="company"
*!*	      flist[2]="dates"
*!*	      .AddFields( @flist, "prod_name", "employee" ) && Fields added
* Add rowfields w/o an array
      .AddFields( "company", "prod_name", "employee" ) && Fields added
      .PivotFields("dates").Orientation = xlRowField   && Dates added as second row field
* Add rowfields w/o an array
      .PivotFields("quantity").Orientation = xlDataField && Quantity set as data field
    Endwith
    .Activesheet.Range("A1").AutoFormat(xlRangeAutoFormatColor2)
  Endwith
  .visible = .t.
Endwith

Cetin