Difference between revisions of "FPSpreadsheet/ru"

From Free Pascal wiki
Jump to navigationJump to search
Line 1,585: Line 1,585:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
==== Passwords ====
+
==== Пароли ====
Workbook and worksheet protection can be secured by passwords. Note that these passwords do not encrypt the file (except for workbook protection in Excel 2007). In the Office applications the user must enter this password to turn off protection or to change protection items. The encrypted password is stored in the <tt>CryptoInfo</tt> record of the wordbook and the worksheets, respectively:
+
Защита рабочей книги и рабочего листа может быть обезопасена паролем. Обратите внимание, что эти пароли не шифруют файл (кроме защиты рабочей книги в Excel 2007). В приложениях Office пользователь должен ввести этот пароль, чтобы отключить защиту или изменить элементы защиты. Зашифрованный пароль хранится в записи <tt>CryptoInfo</tt> словаря и рабочих листов соответственно:
 
<syntaxhighlight>type
 
<syntaxhighlight>type
 
   TsCryptoInfo = record
 
   TsCryptoInfo = record
Line 1,596: Line 1,596:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
{{Warning|FPSpreadsheet does not perform any hashing calculations, the <tt>CryptoInfo</tt> record is just passed through from reading to writing. This causes problems when different file formats are involved in reading and writing. It is attempted to detect '''incompatible combinations'''. In these cases, the '''password protection is removed''', and an error is logged by the workbook.}}
+
{{Warning|FPSpreadsheet не выполняет никаких расчетов хэширования, запись <tt>CryptoInfo</tt> просто передается от чтения до записи. Это вызывает проблемы, если при чтении и записи используются разные форматы файлов. Компонент пытается обнаружить '''несовместимые комбинации'''. В этих случаях '''защита паролем удаляется''', и в словаре записывается ошибка.
 +
}}
  
 
=== Loading and saving ===
 
=== Loading and saving ===

Revision as of 17:31, 17 September 2018

Deutsch (de) English (en) español (es) français (fr) polski (pl) русский (ru)


ENG: AT THE MOMENT THIS PAGE IS UNDER TRANSLATION.
RUS: В НАСТОЯЩИЙ МОМЕНТ СТРАНИЦА НАХОДИТСЯ В ПРОЦЕССЕ ПЕРЕВОДА.



FpSpreadsheet библиотека предоставляет удобный способ создания и чтения табличных документов в различных форматах. Библиотека написана на очень гибкой основе, и поэтому её можно легко расширять поддержкой любого количества форматов.

Скриншот демонстрационной программы spready, предоставляемой с fpspreadsheet, показывающим файл XLS:


fpsgrid.png


Документация

Эта страница вики включает последнюю версию FPSpreadsheet, доступную через subversion. Пожалуйста, см. раздел Стабильные версии для [получения] документации по стабильной версии, которую вы можете скачать.

Документация по API

Ссылка на API

Файл справки в формате CHM можно найти в папке установки FPSpreadsheet docs. Если вы еще не установили пакет, тогда следуйте сюда https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/docs

Второй файл 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, если ячейка еще не существует, а вторая создает пустую ячейки в этом случае.

if MyWorksheet.FindCell('A1') = nil then
  WriteLn('Ячейка A1 не существует.');


Основной тип TCell record

Это объявление типа данных ячейки:

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: комментарий записи находится в comments рабочего листа.
  • cfHyperlink: ячейка содержит гиперссылку, хранящуюся в Hyperlinks рабочего листа.
  • cfMerged: ячейка принадлежит блоку объединенные ячейки и распространяется через несколько ячеек.
  • cfHasFormula: ячейка связана с формулой, которая хранится в Formulas рабочего листа.
  • cf3dFormula: Формула, связанная с ячейкой, содержит элементы, ссылающиеся на другие листы той же книги.
Light bulb  Примечание: После вычисления формулы или после прочтения файла 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;
    ...
Индексирование формата ячейки

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: выравнивание по левому краю, по горизонтали или по правому краю
  • VertAlignment: верхний, нижний или вертикально центрированный текст
  • Border: набор флагов, указывающий, что - если установлено - линия границы нарисована в левом, верхнем, правом или нижнем краях ячеек. Линии рисуются в соответствии с 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 в основном применяются к пустым ячейкам, но если добавлена новая ячейка, она автоматически получит формат строки или столбца. (Если обе строки и столбец имеют разные форматы, тогда будет использоваться формат строки).

Формулы

Два вида формул поддерживаются FPSpreadsheet:

  • Строковые формулы: написаны в строковом [формате], как в офисных приложениях, например "=ROUND(A1+B1,0)". Используются внутри файлов Open/LibreOffice и Excel .xlsx.
  • Формулы RPN [(польской нотации)] используются внутри [приложения] двоичными файлами Excel .xls. Они написаны в Reverse Polish Notation (RPN)[«Обратной польской нотации»], например: A1, B1, Add, 0, ROUND. Если электронная таблица, содержащая формулы, должна быть сохранена в двоичном формате Excel, необходимые формулы RPN генерируются автоматически.

FPSpreadsheet может [производить] преобразования между формулами строк и RPN. Формулы в обоих типах могут быть рассчитаны.

В более старых версиях FPSpreadsheet формулы сохранялись непосредственно в записи ячейки. Это было сделано, чтобы получить анализируемые формулы для более быстрого вычисления. Формулы хранятся в дереве Formulas рабочего листа. Запись формулы содержит индекс строки и столбца ячейки, к которой относится формула, строковое представление формулы, а также дерево парсера для быстрой оценки.

FPSpreadsheet поддерживает большинство формул, предоставляемых обычными приложениями электронных таблиц. Однако при чтении файла, созданного этими приложениями, всегда существует вероятность того, что [в нем] содержится не поддерживаемая формула. Чтобы избежать сбоев fpspreadsheet, чтение формул по умолчанию отключено; ячейка отображает только результат формулы, написанной приложением Office. Чтобы активировать чтение формул, перед открытием файла добавьте элемент boReadformulas в [свойстве] Options книги. Если в этом случае возникает ошибка, читатель [(парсер)] обычно обходит исключение, записывает сообщение об исключении в журнал ошибок книги и продолжает чтение. Если вы хотите, чтобы чтение останавливалось, вы должны добавить boAbortReadingOnFormulaError в Options книги.

Формулы могут ссылаться на данные в других листах той же книги ("3d формулы"), применяя синтаксис Excel (см. ниже). Внешние ссылки на электронные таблицы в других файлах не поддерживаются.

Вычисление формул обычно не требуется, когда файл написан [при помощи] FPSpreadsheet для открытия в приложении Office, потому что он автоматически вычисляет результаты формулы. Однако, если один и тот же файл открывается в приложении, основанном на FPSpreadsheet, вычисленные ячейки будут пустыми, потому что формулы по умолчанию автоматически не вычисляются. Чтобы активировать вычисление формул перед написанием электронной таблицы в файл, вы должны добавить опцию boCalcBeforeSaving в [свойство] Options книги.

Если FPSpreadshet используется в интерактивном приложении (таком, как демка «spready», расположенной в папке examples в [корневом каталоге] установки FPSpreadsheet), желательно автоматически вычислять формулы, когда пользовательские строки или значения ячеек изменяются пользователем. Этого можно достичь с помощью опции boAutoCalc в [свойстве] Options книги.

Таким образом, наиболее общая установка в отношении формул [такова]

  MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];

Вычисление формул можно запускать вручную, вызывая метод CalcFormulas рабочего листа или рабочей книги. Последнее абсолютно необходимо, когда книга содержит 3d формулы, где результат одной ячейки может влиять на ячейки в других листах. Если есть формулы [, используемые] только внутри рабочего листа, то достаточно [использование свойства] CalcFormulas рабочего листа.

Строковые формулы

Строковые формулы записываются так же, как и в приложениях Office. Метод рабочей таблицы для создания строковой формулы WriteFormula:

var
  MyWorksheet: TsWorksheet;
//...
  MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
  // По умолчанию используйте десятичную точку и запятую как разделитель списков!

Несколько замечаний по syntax:

  • Ведущий символ =, который идентифицирует формулы в приложениях Office, здесь не является абсолютно необходимым и может быть удален. Формула хранится в записи ячейки без нее.
  • Случай с именем формулы игнорируется.
  • Пробелы могут быть добавлены для лучшей читаемости, но при сохранении они будут потеряны.
  • Строки должны быть заключены в двойные кавычки.
  • Угловые точки диапазона ячеек должны быть разделены двоеточием (":"), напр. A1:C3. Неопределенные диапазоны будут переупорядочены при анализе формулы, т.е. C3:A1 станет A1:C3.
  • Ссылки на другие рабочие листы должны следовать синтаксису Excel, который разделяет имя листа и адрес ячейки на "!". Например, одну ячейку можно связать с помощью Sheet1!A1. Ряд листов должен быть помещен перед ячейкой или диапазоном ячейки, напр. Sheet1:Sheet2!A1:C3. Обратите внимание, что синтаксис Open/LibreOffice с разделителем и ссылкой на угловые точки 3d-блока (т.е. Sheet1.A1:Sheet2.C3) не поддерживается. Также обратите внимание на то, что рабочий(-ие) лист(-ы), на которые ссылается [добавляемая] формула, должна(-ы) существовать на момент добавления формулы; в противном случае ссылка будет заменена кодом ошибки #REF!, и формула не будет использоваться, даже если недостающий лист будет добавлен позже.
  • Обычно числа с плавающей запятой должны вводиться с точкой в качестве десятичного разделителя, а запятая должна использоваться для разделения аргументов функции.
  • Установка параметра ALocalized метода рабочего листа WriteFormula в TRUE не является обязательной, однако позволяет использовать локализованные десятичные [разделители] и разделители списков, взятые из FormatSettings рабочего листа - см. демо spready.
var
  MyWorksheet: TsWorksheet;
//...
  MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
  // Из-за "true" парсер формулы воспринимает запятую как десятичный разделитель и
  // точку с запятой в качестве разделителя списков, если формат FormatSettings книги yстановлен как здесь.

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.

Используйте методы ReadFormula или ReadFormulaAsString рабочего листа для получения строковой формулы, присвоенной ячейке. Указатель на ячейку должен быть определен как параметр. Последняя функция принимает дополнительный (логический) параметр ALocalized для использования десятичных [разделителей] и разделителей списков свойства FormatSettings рабочей книги для создания строки формулы.

var
  MyWorksheet: TsWorksheet;
  cell: PCell;
//...
  cell := MyWorksheet.FindCell(0, 1);
  WriteLn('Формула во внутреннем формате - ', MyworkSheet.ReadFormula(cell));
  WriteLn('Локализованная формула - ', MyWorksheet.ReadFormulaAsString(cell, true));
//-------------------------------------------------------------------
// Предыдущий пример приведет к следующему результату
Формула во внутреннем формате - ROUND(A1+B2+1.215,0)
Локализованная формула - ROUND(A1+B2+1,215;0)

Формулы RPN (Обратной польской нотации)

На уровне приложения в основном используются строковые формулы, а формулы RPN имеют мало практического значения. Поэтому документация формул RPN была удалена из этой основной вики FPSpreadsheet и может быть найдена в статье "Формулы RPN в FPSpreadsheet".

Общие формулы и формулы массива

  • Общие формулы поддерживаются только для чтения (из файлов Excel).
  • Формулы массива в настоящее время не поддерживаются.

Список встроенных формул

FPSpreadsheet поддерживает более 80 встроенных формул. Чтобы не взорвать эту страницу вики, большая часть документации по этим формулам была перенесена в отдельный документ "Список формул".

Чтобы узнать больше о доступных функциях, посмотрите файл testcases_calcrpnformula.inc в папке tests [в корневом каталоге] установки FPSpreadsheet, где каждая функция включена с хотя бы одним примером.

Расширение FPSpreadsheet по пользовательским формулам

Хотя встроенные формулы охватывают большинство приложений, может потребоваться доступ к формуле, доступной в приложении Office, но не в таблице FPSpreadsheet. По этой причине библиотека поддерживает механизм регистрации, который позволяет добавлять пользовательские функции к электронным таблицам. Это можно сделать, вызвав процедуру RegisterFunction из модуля 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 указывает имя, под которым функция будет вызываться в электронной таблице. Он должен соответствовать имени формулы в приложении Office.
  • AResultType - это символ, который идентифицирует тип данных результата функции:
    • 'F' - число с плавающей запятой
    • 'I' - целое число
    • 'D' - дата / время
    • 'B' - boolean
    • 'S' - строка
  • AParamTypes - это строка, в которой каждый символ идентифицирует тип данных соответствующего аргумента. В дополнение к приведенному выше списку могут использоваться следующие символы:
    • '?' - любой тип
    • '+' - должен быть последний символ. Это означает, что предыдущий символ повторяется бесконечно. Это позволяет подсчитать произвольный аргумент. Обратите внимание, однако, что Excel поддерживает только до 30 аргументов.
    • нижний регистр 'f', 'i', 'd', 'b', 's' указывают необязательные параметры описанного выше типа. Конечно, символы верхнего регистра не могут следовать за строчными символами.
  • AExcelCode - это идентификатор функции в файлах xls. См. «Документация OpenOffice формата файлов Microsoft Excel», раздел 3.11, для списка.
  • ACallback определяет, какую функцию вызывает FPSpreadsheet для вычисления формулы. Это может быть процедура или обработчик событий.
type
  TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
  TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;

TsExpressionResult - это вариантная запись, содержащая данные результата или аргумента нескольких типов:

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;

В качестве примера мы приводим здесь код для формулы CONCATENATE(), которая объединяет две или более строки:

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" упрощает получение строки из TsExpressionResult, 
    // поскольку строка может содержаться в ResString и в полях ResCell.
    // Такая функция существует для каждого базового типа данных.
  end;
  Result := StringResult(s);
  //"StringResult" хранит строку s в поле ResString объекта 
  // TsExpressionResult и устанавливает для ResultType значение rtString.
  // Такая функция существует для каждого базового типа данных.
end;

В папке examples/other в [корневом каталоге] установки FPSpreadsheet имеется разработанный пример (demo_formula_func.pas). В этой демке добавлены четыре финансовые функции (FV(), PV(), PMT(), RATE()) в FPSpreadsheet.

Неподдерживаемые формулы

Иногда требуется создавать файлы для приложений Office с формулами, не поддерживаемыми [компонентом] FPSpreadsheet. Это возможно в некоторой степени, когда опция boIgnoreFormulas рабочей книги активна. Тогда любая произвольная формула может быть записана в ячейку, а формула не проверяется и не оценивается. [Рабочая] книга может быть записана в файл .ods или .xlsx. Старый формат файла xls не может быть использован, потому что формулу необходимо проанализировать для создания необходимой формулы RPN.

В папке examples/other вы можете найти образец проекта demo_ignore_formula, который создает файл ods со ссылками на другой файл данных. Внешние ссылки обычно не поддерживаются FPSpreadsheet, поэтому должны быть использованы обходные методы [их] игнорирования. Обратите внимание, что этот пример не работает с xlsx, потому что Excel записывает информацию о внешних ссылках для разделения XML-файлов в контейнере xlsx.

Форматирование ячеек

Форматы числа и даты/времени

Значения чисел и даты/времени могут отображаться в различных форматах. В FPSpreadsheet это может быть достигнуто двумя способами:

  • использование встроенных форматов чисел путем указания значения для NumberFormat ячейки
  • используя пользовательскую строку format.

