Select Order_id, Cust_ID, Emp_ID, ;
  Order_Date, Order_amt, Order_dsc/100 As Order_dsc,Freight ;
  from _samples+"\data\orders" ;
  into Cursor crsTemp

lcXLSFile = Sys(5)+Curdir()+'myOrders.xls'
Copy To (lcXLSFile) Type Xls


*#include "xlConstants.h"
#include "excel-all.h"

Dimension laArray[3]
laArray[1] = 5 && Order_amt
laArray[2] = 7 && Freight
laArray[3] = Fcount()+1 && Will use later

Use In 'crsTemp'
Use In 'Orders'

oExcel = Createobject("excel.application")
With oExcel
  .Workbooks.Open(lcXLSFile)
  With .ActiveWorkbook.ActiveSheet
    lnRows = .UsedRange.Rows.Count && Get current row count
    lcFirstUnusedColumn = _GetChar(laArray[3]) && Get column in Excel A1 notation
    * Instead of orders order_net field use Excel calculation for net prices
    .Range(lcFirstUnusedColumn+'2:'+;
      lcFirstUnusedColumn+Transform(lnRows)).FormulaR1C1 = ;
      "=RC[-3]*(1-RC[-2])+RC[-1]"
    .Range(lcFirstUnusedColumn+'1').Value = 'Order Net' && Place header
    .Range('E:'+lcFirstUnusedColumn).NumberFormat = "$#,##0.0000" && Format columns
    .Range('F:F').NumberFormat = "0%"

    * Sort : Cust_Id asc, Emp_ID asc, Order_Amt descending
    .UsedRange.Sort(.Range('B2'),,.Range('C2'),,,;
      .Range('E2'),xlDescending,xlGuess,,.F.,xlTopToBottom)

    * Subtotal grouping by customer
    .UsedRange.Subtotal(2, xlSum, @laArray, 1, 0, 1)
    .UsedRange.Columns.Autofit && Autofit columns
  Endwith
  .Visible = .T.
Endwith

* Return A, AA, BC etc noation for nth column
Function _GetChar
  Lparameters tnColumn && Convert tnvalue to Excel alpha notation
  If tnColumn = 0
    Return ""
  Endif
  If tnColumn <= 26
    Return Chr(Asc("A")-1+tnColumn)
  Else
    Return 	_GetChar(Int(Iif(tnColumn % 26 = 0,tnColumn - 1, tnColumn) / 26)) + ;
      _GetChar((tnColumn-1)%26+1)
  Endif