saulogm/advpl-excel/advpl-excel-xlsx

advpl-excel: gerar Excel com custom.excel.Xlsx

A biblioteca advpl-excel (TLPP, namespace custom.excel) grava o XML do .xlsx direto, sem precisar de Excel no servidor. A classe principal é custom.excel.Xlsx. YExcel (arquivo YEXCEL.prw) é só uma subclasse de compatibilidade (Class YExcel From custom.excel.Xlsx): tem os mesmos métodos e parâmetros, e existe apenas para não quebrar código legado.

1. Decisão inicial

Antes de alterar um fonte existente, veja se o usuário fez alterações manuais (git diff/status). Se algo parecer errado, pergunte antes de "corrigir".

Se o projeto tiver src/custom.excel.tlpp, confira a assinatura de métodos pouco comuns com Grep "^Method NomeMetodo\(" (use -i). O código é a fonte da verdade: se a wiki divergir dele, siga o código.

2. Escolha do cTipo no New

custom.excel.Xlsx():New(cNomeFile, cFileOpen, cTipo)

New(cFileOpen = "c:\temp\arq.xlsx") abre um arquivo existente. Se o arquivo tiver senha, use New() seguido de Open(cArq, cSenha), porque New(cFileOpen) gera exceção em arquivo criptografado.

3. Template base (TLPP)

#include "tlpp-core.th"
#include "protheus.ch"
Namespace custom.<modulo>
 
/*/{Protheus.doc} RelExcel
Gera relatório em Excel ...
@type function
@author ...
@since dd/mm/aaaa
@version 1.0
@return character, caminho do arquivo gerado
/*/
User Function RelExcel() As Character
	Local oExcel As Object
	Local oStyCab As Object
	Local oStyMoeda As Object
	Local nFonteCab As Numeric
	Local nCorCab As Numeric
	Local nBorda As Numeric
	Local nLin As Numeric
	Local cArquivo As Character
	Local cAlias As Character
 
	oExcel		:= custom.excel.Xlsx():New(cNomeFile = "RelExcel", cTipo = "A")
 
	//Estilos (criar antes de escrever as células)
	nFonteCab	:= oExcel:AddFont(nTamanho = 11, xCorRGB = "FFFFFFFF", cNome = "Calibri", cfamily = "2", lNegrito = .T.)
	nCorCab		:= oExcel:CorPreenc("FF4F81BD")
	nBorda		:= oExcel:Borda("ALL")
	oStyCab		:= oExcel:NewStyle():Setfont(nFonteCab):Setfill(nCorCab):Setborder(nBorda):SetaValores({oExcel:Alinhamento(cHorizontal = "center", cVertical = "center")})
	oStyMoeda	:= oExcel:NewStyle():SetnumFmt(44):Setborder(nBorda)	//44 = moeda R$
 
	oExcel:ADDPlan(cNome = "Dados")
	oExcel:AddTamCol(nMin = 1, nMax = 1, nWidth = 12)
	oExcel:AddTamCol(nMin = 2, nMax = 2, nWidth = 40)
	oExcel:AddTamCol(nMin = 3, nMax = 3, nWidth = 16)
	oExcel:AddPane(nySplit = 1, nxSplit = 0)	//Congela a linha 1
 
	//Cabeçalho (linha 1, colunas em ordem crescente)
	oExcel:Pos(1,1):SetValue("Código"):SetStyle(oStyCab)
	oExcel:Pos(1,2):SetValue("Descrição"):SetStyle(oStyCab)
	oExcel:Pos(1,3):SetValue("Valor"):SetStyle(oStyCab)
 
	cAlias	:= MpSysOpenQuery("SELECT ... FROM " + RetSqlName("SB1") + " WHERE D_E_L_E_T_ = ' '")
	nLin	:= 1
	While (cAlias)->(!Eof())
		nLin++
		oExcel:Pos(nLin,1):SetValue(AllTrim((cAlias)->B1_COD))
		oExcel:Pos(nLin,2):SetValue(AllTrim((cAlias)->B1_DESC))
		oExcel:Pos(nLin,3):SetValue((cAlias)->B1_PRV1):SetStyle(oStyMoeda)
		(cAlias)->(DbSkip())
	EndDo
	(cAlias)->(DbCloseArea())
 
	oExcel:AutoFilter(nLinha = 1, nColuna = 1, nLinha2 = Max(nLin,2), nColuna2 = 3)
 
	cArquivo	:= oExcel:Save(GetTempPath())	//Retorna o caminho do arquivo gerado
	oExcel:OpenApp()						//Só com interface (SmartClient); nunca em job/schedule
	oExcel:Destroy()						//Libera objetos internos e temporários do servidor
	FreeObj(oExcel)