Форматы чисел могут быть указаны с помощью этих методов [рабочего] листа:

type
  TsWorksheet = class
  public
    // Установка только числовых форматов
    function WriteNumberFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''): PCell; overload;
    procedure WriteNumberFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''); overload;  

    function WriteNumberFormat(ARow, ACol: Cardinal; ANumFormat: TsNumberFormat;
      ADecimals: Integer; ACurrencySymbol: String = ''; APosCurrFormat: Integer = -1;
      ANegCurrFormat: Integer = -1): PCell; overload;
    procedure WriteNumberFormat(ACell: PCell; ANumFormat: TsNumberFormat;
      ADecimals: Integer; ACurrencySymbol: String = '';
      APosCurrFormat: Integer = -1; ANegCurrFormat: Integer = -1); overload;

    function WriteFractionFormat(ARow, ACol: Cardinal; AMixedFraction: Boolean;
      ANumeratorDigits, ADenominatorDigits: Integer): PCell; overload;
    procedure WriteFractionFormat(ACell: PCell; AMixedFraction: Boolean;
      ANumeratorDigits, ADenominatorDigits: Integer); overload;

    // Установка только формата дата/время  
    function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''): PCell; overload;
    procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''); overload;


    // Установка значений ячеек и числовых форматов за один вызов

    // числовые значения
    function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
      AFormat: TsNumberFormat = nfGeneral; ADecimals: Byte = 2;
      ACurrencySymbol: String = ''): PCell; overload;
    procedure WriteNumber(ACell: PCell; ANumber: Double; AFormat: TsNumberFormat = nfGeneral;
      ADecimals: Byte = 2; ACurrencySymbol: String = ''); overload;
    function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
      AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
    procedure WriteNumber(ACell: PCell; ANumber: Double;
      AFormat: TsNumberFormat; AFormatString: String); overload;   

    // значения дата/время
    function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
      AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''): PCell; overload;
    procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
      AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''); overload;
    function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
      AFormatStr: String): PCell; overload;
    procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
      AFormatStr: String); overload;  

    // значения денежной валюты
    function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
      AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
      ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
      ANegCurrFormat: Integer = -1): PCell; overload;
    procedure WriteCurrency(ACell: PCell; AValue: Double;
      AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
      ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
      ANegCurrFormat: Integer = -1); overload;
    function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
      AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
    procedure WriteCurrency(ACell: PCell; AValue: Double;
      AFormat: TsNumberFormat; AFormatString: String); overload;     
  ...
Встроенные форматы чисел

Встроенные форматы определяются перечислением TsNumberFormat. Несмотря на свое название, элементы охватывают как числа, так и дату/время:

type
  TsNumberFormat = (
    // универсальный для всех номеров
    nfGeneral,
    // числа
    nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
    // денежная валюта
    nfCurrency, nfCurrencyRed, 
    // дата и время
    nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
    nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
    // другие (использующие строку формата)
    nfCustom);
  • nfGeneral соответствует форматированию по умолчанию, показывающему как можно больше десятичных знаков [после запятой] (число 3.141592654 не изменится.)
  • nfFixed ограничивает десятичные знаки. Число знаков после запятой должно быть указано при вызове WriteNumber. Пример: с 2 десятичными знаками число 3.141592654 становится 3.14.
  • nfFixedTh: аналогично nfFixed , но добавляет разделитель тысячных [разрядов], когда число отображается как строка: число 3.141592654 останется таким же, как и в предыдущем примере, потому что оно слишком мало, чтобы показать разделитель тысячных [разрядов]. Но число 314159.2654 станет 314,159.26, для 2 десятичных знаков.
  • nfExp выбирает экспоненциальное представление, т.е. разделяет экспоненту. Параметр ADecimals в WriteNumber определяет, сколько десятичных знаков используется. (Число 3.141592654 становится 3.14E+00 в случае двух десятичных знаков).
  • nfPercentage отображает число в процентах. Это означает, что значение умножается на 100, а знак процента добавляется. Опять же, укажите в ADecimals</ tt>, сколько десятичных знаков должно быть показано. (Число 3.141592654 отображается как 314.92%, в случае 2 десятичных знаков).
  • nfFraction представляет число в виде доли. Подробности (смешанная фракция?, максимальное количество цифр для числителя или знаменателя) для могут быть указаны в методе рабочего листа WriteFractionFormat.
  • nfCurrency отображает номер вместе с символом валюты, и существуют специальные правила, как отображать отрицательные значения (в скобках или знак минус до или после номера). Формат форматирования рабочей книги используется для определения знака валюты и способа отображения номеров (FormatSettings.CurrencyString для символа валюты, FormatSettings.CurrencyFormat для положительных, FormatSettings.NegCurrFormat для отрицательных значений). Эти настройки можно переопределить, указав их непосредственно в вызове WriteCurrency.
  • nfCurrendyRed аналогично nfCurrency, кроме того, отрицательные значения отображаются красным цветом.
  • nfShortDateTime представляет DateTimeValue ячейки в 'коротком формате даты/времени", т.е. дни + двухзначный [номер] месяца + двухзначный год + часы + минуты, без секунд. Порядок частей даты берется из FormatSettings рабочей книги. Это относится также к другим форматам даты и времени.
  • nfShortDate создает строку даты, показывающую день + двухзначный [номер] месяца + двузначный год
  • nfShortTime создает строку времени, показывающую часы + минуты.
  • nfLongTime, аналогично [nfShortTime], но включает и секунды
  • nfShortTimeAM, аналогично nfShortTime, но использует формат времени AM/PM, т.е. часы идут до 12, а AM или PM добавляются для указания [половины дня] утром или вечером/пополудни.
  • nfLongTimeAM, аналогично nfShortTimeAM, но включает секунды
  • nfTimeInterval, аналогично nfLongTime, но может быть больше 24 часов. Интервал может также выражаться в минутах или секундах, если используются строки формата [n]:ss или [s], соответственно.
  • nfCustom позволяет указать выделенную строку форматирования.

Как уже отмечалось, в рабочей книге есть свойство FormatSettings, которое предоставляет дополнительную информацию для управления окончательным форматированием. Это, по существу, копия DefaultFormatSettings, объявленная в модуле sysutils (элементы LongDateFormat и ShortDateFormat слегка изменены для лучшего соответствия настройки по умолчанию в основных приложениях электронных таблиц). Основная цель FormatSettings - добавить простой способ локализации в числовые форматы.

Строки формата чисел

В дополнение к этим заранее определенным форматам более специализированное форматирование может быть достигнуто с использованием константы формата nfCustom вместе со специальной строкой формата. Строка формата построена в соответствии с синтаксисом Excel, который близок к синтаксису команд FormatFloat и FormatDateTime команды fpc (см. интерактивную справку для этих функций).

Вот основной список используемых символов:

Символ Определение Строка формата: число --> Выходная строка
General Отображает все десятичные знаки числа 'General':
1.2345678 --> '1.2345678'
0 Отображает не значащие нули, если число имеет меньше знаков, чем [количество] нулей в формате. Если [строка формата] используется для десятичных знаков, то число округляется до такого количества знаков после запятой, что и 0. '000': 1 --> '001'
'0.0': 1 --> '1.0'
'0.0': 1.2345678 --> '1.2'
* Как [и для] "0" выше, но не отображает не значащие нули. '0.*': 1 --> '1.'
'0.*': 1.2345678 --> '1.2'
? Как [и для] "0" выше, но не существенные нули заменяются пробелами. Хорошо для выравнивания десятичных точек и фракций

'??0': 1 --> ' 1'
'0.0??': 1 --> '1.0 '

. Десятичный разделитель; будет заменено значением, используемым в DecimalSeparator [параметра] FormatSettings рабочей книги '0.00': 8.9 --> '8.90'
, Разделитель тысячных [разрядов]; будет заменено значением, используемым в ThousandSeparator [параметра] FormatSettings рабочей книги. Если [сепаратор указан] в конце последовательности форматирования числа, [то] отображаемое значение делится на 1000. '#,##0.00': 1200 --> '1,200.00'
'0.00,': 1200 --> '1.20'
E+, e+ Отображает число в экспоненциальном формате. Цифры, используемые для показателя, определяются числом нулей, добавленным этим символом. Знак экспонента показан для положительных и отрицательных показателей. '0.00E+00': 1200 --> 1.20E+03
E-, e- Отображает число в экспоненциальном формате. Цифры, используемые для показателя, определяются числом нулей, добавленным этим символом. Знак экспонента показан только для отрицательных показателей. '0.00e-000': 1200 --> 1.20e003
% Отображает число как «процент», т.е. число умножается на 100 и добавляется знак %. '0.0%': 0.75 --> 75.0%
/ Этот символ имеет два значения: если ячейка представляет собой "число", то косая черта означает форматирование как дробь, числитель и знаменатель должны следовать зарезервированному месту. Если ячейка представляет "дату/время", то косая черта обозначает разделитель даты, который будет заменен на DateSeparator в [параметре] FormatSettings рабочего листа '#/#': 1.5 --> '3/2'
'# #/#': 1.5 --> '1 1/2'
'# #/16': 1.5 --> '1 8/16'
также: см. примеры даты/времени ниже
: Разделитель между часами, минутами и секундами значения даты/времени. Будет заменен [на] TimeSeparator [параметра] FormatSettings рабочей книги. смотри пример ниже
yyyy Зарезервированное место для года значения даты/времени. Год отображается как четырехзначное число. 'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
В этом примере DateSeparator является символом тире (-).
yy Зарезервированное место для года значения даты/времени. Год отображается как двузначное число. 'yy/mm/dd':
Jan 3, 2012 --> '12-01-03'
m Зарезервированное место для месяца значения даты/времени. Месяц отображается как число без дополнительных цифр.
Обратите внимание, что код m также можно интерпретировать как «минуты» значения времени (см. ниже).
'yyyy/m/dd':
Jan 3, 2012 --> '2012-1-03'
mm Зарезервированное место для месяца значения даты/времени. Месяц отображается как двузначное число, то есть начальный ноль добавляется с января по сентябрь.
Обратите внимание, что код mm также может быть интерпретирован как "минуты" (см. ниже).
'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
mmm Зарезервированное место для месяца значения даты/времени. Месяц отображается [в виде] аббревиатуры. 'yyyy/mmm/dd':
Jan 3, 2012 --> '2012-Jan-03'
mmmm Зарезервированное место для месяца значения даты/времени. Месяц отображается полным именем.
'yyyy/mmm/dd':
Jan 3, 2012 --> '2012-January-03'
d Зарезервированное место дня значения даты/времени, которое будет отображаться в виде числа. День отображается как простое число без добавления начального нуля. 'yyyy/mm/d':
Jan 3, 2012 --> '2012-01-3'
dd Зарезервированное место дня значения даты/времени, которое будет отображаться в виде числа. dd добавляет [лидирующий] ноль в однозначные числа. 'yyyy/mm/dd':
Jan 3, 2012 --> '2012-01-03'
ddd Зарезервированное место дня значения даты/времени. День отображается в виде аббревиатуры. 'dddd, yyyy/mm/ddd':
Jan 03, 2012 --> 'Tue 2012-01-03'
dddd Зарезервированное место дня значения даты/времени. День отображается полным именем. 'dddd, yyyy/mmmm/dd':
Jan 03, 2012 --> 'Tuesday 2012-01-03'
h Зарезервированное место часовой части значения даты/времени. Час отображается как простое число без добавления начального нуля. 'h:mm':
0.25 --> '6:00'
hh Зарезервированное место часовой части значения даты/времени. Час отображается с начальным нулем, если час меньше 10. 'hh:mm':
0.25 --> '06:00'
[hh], или [h] Отображает прошедшее время, так что часовая часть может стать больше 23 '[h]:mm':
1.25 --> '30:00'
m Зарезервированное место минутной части значения даты/времени. Минуты показаны как простое число без добавления начального нуля. Заметим, что если коды m окружены символами даты (y, d), то они интерпретируются как "месяц". 'h:m':
0.25 --> '6:0'
mm Зарезервированное место минутной части значения даты/времени. Минуты с одной цифрой отображаются с начальным нулем. Заметим, что если код mm окружен символами даты (y, d), то он интерпретируется как "месяц". 'h:mm':
0.25 --> '6:00'
[mm], или [m] Отображает прошедшее время так, что минутная часть может стать больше 59 '[mm]:ss':
1.25 --> '1800:00'
s Зарезервированное место секундной части значения даты/времени. Секунды отображаются как простое число без добавления начального нуля. 'hh:mm:s':
0.25 --> '06:00:0'
ss Зарезервированное место секундной части значения даты/времени. Секунды с одной цифрой отображаются с начальным нулем. 'hh:mm:ss':
0.25 --> '06:00:00'
[ss], или [s] Отображает прошедшее время так, что секундная часть может стать больше, чем 59 '[ss]':
1.25 --> '108000'
AM/PM, am/pm, A/P, или a/p Отображает время в 12-часовом формате. 'hh:mm:ss AM/PM':
0.25 --> '6:00:00 AM'
" Текст, заключенный в кавычки, вставляется в форматированные строки буквально. 'yyyy"/"mm"/"dd':
Jan 3, 2012 --> '2012/01/03' (т. е. / не заменяется свойством DateSeparator рабочей книги).
\ Следующий символ строки формата отображается в строке результата буквально. Сам \ не отображается.

'yyyy\/mm\/dd':
Jan 3, 2012 --> '2012/01/03'

; Строка формата может содержать до трех разделов, разделенных точкой с запятой. Первый раздел используется для положительных чисел, второй - для отрицательных чисел, а третий - для нулевых чисел. Если третий раздел отсутствует, то нулевое значение форматируется, как указано в первом разделе. Если второй раздел также отсутствует, то все значения форматируются в соответствии с первым разделом. '"#,##0"$"';-#,##0"$";"-"':
1200 --> '1,200$'
-1200 --> '1,200$'
0 --> '-'
(, и ) Иногда используется для значений валюты, указывающих отрицательные числа, вместо знака "минус" '#,##0"$";(#,##0)"$"':
-1200 --> '(1200)$'
[red] Отформатированная строка отображается в указанном цвете. Вместо [red] вы можете использовать соответственно [black], [white], [green], [blue], [magenta], [yellow], или [cyan]. Часто используется для выделения отрицательных значений валюты. '"$" #,##0.00;[red]("$" #,##0.00)':
-1200 --> '($ 1200.00)'

Цвета

FPSpreadsheet поддерживает цвета для текста, фона ячейки и границ ячеек. Основные цвета EGA объявлены в модуле fpstypes как константы:

Predefined colors
type
  TsColor = DWORD;

const
  scBlack       = $00000000;
  scWhite       = $00FFFFFF;
  scRed         = $000000FF;
  scGreen       = $0000FF00;
  scBlue        = $00FF0000;
  scYellow      = $0000FFFF;
  scMagenta     = $00FF00FF;
  scCyan        = $00FFFF00;
  scDarkRed     = $00000080;
  scDarkGreen   = $00008000;
  scDarkBlue    = $00800000;
  scOlive       = $00008080;
  scPurple      = $00800080;
  scTeal        = $00808000;
  scSilver      = $00C0C0C0;
  scGray        = $00808080;
  scGrey        = scGray; // переопределить, чтобы разрешить различное правописание

  // Идентификатор неопределенного цвета 
  scNotDefined  = $40000000;

  // Идентификатор прозрачного цвета 
  scTransparent = $20000000;


