FiveTech Support Forums

/*
 * Clase TExcelScript v1.14  06-Feb-2004
 *
 * Esta Clase usa la Libreria Ole2 de José F. Giménez
 *
 * Autor: Víctor Manuel Tomas Díaz [Vikthor]
 *
 */

#include "FiveWin.Ch"
#include "common.Ch"

/*
 *  TExcelScript()
 */
CLASS TExcelScript

   DATA cFile

   DATA oExcel
   DATA oWorkBooks
   DATA oBook
   DATA oSheet
   DATA oShape
   DATA oCell
   DATA oFind

   DATA cFont
   DATA nSize
   DATA lBold
   DATA lItalic
   DATA lUnderLine
   DATA nAlign
   DATA lOpen      AS LOGICAL    INIT .F.
   DATA lDefault   AS LOGICAL    INIT .T.

   DATA aExcelCols AS ARRAY INIT {}
   DATA aData      AS ARRAY INIT {}

   DATA cAlias

   DATA nAt
   DATA nFormat
   DATA lExcel
   DATA oClip
   DATA aSheets
   DATA nSheets
   DATA nBooks
   DATA aBooks

   METHOD AddCol( bAction , nAlign , bClrText , bClrPane , bHeading , bFooting )
   METHOD AddComent( nRow, nCol, cText )
   METHOD AddPicture( cFile, nRow , nCol )
   METHOD Borders( cRange, nRow, nCol, nStyle )
   METHOD Browse( nRow , nCol , cAlias , cFont , nSize , bClrText , bClrPane  )
   METHOD CellFormat( nRow, nCol, nBackGround, nLine, cFormat )
   METHOD Chart( cRange , cTitle , nType )
   METHOD ColumnWidth( nCol, nWidth )
   METHOD Copy( cRange )
   METHOD Create( cFileXLS )
   METHOD Duplicate( cRange )
   METHOD End( lClose )
   METHOD Find( cSearch, lMatch, lPart )
   METHOD FindNext()
   METHOD Footers( nOpc , cVal )
   METHOD FormatRange( cRange , aFormat )
   METHOD FormatoNumerico( cRango, cFormato )
   METHOD Formula( nRow , nCol , cValue )
   METHOD Get( nRow , nCol ,cValue )
   METHOD Headers( nOpc , cVal )
   METHOD HowBook()
   METHOD HowSheet()
   METHOD MailSystem()
   METHOD Margins( nOpc , nVal )
   METHOD New()
   METHOD Open( cFileXLS )
   METHOD Paste()
   METHOD Print()
   METHOD ProtectBook( cPassword )
   METHOD ProtectSheet( cPassword )
   METHOD RangeColorFont( cRange, nColor, nRow, nCol )
   METHOD RangeFondo( cRange, nColor )
   METHOD ReadOnly( lMsg )
   METHOD Replace( cSearch, cReplace, lMatch, lPart, lAll, lFull, cFormat )
   METHOD Say( nRow, nCol, xValue, cFont, nSize, lBold, lItalic, lUnderLine, nAlign, nColor, nFondo , nBorder )
   METHOD SeekBook( cBook )
   METHOD SeekSheet( cSheet )
   METHOD SendeMail( cSender, cSubject, lShowMessage, lIncludeAttachment)
   METHOD SendMail( cMail , cSubject , lReturn )
   METHOD Subtotal( cRange, nGroup, nOpe, nCol )

   METHOD AddShape( nShape, nLeft , nTop , nWidth , nHeight )  INLINE ::oShape:AddShape( nShape , nLeft , nTop , nWidth , nHeight ) // [ Vikthor ]
   METHOD AddSheet()                                           INLINE ::oExcel:Sheets:Add()
   METHOD Align( nPos )                                        INLINE ::nAlign := nPos
   METHOD AutoFilter( cRange, nCol, uVal )                     INLINE ::oSheet:Range( cRange ):AutoFilter( nCol, uVal )
   METHOD AutoFit( nCol )                                      INLINE ::oSheet:Columns( nCol ):AutoFit()
   METHOD ColFormat( nCol , cFormat )                          INLINE ::oSheet:Columns( nCol ):Set( "NumberFormat", cFormat )
   METHOD Combinar( cRange )                                   INLINE ::oSheet:Range( cRange ):Merge()
   METHOD CopySheet()                                          INLINE ::oExcel:Sheets:Copy()
   METHOD CountSheets()                                        INLINE ::nSheets := ::oExcel:Sheets:Count()
   METHOD DelSheet( cSheet )                                   INLINE ::oExcel:Sheets( cSheet ):Delete()
   METHOD Dialogs( nTypeDlg )                                  INLINE ::oExcel:Dialogs( nTypeDlg ):Show()
   METHOD Font( cFont )                                        INLINE ::cFont := cFont
   METHOD GetCell()                                            INLINE (::oCell := ::oExcel:Get( "ActiveCell" ), ::oCell )
   METHOD HPageBreaks( oCell )                                 INLINE ::oSheet:HPageBreaks:Invoke("Add", oCell )
   METHOD InsertCol( cRange )                                  INLINE ( IIf( cRange != NIL, ::SetPos( cRange ), ), ::GetCell():Get( "EntireColumn" ):Insert() )
   METHOD InsertRow( cRange )                                  INLINE ( IIf( cRange != NIL, ::SetPos( cRange ), ), ::GetCell():EntireRow():Insert() )
   METHOD lCenterH( lCenter)                                   INLINE ::oSheet:PageSetup:Set( "CenterHorizontally", lCenter )
   METHOD lCenterV( lCenter)                                   INLINE ::oSheet:PageSetup:Set( "CenterVertically", lCenter )
   METHOD MoveSheet( cSheet, cPos, nSheet )                    INLINE ::oExcel:Sheets( cSheet ):Move( cPos, nSheet ) // cPos -> "After" | "Before"
   METHOD MultiLine( nRow , nCol )                             INLINE ::oSheet:Cells( nRow, nCol ):Set("WrapText", TRUE )
   METHOD NameSheet( cSheet, cName )                           INLINE ::oExcel:Sheets(cSheet):Name := cName
   METHOD nBooks()                                             INLINE ::nBooks := ::oBook:Count()
   METHOD nCols()                                              INLINE ::oExcel:oSheet:UsedRange:Columns:Count()
   METHOD nColsCount()                                         INLINE ::oSheet:UsedRange:Columns:Count()
   METHOD nRows()                                              INLINE ::oExcel:oSheet:UsedRange:Rows:Count()
   METHOD nRowsCount()                                         INLINE ::oSheet:UsedRange:Rows:Count()
   METHOD Picture( cFile, cRange )                             INLINE ( IIf( cRange != NIL, ::SetPos(cRange ),), ::oSheet:Pictures:insert(cFile) )
   METHOD Protect( cPassword )                                 INLINE ::ProtectBook( cPassword ) , ::ProtectSheet( cPassword )
   METHOD RanMultiLine( cRange )                               INLINE ::oSheet:Range( cRange ):Set("WrapText", TRUE )
   METHOD Save()                                               INLINE IIF( ::lOpen, ::oBook:Save(), ::oBook:SaveAs( ::cFile, ::nFormat ) )
   METHOD SaveAs( cFilexls , nFormat )                         INLINE ::oBook:SaveAs( cFilexls , nFormat )
   METHOD SetArray( aArray )                                   INLINE ::aData := aArray
   METHOD SetFont( cFont )                                     INLINE ::oSheet:Cells:Font:Name := cFont
   METHOD SetLandScape()                                       INLINE ::oSheet:PageSetup:Set("Orientation",2 )
   METHOD SetPage( nPage  )                                    INLINE ::oSheet:PageSetup:Set( "PaperSize", nPage )
   METHOD SetPortrait()                                        INLINE ::oSheet:PageSetup:Set("Orientation",1 )
   METHOD SetPos( cRange )                                     INLINE (::oSheet:Range( cRange ):Select(), ::GetCell() )
   METHOD SetPrintArea(cRange)                                 INLINE ::oSheet:PageSetup:Set( "printarea", cRange )
   METHOD SetSheet( cSheet)                                    INLINE ::oExcel:Sheets(cSheet):Select() , ::oSheet := ::oExcel:Get( "ActiveSheet" )
   METHOD Size( nSize )                                        INLINE ::nSize := nSize
   METHOD SizeFont( nSize )                                    INLINE ::oSheet:Cells:Font:Size := 12
   METHOD TitleRows( cRange )                                  INLINE ( IIf( cRange!=NIL, ::oSheet:PageSetup:Set( "PrintTitleRows", cRange ), ) )
   METHOD UnProtect( cPassword )                               INLINE ::UnProtectBook( cPassword ) , ::UnProtectSheet( cPassword )
   METHOD UnProtectBook( cPassword )                           INLINE ::oBook:Invoke( 'UnProtect' , cPassword )
   METHOD UnProtectSheet( cPassword )                          INLINE ::oSheet:Invoke( 'UnProtect' , cPassword )
   METHOD Visualizar( lValue )                                 INLINE ::oExcel:Visible := lValue
   METHOD WebPagePreview()                                     INLINE ::oBook:Invoke( "WebPagePreview" )
   METHOD Zoom( nZoom )                                        INLINE ::oSheet:PageSetup:Set( "Zoom", nZoom )

   MESSAGE Clear()   METHOD eClear( cRange )
   MESSAGE Eval()    METHOD eEval( cCommand, lOemAnsi )

   ERROR HANDLER ERROR()

