FPSpreadsheet/ru
│
Deutsch (de) │
English (en) │
español (es) │
français (fr) │
polski (pl) │
русский (ru) │
THIS PAGE IS OUTDATED. PLEASE USE THE ENGLISH PAGE.
FpSpreadsheet библиотека предоставляет удобный способ создания и чтения табличных документов в различных форматах. Библиотека написана на очень гибкой основе, и поэтому её можно легко расширять поддержкой любого количества форматов.
Скриншот демонстрационной программы spready, предоставляемой с fpspreadsheet, показывающим файл XLS:
Документация
Эта страница вики включает последнюю версию FPSpreadsheet, доступную через subversion. Пожалуйста, см. раздел Стабильные версии для [получения] документации по стабильной версии, которую вы можете скачать.
Документация по API
Ссылка на API
Файл справки в формате CHM можно найти в папке установки FPSpreadsheet docs . Если вы еще не установили пакет, тогда следуйте сюда хттпс://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/docs/fpspreadsheet-api.chm для получения fpspreadsheet-api.chm.
--Zoltanleo (talk) 16:53, 10 September 2018 (CEST)В англоязычном оригинале ссылка на мануал битая. Контроллер внешних ссылок wiki не дает корректно вставить исправленную ссылку Второй файл CHM, доступный в папке docs , fpspreadsheet-wiki.chm , представляет собой снимок связанных с FPSpreadsheet страниц вики, объединенных в один файл справки.
Основы
Наименьшими объектами в электронной таблице являются «ячейки», содержащие данные. Ячейки могут содержать различные типы данных, такие как строки, числа, даты, время, логические значения или формулы. Кроме того, ячейки могут содержать информацию о форматировании, то есть стиль шрифта, цвет фона, выравнивание текста и т.д.
Ячейки расположены в сетке, называемой «рабочей таблицей», или «электронной таблицей», состоящей из «строк» и «столбцов». Каждая ячейка имеет уникальный адрес, заданный индексом строки и столбца.
Рабочие листы связаны вместе, формируя «книгу», которая представляет собой документ электронной таблицы приложения. В книге также хранится информация, необходимая для всех листов, то есть список шрифтов, списки сотовых и числовых форматов и т.д.
FPSpreadsheet следует этой же структуре - существует TCell, TsWorksheet и TsWorkbook .
[Рабочая] книга
Класс TsWorkbook является основным классом, видимым для пользователя. Он предоставляет методы для чтения данных и записи в файлы. Универсальная структура библиотеки обеспечивает доступ к различным популярным форматам файлов, таким как Excel .xls или .xlsx, или OpenOffice/LibreOffice .ods.
Формат файла определяется типом TsSpreadsheetFormat, определенным в модуле fpstypes
type
TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML,
sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);
где
- sfExcel2, sfExcel5, sfExcel8 означает версии бинарного формата xls, используемые Excel («BIFF» = «Формат файла двоичного обмена»), причем sfExcel8 является самым современным.
- sfOOXML соответствует новому формату xlsx, представленному Excel2007
- sfExcelXML - это xml-формат, который был представлен Microsoft для Office XP и 2003. Не очень популярен.
- sfOpenDocument - формат электронных таблиц, используемый OpenOffice/LibreOffice; по умолчанию файлы имеют расширение .ods.
- sfCSV</ tt> относится к текстовым файлам с разделителями-запятыми (расширение по умолчанию .csv); они могут быть поняты любым текстовым редактором и всеми программами электронных таблиц, но не содержат информации о форматировании.
- sfHTML обозначает стандартный формат HTML, используемый в веб-браузерах.
- sfWikiTable_Pipes и sfWikiTable_WikiMedia - это формат, используемый таблицами в вики-сайтах.
- sfUser необходим для регистрации пользовательского формата. Планируется внедрение «древних» форматов файлов, таких как Excel3.0/4.0 или Lotus. Тем не менее, вы можете предоставить свои собственные классы чтения и письма для расширения функциональности FPSpreadsheet - см. ниже раздел Добавление новых форматов файлов
При применении fpspreadsheet первая задача - создать экземпляр рабочей книги:
var
MyWorkbook: TsWorkbook;
begin
MyWorkbook := TsWorkbook.Create;
...
Чтение файлов электронных таблиц выполняется (среди прочего) методами рабочей книги
- procedure ReadFromFile (AFileName: string):
Читает файл с заданным именем и автоматически определяет правильный формат файла. - procedure ReadFromFile (AFileName: string; AFormat: TsSpreadsheetFormat):
Читает файл, но предполагает, что формат файла соответствует AFormat .
Для сохранения файла можно использовать следующие методы работы с книгой:
- procedure WriteToFile (const AFileName: string; const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False):
Записывает книгу в данный файл с использованием заданного формата электронной таблицы. Если файл уже существует, он автоматически перезаписывается, если AOverwriteExisting - true : - procedure WriteToFile (const AFileName: String; const AOverwriteExisting: Boolean = False):
dto., но формат файла определяется из предоставленного расширения файла (в случае Excel больше xls используется последняя версия, sfExcel8).
После вызова этих методов полезно посмотреть на свойство рабочей книги ErrorMsg, в котором собраны сообщения из-за ошибок или предупреждений, которые могли возникнуть во время чтения/записи. Это свойство возвращает многострочную строку, которая лучше всего отображается в компоненте memo; если все в порядке, это пустая строка.
Примечание. FPSpreadsheets предоставляет специализированные блоки для чтения и записи в каждый формат файла. Эти блоки недоступны автоматически, вы должны явно добавить их в предложение uses. FPSpreadsheet будет жаловаться на «неподдерживаемый формат файла», если запрошенный читатель/писатель не найден. Ниже приведен список названий устройств:
- xlsbiff2, xlsbiff5 и xlsbiff8 для двоичных форматов файлов xls sfExcel2, sfExcel5 и sfExcel8 , соответственно,
- xlsOOXML для формата xlsx sfOOXML для Excel 2007 и более поздних версий,
- xlsXML для XML-формата Excel XP и 2003,
- fpsopendocument для формата файла sfOpenDocument OpenOffice/LibreOffice,
- fpsCSV для текстовых файлов с разделенными запятыми значениями (csv),
- fpsHTML для файлов HTML,
- wikitables для sfWikiTable_Pipes и sfWikiTable_WikiMedia,
- или просто добавьте fpsallformats, чтобы получить поддержку чтения/записи для всех поддерживаемых форматов файлов.
Рабочий лист
В книге содержится список экземпляров TsWorksheet. Они соответствуют вкладкам, которые вы видите в Excel или Open/LibreOffice. При чтении файла электронной таблицы рабочие листы создаются автоматически в соответствии с содержимым файла. Когда электронная таблица создается вручную для хранения в файле, рабочий лист должен быть создан путем «добавления» в рабочую книгу:
var
MyWorkbook: TsWorkbook;
MyWorksheet: TsWorksheet;
begin
MyWorkbook := TsWorkbook.Create;
MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
// 'My_Table' - это «имя» рабочего листа
...
Доступ к уже имеющимся рабочим листам можно получить с помощью методов TsWorkbook
- function GetFirstWorksheet: TsWorksheet: извлекает первый рабочий лист книги.
- Функция GetWorksheetByIndex (AIndex: Cardinal): TsWorksheet: возвращает рабочий лист с заданным индексом (начиная с 0).
- function GetWorksheetByName (AName: String): TsWorksheet: возвращает рабочий лист с заданным именем, которое было использовано при добавлении рабочего листа.
Количество уже существующих рабочих листов можно запросить, вызвав GetWorksheetCount.
Ячейка
Рабочий лист, наконец, дает доступ к ячейкам. Недавно созданный рабочий лист, как в приведенном выше примере, пуст и не содержит никаких ячеек. Клетки добавляются путем присвоения им данных или атрибутов одним из методов WriteXXXX рабочего листа. Как уже упоминалось, ячейка адресуется индексом строки и столбца, к которой она принадлежит. Как обычно, индексы строк и столбцов начинаются с 0. Поэтому ячейка «A1» относится к строке 0 и столбцу 0. Следует отметить, что индекс строки и столбца всегда указывается в этом порядке, это отличается от соглашения TStringGrid. В следующем примере создается ячейка по адресу A1 и помещается в нее число 1.0.
var
MyWorkbook: TsWorkbook;
MyWorksheet: TsWorksheet;
begin
MyWorkbook := TsWorkbook.Create;
MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
MyWorksheet.WriteNumber(0, 0, 1.0); // "A1" имеет строку = 0 и столбец = 0
...
Также можно напрямую обращаться к ячейкам с помощью методов FindCell(ARow, ACol) или GetCell(ARow, ACol) рабочей книги. Обе функции существуют также в перегруженной версии, к которой адрес ячейки может быть передан в нотации Excel: FindCell(ACellStr: String) или GetCell(ACellStr: String). Имейте в виду, что эти функции возвращают указатель pointer к данным ячейки (тип PCell). Не забудьте разыменовать указатели! Разница между FindCell и GetCell заключается в том, что первая возвращает nil</ tt>, если ячейка еще не существует, а вторая создает пустую ячейки в этом случае.
if MyWorksheet.FindCell('A1') = nil then
WriteLn('Ячейка A1 не существует.');
Основа записи TCell
Это объявление типа данных ячейки:
type
TCell = record
{ Location of the cell }
Worksheet: TsWorksheet;
Col: Cardinal; // zero-based
Row: Cardinal; // zero-based
{ Index of format record }
FormatIndex: Integer;
{ Status flags }
Flags: TsCellFlags; // (cfHasComment, cfMerged, cfHyperlink, ...)
{ Cell content }
UTF8StringValue: String; // строки не могут быть частью варианта записи
case ContentType: TCellContentType of // должно быть в конце объявления
cctEmpty : (); // не имеет данных вообще
cctFormula : (); // UTF8StringValue находится вне варианта записи
cctNumber : (Numbervalue: Double);
cctUTF8String : (); // FormulaValue находится вне варианта записи
cctDateTime : (DateTimevalue: TDateTime);
cctBool : (BoolValue: boolean);
cctError : (ErrorValue: TsErrorValue);
end;
PCell = ^TCell;
Поле ContentType указывает, какой тип данных хранится в ячейке:
type
TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);
Согласно этому полю соответствующие данные можно найти в полях
- NumberValue (для ContentType = cctNumber), или
- UTF8StringValue (для ContentType = cctUTF8String), или
- DateTimeValue (для ContentType = cctDateTime) или
- BoolValue (для ContentType = cctBool), то есть TRUE или FALSE, или
- ErrorValue (для ContentType = cctError).
Из-за использования варианта записи большинство из этих значений перекрываются, то есть модификация NumberValue влияет также на другие значения. Поэтому всегда обращайте внимание на ContentType при непосредственном доступе к записи TCell (описанные ниже методы рабочего листа рассматривают это автоматически).
Поле Flags указывает, связаны ли дополнительные данные с ячейкой, которые не включены в запись ячейки, как правило, для сохранения памяти:
type
TsCellFlag = (cfHasComment, cfHyperlink, cfMerged, cfHasFormula, cf3dFormula);
TsCellFlags = set of TsCellFlag;
- cfHasComment: запись комментарий находится в Комментарии рабочего листа.
- cfHyperlink: ячейка содержит гиперссылку гиперссылка, хранящуюся в Hyperlinks рабочего листа.
- cfMerged: ячейка принадлежит блоку объединенные блоки и распространяется через несколько ячеек.
- cfHasFormula: ячейка связана с формулой, которая хранится в Formulas рабочего листа.
- cf3dFormula: Формула, связанная с ячейкой, содержит элементы, ссылающиеся на другие листы той же книги.
Примечание. После вычисления формулы или после прочтения файла ContentType ячейки формулы преобразуется в формулу результата. Тогда наличие формулы может быть обнаружено только путем вызова функции HasFormula(cell) для запрашиваемой ячейки (cell: PCell) в модуле fpsUtils ; эта функция проверяет наличие элемента cfHasFormula в флажках ячейки.
Как добавлять и считывать данные в/из ячейки
Добавление значений в ячейку наиболее легко сопровождается использованием одного из методов WriteXXXX рабочего листа. Наиболее важными являются:
type
TsWorksheet = class
...
{ Запись денежных значений }
function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
ANegCurrFormat: Integer = -1): PCell; overload;
procedure WriteCurrency(ACell: PCell; AValue: Double;
ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
ANegCurrFormat: Integer = -1); overload;
function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
procedure WriteCurrency(ACell: PCell; AValue: Double;
ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
{Запись значений дата/время }
function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
ANumFormatStr: String): PCell; overload;
procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
ANumFormatStr: String); overload;
{ Запись числовых значений }
function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;
procedure WriteNumber(ACell: PCell; ANumber: Double;
ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;
function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
procedure WriteNumber(ACell: PCell; ANumber: Double;
ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
{ Запись строковых значений }
function WriteText(ARow, ACol: Cardinal; AText: ansistring;
ARichTextParams: TsRichTextParams = nil): PCell; overload;
procedure WriteText(ACell: PCell; AText: String;
ARichTextparams: TsRichTextParams = nil); overload;
// старые методы строк «WriteUTF8Text» сейчас устарели
...
Некоторые из этих методов существуют в перегруженных версиях, в которых параметры форматирования ячейки могут быть добавлены вместе со значением ячейки. Соответственно, для записи также существует несколько методов worksheet для чтения значений ячеек:
type
TsWorksheet = class
...
{ Чтение содержимого ячейки как строки }
function ReadAsText(ARow, ACol: Cardinal): string; overload;
function ReadAsText(ACell: PCell): string; overload;
function ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;
{ Чтение содержимого ячейки как числа }
function ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
function ReadAsNumber(ACell: PCell): Double; overload;
function ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;
{ Чтение содержимого ячейки как значения дата/время }
function ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
function ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
...
Формат Index to Cell
FormatIndex является индексом записи формата ячейки cell format record. Он описывает атрибуты форматирования ячейки. Эти записи собираются по внутреннему списку рабочей книги и определяются следующим образом:
type
TsCellFormat = record
FontIndex: Integer;
TextRotation: TsTextRotation;
HorAlignment: TsHorAlignment;
VertAlignment: TsVertAlignment;
Border: TsCellBorders;
BorderStyles: TsCellBorderStyles;
Background: TsFillPattern;
NumberFormatIndex: Integer;
NumberFormat: TsNumberFormat;
NumberFormatStr: String;
BiDiMode: TsBiDiMode; // bdDefault, bdLTR {left-to-right}, bdRTL {right-to-left)
Protection: TsCellProtection; // cpLockCell, cpHideFormulas
UsedFormattingFields: TsUsedFormattingFields;
//uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign, uffBiDi
end;
- FontIndex: текстовый шрифт, указывает индекс в списке шрифтов книги
- TextRotation: указывает, будет ли текст ячейки написан горизонтально или вертикально
- HorAlignment</ tt>: выравнивание по левому краю, по горизонтали или по правому краю
- VertAlignment: верхний, нижний или вертикально центрированный текст
- Border</ tt>: набор флагов, указывающий, что - если установлено - линия границы нарисована в левом, верхнем, правом или нижнем краях ячеек. Линии рисуются в соответствии с BorderStyles, которые определяют линейность и цвет границы.
- Фон: запись, определяющая фоновое заполнение ячейки (стиль паттерна, цвет паттерна и цвет фона - см. главу фон ячейки ниже).
- NumberFormat и NumberFormatStr указывает способ форматирования числа или даты/времени (например, числа десятичных знаков, длинного или короткого формата даты и т.д.).
- При форматировании ячейки учитываются только те атрибуты формата, для которых установлен флаг в UsedFormattingFields. Если флаг не включен, соответствующий атрибут игнорируется и заменяется значением по умолчанию.
Для указания формата для данной ячейки вызывается соответствующий метод рабочего листа WriteXXXX, для получения вызова формата ReadXXXX. Эти методы обычно получают указатель на ячейку в качестве параметра, но также есть перегруженные версии, которые принимают индекс строки и столбца. Более того, стили форматирования также могут быть применены непосредственно к ячейке с помощью вспомогательного средства записи, реализованного в модуле fpsCell.
См. форматирование ячейки ниже для [получения] более подробного описания.
Столбцы и строки
Записи столбцов и строк добавляются для каждого столбца и строки с размером, отличным от значения по умолчанию:
type
TCol = record
Col: Cardinal;
Width: Single;
ColWidthType: TsColWidthType; // = (cwtDefault, cwtCustom)
FormatIndex: Integer;
end;
PCol = ^TCol;
TRow = record
Row: Cardinal;
Height: Single;
RowHeightType: TsRowHeightType; // = (rhtDefault, rhtCustom, rhtAuto)
FormatIndex: Integer;
end;
PRow = ^TRow;
[Свойства] Column widths и row heights могут быть указаны в различных единицах, определенных типом TsSizeUnits = (suChars, suLines, suMillimeters, suCentimeters, suPoints, suInches). suChars относится к счету символов 0, вписывающихся в ширину столбца, - таким образом Excel определяет ширину столбца. suLines - количество линий, вписывающихся в высоту строки. Оба устройства основаны на размере символа шрифта по умолчанию для книги. Другие единицы - условные физические единицы длины (1 см = 10 мм, 1 дюйм = 25,4 мм = 72 пт). Принимаются дробные значения. Рабочая книга и рабочие листы хранят длины внутри в миллиметрах (MyWorkbook.Units).
Приложения Office обычно настраивают высоту строки автоматически в соответствии с шрифтом или вращением текста [внутри] содержимого ячейки. Этот случай идентифицируется с помощью RowHeightType
, имеющего значение rhtAuto
. Поскольку рабочий лист не может рассчитывать размер текста очень точно, автоматические высоты строк не записываются в FPSpreadsheet; они заменяются на default row height. Высота строки по умолчанию также используется, если строка пуста, т.е. не содержит никаких ячеек данных. Его значение можно изменить, вызвав WriteDefaultRowHeight()
или используя свойство DefaultRowHeight
. В WriteDefaultRowHeight
единицы должны быть указаны в DefaultRowHeight
, [где] они считаются линиями. Аналогично, default column width может быть задана с помощью WriteDefaultColWidth()
или свойством DefaultColWidth
(в символах).
Для того, чтобы перехватить [установку] высоты строк автоматически и по умолчанию, вызовите метод WriteRowHeight(). Эти записи строк идентифицируются с помощью RowHeightType
, имеющего значение rhtCustom
. Точно так же ширина столбцов может быть установлена на определенное значение вызовом WriteColWidth(). ColWidthType
этих столбцов cwtCustom
.
Высоту/ширину конкретной строки/столбца можно получить с помощью методов GetRowHeight или GetColHeight. Обратите внимание, что эти методы возвращают ширину строк/столбцов по умолчанию, если нет записей TRow/TCol.
type TsWorksheet = class
...
{ Установка высоты строки }
procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
{ Установка ширины столбца }
procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
{ Установка высоты строки по умолчанию }
procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
{Установка ширины столбца по умолчанию }
procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);
{ Возврат высоты строки }
function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
{ Возврат ширины столбца }
function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
{ Возврат высоты строки по умолчанию }
function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
{ Возврат ширины столбца по умолчанию }
function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;
property DefaultRowHeight: Single; // в линиях
property DefaultColWidht: Single; // в символах
Существуют также перегруженные версии этих методов, для которых не требуется параметр AUnits. В этом случае высота строк определяются в терминах количества строк, а ширина столбцов определяется в терминах количества символов. Обратите внимание, что эти варианты из предыдущих версий и теперь устарели.
Элемент FormatIndex
формата строк и столбцов применяется ко всей строке или столбцу. Как и в ячейках, эти форматы хранятся в виде записей TsCellFormat
во внутреннем списке книг. Row and column formats в основном применяются к пустым ячейкам, но если добавлена новая ячейка, она автоматически получит формат строки или столбца. (Если обе строки и столбец имеют разные форматы, тогда будет использоваться формат строки).
Formulas
Two kinds of formulas are supported by FPSpreadsheet:
- String formulas: These are written in strings just like in the office applications, for example "=ROUND(A1+B1,0)". They are used internally in the files of Open/LibreOffice and Excel .xlsx.
- RPN formulas are used internally by the binary .xls Excel files. They are written in Reverse Polish Notation (RPN), for example: A1, B1, Add, 0, ROUND. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated.
In older versions of FPSpreadsheet, formulas were stored directly in the cell record. This was given up to have parsed formulas available for faster calculation. The formulas are stored in the tree Formulas of the worksheet. The formula record contains the row and column index of the cell to which the formula belongs, the string representation of the formula, as well as the parser tree for quick evaluation.
FPSpreadsheet supports the majority of the formulas provided by the common spreadsheet applications. However, when reading a file created by these applications, there is always a chance that an unsupported formula is contained. To avoid crashing of fpspreadsheet, reading of formulas is disabled by default; the cell displays only the result of the formula written by the Office application. To activate reading of formulas add the element boReadformulas to the workbook's Options before opening the file. If an error occurs in this case the reader normally catches the exception, writes the exception message into the workbook's errorlog and continues reading. If you want reading to stop you must add the boAbortReadingOnFormulaError to the workbook Options.
Formulas can link to data in other sheets of the same workbook ("3d formulas") by applying the Excel syntax (see below). External links to spreadsheets in other files are not supported.
Calculation of formulas is normally not needed when a file is written by FPSpreadsheet for opening in an Office application because that automatically calculates the formula results. If the same file, however, is openend by an application based on FPSpreadsheet the calculated cells would be empty because the formulas are not automatically calculated by default. To activate calculation of formulas before writing a spreadsheet to file you have to add the option boCalcBeforeSaving to the workbook's Options.
If FPSpreadshet is used in an interactive application (such as the spready demo found in the examples folder of the FPSpreadsheet installation) it is desirable to calculate formulas automatically whenever formula strings or cell values are changed by the user. This can be achieved by the option boAutoCalc in the workbook's Options.
The most general setting regarding formulas, therefore, is
MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];
Calculation of formulas can be triggered manually by calling the method CalcFormulas of the worksheet or workbook. The latter is absolutely required when the workbook contains 3d formulas where the result of one cell can affect cells in other sheets. If there are only within-sheet formulas then the worksheet's CalcFormulas is sufficient.
String formulas
String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is WriteFormula:
var
MyWorksheet: TsWorksheet;
//...
MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
// By default, use dot as decimal and comma as list separator!
A few notes on syntax:
- The leading = character which identifies formulas in the Office applications is not absolutely necessary here and can be dropped. The formula is stored in the cell record without it.
- The case of the formula name is ignored.
- Spaces can be added for better readability, but they will be lost when saving.
- Strings must be enclosed in double quotes.
- The corner points of a cell range must be separated by a colon (":"), e.g. A1:C3. Unordered ranges will be rearranged when the formula is parsed, i.e. C3:A1 becomes A1:C3.
- Links to other worksheets must follow the Excel syntax which separates the sheetname and cell address by a "!". An single cell, for example, can be linked by Sheet1!A1. An range of sheets must be placed before the cell or cell range, e.g. Sheet1:Sheet2!A1:C3. Note that the Open/LibreOffice syntax with a separating point and reference to the corner points of the 3d box (i.e., Sheet1.A1:Sheet2.C3) is not supported. Note also that the worksheet(s) to which the formula links must exist at the time when the formula is added; otherwise the link will be replaced by the error code #REF!, and the formula will not be usable even if the missing sheet is added later.
- Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
- Setting the optional parameter ALocalized of the worksheet methods WriteFormula to TRUE, however, allows to use localized decimal and list separators taken from the workbook's FormatSettings - see spready demo.
var
MyWorksheet: TsWorksheet;
//...
MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
// Because of the "true" the formula parser accepts the comma as decimal and the
// semicolon as list separator if the workbook's FormatSettings are set up like this.
Use the worksheet methods ReadFormula or ReadFormulaAsString to retrieve the string formula assigned to the cell. The pointer to the cell must be given as a parameter. The latter function accepts an additional (boolean) parameter ALocalized to use the decimal and list separators of the workbook's FormatSettings for creation of the formula string.
var
MyWorksheet: TsWorksheet;
cell: PCell;
//...
cell := MyWorksheet.FindCell(0, 1);
WriteLn('The formula in internal format is ', MyworkSheet.ReadFormula(cell));
WriteLn('The localized formula is ', MyWorksheet.ReadFormulaAsString(cell, true));
//-------------------------------------------------------------------
// For the previous example, this will result in the following output
The formula in internal format is ROUND(A1+B2+1.215,0)
The localized formula is ROUND(A1+B2+1,215;0)
RPN formulas
At application level, string formulas are mainly used, and RPN formulas are of little practical importance. Therefore, documentation of RPN formulas has been removed from this main FPSpreadsheet wiki and can be found in the article "RPN Formulas in FPSpreadsheet".
- Shared formulas are only supported for reading (from Excel files).
- Array formulas are not supported, currently.
List of built-in formulas
FPSpreadsheet supports more than 80 built-in formulas. In order not to blow up this wiki page too much documentation of these formulas has been moved to the separate document "List of formulas".
To learn more about the functions available, look at file testcases_calcrpnformula.inc in the tests folder of the FPSpreadsheet installation where every function is included with at least one sample.
Extending FPSpreadsheet by user-defined formulas
Although the built-in formulas cover most of the applications there may be a need to access a formula which is available in the Office application, but not in FPSpreadsheet. For this reason, the library supports a registration mechanism which allows to add user-defined functions to the spreadsheets. This can be done by calling the procedure RegisterFunction from the unit fpsExprParser:
procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;
procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;
- AName specifies the name under which the function will be called in the spreadsheet. It must match the name of the formula in the Office application.
- AResultType is a character which identifies the data type of the function result:
- 'F' - floating point number
- 'I' - integer
- 'D' - date/time
- 'B' - boolean
- 'S' - string
- AParamTypes is a string in which each character identifies the data type of the corresponding argument. In addition to the list shown above the following symbols can be used:
- '?' - any type
- '+' - must be the last character. It means that the preceding character is repeated indefinitely. This allows for an arbitrary argument count. Please note, however, that Excel supports only up to 30 arguments.
- lowercase 'f', 'i', 'd', 'b', 's' indicate optional parameters of the type explained above. Of course, uppercase symbols cannot follow lower-case symbols.
- AExcelCode is the identifier of the function in xls files. See "OpenOffice Documentation of the Microsoft Excel File Format", section 3.11, for a list.
- ACallback identifies which function is called by FPSpreadsheet for calculation of the formula. It can either be a procedure or an event handler.
type
TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;
The TsExpressionResult is a variant record containing result or argument data of several types:
type
TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
rtCell, rtCellRange, rtError, rtAny);
TsExpressionResult = record
Worksheet : TsWorksheet;
ResString : String;
case ResultType : TsResultType of
rtEmpty : ();
rtError : (ResError : TsErrorValue);
rtBoolean : (ResBoolean : Boolean);
rtInteger : (ResInteger : Int64);
rtFloat : (ResFloat : TsExprFloat);
rtDateTime : (ResDateTime : TDatetime);
rtCell : (ResRow, ResCol : Cardinal);
rtCellRange : (ResCellRange : TsCellRange);
rtString : ();
end;
TsExprParameterArray = array of TsExpressionResult;
As an example we show here the code for the CONCATENATE() formula which joins two or more strings:
const
INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;
...
RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);
procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
// CONCATENATE( text1, text2, ... text_n )
var
s: String;
i: Integer;
begin
s := '';
for i:=0 to Length(Args)-1 do
begin
if Args[i].ResultType = rtError then
begin
Result := ErrorResult(Args[i].ResError);
exit;
end;
s := s + ArgToString(Args[i]);
// "ArgToString" simplifies getting the string from a TsExpressionResult as
// a string may be contained in the ResString and in the ResCell fields.
// There is such a function for each basic data type.
end;
Result := StringResult(s);
// "StringResult" stores the string s in the ResString field of the
// TsExpressionResult and sets the ResultType to rtString.
// There is such a function for each basic data type.
end;
There is a worked-out example (demo_formula_func.pas) in the folder examples/other of the FPSpreadsheet installation. In this demo, four financial functions (FV(), PV(), PMT(), RATE()) are added to FPSpreadsheet.
Unsupported formulas
Sometimes it is required to create files for the Office applications with formulas not supported by fpspreadsheet. This is possible to some extent when the workbook option boIgnoreFormulas is active. Then any arbitrary formula can be written to a cell, and the formula is not checked and not evaluated. The workbook can be written to an .ods or .xlsx file. The old xls file format cannot be used because the formula would have to be parsed to create the rpn formula needed.
In folder examples/other you can find the sample project demo_ignore_formula which creates an ods file with references to another data file - external references normally are not supported by fpspreadsheet, and thus the ignore-formulas workaround must be used. Note that this example does not work with xlsx because Excel writes information on the external links to separate xml files within the xlsx container.
--Zoltanleo (talk) 16:50, 10 September 2018 (CEST)Все ниже описанное требует правки и приведения текста в соответствии с англоязычным оригиналом
Формулы
В FPSpreadsheet поддерживаются два вида формул: строковый и RPN. Строковый вид записываются также как и в офисных приложениях, например, "ROUND (A1 + B1)", а RPN формулы записываются в обратной польской нотации (Reverse Polish Notation (RPN)), например: A1, B1, Add, 0, ROUND
RPN формулы
Пример использования встроенной процедуры
// Write the formula E1 = ABS(A1)
SetLength(MyRPNFormula, 2);
MyRPNFormula[0].ElementKind := fekCell;
MyRPNFormula[0].Col := 0;
MyRPNFormula[0].Row := 0;
MyRPNFormula[1].ElementKind := fekABS;
MyWorksheet.WriteRPNFormula(0, 4, MyRPNFormula);
// Write the formula F1 = ROUND(A1, 0)
SetLength(MyRPNFormula, 3);
MyRPNFormula[0].ElementKind := fekCell;
MyRPNFormula[0].Col := 0;
MyRPNFormula[0].Row := 0;
MyRPNFormula[1].ElementKind := fekNum;
MyRPNFormula[1].DoubleValue := 0.0;
MyRPNFormula[2].ElementKind := fekROUND;
MyWorksheet.WriteRPNFormula(0, 5, MyRPNFormula);
Встроенные процедуры
ABS - Возвращает абсолютное значение заданного числа. Принимает 1 аргумент. Пример: ABS (-1) = 1
ROUND - Округляет число до заданного числа десятичных знаков. Принимает 2 аргумента, сначало округляемое число, а затем количество десятичных знаков. Второй аргумент по умолчанию равен нулю и может быть отрицательным.
Примеры
Чтобы создать проект, который использует библиотеку fpspreadsheet, необходимо добавить пакет fpspreadsheet_pkg в ваш Lazarus проект или добавить корневой каталог fpspreadsheet в опциях вашего компилятора, если используется другая IDE.
Пример Excel 5
{
excel5demo.dpr
Demonstrates how to write an Excel 5.x file using the fpspreadsheet library
You can change the output format by changing the OUTPUT_FORMAT constant
AUTHORS: Felipe Monteiro de Carvalho
}
program excel5demo;
{$mode delphi}{$H+}
uses
Classes, SysUtils, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
const OUTPUT_FORMAT = sfExcel5;
var
MyWorkbook: TsWorkbook;
MyWorksheet: TsWorksheet;
MyFormula: TsRPNFormula;
MyDir: string;
begin
// Initialization
MyDir := ExtractFilePath(ParamStr(0));
// Create the spreadsheet
MyWorkbook := TsWorkbook.Create;
MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
// Write some number cells
MyWorksheet.WriteNumber(0, 0, 1.0);
MyWorksheet.WriteNumber(0, 1, 2.0);
MyWorksheet.WriteNumber(0, 2, 3.0);
MyWorksheet.WriteNumber(0, 3, 4.0);
// Write the formula E1 = A1 + B1
// or, in RPN: A1, B1, +
SetLength(MyFormula, 3);
MyFormula[0].ElementKind:=fekCell; {A1}
MyFormula[0].Col := 0;
MyFormula[0].Row := 0;
MyFormula[1].ElementKind:=fekCell; {B1}
MyFormula[1].Col := 1;
MyFormula[1].Row := 0;
MyFormula[2].ElementKind:=fekAdd;; {+}
MyWorksheet.WriteRPNFormula(0, 4, MyFormula);
// Creates a new worksheet
MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');
// Write some string cells
MyWorksheet.WriteUTF8Text(0, 0, 'First');
MyWorksheet.WriteUTF8Text(0, 1, 'Second');
MyWorksheet.WriteUTF8Text(0, 2, 'Third');
MyWorksheet.WriteUTF8Text(0, 3, 'Fourth');
// Save the spreadsheet to a file
MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
MyWorkbook.Free;
end.
Перебор всех Листов (Worksheets)
var
MyWorkbook: TsWorkbook;
MyWorksheet: TsWorksheet;
i: Integer;
begin
// Here load MyWorkbook from a file or build it
for i := 0 to MyWorkbook.GetWorksheetCount() - 1 do
begin
MyWorksheet := MyWorkbook.GetWorksheetByIndex(i);
// Do something with MyWorksheet
end;
Преобразование базы данных в электронную таблицу
Обратите внимание, что это только мета-код, не тестировал.
program db5xls;
{$mode delphi}{$H+}
uses
Classes, SysUtils,
// add database units
fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
const OUTPUT_FORMAT = sfExcel5;
var
MyWorkbook: TsWorkbook;
MyWorksheet: TsWorksheet;
MyDatabase: TSdfDataset;
MyDir: string;
i, j: Integer;
begin
// Initialization
MyDir := ExtractFilePath(ParamStr(0));
// Open the database
MyDatabase := TSdfDataset.Create;
MyDatabase.Filename := 'test.dat';
// Add table description here
MyDatabase.Active := True;
// Create the spreadsheet
MyWorkbook := TsWorkbook.Create;
MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
// Write the field names
for i := 0 to MyDatabase.Fields.Count - 1 do
MyWorksheet.WriteUTF8Text(0, i, MyDatabase.Field[i].FieldName);
// Write all cells to the worksheet
MyDatabase.First;
j := 0;
while not MyDatabase.EOF do
begin
for i := 0 to MyDatabase.Fields.Count - 1 do
MyWorksheet.WriteUTF8Text(j + 1, i, MyDatabase.Field[i].AsString);
MyDatabase.Next;
Inc(j);
end;
// Close the database
MyDatabase.Active := False;
MyDatabase.Free;
// Save the spreadsheet to a file
MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
MyWorkbook.Free;
end.
Скачать
Subversion
FPSpreadsheet Вы можете скачать с SourceForge, также можно воспользоваться программным обеспечение subversion, или следующей командой:
svn co https://lazarus-ccr.svn.sourceforge.net/svnroot/lazarus-ccr/components/fpspreadsheet fpspreadsheet
Текущий состояние
Текущее состояние поддерживаемых форматов:
Формат | Поддержка нескольких листов | Поддержка Unicode | Чтение | Запись | Текст | Число | Строковая формула | RPN формула |
---|---|---|---|---|---|---|---|---|
Excel 2.x | Нет | Нет** | Работает | Работает | Работает | Работает | Не реализовано | Работает |
Excel 3.0 | Нет | Нет** | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано |
Excel 4.0 | Нет | Нет** | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано |
Excel 5.0 (Excel 5.0 and 95) | Да | Нет** | Работает* | Работает | Работает | Работает | Не реализовано | Работает |
Excel 8.0 (Excel 97, 2000, XP and 2003) | Да | Да | Работает* | Работает | Работает | Работает | Не реализовано | Не реализовано |
Microsoft OOXML | Да | Да | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано | Не реализовано |
OpenDocument | Да | Да | Работает | Работает | Работает | Работает | Не реализовано | Не реализовано |
(*) Некоторые ячейки могут быть возвращены пустыми из-за отсутствия реализации некоторых числовых и строковых форматов. (**) В форматах, не поддерживающих Unicode, данные сохраняются в виде ISO 8859-1. Обратите внимание, что при чтении/записи FPSpreadsheet предлагает использовать UTF-8, но эти данные будут преобразованы в ISO при чтении или записи на диск. Будьте осторожны - символы, которые не укладываются в ISO Latin 1, будут потеряны в этих операциях.
Журнал изменений
Январь 2009
- Реализована это кросс-платформенная поддержка для OLE файлов. Теперь файлы Excel 5.0 могут быть созданы в любой операционной системе.
- Добавлена поддержка чтения файлов Excel 2.1
Февраль 2008
- Initial commit to lazarus-ccr with write support for Excel 2.1, Excel 5.0 (Windows only) and experimental OOXML and OpenDocument
Лицензия
LGPL with static linking exception. This is the same license as is used in the Lazarus Component Library.
См. также
Поддержка и багрепорты
Рекомендованное место для обсуждения FPSpreadsheet и получения ответов на ваши вопросы это ветка форума Lazarus Forum.
Багрепорты должны быть отправленны в Lazarus/Free Pascal Bug Tracker; пожалуйста уточните что это "Lazarus-CCR" проект.
Внешние ссылки
- Microsoft OLE Document Format - http://sc.openoffice.org/compdocfileformat.pdf
- Excel file format description - http://sc.openoffice.org/excelfileformat.pdf
--4yma 01:53, 13 April 2010 (CEST)