[Свойство] TsColor представляет значение rgb [значение] цвета, а для красных, зеленых и синих компонентов используется один байт. Результирующее число представлено обратным порядком байт (Little Endian Notation), т. е. сначала в памяти появляется значение красного [цвета]: $00BBGGRR. (Это напрямую совместимо с значениями цвета, как [оно] определено в модуле graphics.)

Старший байт обычно равен нулю, но используется внутри для определения специальных значений цвета, например, для неопределенных или прозрачных цветов.

Light bulb  Примечание: В более старых версиях цвета библиотеки были определены как индексы в цветовой палитре. ЭТО НЕ БОЛЬШЕ РАБОТАЕТ.

Модуль fpsutils содержит некоторые полезные функции для модификации цветов:

  • function GetColorName(AColor: TsColor): String;
    возвращает имя цветов, определенных выше, или строку, показывающую компоненты rgb для других цветов.
  • function HighContrastColor(AColor: TsColor): TsColor;
    возвращает scBlack для «яркого», scWhite для "темного" входного цвета.
  • function TintedColor(AColor: TsColor; tint: Double): TsColor;
    осветляет или затемняет цвет, применяя коэффициент tint = -1 .. + 1, где -1 означает "100% затемнение", +1 означает "100% осветление", а 0 означает "без изменений". Цветовой оттенок сохраняется.

Фон ячейки

Фон ячейки может быть заполнен предопределенными шаблонами, которые идентифицируются записью TsFillPattern::

type
  TsFillPattern = record
    Style: TsFillStyle;   // заполняет шаблон стиля, как определено ниже
    FgColor: TsColor;     // цвет переднего плана заливки шаблона
    BgColor: TsColor;     // цвет фона заливки шаблона
  end;

  TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
    fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
    fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
    fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
  • Используйте метод WriteBackground рабочего листа, чтобы назначить шаблон заполнения конкретной ячейке. Помимо адреса ячейки, этот метод требует тип заполнения шаблона (TsFillStyle) и цвета переднего плана и фона, значения [которых] указаны [свойством] TsColor.
  • Шаблон заполнения конкретной ячейки можно получить, вызвав метод ReadBackground рабочей книги.
  • Для достижения однородного цвета фона можно использовать упрощенный метод WriteBackgroundColor.
  • Ограничения:
    • Файлы OpenDocument поддерживают только однородные заливки. Цвет фона представляет собой смешивание переднего плана и фона rgb компонентов в соотношении, определяемом шаблоном заполнения.
    • Файлы BIFF2 поддерживают только 12,5% черно-белый затененный шаблон.
type
  TsWorksheet = class
  public
    function WriteBackground(ARow, ACol: Cardinal; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor): PCell; overload;
    procedure WriteBackground(ACell: PCell; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor); overload;

    function WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor): PCell; overload;
    procedure WriteBackgroundColor(ACell: PCell; AColor: TsColor); overload;

    function ReadBackground(ACell: PCell): TsFillPattern;
    function ReadBackgroundColor(ACell: PCell): TsColor; overload;
    // ...
  end;

var
  cell: PCell;
...
  // Пример 1: Присваивает шаблон тонких, горизонтальных, желтых полос на синем фоне, пустой ячейке A1 (строка 0, столбец 0)
  MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);

  // Пример 2: Равномерный серый цвет фона ячейки B1 (строка 0, столбец 1), содержащий номер 3.14
  cell := MyWorksheet.WriteNumber(0, 1, 3.14);
  MyWorksheet.WriteBackgroundColor(cell, clSilver);

Границы ячеек

Ячейки можно обвести, рисуя границы вдоль их краев или диагональных линий. Существует четыре границы плюс две диагонали, перечисленные в типе данных TsCellBorder:

type
  TsCellBorder = (cbNorth, cbWest, cbEast, cbSouth, dbDiagUp, dbDiagDown);
  TsCellBorders = set of TsCellBorder;

Чтобы показать линию края, добавьте соответствующую границу к ячейке Borders (тип TsCellBorders, см. выше). Таким образом, каждый край ячейки может обрабатываться отдельно. Для этого используйте метод рабочего листа WriteBorders. Этот пример добавляет верхнюю и нижнюю границы к краям A1 и B1:

  MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);  // A1: строка 0, столбец 0
  MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: строка 0, столбец 1

Линии обычно рисуются как тонкие, сплошные черные линии. Но можно изменить стиль линии и цвет каждой строки. Для этой цели ячейка предоставляет массив записей TsCellBorderStyle:

type
  TsLineStyle = (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair,
    lsMediumDash, lsDashDot, lsMediumDashDot, lsDashDotDot, lsMediumDashDotDot, lsSlantDashDot); 

  TsCellBorderStyle = record
    LineStyle: TsLineStyle;   
    Color: TsColor;
  end;

  TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;   

  TsWorksheet = class
  public
    function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; AStyle: TsCellBorderStyle): PCell; overload;
    procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; AStyle: TsCellBorderStyle); overload;

    function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor): PCell; overload;
    procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor); overload;

    function WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
    proceure WriteBorderColor(ACell: PCell; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;

    function WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
    procedure WriteBorderLineStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;

    function WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles): PCell; overload;      
    procedure WriteBorderStyles(ACell: PCell; const AStyles: TsCellBorderStyles); overload;

    function WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders): PCell; overload
    procedure WriteBorders(ACell: PCell; ABorders: TsCellBorders); overload

    function ReadCellBorders(ACell: PCell): TsCellBorders;
    function ReadCellBorderStyle(ACell: PCell; ABorder: TsCellBorder): TsCellBorderStyle;
    function ReadCellBorderStyles(ACell: PCell): TsCellBorderStyles;
    ...
  end;

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

  • WriteBorderStyle присваивает запись стиля границы ячейки одной границе ячейки. Есть две перегруженные версии этого метода: одна занимает целую запись TsCellBorderStyle, другая - отдельные элементы записи.
  • WriteBorderColor изменяет цвет данной границы, не затрагивая стиль линии этой границы.
  • WriteBorderLineStyle устанавливает стиль линии только границы, но оставляет цвет неизменным.
  • WriteBorderStyles устанавливает стиль границы всех границ данной ячейки сразу. Полезно для копирования стилей границ из одной ячейки в другие ячейки.

Этот пример добавляет тонкую черную границу к вершине [ячейки] и толстую синюю границу по нижней [кромке] ячеек A1 и B1:

var
  cellA1, cellB1: PCell;
...
  cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);    // ячейка A1: строка 0, столбец 0
  MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);  
  MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);

  cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]);    // ячейка B1: строка 0, столбец 1
  MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles);     // копируем все стили границ из ячейки A1 в B1
Light bulb  Примечание: Диагональные линии не поддерживаются [форматами] sfExcel2 и sfExcel5 - они просто опускаются. sfExcel8 и sfOOXML используют один и тот же стиль линии, и цвет для обеих диагоналей; стиль границы, [определенный для] диагональной [снизу - вверх] линии, считается действительным также для диагональной линии [сверху - ] вниз. Некоторые [парсеры] не поддерживают все стили линий, перечисленных в TsLineStyle. В этом случае используются соответствующие стили замены линий.

Шрифты

Текст ячейки может отображаться в разных шрифтах. С этой целью в книге содержится список элементов TsFont:

type
  TsFont = class
    FontName: String;
    Size: Single;   
    Style: TsFontStyles;
    Color: TsColor;
    Position: TsFontPosition;
  end;
  • FontName [имя шрифта] соответствует имени шрифта, используемому операционной системой. В Windows примером будет «Times New Roman».
  • FontSize [размер шрифта] указывается в «точках», т.е. в единицах 1/72 дюйма, которые обычно используются в приложениях Office.
  • FontStyle [стиль шрифта] представляет собой набор элементов [списка со значениями] fssBold, fssItalic, fssStrikeout и fssUnderline [(соответственно жирный, наклонный, зачеркнутый и подчеркнутый)], перечислимого типа TsFontStyle. «Нормальный» шрифт соответствует пустому набору.
  • Color [цвет] определяет цвет переднего плана текстовых символов, указанных в представлении rgb, как обсуждалось выше.
  • Position [положение] один из двух [параметров свойства] fpNormal - fpSuperscript или fpSubscript указывает, должен ли размер шрифта уменьшаться примерно на 1/3 со смещением символов вверх (верхний [регистр]) или вниз (нижний [регистр]).

Каждая ячейка снабжается индексом в списке шрифтов.

Чтобы назначить конкретный шрифт ячейке, используйте один из следующих методов TsSpreadsheet:

type
  TsSpreadsheet = class
  public
    function  WriteFont(ARow, ACol: Cardinal; const AFontName: String;
      AFontSize: Single; AFontStyle: TsFontStyles; AFontColor: TsColor): Integer; overload;
    procedure WriteFont(ARow, ACol: Cardinal; AFontIndex: Integer); overload;
    function WriteFontColor(ARow, ACol: Cardinal; AFontColor: TsColor): Integer;
    function WriteFontSize(ARow, ACol: Cardinal; ASize: Integer): Integer;
    function WriteFontStyle(ARow, ACol: Cardinal; AStyle: TsFontStyles): Integer; 
    // плюс: перегруженные версии, принимающие указатель на ячейку записи, а не индекс строки и столбца в качестве параметра
    // ...
  end;
  • WriteFont назначает шрифт ячейке. Если шрифт еще не существует в списке шрифтов, создается новая запись. Функция возвращает индекс шрифта в списке шрифтов. Кроме того, есть перегруженная версия, которая учитывает только индекс шрифта в качестве параметра.
  • WriteFontColor заменяет цвет шрифта, который в настоящее время назначается ячейке новым [цветом]. Опять же, новый элемент списка шрифтов создается, если шрифт с новым цветом еще не существует. Функция возвращает индекс шрифта в списке.
  • WriteFontSize заменяет размер используемого в настоящее время шрифта ячейки.
  • WriteFontStyle заменяет стиль (обычный, полужирный, курсив и т. д.) используемого в настоящее время шрифта ячейки.

Список шрифтов рабочей книги содержит по крайней мере один элемент, который является шрифтом по умолчанию для ячеек с немодифицированными шрифтами. По умолчанию это [шрифт] "Arial" [размером] 10pt. Используйте метод SetDefaultFont рабочей книги, чтобы назначить другой шрифт первому элементу списка.

Шрифт в указанном индексе списка шрифтов можно просмотреть, вызвав функцию рабочей книги GetFont. Количество доступных шрифтов возвращается [свойством] GetFontCount.

Вот пример, который уменьшает размер всех 10pt-овых шрифтов Arial до 9pt:

var
  i: Integer;
  font: TsFont;
begin
  for i := 0 to MyWorkbook.GetFontCount-1 do
  begin
    font := MyWorkbook.GetFont(i);
    if (font.FontName = 'Arial') and (font.Size = 10.0) then
      font.Size := 9.0;
  end;
end;

Форматирование Rich-text

В дополнение к использованию конкретного шрифта для каждой ячейки также можно указать конкретные атрибуты шрифта для отдельных символов или групп символов в каждом тексте ячейки. Следуя нотации Excel, мы называем эту функцию "форматированием Rich-text" (хотя это не имеет ничего общего с форматом файла *.rtf).

С этой целью модуль fpstypes объявляет тип TsRichTextParams, который представляет собой массив записей TsRichTextParam:

type
  TsRichTextParam = record
    FontIndex: Integer;
    FirstIndex: Integer;
  end;

  TsRichTextParams = array of TsRichTextParam;

FontIndex ссылается на индекс шрифта в FontList рабочей книги, который будет использоваться для форматирования символов, начинающихся с индекса FirstIndex. Нумерация индекса строковых символов FirstIndex начинается с 1.

Существует два способа добавления форматирования "rich-text" в ячейке:

  • Вставка соответствующих HTML-кодов в текст ячейки. Это можно сделать, используя метод WriteTextAsHTML рабочего листа. Чтобы добавить в ячейку A1 текст "Area (m2)", передайте следующую HTML-кодированную строку для этой функции
  MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
  • Альтернативно, стандартный метод текстовой записи WriteText может быть вызван с дополнительным параметром, определяющим параметры форматирования rich-text, которые будут использоваться напрямую:
var
  richTextParams: TsRichTextParams;
  fnt: TsFont;
begin
  SetLength(rtp, 2);
  fnt := MyWorksheet.ReadCellFont(0, 0);
  richTextParams[0].FirstIndex := 8; // Группа верхнего индекса начинается с «2», который является символом #8 текста ячейки.
  richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
  richTextParams[1].FirstIndex := 9; // Обычный шрифт снова начинается с символа # 9.
  richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
  MyWorksheet.WriteUTF8Text(0, 0, 'Area (m2)', richTextParams);
end;

Используйте метод рабочего листа DeleteRichTextParams, чтобы удалить форматирование rich-text из ранее отформатированной ячейки.

Light bulb  Примечание: Если ячейка должна иметь шрифт, отличный от шрифта по умолчанию рабочего листа, то этот шрифт должен быть записан в ячейку перед записью форматированного текста rich-text. В противном случае шрифт в неотформатированных регионах не будет обновлен.

Вращение текста

Обычно текст отображается в ячейках по горизонтали. Тем не менее, можно также поворачивать его на 90 градусов по часовой стрелке или против часовой стрелки. Кроме того, существует возможность установки горизонтальных символов вертикально друг над другом.

Если вам нужна эта функция, используйте метод рабочего листа WriteTextRotation и укажите направление текста элементом [свойства] перечислимого типа TsTextRotation:

type
  TsTextRotation = (trHorizontal, rt90DegreeClockwiseRotation,
    rt90DegreeCounterClockwiseRotation, rtStacked);

  TsWorksheet = class
  public
    function WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation): PCell; overload;
    procedure WriteTextRotation(ACell: PCell; ARotation: TsTextRotation); overload;

    function ReadTextRotation(ACell: PCell): TsTextRotation;
    // ...
  end;

  // пример для повернутого против часовой стрелки текста в ячейке A1:
  WriteTextRotation(0, 0, rt90DegreeCounterClockwizeRotation);
Warning-icon.png

Предупреждение: Более мелкие степени вращения, которые могут поддерживаться некоторыми форматами файлов электронной таблицы, игнорируются.

Выравнивание текста

По умолчанию текст ячейки выровнен по левому и нижнему краям ячейки, за исключением чисел, выровненных по правому краю. Это поведение можно изменить, используя методы рабочего листа WriteHorAlignment и WriteVertAlignment:

type
  TsHorAlignment = (haDefault, haLeft, haCenter, haRight);
  TsVertAlignment = (vaDefault, vaTop, vaCenter, vaBottom);    

  TsWorkbook = class
  public
    function WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment): PCell; overload;
    procedure WriteHorAlignment(ACell: PCell; AValue: TsHorAlignment); overload;
    function ReadHorAlignment(ACell: PCell): TsHorAlignment;

    function WriteVertAlignment(ARow, ACol: Cardinal; AValue: TsVertAlignment): PCell; overload; 
    procedure WriteVertAlignment(ACell: PCell; AValue: TsVertAlignment); overload;
    function ReadVertAlignment(ACell: PCell): TsVertAlignment;
    // ...
  end;

  // Пример: [так] центрируется текст в ячейке А1 как по горизонтали, так и по вертикали
  MyWorkbook.WriteHorAlignment(0, 0, haCenter);
  MyWorkbook.WriteVertAlignment(0, 0, vaCenter);

Перенос слов

Текст, длина которого превышает ширину ячейки, может быть перенесен на несколько строк вызовом метода WriteWordwrap электронной таблицы:

