LlosLab Software Development BlogNotatki inżynieryjne dla programistów PDF, Delphi i oprogramowania do dokumentów.

Artykuł techniczny

Dlaczego Excel ignoruje zcache'owane błędy #SPILL! i #CALC!

HotXLS Delphi Component

Excel ufa zcache'owanemu #SPILL! albo #CALC! tylko wtedy, gdy skoroszyt niesie pełną nowoczesną umowę błędu: metadane Rich Data z URI rozszerzenia XLRICHVALUE będącym GUID małymi literami, fileVersion równym 7, calcId co najmniej 191029 i funkcją obliczeń microsoft.com:RD. HotXLS, natywny komponent XLSX dla Delphi, emituje wszystkie cztery, kiedykolwiek zapisana komórka trzyma któryś z tych błędów

Pomiń jakikolwiek element, a porażka rzadko bywa głośna. Czasem Excel 16 odmawia otwarcia pliku w ogóle. Częściej otwiera się dobrze i pokazuje #VALUE! tam, gdzie twój silnik obliczeń wyprodukował #SPILL!, co wygląda jak błąd obliczeń i wysyła cię na polowanie po kodzie formuł, który jest kompletnie poprawny. Wciśnij F9, a właściwy błąd się pojawia, więc problem bywa zamykany jako "u mnie działa", dopóki klient nie otworzy raportu z wyłączonym obliczaniem automatycznym. Ten wpis przechodzi przez to, co robi każdy element, pułapkę wielkości liter GUID, która wygląda, jakby nie mogła mieć znaczenia, i regułę cache tekstu tablic, która pada dokładnie tak samo po cichu

Dlaczego Excel pokazuje #VALUE! zamiast zcache'owanego #SPILL!?

Excel pokazuje #VALUE!, bo to dosłownie to, co komórka przechowuje. Dziedziczony typ błędu w SpreadsheetML z ECMA-376 ma stałą listę literałów błędów, i #SPILL! oraz #CALC! nie ma na niej. Komórka z t="e" potrafi powiedzieć tylko #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! albo #N/A, więc nowsze wersje Excela zapisują #VALUE! jako wstecznie zgodny fallback i wieszają prawdziwy typ błędu na indeksie metadanych wartości

Ten indeks, atrybut vm, wskazuje do xl/metadata.xml, który wskazuje na wartość Rich Data opisującą rzeczywisty błąd. Starsze czytniki widzą #VALUE! i idą dalej. Bieżący Excel idzie za łańcuchem i wyświetla nowoczesny błąd, ale tylko, jeśli uzna łańcuch za godny zaufania. Tak wygląda na dysku formuła rozlana i zablokowana:

<!-- sheet1.xml: literał dziedziczony plus wskaźnik metadanych wartości -->
<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 to #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS zapisuje typ błędu 8 z offsetami kolumny i wiersza dla #SPILL! oraz typ błędu 13 bez offsetów dla #CALC!. Dziedziczony <v> zostaje #VALUE! w obu przypadkach z zamierzenia, bo to wartość, którą ma zobaczyć każdy czytnik ignorujący Rich Data

GUID małymi literami, którego Excel 16 naprawdę pilnuje

Blok future-metadata XLRICHVALUE musi używać URI rozszerzenia {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} małymi literami, znak w znak. Wielkie litery w heksach i lokalny build Excel 16 odmawiał normalnego otwarcia skoroszytu, mimo że każda część XML się parsowała, każda relacja się rozwiązywała i każda liczba oraz indeks się zgadzały

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

Każdy, kto pracował z COM albo rejestrem, wie, że GUID-y porównuje się bez rozróżniania wielkości liter, i ten nawyk to dokładnie to, co tu gryzie. Wyizolowaliśmy to wolną drogą: zbudowaliśmy minimalny pakiet, który Excel odrzucał, a potem podmienialiśmy po jednej rzeczy. Wymiana typów zawartości nie pomogła. Dodanie części typów rich value nie pomogło. Zmiana prefiksów przestrzeni nazw albo przepisanie danych rich value nie pomogło. Zmiana tego jednego atrybutu uri na małe litery pomogła i ten sam pakiet otworzył się potem i wyświetlił i #CALC!, i #SPILL!. Traktuj URI rozszerzenia jako nieprzejrzysty literał string, kopiuj go z definicji formatu i nigdy nie puszczaj go przez formater GUID, który wybiera własną wielkość liter