ENDCLASS

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():New()
*/
METHOD New() CLASS TExcelScript

   ::lExcel := TRUE

   TRY
      ::oExcel := GetActiveObject( "Excel.Application" )
   CATCH
      TRY
         ::oExcel := CreateObject( "Excel.Application" )
      CATCH
         ::lExcel := FALSE
         MsgAlert( "Miscrosoft Excel no esta instalado en está PC.", "Alerta" )
      END

   END

   ::aExcelCols := {}

   ::oClip  := TClipBoard():New()
   ::oClip:Clear()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Open( cFilexls ) -> Self
*/
METHOD Open( cFileXLS ) CLASS TExcelScript

   IF !File( cFileXLS )
      MsgAlert( "Archivo no encontrado:" + cFileXLS )
      RETURN NIL
   ENDIF

   ::cFile := cFileXLS

   ::oExcel:WorkBooks:Open( ::cFile )

   ::oBook       := ::oExcel:Get( "ActiveWorkBook")
   ::oSheet      := ::oExcel:Get( "ActiveSheet" )
   ::oShape      := ::oSheet:Get( "Shapes" )
   ::cFont       := "Arial"
   ::nSize       := 10
   ::lBold       := FALSE
   ::lItalic     := FALSE
   ::lUnderLine  := FALSE
   ::nAlign      := 1
   ::lDefault    := FALSE
   ::lOpen       := TRUE
   ::nFormat     := ::oBook:Get( "FileFormat" )

   ::SetPos( "A1" )

   ::GetCell()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():ReadOnly( lMsg ) -> Self