type
  TsWorksheet = class
  public
    function WriteWordwrap(ARow, ACol: Cardinal; AValue: Boolean): PCell; overload;
    procedure WriteWordwrap(ACell: PCell; AValue: Boolean); overload;
    function ReadWordwrap(ACell: PCell): Boolean;
    //...
  end;

  // Пример: активация переноса слов в ячейке А1
  MyWorksheet.WriteWordwrap(0, 0, true);

Объединенные ячейки

Подобно приложениям Office, FPSpreadsheet поддерживает также возможность слияния ячеек в одну большую ячейку, которая часто используется как общий заголовок выше аналогичных столбцов. Просто вызовите MergeCells и передайте параметр, чтобы указать диапазон ячеек, которые нужно объединить, либо строку диапазона Excel (например, A1:D5), либо первую и последнюю строку и столбец:

  MyWorksheet.MergeCells('A1:D5');
  // или: MyWorksheet.MergeCells(0, 0, 4, 3);  // первая строка, первый столбец, последняя строка, последний столбец

Содержимое и формат, отображаемые для объединенного диапазона, берутся из верхнего левого угла диапазона ячейки A1 в приведенном выше примере. Эта ячейка вызывается [свойством] MergeBase в библиотеке. За исключением этой угловой ячейки, [там] не должно быть никаких других ячеек в диапазоне. Если [они там окажутся], их содержимое и формат будут скрыты.

Чтобы разбить объединенный диапазон на отдельные ячейки, используйте команду Unmerge и передайте любую ячейку, находящуюся в объединенном диапазоне:

  MyWorksheet.UnmergeCells('B1');
  // или: MyWorksheet.UnmergeCells(0, 1);   // строка, столбец любой ячейки в пределах диапазона

Объединенные ячейки могут быть прочитаны/записаны во все форматы файлов, за исключением sfCSV, sfExcel2 и sfExcel5, которые не поддерживают эту функцию изначально.

Информация, с которой объединены ячейки, хранится во внутреннем списке. В отличие от предыдущих версий, больше невозможно получить доступ к MergeBase из ячейки напрямую. Используйте следующие функции для извлечения информации об объединенных ячейках:

var
  cell, base: PCell;
  r1,c1,r2,c2: Cardinal;
...
  cell := MyWorksheet.FindCell('B1');
  if MyWorksheet.IsMerged(cell) then
  begin
    WriteLn('Ячейка B1 объединена.');

    MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
    WriteLn('Объединенный диапазон - ' + GetCellRangeString(r1, c1, r2, c2));

    base := MyWorksheet.FindMergeBase(cell);
    WriteLn('Основная ячейка для слияния - ' + GetCellString(base^.Row, base^.Col));
  end;

Защита ячейки

Это описано в отдельном разделе ниже.

Дополнительная информация

Комментарии к ячейке

Комментарии могут быть прикреплены к любой ячейке путем вызова

  MyWorksheet.WriteComment(0, 0, 'Это комментарий для ячейки A1');

Они хранятся во внутреннем списке рабочего листа. Используйте соответствующие методы рабочего листа для доступа к комментариям:

  • Если вы хотите узнать, содержит ли конкретная ячейка комментарий, вызовите метод рабочего листа HasComment(cell).
  • Для получения комментария ячейки используйте метод ReadComment(cell) или его перегруженный вариант ReadComment(ARow, ACol).
  • Общее количество комментариев может быть получено из worksheet.Comments.Count.

Гиперссылки

Гиперссылки могут быть прикреплены к ячейкам, чтобы связать ячейки с другими документами или другими ячейками в одной книге. Общий синтаксис для создания гиперссылок

  procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');
  • Целью гиперссылки, переданной как параметр ATarget, должна быть полностью квалифицированной (Унифицированный идентификатор ресурса), состоящей из [определенных] фраз протокола [в определенной последовательности](например, http://, file:///, mailto: и т. д.), за которой следует определенная информация, такая как веб-адрес, имя файла или e-mail адрес и необязательный идентификатор закладки, разделенный символом '#'.
  • Необязательный параметр Tooltip [(подсказка)] расценивается Excel для отображения его в окне подсказки, если мышь находится над гиперссылкой.
Light bulb  Примечание: Гиперссылки могут быть добавлены в пустые ячейки или в ячейки с контентом. В последнем случае отображаемый контент не изменяется гиперссылкой; в первом случае ячейка преобразуется в метку ячейки, показывающую цель гиперссылки. Имейте в виду, что OpenOffice/LibreOffice принимает гиперссылки только с нетекстовыми ячейками.

Примеры:

  // Открытие веб-сайта www.google.com
  MyWorksheet.WriteText(0, 0, 'Открыть google');
  MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');

  // Открытие локального файла с абсолютным путем "C:\readme.txt" (предполагается, [что это] Windows)
  MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');

  // Открытие почтового клиента для отправки почты
  MyWorksheet.WriteText('Отправить письмо');
  MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');

  // Переход к определенной ячейке 
  MyWorksheet.WriteText(5, 0, 'Переход к ячейке A10 на листе2');
  MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');

  // Переход в ячейку A10 на текущем листе и отображение всплывающей подсказки
  MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Переход к ячейке A10');
Light bulb  Примечание: FPSpreadsheet не «следит» за ссылками, он предоставляет только механизм для получения доступа к связанным данным. Однако [компонент] TsWorksheetGrid из пакета laz_fpspreadsheet_visual запускает событие OnClickHyperlink, если ячейку с внешней гиперссылкой щелкают за доли секунды. В соответствующем обработчике событий вы можете, например, загрузить целевую электронную таблицу или открыть веб-браузер для отображения связанного веб-сайта. Если ссылка является внутренней ссылкой на другую ячейку в одной книге, таблица переходит к связанной ячейке.

Изображения

FPSpreadsheet поддерживает встраивание изображений в рабочие листы. Используйте один из методов рабочего листа WriteImage(), чтобы добавить изображение на рабочий лист:

  MyWorksheet.WriteImage(row, col, filename, offsetX, offsetY, scaleX, scaleY);
  MyWorksheet.WriteImage(row, col, stream, offsetX, offsetY, scaleX, scaleY);
  MyWorksheet.WriteImage(row, col, imageindex, offsetX, offsetY, scaleX, scaleY);

Верхний/левый угол изображения помещается в верхний/левый угол ячейки в указанной строке и столбце. Параметры плавающей запятой offsetX, offsetY, scaleX и scaleY являются необязательными: они определяют смещение точки привязки этого изображения от угла ячейки и коэффициент увеличения. Путь к файлу изображения указывается как параметр filename. В качестве альтернативы можно использовать перегруженные версии, которые принимают поток [данных] вместо имени файла или индекса изображения в списке EmbeddedObj рабочей книги - используйте MyWorkbook.FindEmbeddedObj(имя файла), чтобы получить этот индекс для ранее загруженного изображения файл.

Обратите внимание, что FPSpreadsheet должен знать тип изображения для успешного импорта картинок. В настоящее время поддерживаются типы png, jpg, tiff, bmp, gif, svg, wmf, emf и pcx (Excel2007 не может читать импортированные изображения svg and pcx). Другие форматы могут быть зарегистрированы путем записи функции, которая определяет размер изображения и плотность пикселей, а также путем регистрации нового формата с помощью процедуры RegisterImageType - см. модуль fpsImages для [получения] примеров:

type
  TsImageType = integer;
  TGetImageSizeFunc = function (AStream: TStream; out AWidth, AHeight: DWord; out dpiX, dpiY: Double): Boolean;

function RegisterImageType(AMimeType, AExtension: String; AGetImageSize: TGetImageSizeFunc): TsImageType;

Из-за различий в расчете высоты строки и ширины столбцов между приложениями FPSpreadsheet и Office невозможно правильно позиционировать изображения. Если точное положение изображения важно, вы должны соблюдать следующие правила:

  • Предопределите ширину всех столбцов, по крайней мере, до той, которая содержит правый край изображения.
  • Предопределите высоты всех строк, по крайней мере, до той, которая содержит нижний край изображения.
  • Если рабочая книга должна быть сохранена в формате OpenDocument, добавьте изображение после изменения ширины столбцов и высоты строк, поскольку ods привязывает изображение к листу, а не к ячейке (как Excel и FPSpreadsheet).
  • Если точный размер изображения важен, убедитесь, что он помещается в одну ячейку.

Сортировка

Ячейки на рабочем листе можно сортировать по множеству критериев, вызывая метод Sort рабочего листа. Этот метод принимает запись TsSortParams, и края прямоугольника ячейки сортируются как параметры; в перегруженной версии прямоугольник ячейки также может быть задан с помощью строки диапазона типа Excel (например, 'A1:G10'):

type
  TsWorksheet = class
    // ...
    procedure Sort(const ASortParams: TsSortParams;
      ARowFrom, AColFrom, ARowTo, AColTo: Cardinal); overload;
    procedure Sort(ASortParams: TsSortParams; ARange: String); overload;
    // ...
  end;

Критерии сортировки определяются записью типа TsSortParams:

type
  TsSortParams = record
    SortByCols: Boolean;
    Priority: TsSortPriority;  // spNumAlpha ("Сначала числа") или spAlphaNum ("Сначала текст")
    Keys: TsSortKeys;
  end;    

  TsSortKey = record
    ColRowIndex: Integer;
    Options: TsSortOptions;    // set of [spDescending, spCaseInsensitive]
  end;
  • Логическое значение SortByCols определяет, что сортировка выполняется вдоль столбцов (true) или строк (false). Соответственно, ColRowIndex, указанный в ключах сортировки, соответствует индексу столбца или строки (см. ниже).
  • [Свойство] Priority определяет в диапазонах ячеек смешанного содержимого, будет ли возрастающий тип ставить числовые значения перед текстовыми значениями или нет. Пустые ячейки всегда перемещаются в конец отсортированного столбца или строки. В Excel приоритетом является [флаг] "сначала числа" (spNumAlpha).
  • Массив Keys указывает несколько параметров сортировки. Они состоят из индекса отсортированного столбца или строки (ColRowIndex) и набора [свойств] Options для [указания] направления сортировки (spoDescending) и РЕГИСТРОзависимости (spCaseInsensitive). Если [набор свойств] Options пуст, сравнение ячеек чувствительно к регистру, а ячейки упорядочены в порядке возрастания. Если две ячейки считаются "равными" на основе первого ключа (sortParams.Keys[0]), сравнение выполняется со следующими условиями в массиве Keys до тех пор, пока [не будет] обнаружена разница [между ячейками] или все условия [не будут] исчерпаны.

InitSortParams - это удобная утилита для инициализации параметров сортировки:

function InitSortParams(ASortByCols: Boolean = true; ANumSortKeys: Integer = 1;
  ASortPriority: TsSortPriority = spNumAlpha): TsSortParams;

Следующий фрагмент кода показывает типичный вызов сортировки:

var
  sortParams: TsSortParams;
begin
  sortParams := InitSortParams(true, 2);  // сортировка по столбцам, 2 сортировочных ключа

  // первичный ключ сортировки: столбец 3, восходящая [сортировка], нечувствительный к регистру
  sortParams.Keys[0].ColRowIndex := 3;
  sortParams.Keys[0].Options := [ssoCaseInsensitive];

  // вторичный ключ сортировки: столбец 1, нисходящая [сортировка]
  sortParams.Keys[1].ColRowIndex := 1;
  sortParams.Keys[1].Options := [ssoDescending];

  // Блок сортировки распространяется между ячейкой A1 (строка=0, столбец=0) и F10 (строка=9, столбец=5)
  MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
  // или: MyWorksheet.Sort(sortParams, 'A1:F10');
end;

Поиск и замена

Модуль fpsSearch реализует поисковую систему, которая может использоваться для поиска определенного содержимого ячейки в рабочей книге или для замены найденного содержимого ячейки какой-либо другой строкой.

Например:

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
  MyRow, MyCol: Cardinal;
  MySearchParams: TsSearchParams;
begin
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile(AFileName);

    // Specify search criteria
    MySearchParams.SearchText := 'Привет';
    MySearchParams.Options := [soEntireDocument];
    MySearchParams.Within := swWorkbook;

    // или: MySearchParaams := InitSearchParams('Привет', [soEntireDocument], swWorkbook);

    // Создать поисковую систему и выполнить поиск
    with TSearchEngine.Create(MyWorkbook) do begin
      if FindFirst(MySearchParams, MyWorksheet, MyRow, MyCol) then begin
        WriteLn('Первый [искомый текст] "', MySearchparams.SearchText, '" найден в ячейке ', GetCellString(MyRow, MyCol));
        while FindNext(MySeachParams, MyWorksheet, myRow, MyCol) do
          WriteLn('Следующий [искомый текст] "', MySearchParams.SearchText, '" найден в ячейке ', GetCellString(MyRow, MyCol));
      end;
      Free;
    end;
  finally
    MyWorkbook.Free;
  end;
end;

Поисковая система предоставляет два метода для поиска: FindFirst иFindNext. Они очень похожи, но различаются только местом, где начинается поиск. В случае FindFirst начальная ячейка определяется [набором опций] из списка Options, описанных ниже. В случае FindNext поиск начинается в ячейке, следующей за предыдущей найденной ячейкой. Оба метода возвращают рабочий лист, [а также] индексы строк и столбцов ячейки, в которой найден искомый текст. Если поиск безуспешен, результат функции FALSE.

Запись TsSearchParams указывает критерии, используемые для поиска:

type
  TsSearchParams = record
    SearchText: String;
    Options: TsSearchOptions;
    Within: TsSearchWithin;
  end;

Помимо текста для поиска (SearchText), она предоставляет набор опций для сужения поиска:

  • soCompareEntireCell: сравнивает SearchText со всем содержимым ячейки. Если [эта опция] не содержится в Options, текст ячейки сравнивается только частично.
  • soMatchCase: [позволяет] выполнить поиск с учетом регистр
  • soRegularExpr: [здесь] SearchText рассматривается как регулярное выражение
  • soAlongRows: Поисковая система осуществляет поиск сначала вдоль строк. Если [эта опция] не содержится в Options, поиск продолжается по столбцам.
  • soBackward: Поиск начинается с конца документа или выполняется назад из активной ячейки [(к началу документа)]. Если [эта опция] не содержится в Options, поиск начинается с начала документа или выполняется вперед из активной ячейки [(к концу документа)].
  • soWrapDocument: Если поиск достиг конца документа, поиск возобновляется с самого начала (или наоборот, если используется soBackward).
  • soEntireDocument: Поиск начинается с первой ячейки (или последней ячейки, если используется soBackward). Если [эта опция] не содержится в Options, поиск начинается с активной ячейки рабочего листа. [При этом] игнорируется [опция] FindNext.

Поле записи Within идентифицирует часть электронной таблицы для поиска:

  • swWorkbook: Идет поиск по всей рабочей книге. Если поисковая фраза не найдена на первом листе (или последнем листе, если используется soBackward), поиск продолжается со следующего (предыдущего) листа.
  • swWorksheet: Поиск ограничен текущим активным рабочим листом
  • swColumn: Поиск ограничен столбцом активной ячейки
  • swRow: Поиск ограничен строкой активной ячейки

Запись параметров поиска может быть инициализирована вызовом InitSearchParams (в модуле fpsutils) с элементами записи в качестве необязательных параметров.

В дополнение к поиску поисковая система также может использоваться для замены найденного текста другой строкой. Для этого вызовите функции ReplaceFirst или ReplaceNext. Они действуют как аналог [функции] FindXXXX, поэтому для определения критериев поиска требуется запись TsSearchParams. Но в дополнение к поиску эти функции также выполняют замену текста в соответствии со спецификацией в записи TsReplaceParams:

type
  TsReplaceParams = record
    ReplaceText: String;
    Options: TsReplaceOptions;
  end;

ReplaceText идентифицирует строку, которая заменит найденный шаблон текста. Параметры Options определяют набор критериев, по которым выполняется замена:

  • roReplaceEntirecell: Заменяет весь текст ячейки текстом ReplaceText. Если [эта опция] не содержится в Options, тогда заменяется только часть, соответствующая SearchText.
  • roReplaceAll: Выполняет замену во всех найденных ячейках (т.е. просто вызывает ReplaceFirst, чтобы автоматически заменить все).
  • roConfirm: Вызывает обработчик для события OnConfirmReplacement, в котором пользователь должен указать, должна ли выполняться замена или нет. Обратите внимание, что этот обработчик событий является обязательным, если установлен roConfirm.

Используйте функцию InitReplaceParams (в модуле fpsutils) для инициализации записи параметров замены с предопределенными (но необязательными) значениями.

Операции со столбцами и строками

Рабочий лист предоставляет эти методы для вставки, удаления, скрытия или отображения столбцов и строк:

type
  TsWorksheet = class
  ...
    procedure DeleteCol(ACol: Cardinal);
    procedure DeleteRow(ARow: Cardinal);

    procedure InsertCol(ACol: Cardinal);
    procedure InsertRow(ARow: Cardinal);

    procedure RemoveCol(ACol: Cardinal);
    procedure RemoveRow(ARow: Cardinal);

    procedure RemoveAllCols;
    procedure RemoveAllRows

    procedure HideCol(ACol: Cardinal);
    procedure HideRow(ARow: Cardinal);
    
    procedure ShowCol(ACol: Cardinal);
    procedure ShowRow(ARow: Cardinal);

    function ColHidden(ACol: Cardinal): Boolean;
    function RowHidden(ARow: Cardinal): Boolean;
  • Когда столбец или строка удалены [методом] DeleteCol или DeleteRow, любые данные, назначенные этому столбцу или строке, т.е. ячейки, комментарии, гиперссылки, записи TCol или TRow, удаляются. Данные справа или ниже удаленной колонки/строки перемещаются [соответственно] влево или вверх.
  • RemoveCol и RemoveRow по соглашению удаляют только запись столбца или строки, т.е. сбрасывают ширину столбца и высоту строки до значений по умолчанию. Данные ячейки, комментариев и гиперссылок не затрагиваются.
  • RemoveAllCols удаляет все записи столбцов, т.е. сбрасывает ширину всех столбцов; RemoveAllRows делает то же самое с записями строки и высотой строк.
  • Столбец или строка вставляются перед индексом, указанным в качестве параметра метода InsertXXX.

Макет страницы

Общие сведения

Пока что FPSpreadsheet не поддерживает печать рабочих листов, но приложения Office делают [это], и они предоставляют секцию информации в своих файлах для этой цели. В FPSpreadsheets эта информация доступна в классе TsPageLayout, который принадлежит структуре данных TsWorksheet. Его свойства и методы сочетают в себе самые важные функции из мира Excel и OpenDocument.

type 
  TsPageOrientation = (spoPortrait, spoLandscape);

  TsPrintOption = (poPrintGridLines, poPrintHeaders, poPrintPagesByRows,
    poMonochrome, poDraftQuality, poPrintCellComments, poDefaultOrientation,
    poUseStartPageNumber, poCommentsAtEnd, poHorCentered, poVertCentered,
    poDifferentOddEven, poDifferentFirst, poFitPages);

  TsPrintOptions = set of TsPrintOption;  

  TsHeaderFooterSectionIndex = (hfsLeft, hfsCenter, hfsRight);

  TsCellRange = record
    Row1, Col1, Row2, Col2: Cardinal;
  end;

  TsPageLayout = class
  ...
  public
    ...
    { Methods }
    // встроенные изображения заголовка/нижнего колонтитула
    procedure AddHeaderImage(AHeaderIndex: Integer;
      ASection: TsHeaderFooterSectionIndex; const AFilename: String);
    procedure AddFooterImage(AFooterIndex: Integer;
      ASection: TsHeaderFooterSectionIndex; const AFilename: String);
    procedure GetImageSections(out AHeaderTags, AFooterTags: String);
    function HasHeaderFooterImages: Boolean;

    // Повторитель строк и столбцов 
    function HasRepeatedCols: Boolean;
    function HasRepeatedRows: Boolean;
    procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
    procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);

    // диапазоны печати 
    function AddPrintRange(ARow1, ACol1, ARow2, ACol2: Cardinal): Integer; overload;
    function AddPrintRange(const ARange: TsCellRange): Integer; overload;
    function GetPrintRange(AIndex: Integer): TsCellRange;
    function NumPrintRanges: Integer;
    procedure RemovePrintRange(AIndex: Integer);

    { Properties }
    property Orientation: TsPageOrientation read FOrientation write FOrientation;
    property PageWidth: Double read FPageWidth write FPageWidth;
    property PageHeight: Double read FPageHeight write FPageHeight;
    property LeftMargin: Double read FLeftMargin write FLeftMargin;
    property RightMargin: Double read FRightMargin write FRightMargin;
    property TopMargin: Double read FTopMargin write FTopMargin;
    property BottomMargin: Double read FBottomMargin write FBottomMargin;
    property HeaderMargin: Double read FHeaderMargin write FHeaderMargin;
    property FooterMargin: Double read FFooterMargin write FFooterMargin;
    property StartPageNumber: Integer read FStartPageNumber write SetStartPageNumber;
    property ScalingFactor: Integer read FScalingFactor write SetScalingFactor;
    property FitHeightToPages: Integer read FFitHeightToPages write SetFitHeightToPages;
    property FitWidthToPages: Integer read FFitWidthToPages write SetFitWidthToPages;
    property Copies: Integer read FCopies write FCopies;
    property Options: TsPrintOptions read FOptions write FOptions;
    property Headers[AIndex: Integer]: String read GetHeaders write SetHeaders;
    property Footers[AIndex: Integer]: String read GetFooters write SetFooters;
    property RepeatedCols: TsRowColRange read FRepeatedCols;
    property RepeatedRows: TsRowColRange read FRepeatedRows;
    property PrintRange[AIndex: Integer]: TsCellRange read GetPrintRange;
    property FooterImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetFooterImages;
    property HeaderImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetHeaderImages;
  end;

  TsWorksheet = class
  ...
  public
    property PageLayout: TsPageLayout;
    ...
  end;
