Histórico da Página

#include 'protheus.ch'

function u_basicoxlsx()
    local oXlsx as object
    local cFilename as character
    // local oFileW as object
    local lRet as logical

    cFilename := '/system/'+cValToChar(ThreadId())+'_teste_poc.rel'
    ferase(cFilename)

    // oFileW := FwFileWriter():New(cFilename)
    oXlsx := FwPrinterXlsx():New()

    // lRet := oXlsx:Activate(cFilename, oFileW)
    lRet := oXlsx:Activate(cFilename)

    lRet := oXlsx:AddSheet("1 - Plan")

    lRet := oXlsx:SetText(1, 1, "Valor na célula")
    lRet := oXlsx:toXlsx()

    oXlsx:DeActivate()
Return

#include "protheus.ch"
#include "fileio.ch"

function u_fwprtxlsx()
    classe()
    alert('terminou')
return

static function classe()
    local cRootPath := 'C:\Especif\Protheus12\sistemico\protheus_data_27'
    local cPath := "\spool\"  // /spool/ para uma geração no server
    local cArquivo := cPath + "xls_class_rel.rel"
    local cImgRel := 'logo'
    local cImgDir := cRootPath + cPath + 'protheus_logo.png'

    local cBuffer:= ""

    local lRet := .F.
    local oFileW := FwFileWriter():New(cArquivo)
    local oXlsx := FwPrinterXlsx():New()
    local oCellHorAlign := FwXlsxCellAlignment():Horizontal()
    local oCellVertAlign := FwXlsxCellAlignment():Vertical()

    local nHndImagem:= fOpen(cImgDir, FO_READ)
    local nLenImagem := 0

    if nHndImagem < 0
        return MsgStop("Não foi possível abrir " + cImgDir)
    endIf

    nLenImagem := fSeek( nHndImagem, 0, FS_END)
    fSeek( nHndImagem, 0, FS_SET)
    fRead( nHndImagem, @cBuffer, nLenImagem)

    lRet := oXlsx:Activate(cArquivo, oFileW)

    lRet := oXlsx:AddSheet("Minha Plan1")

    lRet := oXlsx:AddImageFromBuffer(5, 8, cImgRel, cBuffer, 0, 0)

    lRet := oXlsx:AddImageFromAbsolutePath(10, 8, cImgDir, 200, 100)

    lRet := oXlsx:UseImageFromBuffer(20, 8, cImgRel, 114, 33)

    // cFont := FwPrinterFont():getFromName("Calibri")
    cFont := FwPrinterFont():Calibri()
    nSize := 14
    lItalic := .T.
    lBold := .T.
    lUnderlined := .T.
    // Comando 'Fonte' com Calibri 14, itálico, negrito e sublinhado
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    // Comando 'Formato de Célula' com cor de texto e fundo personalizadas
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "FF0000", "C0C0C0", "" )

    nColFrom := 1
    nColTo := 5
    nWidth := 24
    lRet := oXlsx:SetColumnsWidth(nColFrom, nColTo, nWidth)

    nRow := 1
    nCol := 1
    // Texto em A1
    lRet := oXlsx:SetText(nRow, nCol, "Texto na célula")

    // cFont := FwPrinterFont():getFromName("Calibri")
    cFont := FwPrinterFont():Calibri()
    nSize := 11
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Calibri 11
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    lRet := oXlsx:ResetCellsFormat()

    nRow := 2
    nCol := 1
    lRet := oXlsx:SetText(nRow, nCol, "00123")

    nRow := 3
    nCol := 1
    // Número 008
    lRet := oXlsx:SetNumber(nRow, nCol, 8)

    nRow := 4
    nCol := 1
    // Número 04
    lRet := oXlsx:SetNumber(nRow, nCol, 4)

    nRow := 5
    nCol := 1
    // Fórmula que soma os dois números anteriores
    lRet := oXlsx:SetFormula(nRow, nCol, "=SUM(A3:A4)")

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "#,##0"
    // Comando 'Formato de Célula' com cor de texto e fundo personalizadas
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 4
    nCol := 2
    // Número com formato customizado (123.123.123)
    lRet := oXlsx:SetNumber(nRow, nCol, 123123123)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "0.00%"
    // Comando 'Formato de Célula' com cor de texto e fundo personalizadas
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 4
    nCol := 3
    // Número com formato customizado (4,27%)
    lRet := oXlsx:SetNumber(nRow, nCol, 0.0427)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "R$ #,##0.00;[Red]-R$ #,##0.00"
    // Seta formato numérico R$ #,##0.00;[Red]-R$ #,##0.00
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 4
    nCol := 4
    // Número com formato customizado (R$ 1234,56)
    lRet := oXlsx:SetNumber(nRow, nCol, 1234.56)

    nRow := 4
    nCol := 5
    // Número com formato customizado (R$ 1234,56)
    lRet := oXlsx:SetNumber(nRow, nCol, -1234.56)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "dd/mm/yyyy"
    // Seta formato de data dd/mm/yyyy
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 6
    nCol := 1
    dValue := STOD("20190823")
    // Data "ano, mês, dia, hora, minuto, segundo" no padrão: yyyy,mm,dd,hh,mm,ss.sss
    lRet := oXlsx:SetDate(nRow, nCol, dValue)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "hh:mm"
    // Seta formato de hora hh:mm
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 6
    nCol := 2
    oXlsxDate := FwXlsxDateFormat():New()
    oXlsxDate:SetHour("17")
    oXlsxDate:SetMinute("55")
    cValue := oXlsxDate:toPrinterFormat()
    // 17:55
    lRet := oXlsx:SetDateTime(nRow, nCol, cValue)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "dd/mm/yy hh:mm:ss.000"
    // Seta formato de data e hora dd/mm/yy hh:mm:ss.000
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 6
    nCol := 3
    oXlsxDate := FwXlsxDateFormat():New()
    oXlsxDate:SetYear("2019")
    oXlsxDate:SetMonth("8")
    oXlsxDate:SetDay("23")
    oXlsxDate:SetHour("17")
    oXlsxDate:SetMinute("55")
    oXlsxDate:SetSeconds("43.123")
    cValue := oXlsxDate:toPrinterFormat()
    // data e hora completas
    lRet := oXlsx:SetDateTime(nRow, nCol, cValue)

    nRow := 6
    nCol := 4
    oXlsxDate := FwXlsxDateFormat():New()
    oXlsxDate:SetYear("2019")
    oXlsxDate:SetMonth("8")
    oXlsxDate:SetDay("23")
    oXlsxDate:SetHour("17")
    oXlsxDate:SetMinute("55")
    oXlsxDate:SetSeconds("43.123")
    cValue := oXlsxDate:toPrinterFormat()
    // data e hora completas
    lRet := oXlsx:SetDateTime(nRow, nCol, cValue)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := "mmm-yyyy"
    // Seta formato de data mmm-yyyy
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 6
    nCol := 5
    dValue := STOD("20190823")
    // Data "ano, mês, dia, hora, minuto, segundo" no padrão: yyyy,mm,dd,hh,mm,ss.sss
    lRet := oXlsx:SetDate(nRow, nCol, dValue)

    // cFont := FwPrinterFont():getFromName("Calibri")
    cFont := FwPrinterFont():Calibri()
    nSize := 11
    lItalic := .F.
    lBold := .F.
    lUnderlined := .T.
    // Calibri sublinhada para url
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .F.
    nRotation := 0
    cCustomFormat := ""
    // Seta formato com texto azul
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "0000FF", "FFFFFF", cCustomFormat)

    nRow := 7
    nCol := 1
    // url
    lRet := oXlsx:SetHyperlink(nRow, nCol, "http://www.totvs.com.br")

    nRow := 8
    nCol := 1
    // url
    lRet := oXlsx:SetHyperlink(nRow, nCol, "http://www.google.com")
    // Texto de exibição da url inserida no comando anterior
    lRet := oXlsx:SetText(nRow, nCol, "Google")

    nRow := 9
    nCol := 1
    // URIs locais são suportadas para referências
    lRet := oXlsx:SetHyperlink(nRow, nCol, "internal:'Minha Plan1'!A2")

    nRow := 10
    nCol := 1
    // URIs locais são suportadas para referências
    lRet := oXlsx:SetHyperlink(nRow, nCol, "internal:'Minha Plan2'!B2")

    // cFont := FwPrinterFont():getFromName("Calibri")
    cFont := FwPrinterFont():Calibri()
    nSize := 11
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Calibri 11
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    nRow := 11
    nCol := 1
    // lógico
    lRet := oXlsx:SetBoolean(nRow, nCol, .T.)

    nRow := 11
    nCol := 2
    // lógico
    lRet := oXlsx:SetBoolean(nRow, nCol, .F.)

    cHorAlignment := oCellHorAlign:Fill()
    cVertAlignment := oCellVertAlign:Justify()
    lWrapText := .T.
    nRotation := 0
    cCustomFormat := ""
    // Formato
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "FF00FF", "808080", cCustomFormat)

    lTop := .T.
    lBottom := .T.
    lLeft:= .T.
    lRight := .T.
    cStyle := FwXlsxBorderStyle():DashDot()
    cColor := "008000"
    // Borda
    lRet := oXlsx:SetBorder(lLeft, lTop, lRight, lBottom, cStyle, cColor)

    nRow := 12
    nCol := 1
    // blank - Usado somente para aplicar a formatação
    lRet := oXlsx:ApplyFormat(nRow, nCol, "")

    nRow := 12
    nCol := 2
    // blank - Usado somente para aplicar a formatação ??? o que acontece quando tem valor ???
    lRet := oXlsx:ApplyFormat(nRow, nCol, "1")

    // cFont := FwPrinterFont():getFromName('code128b')
    cFont := FwPrinterFont():Code128b()
    nSize := 50
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Fonte Code 128 (para código de barras)
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    nRow := 2
    nCol := 13
    // Texto para o código de barras Code128 (o texto é codificado pela FWMSPrinter)
    lRet := oXlsx:SetText(nRow, nCol, "Ò,BX'hÓ")

    // cFont := FwPrinterFont():getFromName('qrcode')
    cFont := FwPrinterFont():QrCode()
    nSize := 50
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Fonte QRCode (para código de barras 2D)
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    nRow := 6
    nCol := 13
    // Texto para o código de barras QRCode
    lRet := oXlsx:SetText(nRow, nCol, "QRCode gerado para o Excel")

    // cFont := FwPrinterFont():getFromName('datamatrix')
    cFont := FwPrinterFont():DataMatrix()
    nSize := 50
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Fonte DataMatrix (para código de barras 2D)
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    nRow := 11
    nCol := 13
    // Texto para o código de barras Datamatrix
    lRet := oXlsx:SetText(nRow, nCol, "Datamatrix gerado para o Excel")

    // cFont := FwPrinterFont():getFromName('PDF417')
    cFont := FwPrinterFont():PDF417()
    nSize := 300
    lItalic := .T.
    lBold := .F.
    lUnderlined := .F.
    // Fonte PDF417 (para código de barras 2D)
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    nRow := 16
    nCol := 13
    // Texto para o código de barras PDF417
    lRet := oXlsx:SetText(nRow, nCol, "PDF417 gerado para o Excel")

    // cFont := FwPrinterFont():getFromName('calibri')
    cFont := FwPrinterFont():Calibri()
    nSize := 11
    lItalic := .F.
    lBold := .F.
    lUnderlined := .F.
    // Calibri 11
    lRet := oXlsx:SetFont(cFont, nSize, lItalic, lBold, lUnderlined)

    // Nova página
    lRet := oXlsx:AddSheet("Minha Plan2")

    cHorAlignment := oCellHorAlign:Center()
    cVertAlignment := oCellVertAlign:Center()
    lWrapText := .F.
    nRotation := 270
    cCustomFormat := ""
    // Seta texto vermelho com alinhamento horizontal e vertical centralizado e com rotação de texto vertical
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "FF0000", "FFFFFF", cCustomFormat)

    nRowFrom := 2
    nColFrom := 2
    nRowTo := 21
    nColTo := 3
    // Mescla intervalo B2:C21
    lRet := oXlsx:MergeCells(nRowFrom, nColFrom, nRowTo, nColTo)

    nRow := 2
    nCol := 2
    // Texto das células mescladas (apontando sempre para a primeira célula do intervalo)
    lRet := oXlsx:SetText(nRow, nCol, "Células Mescladas")

    lTop := .T.
    lBottom := .T.
    lLeft:= .F.
    lRight := .F.
    cStyle := FwXlsxBorderStyle():Medium()
    cColor := "0000FF"
    // Borda azul, superior e inferior
    lRet := oXlsx:SetBorder(lLeft, lTop, lRight, lBottom, cStyle, cColor)

    cHorAlignment := oCellHorAlign:Default()
    cVertAlignment := oCellVertAlign:Default()
    lWrapText := .T.
    nRotation := 0
    cCustomFormat := ""
    // Seta texto texto com quebra de linha
    lRet := oXlsx:SetCellsFormat(cHorAlignment, cVertAlignment, lWrapText, nRotation, "000000", "FFFFFF", cCustomFormat)

    nRow := 24
    nCol := 2
    // Texto da célula com borda
    lRet := oXlsx:SetText(nRow, nCol, "Texto com quebra de linha")

    nRowFrom := 26
    nRowTo := 30
    nHeight := 18.75
    // Altura 18,75 nas linhas 26 a 30
    lRet := oXlsx:SetRowsHeight(nRowFrom, nRowTo, nHeight)
    // Largura 23,71
    lRet := oXlsx:SetColumnsWidth(5, 10, 23.71)

    lRet := oXlsx:ResetBorder()
    // Limpa o formato
    lRet := oXlsx:ResetCellsFormat()
    // Nova planilha
    lRet := oXlsx:AddSheet("AutoFiltro")

    // Pequena planilha para testar o AutoFiltro
    lRet := oXlsx:SetValue(1, 1, "Produto") // A1
    lRet := oXlsx:SetValue(1, 2, "Mês")
    lRet := oXlsx:SetValue(1, 3, "Total")
    lRet := oXlsx:SetValue(2, 1, 1)
    lRet := oXlsx:SetValue(3, 1, 1)
    lRet := oXlsx:SetValue(4, 1, 2)
    lRet := oXlsx:SetValue(5, 1, 2)
    lRet := oXlsx:SetValue(6, 1, 3)
    lRet := oXlsx:SetValue(7, 1, 3)
    lRet := oXlsx:SetValue(2, 2, "Janeiro")
    lRet := oXlsx:SetValue(3, 2, "Março")
    lRet := oXlsx:SetValue(4, 2, "Janeiro")
    lRet := oXlsx:SetValue(5, 2, "Março")
    lRet := oXlsx:SetValue(6, 2, "Fevereiro")
    lRet := oXlsx:SetValue(7, 2, "Março")
    lRet := oXlsx:SetValue(2, 3, 100)
    lRet := oXlsx:SetValue(3, 3, 150)
    lRet := oXlsx:SetValue(4, 3, 200)
    lRet := oXlsx:SetValue(5, 3, 150)
    lRet := oXlsx:SetValue(6, 3, 100)
    lRet := oXlsx:SetValue(7, 3, 100) // C7

    // Aplica auto filtro no intervalo A1:C7
    lRet := oXlsx:ApplyAutoFilter(1,1,7,3)

    lRet := oXlsx:toXlsx()

return