Artigo Técnico

Comparar duas pastas de trabalho do Excel no Delphi

Duas cópias da mesma pasta de trabalho trimestral podem exibir 1,250.00 na célula D2 e ainda assim discordar sobre o que aquela célula é: em uma é um literal digitado, na outra é =SUM(A2:C2). Um revisor alternando entre os arquivos vê telas idênticas. Uma comparação byte a byte dos dois pacotes .xlsx vai para o outro extremo e reporta quase tudo como diferente, porque um zip gravado duas vezes raramente coincide byte a byte. Nenhuma das duas verificações responde à pergunta que as pessoas realmente fazem depois de uma rodada de revisão: quais células, fórmulas e planilhas mudaram

Essa pergunta surge em alguns lugares previsíveis. Uma pasta de trabalho financeira volta da revisão, uma exportação noturna é regenerada depois de uma mudança de código, ou dois departamentos devolvem suas próprias edições de um mesmo modelo. Inspeção lado a lado serve para uma planilha e é inviável para vinte, e é exatamente a troca de fórmula por literal acima que um par de olhos cansado não percebe

Diagrama geral da comparação de pastas de trabalho do HotXLS no Delphi: baseline.xlsx e reviewed.xlsx alimentam o TXLSXWorkbookCompare, que emparelha planilhas pelo nome, percorre as células gravadas e emite registros de diferença estruturados mais um relatório legível
O TXLSXWorkbookCompare transforma duas pastas carregadas do HotXLS em uma lista de diffs estruturada para a lógica e um relatório legível para revisores

Comparando duas pastas de trabalho com TXLSXWorkbookCompare

O HotXLS compara pastas de trabalho por meio do TXLSXWorkbookCompare na unit lxCompare. Compare(Left, Right) recebe dois objetos TXLSXWorkbook carregados e devolve o número de diferenças encontradas. A lista de diferenças fica disponível por índice via Diff(I) e Count, e Report acrescenta uma linha legível por diferença a qualquer TStrings. O Excel não participa em nenhum momento; a comparação roda sobre o modelo de objetos que o HotXLS já carregou, então funciona igual em um job de console, um serviço ou uma aplicação C++Builder

uses
  SysUtils, Classes, lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    // Open devolve 1 em caso de sucesso; um open que falha para aqui,
    // senão uma pasta vazia é comparada e o resultado não significa nada
    if Left.Open('baseline.xlsx') <> 1 then
      raise Exception.Create('cannot open baseline.xlsx');
    if Right.Open('reviewed.xlsx') <> 1 then
      raise Exception.Create('cannot open reviewed.xlsx');

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // uma linha legível por diferença
      Lines.SaveToFile('workbook-diff.txt');
      Writeln(Format('%d difference(s) written', [Cmp.Count]));
    end;
  finally
    Lines.Free;
    Cmp.Free;
    Right.Free;
    Left.Free;
  end;
end;

As verificações explícitas de open importam mais do que parecem. Compare devolve 0 e limpa a lista quando qualquer argumento é nil, e duas pastas vazias também comparam como iguais, então um resultado zero só significa "equivalente" quando os dois arquivos realmente carregaram. Uma linha típica do workbook-diff.txt é value changed: Data!A2: 10 -> 99

O que conta como diferença por padrão?

Sem opções ligadas, o TXLSXWorkbookCompare reporta oito tipos de diferença: planilha adicionada ou removida, célula adicionada ou removida, valor alterado, fórmula alterada e intervalo mesclado adicionado ou removido. Os valores do enum são lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded e lckMergeRemoved. Tudo é medido tendo a pasta da esquerda como linha de base, então um item adicionado existe só à direita e um removido só à esquerda

Diagrama dos oito tipos padrão de diferença entre pastas de trabalho do HotXLS, agrupados em nível de planilha, célula e mesclagem, cada um medido tendo a pasta da esquerda como linha de base, com linha e coluna zero marcando entradas de nível de planilha e de mesclagem
Por padrão o HotXLS reporta oito tipos de diferença em três níveis, todos expressos contra a pasta da esquerda, então reordenar planilhas não reporta nada

Planilhas são emparelhadas pelo nome, e a comparação ignora maiúsculas e minúsculas, então Data à esquerda emparelha com DATA à direita. Arrastar uma aba para outra posição portanto não gera entradas. Renomear uma planilha é outra história: Q3 renomeada para Q3 Final aparece como uma linha sheet removed e uma sheet added, porque nada no arquivo registra que as duas são a mesma planilha. Uma planilha que existe em apenas um lado produz uma única entrada de nível de planilha em vez de uma linha por célula dentro dela, o que mantém o relatório legível quando duas pastas diferem na estrutura

