LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

Техническая статья

Почему Excel игнорирует кэшированные #SPILL! и #CALC!

HotXLS Delphi Component

Excel доверяет кэшированному #SPILL! или #CALC!, только когда книга несёт полный контракт современных ошибок: метаданные Rich Data, чей URI расширения XLRICHVALUE — GUID в нижнем регистре, fileVersion 7, calcId не ниже 191029 и фича расчёта microsoft.com:RD. HotXLS, нативный XLSX-компонент для Delphi, выдаёт все четыре, как только сохраняемая ячейка держит одну из этих ошибок

Пропустите любую из частей — и отказ редко бывает громким. Иногда Excel 16 вообще отказывается открывать файл. Чаще он открывается нормально и показывает #VALUE! там, где ваш движок расчёта выдал #SPILL!, что выглядит как баг расчёта и отправляет вас шерстить идеально правильный код формул. Нажмёте F9 — появится правильная ошибка, поэтому проблему обычно закрывают как «у меня работает», пока клиент не откроет отчёт с выключенным автопересчётом. Этот пост проходит по тому, что делает каждая часть, по ловушке регистра GUID, которая выглядит совершенно неважной, и по правилу кэша текста массива, падающему ровно так же тихо

Почему Excel показывает #VALUE! вместо кэшированного #SPILL!?

Excel показывает #VALUE!, потому что это буквально то, что хранит ячейка. Унаследованный тип ошибки в ECMA-376 SpreadsheetML имеет фиксированный список литералов ошибок, и #SPILL! с #CALC! в нём нет. Ячейка с t="e" может сказать только #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! или #N/A, поэтому новые версии Excel пишут #VALUE! как обратно совместимый фоллбэк, а настоящий тип ошибки вешают на индекс value-метаданных

Этот индекс — атрибут vm — указывает в xl/metadata.xml, а тот — в Rich Data-значение, описывающее настоящую ошибку. Старые читатели видят #VALUE! и идут дальше. Текущий Excel идёт по цепочке и показывает современную ошибку, но только если решит, что цепочке можно доверять. Вот как формула, разлитая и заблокированная, выглядит на диске:

<!-- sheet1.xml: унаследованный литерал плюс указатель value-метаданных -->
<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>

HotXLS пишет тип ошибки 8 со смещениями по колонке и строке для #SPILL! и тип 13 без смещений для #CALC!. Унаследованный <v> в обоих случаях нарочно остаётся #VALUE!: именно это значение должен видеть любой читатель, игнорирующий Rich Data

GUID в нижнем регистре, который Excel 16 реально проверяет

Блок future-метаданных XLRICHVALUE обязан использовать URI расширения {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} в нижнем регистре, символ в символ. Поднимите hex-буквы в верхний — и локальная сборка Excel 16 отказывалась нормально открывать книгу, хотя каждая XML-часть разбиралась, каждое отношение разрешалось, и все счётчики с индексами сходились

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

Всякий, кто работал с COM или реестром, знает, что GUID сравниваются без учёта регистра, — и именно эта привычка здесь кусается. Мы изолировали это медленным способом: собрали минимальный пакет, который Excel отвергал, и меняли по одной вещи за раз. Замена content types не помогла. Добавление части rich value types не помогло. Смена префиксов пространств имён или переписывание rich value data не помогли. Нижний регистр в единственном атрибуте uri помог, и тот же пакет после этого открылся и показал и #CALC!, и #SPILL!. Обращайтесь с URI расширения как с непрозрачным строковым литералом, копируйте его из определения формата и никогда не прогоняйте через форматтер GUID, выбирающий собственный регистр

Есть практическое следствие для всех, кто пишет диагностические пакеты руками. Правки делайте XML-парсером, а не глобальной заменой текста по сериализованному XML. Косая подмена, нацеленная на один токен, может попортить сущности вроде &gt; в других местах части — и вы будете отлаживать сломанный зонд вместо исходного вопроса

Что ещё нужно Excel, чтобы прочитать кэшированную современную ошибку?

Части метаданных и их отношения необходимы, но недостаточны. С совершенно валидной цепочкой Rich Data Excel всё равно игнорировал кэшированную ошибку, пока часть книги не объявляла себя вышедшей из современного движка расчёта. HotXLS пишет три объявления в workbook.xml, как только сохраняемая ячейка держит #SPILL! или #CALC!:

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

Элемент fileVersion — обычные метаданные книги из ECMA-376 Part 1, а lastEdited / lowestEdited со значением 7 говорят Excel, что файл последний раз сохраняло поколение приложений, понимающее Rich Data. calcId на calcPr идентифицирует версию движка расчёта, и 191029 — минимум, при котором мы застали Excel принимающим эти кэши. Запись microsoft.com:RD в расширении фич расчёта, в пространстве имён http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, объявляет, что движок, породивший кэшированные значения, вычислял с семантикой Rich Data. Дать только подтип, только часть типов или только другие префиксы и пути отношений исход не меняло. Все три объявления обязаны присутствовать вместе