*/
METHOD ReadOnly( lMsg ) CLASS TExcelScript
   LOCAL lRet := FALSE

   IF ::oBook:ReadOnly
      lRet := TRUE
      IF lMsg
         MsgInfo( " El archivo " + ::cFile + " está abierto en otra sesión." )
      ENDIF
   ENDIF

RETURN lRet

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Create( cFilexls ) -> Self
*/
METHOD Create( cFileXLS ) CLASS TExcelScript

  ::cFile := cFileXLS

  ::oExcel:WorkBooks:Add()

  ::oBook       := ::oExcel:Get( "ActiveWorkBook" )
  ::oSheet      := ::oExcel:Get( "ActiveSheet"    )
  ::oShape      := ::oSheet:Get( "Shapes"         )
  ::cFont       := "Arial"
  ::nSize       := 10
  ::lBold       := FALSE
  ::lItalic     := FALSE
  ::lUnderLine  := FALSE
  ::nAlign      := 1
  ::lDefault    := TRUE
  ::nFormat     := ::oBook:Get( "FileFormat" )

  ::SetPos( "A1" )

  ::GetCell()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Get( nRow , nCol , cValue ) -> xRet
*/
METHOD Get( nRow , nCol , cValue ) CLASS TExcelScript
   LOCAL xRet
   LOCAL cType

   DEFAULT cValue := NIL //"C"

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNil( nRow ) .or. HB_IsNil( nCol )
      ::GetCell()
      DEFAULT nRow := ::oCell:Row
      DEFAULT nRow := ::oCell:Col
   ENDIF

   xRet := ::oSheet:Cells( nRow, nCol ):Value
   xRet := IIf( ValType( xRet )=="U", "" , xRet )
   cType := ValType( xRet )

   IF !HB_IsNil( cValue )
      IF HB_IsNumeric( cValue )
         xRet := IIf( HB_IsString( xRet ), Val( xRet ) ,;
                      IIf( HB_IsDate( xRet ), xRet, Val( Str( xRet, 21, NumGetDecimals( xRet ) ) ) ) )
      ENDIF

      IF HB_IsString( cValue )
         xRet := IIF( HB_IsNumeric( xRet ), Str( xRet, 21, NumGetDecimals( xRet ) ) ,;
                      IIF( HB_IsDate( xRet ), DToS( xRet ), xRet )  )
      ENDIF

   ENDIF