Light bulb  Примечание: Тот факт, что PageLayout принадлежит рабочему листу, указывает на то, что в одной книге может быть несколько макетов страниц, по одному для каждого листа.
  • Orientation [(ориентация)] определяет ориентацию печатной бумаги, как портретной, так и альбомной.
  • Page width [(ширина страницы)] и page height [(высота страницы)] ссылаются на стандартную ориентацию бумаги, обычно портретную.
  • Left, top, right and bottom margins не требуют пояснений и приведены в миллиметрах.
  • HeaderMargin [(отступ заголовка)] понимается - как и в Excel - как расстояние между верхним краем бумаги и верхней частью заголовка, и TopMargin [(отступ сверху)] - соответственно, как расстояние между верхним краем бумаги и верхней частью первой строки таблицы, т.е. если заголовок содержит несколько разрывов строк, он может располагаться в части таблицы вне печати. Это отличается от файлов OpenDocument, где заголовок может соответственно расти [по высоте].
  • StartPageNumber [(номер начальной страницы)] должен быть изменен, если печать [документа] не должна начинаться со страницы 1. Для этого параметра [вручную] необходимо добавить опцию poUseStartPageNumber в Options макета страницы, но обычно это делается автоматически.
  • ScalingFactor [(коэффициент масштабирования)] указывается в процентах и может использоваться для уменьшения количества печатаемых страниц. Изменение этого свойства очищает опцию poFitToPages [свойства] Options макета страницы.
  • В качестве альтернативы ScalingFactor вы также можете использовать FitHeightToPages ([подгонять высоту до страниц]) или FitWidthToPages ([подгонять ширину до страниц]). Опция poFitToPages должна быть активной, чтобы переопределить параметр ScalingFactor. FitHeightToPages указывает целое количество страниц, на которых должна уместиться вся высота рабочего листа. Соответственно, FitWidthToPages может использоваться для определения целого количества страниц, которым должна соответствовать вся ширина рабочего листа. Значение 0 определяет специальное понятие "использовать столько страниц, сколько необходимо". Таким образом, этот вариант может быть установлен для параметра Excel "Устанавливать все столбцы на одной странице", например:
  MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
  MyWorksheet.PageLayout.FitWidthToPages := 1;     // все столбцы на ширине одной страницы
  MyWorksheet.PageLayout.FitHeightToPages := 0;    // использовать как можно больше страниц
  • Header rows and columns repeated on every printed page [(заголовки строк и столбцов повторяются на каждой печатной странице)] может быть определена [свойствами] RepeatedCols и RepeatedRows записей; для повтора [на каждой странице] их элементы FirstIndex и LastIndex ссылаются на индексы первого и последнего столбца или строки, соответственно. Для определения этих чисел используйте методы SetRepeatedCols и SetRepeatedRows. Обратите внимание, что второй параметр для последнего индекса может быть опущен при использовании только одной строки или столбца заголовка.
  • Print ranges [(диапазон печати)] или print areas [(область печати)] (пользуясь терминологией Excel) может использоваться для ограничения печати только в диапазоне ячеек. Используйте методы AddPrintRange для определения диапазона ячеек для печати: укажите индексы левого столбца, верхней строки, правого столбца и нижней строки диапазона для печати. Рабочий лист может содержать несколько диапазонов печати.
  • Copies [(копии)] определяет, как часто печатается рабочий лист.
  • Options [(опции)] определяет дополнительные свойства печати, их имена являются самообучающимися. Они были определены в соответствии с [спецификацией] файлов Excel, некоторые из них не существуют в файлах ODS и там игнорируются.

Заголовки и колонтитулы

Текст заголовков и колонтитулов может состоять из выровненных по левому краю, центрированных и выровненных по правому краю строк. Добавьте символ &L, чтобы указать, что следующая строка должна быть напечатана с выравниванием слева; используйте &C соответственно для центрируемых [строк] и &R для [строк] с выравниванием по правому краю. Существуют другие символы, которые во время печати будут дополнять [действие символов]:

  • &L: начинает описание раздела с выравниванием по левому краю текста заголовка или нижнего колонтитула
  • &C: начинает описание раздела с выравниванием по центру текста заголовка или нижнего колонтитула
  • &R: начинает описание раздела с выравниванием по правому краю текста заголовка или нижнего колонтитула
  • &P: номер страницы
  • &N: количество страниц
  • &D: текущая дата печати
  • &T: текущее время печати
  • &A: имя рабочего листа
  • &F: имя файла без пути [к файлу]
  • &P: путь к файлу без имени файла
  • &G: встроенное изображение - используйте методы AddHeaderImage или AddFooterImage, чтобы указать файл изображения; это также добавляет [символ] &G к другим кодам текущего раздела header/footer. Обратите внимание, что не все типы изображений, известные в приложении Office, могут быть приняты. В настоящее время изображение может быть [с расширением] jpeg, png, gif, bmp, tiff, pcx, svg, wmf или emf.
  • &B: жирный вкл/выкл
  • &I: курсив вкл/выкл
  • &U: подчеркивание вкл/выкл
  • &E: двойное подчеркивание вкл/выкл
  • &S: зачеркнутый вкл/выкл
  • &H: тень вкл/выкл
  • &O: контур вкл/выкл
  • &X: верхний индекс вкл/выкл
  • &Y: нижний индекс вкл/выкл
  • &"font": начинает использовать шрифт с определенным именем, напр., &"Arial"
  • &number: начинает использовать определенный размер шрифта (в точках), напр., &16
  • &Krrggbb: переключает цвет шрифта в определенное двоичное значение указанного цвета, напр., используйте &KFF0000 для [переключения] в красный.

Массивы Headers[]/Footers[] предоставляют пространство для использования трех разных верхних и нижних колонтитулов:

  • Headers[0] относится к заголовку, используемому только на первой странице, [также] аналогично для Footers[0]. Вместо индекса 0 вы можете использовать константу HEADER_FOOTER_INDEX_FIRST. Оставьте эту строку пустой, если на первой странице нет специального заголовка/нижнего колонтитула.
  • Headers[1] относится к заголовку на страницах с нечетными номерами страниц, [также] аналогично для Footers[1]</ tt>. Вместо индекса 1 вы можете использовать константу HEADER_FOOTER_INDEX_ODD .
  • Headers[2] относится к заголовку на страницах с четными номерами страниц, [также] аналогично для Footers[2]. Вместо индекса 2 вы можете использовать константу HEADER_FOOTER_INDEX_EVEN.

Оставьте строки с индексом 0 и 2 пустыми, если в распечатке всегда должен быть один и тот же верхний/нижний колонтитул. Для большей ясности вы можете использовать константу HEADER_FOOTER_INDEX_ALL. Например:

  MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T';           // выровненный по центру заголовок "дата время" на всех страницах 
  MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N';   // выровненный  по правому краю нижний колонтитул на нечетных страницах "Страница .. из .." 
  MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N';  // то же, но на четных страницах с выравниванием слева

Защита

В приложениях Office книги могут быть защищены от непреднамеренных изменений пользователем. fpspreadsheet способен читать и записывать структуры данных, связанные с защитой, но не применяет их. Это означает, например, что ячейки могут быть изменены пользователем, хотя рабочий лист указан как заблокированный.

Защита осуществляется на трех уровнях: защита рабочей книги, защита рабочего листа и защита элементов.

Защита рабочей книги

TsWorkbook содержит набор параметров защиты рабочей книги или документов:

  • bpLockRevision: указывает, что книга заблокирована для ревизии
  • bpLockStructure: если этот параметр установлен, рабочие листы в книге нельзя перемещать, удалять, скрывать, скрывать или переименовывать, а новые рабочие листы не могут быть вставлены.
  • bpLockWindows: указывает, что окна книги в приложении Office заблокированы. Окна имеют одинаковый размер и положение каждый раз, когда рабочая книга открывается приложением Office.

В отношении защиты рабочей книги используется опция рабочего листа soPanesProtection, которая предотвращает изменение панелей рабочего листа, если рабочая книга защищена.

В зависимости от формата файла могут поддерживаться только некоторые из этих параметров. В этих случаях не поддерживаемые параметры являются общепринятыми значениями по умолчанию.

Защита рабочего листа

TsWorksheet содержит аналогичный набор параметров защиты. Всякий раз, когда опция включена в набор Protection рабочей книги, соответствующее действие не разрешено и заблокировано:

  • spCells: ячейки в листе защищены. Это зависит от уровня защиты ячейки, может ли конкретная ячейка быть изменена или нет. По умолчанию ни одна ячейка не может быть изменена.
  • spDeleteColumns: удаление столбцов не допускается
  • spDeleteRows: удалить строки невозможно
  • spFormatCells: форматирование ячеек не допускается
  • spFormatColumns: столбцы не могут быть отформатированы
  • spFormatRows: строки не могут быть отформатированы
  • spInsertColumns: не допускается вставка столбцов
  • spInsertRows: строки не могут быть вставлены
  • spInsertHyperlinks: невозможно вставить новые гиперссылки
  • spSort: рабочий лист не может быть отсортирован
  • spSelectLockedCells: ячейки, которые заблокированы, больше не могут быть выбраны
  • spSelectUnlockedCells: невозможно выбрать даже [те] ячейки, которые разблокированы. Вместе с spSelectLockedCells это означает, что выбор на листе заморожен.