Células são emparelhadas por posição. A comparação percorre as células gravadas de cada planilha emparelhada, e isso inclui células que guardam apenas formatação. Uma célula formatada mas vazia que existe à esquerda e não à direita é reportada como cell removed: Data!C4 was (blank). Intervalos mesclados comparam pelo texto A1, então A1:D1 alargado para A1:E1 rende um merge removed e um merge added

Por que fórmula e literal com o mesmo número reportam mudança?

Porque a fórmula é o conteúdo autoral. Quando qualquer lado de um par de células contém uma fórmula, o TXLSXWorkbookCompare compara o texto da fórmula, e fórmula de um lado contra literal do outro é uma entrada lckFormulaChanged mesmo que ambos exibam 1,250. A linha de relatório do exemplo de abertura é formula changed: Data!D2: 1250 -> =SUM(A2:C2), e o registro carrega LeftKind = lcvNumber e RightKind = lcvFormula. O texto da fórmula é comparado exatamente como gravado, com um sinal de igual adicionado para exibição

Valores literais comparam como valores digitados. Número compara com número, texto com texto, e o número 10 contra o texto "10" é uma mudança mesmo que ambas as células imprimam os mesmos caracteres. O relatório mostra os rótulos de tipo sempre que os tipos diferem: value changed: Data!B7: [number] 10 -> [text] 10. Esse caso é comum em arquivos que passam por idas e voltas de CSV ou copiar e colar de usuário, e é o que quebra consultas depois

Uma fórmula com texto inalterado não reporta nada por padrão, mesmo quando o resultado em cache difere. Para revisar conteúdo autoral isso está certo. Para teste de regressão de um gerador é leniente demais, porque uma planilha de entrada alterada pode mover todos os totais sem tocar em uma fórmula sequer. Ligar lcoFormulaCachedResults em Options acrescenta uma entrada lckFormulaCacheChanged sempre que os resultados em cache divergirem, ou quando um lado tem valor em cache e o outro não, o que é reportado como (missing cache). Para testes de regressão de geradores, ligue essa opção; um total que se desloca com fórmulas inalteradas é exatamente a falha que esses testes existem para pegar

Resultados em cache são tão frescos quanto o último cálculo, então recalcule as duas pastas primeiro quando a pergunta for "o que essas fórmulas produzem hoje". TXLSXWorkbook.Recalculate devolve lxOk em caso de sucesso; com iteração desligada, um ciclo de referências devolve lxErrorRef. Ruído de ponto flutuante é a outra fonte de falsos alarmes, e SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) cuida disso para literais numéricos e resultados em cache igualmente:

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // Atualize os dois caches antes de comparar resultados
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 absoluto ou 1e-12 relativo; ambos devem ser finitos e não negativos,
  // senão EArgumentException é lançada e nenhum valor muda
  Cmp.SetNumericTolerance(1e-9, 1e-12);

  Result := Cmp.Compare(Expected, Actual);
end;

As tolerâncias têm padrão zero, o que significa comparação exata. Elas se aplicam apenas a números. Datas, geometria de linhas e colunas e estilos sempre comparam exatamente, então uma tolerância ajustada para arredondamento de moeda nunca esconde uma data que se moveu por um segundo

Dirigindo a lógica pelas diferenças estruturadas

Cada registro TlxCompareDiff expõe Kind, Sheet, Row e Col base 1, um Ref em A1, LeftText e RightText e, para entradas de célula, LeftKind e RightKind. Entradas de nível de planilha e de mesclagem carregam zero em Row e Col. Com verificações opcionais ligadas, entradas de geometria de linha carregam apenas linha, entradas de geometria de coluna apenas coluna, e funcionalidades da pasta de trabalho como defined names não carregam nenhuma das duas, então ramifique por Kind em vez de coordenadas zero:

var
  I, FormulaEdits: Integer;
  D: TlxCompareDiff;
begin
  FormulaEdits := 0;
  for I := 0 to Cmp.Count - 1 do
  begin
    D := Cmp.Diff(I);
    case D.Kind of
      lckFormulaChanged:
        begin
          Inc(FormulaEdits);
          Writeln(Format('%s!%s: %s => %s',
            [D.Sheet, D.Ref, D.LeftText, D.RightText]));
        end;
      lckValueChanged:
        if D.LeftKind <> D.RightKind then
          Writeln('type change: ' + D.Describe);
      lckSheetAdded, lckSheetRemoved:
        Writeln('structure: ' + D.Describe);
      lckMergeAdded, lckMergeRemoved:
        Writeln('layout: ' + D.Describe);
    end;
  end;

  // Uma política de revisão que bloqueia apenas por edições de fórmula
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

A ordem da lista é fixa. Compare ordena as diferenças por nome de planilha, depois linha, coluna, tipo e referência, então duas execuções sobre o mesmo par de arquivos produzem o mesmo texto de relatório, linha por linha. Nomes de planilha ordenam por comparação ordinal de string, não por posição de aba, e entradas com linha zero vêm primeiro dentro do grupo de cada planilha. Isso torna o texto do relatório um 'golden file' justo para teste de regressão: versione o workbook-diff.txt esperado e faça o build falhar quando o novo divergir. Para decisões de política, leia os campos do registro diretamente e deixe a linha descritiva para humanos

