Sign in Join
Wiki copytemporary

FPSpreadsheet/pl

Deutsch · English · Español · Français · Polski

Warning: Ta strona została przetłumaczona w dniu 2021-01-27 i może nie być aktualna. Dlatego skorzystaj także z witryny angielskiej

Biblioteka fpSpreadsheet oferuje wygodny sposób generowania i czytania arkuszy kalkulacyjnych w różnych formatach. Biblioteka jest napisana w bardzo elastyczny sposób, z możliwością łatwego rozszerzenia do obsługi dowolnej liczby formatów.

Zrzut ekranu spready demo programu dostarczonego z fpspreadsheet przedstawiający plik XLS:

Note: Możesz także pobrać program spready demo za pomocą komendy: svn checkout http://svn.code.sf.net/p/lazarus-ccr/svn/applications/spready

Dokumentacja

Ta strona wiki zawiera najnowszą wersję development/trunk FPSpreadsheet dostępną za pośrednictwem subversion. Zobacz sekcję Wersje stabilne aby zapoznać się z dokumentacją dotyczącą stabilnej wersji, którą możesz także pobrać.

API Documentation

Dokumentacja API

Plik pomocy w formacie CHM można znaleźć w folderze docs pakietu instalacyjnego FPSpreadsheet. Jeśli jeszcze nie zainstalowałeś pakietu, wykonaj następujące czynności: http://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet/docs/fpspreadsheet-api.chm aby pobrać fpspreadsheet-api.chm.

Drugi plik CHM dostępny w folderze docs, fpspreadsheet-wiki.chm, to migawka stron wiki związanych z FPSpreadsheet, spakowana w pojedynczy plik pomocy.

Podstawy

Najmniejsze elementy w arkuszu kalkulacyjnym to cells (komórki) zawierające dane. Komórki mogą zawierać różne typy danych, takie jak ciągi, liczby, daty, godziny, wartości logiczne lub formuły. Ponadto komórki mogą zawierać informacje o formatowaniu, tj. Styl czcionki, kolor tła, wyrównanie tekstu itp.

Komórki są ułożone w strukturę przypominającą siatkę, zwaną worksheet (arkuszem roboczym) lub spreadsheet (arkuszem kalkulacyjnym), składającą się z rows (wierszy) i columns (kolumn). Każda komórka ma unikalny adres podany w indeksie wiersza i kolumny.

Arkusze są połączone w workbook (skoroszyt), który reprezentuje dokument aplikacji arkusza kalkulacyjnego. Skoroszyt przechowuje również informacje, które są potrzebne dla wszystkich arkuszy roboczych, tj. lista czcionek, listy formatu komórek i liczb itp.

FPSpreadsheet ma tę samą strukturę - zawiera TCell, TsWorksheet i TsWorkbook.

Skoroszyt

Klasa TsWorkbook jest główną klasą widoczną dla użytkownika. Zapewnia metody odczytu danych i zapisywania do plików. Wszechstronna struktura biblioteki zapewnia dostęp do różnych popularnych formatów plików, takich jak Excel .xls lub .xlsx, a także OpenOffice/LibreOffice .ods.

Format pliku jest określony przez typ TsSpreadsheetFormat zdefiniowany w module fpstypes

pascal
type
  TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML, 
    sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);

gdzie:

Podczas stosowania FPSpreadsheet pierwszym zadaniem jest utworzenie instancji skoroszytu:

pascal
var
  MyWorkbook: TsWorkbook;
begin
  MyWorkbook := TsWorkbook.Create;
  ...

Czytanie plików arkuszy kalkulacyjnych odbywa się (między innymi) metodami skoroszytowymi

Do zapisywania do pliku można użyć następujących metod skoroszytu:

Po wywołaniu tych metod warto przyjrzeć się właściwości ErrorMsg skoroszytu, w której gromadzone są komunikaty z powodu błędów lub ostrzeżeń, które mogły wystąpić podczas odczytu/zapisu. Ta właściwość zwraca wielowierszowy ciąg, który jest najlepiej wyświetlany w komponencie memo; jeśli wszystko było w porządku, jest to pusty ciąg.

Uwaga: PSpreadsheets zapewnia wyspecjalizowane moduły do odczytu i zapisu w każdym formacie pliku. Te moduły nie są dostępne automatycznie, musisz je jawnie dodać do klauzuli uses. FPSpreadsheet zgłosi „nieobsługiwany format pliku”, jeśli nie zostanie znaleziony żaden pasujący moduł do odczytu/zapisu. Oto lista nazw takich modułów:

Arkusz roboczy

Skoroszyt zawiera listę instancji TsWorksheet. Odpowiadają one zakładkom, które widzisz w programie Excel lub Open/LibreOffice. Podczas czytania pliku arkusza kalkulacyjnego poszczególne arkusze są tworzone automatycznie zgodnie z zawartością pliku. Kiedy arkusz kalkulacyjny jest tworzony ręcznie, aby zapisać go w pliku, arkusz roboczy musi zostać utworzony przez dodanie go do skoroszytu:

pascal
var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My_Table');  
    // 'My_Table' to „nazwa” arkusza
  ...

Dostęp do już istniejących arkuszy roboczych można uzyskać przy użyciu metod TsWorkbook

Liczbę już istniejących arkuszy można sprawdzić wywołując metodę GetWorksheetCount.

Komórka

Wreszcie arkusz roboczy zapewnia dostęp do komórek. Nowo utworzony arkusz, jak w powyższym przykładzie, jest pusty i nie zawiera żadnych komórek. Komórki są dodawane przez przypisanie do nich danych lub atrybutów za pomocą jednej z metod WriteXXXX arkusza. Jak już wspomniano, komórka jest adresowana przez indeks wiersza i kolumny, do których należy. Jak zwykle, indeksy wierszy i kolumn zaczynają się od 0. W związku z tym komórka „A1” należy do wiersza 0 i kolumny 0. Należy zauważyć, że indeksy wierszy i kolumn są zawsze podawane w tej kolejności, która różni się od konwencji stosowanej w TStringGrid. Poniższy przykład tworzy komórkę pod adresem A1 i umieszcza w niej liczbę 1.0.

pascal
var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
  MyWorksheet.WriteNumber(0, 0, 1.0);  //„A1” ma rząd=0 i kolumnę=0
  ...