Эти уровни защиты становятся активными, если опция soProtected добавлена в [набор] Options рабочего листа или [посредством] вызова метода Protect(true) рабочего листа.

Защита ячейки

Защита ячеек становится активной, когда включена защита рабочего листа. Он управляется набором элементов TsCellProtection, которые принадлежат к формату ячейки:

  • cpLockCell: этот параметр определяет, может ли содержимое ячейки быть изменено пользователем. Поскольку по умолчанию ячейки защищенного рабочего листа обычно не могут редактироваться. Чтобы разблокировать некоторые ячейки для пользовательского ввода, опция cpLockCell должна быть удалена из защиты этих ячеек.
  • cpHideFormulas: предотвращает отображение формул в приложении Office.

Защита ячейки может быть изменена путем вызова метода WriteCellProtection рабочего листа. И наоборот, [свойство] ReadCellProtection может использоваться для извлечения состояния защиты конкретной ячейки:

// запрос и изменение состояния защиты ячейки A1 (строка=0, столбец=0)
var
  cell: PCell;
  cellprot: TsCellProtections;
...
  // Находим ячейку
  cell := worksheet.FindCell(0, 0);
  // запрос защиты ячейки
  cellprot := worksheet.ReadCellProtection(cell);
  // Разблокировка ячейки для редактирования без изменения видимости формул
  worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
  // Сокрытие формулы ячейки и разблокировка ячейки.
  worksheet.WriteCellProtection(cell, [cpHideFormulas]);

Пароли

Защита рабочей книги и рабочего листа может быть обезопасена паролем. Обратите внимание, что эти пароли не шифруют файл (кроме защиты рабочей книги в Excel 2007). В приложениях Office пользователь должен ввести этот пароль, чтобы отключить защиту или изменить элементы защиты. Зашифрованный пароль хранится в записи CryptoInfo словаря и рабочих листов соответственно:

type
  TsCryptoInfo = record
    PasswordHash: String;
    Algorithm: TsCryptoAlgorithm;  // caExcel, caSHA1, caSHA256, etc.
    SaltValue: String;
    SpinCount: Integer;
  end;
Warning-icon.png

Предупреждение: FPSpreadsheet не выполняет никаких расчетов хэширования, запись CryptoInfo просто передается от чтения до записи. Это вызывает проблемы, если при чтении и записи используются разные форматы файлов. Компонент пытается обнаружить несовместимые комбинации. В этих случаях защита паролем удаляется, и в словаре записывается ошибка.

Loading and saving

Добавление новых форматов файлов

FPSpreadsheet is open to any spreadsheet file format. In addition to the built-in file formats which are specified by one of the sfXXXX declarations, it is possible to provide dedicated reader and writer units to get access to special file formats.

  • Write a unit implementing a reader and a writer for the new file format. They must inherit from the basic TsCustomSpreadReader and TsCustomWriter, respectively, - both are implemented in unit fpsReaderWriter -, or from one of the more advanced ones belonging to the built-in file formats.
  • Register the new reader/writer by calling the function RegisterSpreadFileFormat in the initialization section of this unit (implemented in unit fpsRegFileFormats):
function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
  AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;
  • AFormat must have the value sfUser to register an external file format.
  • AReaderClass is the class of the reader (or nil, if reading functionality is not implemented).
  • AWriterClass is the class of the writer (or nil, if writing functionality is not implemented).
  • AFormatName defines the name of the format as used for example in the filter list of file-open dialogs.
  • ATechnicalName defines a shorter format name.
  • AFileExtensions is an array of file extensions used in the files. The first array element denotes the default extension. The extensions must begin with a period as in .xls.
  • The registration function returns a numerical value (TsSpreadFormatID) which can be used as format identifier in the workbook reading and writing functions which exist in overloaded version accepting a numerical value for the format specifier. In contract to the built-in formats the FormatID is negative.
  • Finally, in your application, add the new unit to the uses clause. This will call the registrations function when the unit is loaded and make the new file format available to FPSpreadsheet.

Stream selection

Workbooks are loaded and saved by means of the ReadFromFile and WriteToFile methods, respectively (or by their stream counterparts, ReadFromStream and WriteToStream).

By default, the data files are accessed by means of memory streams which yields the fastest access to the files. In case of very large files (e.g. tens of thousands of rows), however, the system may run out of memory. There are two methods to defer the memory overflow by some extent.

  • Add the element boBufStream to the workbook's Options. In this case, a "buffered" stream is used for accessing the data. This kind of stream holds a memory buffer of a given size and swaps data to file if the buffer becomes too small.
  • Add the element boFileStream to the workbook's Options. This option avoids memory streams altogether and creates temporary files if needed. This is, however, the slowest method of data access.
  • If both options are set then boBufStream is ignored.
  • In practice, however, the effect of the selected streams is not very large if memory is to be saved.

Virtual mode

Beyond the transient memory usage during reading/writing the main memory consumptions originates in the internal structure of FPSpreadsheet which holds all data in memory. To overcome this limitation, a "virtual mode" has been introduced. In this mode, data are received from a data source (such as a database table) and are passed through to the writer without being collected in the worksheet. It is clear that data loaded in virtual mode cannot be displayed in the visual controls. Virtual mode is good for conversion between different data formats.

These are the steps required to use this mode:

  • Activate virtual mode by adding the option boVirtualMode to the Options of the workbook.
  • Tell the spreadsheet writer how many rows and columns are to be written. The corresponding worksheet properties are VirtualRowCount and VirtualColCount.
  • Write an event handler for the event OnWriteCellData of the worksheet. This handler gets the index of row and column of the cell currently being saved. You have to return the value which will be saved in this cell. You can also specify a template cell that physically exists in the workbook from which the formatting style is copied to the destination cell. Please be aware that when exporting a database, you are responsible for advancing the dataset pointer to the next database record when writing of a row is complete.
  • Call the WriteToFile method of the workbook.

Virtual mode also works for reading spreadsheet files.

The folder example/other contains a worked out sample project demonstrating virtual mode using random data. More realistic database examples are in example/db_import_export and in the chapter on converting a large database table using virtual mode.

Dataset export

FPC contains a set of units that allow you to export datasets to various formats (XML, SQL statements, DBF files,...). There is a master package that allows you to choose an export format at design time or run time (Lazarus package lazdbexport).

FPSpreadsheet has TFPSExport which plugs into this system. It allows you to export the contents of a dataset to a new spreadsheet (.xls, .xlsx, .ods, wikitable format) file into a table on the first sheet by default. In addition, if MultipleSheets is set to TRUE it is possible to combine several sheets into individual worksheets in the same file. You can optionally include the field names as header cells on the first row using the HeaderRow properties in the export settings. The export component tries to find the number format of the cells according to the dataset field types.

For more complicated exports, you need to manually code a solution (see examples below) but for simple data transfer/dynamic exports at user request, this unit will probably be sufficient.

A simple example of how this works:

uses
...
fpsexport
...
var
  Exp: TFPSExport;
  ExpSettings: TFPSExportFormatSettings;
  TheDate: TDateTime;
begin
  FDataset.First; //assume we have a dataset called FDataset
  Exp := TFPSExport.Create(nil);
  ExpSettings := TFPSExportFormatSettings.Create(true);
  try
    ExpSettings.ExportFormat := efXLS; // choose file format
    ExpSettings.HeaderRow := true; // include header row with field names
    Exp.FormatSettings := ExpSettings; // apply settings to export object
    Exp.Dataset:=FDataset; // specify source
    Exp.FileName := 'c:\temp\datadump.xls';
    Exp.Execute; // run the export
  finally
    Exp.Free;
    ExpSettings.Free;
  end;

Export to DBF

You can save the information from Excel to dbf rather easily. Here is the example from forum. Please note that this example is supposed to work with Win1251 encoding by default. To get more information please refer to this thread [1]

procedure TForm1.ExportToDBF(AWorksheet: TsWorksheet; AFileName: String);
var
  i: Integer;
  f: TField;
  r, c: Cardinal;
  cell: PCell;
begin
  DbfGlobals.DefaultCreateCodePage := 1251; //default encoding of the dbf
  DbfGlobals.DefaultOpenCodePage := 1251; //default encoding of the dbf
  if Dbf1.Active then Dbf1.Close;
  if FileExists(AFileName) then DeleteFile(AFileName);

  Dbf1.FilePathFull := ExtractFilePath(AFileName);
  Dbf1.TableName := ExtractFileName(AFileName);
  Dbf1.TableLevel := 25;  // DBase IV: 4 - most widely used; or 25 = FoxPro supports nfCurrency
  Dbf1.LanguageID := $C9; //russian language by default
  Dbf1.FieldDefs.Clear;
  //below are the fields in excel
  Dbf1.FieldDefs.Add('fam', ftString);
  //add other fields you want to save

  Dbf1.CreateTable;
  Dbf1.Open;

  for f in Dbf1.Fields do
    f.OnGetText := @DbfGetTextHandler;

  // Skip row 0 which contains the headers
  for r := 1 to AWorksheet.GetLastRowIndex do begin
    Dbf1.Append;
    for c := 0 to Dbf1.FieldDefs.Count-1 do begin
      f := Dbf1.Fields[c];
      cell := AWorksheet.FindCell(r, c);
      if cell = nil then
        f.Value := NULL
      else
        case cell^.ContentType of
          cctUTF8String: f.AsString := UTF8ToCP1251(cell^.UTF8StringValue);
          cctNumber: f.AsFloat := cell^.NumberValue;
          cctDateTime: f.AsDateTime := cell^.DateTimeValue;
          else f.AsString := UTF8ToCP1251(AWorksheet.ReadAsText(cell));
        end;
    end;
    Dbf1.Post;
  end;
end;

//This procedure is called so the text in the cells could be correctly dispayed on WorkSheetGrid on the form, otherwise '?' symbols showing
procedure TForm1.DbfGetTextHandler(Sender: TField; var AText: string; DisplayText: Boolean);
begin
  if DisplayText then
    AText := CP1251ToUTF8(Sender.AsString);
end;

Visual controls for FPSpreadsheet

The package laz_fpspreadsheet_visual implements a series of controls which simiplify creation of visual GUI applications:

  • TsWorkwookSource links the controls to a workbook and notifies the controls of changes in the workbook.
  • TsWorksheetGrid implements a grid control with editing and formatting capabilities; it can be applied in a similar way to TStringGrid.
  • TsWorkbookTabControl provides a tab sheet for each worksheet of the workbook. It is the ideal container for a TsWorksheetGrid.
  • TsCellEdit corresponds to the editing line in Excel or Open/LibreOffice. Direct editing in the grid, however, is possible as well.
  • TsCellIndicator displays the name of the currently selected cell; it can be used for navigation purposes by entering a cell address string.
  • TsCellCombobox offers to pick cell properties for a selection of formatting attributes: font name, font size, font color, background color.
  • TsSpreadsheetInspector is a tool mainly for debugging; it displays various properties of workbook, worksheet, cell value and cell formatting, similar to the ObjectInspector of Lazarus. It is read-only, though.
  • Various standard actions are provided in the unit fpsActions. Applied to menu or toolbar, they simplify typical formatting and editing tasks without having to write a line of code.
  • TsWorkbookChartSource interfaces a workbook with the TAChart library. It defines the cell ranges from which a Chart series can get its data. There is also an older component, TsWorksheetChartSource, which requires all cells to be from the same worksheet. For this reason, it has not been fully integrated into the environment of FPSpreadsheet controls and will be removed from the library in the future.

See the FPSpreadsheet tutorial: Writing a mini spreadsheet application for more information and a tutorial, and see demo projects for examples of the application of these components.

Examples

To create a project which uses the fpspreadsheet library, add the fpspreadsheet_pkg package to it's Lazarus project, or add the base directory of fpspreadsheet to you compiler options if using another IDE.

Units

The entire FPSpreadsheet package consists of several units. A spreadsheet application typically "uses" the following units:

  • fpspreadsheet: implements TsWorkbook and TsWorksheet and the basic file reading/writing methods
  • fpstypes: declares most of the data types and constants used throughout the package. Note: in older versions these declarations were contained in fpspreadsheet.pas.
  • the unit(s) implementing the reader/writer for a given file format, e.g. xlsbiff8 for binary Excel files. If the application will be able to handle all formats "use" the unit fpsallformats.

The next units are required only occasionally:

  • fpsutils: a collection of utility functions that are occasionally needed (e.g. conversion of col/row indexes to Excel-like cell address string).
  • fpscell: this unit is required if you use direct cell formatting (e.g. cell^.BackgroundColor := scYellow) instead of calling the corresponding worksheet method (MyWorksheet.WriteBackgroundColor(cell, scYellow), in this example).
  • fpsnumformat: collects all utility functions related to number formats.

All other units are probably not needed at the application level. In case of the visual spreadsheet controls, the needed units usually are inserted at design-time automatically.

Excel 5 example

Note: at least with fpspreadsheet from trunk (development version), this example requires (at least) Lazarus avglvltree.pas, lazutf8.pas, asiancodepagefunctions.inc, asiancodepages.inc and lconvencoding.pas (in the $(LazarusDir)\components\lazutils\ directory)

