LBlogue de Desenvolvimento de Software losLabNotas de engenharia para programadores de PDF, Delphi e software de documentos.

Artigo Técnico

Porque é que o Excel ignora erros #SPILL! e #CALC! em cache

HotXLS Delphi Component

O Excel só confia num #SPILL! ou #CALC! em cache quando o workbook traz o contrato completo de erros modernos: metadados Rich Data cujo URI de extensão XLRICHVALUE é o GUID em minúsculas, um fileVersion de 7, um calcId de pelo menos 191029, e a funcionalidade de cálculo microsoft.com:RD. O HotXLS, o componente XLSX nativo para Delphi, emite os quatro sempre que uma célula gravada guarda um desses erros

Faltar qualquer peça e a falha raramente é barulhenta. Às vezes o Excel 16 recusa-se a abrir o ficheiro de todo. Mais frequentemente abre bem e mostra #VALUE! onde o seu motor de cálculo produziu #SPILL!, o que parece um bug de cálculo e manda-o caçar por código de fórmulas que está perfeitamente correto. Prime F9 e o erro certo aparece, por isso o problema tende a ser encerrado como "funciona na minha máquina" até um cliente abrir o relatório com o cálculo automático desligado. Este post percorre o que cada peça faz, a armadilha das maiúsculas do GUID que parece não poder importar de forma nenhuma, e a regra de cache de texto de array que falha exatamente do mesmo modo silencioso

Porque é que o Excel mostra #VALUE! em vez de um #SPILL! em cache?

O Excel mostra #VALUE! porque é literalmente isso que a célula guarda. O tipo de erro legado no SpreadsheetML do ECMA-376 tem uma lista fixa de literais de erro, e #SPILL! e #CALC! não estão nela. Uma célula com t="e" só pode dizer #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! ou #N/A, por isso as versões mais recentes do Excel escrevem #VALUE! como fallback retrocompatível e penduram o tipo de erro real num índice de valor-metadados

Esse índice, o atributo vm, aponta para xl/metadata.xml, que aponta para um valor Rich Data que descreve o erro real. Leitores mais antigos veem #VALUE! e seguem em frente. O Excel atual segue a cadeia e mostra o erro moderno, mas só se decidir que a cadeia é de confiança. Aqui está o aspeto de uma fórmula derramada e bloqueada em disco:

<!-- sheet1.xml: literal legado mais um ponteiro de valor-metadados -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8 é #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

O HotXLS escreve o tipo de erro 8 com offsets de coluna e de linha para #SPILL!, e o tipo de erro 13 sem offsets para #CALC!. O <v> legado fica #VALUE! em ambos os casos de propósito, porque esse é o valor que qualquer leitor que ignore Rich Data supostamente deve ver

O GUID em minúsculas que o Excel 16 realmente verifica

O bloco de metadados futuros XLRICHVALUE tem de usar o URI de extensão {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} em minúsculas, caractere a caractere. Pôr as letras hexadecimais em maiúsculas e o build local do Excel 16 recusava-se a abrir o workbook normalmente, embora cada parte XML fizesse parse, cada relacionamento resolvesse, e cada contagem e índice batessem certo

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

Quem já trabalhou com COM ou com o registry sabe que GUIDs comparam-se insensíveis a maiúsculas, e esse hábito é exatamente o que morde aqui. Isolámo-lo da forma lenta: construímos um pacote mínimo que o Excel rejeitava, e depois trocámos uma coisa de cada vez. Substituir os content types não corrigiu. Acrescentar uma parte de rich value types não corrigiu. Mudar prefixos de namespace ou reescrever os dados de rich value não corrigiu. Pôr em minúsculas aquele único atributo uri corrigiu, e o mesmo pacote abriu então e mostrou tanto #CALC! como #SPILL!. Trate o URI de extensão como um literal de string opaco, copie-o da definição do formato, e nunca o passe por um formatador de GUID que escolha as suas próprias maiúsculas

Há um corolário prático para quem escreve pacotes de diagnóstico à mão. Faça as edições com um parser XML, não com uma substituição global de texto sobre XML serializado. Uma substituição indiscriminada dirigida a um token pode corromper entidades como &gt; noutros pontos da parte, e depois está a depurar uma sonda partida em vez da questão original

Que mais precisa o Excel para ler um erro moderno em cache?

As partes de metadados e os seus relacionamentos são necessários mas não suficientes. Com uma cadeia Rich Data perfeitamente válida no lugar, o Excel ainda ignorava o erro em cache até a parte do workbook declarar-se como vinda de um motor de cálculo moderno. O HotXLS escreve três declarações em workbook.xml sempre que uma célula gravada guarda #SPILL! ou #CALC!:

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

O elemento fileVersion é metadados de workbook simples do ECMA-376 Part 1, e lastEdited / lowestEdited de 7 dizem ao Excel que o ficheiro foi gravado pela última vez por uma geração de aplicação que compreende Rich Data. O calcId no calcPr identifica a versão do motor de cálculo, e 191029 é o mínimo que encontrámos o Excel aceitar para estas caches. A entrada microsoft.com:RD na extensão de funcionalidades de cálculo, no namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, declara que o motor que produziu os valores em cache avaliou com semântica Rich Data. Fornecer só o subtipo, só a parte de types, ou só prefixos e caminhos de relacionamento diferentes não mudou o desfecho. As três declarações tinham de estar presentes em conjunto