Możliwy jest również bezpośredni dostęp do komórek za pomocą metod skoroszytu FindCell(ARow, ACol) lub GetCell(ARow, ACol). Obie funkcje istnieją również w wersji przeciążonej, do której można przekazać adres komórki w notacji Excela: FindCell(ACellStr: String) lub GetCell(ACellStr: String). Należy pamiętać, że te funkcje zwracają wskaźnik pointer do danej komórki (typ PCell). Nie zapomnij odwołać się do wskaźników! Różnica między FindCell i GetCell polega na tym, że pierwsza zwraca wartość nil, jeśli komórka jeszcze nie istnieje, a druga tworzy pustą komórkę w tym przypadku.

pascal
if MyWorksheet.FindCell('A1') = nil then
  WriteLn('Komórka A1 nie istnieje.');
Podstawowy rekord TCell

Oto deklaracja typu danych komórki:

pascal
type
  TCell = record
    { Lokalizacja komórki }
    Worksheet: TsWorksheet;
    Col: Cardinal; // od zera
    Row: Cardinal; // od zera

    { Indeks formatu zapisu }
    FormatIndex: Integer;

    { Flagi stanu }
    Flags: TsCellFlags;        // (cfHasComment, cfMerged, cfHyperlink, ...)

    { Zawartość komórki }
    UTF8StringValue: String;   // ciąg nie może być częścią rekordu wariantowego
    case ContentType: TCellContentType of  // który musi znajdować się na końcu deklaracji
      cctEmpty      : ();      // nie ma żadnych danych
      cctFormula    : ();      // UTF8StringValue znajduje się poza rekordem wariantowym
      cctNumber     : (Numbervalue: Double);
      cctUTF8String : ();      // FormulaValue znajduje się poza rekordem wariantowym
      cctDateTime   : (DateTimevalue: TDateTime);
      cctBool       : (BoolValue: boolean);
      cctError      : (ErrorValue: TsErrorValue);
  end;
  PCell = ^TCell;

Pole ContentType wskazuje, jaki typ danych jest przechowywany w komórce:

pascal
type
  TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);

Zgodnie z tym polem odpowiednie dane można znaleźć w polach

Ze względu na użycie rekordu wariantu większość z tych wartości zachodzi na siebie, tj. modyfikacja NumberValue wpływa również na inne wartości. Dlatego zawsze bierz pod uwagę wartość ContentType podczas bezpośredniego dostępu do rekordu TCell (metody arkusza omówione poniżej uwzględniają to automatycznie).

Pole Flags mówi, czy z komórką są powiązane dodatkowe dane, które nie są zawarte w rekordzie komórki, zwykle w celu zaoszczędzenia pamięci:

pascal
type 
  TsCellFlag = (cfHasComment, cfHyperlink, cfMerged, cfHasFormula, cf3dFormula);
  TsCellFlags = set of TsCellFlag;

Note: Po obliczeniu formuły lub po odczytaniu pliku, formuła komórki w ContentType jest konwertowana na wynik. Wówczas obecność formuły można wykryć tylko przez wywołanie funkcji HasFormula(cell) dla odpytywanej komórki (cell: PCell) w module fpsUtils; ta funkcja sprawdza obecność elementu cfHasFormula we flagach komórki.

Jak dodawać i czytać dane do/z komórki

Dodawanie wartości do komórki jest najłatwiejsze przy użyciu jednej z metod arkusza WriteXXXX. Najważniejsze z nich to:

pascal
type
  TsWorksheet = class
    ...
    { Zapisywanie wartości walutowych }
    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;

    { Zapisywanie wartości daty/czasu }
    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;

    { Zapisywanie wartości liczbowych } 
    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;

    { Zapisywanie ciągów typu string }
    function WriteText(ARow, ACol: Cardinal; AText: ansistring;
      ARichTextParams: TsRichTextParams = nil): PCell; overload;
    procedure WriteText(ACell: PCell; AText: String;
      ARichTextparams: TsRichTextParams = nil); overload;

    // stare metody typu string, jak "WriteUTF8Text" są teraz przestarzałe
    ...

Niektóre z tych metod istnieją w przeciążonych wersjach, w których parametry formatowania komórki można dodać razem z wartością komórki. Odpowiednio jak do zapisu, istnieje również kilka metod arkusza worksheet do odczytu wartości komórek:

pascal
type
  TsWorksheet = class
    ...
    { Odczytywanie zawartości komórki jako ciągu }
    function  ReadAsText(ARow, ACol: Cardinal): string; overload;
    function  ReadAsText(ACell: PCell): string; overload;
    function  ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;

    { Odczytywanie zawartości komórki jako liczby }
    function  ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
    function  ReadAsNumber(ACell: PCell): Double; overload;
    function  ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;

    { Odczytywanie zawartości komórki jako wartości daty/czasu }
    function  ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
    function  ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
    ...
Indeks do formatu komórki

FormatIndex jest to indeks rekordu formatu komórki. Opisuje on atrybuty formatowania komórki. Te rekordy są zbierane przez wewnętrzną listę skoroszytu i są zdefiniowane w następujący sposób:

pascal
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 {kierunek pisania od lewej do prawej}, bdRTL {od prawej do lewej)
    Protection: TsCellProtection;   // cpLockCell, cpHideFormulas
    UsedFormattingFields: TsUsedFormattingFields;
      //uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign, uffBiDi
  end;

Aby określić format dla danej komórki, wywołaj odpowiednią metodę arkusza WriteXXXX, aby pobrać format wywołaj ReadXXXX. Te metody zwykle pobierają wskaźnik do komórki jako parametr, ale istnieją również przeciążone wersje, które akceptują indeks wierszy i kolumn. Ponadto style formatowania można również zastosować bezpośrednio do komórki za pomocą rekordu pomocniczego zaimplementowanego w module fpsCell.

Aby uzyskać bardziej szczegółowy opis, zobacz formatowanie komórek poniżej.

Kolumny i wiersze

Rekordy kolumn i wierszy są dodawane do każdej kolumny i wiersza o właściwościach innych niż domyślne:

pascal
type
  TCol = record
    Col: Cardinal;
    Width: Single;
    ColWidthType: TsColWidthType;     // = (cwtDefault, cwtCustom)
    FormatIndex: Integer;
    Options: TsColRowOptions;         // set of [croHidden, croPageBreak]
  end;
  PCol = ^TCol;

  TRow = record
    Row: Cardinal;
    Height: Single;
    RowHeightType: TsRowHeightType;   // = (rhtDefault, rhtCustom, rhtAuto)
    FormatIndex: Integer;
    Options: TsColRowOptions;         // set of [croHidden, croPageBreak]
    Hidden: Boolean;
    PageBreak: Boolean;
  end;
  PRow = ^TRow;
Szerokość kolumny / wysokość wiersza