{
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, fpstypes, fpspreadsheet, fpsallformats, laz_fpspreadsheet;

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;
  try
    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
    MyWorksheet.WriteFormula(0, 4, 'A1+B1');

    // Creates a new worksheet
    MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');

    // Write some string cells
    MyWorksheet.WriteText(0, 0, 'First');
    MyWorksheet.WriteText(0, 1, 'Second');
    MyWorksheet.WriteText(0, 2, 'Third');
    MyWorksheet.WriteText(0, 3, 'Fourth');

    // Save the spreadsheet to a file
    MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
  finally
    MyWorkbook.Free;
  end;
end.

Opening an existing spreadsheet

To open a spreadsheet while specifying a particular format to use use ReadFromFile with two parameters:

MyWorkbook.ReadFromFile(AFileName, sfExcel5);

It is also possible to call ReadFromFile with only one parameter, the filename. Then the workbook will use the extension to auto-detect the file format. In case of the ambiguous extension .xls (Excel 2-8) it will simply try various possibilities until one works. Although typical fingerprint byte patterns are checked now it is still possible that an exception will be raised for each incorrect format if run from the IDE at designtime; this does not occur at runtime.

MyWorkbook.ReadFromFile(AFileName);

Writing a spreadsheet to file based on extension

Similar to the ReadFromFile routine, there is also a WriteToFile procedure to determine the spreadsheet's type based on the filename suffix. It uses the GetFormatFromFileName routine in the previous section's code, so the actual code is simple. However, it will always write files with a given extension using the latest format that uses that extension (e.g. Excel .xls files will be written as sfExcel8), so if you want to write them in an earlier format, you have to use the base routine.

As above, this code patches the fpspreadsheet.pas unit.

procedure TsWorkbook.WriteToFile(const AFileName: string; const AOverwriteExisting: Boolean = False); overload;
var SheetType: TsSpreadsheetFormat;
begin
  if getFormatFromFileName(AFileName, SheetType) then
    WriteToFile(AFileName, SheetType, AOverwriteExisting)
  else raise Exception.Create(Format(
    '[TsWorkbook.WriteToFile] Attempted to save a spreadsheet by extension, but the extension %s is invalid.', [ExtractFileExt(AFileName)]));
end;

Iterating through all 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;

Iteration through cells

The first idea is to use a simple for-to loop:

var
  MyWorksheet: TsWorksheet;
  col, row: Cardinal;
  cell: PCell;
begin
  for row:=0 to MyWorksheet.GetLastRowIndex do
    for col := 0 to MyWorksheet.GetLastColIndex do
    begin
      cell := MyWorksheet.FindCell(row, col);
      WriteLn(MyWorksheet.ReadAsUTF8Text(cell));
    end;
end;

FindCell initiates a search for a cell independently of the previously found cell. Cells, however, are are organized interally in a sorted tree structure, and each cell "knows" its previous and next neighbors. Moreover, FindCell wastes time on searching for non-existing cells in case of sparsely-occupied worksheets. In general, it is more efficient to use the for-in syntax which takes advantage of the internal tree structure by means of special enumerators. Note that there are also dedicated enumerators for searching along rows, columns or in cell ranges:

var
  MyWorksheet: TsWorksheet;
  cell: PCell;
begin
  // Search in all cells
  for cell in Myworksheet.Cells do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in column 0 only
  for cell in MyWorksheet.Cells.GetColEnumerator(0) do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in row 2 only
  for cell in MyWorksheet.Cells.GetRowEnumerator(2) do
    WriteLn(MyWorksheet.ReadAsText(cell));

  // Search in range A1:C2 only (rows 0..1, columns 0..2)
  for cell in MyWorksheet.Cells.GetRangeEnumerator(0, 0, 1, 2) do
    WriteLn(MyWorksheet.ReadAsText(cell));
end;

Converting a database to a spreadsheet

The easiest solution is to use the DatasetExport component.

If you need to have more control over the process, use something like:

program db5xls;
 
{$mode delphi}{$H+}
 
uses
  Classes, SysUtils, 
  // add database units
  fpstypes, fpspreadsheet, fpsallformats;
 
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.WriteText(0, i, MyDatabase.Fields[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.WriteText(j + 1, i, MyDatabase.Fields[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.

Converting a large database table to a spreadsheet using virtual mode

Light bulb  Примечание: The example program in examples\db_import_export shows a demonstration of using virtual mode to export datasets to spreadsheet files.

We want to write a large database table to a spreadsheet file. The first row of the spreadsheet is to show the field names in bold type face and with a gray background.

Normally, FPSpreadsheet would load the entire representation of the spreadsheet into memory, so we'll use virtual mode to minimize memory usage.

type
  TDataProvider = class;

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
  MyDatabase: TSdfDataset;
  MyDir: string;
  MyHeaderTemplateCell: PCell;
  DataProvider: TDataProvider;

// Implement TDataProvider here - see below...

begin
  // Initialization
  MyDir := ExtractFilePath(ParamStr(0));
 
  // Open the database
  MyDatabase := TSdfDataset.Create;
  try
    MyDatabase.Filename := 'test.dat';
    // Add table description here
    MyDatabase.Active := True;

    // Create the spreadsheet
    MyWorkbook := TsWorkbook.Create;
    try
      MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');

      // Create the template cell for the header line, we want the 
      // header in bold type-face and gray background color
      // The template cell can be anywhere in the workbook, let's just select cell A1.
      MyWorksheet.WriteFontStyle(0, 0, [fssBold]);
      MyWorksheet.WriteBackgroundColor(0, 0, scGray);
      // We'll need this cell again and again, so let's save the pointer to it in a variable
      MyHeaderTemplateCell := MyWorksheet.Find(0, 0);
 
      // Enter virtual mode
      MyWorkbook.Options := MyWorkbook.Options + [boVirtualMode];

      // Define number of columns - we want a column for each field
      MyWorksheet.VirtualColCount := MyDatabase.FieldCount;

      // Define number of rows - we want every record, plus 1 row for the title row
      MyWorksheet.VirtualRowCount := MyDatabase.RecordCount + 1;

      // Link the event handler which passes data from database to spreadsheet writer
      MyWorksheet.OnWriteCellData := @DataProvider.WriteCellData;

      // Write all cells to an Excel8 file
      // The data to be written are specified in the OnWriteCellData event handler.
      MyWorkbook.WriteToFile(MyDir + 'test.xls', sfExcel8);

    finally
      // Clean-up
      MyWorkbook.Free;
    end;

  finally
    // Close the database & clean-up
    MyDatabase.Active := False;
    MyDatabase.Free;
  end;

end.

What is left is to write the event handler for OnWriteCellData. For the command-line program above we setup a particular data provider class (in a gui program the event handler can also be a method of any form):

type
  TDataProvider = class
    procedure WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
  end;

procedure TDataProvider.WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; AValue: variant; var: AStyleCell: PCell);
begin
  // Let's handle the header row first:
  if ARow = 0 then begin
    // The value to be written to the spreadsheet is the field name.
    AValue := MyDatabase.Fields[ACol].FieldName;
    // Formatting is defined in the HeaderTemplateCell.
    AStyleCell := MyHeaderTemplateCell;
    // Move to first record
    MyDatabase.First;
  end else begin
    // The value to be written to the spreadsheet is the record value in the field corresponding to the column.
    // No special requirements on formatting --> leave AStyleCell at its default (nil).
    AValue := MyDatabase.Fields[ACol].AsVariant;
    // Advance database cursor if last field of record has been written
    if ACol = MyDatabase.FieldCount-1 then MyDatabase.Next;
  end;
end;

Converting between two spreadsheet formats

program ods2xls;
 
{$mode delphi}{$H+}
 
uses
  Classes, SysUtils, 
  fpstypes, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
 
const 
  INPUT_FORMAT = sfOpenDocument;
  OUTPUT_FORMAT = sfExcel8;
 
var
  MyWorkbook: TsWorkbook;
  MyDir: string;
begin
  // Initialization
  MyDir := ExtractFilePath(ParamStr(0));
 
  // Convert the spreadsheet
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile(MyDir + 'test.ods', INPUT_FORMAT);
    MyWorkbook.WriteToFile(MyDir + 'test.xls', OUTPUT_FORMAT);
  finally
    MyWorkbook.Free;
  end;
end.

Reading and writing of CSV files

CSV files (CSV = comma-separated values) are plain text files without metadata. Therefore, additional information for correct reading of writing from/to a worksheet is required. The global record CSVParams makes available fundamental settings for this purpose:

type
  CSVParams: TsCSVParams = record    // W = writing, R = reading, RW = reading/writing
    SheetIndex: Integer;             // W: Index of the sheet to be written
    LineEnding: TsCSVLineEnding;     // W: Specification for line ending to be written
    Delimiter: Char;                 // RW: Column delimiter
    QuoteChar: Char;                 // RW: Character for quoting texts
    Encoding: String;                // RW: Encoding of file
    DetectContentType: Boolean;      // R: try to convert strings to content types
    NumberFormat: String;            // W: if empty write numbers like in sheet, otherwise use this format
    AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
    TrueText: String;                // RW: String for boolean TRUE
    FalseText: String;               // RW: String for boolean FALSE
    FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
  end;

This record contains fields which are evaluated for reading only, writing only, or for both - see the attached comments.

A common situation is to read a file using a number decimal separator which is different from the system's decimal separator: Suppose you are on a European system in which the decimal separator is a comma, but the csv file to be read originates from a machine which uses a decimal point. And suppose also, that the file contains tab characters as column separator instead of the default comma. In this case, simply set the CSVParams.FormatSettings.DecimalSeparator to '.', and the CSVParams.Delimiter to #9 (TAB character) before reading the file:

uses
  fpstypes, fpspreadsheet, fpscsv;
var
  MyWorkbook: TsWorkbook;
begin
  CSVParams.FormatSettings.DecimalSeparator := '.';
  CSVParams.Delimiter := #9;
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile('machine-data.csv', sfCSV);
  finally
    MyWorkbook.Free;
  end;
end;
Light bulb  Примечание: If you want to give the user the possibility to interactively modify the CSVParams record have a look at the unit scsvparamsform.pas of the spready demo which provides a ready-to-use dialog.

Sample projects in the fpspreadsheet installation folder

A bunch of sample projects accompanies the FPSpreadsheet installation. They can be found in the folder "examples". Here is a brief description of these sample projects

  • db_import_export is an example showing how to export a large database table to a spreadsheet file using virtual mode or TFPSExport. It also shows importing the spreadsheet into a database using virtual mode.
  • fpsspeedtest compares the effect of file format and various reading/writing parameters on the speed of writing and reading very large spreadsheet files. Again, please run the write test first which create the test files used for the read test.
  • Folder read_write:
    • excel2demo contains command-line programs for writing and reading Excel 2.x xls files. Please run the write demo before the read demo so the required spreadsheet file is generated.
    • excel5demo, like excel2demo, but for Excel 5 xls files.
    • excel8demo, like excel2demo, but for Excel 97-2003 xls files.
    • csvdemo, like excel2demo, but for CSV files.
    • htmldemo, like excel2demo, but for HTML file (currently writing only).
    • ooxmldemo, like excel2demo, but for the new Excel xlsx files.
    • opendocdemo, like excel2demo, but for OpenOffice/LibreOffice ods files
    • wikitabledemo, like excel2demo, but for wiki table files. Note that the write example currently writes a format that the read example cannot understand.
  • other: simple commandline programs showing various aspects of the fpspreadsheet package. Have a look at readme.txt for more details.
  • Folder visual:
    • fpschart shows the application of the TsWorksheetChartSource and TsWorkbookChartSource and the interaction with the TAChart plotting package.
    • fpsctrls and fpsctrls_no_install create a GUI spreadsheet application with a minimum amount of written code; the latter demo is good for testing because it does not require installation of the FPSpreadsheet packages. Step-by-step instructions on how fpsctrls is made can be found in the FSpreadsheet tutorial.
    • fpsgrid and fpsgrid_no_install show the basic application of the TsWorksheetGrid without using the TsWorkbookSource component; the latter demo does not require installation of any FPSpreadsheet package.
    • wikitablemaker is a small application for creation of code to be used for tables on wiki pages. Type the data into a TsWorksheetGrid (or load an existing spreadsheet file), go to page "Code" to see the generated wiki code, click "Copy to clipboard" and paste the code into the wiki page.
  • spready is an extended application of the the entire library showing spreadsheet files with formatting, editing of cells, etc. Since it is a stand-alone application it has been moved to the folder applications/spready' of the Lazarus Components and Code Library.

Download

Subversion

You can download FPSpreadsheet using the subversion software and the following command line:

svn checkout svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet

SVN change log

  • Hide (and unhide) rows and columns

Incompatible changes

  • (nothing yet)

Стабильные версии

You can find all releases of FPSpreadsheet on sourceforge.


Version 1.10.1

This is the latest stable release.

Change log (with respect to v1.8x)
  • Workbook, worksheet and cell protection (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).
  • New package laz_fpspreadsheet_crypto to decipher the encryption for worksheet-protection in xls files. Requires DCPcrypt.
  • TsWorksheetGrid can display embedded images.
  • Drag and drop in TsWorksheetGrid
  • New, highDPI-aware component palette icons.
  • New workbook optiond boAbortReadingOnFormulaError and boIgnoreFormulas.
  • Formulas with references to other sheets, i.e. '=Sheet1!A1+Sheet2!A2'
Incompatible changes
  • The field FormulaValue has been removed from the cell record. The cell formula now can be retrieved by calling Worksheet.ReadFormula(cell).

Version 1.8

Change log (with respect to v1.6x)
  • "Rich-text" formatting of label cells, i.e. assignment different fonts to groups of characters within the cell text. For this purpose, HTML codes (such as <B>...</B>) can be embedded in the cell text to identify the parts with different font (--> TsWorksheet.WriteTextAsHTML).
  • Searching for cells with specified content in worksheet or workbook.
  • Support for reading and writing of HTML format
  • Support for writing of the ExcelXML format (Excel XP and 2003)
  • Ability to use user-provided file reader/writer classes to extend FPSpreadsheet to new file formats.
  • Readers and writers now support all the line styles of Excel8 and OOXML.
  • xls, xlsx and ods readers/writers now support the active worksheet and selected cell.
  • Ability to write to/read from the system's clipboard for copy & paste using the visual controls.
  • Support for print ranges and repeated header rows and columns in the Office applications.
  • Support for embedded images (currently only writing to xlsx and ods, no reading).
  • Improved compatibility of TsWorksheetGrid with TStringGrid (Cells[Col,Row] property). Standalone application as an advanced StringGrid replacement.
  • Support for Right-to-left mode in TsWorksheetGrid. In addition to the system-wide RTL mode, there are also parameters BiDiMode in the Worksheet and cells allowing to controls text direction at worksheet and cell level individually, like in Excel or LibreOffice Calc.
  • Support of several units for specification of column widths and row heights.
  • The library now supports localization using po files. Translations are welcome.
  • Zoom factor read and written by the worksheet, and applied by the TsWorksheetGrid.
  • Support of column and row formats
  • Support of hidden worksheets
Incompatible changes
  • VirtualMode was changed in order to be able to treat worksheets of the same workbook differently. VirtualRowCount and VirtualColCount are now properties of the worksheet, and similarly, the event handler OnWriteCellData. In older versions, these properties had belonged to the workbook.
  • The worksheet methods ReadAsUTF8Text and WriteUTF8Text have been renamed to ReadAsText and WriteText, respectively. The old ones are still available and marked as deprecated; they will be removed in later versions.
  • The public properties of TsWorksheetGrid using a TRect as parameter were modified to use the Left, Top, Right, Bottom values separately.
  • The PageLayout is a class now, no longer a record. As a consequence, some array properties cannot be set directly any more, use the corresponding methods instead.
  • Most of the predefined color constants were marked as deprecated; only the basic EGA colors will remain.
  • Unit fpsNumFormatParser is integrated in fpsNumFormat. Old code which "uses" fpsNumFormatParser must "use" fpsNumFormat now.
  • The source files of the laz_fpspreadsheet, laz_fpspreadsheet_visual and laz_fpspreadsheetexport_visual packages have been moved to separate folders in order to resolve some occasional compilation issues. Projects which do not use the packages but the path to the sources must adapt the paths.


Version 1.6

Change log (with respect to v1.4.x)
  • TsWorkbookChartSource is a new component which facilitates creation of charts from non-contiguous spreadsheet data in various worksheets. It interfaces to a workbook via tha WorkbookSource component. In the long run, it will replace the older TsWorksheetChartSource which required contiguous x/y data blocks in the same worksheet.
  • Major reconstruction of the cell record resulting in strong reduction of memory consumption per cell (from about 160 bytes per cell down to about 50)
  • Implementation of a record helper for the TCell which simplifies cell formatting (no need to set a bit in UsedFormattingFields any more, automatic notification of visual controls)
  • Comments in cells
  • Background fill patterns
  • Hyperlinks
  • Enumerators for worksheet's internal AVLTrees for faster iteration using a for-in loop.
  • Formatting of numbers as fractions.
  • Improved number format parser for better recognition of Excel-like number formats.
  • Page layout (page margins, headers, footer, used for only when printing in the Office applications - no direct print support in fpspreadsheet!)
  • Improved color management: no more palettes, but direct rgb colors. More pre-defined colors.
  • A snapshot of the wiki documentation is added to the library as chm help file.
Incompatible changes
  • All type declarations and constants are moved from fpspreadsheet.pas to the new unit fpstypes.pas. Therefore, most probably, this unit has to be added to the uses clause.
  • Because fpspreadsheet supports now background fill patterns the cell property BackgroundColor has been replaced by Background. Similarly, the UsedFormattingFields flag uffBackgroundColor is called uffBackground now.
  • Another UsedFormattingFields flag has been dropped: uffBold. It is from the early days of fpspreadsheet and has become obsolete since the introduction of full font support. For achieving a bold type-face, now call MyWorksheet.WriteFont(row, col, BOLD_FONTINDEX), or Myworksheet.WriteFontStyle(row, col, [fssBold]).
  • Iteration through cells using the worksheet methods GetFirstCell and GetNextCell has been removed - it failed if another iteration of this kind was called within the loop. Use the new for-in syntax instead.
  • Support for shared formulas has been reduced. The field SharedFormulaBase has been deleted from the TCell record, and methods related to shared formulas have been removed from TsWorksheet. Files containing shared formulas can still be read, the shared formulas are converted to multiple normal formulas.
  • The color palettes of previous versions have been abandoned. TsColor is now a DWord representing the rgb components of a color (just like TColor does in the graphics unit), it is not an index into a color palette any more. The values of pre-defined colors, therefore, have changed, their names, however, are still existing. The workbook functions for palette access have become obsolete and were removed.

Version 1.4

Change log (with respect to v1.2.x)
  • Full support for string formulas; calculation of RPN and string formulas for all built-in formulas either directly or by means of registration mechanism. Calculation occurs when a workbook is saved (activate workbook option boCalcBeforeSaving) or when cell content changes (active workbook option boAutoCalc).
  • Shared formulas (reading for sfExcel5, sfExcel8, sfOOXML; writing for sfExcel2, sfExcel5, sfExcel8).
  • Significant speed-up of writing of large spreadsheets for the xml-based formats (ods and xlsx), speed up for biff2; speedtest demo program
  • VirtualMode allowing to read and write very large spreadsheet files without loading entire document representation into memory. Formatting of cells in VirtualMode.
  • Demo program for database export using virtual mode and TFPSExport.
  • Added db export unit allowing programmatic exporting datasets using TFPSExport. Similar export units are e.g. fpdbfexport, fpXMLXSDExport.
  • Reader for xlsx files, now fully supporting the same features as the other readers.
  • Reader/writer for CSV files based on CsvDocument.
  • Wikitables writer supports now most of the fpspreadsheet formatting options (background color, font style, font color, text alignment, cell borders/line styles/line colors, merged cells, column widths, row heights); new "wikitablemaker" demo
  • Insertion and deletion of rows and columns into a worksheet containing data.
  • Implementation of sorting of a worksheet.
  • Support of diagonal "border" lines
  • Logging of non-fatal error messages during reading/writing (TsWorksheet.ErrorMsg)
  • Merged cells
  • Registration of currency strings for automatic conversion of strings to currency values
  • A set of visual controls (TsWorkbookSource, TsWorkbookTabControl, TsSpreadsheetInspector, TsCellEdit, TsCellIndicator, TsCellCombobox, in addition to the already-existing TsWorksheetGrid) and pre-defined standard actions to facilitate creation of GUI applications.
  • Overflow cells in TsWorksheetGrid: label cells with text longer than the cell width extend into the neighboring cell(s).
Incompatible changes
  • The option soCalcBeforeSaving now belongs to the workbook, no longer to the worksheet, and has been renamed to boCalcBeforeSaving (it controls automatic calculation of formulas when a workbook is saved).
  • The workbook property ReadFormulas is replaced by the option flag boReadFormulas. This means that you have to add this flag to the workbook's Options in order to activate reading of formulas.
  • With full support of string formulas some features related to RPN formulas were removed:
    • The field RPNFormulaResult of TCell was dropped, as well as the element cctRPNFormula in the TsContentType set.
    • Sheet function identifiers were removed from the TsFormulaElement set, which was truncated after fekParen.
    • To identify a sheet function, its name must be passed to the function RPNFunc (instead of using the now removed fekXXXX token). In the array notation of the RPN formula, a sheet function is identified by the new token fekFunc.
    • The calling convention for registering user-defined functions was modified. It now also requires the Excel ID of the function (see "OpenOffice Documentation of Microsoft Excel Files", section 3.11, or unit xlsconst containing all token up to ID 200 and some above).
    • Code related to RPN formulas was moved to a separate unit, fpsRPN. Add this unit to the uses clause if you need RPN features.

Wiki documentation of old releases

This wiki page is work in progress and updated whenever a new feature is added; therefore, its state corresponds to the svn trunk version of the package. If you work with an older stable version please use these "historic" wiki versions:

Installation

  • If you only need non-GUI components: in Lazarus: Package/Open Package File, select laz_fpspreadsheet.lpk, click Compile. Now the package is known to Lazarus (and should e.g. show up in Package/Package Links). Now you can add a dependency on laz_fpspreadsheet in your project options and fpspreadsheet to the uses clause of the project units that need to use it.
  • If you also want GUI components (TsWorksheetGrid and TsWorksheetChartSource): Package/Open Package File, select laz_fpspreadsheet_visual.lpk, click Compile, then click Use, Install and follow the prompts to rebuild Lazarus with the new package. Drop needed grid/chart components on your forms as usual.
  • If you want to have a GUI component for dataset export: Package/Open Package File, select laz_fpspreadsheetexport_visual.lpk, click Compile, then click, Use, Install and follow the prompts to rebuild Lazarus with the new package. Drop needed export components from the Data Export tab on your forms as usual.
  • FPSpreadsheet is developed with the latest stable fpc version (currently fpc 3.0.2). We only occasionally check older versions.
  • The basic spreadsheet functionality works with Lazarus versions back to version 1.0. Some visual controls or demo programs, however, require newer versions. Please update your Lazarus if you have an older version and experience problems.

Compiler options

Here is a list of conditional defines which can be activated in order to tweak some operating modes of the packages and/or make it compilable with older Lazarus/FPC versions:

  • FPS_DONT_USE_CLOCALE: In Unix systems, the unit clocale is automatically added to the uses clause of fpspreadsheet.pas. This unit sets up localization settings needed for locale-dependent number and date/time formats. However, this adds a dependence on the C library to the package [2]. If this is not wanted, define FPS_DONT_USE_CLOCALE.
  • FPS_VARISBOOL: fpspreadsheet requires the function VarIsBool which was introduced by fpc 2.6.4. If an older FPC versions is used define FPS_VARISBOOL. Keep undefined for the current FPC version.
  • FPS_LAZUTF8: fpspreadsheet requires some functions from the unit LazUTF8 which were introduced by Lazarus 1.2. If an older Lazarus version is used define FPS_LAZUTF8. Keep undefined for the current Lazarus version.

All these defines are collected in the include file fps.inc.

Support and Bug Reporting

The recommended place to discuss FPSpreadsheet and obtain support is asking in the Lazarus Forum.

Bug reports should be sent to the Lazarus/Free Pascal Bug Tracker; please specify the "Lazarus-CCR" project.

Current Progress

Progress by supported cell content

Format Multiple
sheets
Unicode Reader
support
Writer
support
Text Number String
Formula
RPN
Formula
3D cell
references
Date/Time Comments Hyperlinks Images +++ Protection
CSV files No Yes + Working ++ Working ++ Working ++ Working ++ N/A N/A N/A Working ++ N/A N/A N/A N/A
Excel 2.x No No * Working ** Working Working Working Working Working *** N/A Working **** Working N/A N/A Working
Excel 5.0 (Excel 5.0 and 95) Yes No * Working ** Working Working Working Working Working *** Working Working **** Working N/A N/A Working
Excel 8.0 (Excel 97- 2003) Yes Yes Working ** Working Working Working Working Working *** Working Working **** Reading only Working Not working Working
Excel OOXML Yes Yes Working ** Working Working Working Working *** Working Working Working **** Working Working Writing only Working
OpenDocument Yes Yes Working ** Working Working Working Working *** Working Working Working **** Working Working Working Working
HTML No Yes Working ++ Working ++ Working ++ Working ++ N/A N/A N/A Working ++ N/A Working Not working N/A
Wikitable files (Mediawiki) No Yes planned Working ++ Working ++ Working ++ N/A N/A N/A Working ++ N/A N/A Not working N/A

(+) Depends on file.
(++) No "true" number format support because the file does not containg number formatting information. But the number format currently used in the spreadsheet understood.
(+++) Only very basic image support: no transformations, no cropping, no image manipulation.
(*) In formats which don't support Unicode the data is stored by default as ISO 8859-1 (Latin 1). You can change the encoding in TsWorkbook.Encoding. Note that FPSpreadsheet offers UTF-8 read and write routines, but the data might be converted to ISO when reading or writing to the disk. Be careful that characters which don't fit selected encoding will be lost in those operations. The remarks here are only valid for formats which don't support Unicode.
(**) Some cell could be returned blank due to missing or non ready implemented number and text formats.
(***) This is the format in which the formulas are written to file (determined by design of the file format).
(****) Writing of all formats is supported. Some rare custom formats, however, may not be recognized correctly. BIFF2 supports only built-in formats by design.

Progress of the formatting options

Quite some formatting options are available:

Format Text
alignment
Text
rotation
    Font     Rich
text
  Border   Color
support
Back
ground
Word
wrap
Col&Row
size
Number
format
Merged
cells
Page
layout
Print
ranges
Header/footer
images
Column/row
format
Hide
cols/rows
CSV files N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A
Excel 2.x Working * N/A Working N/A Working Working Working** N/A Working Working N/A Working N/A N/A Working N/A
Excel 5.0
(Excel 5.0 and 95)
Working Working Working Working Working Working Working Working Working Working N/A Working Working N/A Working Working
Excel 8.0
(Excel 97 - XP)
Working Working Working Working Working Working Working Working Working Working Working Working Working Not working Working Working
Excel OOXML (xlsx) Working Working Working Working Working Working Working Working Working Working Working Working Working Writing only Working Working
OpenDocument Working Working Working Working Working Working Working*** Working Working Working Working Working Working Working Working Working
HTML (+) Working bugs Working Working bugs Working Working **** Writing only Writing only N/A Working N/A N/A Not working Writing only to be done
Wikitable (Mediawiki) Writing only N/A Writing only Not working Writing only Writing only Writing only Writing only N/A Writing only N/A N/A Not working Writing only to be done

(N/A) Feature is not available for this format intrinsically.
(*) BIFF2 supports only horizontal text alignment, vertical alignment is ignored.
(**) BIFF2 does not support a background color; a dotted black&white background is used instead.
(***) OpenDocument supports only uniform backgrounds; a fill color interpolated between foreground and background colors is written instead.
(****) Only uniform background color, no fill styles.
(+) HTML reader does not support styles. Since the writer does use styles these files are not read back correctly.

Progress of workbook/worksheet user-interface options

Some additional options were added to interface the file contents with the TsWorksheetGrid:

Format Hide grid lines Hide headers Frozen Panes Active sheet/cell Zooming BiDi mode
Excel 2.x Working Working not working N/A N/A N/A
Excel 5.0 (Excel 5.0 and 95) Working Working Working Working Working N/A
Excel 8.0 (Excel 97 - XP) Working Working Working Working Working Working
Excel OOXML Working Working Working Working Working Working
OpenDocument Working Working Working Working Working Working
HTML Writing only Writing only N/A N/A N/A not working

To do list

Note: this list is provisional, maintained by developers and can change without notice. If you are interested in contributing, please feel free to get in touch and submit a patch - thanks!

  • Find out why BIFF2 files are corrupt when saved with frozen rows/cols activated.
  • Add row and column formats
  • Add reading support for wikitable (Mediawiki) files
  • xls reader crashes for some incorrectly written xls files (which Excel can read), see http://forum.lazarus.freepascal.org/index.php/topic,25624.0.html.
  • Improve color support: due to palette support colors may change from file to file currently.
  • Add embedded images.
  • Fix writing of cell comments to BIFF8 files.

Long-term:

  • Provide a more common user interface to fpspreadsheet (setter/getter and properties instead of Read*/Write* methods, MyWorksheet.Cells[ARow, ACol]), make TCell a class, avoid the pointers PCell.
  • Store formatting in a format list of the workbook, not in the cell, to reduce memory usage.
  • Use BIFF8 file-wide string storage instead of storing strings in cells (should reduce memory usage in case of many duplicate strings)
  • Redo ooxml and ods readers based upon SAX/TXmlReader instead of DOM to reduce memory usage of large files.
  • Add an fpspreadsheetexport unit and component on "Data Export" tab similar to fpdbfexport FPSpreadsheetexport demo preferably with all export formats component. Find a way to register export format to all formats (look into how lazreport pdf export performs a similar procedure).

Changelog

Changes in the development version that have not yet been released are already documented on this page.

  • 13 August 2018:
    Version 1.10.1 bug fix release, based on revision 6598.
  • 6 July 2018:
    Version 1.10 release, based on svn revision 6547.
  • 16 June 2017:
    Version 1.8.3. Fixes compilation with Lazarus 1.8. New, highDPI-aware component palette icons
  • 11 April 2017:
    Version 1.8.2, based on svn revision 5832. Fixes writing distorted images due to incorrect row height calculation.
  • 2 March 2017:
    Version 1.8, based on svn revision 5781.
  • 13 April 2016:
    Version 1.6.2 released, based on svn revision 4619. Fixes integer overflow in formulas (Issue #29999).
  • 28 June 2015:
    Version 1.6 released, based on svn release 4106. Improved memory consumption, comments, hyperlinks, background fill patterns, cell enumerator, fraction number format, page layout, modified color management, offline wiki version included.
  • 13 March 2015:
    Version 1.4.3 released. Fixes formula calculation in worksheet grid.
  • 31 January 2015:
    Version 1.4.2 released. Fixes incomplete reading of ods files with repeated cells (Issue #0027389)
  • 26 January 2015:
    Version 1.4.1 released. A bugfix release for 1.4 that fixes incorrect streaming of TsWorksheetGrid and reading errors of biff5 and biff8 readers.
  • 31 December 2014:
    Version 1.4 released (based on subversion revision 3856 with full formula support, xlsx reader, csv reader and writer, wikitables writer, speed enhancement, virtual mode, db export component, row and column insertion/deletion, sorting, merged cells, visual controls in addition to (improved) fpspreadsheet grid.
  • 3 September 2014:
    Version 1.2.1 released. This is a bugfix release for 1.2 that fixes a bug in spreadsheetGrid (Issue #26521)
  • 26 June 2014:
    Version 1.2 released (based on subversion revision 3241) with fpsgrid improvements, date/time cell support for .xls, .ods, vastly improved cell formatting support, improved formula support, unit tests.
  • Jan 2013:
    Version 1.1 released with all improvements up to revision 2621
  • Nov 2011:
    Released version 1.0, which includes OO XML generation support with bold formatting supported and also a lot of improvements from the last years
  • Jan 2009:
    Implemented a cross-platform support for OLE file. Now Excel 5.0 files can be created in any operating system. Adds read support for Excel 2.1
  • Feb 2008:
    Initial commit to lazarus-ccr with write support for Excel 2.1, Excel 5.0 (Windows only) and experimental OOXML and OpenDocument

License

LGPL with static linking exception. This is the same license as is used in the Lazarus Component Library.

Referenes

Wiki links

External Links