LlosLab Software Development BlogTechnické poznámky pro vývojáře PDF, Delphi a dokumentového softwaru.

Technický článek

Proč Excel ignoruje cacheované chyby #SPILL! a #CALC!

HotXLS Delphi Component

Excel věří cacheovanému #SPILL! nebo #CALC! jen tehdy, když sešit nese plnou moderní chybovou smlouvu: metadata Rich Data, jejichž rozšiřující URI XLRICHVALUE je malými písmeny psaný GUID, fileVersion 7, calcId alespoň 191029 a výpočetní funkce microsoft.com:RD. HotXLS, nativní XLSX komponenta pro Delphi, vypustí všechny čtyři, kdykoli ukládaná buňka drží jednu z těch chyb

Ujde-li kterýkoli kus, selhání bývá zřídka hlasité. Někdy Excel 16 odmítne soubor otevřít úplně. Častěji se otevře v pořádku a ukazuje #VALUE! tam, kde váš výpočetní engine vyprodukoval #SPILL!, což vypadá jako výpočetní chyba a pošle vás prohrábat kód vzorců, který je úplně správný. Stiskněte F9 a správná chyba se objeví, takže problém se tenduje zavřít jako „na mém stroji to funguje“, dokud zákazník neotevře report s vypnutým automatickým výpočtem. Tenhle příspěvek projde tím, co dělá každý kus, pastí velikosti písmen GUID, která vypadá, že nemůže nijak vadit, a pravidlem textové cache pole, které selhává úplně stejně potichu

Proč ukazuje Excel #VALUE! místo cacheovaného #SPILL!?

Excel ukazuje #VALUE!, protože to je doslova to, co buňka ukládá. Zastaralý chybový typ v ECMA-376 SpreadsheetML má pevný seznam chybových literálů a #SPILL! a #CALC! na něm nejsou. Buňka s t="e" může říct jen #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! nebo #N/A, takže novější verze Excelu zapisují #VALUE! jako zpětně kompatibilní zálohu a skutečný chybový typ věší na index value-metadata

Ten index, atribut vm, ukazuje do xl/metadata.xml, která ukazuje do hodnoty Rich Data popisující skutečnou chybu. Starší čtečky vidí #VALUE! a jdou dál. Aktuální Excel řetěz sleduje a zobrazí moderní chybu, ale jen tehdy, když se rozhodne, že je řetěz důvěryhodný. Takto na disku vypadá vzorec, který se rozlil a narazil na blokádu:

<!-- sheet1.xml: zastaralý literál plus ukazatel 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 je #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS zapisuje chybový typ 8 se sloupcovým a řádkovým odsazením pro #SPILL! a chybový typ 13 bez odsazení pro #CALC!. Zastaralé <v> zůstává v obou případech #VALUE! záměrně, protože to je hodnota, kterou má vidět každá čtečka ignorující Rich Data

Malý GUID, který Excel 16 doopravdy kontroluje

Blok future-metadata XLRICHVALUE musí použít rozšiřující URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} malými písmeny, znak za znakem. Velká šestnáctková písmena a lokální build Excel 16 odmítl sešit normálně otevřít, ačkoli každá XML část se sparsovala, každá relace se rozluštila a každý počet a index seděl

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

Kdokoli, kdo dělal s COM nebo registrem, ví, že GUIDy se srovnávají necitlivě na velikost písmen, a právě ten návyk tady kouše. Izolovali jsme to pomalou cestou: postavili minimální balíček, který Excel odmítl, pak vyměňovali po jedné věci. Výměna content types neopravila. Přidání části rich value types neopravilo. Změna prefixů jmenných prostorů nebo přepis rich value data neopravila. Opravila to malá písmena u té jediné atributy uri a týž balíček pak otevřel a zobrazil #CALC! i #SPILL!. Berte rozšiřující URI jako neprůhledný string literal, kopírujte ho z definice formátu a nikdy ho nepouštějte GUID formátovačem, který si vybere vlastní velikost písmen