Jest praktyczny wniosek dla każdego, kto pisze pakiety diagnostyczne ręcznie. Rób edycje parserem XML, nie globalną podmianą tekstu po zserializowanym XML. Zamiatająca podmiana wymierzona w jeden token może uszkodzić encje, jak &gt; gdzie indziej w części, i wtedy debugujesz zepsutą sondę zamiast pierwotnego pytania

Czego jeszcze Excel potrzebuje, by przeczytać zcache'owany nowoczesny błąd?

Części metadanych i ich relacje są konieczne, ale niewystarczające. Z kompletnie poprawnym łańcuchem Rich Data Excel wciąż ignorował zcache'owany błąd, dopóki część skoroszytu nie zadeklarowała się jako pochodząca od nowoczesnego silnika obliczeń. HotXLS zapisuje trzy deklaracje do workbook.xml zawsze, gdy zapisana komórka trzyma #SPILL! albo #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 to zwykłe metadane skoroszytu z ECMA-376 Part 1, a lastEdited / lowestEdited równe 7 mówią Excelowi, że plik był ostatnio zapisywany przez generację aplikacji rozumiejącą Rich Data. calcId na calcPr identyfikuje wersję silnika obliczeń, a 191029 to minimum, przy którym widzieliśmy Excela przyjmującym te cache. Wpis microsoft.com:RD w rozszerzeniu funkcji obliczeń, w przestrzeni nazw http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, deklaruje, że silnik, który wyprodukował zcache'owane wartości, liczył z semantyką Rich Data. Podanie samego subtypu, samej części typów albo tylko innych prefiksów i ścieżek relacji nie zmieniało wyniku. Wszystkie trzy deklaracje musiały być obecne razem

Trzy szczegóły w tym, jak HotXLS je stosuje, są warte poznania, jeśli ustawiasz te wartości sam:

  • TXLSXWorkbook.CalcId nie jest nadpisywane. Writer podbija emitowaną wartość do 191029 tylko w wyjściu, więc wołający, który skonfigurował wyższe CalcId, je zachowuje, a właściwość w pamięci zostaje dokładnie taka, jaką ją przypisałeś
  • Istniejące funkcje obliczeń i niepowiązane rozszerzenia skoroszytu, które przyszły z otwartym plikiem, są zachowywane, nie wymieniane
  • Writer sprawdza istniejącą funkcję microsoft.com:RD przed dodaniem, więc powtarzany zapis tego samego skoroszytu nigdy nie nakłada zduplikowanych deklaracji
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 rozlewa trzy wiersze; A2 jest zajęta, więc wynik to #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Wyższe CalcId przeżywa; writer podbija do 191029 tylko, gdy niższe
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // części rich error zapisywane automatycznie
  finally
    Book.Free;
  end;
end;

Części Rich Data nigdy nie budujesz sam. Przed zapisem TXLSXWorkbook skanuje każdą komórkę pod kątem wartości #SPILL! albo #CALC!, tworzy albo rozszerza xl/metadata.xml i dwie części rich value oraz zrzuca nieaktualne łącza metadanych wartości z komórek, których błąd zmienił się albo zniknął. Jawne przypisanie CalcId wyżej jest opcjonalne i pokazane tylko po to, by pokazać, że jest respektowane

Cache tekstu tablic należy do t="str", nie do stringów współdzielonych

Ten sam cichy błąd wychodzi przy wynikach tekstowych wewnątrz tablic. HotXLS zapisuje zcache'owany tekst komórki formuły i każdej komórki obserwującej w zakresie CSE albo tablicy dynamicznej jako t="str" z literalnym stringiem w <v>. Zapisanie tych cache jako stringów współdzielonych z t="s" każe Excelowi pokazać #VALUE! w komórkach tablicy, gdy czyta zcache'owany plik

<!-- Dobrze: literałowy cache tekstu na obserwatorze tablicy -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Źle: indeks współdzielonego stringa, Excel pokazuje #VALUE! z cache -->
<c r="B3" t="s"><v>17</v></c>

