We can create an Excel object in VFP and use all the methods and properties of Excel class to manipulate Excel file.
In the main program, create an ExcelPlus object:
SET PROCEDURE TO ExcelPlus
oExcel = CREATEOBJECT("ExcelPlus")
oExcel.StartExcel
Then, include file ExcelPlus.prg in the project. ExcelPlus.prg defines a class, ExcelPlus, which is an inheritance of class Excel.Application in VBA, so class ExcelPlus can access all the methods and properties of class Excel.Application in VBA. New customized methods can also be easily created in ExcelPlus.prg.
This is part of ExcelPlus.prg:
DEFINE CLASS ExcelPlus AS SESSION
#DEFINE OKAY 0
#DEFINE EXCELSTART 1
#DEFINE EXCELSTOP 2
#DEFINE NULLDATA 3
#DEFINE UNKNOWNALIGNMENT 4
#DEFINE UNKNOWNORIENTATION 5
#DEFINE BADPAGEMARGIN 6
#DEFINE ITEMNOTFOUND 7
#DEFINE COLORNOTDEFINED 8
#DEFINE TOOFARRIGHT 1001
#DEFINE TOOFARLEFT 1002
#DEFINE TOOFARUP 1003
#DEFINE TOOFARDOWN 1004
#DEFINE COLOR_BLACK 1
#DEFINE COLOR_DARKRED 9
PROTECTED AddressLo, AddressHi, MaxCol, MaxRow, oExcel, oWorkbook, DATASESSION
HIDDEN CurrentColLo, CurrentColHi
* exposed properties
CurrentCell = [A1]
CurrentCol = [A]
CurrentRow = 1
CurrentSheet = [Sheet1]
ExcelVisible = .F.
NewSheets = 1
ErrorCode = 0
ErrorMsg = [Okay]
* protected properties
AddressLo = [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
AddressHi = [ ABCDEFGHI]
MaxCol = [IV]
MaxRow = 65536
oExcel = .NULL.
oWorkbook = .NULL.
DATASESSION = 1
* hidden properties
CurrentColHi = 1
CurrentColLo = 1
**********************************************
PROCEDURE ErrorStatus(tnErrornumber)
DO CASE
* informational messages
CASE tnErrornumber = OKAY
THIS.ErrorMsg = [Okay]
CASE tnErrornumber = EXCELSTART
THIS.ErrorMsg = [Excel object instanciated.]
CASE tnErrornumber = EXCELSTOP
THIS.ErrorMsg = [Excel object destroyed.]
CASE tnErrornumber = NULLDATA
THIS.ErrorMsg = [Excel returned a NULL. Converted to space.]
CASE tnErrornumber = UNKNOWNALIGNMENT
THIS.ErrorMsg = [Unknown cell alignment. No cell alignment set.]
CASE tnErrornumber = UNKNOWNORIENTATION
THIS.ErrorMsg = [Unknown page orientation. No orientation set.]
CASE tnErrornumber = BADPAGEMARGIN
THIS.ErrorMsg = [Page margin less than 0. No margins set.]
CASE tnErrornumber = ITEMNOTFOUND
THIS.ErrorMsg = [Item not found prior to set limit.]
CASE tnErrornumber = COLORNOTDEFINED
THIS.ErrorMsg = [Color not defined in object.]
* error messages
CASE tnErrornumber = TOOFARRIGHT
THIS.ErrorMsg = [Attempt to go right of column ] + THIS.MaxCol + [.]
CASE tnErrornumber = TOOFARLEFT
THIS.ErrorMsg = [Attempt to go left of column A.]
CASE tnErrornumber = TOOFARUP
THIS.ErrorMsg = [Attempt to go above row 1.]
CASE tnErrornumber = TOOFARDOWN
THIS.ErrorMsg = [Attempt to go below row ] + ALLTRIM(STR(THIS.MaxRow)) + [.]
OTHERWISE
THIS.ErrorMsg = [Unknown]
ENDCASE
THIS.ErrorCode = tnErrornumber
* give the programmer a heads-up.
IF tnErrornumber > 1000
WAIT WINDOW THIS.ErrorMsg
ENDIF
ENDPROC
**********************************************
PROCEDURE StartExcel
THIS.oExcel = CREATEOBJECT("Excel.Application")
THIS.ErrorStatus(EXCELSTART)
ENDPROC
**********************************************
PROCEDURE StopExcel
THIS.oExcel.QUIT()
THIS.oExcel = .NULL.
THIS.ErrorStatus(EXCELSTOP)
ENDPROC
**********************************************
PROCEDURE OpenSpreadSheet(tcPathName)
THIS.oExcel.Workbooks.OPEN(tcPathName,.F.)
THIS.oWorkbook = THIS.oExcel.ActiveWorkbook
ENDPROC
**********************************************
PROCEDURE SaveSpreadSheet(tcFileName,tcPassWord)
IF PARAMETERS() = 1
tcPassWord = []
ENDIF
This.oExcel.DisplayAlerts = .F.
DO CASE
CASE VAL(This.oExcel.Version) > 11 AND EMPTY(tcPassWord)
THIS.oWorkbook.SaveAs(tcFileName+[.xls], 56)
CASE VAL(This.oExcel.Version) > 11
THIS.oWorkbook.SaveAs(tcFileName+[.xls], 56, tcPassWord)
CASE EMPTY(tcPassWord)
THIS.oWorkbook.SaveAs(tcFileName+[.xls])
OTHERWISE
THIS.oWorkbook.SaveAs(tcFileName+[.xls], tcPassWord)
ENDCASE
This.oExcel.DisplayAlerts = .T.
RETURN
ENDPROC
**********************************************
PROCEDURE CloseSpreadSheet
This.oExcel.DisplayAlerts = .F.
This.oWorkbook.Close() && Unsaved changes will be discarded
This.oExcel.DisplayAlerts = .T.
*!* This.oWorkbook.Close
RETURN
ENDPROC
**********************************************
PROCEDURE NewWorkBook
WITH THIS.oExcel
.SheetsInNewWorkbook = THIS.NewSheets
THIS.oWorkbook = .Workbooks.ADD()
ENDWITH
ENDPROC
**********************************************
PROTECTED PROCEDURE ExcelVisible_assign
* automatically sets the visible
* property of excel
LPARAMETERS tlView
THIS.ExcelVisible = tlView
THIS.oExcel.VISIBLE = THIS.ExcelVisible
ENDPROC
**********************************************
FUNCTION GoToCell(tcCell)
LOCAL lcCell
lcCell = UPPER(tcCell)
* check to see if we passed the limits
* these limits are for hand entered
* addresses programmatic addresses
* are checked with the move methods
IF THIS.Limits(lcCell)
* navigate to a particular cell,
* but if already there do nothing
THIS.oExcel.RANGE(lcCell).SELECT
IF THIS.CurrentCell # lcCell
THIS.CurrentCell = lcCell
ENDIF
ENDIF
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
**********************************************
FUNCTION GoToCol(tcCol)
THIS.GoToCell(tcCol + ALLTRIM(STR(THIS.CurrentRow)))
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
**********************************************
FUNCTION GoToRow(tnRow)
THIS.GoToCell(THIS.CurrentCol + ALLTRIM(STR(tnRow)))
* return error code established in
* the limits check
RETURN THIS.ErrorCode
ENDFUNC
Reference:
http://www.tomorrowssolutionsllc.com/Conference%20Sessions/Driving%20Word%20and%20Excel%20from%20Visual%20FoxPro.pdf