Existuje praktický důsledek pro každého, kdo píše diagnostické balíčky ručně. Dělejte úpravy XML parserem, ne globálním nahrazením textu přes serializované XML. Slepá náhrada mířená na jeden token dokáže poškodit entity jako &gt; jinde v části a pak ladíte rozbitou sondu místo původní otázky

Co dalšího potřebuje Excel ke čtení cacheované moderní chyby?

Metadata části a jejich relace jsou nutné, ale nestačí. S dokonale platným řetězem Rich Data na místě Excel cacheovanou chybu pořád ignoroval, dokud se část sešitu neohlásila jako pocházející z moderního výpočetního enginu. HotXLS zapisuje tři deklarace do workbook.xml, kdykoli ukládaná buňka drží #SPILL! nebo #CALC!:

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

Element fileVersion je obyčejná metadata sešitu z ECMA-376 Part 1 a lastEdited / lowestEdited 7 říkají Excelu, že soubor naposledy ukládala generace aplikací rozumící Rich Data. calcId na calcPr identifikuje verzi výpočetního enginu a 191029 je minimum, které jsme viděli Excel přijímat u těchto cache. Položka microsoft.com:RD v rozšíření výpočetních funkcí, v prostoru http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, deklaruje, že engine, který vyprodukoval cacheované hodnoty, vyhodnocoval se sémantikou Rich Data. Poskytnout jen podtyp, jen types část nebo jen jiné prefixy a relační cesty výsledek nezměnilo. Všechny tři deklarace musely být přítomny dohromady

Tři detaily v tom, jak je HotXLS aplikuje, stojí za znalost, pokud tyto hodnoty nastavujete sami:

  • TXLSXWorkbook.CalcId se nepřepisuje. Zapisovač zvýší vypouštěnou hodnotu na 191029 jen ve výstupu, takže volající, který nakonfiguroval vyšší CalcId, si ho drží a vlastnost v paměti zůstává úplně to, co jste přiřadili
  • Existující výpočetní funkce a nesouvisející rozšíření sešitu, která přišla s otevřeným souborem, se zachovávají, nenahrazují
  • Zapisovač před přidáním kontroluje existující funkci microsoft.com:RD, takže opakované ukládání téhož sešitu nikdy nezásobí duplicitními deklaracemi
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 se rozlévá na tři řádky; A2 je obsazená, takže výsledek je #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Vyšší CalcId přežije; zapisovač ho zvýší na 191029 jen při nižším
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error části se zapisují automaticky
  finally
    Book.Free;
  end;
end;

Rich Data části si nikdy nestavíte sami. Před zápisem projde TXLSXWorkbook každou buňku na hodnoty #SPILL! nebo #CALC!, vytvoří nebo rozšíří xl/metadata.xml a dvě rich value části a odstraní zastaralé odkazy value-metadata z buněk, jejichž chyba se mezitím změnila nebo smazala. Explicitní přiřazení CalcId výše je volitelné a ukazuje se jen proto, aby bylo jasné, že se respektuje

Textové cache polí patří do t="str", ne do sdílených řetězců

Táž potichu selhávající věc se ukazuje u textových výsledků uvnitř polí. HotXLS ukládá cacheovaný text buňky vzorce a všech následnických buněk v rozsahu CSE nebo dynamického pole jako t="str" s doslovným řetězcem v <v>. Zapsat ty cache jako sdílené řetězce s t="s" donutí Excel ukazovat #VALUE! v buňkách pole, když čte cacheovaný soubor

<!-- Správně: doslovná textová cache na následníkovi pole -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Špatně: index sdíleného řetězce, Excel ukazuje #VALUE! z cache -->
<c r="B3" t="s"><v>17</v></c>