Optando por verificações de formatação, layout e objetos

O conjunto padrão é uma comparação de dados, e uma célula cujo formato de número mudou de General para Currency não reporta nada sob ele. A propriedade Options amplia a comparação uma categoria por vez. lcoCellStyles compara o estilo resolvido de cada célula emparelhada e lcoBlankStyleRegions faz o mesmo para áreas formatadas que não têm células gravadas, fundindo diferenças adjacentes em retângulos cujo canto distante fica em LastRow e LastCol. lcoAxisGeometry compara alturas de linha e larguras de coluna junto com estado oculto, nível de estrutura de tópico e recolhido. lcoRichTextRuns compara runs formatados dentro de células de texto

Conteúdo de nível de objeto tem seus próprios interruptores: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (que cobre também imagens dentro da célula), lcoPivotTables, lcoPivotCaches, lcoSlicerState e lcoTimelineState. Essas entradas colocam a chave da funcionalidade em Ref e dizem por meio de Change se ela foi adicionada, removida ou alterada. Defined names, pivot caches e os caches de slicer e timeline da pasta de trabalho carregam Sheet vazio

Leia a saída dessas verificações com as expectativas certas. Entradas de estilo, rich text e funcionalidade reportam digests SHA-256 de uma definição normalizada em LeftText e RightText, então o relatório diz que 'o estilo da célula C5 mudou', mas não diz qual propriedade mudou. Entradas de geometria são legíveis, por exemplo size=15; hidden=0; outline=0; collapsed=0, e uma linha sem altura explícita compara na altura padrão da planilha, então uma linha com 15 pontos explícitos coincide com uma sem valor quando a altura padrão também é 15. Mantenha o lcoCellStyles desligado em revisões de conteúdo. Em uma pasta grande ele transforma cada coluna reformatada em uma parede de hashes que esconde as três edições de célula que o revisor foi chamado para aprovar

// Revisão de layout: geometria, mesclagens e formatos condicionais apenas
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Limite o que as verificações de objeto podem capturar: 32 MiB, 500.000 itens
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
  Cmp.Compare(Left, Right);
except
  on E: EXLSXSemanticBudget do
    Writeln('workbooks too large for this check: ' + E.Message);
end;

As verificações de objeto capturam suas definições antes de comparar, e o SetFeatureLimits limita essa captura. Os padrões são 67.108.864 bytes e 1.000.000 de itens; exceder qualquer um dos dois lança EXLSXSemanticBudget. Quando o Compare lança, a lista de diferenças da chamada bem-sucedida anterior permanece no lugar, então um chamador que captura a exceção não deve ler Count como resultado da execução que falhou

Datas, linhas inseridas e onde comparar vale a pena

Células que a pasta guarda como datas comparam como datas, exatamente, e imprimem como date(...) com o valor numérico de data subjacente. Uma data de um lado contra um número puro do outro mostra os rótulos de tipo, o sinal usual de que uma coluna perdeu o formato de data no caminho por alguma outra ferramenta. Sistemas de data mistos são a armadilha aqui. Uma pasta no sistema de datas 1904 e uma no sistema 1900 gravam o mesmo dia do calendário com seriais que diferem em 1.462, então confira o Date1904 dos dois lados antes de confiar em uma coluna de mudanças de data. O contexto está em números seriais de data e o sistema 1904

O emparelhamento posicional tem uma consequência que vale planejar. Insira uma linha perto do topo de uma planilha e cada célula abaixo dela se move, então a comparação reporta uma sequência de células alteradas, adicionadas e removidas pela planilha abaixo, e fórmulas cujas referências foram reescritas pela inserção reportam como alteradas. Quando você controla o pipeline, rode uma comparação antes da edição estrutural e outra depois, para que cada relatório cubra um tipo de mudança. As regras de reescrita estão cobertas em ajuste de referências de fórmula em inserção e exclusão

Três trabalhos justificam essa integração. Teste de regressão de um gerador de relatórios: mantenha uma pasta de trabalho conhecida e boa, regenere, compare com resultados em cache ligados e falhe com qualquer diferença. Revisão de mudanças: entregue ao revisor o relatório ordenado em vez de dois arquivos. Verificações de migração: depois de converter um lote de pastas legadas, compare cada resultado com sua origem e então conte formatação e funcionalidades de objeto de cada lado com a passagem de inventário de o workbench de auditoria e conversão de pastas de trabalho

Comparação, recálculo e conversão compartilham um mesmo motor no HotXLS para Delphi e C++Builder, e a página do componente de planilha HotXLS para Delphi lista o resto do que esse motor lê e escreve