Szerokość kolumn i Wysokość wierszy można określić w różnych jednostkach zdefiniowanych przez typ TsSizeUnits = (suChars, suLines, suMillimeters, suCentimeters, suPoints, suInches). suChars odnosi się do znaków liczby 0 mieszczących się w szerokości kolumny - w ten sposób Excel definiuje szerokości kolumn. suLines to liczba linii tekstu mieszczących się na wysokości wiersza. Obie jednostki są oparte na rozmiarze znaku domyślnej czcionki skoroszytu. Pozostałe jednostki to konwencjonalne jednostki długości fizycznej (1 cm = 10 mm, 1 cal = 25,4 mm = 72 punkty). Akceptowane są wartości ułamkowe. Skoroszyt i arkusze robocze przechowują wewnętrznie długości w milimetrach (MyWorkbook.Units).

Aplikacje pakietu Office zazwyczaj automatycznie dostosowują wysokość wierszy zgodnie z czcionką lub rotacją tekstu w zawartości komórki. Ten przypadek jest identyfikowany przez RowHeightType o wartości rhtAuto. Ponieważ arkusz nie może bardzo dokładnie obliczyć rozmiaru tekstu, automatyczne wysokości wierszy nie są zapisywane przez FPSpreadsheet; są one zastępowane przez domyślną wysokość wiersza. Domyślna wysokość wiersza jest również używana, jeśli wiersz jest pusty, tj. gdy nie zawiera żadnych komórek danych. Jej wartość można zmienić, wywołując metodę WriteDefaultRowHeight() arkusza lub używając właściwości arkusza DefaultRowHeight. W WriteDefaultRowHeight() jednostki muszą być określone, podczas gdy w DefaultRowHeight zakłada się, że są to linie. Podobnie, domyślną szerokość kolumny można określić za pomocą funkcji WriteDefaultColWidth() lub właściwości DefaultColWidth (w znakach).

W celu przekroczenia automatycznych i domyślnych wysokości wierszy wywołaj metodę arkusza WriteRowHeight(). Te rekordy wierszy są identyfikowane przez RowHeightType o wartości rhtCustom. W ten sam sposób szerokość kolumn można ustawić na określoną wartość, wywołując WriteColWidth(). ColWidthType tych kolumn to cwtCustom.

Wysokość/szerokość konkretnego wiersza/kolumny można pobrać za pomocą metod GetRowHeight lub GetColWidth. Zwróć uwagę, że te metody zwracają domyślne wysokości wierszy/szerokości kolumn, jeśli nie ma rekordów TRow/TCol.

pascal
type TsWorksheet = class
  ...
  { Ustaw wysokość wiersza }
  procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
  { Ustaw szerokość kolumny }
  procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
  { Ustaw domyślną wysokość wiersza }
  procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
  { Ustaw domyślną szerokość kolumny }
  procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);

  { Zwróć wysokość wiersza }
  function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
  { Zwróć szerokość kolumny }
  function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
  { Zwróć domyślną wysokość wiersza }
  function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
  { Zwróć domyślną szerokość kolumny }
  function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;

  property DefaultRowHeight: Single;  // w liniach tekstu
  property DefaultColWidht: Single;   // w liczbie znaków

Istnieją również przeciążone wersje tych metod, które nie wymagają parametru AUnits. W tym przypadku wysokości wierszy są określane jako liczba linii tekstu, a szerokości kolumn są określane jako liczba znaków. Zauważ, że te warianty pochodzą z poprzednich wersji i są teraz przestarzałe.

Formaty kolumn i wierszy

Element FormatIndex formatu rekordów wierszy i kolumn ma zastosowanie do całego wiersza lub całej kolumny. Razem z komórkami te formaty są przechowywane jako rekordy TsCellFormat na wewnętrznej liście skoroszytów. FormatIndex jest indeksem właściwości formatu na tej liście. Zobacz szczegóły w sekcji Formatowanie komórek. Formaty wierszy i kolumn są stosowane głównie do pustych komórek, ale po dodaniu nowej komórki automatycznie otrzyma ona format wiersza lub kolumny. (Jeśli zarówno wiersz, jak i kolumna mają różne formaty, zostanie użyty format wiersza).

Tutaj jest lista metod arkusza dostępna dla formatowania kolumn i wierszy:

pascal
type TsWorksheet = class
  // Przypisuje format do kolumny lub wiersza
  procedure WriteColFormatIndex(ACol: Cardinal; AFormatIndex: Integer);
  procedure WriteRowFormatIndex(ARow: Cardinal; AFormatIndex: Integer);

  // Odczytuje format kolumny lub wiersza
  function  GetColFormatIndex(ACol: Cardinal): Integer;
  function  GetRowFormatIndex(ARow: Cardinal): Integer;

  // Zwraca czcionkę przypisaną do kolumny lub wiersza
  function  ReadColFont(ACol: PCol): TsFont;
  function  ReadRowFont(ARow: PRow): TsFont; 

  // Sprawdza, czy jakakolwiek kolumna lub wiersz ma specjalny format
  function  HasRowFormats: Boolean; 
  function  HasColFormats: Boolean;
Hidden columns / rows

Adding the flag croHidden to the row's or column's Options hides the row or column in the Office application (or in TsWorksheetGrid). The following worksheet methods are helpers to handle row/column visibility:

pascal
type TsWorksheet = class
  ...
  { Hide column/row }
  procedure HideCol(ACol: Cardinal);
  procedure HideRow(ARow: Cardinal);

  { Show a previously hidden column/row }
  procedure ShowCol(ACol: Cardinal);
  procedure ShowRow(ARow: Cardinal);

  { Check whether column/row is hidden }
  function  ColHidden(ACol: Cardinal): Boolean;
  function  RowHidden(ARow: Cardinal): Boolean;
Page breaks

The flag croPageBreak can be added to the row's or column's Options in order to force a page break before the corresponding row or column when the worksheet is printed by the Office applications. Note that FPSpreadsheet itself does not support printing. The following worksheet methods help with page breaks:

pascal
type TsWorksheet = class
  ...
  { Enforce a page break before the specified column/row }
  procedure AddPageBreakToCol(ACol: Cardinal);
  procedure AddPageBreakToRow(ARow: Cardinal);

  { Removes a page break previously added to a column/row }
  procedure RemovePageBreakFromCol(ACol: Cardinal);
  procedure RemovePageBreakFromRow(ARow: Cardinal);

  { Checks whether a page break is forced before the specified column/row }
  function IsPageBreakCol(ACol: Cardinal): Boolean;
  function IsPageBreakRow(ARow: Cardinal): Boolean;

Events