Tohle se snadno pokazí, protože t="s" je správná volba pro obyčejné hodnotové buňky a většina zapisovačů směruje každý řetězec defaultně přes tabulku sdílených řetězců. Past je ve verifikaci. Přepočítejte v Excelu a buňky pole ukáže správný text, což dokáže, že vzorec je v pořádku, a nedokáže nic o cache, kterou jste zapsali. Otevírají-li vaši uživatelé reporty s ručním výpočtem, nebo čte cacheované hodnoty bez výpočtu navazující nástroj, verze se sdílenými řetězci je prostě rozbitá

Syntaxe ukládání vzorců: úvodní rovná se, čárky a _xlws

Do téhož zapisovače přistává související úklid: vzorce jdoucí do OOXML se ukládají v gramatice souboru, ne v gramatice, kterou napsal uživatel. HotXLS si drží libovolný text, který jste přiřadili do Formula, pro zobrazení při cestě tam a zpátky, ale serializovaný vzorec zahodí úvodní =, používá čárky mezi argumenty funkcí a oddělovače řádků pole konstant a textu v uvozovkách nechává být. FILTER a SORT se ukládají s prefixem _xlfn._xlws., který Excel u těchto funkcí jen pro list očekává, zatímco jiné budoucí funkce jako SEQUENCE dostávají holý prefix _xlfn. před názvem

// Zadáno se středníkovými oddělovači, uloženo jako
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // žádný řádek nad 1000 dá #CALC!, uloženo s Rich Data

Uvnitř zapisovače záleží na pořadí. XML escapování běží po normalizaci syntaxe, nikdy před ní, ze stejného důvodu, proč diagnostické balíčky potřebují parser: přepis oddělovačů aplikovaný na už escapovaný text by znetvořil entity jako &gt; v porovnávacích operátorech

Jak ověřit cacheovanou chybu, aniž byste se oklamali

Jediný důvěryhodný test je otevřít soubor v Excelu s výpočtem už nastaveným na ruční, přečíst buňky a teprve pak přepočítat. Excel dědí výpočetní mód z prvního sešitu otevřeného v relaci, takže náš akceptační harness nejdřív vytvoří vlastní seed sešit, přepne ho na ruční a pak otevře testovaný soubor. Sondy, které seed krok přeskočily, dokázaly jen, že se soubor otevřel a správně spočítal. Cokoli, co označily za cacheované, už bylo přepočítáno

Ten rozdíl je celý článek v miniatuře. Moderní chybová cache je správná, když ji Excel zobrazí dřív, než se spustí jakýkoli výpočet, a každý požadavek výše, od malého GUID po textové následníky t="str", se našel kontrolou toho stavu, ne přepočítaného. O pozadí toho, jak HotXLS rozměry a vlastnictví rozlitého rozsahu vůbec řeší, píše maticové vzorce s rozlitím v Delphi s HotXLS. Je-li vaším příznakem výzva k opravě místo špatné chyby, lepším výchozím bodem jsou pravidla na úrovni balíčku v proč Excel opravuje platný XLSX

FAQ

Potřebuje HotXLS nějaké nastavení ke správnému uložení #SPILL! a #CALC!?

Ne. HotXLS detekuje hodnoty #SPILL! a #CALC! při uložení a automaticky zapisuje rich data části, fileVersion, minimální calcId a funkci microsoft.com:RD

Proč XML buňky pořád obsahuje #VALUE!?

Zastaralý chybový typ SpreadsheetML nemá literál pro #SPILL! ani #CALC!, takže #VALUE! je zpětně kompatibilní záloha. Aktuální Excel čte skutečnou chybu přes odkaz value-metadata vm

Sníží HotXLS CalcId, který si nastavím sám?

Ne. TXLSXWorkbook.CalcId se ve zapsaném souboru zvyšuje na 191029 jen tehdy, když je přítomna moderní chyba, a vyšší hodnota, kterou jste nakonfigurovali, zůstává beze změny

HotXLS čte a zapisuje XLSX, XLSM i zastaralé XLS z Delphi a C++Builder s vlastním výpočetním enginem, včetně dynamických polí. Detaily a zkušební stažení najdete na stránce komponenty HotXLS Delphi spreadsheet