Excel формулы в BIFF экспорте

Начиная с версии FR VCL 4.11.15 экспорт BIFF умеет экспортировать формулы. Например, чтобы экспортировать формулу SUM(A1:B2) нужно положить на отчёт TfrxMemoView и записать в нём

=SUM(A1:B2)

Первым символом должен быть знак равенства “=”, а оставшаяся часть – правильной Excel формулой. Экспорт формул управляется свойством BIFF экспорта ExportFormulas и чтобы отключить эту возможность, достаточно написать

procedure DisableFormulas(Exp: TfrxBIFFExport);
begin
Exp.ExportFormulas := False
end;



По умолчанию экспорт формул включён.

Что произойдёт, если в отчёте встретилась неправильная формула? Экспорт попытается её обработать, но обнаружив ошибку, сохранит формулу в xls файле в виде обычной текстовой ячейки.

Какие формулы поддерживает экспорт

Экспорт поддерживает Excel формулы. Нужно заметить, что синтаксис формул в Excel, OpenOffice и LibreOffice различается в деталях и хотя в большинстве случаев с этими различиями не придётся столкнуться, всё же стоит иметь ввиду, что поддерживается именно Excel-синтаксис. Один из примеров таких различий в форме записи ссылки на внешнюю ячейку. Допустим в отчёте есть страницы PageA и PageB и нужно, чтобы в ячейке A1 на странице PageA была сумма первых десяти ячеек третьего столбца со страницы PageB. Это можно записать такой формулой в Excel:

=SUM(PageB!C1:C10)

в то же время, эта формула записывается в LibreOffice по другому:

=SUM($PageB.C1:C10)

Нужно иметь ввиду, что BIFF экспорт поддерживает только первую форму записи.

Операторы

Формулы в Excel позволяют использовать разнообразные операторы и функции. Ниже перечислены те из них, что поддерживаются в BIFF экспорте:

1. Унарные операторы + – и бинарные операторы + – * / ^ а также операторы сравнения < <= = >= > <>
2. Унарный оператор % который пишется после операнда и делит его на 100. Например формула =A1% равносильна =A1/100
3. Оператор : который создаёт диапазон ячеек. Чтобы посчитать сумму первых трёх ячеек столбца G можно написать =SUM(G1, G2, G3) или =SUM(G1:G3)
4. Оператор & соединяющий строки: = “abc” & “def” равносильно =”abcdef”
5. Оператор ! который позволяет сделать ссылку на ячейку или диапазон ячеек расположенных на другой странице того же документа. Такой пример уже был: =SUM(PageB!C1:C10) Есть и другие разновидности оператора ! но они пока что не поддерживаются экспортом. Их можно добавить, если в этом будет необходимость у пользователей экспорта.
6. Оператор пересечения областей, обозначаемый пробелом. например выражение A2:C2 B1:H8 равно B2

Строки

Экспорт поддерживает два вида строк, различающиеся обрамляющими их кавычками: ‘abc’ и “abc”. Чтобы вставить в строку символ кавычки, можно или удвоить его или применить другие обрамляющие кавычки. Например следующие строки идентичны: “abc””def” и ‘abc”def’

При вызове внешней ячейки оператором ! можно использовать строки, если имя страницы содержит пробелы или написано национальным алфавитом. Следующие формулы равносильны:

=SUM(PageB!C1:C10)
=SUM(&amp;amp;amp;amp;quot;PageB&amp;amp;amp;amp;quot;!C1:C10)
=SUM('PageB'!C1:C10)

Последние два способа записи позволяют использовать сложные имена страниц: =SUM(“Another Page In This Document”!C1:C10)

Функции

В формулах можно вызывать стандартные Excel функции. Одна из таких известных функции это SUM – она суммирует свои аргументы. Excel поддерживает очень много функций. Экспорт BIFF поддерживает лишь примерно 150 из них. Среди этих функций есть широко используемые SUM, AVERAGE, INDIRECT, MIN, MAX, AND, OR и так далее. Чтобы добавить подержку новой функции в экспорт, достаточно в файле frxBIFF.pas дописать одну строчку:

class procedure TBiffFormulaFuncList.Init;
begin
if GetCount &amp;amp;amp;amp;amp;amp;gt; 0 then
Exit;

{ http://sc.openoffice.org/excelfileformat.pdf
http://msdn.microsoft.com/en-us/library/dd904817.aspx }

Add(0,    'count',        1,  30, 'v', 'r');
Add(1,    'if',           2,  3,  'r', 'vr');
Add(2,    'isna',         1,  1,  'v', 'v');

&amp;amp;amp;amp;amp;amp;lt;...&amp;amp;amp;amp;amp;amp;gt;

Add(362,  'maxa',         1,  30, 'v', 'r');
Add(363,  'mina',         1,  30, 'v', 'r');
end;

Описание функций можно найти по приведённым ссылкам. Можно также сообщить мне, что нужна такая то функция и я добавлю её описание.

Технические детали

Формула в xls файле представлена как обычная ячейка. Она выглядит как запись с кодом 6 (http://msdn.microsoft.com/en-us/library/dd908919.aspx), внутри которой указаны строка, столбец, настройки форматирования (индекс XF записи), результат формулы и код.

Код формулы это набор инструкций переменной длины, оперирующих в рамках виртуальной машины без регистров со стеком LIFO. Инструкции можно разделить на две группы: те, что заталкивают на стек новые значения и те, что забирают значения со стека, делают какую то операцию над ними и возвращают результат на стек. Пример простой формулы: int(1) int(2) add Первые две инструкции кладут на стек две целых 4-байтных числа 1 и 2, а третья – забирает два значения со стека, складывает их и возвращает сумму на стек, после чего в стеке находится всего одно число: 3.

Одну и ту же формулу можно представить разным набором инструкций. BIFF экспорт старается выбирать те инструкции, что занимают меньше места. Для примера рассмотрим простую формулу состоящую всего из одного числа: =-7.0 Есть два способа записать код этой формулы:

double(-7.0)

этот код занимает 9 байт; и второй вариант:

int(7)
neg

этот код занимает 6 байт. То же самое верно и для более сложных случаев.

Leave a comment