CLEAR
obj = NEWOBJECT("excelfiles")
obj.cnamefile = "F:\teste.xls"
*!*obj.cpassword = '654321'
obj.openfile()
?obj.readcellvalue("A1")
?obj.readcellvalue("A2")
*!*obj.cpassword = ''
*!*obj.savefile('')
obj.closefile()
obj = NULL
DEFINE CLASS excelfiles AS CUSTOM
NAME = "EXCELFILES"
HIDDEN objexcel
objexcel = NULL
cnamefile = ""
cpassword = NULL
quitexcel = .T.
FUNCTION openfile AS logical
*!* It open o file specified in the property "cNameFile".
LOCAL fileopened AS logical
m.fileopened = .F.
IF FILE(THIS.cnamefile,1) THEN
TRY
THIS.objexcel.workbooks.OPEN(THIS.cnamefile,,,,THIS.cpassword)
m.fileopened = .T.
CATCH TO oerr
LOCAL strerro AS STRING
m.strerro = ""
m.strerro = "" + m.strerro + "Date: " + DTOC(DATE()) + _enter
m.strerro = "" + m.strerro + "Hour: " + TIME() + _enter
m.strerro = "" + m.strerro + "Error: " + ALLTRIM(STR(m.oerr.ERRORNO)) + _enter
m.strerro = "" + m.strerro + "LineNo: " + ALLTRIM(STR(m.oerr.LINENO)) + _enter
m.strerro = "" + m.strerro + "Message: " + m.oerr.MESSAGE + _enter
m.strerro = "" + m.strerro + "Procedure: " + m.oerr.PROCEDURE + _enter
m.strerro = "" + m.strerro + "Details: " + m.oerr.DETAILS + _enter
m.strerro = "" + m.strerro + "StackLevel: " + ALLTRIM(STR(m.oerr.STACKLEVEL)) + _enter
m.strerro = "" + m.strerro + "LineContents: " + m.oerr.LINECONTENTS + _enter + _enter
STRTOFILE(m.strerro, SYS(5)+CURDIR()+"ErrosABT.log", 1)
MESSAGEBOX(m.strerro, 16, THIS.NAME)
FINALLY
ENDTRY
ENDIF
RETURN m.fileopened
ENDFUNC
PROCEDURE closefile AS void
*!* It open o file specified in the property "cNameFile".
THIS.objexcel.workbooks.CLOSE()
ENDPROC
FUNCTION savefile AS logical
*!* It saves the file with the same name.
LPARAMETERS m.cpassword AS STRING
LOCAL filesaved AS logical
m.filesaved = .F.
TRY
IF (VARTYPE(m.cpassword) == "C") THEN
THIS.objexcel.activeworkbook.PASSWORD = m.cpassword
ENDIF
THIS.objexcel.SAVE()
m.filesaved = .T.
CATCH TO oerr
LOCAL strerro AS STRING
m.strerro = ""
m.strerro = "" + m.strerro + "Date: " + DTOC(DATE()) + _enter
m.strerro = "" + m.strerro + "Hour: " + TIME() + _enter
m.strerro = "" + m.strerro + "Error: " + ALLTRIM(STR(m.oerr.ERRORNO)) + _enter
m.strerro = "" + m.strerro + "LineNo: " + ALLTRIM(STR(m.oerr.LINENO)) + _enter
m.strerro = "" + m.strerro + "Message: " + m.oerr.MESSAGE + _enter
m.strerro = "" + m.strerro + "Procedure: " + m.oerr.PROCEDURE + _enter
m.strerro = "" + m.strerro + "Details: " + m.oerr.DETAILS + _enter
m.strerro = "" + m.strerro + "StackLevel: " + ALLTRIM(STR(m.oerr.STACKLEVEL)) + _enter
m.strerro = "" + m.strerro + "LineContents: " + m.oerr.LINECONTENTS + _enter + _enter
STRTOFILE(m.strerro, SYS(5)+CURDIR()+"ErrosABT.log", 1)
MESSAGEBOX(m.strerro, 16, THIS.NAME)
FINALLY
ENDTRY
RETURN m.filesaved
ENDFUNC
FUNCTION savefileas AS logical
*!* It saves the file with other name.
LPARAMETERS cnewnamefile AS STRING, m.cpassword AS STRING
IF (VARTYPE(m.cpassword) == "C") THEN
THIS.objexcel.activeworkbook.PASSWORD = m.cpassword
ENDIF
IF (VARTYPE(m.cnewnamefile) == "C") THEN
THIS.objexcel.activeworkbook.SAVEAS(m.cnewnamefile)
RETURN .T.
ELSE
RETURN .F.
ENDIF
ENDFUNC
FUNCTION readcellvalue
*!* It edit the value of a cell.
LPARAMETERS ccelllinecollumn AS STRING
RETURN THIS.objexcel.RANGE(m.ccelllinecollumn).VALUE
ENDFUNC
PROCEDURE editcellvalue AS void
*!* It edit the value of a cell.
LPARAMETERS ccelllinecollumn AS STRING, cnewvalue AS STRING
THIS.objexcel.RANGE(m.ccelllinecollumn).VALUE = m.cnewvalue
ENDPROC
PROCEDURE editcellcolor AS void
*!* It edit the color of a cell.
*!* Minimum:1 Maximim: 56
LPARAMETERS ccelllinecollumn AS STRING, ncolor AS INTEGER
THIS.objexcel.RANGE(m.ccelllinecollumn).interior.colorindex = m.ncolor
ENDPROC
PROCEDURE editcellwidth AS void
*!* It edit the value of a cell.
LPARAMETERS ccelllinecollumn AS STRING, nwidth AS INTEGER
THIS.objexcel.RANGE(m.ccelllinecollumn).COLUMNWIDTH = m.nwidth
ENDPROC
PROCEDURE insertcelltoright AS void
LPARAMETERS cellrange AS STRING
*!* Insere uma nova célula, movendo o restante para a direita
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.INSERT(1)
ENDPROC
PROCEDURE deletecelltoleft AS void
LPARAMETERS cellrange AS STRING
*!* Remove a célula, movendo o restante para a esquerda
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.DELETE(1)
ENDPROC
PROCEDURE insertcelltobottom AS void
LPARAMETERS cellrange AS STRING
*!* Insere uma nova célula, movendo o restante para baixo
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.INSERT(2)
ENDPROC
PROCEDURE deletecelltotop AS void
LPARAMETERS cellrange AS STRING
*!* Remove a célula, movendo o restante para a cima
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.DELETE(2)
ENDPROC
PROCEDURE insertrowtobottom AS void
LPARAMETERS cellrange AS STRING
*!* Insere uma nova linha acima da célula selecionada, movendo o restante para baixo
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.INSERT(3)
ENDPROC
PROCEDURE deletecelltotop AS void
LPARAMETERS cellrange AS STRING
*!* Remove a linha da célula selecionada, movendo o restante para cima
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.DELETE(3)
ENDPROC
PROCEDURE insertcolltoright AS void
LPARAMETERS cellrange AS STRING
*!* Insere uma nova coluna à direita da célula selecionada, movendo o restante para a direita
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.INSERT(4)
ENDPROC
PROCEDURE deletecolltoleft AS void
LPARAMETERS cellrange AS STRING
*!* Remove a coluna da célula selecionada, movendo o restante para a esquerda
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.DELETE(4)
ENDPROC
PROCEDURE deletecolltoleft AS void
LPARAMETERS cellrange AS STRING
*!* Remove a coluna da célula selecionada, movendo o restante para a esquerda
THIS.objexcel.RANGE(m.cellrange).SELECT
THIS.objexcel.activecell.DELETE(4)
ENDPROC
PROCEDURE mergecell AS void
LPARAMETERS cellrange AS STRING
*!* Remove a coluna da célula selecionada, movendo o restante para a esquerda
THIS.objexcel.RANGE(m.cellrange).merge
ENDPROC
PROCEDURE editcellheight AS void
*!* It edit the value of a cell.
LPARAMETERS ccelllinecollumn AS STRING, nheight AS INTEGER
THIS.objexcel.RANGE(m.ccelllinecollumn).ROWHEIGHT = m.nheight
ENDPROC
PROCEDURE cellbold AS void
*!* Bold
LPARAMETERS ccelllinecollumn AS STRING, lbold AS logical
THIS.objexcel.RANGE(m.ccelllinecollumn).FONT.bold = m.lbold
ENDPROC
PROCEDURE cellitalic AS void
*!* Italic
LPARAMETERS ccelllinecollumn AS STRING, litalic AS logical
THIS.objexcel.RANGE(m.ccelllinecollumn).FONT.italic = m.litalic
ENDPROC
PROCEDURE cellunderline AS void
*!* Underline
LPARAMETERS ccelllinecollumn AS STRING, lunderline AS logical
THIS.objexcel.RANGE(m.ccelllinecollumn).FONT.italic = m.lunderline
ENDPROC
PROCEDURE cellforecolor AS void
*!* Forecolor
LPARAMETERS ccelllinecollumn AS STRING, ncolor AS INTEGER
THIS.objexcel.RANGE(m.ccelllinecollumn).FONT.colorindex = m.ncolor
ENDPROC
PROCEDURE cellbordersingler AS void
*!* Forecolor
LPARAMETERS ccelllinecollumn AS STRING, nstyle AS INTEGER
THIS.objexcel.RANGE(m.ccelllinecollumn).BORDERS.linestyle = m.nstyle
ENDPROC
PROCEDURE cellhorizontalalignment AS void
*!* 1: Left; 2: Center; 3: Right
LPARAMETERS ccelllinecollumn AS STRING, nposition AS INTEGER
DO CASE
CASE (m.nposition = 1)
THIS.objexcel.RANGE(m.ccelllinecollumn).horizontalalignment=-4131
CASE (m.nposition = 2)
THIS.objexcel.RANGE(m.ccelllinecollumn).horizontalalignment=-4108
CASE (m.nposition = 3)
THIS.objexcel.RANGE(m.ccelllinecollumn).horizontalalignment=-4152
OTHERWISE
ENDCASE
ENDPROC
PROCEDURE showexcel AS void
THIS.objexcel.VISIBLE = .T. && Excel visible
ENDPROC
HIDDEN PROCEDURE INIT AS void
THIS.objexcel = NEWOBJECT('Excel.Application')
*!*
THIS.objexcel.displayalerts = .F. && Disable any dialog message
THIS.objexcel.VISIBLE = .F. && Excel invisible
ENDPROC
HIDDEN PROCEDURE DESTROY AS void
IF (THIS.quitexcel = .T.) THEN
THIS.objexcel.QUIT()
ENDIF
THIS.objexcel = NULL
ENDPROC
HIDDEN PROCEDURE ERROR AS void
LPARAMETERS nerror, cmethod, nline
MESSAGEBOX("Erro: " + ALLTRIM(STR(m.nerror)) + _enter+;
"Método: " + ALLTRIM(m.cmethod) + _enter+;
"Linha: " + ALLTRIM(STR(m.nline)) + _enter+;
"Descrição: "+MESSAGE(),16, THIS.NAME)
ENDPROC
ENDDEFINE
