Кто-то создал эту книгу много лет назад. Она пересчитывается при изменении данных, итоги меняются так, как уже никто не предскажет, и никакой документации нет — потому что формулы и есть документация. Чтение чисел не расскажет, как они были получены. Чтение правил — расскажет.
Spire.XLS for JavaScript компилирует движок электронных таблиц в WebAssembly, поэтому приложение на React может открыть существующий .xlsx в браузере, обойти его ячейки и извлечь правило, стоящее за каждой из них. Книга перемещается через виртуальную файловую систему (VFS), поэтому ничего не загружается на сервер и бэкенд не задействован.
К каждой ячейке задаются два вопроса: содержит ли она формулу и, если да, что эта формула говорит? Первый — это проверка свойства. Второй — это чтение. Практически всё в этой статье вытекает из того, чтобы не смешивать эти два действия.
О настройке проекта см. Интеграция Spire.XLS for JavaScript в проект на React. Примеры ниже предполагают, что пакет установлен и модуль WebAssembly инициализирован.
Когда нужны формулы, а не числа
Причина читать правила, а не значения, почти всегда одна из следующих:
- Принять на себя модель, которую никто не документировал. Правила — единственное сохранившееся описание того, что делает книга.
- Перенос вычислений из электронной таблицы. Чтобы реализовать вычисление в коде приложения заново, нужно знать точное выражение, а не только его последний результат.
- Проверка согласованности. Одна строка, тихо использующая иное правило, чем окружающие её строки, незаметна в значениях и очевидна в формулах.
- Подготовка запроса на изменение. Список ячеек и содержащихся в них правил — это то, что бизнес-пользователь может просмотреть и исправить.
- Проверка книги, созданной вашим собственным кодом. Подтверждение того, что записано именно то, что сохранилось — о стороне записи этой пары см. Как вставлять формулы и функции Excel в JavaScript (React).
Предварительные требования
Вам нужен проект на React с установленным Spire.XLS for JavaScript и инициализированным модулем WebAssembly, доступным по адресу window.wasmModule.spirexls. Книга, которую вы хотите проверить, уже должна находиться в VFS — загружена из общей папки вашего приложения с помощью FetchFileToVFS или записана туда в виде байтов, если она пришла откуда-то ещё.
Если результат будет форматироваться — ширины столбцов и тому подобное — загрузите в VFS также шрифт, как это делает пример.
Два вопроса к каждой ячейке
Начните с того, что запросите у листа область, которую он действительно использует:
// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;
for (const cell of usedRange.Cells) {
if (cell.HasFormula) {
// this cell holds a rule
}
}
AllocatedRange — это та половина фрагмента, которая вас защищает. Перебор A1:Z1000 на листе с двенадцатью используемыми строками тратит большую часть времени на пустые ячейки и оставляет вам их фильтрацию после. Запрос у листа его выделенной области делает цикл пропорциональным содержимому, что важно, как только книга становится реальной.
Затем HasFormula решает, что стоит читать. Это обычное логическое значение, и оно отвечает ровно на один вопрос — содержит ли ячейка формулу — который, как выясняется, является более узким вопросом, чем кажется.
Полный пример
Компонент ниже загружает существующую книгу, обходит её используемый диапазон и записывает каждую найденную формулу в новый лист в виде удобочитаемой строки — адрес ячейки и хранящееся в ней правило:
function App() {
const readFormulasAndFunctions = async () => {
// Get the Spire.XLS WASM module
const xlsModule = window.wasmModule?.spirexls;
// Check whether the module is ready
if (!xlsModule) {
alert('Spire.Xls is not ready yet');
return;
}
// Load the font and Excel file into the VFS
await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
const inputFileName = 'FormulasAndFunctions.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);
// Create a Workbook object
const workbook = new xlsModule.Workbook();
// Load the Excel workbook
workbook.LoadFromFile({ fileName: inputFileName });
// Get the first worksheet
const sheet = workbook.Worksheets.get(0);
// Get the used cell range of the worksheet
const usedRange = sheet.AllocatedRange;
// Create an output workbook
const output = new xlsModule.Workbook();
const outSheet = output.Worksheets.get(0);
let outRow = 1;
// Loop through the used cells
for (const cell of usedRange.Cells) {
// Check whether the cell contains a formula or function
if (cell.HasFormula) {
// Get the cell name
const cellname = cell.RangeAddressLocal;
// Get the formula or function in the cell
const formula = cell.Formula;
// Write the cell name and formula that were read
outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
outRow += 1;
}
}
// Set the output column width so the text displays completely
outSheet.SetColumnWidth(1, 45);
// Save the output workbook
const outputFileName = 'ReadFormulasAndFunctions_output.xlsx';
output.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });
// Release resources
output.Dispose();
// Read the converted file from the VFS and trigger a download
const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
a.click();
URL.revokeObjectURL(url);
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Read Formulas and Functions</h1>
<button onClick={readFormulasAndFunctions}>
Start
</button>
</div>
);
}
export default App;
Чтение формул и результатов функций из листов Excel
Обратите внимание, что код делает с исходной книгой: он только читает её, и ничего больше. Для вывода создаётся второй Workbook, поэтому проверяемый файл никогда не изменяется. Это важно, когда вы изучаете чужой документ — проверка должна быть неразрушающей по своей конструкции, а не за счёт того, что вы помните о необходимости не сохранять.
Формула или значение
Именно здесь узкое понимание HasFormula окупается, потому что свойства, которые можно прочитать из ячейки, возвращают не одно и то же:
| Свойство | Что вы получаете | Когда к нему прибегать |
|---|---|---|
HasFormula |
Содержит ли ячейка формулу | При отборе диапазона перед чтением чего-либо |
Formula |
Строка формулы в том виде, в каком она хранится — =SUM(B1:F1)
|
Вам нужно правило |
FormulaNumberValue |
Числовой результат вычисления этой формулы | Вам нужно число, которое произвело правило |
NumberValue |
Число, содержащееся в ячейке данных | Ячейка содержит данные, а не правило |
Text |
Текст в том виде, в каком он записан в ячейку | Вам нужна отображаемая строка |
Наибольшую путаницу вызывает пара Formula и FormulaNumberValue: одна и та же ячейка, два совершенно разных ответа. Один — это правило; другой — то, что правило произвело. Запросите не тот — и получите технически корректное значение, которое не является тем, что вы искали: аудит формул, возвращающий числа, или извлечение значений, возвращающее формулы.
Составление описи формул
В примере каждое найденное совпадение записывается во вторую книгу и скачивается. Это правильная форма, когда сама опись является документом — тем, что можно передать проверяющему или приложить к заявке.
Когда же опись предназначена для отображения на экране, сначала соберите те же данные, а затем решите, как их представить:
// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
if (cell.HasFormula) {
inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
}
}
Именно RangeAddressLocal делает результат пригодным для использования. Он возвращает адрес в собственной нотации листа — имя, которое человек использовал бы при обсуждении ячейки, — а не пару «строка и столбец», которая технически эквивалентна и практически нечитаема. С записью, которая говорит B7, можно работать; запись, которая говорит «строка 7, столбец 2», нужно сначала перевести.
Более одного листа
Приведённый выше цикл охватывает один лист. Опись на уровне всей книги означает повторение его по очереди для каждого листа, при этом каждый лист извлекается так же, как первый, с помощью Workbook.Worksheets.get(i), принимающего индекс.
Прежде чем масштабировать это, стоит правильно сделать две вещи. Записывайте, с какого листа пришла каждая запись, потому что B7 на двух листах — это две разные ячейки, а список, который их не различает, становится неоднозначным именно в тот момент, когда это важно. И держите столбец вывода достаточно широким — адреса и строки правил длинные, а обрезанная опись хуже узкой.
Почему ячейка с формулой может остаться незамеченной
Ячейка, отображающая =SUM(B1:F1), не обязательно содержит формулу. Если она была записана через Text или Value вместо Formula, или введена в ячейку, уже отформатированную как текст, то символы хранятся как строка. Лист показывает формулу; ячейка содержит метку.
HasFormula корректно сообщает об этом как false, и сканирование, ожидающее найти эту ячейку, ничего не находит. В этом и заключается ловушка данного рабочего процесса: это не выглядит как сбой — книга наглядно содержит формулы, код выполняется без ошибок, а в описи не хватает ровно столько ячеек, сколько было введено как текст.
Когда формула, по-видимому, отсутствует в описи, проверьте, как она была записана, прежде чем проверять код чтения. Если книга создаётся вашим собственным приложением, это то же самое различие свойств, которое вставка формул рассматривает со стороны записи.
Типичные проблемы
Сканирование ничего не находит, но лист полон формул.
Они хранятся как текст. См. раздел выше — HasFormula сообщает только о настоящих формулах.
Результат — число, хотя мне нужна была формула, или наоборот.
Вы прочитали не то свойство. Formula даёт правило, FormulaNumberValue даёт вычисленное число.
Цикл работает медленно или создаёт сотни пустых записей.
Он обходит фиксированный прямоугольный диапазон вместо выделенной области листа. Используйте AllocatedRange в качестве источника перебора.
Отсутствуют ячейки со второго листа. Цикл выполняется на одном листе. Повторите его для каждого листа и сохраняйте имя листа рядом с каждой записью.
Исходная книга изменилась после выполнения.
Этого не должно было произойти — пример читает одну книгу, а записывает в другую. Убедитесь, что вывод сохраняется в другой объект Workbook, как в приведённом выше коде.
Часто задаваемые вопросы
Нужен ли установленный Excel, чтобы читать формулы из книги?
Нет. Движок входит в состав пакета и работает как WebAssembly внутри браузера. Исходное приложение для работы с электронными таблицами нигде не задействовано.
Можно ли прочитать вычисленное значение вместо формулы?
Да. Читайте FormulaNumberValue вместо Formula из той же ячейки. Сначала используйте HasFormula, чтобы задавать этот вопрос только тем ячейкам, где он имеет смысл.
Изменяет ли чтение книгу?
Чтение — нет. Пример открывает входной файл, создаёт отдельную выходную книгу для результатов и сохраняет только её, поэтому проверяемый файл остаётся таким, каким был.
Какие форматы Excel я могу читать?
Как устаревший формат .xls, так и современные файлы .xlsx поддерживаются одним и тем же API, поэтому книгу не нужно преобразовывать перед проверкой.
Работает ли это с книгами, хранящимися на сервере?
Да, если вы можете доставить байты в браузер. Запишите их в VFS и загружайте оттуда — само чтение полностью выполняется на стороне клиента, и книга загружается на сервер только если ваше приложение решит это сделать.