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

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


Код

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


Код

1
2
3
4

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

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

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

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

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


Код

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


Код

Нужно иметь ввиду, что 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("Another Page In This Document"!C1:C10)

Функции

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


Код

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

class procedure TBiffFormulaFuncList.Init;
begin
 if GetCount > 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');
 
 <...>
 
 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 Есть два способа записать код этой формулы:


Код

1

double(-7.0) 

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


Код

1
2

int(7)
neg 

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