Worksheets and workbooks fire a series of events such as OnChangeCell, OnChangeFont, etc. The events usually are intended for interaction with the visual spreadsheet controls. If you need to attach your own handlers you should make sure that the original handler is called, at least in a gui program using the spreadsheet controls. Or you use the events provided by the visual controls themselves, e.g. WorksheetGrid.OnEditingDone instead of WorksheetGrid.Worksheet.OnChangeCell.

Formulas

Two kinds of formulas are supported by FPSpreadsheet:

FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated.

In older versions of FPSpreadsheet, formulas were stored directly in the cell record. This was given up to have parsed formulas available for faster calculation. The formulas are stored in the tree Formulas of the worksheet. The formula record contains the row and column index of the cell to which the formula belongs, the string representation of the formula, as well as the parser tree for quick evaluation.

FPSpreadsheet supports the majority of the formulas provided by the common spreadsheet applications. However, when reading a file created by these applications, there is always a chance that an unsupported formula is contained. To avoid crashing of fpspreadsheet, reading of formulas is disabled by default; the cell displays only the result of the formula written by the Office application. To activate reading of formulas add the element boReadformulas to the workbook's Options before opening the file. If an error occurs in this case the reader normally catches the exception, writes the exception message into the workbook's errorlog and continues reading. If you want reading to stop you must add the boAbortReadingOnFormulaError to the workbook Options.

Formulas can link to data in other sheets of the same workbook ("3d formulas") by applying the Excel syntax (see below). External links to spreadsheets in other files are not supported.

Calculation of formulas is normally not needed when a file is written by FPSpreadsheet for opening in an Office application because that automatically calculates the formula results. If the same file, however, is openend by an application based on FPSpreadsheet the calculated cells would be empty because the formulas are not automatically calculated by default. To activate calculation of formulas before writing a spreadsheet to file you have to add the option boCalcBeforeSaving to the workbook's Options.

If FPSpreadshet is used in an interactive application (such as the spready demo found in the examples folder of the FPSpreadsheet installation) it is desirable to calculate formulas automatically whenever formula strings or cell values are changed by the user. This can be achieved by the option boAutoCalc in the workbook's Options.

The most general setting regarding formulas, therefore, is

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

Calculation of formulas can be triggered manually by calling the method CalcFormulas of the worksheet or workbook. The latter is absolutely required when the workbook contains 3d formulas where the result of one cell can affect cells in other sheets. If there are only within-sheet formulas then the worksheet's CalcFormulas is sufficient.

String formulas

String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is WriteFormula:

pascal
var
  MyWorksheet: TsWorksheet;
//...
  MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
  // By default, use dot as decimal and comma as list separator!

A few notes on syntax:

pascal
var
  MyWorksheet: TsWorksheet;
//...
  MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
  // Because of the "true" the formula parser accepts the comma as decimal and the
  // semicolon as list separator if the workbook's FormatSettings are set up like this.

Use the worksheet methods ReadFormula or ReadFormulaAsString to retrieve the string formula assigned to the cell. The pointer to the cell must be given as a parameter. The latter function accepts an additional (boolean) parameter ALocalized to use the decimal and list separators of the workbook's FormatSettings for creation of the formula string.

pascal
var
  MyWorksheet: TsWorksheet;
  cell: PCell;
//...
  cell := MyWorksheet.FindCell(0, 1);
  WriteLn('The formula in internal format is ', MyworkSheet.ReadFormula(cell));
  WriteLn('The localized formula is ', MyWorksheet.ReadFormulaAsString(cell, true));
//-------------------------------------------------------------------
// For the previous example, this will result in the following output
The formula in internal format is ROUND(A1+B2+1.215,0)
The localized formula is ROUND(A1+B2+1,215;0)

RPN formulas

At application level, string formulas are mainly used, and RPN formulas are of little practical importance. Therefore, documentation of RPN formulas has been removed from this main FPSpreadsheet wiki and can be found in the article "RPN Formulas in FPSpreadsheet".

Shared formulas and array formulas

List of built-in formulas

FPSpreadsheet supports more than 80 built-in formulas. In order not to blow up this wiki page too much documentation of these formulas has been moved to the separate document "List of formulas".

To learn more about the functions available, look at file testcases_calcrpnformula.inc in the tests folder of the FPSpreadsheet installation where every function is included with at least one sample.

Extending FPSpreadsheet by user-defined formulas

Although the built-in formulas cover most of the applications there may be a need to access a formula which is available in the Office application, but not in FPSpreadsheet. For this reason, the library supports a registration mechanism which allows to add user-defined functions to the spreadsheets. This can be done by calling the procedure RegisterFunction from the unit fpsExprParser:

pascal
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;
pascal
type
  TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
  TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;

The TsExpressionResult is a variant record containing result or argument data of several types:

pascal
type
  TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
    rtCell, rtCellRange, rtError, rtAny);

  TsExpressionResult = record
    Worksheet       : TsWorksheet;
    ResString       : String;
    case ResultType : TsResultType of
      rtEmpty       : ();
      rtError       : (ResError       : TsErrorValue);
      rtBoolean     : (ResBoolean     : Boolean);
      rtInteger     : (ResInteger     : Int64);
      rtFloat       : (ResFloat       : TsExprFloat);
      rtDateTime    : (ResDateTime    : TDatetime);
      rtCell        : (ResRow, ResCol : Cardinal);
      rtCellRange   : (ResCellRange   : TsCellRange);
      rtString      : ();
  end;

  TsExprParameterArray = array of TsExpressionResult;

As an example we show here the code for the CONCATENATE() formula which joins two or more strings:

pascal
const
  INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;  
...
  RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);

procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
// CONCATENATE( text1, text2, ... text_n )
var
  s: String;
  i: Integer;
begin
  s := '';
  for i:=0 to Length(Args)-1 do
  begin
    if Args[i].ResultType = rtError then
    begin
      Result := ErrorResult(Args[i].ResError);
      exit;
    end;
    s := s + ArgToString(Args[i]);
    // "ArgToString" simplifies getting the string from a TsExpressionResult as 
    // a string may be contained in the ResString and in the ResCell fields.
    // There is such a function for each basic data type.
  end;
  Result := StringResult(s);
  // "StringResult" stores the string s in the ResString field of the
  // TsExpressionResult and sets the ResultType to rtString.
  // There is such a function for each basic data type.
end;

There is a worked-out example (demo_formula_func.pas) in the folder examples/other of the FPSpreadsheet installation. In this demo, four financial functions (FV(), PV(), PMT(), RATE()) are added to FPSpreadsheet.

Unsupported formulas

