Konu: Excel alt toplam
Visual Fox Pro
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