Три детали в том, как HotXLS их применяет, стоит знать, если задаёте эти значения сами:

  • TXLSXWorkbook.CalcId не перезаписывается. Писатель поднимает выдаваемое значение до 191029 только в выводе, поэтому вызывающий, настроивший более высокий CalcId, его сохраняет, а свойство в памяти остаётся ровно тем, что вы присвоили
  • Существующие фичи расчёта и посторонние расширения книги, пришедшие с открытым файлом, сохраняются, а не заменяются
  • Писатель проверяет наличие уже существующей фичи microsoft.com:RD перед добавлением, поэтому повторное сохранение той же книги никогда не нагромождает дубликаты объявлений
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 разливается на три строки; A2 занята, поэтому результат — #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Более высокий CalcId выживает; писатель поднимает до 191029, только если ниже
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // части rich-ошибок пишутся автоматически
  finally
    Book.Free;
  end;
end;

Части Rich Data вы никогда не строите сами. Перед записью TXLSXWorkbook сканирует каждую ячейку на значения #SPILL! или #CALC!, создаёт или расширяет xl/metadata.xml и две части rich value и срезает протухшие ссылки value-метаданных с ячеек, чья ошибка с тех пор сменилась или очистилась. Явное присваивание CalcId выше — необязательное и показано лишь затем, чтобы подчеркнуть: оно уважается

Текстовые кэши массивов живут в t="str", а не в shared strings

Тот же тихий отказ проявляется и для текстовых результатов внутри массивов. HotXLS сохраняет кэшированный текст ячейки формулы и каждой ячейки-последователя в диапазоне CSE или динамического массива как t="str" с литеральной строкой в <v>. Запись этих кэшей shared strings с t="s" заставляет Excel показывать #VALUE! в ячейках массива при чтении кэшированного файла

<!-- Верно: литеральный текстовый кэш на ячейке-последователе массива -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Неверно: индекс shared-string, Excel показывает #VALUE! из кэша -->
<c r="B3" t="s"><v>17</v></c>

Здесь легко ошибиться, потому что t="s" — правильный выбор для обычных ячеек значений, и большинство писателей по умолчанию гонят каждую строку через таблицу shared strings. Ловушка — в проверке. Пересчитайте в Excel, и ячейки массива покажут правильный текст: это доказывает, что формула в порядке, и не доказывает ничего о записанном вами кэше. Если ваши пользователи открывают отчёты с ручным пересчётом или нисходящий инструмент читает кэшированные значения без вычисления, shared-string-версия просто сломана

Синтаксис хранения формул: ведущее равенство, запятые и _xlws

Смежная чистка попадает в тот же писатель: формулы, уходящие в OOXML, хранятся в файловой грамматике, а не в той, что напечатал пользователь. HotXLS хранит любой текст, присвоенный Formula, для отображения при обратной поездке, но сериализованная формула сбрасывает ведущий =, ставит запятые между аргументами функций, а разделители строк массивных констант и текст в кавычках не трогает. FILTER и SORT хранятся с префиксом _xlfn._xlws., которого Excel ждёт от этих функций только-для-листов, тогда как прочие future-функции вроде SEQUENCE получают простой _xlfn.-префикс

// Набрано с разделителями-точками с запятой, сохранено как
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // нет строк выше 1000 — даёт #CALC!, сохраняется с Rich Data

Порядок внутри писателя имеет значение. XML-экранирование идёт после нормализации синтаксиса и никогда до — по той же причине, по которой диагностическим пакетам нужен парсер: переписывание разделителей на уже экранированном тексте измяло бы сущности вроде &gt; в операторах сравнения

Как проверить кэшированную ошибку, не обманывая себя

Единственный заслуживающий доверия тест — открыть файл в Excel с уже выставленным ручным расчётом, прочитать ячейки и лишь затем пересчитать. Excel наследует режим расчёта от первой книги, открытой в сессии, поэтому наш приёмочный стенд сперва создаёт собственную затравочную книгу, переключает её на ручной режим, и только потом открывает испытуемый файл. Пробы, пропускавшие шаг затравки, доказывали лишь, что файл открылся и правильно посчитался. Всё, что они помечали как кэш, уже было пересчитано

Это различие — вся статья в миниатюре. Кэш современной ошибки правилен, когда Excel показывает его до всякого расчёта, и каждое требование выше — от GUID в нижнем регистре до текстовых последователей t="str" — было найдено проверкой именно этого состояния, а не пересчитанного. О том, как HotXLS вообще размечает и держит spill-диапазоны, смотрите формулы разлива динамических массивов в Delphi с HotXLS. Если ваш симптом — запрос на починку, а не неверная ошибка, лучше стартовать с правил уровня пакета в том, почему Excel чинит валидный XLSX

FAQ

Нужна ли HotXLS какая-то настройка, чтобы правильно сохранять #SPILL! и #CALC!?

Нет. HotXLS распознаёт значения #SPILL! и #CALC! в момент сохранения и автоматически пишет части Rich Data, fileVersion, минимальный calcId и фичу microsoft.com:RD

Почему XML ячейки всё ещё содержит #VALUE!?

У унаследованного типа ошибок SpreadsheetML нет литерала для #SPILL! или #CALC!, поэтому #VALUE! — обратно совместимый фоллбэк. Текущий Excel читает настоящую ошибку через ссылку value-метаданных vm

Понизит ли HotXLS CalcId, заданный мной?

Нет. TXLSXWorkbook.CalcId поднимается до 191029 в записанном файле только при наличии современной ошибки, а более высокое настроенное вами значение сохраняется как есть

HotXLS читает и пишет XLSX, XLSM и унаследованный XLS из Delphi и C++Builder собственным движком расчёта, динамические массивы включительно. Подробности и пробная загрузка — на странице HotXLS Delphi spreadsheet component