Gerar planilha no OpenOffice

zekasan · April 6, 2014

Alguma alma caridosa pode me ajudar com esta proeza?

Agradeceria muito.

Jmsilva · April 7, 2014

/*Autor: Jose Maria da Silva - nick JMSILVA
** Termo uso: livre, preservar a autoria
*/


#include "FiveWin.ch"
//#include "constant.ch"
#DEFINE CH_PICTMIX 0       //QQ DADO
#DEFINE CH_PICTDTA 36      //DATA 36=MMDDAAAA 37=MMDDAA
#DEFINE CH_PICTINT 1       //NUM INT
#DEFINE CH_PICTSTR 100     //STRING
#DEFINE CH_PICTDNH 104      //NUM R$
#DEFINE CH_PICTPOR 11      //% 99.99
#DEFINE CH_PICTD02 2       //#,00
#DEFINE CH_PICTD03 156       //#,##0
//----------------------------------------------------------------------------//

**==============================================================================
CLASS TCalc //FROM TControl
**==============================================================================
  DATA oService,;
       oArquivo,;
       oDesktop,;
       oVista,;
       oAllPlan,;
       oPlan,;
       oColl

   DATA nPlanSele //PROTECTED
   DATA cFile //PROTECTED
   DATA cFileOut  INIT "Plan_"+STRZERO(HB_RandomInt(999),3) PROTECTED  //so nome
   DATA cPathOut  INIT "C:\CORREIO"


   METHOD New( lHide ) CONSTRUCTOR
   METHOD Open( lHide, cFILE ) CONSTRUCTOR
   METHOD Plan( lHide, cFILE ) CONSTRUCTOR

   METHOD Close() INLINE ::oArquivo:Close(.T.)

   METHOD SetText( cCol, nRow, cText, nFont, nAlign )

   METHOD SetDate( cCol, nRow, dData, nFont, nAlign )

   METHOD SetValor( cCol, nRow, nValor, nFont, nAlign )

   METHOD SetLongLat( cCol, nRow, cGPS, nFont, nAlign )

   METHOD CharColor( cCol, nRow, nRGB )    //cor letra

   METHOD BrushColor( cCol, nRow, nRGB )   //cor do fundo

   METHOD MesclarCell( cCell )   //A1:C2

   METHOD FileOut( )
   //alinhamento vertical e horizontal
   //get
   //salvar  xls,ods,pdf
   METHOD Salvar_ODS()
   METHOD Salvar_XLS()
   METHOD Salvar_PDF()

   //borda
   //falta o decimal numero
   METHOD ArrayPlan()


ENDCLASS

//----------------------------------------------------------------------------//
**==============================================================================
METHOD New( lHide ) CLASS TCalc
**==============================================================================
   LOCAL aProp:={}

   DEFAULT lHide := .T.

   TRY //isto é show
     ::oService := TOleAuto():New("com.sun.star.ServiceManager")
   CATCH
     RETURN NIL
   END
   // inicializa processo de criar planilha A1=0,0, B1=0,1
   ::oDesktop := ::oService:createInstance("com.sun.star.frame.Desktop")
   IF lHide
      AAdd(aProp,MakeProperty(::oService,"Hidden",.T.))    //oculta
   ENDIF
   ::oArquivo := ::oDesktop:loadComponentFromURL("private:factory/scalc","_blank",0,aProp)
   ::oVista = ::oArquivo:getCurrentController()  //obj controlador

   // obtém planilhas
   ::oAllPlan := ::oArquivo:GetSheets()
   ::oAllPlan:insertNewByName("Plan_SAA",0)  //inseri uma nova planilha
   ::oPlan := ::oAllPlan:GetByIndex(0)
   ::nPlanSele := 0
   ::oColl := ::oPlan:getColumns()  //obtem as colunas
   //::cFileOut := STRZERO(HB_RandomInt(999),3)
   ::cFile    := "C:\CORREIO\PLAN_"+::cFileOut

return Self


