Excel alt toplam

Select emp_id,First_name,Last_name From employee Into Cursor myEmployee

Select Cust_id, o.Order_id, Product_Id, Unit_price, Quantity ;

  from orders o inner Join orditems oi On o.Order_id = oi.Order_id ;

  order By Cust_id, o.Order_id ;

  where Cust_id = 'ALFKI' ;

  into Cursor myOrders

 

Dimension laSubtotal[2]

laSubtotal[1] = 4 && Unit_price column

laSubtotal[2] = 5 && Quantity colum

 

#include "xlConstants.h"

oExcel = Createobject("excel.application")

With oExcel

  .Workbooks.Add

  .Visible = .T.

  * Place employee data at top and name the range as 'Employees'

  PasteAtLocation(oExcel, 'myEmployee', 'A1', 'Employees')

  * Place orders data 2 rows below Employee data

  * and name the range as 'Orders'

  PasteAtLocation(oExcel, 'myOrders',GetRangeAfterSpecifiedRange(oExcel,'Employees'),'Orders')

 

  *!* Subtotal grouping by customer then by order

  With .ActiveWorkBook.ActiveSheet.Range('Orders')

     .Subtotal(1, xlSum, @laSubtotal)

     .Subtotal(2, xlSum, @laSubtotal,.f.,.f.,.f.)

  EndWith

  .ActiveWorkBook.ActiveSheet.Outline.ShowLevels(4)

Endwith

 

Function PasteAtLocation && Paste cursor content at given range and name the range

  Lparameters toExcel, tcAlias, tcRange, tcRangeName

  Local lcTemp,ix,lcHeaders

  lcTemp = Sys(2015)+'.tmp'

  Select (m.tcAlias)

  Copy To (m.lcTemp) Type Delimited With "" With Tab

  lcHeaders=''

  For ix=1 To Fcount()

    lcHeaders = m.lcHeaders +  Iif(Empty(m.lcHeaders),'',Chr(9)) + Field(m.ix)

  Endfor

  _Cliptext = m.lcHeaders + Chr(13) + Chr(10) + Filetostr(m.lcTemp)

  Erase (lcTemp)

  toExcel.ActiveWorkBook.ActiveSheet.Range(m.tcRange).PasteSpecial()

  toExcel.Selection.Name = m.tcRangeName

  toExcel.Selection.Rows(1).Orientation = 90

  toExcel.Selection.Columns.Autofit

  toExcel.Range('A1').Activate

Endfunc

 

Function GetRangeAfterSpecifiedRange && Get available cell after a named range - skip 2 rows

  Lparameters toExcel, tcRangeName

  Local loRange, lcRange

  With toExcel.ActiveWorkBook.ActiveSheet

    loRange = .Range(m.tcRangeName)

    lcRange = loRange.Offset(loRange.Rows.Count + 2,0).Resize(1,1).Address()

  Endwith

  Return lcRange

endfunc