Return cArquivo

Um relatório com muitas colunas vindas de uma query costuma ficar mais simples com Alias2Tab (a coluna, o título e a máscara vêm da SX3). Veja references/tabelas-bulk-alias.md.

4. Regras que evitam erro

Cada regra existe porque o erro correspondente acontece na prática:

• No tipo "A", escreva em ordem. Linha 1 → 2 → 3, e dentro da linha, coluna 1 → 2 → 3. Com duas tabelas lado a lado, preencha linha por linha e não tabela por tabela (é o que fazem os exemplos de gráfico). Se a lógica precisar voltar, use cTipo = "M".
• Fórmula: nomes das funções em inglês, , como separador e sem = inicial. O valor é calculado pela própria classe no Save/Save2Html (motor AdvPL com as funções do Excel): SetValue(, "SUM(A2:A3)"). Se xValor for informado, ele é mantido sem recalcular. O que não puder ser calculado (funções do ambiente do Excel, como CUBE*/RTD/WEBSERVICE) fica para o Excel recalcular ao abrir. Detalhes, AddFuncFormula, SetCalcLocal e a lista de funções em references/formulas.md. Para montar referências use oExcel:Ref(nLin,nCol) (Ref(3,3,.T.,.T.) retorna $C$3).
• Fórmula em português e L1C1: oExcel:Formula(cFormula, lPtBr = .T., lL1C1 = .T.) prepara a fórmula escrita como no Excel em português (SOMA, SE, PROCV, ; entre argumentos, , decimal) e com referências L1C1 (L2C3 absoluta, L[-1]C relativa à célula, L/C omitidos = linha/coluna atual). Ela é convertida para inglês/A1 no SetValue/Cell, relativa à célula posicionada, então o mesmo objeto serve para todas as linhas de um laço: oFml := oExcel:Formula('SE(LC[-1]>0;LC[-2]/LC[-1];0)') → oExcel:Cell(nLin,5,,oFml) grava IF(D2>0,C2/D2,0), IF(D3>0,C3/D3,0)... Libere com FreeObj(oFml) após o laço. lPtBr = .F. para nomes em inglês e lL1C1 = .F. para referências A1. Detalhes em references/formulas.md.
• Nome definido (AddNome) deve existir antes da fórmula que o usa.
• Estilos são criados uma vez e reutilizados. Cada NewStyle/AddFont/CorPreenc dentro de um laço infla o styles.xml. Para variar por linha ou coluna use herança (NewStyle(oPai)) ou regras (NewStyRules).
• Nomes reais dos parâmetros: AddFont(..., xCorRGB = ...) (mas Font(..., cCorRGB = ...) na formatação condicional), AddStyles(..., nborderId = ...), Cell(..., xStyle = ...) e Borda(cTipo = "ALL"|"T"|"B"|"L"|"R"|combinação).
• Parâmetros que confundem:
• CorPreenc(cFgCor, cBgCor) recebe a cor de frente primeiro. Preenc(cBgCor, cFgCor), usado na formatação condicional, recebe o fundo primeiro.
• As letras de borda também mudam: Borda usa T/B/L/R e ObjBorda usa C/B/E/D.
• Cores: ARGB ("FF4F81BD") ou RGB ("4F81BD").
• Um único `AutoFilter` por planilha. Uma tabela formatada usa oTab:AddFilter() em vez dele.
• Tabela formatada: oTab:Finish() é obrigatório. AddTotal vem antes de AddTotais(). Use AddColumn(cNome, nStyle, cNameField) para dar um estilo padrão e um apelido à coluna. Depois de um oTab:Alias2Tab, Cell e AddTotal aceitam o nome do campo. O estilo de cabeçalho de um campo vai em NewFldTab(..., xStyleCab = oSty) (ou jCab[campo]["stylecab"]).
• Bulk: FlushBulk() antes do Save.
• Subtotais: DefSubTotal/AddSubTotal antes do Alias2Tab.
• Agrupamento: SetsumRight antes de SetColLevel. NivelLinha(n) vale para as linhas seguintes até NivelLinha() sem argumentos.
• Combo: só ChartBar, ChartLine e ChartArea sem 3D podem ser membros, e eles são criados com New(oExcel) (New(Nil) falha).
• Gráfico: cada gráfico recebe Insert(...) uma única vez, antes do Save. Set3D só existe para barras, linhas, pizza, área e superfície; nos outros gera exceção.
• Html (`Save2Html`): o html mostra o valor das fórmulas calculado pelo motor AdvPL (ou o xValor informado) e recalcula no navegador quando um input muda (funções extras via formula.js, oHtml:SetFormulaJs(cUrl) sem internet). No tipo "A", o primeiro Save/Save2Html encerra a gravação. As linhas e imagens ficam preservadas após o Save até o Destroy, então Save2Html pode vir depois do Save sem NewHtml() (o NewHtml() serve só para configurar o html). Não chame FreeObj no objeto do NewHtml() (o Destroy libera). Detalhes em references/html.md.
• Json/csv (`Save2Json`/`Save2Csv`): mesmas regras de ordem do Save2Html. Save2Csv retorna um array (um arquivo por planilha); o json padrão é "registros" (1ª linha com valor = chaves, use oDados:SetLinhaCab(n) quando há título acima). Detalhes em references/json-csv.md.
• Datas: SetValue(date()) grava uma data. Para data e hora use SetValue(oExcel:GetDateTime(dData = dDate, cTime = cHora)) ou SetDateTime(dDate, cTime). Na leitura, uma célula de data e hora retorna um objeto DateTime (:GetDate(), :GetTime()).

