locatorgrid ten excele aktarma

Local lcDataLocation, lcSQL, lcHeaders

lcDataLocation = _samples+'data'

 

Text to lcSQL noshow pretext 15

select first_name,last_name,CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee

endtext

 

lcHeaders = 'First Name of Employee,Last Name of Employee,Notes about Employee'

 

VFP2Excel(m.lcDataLocation, m.lcSQL, 'Employees', m.lcHeaders  )

 

********************************************************************

*** Author: Cetin Basoz

*** Function to transfer data to excel

***

*** There are multiple variations of this function

*** posted to various VFP internet forums that have

*** different parameters such as specifying a range etc.

*** The body of the code is same.

*** CopyFromRecordSet is explicitly used over QueryTables.Add

*** because latter has side effects on portability of generated xls

*** but OTOH ability of editing source is available with the latter.

********************************************************************

Function VFP2Excel

  Lparameters tcDataSource, tcSQL, tcSheetName,tcHeaders

  Local loConn As AdoDB.Connection, ;

    loRS As AdoDB.Recordset,;

    oExcel As Excel.Application

 

  loConn = Createobject("Adodb.connection")

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

  loConn.Open()

  loRS = loConn.Execute(m.tcSQL)

  oExcel = Createobject("Excel.Application")

  With oExcel

    .WorkBooks.Add

    .Visible = .T.

    With .ActiveWorkBook.ActiveSheet

      .Name = m.tcSheetName

      Local Array laHeaders[1]

      For ix=1 To Alines(laHeaders,m.tcHeaders,1,',')

        .Cells(1,m.ix).Value = laHeaders[m.ix]

      Endfor

      .Range('A2').CopyFromRecordSet( loRS )

      .UsedRange.Rows(1).Font.Bold = .T.

      .UsedRange.Columns.AutoFit

      .UsedRange.Rows.AutoFit

    Endwith

  Endwith

  loRS.Close

  loConn.Close

endfunc