**==============================================================================
METHOD Open(lHide,cFile ) CLASS TCalc
**==============================================================================
   LOCAL aProp:={},cTemp

   DEFAULT lHide := .T.

   IF EMPTY(cFile) .OR. !FILE(cFile)
      RETURN NIL
   ENDIF

   TRY //isto é show
     ::oService := TOleAuto():New("com.sun.star.ServiceManager")
   CATCH
     RETURN NIL
   END

   cTemp := "file:///"+StrTran(cFile,"\","/")

   // inicializa processo de criar planilha A1=0,0, B1=0,1
   ::oDesktop := ::oService:createInstance("com.sun.star.frame.Desktop")
   IF lHide
      AAdd(aProp,MakeProperty(::oService,"Hidden",.T.))    //oculta
   ENDIF
   ::oArquivo := ::oDesktop:loadComponentFromURL(cTemp,"_blank",0,aProp)
   ::oVista   := ::oArquivo:getCurrentController()  //obj controlador

   // obtém planilhas
   ::AllPlan := ::oArquivo:GetSheets()
   ::oPlan   := ::oAllPlan:GetByIndex(0)
   ::nPlanSele := 0
   ::oColl := ::oPlan:getColumns()  //obtem as colunas
   ::cFile := cFile   //drive,path,nome
   //::cFileOut := STRZERO(HB_RandomInt(999),3)

return Self

**==============================================================================
METHOD Plan( nId ) CLASS TCalc
**==============================================================================
   nId := MAX(0,nId-1)
   ::oPlan := ::oAllPlan:GetByIndex(nId)
   ::nPlanSele := nId
   ::oColl := ::oPlan:getColumns()  //obtem as colunas
   ::oVista:setActiveSheet(::oPlan)  //ativa a planilha
return Self

**==============================================================================
METHOD SetText(cCol, nRow, cText, nFont, nAlign ) CLASS TCalc
**==============================================================================
   local oCell,nCOL
   DEFAULT nFont := 12, nAlign := 1

   if empty(cCol) .or. empty(nRow) .or. empty(cText)
      return .f.
   endif
   cText := StrTran(cText,"§", "º ")
   cText := StrTran(cText,"¦", "ª ")

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:SetString(cText)  //grava na celula (a:1)
   oCell:CharHeight := nFont
   oCell:HoriJustify := nAlign

return .t.

**==============================================================================
METHOD SetDate(cCol, nRow, dData, nFont, nAlign ) CLASS TCalc
**==============================================================================
   local oCell,nCOL
   DEFAULT nFont := 12, nAlign := 2

   if empty(cCol) .or. empty(nRow) .or. empty(dData)
      return .f.
   endif

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:SetFormula(FORMAT_DATA(dData)) //oCell:SetFormula := FORMAT_DATA(dData)
   oCell:NumberFormat := CH_PICTDTA
   oCell:CharHeight   := nFont
   oCell:HoriJustify  := nAlign
   ::oColl:GetByName(cCol):Width := 2500

return .t.

**==============================================================================
METHOD SetValor(cCol, nRow, nValor, nFont, nAlign ) CLASS TCalc
**==============================================================================
   local oCell,nCOL
   DEFAULT nFont := 12, nAlign := 3, nValor := 0

   if empty(cCol) .or. empty(nRow) //.or. empty(nValor)
      return .f.
   endif

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:SetValue(nValor)
   oCell:NumberFormat := CH_PICTD03  //156
   oCell:CharHeight   := nFont
   oCell:HoriJustify  := nAlign
   ::oColl:GetByName(cCol):Width := 2000

return .t.

**==============================================================================
METHOD SetLongLat(cCol, nRow, cGPS, nFont, nAlign ) CLASS TCalc
**==============================================================================
   local oCell,nCOL
   DEFAULT nFont := 12, nAlign := 2

   if empty(cCol) .or. empty(nRow) .or. empty(cGPS)
      return .f.
   endif

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:SetString(TRANSFORM(cGPS,"@R 99º 99' 99.99''"))
   oCell:CharHeight   := nFont
   oCell:HoriJustify  := nAlign
   ::oColl:GetByName(cCol):Width := 3000

return .t.

**==============================================================================
METHOD CharColor(cCol, nRow, nCor ) CLASS TCalc
**==============================================================================
   local oCell,nCOL

   if empty(cCol) .or. empty(nRow) .or. empty(nCor)
      return .f.
   endif

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:CharColor := nCOR                    //cor da letra

return .t.

**==============================================================================
METHOD BrushColor(cCol, nRow, nCor ) CLASS TCalc
**==============================================================================
    local oCell,nCOL

   if empty(cCol) .or. empty(nRow) .or. empty(nCor)
      return .f.
   endif

   nCOL := ASC(cCOL) - 65
   oCell := ::oPlan:getCellByPosition(nCOL,nROW-1)  //coluna, linha - posiciona
   oCell:CellBackColor := nCOR                   //cor da letra

return .t.

**==============================================================================
METHOD MesclarCell(cCell ) CLASS TCalc
**==============================================================================
   local oRange

   if empty(cCell) .or. at(":",cCell) == 0
      return .f.
   endif

   oRange = ::oPLAN:getCellRangeByName(cCell) //Mesclar
   oRange:Merge(.T.)

return .t.

**==============================================================================
METHOD ArrayPlan() CLASS TCalc
**==============================================================================
   local aNomePlan:={},nCT,oPlan

   FOR nCT:=1 TO ::oAllPlan:Count
       oPlan := ::oAllPlan:getByIndex(nCT-1)
       AADD(aNomePlan,oPlan:GetName())
   NEXT
return aNomePlan

**==============================================================================
METHOD SALVAR_ODS() CLASS TCalc
**==============================================================================
   LOCAL cFILE
   cFILE := "file:///"+StrTran(::cPathOut,"\","/")+"/"+::cFileOut+".ODS"

   ::oArquivo:storeToURL(cFILE, {})

return nil

**==============================================================================
METHOD SALVAR_XLS() CLASS TCalc
**==============================================================================
   local cFILE,oTip
   cFILE := "file:///"+StrTran(::cPathOut,"\","/")+"/"+::cFileOut+".XLS"

   oTip := MakeProperty(::oService, "FilterName", "MS Excel 97")

   ::oArquivo:storeToURL(cFILE, {oTIP})
return nil

**==============================================================================
METHOD SALVAR_PDF() CLASS TCalc
**==============================================================================
   local cFILE,oTip, oZip

   cFILE := "file:///"+StrTran(::cPathOut,"\","/")+"/"+::cFileOut+".PDF"
   //Msgstop(Cfile)   // Cfile := "file:///c:/correio/plan_000.pdf"
   oZip := MakeProperty(::oService, "CompressMode", 1)
   oTip := MakeProperty(::oService, "FilterName", "writer_pdf_Export")

   ::oArquivo:storeToURL(cFILE, {oZip,oTip})

return nil

**==============================================================================
METHOD FileOut() CLASS TCalc
**==============================================================================
   local cFile

   cFile := ::cPathOut+"\"+::cFileOut+".ODS"

return cFile

**==============================================================================
STATIC FUNCTION FORMAT_DATA(dSTRDATA)
**==============================================================================
LOCAL cFORMULA

IF VALTYPE(dSTRDATA) != "D" .OR. EMPTY(dSTRDATA)
   RETURN("")
ENDIF

cFORMULA := "=DATE("+STRZERO(YEAR(dSTRDATA),4)+";"
cFORMULA += STRZERO(MONTH(dSTRDATA),2)+";"+STRZERO(DAY(dSTRDATA),2)+")"

RETURN(cFORMULA)

**==============================================================================
STATIC FUNCTION MakeProperty(oServ,cName,nValue)
**==============================================================================
LOCAL oStruct := oServ:Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oStruct:Name  := cName
oStruct:Value := nValue
RETURN(oStruct)

Caríssimo, veja se ajuda.

Jmsilva · April 7, 2014

**======================================

FUNCTION main()

**======================================

LOCAL oPLAN,nCOL,cFILE,aCAB:={},nLIN,nCT

LOCAL aVETSAY:={} //CARREGAR COM DADOS

aCAB := {"CODIGO","DESCRICAO","ESTOQUE","VALOR"}

oPLAN := TCalc():New()

//ENTRA METER() ---BARRA DE PROGRESSÃO

oPLAN:MesclarCell("A1:I1")

oPLAN:MesclarCell("A2:I2")

oPLAN:CharColor("A", 1, RGB(255,10,10)) //COR DA LETRA

oPLAN:CharColor("A", 2 ,RGB(255,10,10))

oPLAN:SetText("A",1,"NOME DA EMPRESA",13,2)

oPLAN:SetText("A",2,"PLANILHA ESTOQUE",13,2)

nLIN := 4

FOR nCOL:=1 TO LEN(aCAB)

oPLAN:SetText( CHR(nCOL+64), nLIN, aCAB[nCOL],12,1)

oPLAN:BrushColor( CHR(nCOL+64), nLIN, RGB(255,255,0)) //COR FUNDO

FOR nCT:=1 TO LEN(aVETSAY)

oPLAN:SetText("A" ,nLIN,aVETSAY[nCT,1])

oPLAN:SetText("B" ,nLIN,aVETSAY[nCT,2])

oPLAN:SetValor("C",nLIN,aVETSAY[nCT,3])

oPLAN:SetValor("D",nLIN,aVETSAY[nCT,4])

nLIN++ //proxima linha da planilha

//METER++

oPLAN:Salvar_ODS()

cFILE := oPLAN:FileOut()

oPLAN:Close()

//METER FINALIZA

IF FILE(cFILE)

ShellExecute(0,"open",cFILE)

ELSE

//MSG DE ERRO("")

ENDIF

RETURN NIL

zekasan · April 7, 2014

Assim que resolver um pepino no servidor da empresa vou testar, muito obrigado pelo retorno.

zekasan · April 7, 2014

Ótimo, obrigado novamente Jmsilva, quebrou uma árvore inteira.

kapiaba · April 21, 2021

Esta classe sofreu melhorias?

Obg. abs.

Jmsilva · April 22, 2021

11 horas atrás, kapiaba disse:

Esta classe sofreu melhorias?

Obg. abs.

Não me recordo, mas caso esteja com algum problema ou tem a necessidade de algum recurso que não tem, é só postar que verifico a possibilidade da implementação.

Sign In

Gerar planilha no OpenOffice

Recommended Posts

zekasan

Link to comment

Share on other sites

Jmsilva

Link to comment

Share on other sites

Jmsilva

Link to comment

Share on other sites

zekasan

Link to comment

Share on other sites

zekasan

Link to comment

Share on other sites

kapiaba

Link to comment

Share on other sites

Jmsilva

Link to comment

Share on other sites

Join the conversation

Browse

Activity