Três detalhes na forma como o HotXLS os aplica valem a pena conhecer se definir estes valores você mesmo:

  • O TXLSXWorkbook.CalcId não é sobreposto. O escritor sobe o valor emitido para 191029 apenas no output, por isso um chamador que configurou um CalcId mais alto mantém-no, e a propriedade em memória fica exatamente com o que atribuiu
  • Funcionalidades de cálculo existentes e extensões de workbook não relacionadas que vieram com um ficheiro aberto são preservadas, não substituídas
  • O escritor verifica a existência de uma funcionalidade microsoft.com:RD antes de acrescentar uma, por isso gravar o mesmo workbook repetidamente nunca empilha declarações duplicadas
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 derrama três linhas; A2 está ocupada, por isso o resultado é #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Um CalcId mais alto sobrevive; o escritor só o sobe para 191029 se estiver mais baixo
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // partes de erro rich escritas automaticamente
  finally
    Book.Free;
  end;
end;

Você nunca constrói as partes Rich Data você mesmo. Antes de gravar, o TXLSXWorkbook varre todas as células à procura de valores #SPILL! ou #CALC!, cria ou estende xl/metadata.xml e as duas partes de rich value, e deita fora ligações de valor-metadados caducas de células cujo erro entretanto mudou ou foi limpo. A atribuição explícita de CalcId acima é opcional e só é mostrada para deixar claro que é respeitada

Caches de texto de array pertencem a t="str", não a shared strings

A mesma falha silenciosa aparece para resultados de texto dentro de arrays. O HotXLS grava o texto em cache de uma célula de fórmula, e de cada célula seguidora num intervalo CSE ou de array dinâmico, como t="str" com a string literal em <v>. Escrever essas caches como shared strings com t="s" faz o Excel mostrar #VALUE! nas células do array quando lê o ficheiro em cache

<!-- Correto: cache de texto literal num seguidor de array -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Errado: índice de shared string, o Excel mostra #VALUE! a partir da cache -->
<c r="B3" t="s"><v>17</v></c>

Este é fácil de errar porque t="s" é a escolha certa para células de valor comuns, e a maioria dos escritores encaminha todas as strings pela tabela de shared strings por predefinição. A armadilha está na verificação. Recalcule no Excel e as células do array mostram o texto correto, o que prova que a fórmula está boa e não prova nada sobre a cache que escreveu. Se os seus utilizadores abrem relatórios com cálculo manual, ou uma ferramenta a jusante lê valores em cache sem calcular, a versão com shared string está simplesmente partida

Sintaxe de armazenamento de fórmulas: igual inicial, vírgulas e _xlws

Uma limpeza relacionada aterra no mesmo escritor: as fórmulas que vão para OOXML são guardadas na gramática do ficheiro, não na gramática que um utilizador digitou. O HotXLS guarda o texto que atribuiu a Formula para exibição no round trip, mas a fórmula serializada deita fora o = inicial, usa vírgulas entre argumentos de função, e deixa quietos os separadores de linha de constantes de array e o texto entre aspas. FILTER e SORT são guardados com o prefixo _xlfn._xlws. que o Excel espera para essas funções só de worksheet, enquanto outras funções futuras como SEQUENCE levam o prefixo simples _xlfn. antes do nome

// Introduzido com separadores de ponto e vírgula, guardado como
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // nenhuma linha acima de 1000 dá #CALC!, gravado com Rich Data

A ordem importa dentro do escritor. O escaping de XML corre depois da normalização de sintaxe, nunca antes, pela mesma razão pela qual os pacotes de diagnóstico precisam de um parser: uma reescrita de separadores aplicada a texto já escapado estragaria entidades como &gt; em operadores de comparação

Como verificar um erro em cache sem se enganar

O único teste de confiança é abrir o ficheiro no Excel com o cálculo já posto em manual, ler as células, e só então recalcular. O Excel herda o modo de cálculo do primeiro workbook aberto numa sessão, por isso o nosso harness de aceitação primeiro cria o seu próprio workbook semente, põe-no em manual, e depois abre o ficheiro em teste. Sondas que saltavam o passo da semente só provavam que o ficheiro abria e calculava corretamente. O que rotulavam como em cache já tinha sido recalculado

Essa distinção é o artigo inteiro em miniatura. Uma cache de erro moderno está correta quando o Excel a mostra antes de qualquer cálculo correr, e cada requisito acima, do GUID em minúsculas aos seguidores de texto t="str", foi encontrado verificando esse estado e não o recalculado. Para o pano de fundo de como o HotXLS dimensiona e possui spill ranges desde o início, veja fórmulas de spill de array dinâmico em Delphi com HotXLS. Se o seu sintoma é um aviso de reparação em vez de um erro errado, as regras ao nível do pacote em porque é que o Excel repara um XLSX válido são o melhor ponto de partida

FAQ

O HotXLS precisa de alguma definição para gravar #SPILL! e #CALC! corretamente?

Não. O HotXLS deteta valores #SPILL! e #CALC! na hora de gravar e escreve as partes Rich Data, o fileVersion, o calcId mínimo e a funcionalidade microsoft.com:RD automaticamente

Porque é que o XML da célula ainda contém #VALUE!?

O tipo de erro legado do SpreadsheetML não tem literal para #SPILL! ou #CALC!, por isso #VALUE! é o fallback retrocompatível. O Excel atual lê o erro real através da ligação de valor-metadados vm

O HotXLS baixa um CalcId que eu defina?

Não. O TXLSXWorkbook.CalcId só é subido para 191029 no ficheiro escrito quando um erro moderno está presente, e um valor mais alto que configurou é mantido como está

O HotXLS lê e escreve XLSX, XLSM e XLS legado a partir de Delphi e C++Builder com o seu próprio motor de cálculo, arrays dinâmicos incluídos. Detalhes e uma transferência de teste estão na página do HotXLS Delphi spreadsheet component