To łatwe do sparcia, bo t="s" to właściwy wybór dla zwykłych komórek wartości, a większość writerów kieruje każdy string do tabeli współdzielonych stringów domyślnie. Pułapka jest w weryfikacji. Przelicz w Excelu, a komórki tablicy pokażą właściwy tekst, co dowodzi, że formuła jest dobra, a o zapisanym cache nie dowodzi nic. Jeśli twoi użytkownicy otwierają raporty z obliczaniem ręcznym albo narzędzie downstream czyta zcache'owane wartości bez liczenia, wersja ze współdzielonymi stringami jest po prostu zepsuta

Składnia przechowywania formuł: wiodący znak równości, przecinki i _xlws

Pokrewna korekta ląduje w tym samym writerze: formuły idące do OOXML są przechowywane w gramatyce plikowej, nie w gramatyce, którą wpisał użytkownik. HotXLS trzyma tekst, jaki przypisałeś do Formula, do wyświetlania w obie strony, ale serializowana formuła zrzuca wiodący =, używa przecinków między argumentami funkcji i zostawia separatory wierszy stałych tablicowych oraz cytowany tekst w spokoju. FILTER i SORT są przechowywane z prefiksem _xlfn._xlws., którego Excel oczekuje dla tych funkcji tylko-arkuszowych, podczas gdy inne funkcje przyszłe, jak SEQUENCE, dostają zwykły prefiks _xlfn. przed nazwą

// Wpisane średnikami, zapisane jako
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // brak wiersza ponad 1000 daje #CALC!, zapisane z Rich Data

Kolejność ma znaczenie w writerze. Escapowanie XML biegnie po normalizacji składni, nigdy przed, z tego samego powodu, dla którego pakiety diagnostyczne potrzebują parsera: przepisanie separatorów na już escapowanym tekście zmasakrowałoby encje, jak &gt; w operatorach porównania

Jak zweryfikować zcache'owany błąd, nie oszukując samego siebie

Jedynym godnym zaufania testem jest otwarcie pliku w Excelu z obliczaniem już ustawionym na ręczne, przeczytanie komórek i dopiero potem przeliczenie. Excel dziedziczy tryb obliczeń z pierwszego skoroszytu otwartego w sesji, więc nasz acceptance harness najpierw tworzy własny skoroszyt nasionowy, przełącza go na ręczny, a potem otwiera testowany plik. Sondy, które pomijały krok nasionowy, dowodziły tylko, że plik się otworzył i policzył poprawnie. Cokolwiek nazwały zcache'owanym, było już przeliczone

To rozróżnienie to cały artykuł w miniaturze. Nowoczesny cache błędu jest poprawny, gdy Excel go wyświetla, zanim jakiekolwiek obliczenie ruszy, i każde wymaganie z góry, od GUID małymi literami do tekstowych obserwatorów t="str", zostało znalezione przez sprawdzanie tego stanu, a nie przeliczonego. Tło tego, jak HotXLS wymiaruje i posiada zakresy rozlania na samym początku, przynosi artykuł o formułach rozlewania tablic dynamicznych w Delphi z HotXLS. Jeśli twoim symptomem jest okno naprawy, a nie zły błąd, lepszym punktem startu są reguły poziomu pakietu z artykułu o tym, dlaczego Excel naprawia poprawny XLSX

FAQ

Czy HotXLS potrzebuje jakiegoś ustawienia, by zapisywać #SPILL! i #CALC! poprawnie?

Nie. HotXLS wykrywa wartości #SPILL! i #CALC! w czasie zapisu i zapisuje części Rich Data, fileVersion, minimalne calcId i funkcję microsoft.com:RD automatycznie

Dlaczego XML komórki wciąż zawiera #VALUE!?

Dziedziczony typ błędu SpreadsheetML nie ma literału dla #SPILL! ani #CALC!, więc #VALUE! jest wstecznie zgodnym fallbackiem. Bieżący Excel czyta rzeczywisty błąd przez łącze metadanych wartości vm

Czy HotXLS obniży CalcId, który ustawiłem sam?

Nie. TXLSXWorkbook.CalcId jest podbijane do 191029 w zapisanym pliku tylko, gdy obecny jest nowoczesny błąd, a wyższa wartość, którą skonfigurowałeś, zostaje jak jest

HotXLS czyta i zapisuje XLSX, XLSM i dziedziczony XLS z Delphi i C++Builder własnym silnikiem obliczeń, tablice dynamiczne włącznie. Szczegóły i pobieranie wersji próbnej są na stronie komponentu arkuszowego HotXLS Delphi