Sometimes it is required to create files for the Office applications with formulas not supported by fpspreadsheet. This is possible to some extent when the workbook option boIgnoreFormulas is active. Then any arbitrary formula can be written to a cell, and the formula is not checked and not evaluated. The workbook can be written to an .ods or .xlsx file. The old xls file format cannot be used because the formula would have to be parsed to create the rpn formula needed.

In folder examples/other you can find the sample project demo_ignore_formula which creates an ods file with references to another data file - external references normally are not supported by fpspreadsheet, and thus the ignore-formulas workaround must be used. Note that this example does not work with xlsx because Excel writes information on the external links to separate xml files within the xlsx container.

Cell formatting

Number and date/time formats

Numbers and date/time values can be displayed in a variety of formats. In FPSpreadsheet this can be achieved in two ways:

Number formats can be specified by these worksheet methods:

pascal
type
  TsWorksheet = class
  public
    // Set number formats alone
    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;

    // Set date/time formats alone  
    function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''): PCell; overload;
    procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
      const AFormatString: String = ''); overload;

    // Set cell values and number formats in one call

    // number values
    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;   

    // date/time values
    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;  

    // currency values
    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;     
  ...
Built-in number formats

The built-in formats are defined by the enumeration TsNumberFormat. In spite of its name, the elements cover both number and date/time values:

pascal
type
  TsNumberFormat = (
    // general-purpose for all numbers
    nfGeneral,
    // numbers
    nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
    // currency
    nfCurrency, nfCurrencyRed, 
    // dates and times
    nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
    nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
    // other (using format string)
    nfCustom);

As already noted the workbook has a property FormatSettings which provides additional information to control the resulting formatting. This is essentially a copy of the DefaultFormatSettings declared in the sysutils unit (the elements LongDateFormat and ShortDateFormat are slightly modified to better match the default settings in the main spreadsheet applications). The main purpose of the FormatSettings is to add a simple way of localization to the number formats.

Number format strings

In addition to these pre-defined formats, more specialized formatting can be achieved by using the format constant nfCustom along with a dedicated format string. The format string is constructed according to Excel syntax which is close to the syntax of fpc's FormatFloat and FormatDateTime commands (accepted as well, see the online-help for these functions).

Here is a basic list of the symbols used:

<thead>

</thead> <tbody>

1.2345678 --> <code>'1.2345678'</code></td>

<code>'0.0'</code>: 1 --> <code>'1.0'</code><br /> <code>'0.0'</code>: 1.2345678 --> <code>'1.2'</code></td>

<code>'0.*'</code>: 1.2345678 --> <code>'1.2'</code></td>

<code>'0.0??'</code>: 1 --> <code>'1.0 '</code></td>

<code>'0.00,'</code>: 1200 --> <code>'1.20'</code></td>

<code>'# #/#'</code>: 1.5 --> <code>'1 1/2'</code><br /> <code>'# #/16'</code>: 1.5 --> <code>'1 8/16'</code><br /> also: see date/time examples below</td>

Jan 3, 2012 --> <code>'2012-01-03'</code><br /> In this example, the <code>DateSeparator</code> is a dash character (<code>-</code>).</td>

Jan 3, 2012 --> <code>'12-01-03'</code></td>

Please note that the <code>m</code> code can also be interpreted as the "minutes" of a time value (see below).</td>

Jan 3, 2012 --> <code>'2012-1-03'</code></td>

Please note that the <code>mm</code> code can also be interpreted as the "minutes" of a time value (see below).</td>

Jan 3, 2012 --> <code>'2012-01-03'</code></td>

Jan 3, 2012 --> <code>'2012-Jan-03'</code></td>

Jan 3, 2012 --> <code>'2012-January-03'</code></td>

Jan 3, 2012 --> <code>'2012-01-3'</code></td>

Jan 3, 2012 --> <code>'2012-01-03'</code></td>

Jan 03, 2012 --> <code>'Tue 2012-01-03'</code></td>

Jan 03, 2012 --> <code>'Tuesday 2012-01-03'</code></td>

0.25 --> <code>'6:00'</code></td>

0.25 --> <code>'06:00'</code></td>

1.25 --> <code>'30:00'</code></td>

0.25 --> <code>'6:0'</code></td>

0.25 --> <code>'6:00'</code></td>

1.25 --> <code>'1800:00'</code></td>

0.25 --> <code>'06:00:0'</code></td>

0.25 --> <code>'06:00:00'</code></td>

1.25 --> <code>'108000'</code></td>

0.25 --> <code>'6:00:00 AM'</code></td>

Jan 3, 2012 --> <code>'2012/01/03'</code> (i.e. the <code>/</code> is not replaced by the <code>DateSeparator</code> of the workbook).</td>

Jan 3, 2012 --> <code>'2012/01/03'</code></td>

1200 --> <code>'1,200$'</code><br /> -1200 --> <code>'1,200$'</code><br /> 0 --> <code>'-'</code></td>

-1200 --> <code>'(1200)$'</code></td>

-1200 --> '<code>($ 1200.00)</code>'</td>

</tbody>

Colors

FPSpreadsheet supports colors for text, cell background, and cell borders. The basic EGA colors are declared in unit fpstypes as constants:

pascal
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; // redefine to allow different spelling

  // Identifier for undefined color 
  scNotDefined  = $40000000;

  // Identifier for transparent color 
  scTransparent = $20000000;

The TsColor represents the rgb value of a color, a single byte being used for the red, green, and blue components. The resulting number is in little endian notation, i.e. the red value comes first in memory: $00BBGGRR. (This is directly compatible with the color values as defined in the graphics unit.)

The high order byte is usually zero but is used internally to identify special color values, such as for undefined or transparent colors.

Note: In older versions of the library colors were defined as indexes into a color palette. THIS IS NO LONGER WORKING.

Unit fpsutils contains some useful functions for modification of colors:

Cell background

The cell background can be filled by predefined patterns which are identified by the record TsFillPattern:

pascal
type
  TsFillPattern = record
    Style: TsFillStyle;   // fill style pattern as defined below
    FgColor: TsColor;     // forground color of the fill pattern
    BgColor: TsColor;     // background color of the fill pattern
  end;

  TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
    fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
    fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
    fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
pascal
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;
...
  // Example 1: Assign a pattern of thin, horizontal, yellow stripes on a blue background to empty cell A1 (row 0, column 0)
  MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);

  // Example 2: Uniform gray background color of cell B1 (row 0, column 1) containing the number 3.14
  cell := MyWorksheet.WriteNumber(0, 1, 3.14);
  MyWorksheet.WriteBackgroundColor(cell, clSilver);

Cell borders

