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

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

Perca qualquer peça e a falha raramente é barulhenta. Às vezes o Excel 16 se recusa a abrir o arquivo de forma alguma. Mais frequentemente ele abre bem e mostra #VALUE! onde o seu motor de cálculo produziu #SPILL!, o que parece um bug de cálculo e te manda caçar por código de fórmula perfeitamente correto. Aperte F9 e o erro certo aparece, então o problema tende a ser fechado 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 da caixa do GUID que parece não poder importar de jeito nenhum, e a regra de cache de texto de array que falha exatamente do mesmo jeito silencioso

Por 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, então versões mais novas do Excel escrevem #VALUE! como o fallback compatível com versões anteriores e penduram o tipo de erro real em um índice de value-metadata

Esse índice, o atributo vm, aponta para xl/metadata.xml, que aponta para um valor Rich Data descrevendo o erro real. Leitores antigos veem #VALUE! e seguem em frente. O Excel atual segue a cadeia e exibe o erro moderno, mas só se decidir que a cadeia é confiável. Aqui está como uma fórmula derramada e bloqueada parece no disco:

<!-- sheet1.xml: literal legado mais um ponteiro de value-metadata -->
<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 error type 8 com offsets de coluna e linha para #SPILL!, e error type 13 sem offsets para #CALC!. O <v> legado permanece #VALUE! em ambos os casos de propósito, porque esse é o valor que qualquer leitor que ignora Rich Data deve ver

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

O bloco future-metadata XLRICHVALUE precisa usar o URI de extensão {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} em minúsculas, caractere por caractere. Coloque as letras hex em maiúsculas e o build local do Excel 16 se recusava a abrir a pasta de trabalho normalmente, mesmo com toda part XML parseando, todo relationship resolvendo, e toda contagem e índice batendo

<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>

Todo mundo que já trabalhou com COM ou com o registro sabe que GUIDs comparam case-insensitive, e esse hábito é exatamente o que morde aqui. Isolamos do jeito lento: construímos um pacote mínimo que o Excel rejeitava, e então trocávamos uma coisa por vez. Substituir os content types não resolveu. Adicionar uma part de rich value types não resolveu. Mudar prefixos de namespace ou reescrever os rich value data não resolveu. Colocar aquele único atributo uri em minúsculas resolveu, e o mesmo pacote então abriu e exibiu tanto #CALC! quanto #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 escolhe a própria caixa

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 abrangente mirando um token pode corromper entidades como > em outro lugar da part, e então você está depurando um probe quebrado em vez da pergunta original

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

As parts de metadata e os seus relationships 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 part de workbook se declarar como vinda de um motor de cálculo moderno. O HotXLS escreve três declarações no workbook.xml sempre que uma célula salva 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 é metadata de workbook comum do ECMA-376 Part 1, e lastEdited / lowestEdited de 7 dizem ao Excel que o arquivo foi salvo pela última vez por uma geração de aplicação que entende Rich Data. O calcId no calcPr identifica a versão do motor de cálculo, e 191029 é o mínimo que encontramos o Excel aceitar para esses caches. A entrada microsoft.com:RD na extensão de recursos 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 part de types, ou só prefixos e caminhos de relationship diferentes não mudou o resultado. As três declarações precisavam estar presentes juntas

Três detalhes em como o HotXLS os aplica valem conhecer se você define esses valores você mesmo:

O TXLSXWorkbook.CalcId não é sobrescrito. O escritor eleva o valor emitido para 191029 apenas na saída, então um chamador que configurou um CalcId mais alto o mantém, e a propriedade na memória fica exatamente o que você atribuiu
Recursos de cálculo existentes e extensões de workbook não relacionadas que vieram com um arquivo aberto são preservados, não substituídos
O escritor verifica um recurso microsoft.com:RD existente antes de adicionar um, então salvar a mesma pasta de trabalho 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, então 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ó eleva para 191029 se menor
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // parts de erro rich escritas automaticamente
  finally
    Book.Free;
  end;