5. Ciclo de vida e liberação de memória

New → (estilos) → ADDPlan → escrever → Save(cLocal) → [OpenApp()] → Destroy() → FreeObj(oExcel)

Para gerar também o html: oHtml := oExcel:NewHtml() (opcional, para configurar), depois oExcel:Save2Html(cLocal, oHtml) antes ou depois do Save, sempre antes do Destroy(). OpenApp() abre o último arquivo gerado. Json e csv seguem o mesmo padrão: oDados := oExcel:NewDados() (opcional), oExcel:Save2Json(cLocal, oDados) / oExcel:Save2Csv(cLocal, oDados).

• Save(cLocal) retorna o caminho do arquivo. Se cLocal for uma pasta local (ex.: GetTempPath()), o arquivo é copiado para o client. Um caminho do servidor (ex.: "\temp") mantém o arquivo no servidor.
• OpenApp() faz ShellExecute no client. Não o use em job, schedule, REST ou RpcSetEnv sem interface.
• Destroy() (equivale a Close()) remove as pastas e tabelas temporárias e libera tudo o que a instância criou e registrou em aCleanObj: estilos (NewStyle), regras (NewStyRules, NewRuleLine), tabelas (AddTabela), gráficos e séries (NewChart/AddSerie e classes ChartX():New(oExcel)), campos de bulk, jCab passado ao NewFldTab e objetos DateTime. Não chame `FreeObj` nesses objetos depois do `Destroy`, porque seria liberar duas vezes. Depois do Destroy chame apenas FreeObj(oExcel).
• Arrays locais criados por você (aCampos, aOnlyFieds, arrays de dados) devem ser liberados com FwFreeArray(). Objetos ou JSON criados por você e não entregues à lib devem ser liberados com FreeObj(). Faça isso em todos os fluxos de saída, inclusive no Catch/tratamento de erro, e sem liberar variável não inicializada.
• Havendo tratamento de erro, garanta o Destroy() também no caminho de erro, senão ficam temporários em \tmpxls\ no servidor:

Try
	...
	cArquivo := oExcel:Save(GetTempPath())
Catch oErro
	ConOut("Erro ao gerar Excel: " + oErro:Description)
EndTry
If oExcel <> Nil
	oExcel:Destroy()
	FreeObj(oExcel)
EndIf

• Gravar(cLocal, lAbrir, lDelSrv) é da API v1 e faz Save + OpenApp opcional + Close numa chamada. Em código novo prefira a sequência explícita.

6. Encoding dos fontes gerados