Cells can be emphasized by drawing border lines along their edges or diagonal lines. There are four borders plus two diagonals enumerated in the data type TsCellBorder:

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

In order to show a border line add the corresponding border to the cell's set Borders (type TsCellBorders, see above). In this way, each cell edge can be handled separately. Use the worksheet method WriteBorders for this purpose. This example adds top and bottom borders to the edges A1 and B1:

pascal
  MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);  // A1: row 0, column 0
  MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: row 0, column 1

Lines usually are drawn as thin, solid, black lines. But it is possible to modify line style and color of each line. For this purpose, the cell provides an array of TsCellBorderStyle records:

pascal
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;

The style of a given cell border can be specified by the following methods provided by the worksheet:

This example adds a thin black border to the top, and a thick blue border to the bottom of cells A1 and B1:

pascal
var
  cellA1, cellB1: PCell;
...
  cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);    // cell A1: row 0, column 0
  MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);  
  MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);

  cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]);    // cell B1: row 0, column 1
  MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles);     // copy all border styles from cell A1 to B1

Note: Diagonal lines are not supported by sfExcel2 and sfExcel5 - they are just omitted. sfExcel8 and sfOOXML use the same linestyle and color for both diagonals; when writing the border style of the diagonal-up line is assumed to be valid also for the diagonal-down line. Some writers do not support all the line styles of the TsLineStyle enumeration. In this case, appropriate replacement line styles are used.

Fonts

The cell text can displayed in various fonts. For this purpose, the workbook provides a list of TsFont items:

pascal
type
  TsFont = class
    FontName: String;
    Size: Single;   
    Style: TsFontStyles;
    Color: TsColor;
    Position: TsFontPosition;
  end;

Every cell is provided with an index into the font list.

In order to assign a particular font to a cell, use one of the following methods of TsSpreadsheet:

pascal
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; 
    // plus: overloaded versions accepting a pointer to a cell record instead of the row and column index as parameter
    // ...
  end;

The workbook's font list contains at least one item which is the default font for cells with unmodified fonts. By default, this is 10-point "Arial". Use the workbook method SetDefaultFont to assign a different font to the first list item.

The font at a given index of the font list can be looked up by calling the workbook function GetFont. The count of available fonts is returned by GetFontCount.

Here is an example which decreases the size of all 10-point "Arial" fonts to 9-point:

pascal
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 formatting

In addition to using a specific font for each cell it is also possible to specify particular font attributes for individual characters or groups of characters in each cell text. Following the Excel notation, we call this feature Rich-text formatting (although is has nothing in common with the "rich-text" file format).

For this purpose, unit fpstypes declares the type TsRichTextParams which is an array of TsRichTextParam records:

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

  TsRichTextParams = array of TsRichTextParam;

FontIndex refers to the index of the font in the workbook's FontList to be used for formatting of the characters beginning at the index FirstIndex. Being a string character index the FirstIndex is 1-based.

There are two ways to add "rich-text" formatting to a cell text:

pascal
  MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
pascal
var
  richTextParams: TsRichTextParams;
  fnt: TsFont;
  cell: PCell;
begin
  SetLength(rtp, 2);
  cell := MyWorksheet.GetFont(0, 0);
  fnt := MyWorksheet.ReadCellFont(cell);
  richTextParams[0].FirstIndex := 8; // The superscript groups begins with "2" which is the (1-based) character #8 of the cell text.
  richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
  richTextParams[1].FirstIndex := 9; // Normal font again beginning with character #9.
  richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
  MyWorksheet.WriteUTF8Text(cell, 'Area (m2)', richTextParams);
end;

Use the worksheet method DeleteRichTextParams to remove rich-text formatting from a previously formatted cell.

Note: If the cell is supposed to have a font different from the worksheet's default font then this font must be written to the cell before writing the rich-text formatted text. Otherwise the font in the unformatted regions will not be up-to-date.

Text rotation

Usually text is displayed in the cells horizontally. However, it is also possible to rotate it by 90 degrees in clockwise or counterclockwise directions. In addition, there is also an option to stack horizontal characters vertically above each other.

If you need this feature use the worksheet method WriteTextRotation and specify the text direction by an element of the enumeration type TsTextRotation:

pascal
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;

  // example for counter-clockwise rotated text in cell A1:
  WriteTextRotation(0, 0, rt90DegreeCounterClockwizeRotation);

Warning: Finer degrees of rotation which may be supported by some spreadsheet file formats are ignored.

Text alignment

By default, cell texts are aligned to the left and bottom edges of the cell, except for numbers which are right-aligned. This behavior can be changed by using the worksheet methods WriteHorAlignment and WriteVertAlignment:

pascal
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;

  // Example: Center the text in cell A1 both horizontally and vertically
  MyWorkbook.WriteHorAlignment(0, 0, haCenter);
  MyWorkbook.WriteVertAlignment(0, 0, vaCenter);

Word wrap

Text which is longer than the width of a cell can wrap into several lines by calling the method WriteWordwrap of the spreadsheet:

pascal
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;

  // Example: activate wordwrap in cell A1
  MyWorksheet.WriteWordwrap(0, 0, true);

Merged cells

Like the Office applications, FPSpreadsheet supports also to feature of merging cells to a single large cell which is often used as a common header above simlar columns. Simply call MergeCells and pass a parameter to specify the cell range to be merged, either an Excel range string (such as A1:D5), or the first and last rows and columns:

pascal
  MyWorksheet.MergeCells('A1:D5');
  // or: MyWorksheet.MergeCells(0, 0, 4, 3);  // first row, first column, last row, last column

The content and format displayed for a merged range is taken from the upper left corner of the range, cell A1 in above example. This cell is called the MergeBase in the library. Except for this corner cell, there must not be any other cells in the range. If there are their contents and format will be hidden.

In order to break up a merged range back up into individual cells, use the command Unmerge and pass any cell that is within the merged range:

pascal
  MyWorksheet.UnmergeCells('B1');
  // or: MyWorksheet.UnmergeCells(0, 1);   // row, column of any cell within the range

Merged cells can be read from/written to all file formats except for sfCSV, sfExcel2 and sfExcel5 which do not support this feature natively.

The information which cells are merged is stored in an internal list. Unlike in earlier versions it is no longer possible to access the MergeBase from the cell directly. Use the following functions to extract information on merged cells:

pascal
var
  cell, base: PCell;
  r1,c1,r2,c2: Cardinal;