RETURN xRet

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():RangeFondo( cRange, nColor ) -> Self
*/
METHOD RangeFondo( cRange, nColor, nRow, nCol ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cRange := "", ;
           nColor := Rgb( 255, 255, 255 ), ;
           nRow   := ::oCell:Row , ;
           nCol   := ::oCell:Column


   IF !Empty( cRange )
      ::oSheet:Range( cRange ):Interior:Color     := nColor
   ELSE
      ::oSheet:Cells( nRow, nCol ):Interior:Color := nColor
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
 *  TExcelScript():RangeColorFont(  cRange, nColor, nRow, nCol )
 */
METHOD RangeColorFont( cRange, nColor, nRow, nCol ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cRange := "", ;
           nColor := Rgb( 000, 000, 000 ), ;
           nRow   := ::oCell:Row, ;
           nCol   := ::oCell:Column

   IF !Empty( cRange )
      ::oSheet:Range( cRange ):Font:Color    := nColor
   ELSE
      ::oSheet:Cells( nRow, nCol ):Font:Color := nColor
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Borders( cRange, nRow, nCol, nStyle ) -> Self
*/
METHOD Borders( cRange, nRow, nCol, nStyle ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cRange := "", ;
           nColor := Rgb( 000, 000, 000 ), ;
           nRow   := ::oCell:Row, ;
           nCol   := ::oCell:Column

   IF Empty( cRange )
      ::oSheet:Cells( nRow, nCol ):Borders():LineStyle  := nStyle
   ELSE
      ::oSheet:Range( cRange ):Borders():LineStyle      := nStyle
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():CellFormat( nRow, nCol, nColor, nLine, cFormat) -> Self
*/
METHOD CellFormat( nRow, nCol, nColor, nLine, cFormat ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNil( nRow ) .or. HB_IsNil( nCol )
      ::GetCell()
      DEFAULT nRow := ::oCell:Row
      DEFAULT nCol := ::oCell:Column
   ENDIF

   IF ::lDefault
      DEFAULT nColor := Rgb( 255, 255, 255 )
   ENDIF

   IF !HB_IsNil( nColor )
      ::oSheet:Cells( nRow, nCol ):Interior:Color := nColor
   ENDIF

   IF !HB_IsNil( cFormat )
      ::oSheet:Cells( nRow, nCol ):Set( "NumberFormat", cFormat )
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():AddComent( nRow, nCol, cText ) -> Self
*/
METHOD AddComent( nRow, nCol, cText ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cText := ""

   IF !Empty( cText )
      ::oSheet:Cells( nRow, nCol ):AddComment( cText )
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Print() -> Self
*/
METHOD Print() CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

  ::oSheet:PrintOut()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Say( nRow, nCol, xValue, cFont, nSize, lBold, lItalic, ;
                    lUnderLine, nAlign, nColor, nFondo, nOrien, nStyle, cFormat ) -> NIL
  nAlign -> 1  // Derecha
  nAlign -> 4  // Izquierda
  nAlign -> 7  // Centrado
*/
METHOD Say( nRow, nCol, xValue, cFont, nSize, lBold, lItalic, ;
            lUnderLine, nAlign, nColor, nFondo, nOrien, nStyle, cFormat ) CLASS TExcelScript

   LOCAL xVret

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF ::lDefault
      DEFAULT cFont       := ::cFont
      DEFAULT nSize       := ::nSize
      DEFAULT lBold       := ::lBold
      DEFAULT lItalic     := ::lItalic
      DEFAULT lUnderLine  := ::lUnderLine
      DEFAULT nAlign      := ::nAlign
      DEFAULT nColor      := Rgb( 000, 000, 000 )
      DEFAULT nFondo      := RGB( 255, 255, 255 )
      DEFAULT nOrien      := 0
      DEFAULT nStyle      := 1
      DEFAULT cFormat     := "0"
   ENDIF

   IF HB_IsNil( nRow ) .or. HB_IsNil( nCol )
      ::GetCell()
      DEFAULT nRow := ::oCell:Row
      DEFAULT nCol := ::oCell:Column
   ENDIF

   IF !HB_IsNil( cFont )
    ::oSheet:Cells( nRow, nCol ):Font:Name := cFont
   ENDIF

   IF !HB_IsNil( nSize )
      ::oSheet:Cells( nRow, nCol ):Font:Size := nSize
   ENDIF

   IF !HB_IsNil( lBold )
      ::oSheet:Cells( nRow, nCol ):Font:Bold := lBold
   ENDIF

   IF !HB_IsNil( lItalic )
      ::oSheet:Cells( nRow, nCol ):Font:Italic := lItalic
   ENDIF

   IF !HB_IsNil( lUnderLine )
      ::oSheet:Cells( nRow, nCol ):Font:UnderLine := lUnderLine
   ENDIF

   IF !HB_IsNil( nColor )
      ::oSheet:Cells( nRow, nCol ):Font:Color := nColor
   ENDIF

   IF HB_IsNumeric( xValue )
      ::oSheet:Cells( nRow, nCol ):Set( "NumberFormat", cFormat )
   ENDIF

   ::oSheet:Cells( nRow, nCol ):Value := xValue

   IF !HB_IsNil( nFondo )
      ::oSheet:Cells( nRow, nCol ):Interior:Color := nFondo
   ENDIF

   IF !HB_IsNil( nAlign )
      ::oSheet:Cells( nRow, nCol ):Set( "HorizontalAlignment", Alltrim( Str( nAlign ) ) )
      ::oSheet:Cells( nRow, nCol ):Set( "Orientation"        , nOrien )
   ENDIF

   IF !HB_IsNil( nStyle )
      ::oSheet:Cells( nRow, nCol ):Borders():LineStyle  := nStyle
   ENDIF

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():End( lClose ) -> NIL
*/
METHOD End( lClose ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT lClose := TRUE

   IF HB_IsObject( ::oFind )
      ::oFind := NIL
   ENDIF

   IF HB_IsObject( ::oCell )
      ::oCell := NIL
   ENDIF

   IF HB_IsObject( ::oSheet )
      ::oSheet := NIL
   ENDIF

   IF HB_IsObject( ::oBook )
      ::oBook := NIL
   ENDIF

   IF !lClose
      ::lOpen := FALSE
      ::oExcel:WorkBooks:Close()
   ENDIF

   ::oClip:End()

   ::oExcel:Quit()

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Eval( cCommand, lOemAnsi, xParam ) -> Self
*/
METHOD eEval( cCommand, lOemAnsi, xParam ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT lOemAnsi := FALSE

   IF lOemAnsi
      cCommand := OemToAnsi( cCommand )
   ENDIF

   //soporte de lineas de Comentarios
   IF Left( AllTrim( cCommand ), 1 ) $ "*/#"

   ELSEIF Left( AllTrim( cCommand ), 1 ) == "!"
      cCommand  := AllTrim( SubStr( cCommand, 2 ) )
      Eval( &( "{|oThis, uParam| " + cCommand + " } " ), Self, xParam )
   ELSE
      Eval( &( "{|oThis, uParam| oThis:" + cCommand + " } " ), Self, xParam )
   ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():SubTotal( cRange, nGroup, nOpe, nCol ) -> Self
*/
METHOD SubTotal( cRange, nGroup, nOpe, nCol ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT nOpe := 1

   DO CASE
   CASE nOpe == 1
      nOpe := -4157   // Sum
   CASE nOpe == 2
      nOpe := -4106   // Ave
   CASE nOpe == 3
      nOpe := -4112   // Count
   CASE nOpe == 4
      nOpe := -4155   // StDev
   CASE nOpe == 5
      nOpe := -4156   // StDevP
   OTHERWISE
      nOpe := -4157   // Sum
   ENDCASE

   ::oSheet:Range( cRange ):SubTotal( nGroup, nOpe, nCol  )

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Duplicate( cRange ) -> Self
*/
METHOD Duplicate( cRange ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cRange := ::oCell:Row

   ::oExcel:Rows( cRange ):Select()
   ::oExcel:Selection:Copy()
   ::oExcel:Selection:Insert()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Clear( cRange ) -> Self
*/
METHOD eClear( cRange ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNil( cRange )
      RETURN Self
   ENDIF

   ::oExcel:Range( cRange ):Select()
   ::oExcel:Selection:Invoke( "ClearContents" )

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Find( cSearch, lMatch, lPart ) -> lFound
*/
METHOD Find( cSearch, lMatch, lPart ) CLASS TExcelScript
   LOCAL oRange, lFound := FALSE

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNil( cSearch )
      RETURN lFound
   ENDIF

   DEFAULT lMatch := FALSE,;
           lPart  := FALSE

   ::GetCell():Activate()

   oRange := ::oSheet:Cells:Find( cSearch )

   IF HB_IsObject( oRange ) .and. oRange[ 01 ] > 0

      oRange:Activate()

      ::GetCell()

      ::oFind := oRange

      lFound := TRUE

      IF lMatch .or. !lPart
         WHILE !IIf( lPart, cSearch $ ::Get( ::oCell:Row, ::oCell:Column ), cSearch == ::Get( ::oCell:Row, ::oCell:Column ) )
            IF !::FindNext( oRange )
               lFound := FALSE
               EXIT
            ENDIF
         ENDDO
      ENDIF

  ENDIF

  oRange := NIL

RETURN lFound

/*-------------------------------------------------------------------------------------------------*/
/*
 *  TExcelScript():FindNext() -> lFound
 */
METHOD FindNext() CLASS TExcelScript
  LOCAL lFound := FALSE
  LOCAL oRange, cRange, oCell

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsObject( ::oFind )

      oCell   := ::oCell
      cRange  := ::oExcel:Get( "ActiveCell" ):Address
      oRange  := ::oExcel:Cells:FindNext( ::oFind )

      IF HB_IsObject( oRange ) .and. oRange[ 1 ] > 0

         oRange:Activate()

         ::GetCell()

         IF ::oCell:Row == oCell:Row
            lFound := ( ::oCell:Column > oCell:Column )
         ELSEIF ::oCell:Row > oCell:Row
            lFound := TRUE
         ENDIF

         IF lFound
            ::oFind := oRange
         ELSE
            ::SetPos( cRange )
            ::oFind := NIL
         ENDIF

      ENDIF

   ENDIF

   oRange := NIL

RETURN lFound

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Replace( cSearch, cReplace, lMatch, lPart, lAll, lFull, cFormat ) -> lFound
*/
METHOD Replace( cSearch, cReplace, lMatch, lPart, lAll, lFull, cFormat ) CLASS TExcelScript
   LOCAL lFound  := FALSE

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT lAll  := FALSE,;
           lFull := FALSE

   IF !HB_IsNil( cReplace )

      WHILE ::Find( cSearch, lMatch, lPart )
         lFound := TRUE

         IF !HB_IsNil( cFormat )
            ::CellFormat( ,,,, cFormat )
         ENDIF

         IF lFull
            ::Say( ,, cReplace )
         ELSE
            ::Say( ,, StrTran( ::Get(), cSearch, cReplace ) )
         ENDIF

         IF !lAll
            EXIT
         ENDIF

      ENDDO

   ENDIF

RETURN lFound

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Chart( cRange , cTitle , nType , nDepth , nGapDepth ) -> Self
*/
METHOD Chart( cRange , cTitle , nType , nDepth , nGapDepth ) CLASS TExcelScript
   LOCAL oChart , oSheet

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cTitle    := "Grafica" ,;
           nDepth    := 20 ,;    // Profundidad de la Grafica
           nGapDepth := 20       // Separacion entre series

   ::oSheet:Range( cRange ):Select()
   ::oExcel:Charts:Add()

   WITH OBJECT ( oChart := ::oExcel:Get( "ActiveChart" ) )

      :ChartType       := nType
      :HasTitle        := TRUE
      :ChartTitle:Text := cTitle

      :Set( "DepthPercent", nDepth    )
      :Set( "GapDepth"    , nGapDepth )

   END

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():aAddCol( bAction, nAlign,  bClrText, bClrPane, bHeading, bFooting ) -> Self
*/
METHOD AddCol(  bAction, nAlign,  bClrText, bClrPane, bHeading, bFooting ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT nAlign    := 1        ,; // Derecha
           bAction   := {|| "" } ,;
           bFooting  := {|| "" } ,;
           bHeading  := {|| "" } ,;
           bClrText  := {|| Rgb( 000,000,000 ) } ,;
           bClrPane  := {|| Rgb( 255,255,255 ) }

   AAdd( ::aExcelCols, { bAction , nAlign , bClrText , bClrPane , bHeading , bFooting } )

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Browse( nRow, nCol, cAlias, cFont, nSize, bClrText, bClrPane ) -> Self
*/
METHOD Browse( nRow, nCol, cAlias, cFont, nSize, bClrText, bClrPane ) CLASS TExcelScript
   LOCAL nExcelCols := Len( ::aExcelCols )
   LOCAL nCiclo
   LOCAL nI

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cFont    := "Tahoma" , ;
           bClrText := {|| Rgb( 000, 000, 000 ) } , ;
           bClrPane := {|| Rgb( 255, 255, 255 ) } , ;
           nSize    := 10 , ;
           nRow     := 1  , ;
           nCol     := 1

   nCol--

   ::nAt    := 0
   ::cAlias := cAlias

   IF !Empty( ::cAlias )

      /* encabezados */
      FOR nCiclo := 1 TO nExcelCols
         ::Say( nRow, nCol + nCiclo, Eval( ::aExcelCols[ nCiclo, 5 ] ), cFont, nSize,,,, ::aExcelCols[ nCiclo, 2 ], ;
              Eval(  bClrText  ), Eval(  bClrPane  ) )

         ::Borders( NIL, nRow , nCol + nCiclo , 1 )
      NEXT

      nRow ++

      /* arreglo o DBF */
      IF Lower( ::cAlias ) == "array"

         (::cAlias)->( DbGoTop() )

         FOR nI := 1 TO Len( ::aData )
            FOR nCiclo := 1 TO nExcelCols
               ::Say( nRow , nCol+nCiclo, ::aData[ nI, nCiclo ], cFont, nSize,,,, ::aExcelCols[ nCiclo, 2 ], ;
                      Eval( ::aExcelCols[ nCiclo, 3 ] ), Eval(  ::aExcelCols[ nCiclo, 4 ] ) )
               ::Borders( , nRow , nCol + nCiclo, 1 )
            NEXT
            ::nAt++
            nRow++
         NEXT

      ELSE

         DO WHILE !( ::cAlias )->( Eof() )
            FOR nCiclo := 1 TO nExcelCols
               ::Say( nRow , nCol + nCiclo, Eval( ::aExcelCols[ nCiclo, 1 ] ), cFont, nSize,,,, ::aExcelCols[ nCiclo, 2 ], ;
                      Eval( ::aExcelCols[ nCiclo, 3 ] ), Eval( ::aExcelCols[ nCiclo, 4 ] ) )
               ::Borders( NIL, nRow , nCol + nCiclo, 1 )
            NEXT
            ::nAt++
            nRow++
            (::cAlias )->( DbSkip() )
         ENDDO

      ENDIF

      /* Footers */
      FOR nCiclo := 1 TO Len( ::aExcelCols )
         ::Say( nRow , nCol + nCiclo, Eval( ::aExcelCols[ nCiclo, 6 ] ), cFont, nSize,,,, ::aExcelCols[ nCiclo, 2 ] ,;
              Eval(  bClrText  ), Eval(  bClrPane  ) )
         ::Borders( NIL, nRow , nCol + nCiclo , 1 )
         ::AutoFit( nCol + nCiclo )
      NEXT

  ENDIF

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Margins( nOpc, cVal ) -> Self
 */
METHOD Margins( nOpc, nVal ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT nVal := 0 ,;
           nOpc := 0

   DO CASE
   CASE nOpc == 0  // Todos
        ::oSheet:PageSetup:Set( "RightMargin"  , nVal )
        ::oSheet:PageSetup:Set( "TopMargin"    , nVal )
        ::oSheet:PageSetup:Set( "LeftMargin"   , nVal )
        ::oSheet:PageSetup:Set( "BottomMargin" , nVal )
        ::oSheet:PageSetup:Set( "FooterMargin" , nVal )
        ::oSheet:PageSetup:Set( "HeaderMargin" , nVal )
   CASE nOpc == 1  // Right
        ::oSheet:PageSetup:Set( "RightMargin"  , nVal )
   CASE nOpc == 2  // Top
        ::oSheet:PageSetup:Set( "TopMargin"    , nVal )
   CASE nOpc == 3  // Left
        ::oSheet:PageSetup:Set( "LeftMargin"   , nVal )
   CASE nOpc == 4  // Bottom
        ::oSheet:PageSetup:Set( "BottomMargin" , nVal )
   CASE nOpc == 5  // Footer Margin
        ::oSheet:PageSetup:Set( "FooterMargin" , nVal )
   CASE nOpc == 6  // Header Margin
        ::oSheet:PageSetup:Set( "HeaderMargin" , nVal )
   ENDCASE

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Footers( nOpc, nVal ) -> Self
*/
METHOD Footers( nOpc, nVal ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT nVal := 1 , ; // Centrado
           nOpc := 0

   DO CASE
   CASE nOpc == 1  // Center
        ::oSheet:PageSetup:Set( "CenterFooter" , nVal )
   CASE nOpc == 2  // Left
        ::oSheet:PageSetup:Set( "LeftFooter"   , nVal )
   CASE nOpc == 3  // Right
        ::oSheet:PageSetup:Set( "RightFooter"  , nVal )
   ENDCASE

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Headers( nOpc, cVal ) -> Self
*/
METHOD Headers( nOpc, cVal ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cVal := 1 ,; // Centrado
           nOpc := 0

   DO CASE
   CASE nOpc == 1  // Center
        ::oSheet:PageSetup:Set( "CenterHeader" , cVal )
   CASE nOpc == 2  // Left
        ::oSheet:PageSetup:Set( "LeftHeader"   , cVal )
   CASE nOpc == 3  // Right
        ::oSheet:PageSetup:Set( "RightHeader"  , cVal )
   ENDCASE

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():SendMail( cMail, cSubject, lReturn ) -> Self
*/
METHOD SendMail( cMail, cSubject, lReturn ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   DEFAULT cMail    := "usuario@dominio.com" ,;
           cSubject := "TExcel Mailer Class" ,;
           lReturn  := TRUE

    ::oBook:SendMail( cMail , cSubject , lReturn )

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():MailSystem() -> nMailSystem
*/
METHOD MailSystem() CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

RETURN ::oExcel:MailSystem()

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():AddPicture( cFile, nRow , nCol , nWidth , nHeight ) -> NIL
*/
METHOD AddPicture( cFile, nRow , nCol , nWidth , nHeight ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF Empty( cFile )
      RETURN NIL
   ELSE
      IF !File( cFile )
         RETURN NIL
      ENDIF
   ENDIF

   DEFAULT nRow    := 1   , ;
           nCol    := 1   , ;
           nWidth  := 100 , ;
           nHeight := 100

  ::oShape:Invoke( "AddPicture" , cFile , TRUE, TRUE, nRow , nCol , nWidth , nHeight )

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():SendeMail( cSender, cSubject, lShowMessage, lIncludeAttachment ) -> NIL
*/
METHOD SendeMail( cSender, cSubject, lShowMessage, lIncludeAttachment ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF Empty( cSender )
      RETURN NIL
   ENDIF

   DEFAULT cSender            := "usuario@dominio.com" ,;
           cSubject           := "TExcel Mailer Class" ,;
           lShowMessage       := FALSE ,;
           lIncludeAttachment := FALSE

  ::oBook:Invoke( "SendForReview", cSender, cSubject, lShowMessage, lIncludeAttachment )

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():ProtectBook() -> NIL
*/
METHOD ProtectBook( cPassword ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::oBook:Invoke( 'Protect' , cPassword , TRUE, TRUE )

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():ProtectSheet() -> NIL
*/
METHOD ProtectSheet( cPassword ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::oSheet:Invoke( 'Protect' , cPassword , TRUE, TRUE, TRUE, TRUE )

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Copy() -> Self
*/
METHOD Copy( cRange ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNil( cRange )
      RETURN Self
   ENDIF

   ::oExcel:Range( cRange ):Select()
   ::oExcel:Selection:Copy()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Paste() -> Self
*/
METHOD Paste() CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::oSheet:Paste()

RETURN Self

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():FormatRange() -> NIL
*/
METHOD FormatRange( cRange , cFont , nSize , lBold , lItalic , nAlign , nFore , nBack , nStyle , cFormat , lAutoFit ) CLASS TExcelScript
   LOCAL oRange

   IF !::lExcel
      RETURN NIL
   ENDIF

   oRange := ::oSheet:Range( cRange )

   IIf( HB_IsNil( cFont    ), , oRange:Font:Name           := cFont   )
   IIf( HB_IsNil( nSize    ), , oRange:Font:Size           := nSize   )
   IIf( HB_IsNil( lBold    ), , oRange:Font:Bold           := lBold   )
   IIf( HB_IsNil( lItalic  ), , oRange:Font:Italic         := lItalic )
   IIf( HB_IsNil( nFore    ), , oRange:Font:Color          := nFore   )
   IIf( HB_IsNil( nBack    ), , oRange:Interior:Color      := nBack   )
   IIf( HB_IsNil( nStyle   ), , oRange:Borders():LineStyle := nStyle  )
   IIf( HB_IsNil( lAutoFit ), , oRange:Columns:AutoFit()              )
   IIf( HB_IsNil( cFormat  ), , oRange:Set( "NumberFormat", cFormat)  )
   IIf( HB_IsNil( nAlign   ), , oRange:Set( "HorizontalAlignment", Alltrim( Str( nAlign ) ) ) )

   oRange := NIL

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():SeekSheet() -> lRet
*/
METHOD SeekSheet( cSheet ) CLASS TExcelScript
   LOCAL lRet := FALSE

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::HowSheet()

   lRet := IIf( AScan( ::aSheets , cSheet ) > 0 , TRUE  ,  FALSE )

RETURN lRet

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():HowSheet()
*/
METHOD HowSheet() CLASS TExcelScript
   LOCAL nSheets
   LOCAL i

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::aSheets := {}

   nSheets := ::oExcel:Sheets:Count()

   FOR i := 1 TO nSheets
      AAdd( ::aSheets , ::oExcel:Sheets:Item( i ):Name )
   NEXT

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript:FormatoNumerico( cRango, cFormato ) -> NIL
*/
METHOD FormatoNumerico( cRango, cFormato ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::oExcel:Range( cRango ):Select()
   ::oExcel:Selection:NumberFormat = cFormato

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript:ColumnWidth( nCol, nWidth ) -> NIL
*/
METHOD ColumnWidth( nCol, nWidth ) CLASS TExcelScript
   IF !::lExcel
      RETURN NIL
   ENDIF

   IF HB_IsNumeric( nCol )
     nCol := cColumn2Letter( nCol ) + ":" + cColumn2Letter( nCol )
   ENDIF

   ::oSheet:Columns( nCol ):ColumnWidth := nWidth

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
cMakeRange()
*/
FUNCTION cMakeRange( nRowIni, nColIni, nRowFin, nColFin )
   LOCAL cRange := cColumn2Letter( nColIni ) + N2C( nRowIni )

   IF !HB_IsNil( nRowFin ) .and. !HB_IsNil( nColFin )
      cRange  += ":" + cColumn2Letter( nColFin ) + N2C( nRowFin )
   ENDIF

RETURN cRange

/*-------------------------------------------------------------------------------------------------*/
/*
N2C
*/
FUNCTION N2C( nNum )
RETURN AllTrim( Str( Int( nNum ) ) )

/*-------------------------------------------------------------------------------------------------*/
/*
cLetter2Column()
*/
FUNCTION cLetter2Column( cLetter )
   LOCAL nCol := 0
RETURN Asc( cLetter ) - 64

/*-------------------------------------------------------------------------------------------------*/
/*
cColumn2Letter()
 */
FUNCTION cColumn2Letter( n )
   LOCAL r := ""

   IF n > 26
      r := Chr( 64 + Int( n / 26 ) )
      n := n % 26
   ENDIF

   r += Chr( 64 + n )

RETURN r

/*-------------------------------------------------------------------------------------------------*/
/*
NumGetDecimals( <nNumber> ) --> nDecimals
*/
STATIC FUNCTION NumGetDecimals( nNumber )
  LOCAL cNum, nLen
  LOCAL nPos, nDec

  cNum := Str( nNumber, 21, 10 )
  nLen := Len( cNum )
  nPos := At( ".", cNum )

  IF nPos > 0
    FOR nDec := nLen TO nPos STEP -1
      IF SubStr( cNum, nDec, 1 ) == "0"
        cNum := SubStr( cNum, 1, Len( cNum ) - 1 )
      ELSE
        EXIT
      ENDIF
    NEXT

    RETURN Len( AllTrim( SubStr( cNum, nPos + 1 ) ) )

  ENDIF

RETURN 0

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Error() -> NIL
*/
METHOD Error() CLASS TExcelScript
   LOCAL cMsg, nParam

   cMsg := __GetMessage()

   MsgInfo( "La propiedad "+ cMsg + " no existe " , "Alerta" )

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():Formula() -> NIL
*/
METHOD Formula( nRow , nCol , cValue ) CLASS TExcelScript

   IF !::lExcel
      RETURN NIL
   ENDIF

   TRY
      ::oSheet:Cells( nRow , nCol ):FormulaLocal:=cValue
   CATCH
      MsgAlert( "La formula no es correcta " + cValue , "Alerta" )
   END

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():SeekBook() -> lRet
*/
METHOD SeekBook( cBook )
   LOCAL lRet := FALSE

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::HowBook()

   lRet := IIF( AScan( ::aBook , cBook ) > 0 , TRUE,  FALSE )

RETURN lRet

/*-------------------------------------------------------------------------------------------------*/
/*
TExcelScript():HowBook() -> NIL
*/
METHOD HowBook()
   LOCAL nBooks := ::nBook()
   LOCAL i

   IF !::lExcel
      RETURN NIL
   ENDIF

   ::aBook := {}

   FOR i := 1 TO nBook
       AAdd( ::aBooks, ::oBook:Item( i ):Name )
   NEXT

RETURN NIL

/*-------------------------------------------------------------------------------------------------*/
/*EOF*/
/*-------------------------------------------------------------------------------------------------*/