Excel Automation... Excel Filtreleme Örneği.

*!*    Author: Cetin Basoz

*** Constant Group: XlAutoFilterOperator

 

#Define xlAnd                                             1

#Define xlBottom10Items                                   4

#Define xlBottom10Percent                                 6

#Define xlOr                                              2

#Define xlTop10Items                                      3

#Define xlTop10Percent                                    5

 

Local oExcel

oExcel = Createobject("Excel.Application")

With oExcel

  .WorkBooks.Add

  .Visible = .T.

  With .ActiveWorkBook.ActiveSheet

    VFP2Excel(_samples+'data\testdata.dbc','select * from customer',.Range('A1'))

 

    * Country alan indexini al

    Use (_samples+'data\customer') In 0

    Afields(aFieldList,'customer')

    Use In 'customer'

    lnFieldIndex = Ascan(aFieldList,'country',1,-1,1,1+2+4+8)

 

    * Country = 'UK' or Country < 'Germany' filtrele

    .Range('A1').AutoFilter(m.lnFieldIndex,"UK",xlOr,"<Germany")

 

  Endwith

Endwith

 

Function VFP2Excel

  Lparameters tcDataSource, tcSQL, toRange

  Local loConn As AdoDB.Connection, ;

    loRS As AdoDB.Recordset,;

    ix

  loConn = Createobject("Adodb.connection")

  loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource

  loConn.Open()

  loRS = loConn.Execute(m.tcSQL)

 

  For ix=1 To loRS.Fields.Count

    toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)

    toRange.Offset(0,m.ix-1).Font.Bold = .T.

  Endfor

  toRange.Offset(1,0).CopyFromRecordSet( loRS )

  loRS.Close

  loConn.Close

Endfunc