...
  cell := MyWorksheet.FindCell('B1');
  if MyWorksheet.IsMerged(cell) then
  begin
    WriteLn('Cell B1 is merged.');

    MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
    WriteLn('The merged range is ' + GetCellRangeString(r1, c1, r2, c2));

    base := MyWorksheet.FindMergeBase(cell);
    WriteLn('The merge base is cell ' + GetCellString(base^.Row, base^.Col));
  end;

Cell protection

This is described in a separate section below.

Additional data

Cell comments

Comments can be attached to any cell by calling

pascal
  MyWorksheet.WriteComment(0, 0, 'This is a comment for cell A1');

They are stored in an internal list of the worksheet. Use the corresponding worksheet methods to access comments:

Hyperlinks

Hyperlinks can be attached to cells in order to link cells to other documents or other cells in the same workbook. The general syntax for creating hyperlinks is

pascal
  procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');

Note: Hyperlinks can be added to empty cells or to cells with content. In the latter case, the displayed content is not changed by the hyperlink; in the former case the cell is converted to a label cell showing the hyperlink target. Be aware that OpenOffice/LibreOffice only does accept hyperlinks with non-text cells.

Examples:

pascal
  // Open the web site www.google.com
  MyWorksheet.WriteText(0, 0, 'Open google');
  MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');

  // Open the local file with the absolute path "C:\readme.txt" (assuming Windows)
  MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');

  // Open the mail client to send a mail
  MyWorksheet.WriteText('Send mail');
  MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');

  // Jump to a particular cell 
  MyWorksheet.WriteText(5, 0, 'Jump to cell A10 on sheet2');
  MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');

  // Jump to cell A10 on the current sheet and display a popup hint
  MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Go to cell A10');

Note: FPSpreadsheet does not "follow" the links, it only provides a mechanism to get access to the link data. TsWorksheetGrid from the laz_fpspreadsheet_visual package, however, fires the event OnClickHyperlink if a cell with an external hyperlink is clicked for fractions of a second. In the corresponding event handler, you can, for example, load a target spreadsheet, or open a web browser to display the linked web site. If the link is an internal link to another cell within the same workbook then the grid jumps to the related cell.

Images

FPSpreadsheet supports embedding of images in worksheets. Use one of the worksheet methods WriteImage() to add an image to the worksheet:

pascal
  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);

The upper/left corner of the image is placed at the upper/left corner of the cell in the specified row and column. The floating point parameters offsetX, offsetY, scaleX and scaleY are optional: they define an offset of this image anchor point from the cell corner and a magnification factor. The path to the image file is given as parameter filename. Alternatively, overloaded versions can be used which accept a stream in place of the file name or an image index in the workbook's EmbeddedObj list - use MyWorkbook.FindEmbeddedObj(filename) to get this index for a previously loaded image file.

Note that FPSpreadsheet needs to know the image type for successfull picture import. Currently, the types png, jpg, tiff, bmp, gif, svg, wmf, emf, and pcx are supported (Excel2007 cannot read imported svg and pcx images). Other formats can be registered by writing a function which determines the image size and pixel density, and by registering the new format using the procedure RegisterImageType - see unit fpsImages for examples:

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

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

Due to differences in row height and column width calculation between FPSpreadsheet and Office applications it is not possible to position images correctly. If exact image positions are important you should follow these rules:

Sorting

Cells in a worksheet can be sorted for a variety of criteria by calling the Sort method of the worksheet. This method takes a TsSortParams record and the edges of the cell rectangle to be sorted as parameters; in an overloaded version, the cell rectangle can also be specified by means of an Excel-type range string (e.g. 'A1:G10'):

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

The sorting criteria are defined by a record of type TsSortParams:

pascal
type
  TsSortParams = record
    SortByCols: Boolean;
    Priority: TsSortPriority;  // spNumAlpha ("Numbers first"), or spAlphaNum ("Text first")
    Keys: TsSortKeys;
  end;    

  TsSortKey = record
    ColRowIndex: Integer;
    Options: TsSortOptions;    // set of [spDescending, spCaseInsensitive]
  end;

InitSortParams is a handy utility to initialize the sorting parameters:

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

The next code fragment shows a typical sorting call:

pascal
var
  sortParams: TsSortParams;
begin
  sortParams := InitSortParams(true, 2);  // sorting along columns, 2 sorting keys

  // primary sorting key: column 3, ascending, case-insensitive
  sortParams.Keys[0].ColRowIndex := 3;
  sortParams.Keys[0].Options := [ssoCaseInsensitive];

  // secondary sorting key: colum 1, descending
  sortParams.Keys[1].ColRowIndex := 1;
  sortParams.Keys[1].Options := [ssoDescending];

  // The sorted block extends between cells A1 (row=0, col=0) and F10 (row=9, col=5)
  MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
  // or: MyWorksheet.Sort(sortParams, 'A1:F10');
end;

Searching and replacing

Unit fpsSearch implements a search engine which can be used to look for specific cell content within a workbook, or to replace the found cell content by some other string.

Example:

pascal
uses
  fpsTypes, fpSpreadsheet, fpsUtils, fpsSearch, fpsAllFormats;
var
  MyWorkbook: TsWorkbook;
  foundWorksheet: TsWorksheet;
  foundRow, foundCol: Cardinal;
  MySearchParams: TsSearchParams;
begin
  MyWorkbook := TsWorkbook.Create;
  try
    MyWorkbook.ReadFromFile(AFileName);

    // Specify search criteria
    MySearchParams.SearchText := 'Hallo';
    MySearchParams.Options := [soEntireDocument];
    MySearchParams.Within := swWorkbook;

    // or: MySearchParaams := InitSearchParams('Hallo', [soEntireDocument], swWorkbook);

    // Create search engine and execute search
    with TsSearchEngine.Create(MyWorkbook) do begin
      if FindFirst(MySearchParams, foundWorksheet, foundRow, foundCol) then begin
        WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
        while FindNext(MySeachParams, foundWorksheet, foundRow, foundCol) do
          WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
      end;
      Free;
    end;
  finally
    MyWorkbook.Free;
  end;
end;

The search engine provides two methods for searching: FindFirst and FindNext. They are very similar, they only differ in where the search begins. In case of FindFirst, the starting cell is determined from the Options described below. In case of FindNext the search begins at the cell adjacent to the previously found cell. Both methods return the worksheet and row and column indexes of the cell in which the search text is found. If the search is not successful then the function result is FALSE, and the foundWorksheet is nil. It is clear that the foundWorksheet cannot be used for anything else while the search is running.

The record TsSearchParams specifies the criteria used for searching:

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

Besides the text to be searched (SearchText) it provides a set of options to narrow the search:

The record field Within identifies the part of the spreadsheet to be searched:

The search params record can be initialized by calling InitSearchParams (in unit fpsutils) with the record elements as optional parameters. Use the methods Workbook.ActiveWorksheet and Worksheet.SelectCell(ARow, ACol) to define the active worksheet and the position of the active cell, respectively, if needed by the search.

In addition to searching the search engine can also be used for replacing the found text by another string. Call the functions ReplaceFirst or ReplaceNext for this purpose. They act like their FindXXXX counterparts, therefore, they require a TsSearchParams record to specify the search criteria. But in addition to searching, these functions also perform the text replacement according to the specification in a TsReplaceParams record:

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

The ReplaceText identifies the string which will replace the found text pattern. The Options define a set of criteria how the replacement is done:

Use the function InitReplaceParams (in unit fpsutils) to initialize the replace parameters record with the provided (but optional) values.

Column and row operations

The worksheet provides these methods for inserting, deleting, hiding or unhiding columns and rows:

pascal
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;

Page layout

General

So far, FPSpreadsheet does not support printing of worksheets, but the Office applications do, and they provide a section of information in their files for this purpose. In FPSpreadsheets this information is available in the TsPageLayout class which belongs to the TsWorksheet data structure. Its properties and methods combine the most important features from the Excel and OpenDocument worlds.

pascal
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 }
    // embedded header/footer images
    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;

    // Repeated rows and columns 
    function HasRepeatedCols: Boolean;
    function HasRepeatedRows: Boolean;
    procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
    procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);

    // print ranges 
    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;

Note: The fact that the PageLayout belongs to the worksheet indicates that there can be several page layouts in the same workbook, one for each worksheet.

pascal
  MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
  MyWorksheet.PageLayout.FitWidthToPages := 1;     // all columns on one page width
  MyWorksheet.PageLayout.FitHeightToPages := 0;    // use as many pages as needed

Headers and footers

Header and footer texts can be composed of left-aligned, centered and right-aligned strings. Add the symbol &L to indicate that the following string is to be printed as the left-aligned part; use &C accordingly for the centered and &R for the right-aligned parts. There are other symbols which will be replaced by their counterparts during printing:

The arrays Headers[]/Footers[] provide space for usage of three different headers or footers:

Leave the strings at index 0 and 2 empty if the print-out should always have the same header/footer. You can use the constant HEADER_FOOTER_INDEX_ALL for better clarity. Example:

pascal
  MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T';           // centered "date time" on all pages as header
  MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N';   // right-aligned "Page .. of ..." on odd pages as footer
  MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N';  // dto, but left-aligned on even pages

Protection

In the Office applications, workbooks can be protected from unintentional changes by the user. fpspreadsheet is able to read and write the data structures related to protection, but does not enforce them. This means, for example, that cells can be modified by the user although the worksheet is specified as being locked.

Protection is handled at three levels: workbook protection, worksheet protection and cell protection.

Workbook protection

TsWorkbook contains a set of workbook or document protection options:

In relation to workbook protection is the worksheet option soPanesProtection which prevents the panes of a worksheet from being modified if the workbook is protected.

Depending on the file format, only some of these options might be supported. In these cases, the non-supported options are commonly accepted default values.

Worksheet protection

TsWorksheet houses a similar set of protection options. Whenever an option is included in the set Protection of the workbook the corresponding action is not allowed and locked:

These levels of protection become active if the option soProtected is added to the worksheet's Options, or by calling the worksheet method Protect(true).

Cell protection

Cell protection becomes active when the worksheet protection is enabled. It is controlled by a set of TsCellProtection elements which belong to the cell format record:

Cell protection can be changed by calling the worksheet method WriteCellProtection. Conversely, ReadCellProtection can be used to retrieve the protection state of a particular cell:

pascal
// query and modifiy the protection state of cell A1 (row=0, col=0)
var
  cell: PCell;
  cellprot: TsCellProtections;
...
  // Find the cell
  cell := worksheet.FindCell(0, 0);
  // query cell protection
  cellprot := worksheet.ReadCellProtection(cell);
  // Unlock the cell for editing, don't change the visibility of formulas
  worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
  // Hide formula of the cell and unlock the cell.
  worksheet.WriteCellProtection(cell, [cpHideFormulas]);

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 CryptoInfo record of the wordbook and the worksheets, respectively:

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

Warning: FPSpreadsheet does not perform any hashing calculations, the CryptoInfo 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.

Loading and saving

Adding new file formats

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.

pascal
function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
  AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;

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.

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:

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:

pascal
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

pascal
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:

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

Please see FPSpreadsheet: Examples for a variety of code examples.

Download

Subversion

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

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

SVN change log

Incompatible changes

Stable releases

You can find all releases of FPSpreadsheet on sourceforge.

Version 1.12

Change log (with respect to v1.10x)

Version 1.10.1

This is the latest stable release.

Change log (with respect to v1.8x)
Incompatible changes

Version 1.8

Change log (with respect to v1.6x)
Incompatible changes

Version 1.6

Change log (with respect to v1.4.x)
Incompatible changes

Version 1.4

Change log (with respect to v1.2.x)
Incompatible changes

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

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:

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

<thead>

sheets</th>

support</th>

support</th>

Formula</th>

Formula</th>

references</th>

formats</th>

</thead> <tbody>

</tbody>

(+) 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.
(++++) Extended OOXML formatting options not supported.
() 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

The following formatting options are available:

<thead>

alignment</th>

rotation</th>

text</th>

support</th>

ground</th>

wrap</th>

size</th>

format</th>

cells</th>

layout</th>

ranges</th>

images</th>

format</th>

cols/rows</th>

breaks</th>

</thead> <tbody>

(Excel 5.0 and 95)</td>

(Excel 97 - XP)</td>

</tbody>

(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 Tab color
Excel 2.x Working Working not working N/A N/A N/A N/A
Excel 5.0 (Excel 5.0 and 95) Working Working Working Working Working N/A N/A
Excel 8.0 (Excel 97 - XP) Working Working Working Working Working Working Working
Excel2003/XML Working Working Working Working Working || class="working|Working N/A
Excel OOXML Working Working Working Working Working Working Working
OpenDocument Working Working Working Working Working Working Working
HTML Writing only Writing only N/A N/A N/A not working N/A

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!

Long-term:

Changelog

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

License

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

References

Wiki links

External Links

From the Free Pascal wiki, page "FPSpreadsheet/pl", as it stood on 2021-08-14 after a change by FTurtle, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's Licensing page. If you have concerns about this material, message the admin.

Revision 2, last changed by WikiArchive .

Talk about this page

Nobody has opened a bench for this page yet. Sign in to open one.