end;

Você nunca constrói as parts de Rich Data você mesmo. Antes de escrever, o TXLSXWorkbook varre toda célula em busca de valores #SPILL! ou #CALC!, cria ou estende o xl/metadata.xml e as duas parts de rich value, e derruba links de value-metadata obsoletos de células cujo erro mudou ou foi limpo desde então. A atribuição explícita de CalcId acima é opcional e só aparece para mostrar que ela é 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 salva o texto em cache de uma célula de fórmula, e de toda célula seguidora em um intervalo CSE ou de array dinâmico, como t="str" com a string literal no <v>. Escrever esses caches como shared strings com t="s" faz o Excel exibir #VALUE! nas células do array quando ele lê o arquivo em cache

<!-- Correto: cache de texto literal em uma célula seguidora de array -->
<c r="B3" t="str"><v>Northwind</v></c>

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

Esse é fácil de errar porque t="s" é a escolha certa para células de valor comuns, e a maioria dos escritores encaminha toda string pela shared string table por padrã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 o cache que você escreveu. Se os seus usuários abrem relatórios com cálculo manual, ou uma ferramenta a jusante lê valores em cache sem calcular, a versão shared-string está simplesmente quebrada

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

Uma limpeza relacionada aterrissa no mesmo escritor: fórmulas que vão para OOXML são guardadas na gramática do arquivo, não na gramática que um usuário digitou. O HotXLS mantém o texto que você atribuiu ao Formula para exibição de round-trip, mas a fórmula serializada descarta o = inicial, usa vírgulas entre argumentos de função, e deixa em paz separadores de linha de constantes de array e texto entre aspas. FILTER e SORT são guardados com o prefixo _xlfn._xlws. que o Excel espera para essas funções exclusivas de worksheet, enquanto outras future functions como SEQUENCE levam o prefixo simples _xlfn. antes do nome

// Digitado 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!, salvo com Rich Data

A ordem importa dentro do escritor. O escaping de XML roda depois da normalização de sintaxe, nunca antes, pelo mesmo motivo de os pacotes de diagnóstico precisarem de um parser: uma reescrita de separador aplicada a texto já escapado estragaria entidades como > em operadores de comparação

Como verificar um erro em cache sem se enganar

O único teste confiável é abrir o arquivo no Excel com o cálculo já definido como manual, ler as células e só então recalcular. O Excel herda o modo de cálculo da primeira pasta de trabalho aberta em uma sessão, então o nosso harness de aceitação primeiro cria a sua própria pasta de trabalho semente, a muda para manual, e depois abre o arquivo sob teste. Probes que pulavam o passo da semente só provavam que o arquivo abria e calculava corretamente. O que eles rotulavam como em cache já tinha sido recalculado

Essa distinção é o artigo inteiro em miniatura. Um cache de erro moderno está correto quando o Excel o exibe antes de qualquer cálculo rodar, e todo requisito acima, do GUID em minúsculas às células seguidoras de texto t="str", foi encontrado verificando esse estado e não o recalculado. Para o contexto de como o HotXLS dimensiona e possui spill ranges em primeiro lugar, veja fórmulas de spill de array dinâmico em Delphi com HotXLS. Se o seu sintoma é um prompt de reparo em vez de um erro errado, as regras de nível de pacote em por que o Excel repara um XLSX válido são o melhor ponto de partida

FAQ

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

Não. O HotXLS detecta valores #SPILL! e #CALC! no momento de salvar e escreve as parts de Rich Data, o fileVersion, o calcId mínimo e o recurso microsoft.com:RD automaticamente

Por 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!, então #VALUE! é o fallback compatível com versões anteriores. O Excel atual lê o erro real através do link de value-metadata vm

O HotXLS abaixa um CalcId que eu mesmo defini?

Não. O TXLSXWorkbook.CalcId só é elevado para 191029 no arquivo escrito quando um erro moderno está presente, e um valor mais alto que você 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 download de teste estão na página do componente de planilha HotXLS para Delphi