FPSpreadsheet

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

The fpSpreadsheet library offers a convenient way to generate and read spreadsheet documents in various formats. The library is written in a very flexible manner, capable of being extended to support any number of formats easily.

Screenshot of spready demo program provided with fpspreadsheet showing an XLS file:

Documentation

This wiki pages covers the latest development/trunk version of FPSpreadsheet available via subversion. Please see section #Stable releases for documentation on the stable release that you can download.

API Documentation

API Reference

A help file in CHM format can be found in the fpspreadsheet installation root folder. If you did not yet install the package follow http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/fpspreadsheet.chm to "fpspreadsheet.chm".

Basics

The smallest entities in a spreadsheet are the cells which contain the data. Cells can hold various data types, like strings, numbers, dates, times, boolean values, or formulas.

The cells are arranged in a grid-like structure, called worksheet, or spreadsheet, consisting of rows and columns. Each cell has a unique address given by the row and column index.

Worksheets are bound together to form a workbook which represents the document of the spreadsheet application.

fpspreadsheet follows this same structure - there is a TCell, a TsWorksheet, and a TsWorkbook.

Workbook

The class TsWorkbook is the main class visible to the user. It provides methods for reading data from and writing them to file. The versatile structure of the library provides access to various popular file formats, like Excel .xls or .xlsx, or OpenOffice .ods.

The file format is specified by the type TsSpreadsheetFormat

type
  TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfOOXML, 
    sfOpenDocument, sfCSV, sfWikiTable_Pipes, sfWikiTable_WikiMedia);

where

• sfExcel* stands for versions of the binary xls format used by Excel ("BIFF" = "Binary Interchange File Format")
• sfOOXLM corresponds to the newer xlsx format introduced by Excel2007
• sfOpenDocument is the spreadsheet format used by OpenOffice/LibreOffice
• sfCSV refers to comma-delimited text files; they can be understood by any text editor and all spreadsheet programs, but do not contain formatting information.
• sfWikiTable_Pipes and sfWikiTable_WikiMedia is the format used by tables in wiki websites.

There are no plans to implement "ancient" file formats like Excel3.0/4.0 or Lotus.

When applying fpspreadsheet the first task is to create an instance of the workbook:

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

Reading of spreadsheet files is accomplished (among others) by the workbook methods

• procedure ReadFromFile(AFileName: string):
  Reads the file with the given name and automatically determines the correct file format.
• procedure ReadFromFile(AFileName: string; AFormat: TsSpreadsheetFormat):
  Reads the file, but assumes that the file format is as specified by AFormat.

The following workbook methods can be used for saving to file:

• procedure WriteToFile(const AFileName: string; const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False):
  Writes the workbook to the given file using the given spreadsheet format. If the file already exists it is automatically overwritten if AOverwriteExisting is true:
• procedure WriteToFile(const AFileName: String; const AOverwriteExisting: Boolean = False):
  dto., but the file format is determined from the file extension provided (in case of Excel's xls the most recent version, sfExcel8, is used).

After calling these methods it is advantageous to look at the workbook's property ErrorMsg in which messages due to errors or warnings are collected that might have occured during reading/writing. This property returns a multi-lined string which is displayed best in a memo component; if everything was fine it is an empty string.

Note: FPSpreadsheets provides specialized classes for reading from and writing to each file format. These units are not available automatically, you have to add them to the uses clause explicitly. FPSpreadsheet will complain about "unsupported file format" if the requested reader/writer is not found. Here is a list of the unit names:

• xlsbiff2, xlsbiff2 and xlsbiff8 for the binary xls file formats sfExcel2, sfExcel5 and sfExcel8, respectively,
• xlsOOXML for the xlsx file format sfOOXML of Excel 2007 and later,
• fpsopendocument for the file format sfOpenDocument of OpenOffice/LibreOffice,
• fpscsv for text files with comma-separated values (csv),
• wikitables for sfWikiTable_Pipes and sfWikiTable_WikiMedia,
• or, simply add fpsllformats to get read/write support for all file formats.

Worksheet

The workbook contains a list of TsWorksheet instances. They correspond to the tabs that you see in Excel or Open/LibreOffice. When reading a spreadsheet file the worksheets are created automatically according to the file contents. When a spreadsheet is created manually to be stored on file a worksheet has to be created by adding it to the workbook:

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My_Table');  
    // 'My_Table' is the "name" of the worksheet
  ...

Already existing worksheets can be accessed by using the TsWorkbook methods

• function GetFirstWorksheet: TsWorksheet: retrieves the first worksheet of the workbook.
• function GetWorksheetByIndex(AIndex: Cardinal): TsWorksheet: returns the worksheet with the given index (starting at 0).
• function GetWorksheetByName(AName: String): TsWorksheet: returns the worksheet with the given name which was used when the worksheet was added.

The count of already existing worksheets can be queried by calling GetWorksheetCount.

Cell

The worksheet, finally, gives access to the cells. A newly created worksheet, as in above example, is empty and does not contain any cells. Cells are added by assigning data or attributes to them by one of the WriteXXXX methods of the worksheet. As already mentioned, a cell is addressed by the index of the row and column to which it belongs. As usual, row and column indexes start at 0. Therefore, cell "A1" belongs to row 0 and column 0. It should be noted that row and column index are always specified in this order, this is different from the convention of TStringGrid. The following example creates a cell at address A1 and puts the number 1.0 in it.

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
begin
  MyWorkbook := TsWorkbook.Create;
  MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
  MyWorksheet.WriteNumber(0, 0, 1.0);  // "A1" has row=0 and col=0
  ...

It is also possible to access cells directly by means of the methods FindCell(ARow, ACol) or GetCell(ARow, ACol) of the workbook. Please be aware that these functions return a pointer to the cell data (type PCell). Don't forget to dereference the pointers! The difference between FindCell and GetCell is that the former one returns nil, if a cell does not yet exist, while the latter one creates an empty cell in this case. This is the declaration of the cell's data type:

  TCell = record
    Col: Cardinal; // zero-based
    Row: Cardinal; // zero-based
    ContentType: TCellContentType;

    { Possible values for the cells }
    FormulaValue: String;
    SharedFormulaBase: PCell;
    NumberValue: double;
    UTF8StringValue: ansistring;
    DateTimeValue: TDateTime;
    BoolValue: Boolean;
    ErrorValue: TsErrorValue;

    { Attributes, i.e. formatting fields }
    UsedFormattingFields: TsUsedFormattingFields;
    FontIndex: Integer;
    TextRotation: TsTextRotation;
    HorAlignment: TsHorAlignment;
    VertAlignment: TsVertAlignment;
    Border: TsCellBorders;
    BorderStyles: TsCelLBorderStyles;
    BackgroundColor: TsColor;
    NumberFormat: TsNumberFormat;
    NumberFormatStr: String;
  end;

  PCell = ^TCell;

Note: It is not recommended to manipulate the contents of TCell directly. Always use the corresponding WriteXXXX methods of the spreadsheet instead. The reason is that fpspreadsheet is under active development, and there is a large chance that the internal structure of TCell will change

The field ContentType indicates which data type is stored in the cell:

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

According to this field the corresponding data can be found in the fields

• FormulaValue (for ContentType=cctFormula),
• NumberValue (for ContentType=cctNumber), or
• UTF8StringValue (for ContentType=cctUTF8String), or
• DateTimeValue (for ContentType=cctDateTime), or
• BoolValue (for ContentType=cctBool), i.e. TRUE or FALSE, or
• ErrorValue (for ContentType=cctError).

Note: After calculation of a formula or after reading of a file, the ContentType of the formula cell is converted to that of the result. Then the presence of a formula can only be detetected from the FormulaValue string.

The field SharedFormulaBase stores a pointer to the cell in which a shared formula is defined.

In addition to the data values, each cell contains a variety of attributes to be used for formatting:

• FontIndex: text font by specifying the index in the workbook's font list
• TextRotation: specifies whether the cell text is written horizontally or vertically
• HorAlignment: left-aligned, horizontally centered, or right-aligned text
• VertAlignment: top, buttom or vertically centered text
• Border: a set of flags indicating if a border line is to be drawn at the left, top, right, or bottom cell edge. the lines are drawn according to the BorderStyles.
• BackgroundColor: Index into the workbook's color palette for the background color of the cell.
• NumberFormat and NumberFormatStr specify how number or date/time values are formatted (e.g., number of decimal places, long or short date format, etc.).

See cell formatting below for a more detailed description.

Formulas

Two kinds of formulas are supported by FPSpreadsheet:

• String formulas: These are written in strings just like in the office applications, for example "=ROUND(A1+B1,0)". They used internally in the files of Open/LibreOffice and Excel xlsx.
• RPN formulas are used internally in the binary xls Excel files. They are written in Reverse Polish Notation (RPN), for example: A1, B1, Add, 0, ROUND.

The current trunk version of FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated. The cell record stores the string formula in the field FormulaValue. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.

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. To activate reading of formulas add the element boReadformulas to the workbook's Options before reading a file.

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

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

The most general setting regarding formulas, therefore, is

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

String formulas

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

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

A few notes on syntax:

• The leading = character which identifies formulas in the Office applications is not absolutely necessary here and can be dropped. The formula is stored in the cell record without it.
• The case of the formula name is ignored.
• Spaces can be added for better readability, but they will be lost when saving.
• Strings must be enclosed in double quotes.
• Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
• Setting the optional parameter ALocalized of the worksheet methods WriteFormula to TRUE, however, allows to use localized decimal and list separators taken from the workbook's FormatSettings - see spready demo.

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

RPN formulas

Understanding RPN formulas

RPN formulas are an intermediate result after parsing a string formula. They consist of tokens, i.e. information on the constituents of the formula in a way that can be immediately used for calculation of the expression result. There are tokens for numbers, operations, functions etc. When parsing, the tokens are extracted from the expression string and pushed onto a stack. In FPSpreadsheet, this stack corresponds to the array TsRPNFormula, the array elements correspond to the tokens on the stack. When calculating the formula, Excel traverses the stack buttom-up (meaning in FPSpreadsheet: from low- to high-index array elements). Whenever it finds a token for an operation or function it removes this token from the stack, along with the tokens of the operands, and replaces them by the result of the calculation.

Here's an example: In a simple expression like "=4+5", the stack contains the tokens for the number constants:

• the first argument: [4]
• the second argument: [5]
• the operation [+].

The "+" operation is a binary operation, meaning that it needs two arguments. Therefore, when Excel reaches the [+] token, it removes the [+] and both operands from the stack and replaces them by the result of the calculation, the token with the value 9. Since there are no other elements on the stack, this is the final result of the calculation.

Now a more complex examples: "=ROUND(2+4*3.141592, 2)" which rounds the result of the calculation 2+4*3.141592 to two decimals. The function "ROUND" requires two parameters: the value to be rounded, and the number of decimal places. In total, the stack consists of these elements:

• [2]
• [4]
• [3.141592]
• [*]
• [+]
• [2]
• [ROUND]

Going from first to last, the first operation/function token met is [*]. As this is another binary operation, this requires two arguments. Therefore, [4], [3.141592] and [*] are removed from the stack and replaced by the result [12.56637].

Now the stack looks like:

• [2]
• [12.56637]
• [+]
• [2]
• [ROUND]

Now, the first operation token found is [+] replacing [2], [12.56637], [+] by [14.56637]. Finally, the stack is left with the tokens needed for the ROUND function:

• [14.56637]
• [2]
• [ROUND]

which immediately leads to the final result [14.57].

Using simple constant numbers

For coding above formula "=4+5" in FPSpreadsheet the length of the RPNFormula array must be set to 3 (3 elements, "4", "5", "+"). The first and second elements are "numbers" which has to be indicated by setting ElementKind=fekNum for these array elements. The value of each number is specified as the DoubleValue of the formula element. The last element is the formula which is specified by the ElementKind of fekAdd.

In total, this results in the following code:

var
  MyRPNFormula: TsRPNFormula;
begin
  // Write the formula =4+5
  MyWorksheet.WriteUTF8Text(3, 0, '=4+5'); // A4
  // Write the RPN formula to the spreadsheet
  SetLength(MyRPNFormula, 3);
  MyRPNFormula[0].ElementKind := fekNum;
  MyRPNFormula[0].DoubleValue := 4.0;
  MyRPNFormula[1].ElementKind := fekNum;
  MyRPNFormula[1].DoubleValue := 5.0;
  MyRPNFormula[2].ElementKind := fekAdd;
  MyWorksheet.WriteRPNFormula(3, 2, MyRPNFormula);
end;

This requires quite some typing. For simplification a methodology of nested function calls has been added to FPSpreadsheet in which every element is specified by a function which links to the next element function via its last argument:

begin
  // Write the formula =4+5
  MyWorksheet.WriteUTF8Text(3, 0, '=4+5');
  // Write the RPN formula to the spreadsheet
  MyWorksheet.WriteRPNFormula(3, 2,  // Row and column of the formula cell
    CreateRPNFormula(                // function to create a compact RPN formula
      RPNNumber(4,                   // 1st operand: a number with value 4
      RPNNumber(5,                   // 2nd operand: a number with value 5
      RPNFunc(fekAdd,                // function to be performed: add
      nil)))));                      // end of list
end;

Using cells in formulas

Of course, the formulas can also contain links to cells. For this purpose the ElementType needs to be fekCellValue. This instructs Excel to use the value of the cell in the calculation. There are, however, also functions which require other properties of the cell, like format or address. For this case, use fekCellRef for the ElementKind. Another specialty is the usage of absolute and relative cell addresses ($A$1 vs. A1, respectively). Cell row and column addresses specified in the RPNFormula elements are absolute by default. If you want relative rows/columns add rfRelRow or rfRelCol to the element's RelFlags set. Or, if you prefer the nested function notation simply use the function RPNCellValue (or RPNCellRef) with the standard notation of the cell adress using the $ sign.

Here, as an example, =A1*$B$1 in array notation:

var
  MyRPNFormula: TsRPNFormula;
begin
  SetLength(MyRPNFormula, 3);
  MyRPNFormula[0].ElementKind := fekCellValue;
  MyRPNFormula[0].Row := 0;   // A1
  MyRPNFormula[0].Col := 0;
  MyRPNFormula[0].RelFlags := [rfRelRow, rfRelCol];  // relative!
  MyRPNFormula[1].ElementKind := fekCellValue;
  MyRPNFormula[1].Row := 1;
  MyRPNFormula[1].Col := 0;   // $B$1, RelFlags not needed since absolute address
  MyRPNFormula[2].ElementKind := fekMul;
  MyWorksheet.WriteRPNFormula(3, 2, MyRPNFormula);
end;

And now in nested function notation:

  MyWorksheet.WriteRPNFormula(3, 2,  // Row and column of the formula cell
    CreateRPNFormula(                // function to create a compact RPN formula
      RPNCellValue('A1',             // 1st operand: contents of cell "A1" (relative!)
      RPNCellValue('$B$1',           // 2nd operand: contents of cell "$B$1" (absolute!)
      RPNFunc(fekMul,                // function to be performed: multiply
      nil)))));                      // end of list

Using ranges of cells

In spreadsheet applications like Excel, the notation A1:C5 refers to a range of cells: the rectangle between (and including) cells A1 and C5.

This feature is available in FPSpreadsheet as well: use the ElementKind fekCellRange and a second set of row/column indices (Row2 and Col2, respectively). There are also flags rfRelRow2 and rfRelCol2 to mark the second corner cell as relative.

Using built-in operations and functions

Here is a list of the basic operations available in FPSpreadsheet RPN formulas:

ElementKind	Example	Meaning	Operands	Argument types	Argument function
fekAdd	=A1+A2	add numbers	2	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekSub	=A1-A2	subtract numbers	2	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekMul	=A1*A2	multiply numbers	2	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekDiv	=A1/A2	divide numbers	2	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekPercent	=A1%	divide a number by 100 and add "%" sign	1	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekPower	=A1^2	power of two numbers	2	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekUMinus	=-A1	unary minus	1	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekUPlus	=+A1	unary plus	1	fekNum, fekCellValue	RPNNumber(), RPNCellValue()
fekConcat	="Hello "&A1	string concatenation	2	fekString, fekCellValue	RPNString(), RPNCellValue()

Column "Operands" indicates how many operands are required on the stack before the function.

Beyond that, Excel provides a huge number of functions, many of which have been made available for FPSpreadsheet via the fekFunc symbol. To specify the formula you must pass the formula's name to the FuncName element of the FormulaElement. The formula name can be found in the 1st column of the table in this page.

Here is an example which calculates the sine function of the number in cell A1:

  MyWorksheet.WriteRPNFormula(3, 2,  // Row and column of the formula cell
    CreateRPNFormula(                // function to create a compact RPN formula
      RPNCellValue('A1',             // 1st operand: contents of cell "A1" 
      RPNFunc('SIN',                 // function to be performed: 'SIN()'
      nil))));                       // end of list

or, in array syntax:

var
  MyRPNFormula: TsRPNFormula;
begin
  SetLength(MyRPNFormula, 2);
  MyRPNFormula[0].ElementKind := fekCellValue;
  MyRPNFormula[0].Row := 0;   // A1
  MyRPNFormula[0].Col := 0;
  MyRPNFormula[0].RelFlags := [rfRelRow, rfRelCol];  // relative!
  MyRPNFormula[1].ElementKind := fekFunc;
  MyRPNFormula[1].FuncName := 'SIN';
  MyWorksheet.WriteRPNFormula(3, 2, MyRPNFormula);
end;

Please note that some functions allow a variable count of parameters. In this case, this value has to be specified as ParamsNum in the formula. The function SUM, for example, accepts up to 30 parameters. For calculating the sum of all numbers in the range A1:C10, therefore, we have to specify explicitly that a single parameter (the cell block A1:C10) is used:

  SetLength(MyRPNFormula, 2);
  MyRPNFormula[0].ElementKind := fekCellRange;
  MyRPNFormula[0].Row := 0;   // A1
  MyRPNFormula[0].Col := 0;
  MyRPNFormula[0].Row2 := 9;  // C10
  MyRPNFormula[0].Col2 := 2;
  MyRPNFormula[0].RelFlags := [rfRelRow, rfRelCol, rfRelRow2, rfRelCol2];
  MyRPNFormula[1].ElementKind := fekFUNC;
  MyRPNFormula[1].FuncName := 'SUM';
  MyRPNFormula[1].ParamsNum := 1;      // 1 argument used in SUM
  MyWorksheet.WriteRPNFormula(1, 2, MyRPNFormula);  // cell C2

or, shorter:

  MyRPNFormula.WriteRPNFormula(1, 2, CreateRPNFormula(
    RPNCellRange('A1:C10',
    RPNFunc(fekSUM, 1,        // SUM with 1 argument
    nil))));

Displaying RPN formulas

xls files store formulas in RPN notation, When reading such a file FPSpreadsheet reconstructs the string formula automatically.

Please note that the order of calculation is defined by the order of tokens in the RPN formula. The RPN formula by itself does not require parentheses as they would be needed for string formulas. However, this can cause problems when reconstructing string formulas from RPN formulas. For example, suppose the formula "=(1+2)*(2+3)". This is parsed to the token sequence [1], [2], [*], [2], [3], [*], [+] which makes sure that the correct order of operations is used. When the formula is reconstructed by ReadRPNFormulaAsString, however, it will be displayed as "=1+2*2+3" which obviously is not correct. To avoid this problem Excel provides a particular "parenthesis" token. In fpspreadsheet, add a fekParen token to the token array to put the preceding expression in parenthesis:

  MyWorksheet.WriteRPNFormula(0, 0, CreateRPNFormula(
    RPNNumber(1,
    RPNNumber(2,
    RPNFunc(fekAdd,
    RPNParen,        // <--- this sets the parenthesis around the term (1+2)
    RPNNumber(2,
    RPNNumber(3,
    RPNFunc(fekAdd,
    RPNParen,        // <--- and this is the parenthesis around (2+3)
    RPNFunc(fekMul,
    nil))))))))));

It should be emphasized again that the parenthesis token does not have an effect on the calculation result, only on the reconstructed string formula.

Shared formulas

In spreadsheet applications, the same kind of formula can be copied to other cells by dragging it into adjacent cells. The resulting formula is stored only in the first cell, and the other cells contain only a link to this "base cell" of the formula.

These shared formulas are now supported by FPSpreadsheet as well. A rectangular range of cells is allowed to share a formula which is specified in the top/left corner of this range.

Creation of a shared formula is very easy, similar to writing an ordinary formula. The only difference is that you specify the range in which the formula is valid, instead of a single cell. There are two overloaded methods, one specifies the cell range by the row/column coordinates of the the top/left and right/bottom corners, the other one takes a string with an Excel-like range-address (such as A1:C5).

type
  TsWorksheet = class
    // ...
    procedure WriteSharedFormula(ARow1, ACol1, ARow2, ACol2: Cardinal; const AFormula: String); overload;
    procedure WriteSharedFormula(ACellRange: String; const AFormula: String); overload;
    // Currently there are not overloaded versions with rpn formulas
    // ...

The formula is provided as a string formula with relative cell addresses as seen from the top/left corner of the range; absolute cell addresses are taken literally. Suppose, as an example, that we want to use a shared formula in the top five cells of column B which adds 100 to the cells immediately to the left of this range. The top/left corner of this range is cell B1. Seen from there the cell immediately to the left is cell A1. Therefore, the shared formula is =A1+100, the cell range sharing this formula is B1:B5:

  MyWorksheet.WriteSharedFormula('B1:B5', '=A1+100');

While shared formulas are fully supported by FPSpreadsheet you must know that this concept is not known to the sfExcel2 and sfOpenDocument file formats. Nevertheless, a shared formula can still be written to these formats because the shared formula is copied into the single cells of the range with adapted relative cell addresses: in above example the shared formula is replaced by

  MyWorksheet.WriteSharedFormula(0, 1, '=A1+100');   // row=0, col=1 --> cell B1
  MyWorksheet.WriteSharedFormula(1, 1, '=B1+100');   // row=1, col=1 --> cell B2
  MyWorksheet.WriteSharedFormula(2, 1, '=C1+100');   // row=2, col=1 --> cell B3
  MyWorksheet.WriteSharedFormula(3, 1, '=D1+100');   // row=3, col=1 --> cell B4
  MyWorksheet.WriteSharedFormula(4, 1, '=E1+100');   // row=4, col=1 --> cell B5

Note: For simplification of cell references, shared formula blocks are split into individual cells if rows or columns are added to or deleted from an existing worksheet.

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 a separate document.

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

Extending FPSpreadsheet by user-defined formulas

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

procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
  const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;

procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
  const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;

• AName specifies the name under which the function will be called in the spreadsheet. It must match the name of the formula in the Office application.
• AResultType is a character which identifies the data type of the function result:
  • 'F' - floating point number
  • 'I' - integer
  • 'D' - date/time
  • 'B' - boolean
  • 'S' - string
• AParamTypes is a string in which each character identifies the data type of the corresponding argument. In addition to the list shown above the following symbols can be used:
  • '?' - any type
  • '+' - must be the last character. It means that the preceding character is repeated indefinitely. This allows for an arbitrary argument count. Please note, however, that Excel supports only up to 30 arguments.
  • lowercase 'f', 'i', 'd', 'b', 's' indicate optional parameters of the type explained above. Of course, uppercase symbols cannot follow lower-case symbols.
• AExcelCode is the identifier of the function in xls files. See "OpenOffice Documentation of the Microsoft Excel File Format", section 3.11, for a list.
• ACallback identifies which function is called by FPSpreadsheet for calculation of the formula. It can either be a procedure or an event handler.

  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:

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

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

  TsExprParameterArray = array of TsExpressionResult;

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

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

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

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

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:

• using built-in number formats by specifiying a value for the NumberFormat of the cell
• using a custom format string.

Number formats can be specified by these worksheet methods:

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;  

    // 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;     
  ...

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

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

• nfGeneral corresponds to the default formatting showing as many decimals as possible (the number 3.141592654 would be unchanged.)
• nfFixed limits the decimals. The number of decimal places has to be specified in the call to WriteNumber. Example: with 2 decimals, the number 3.141592654 becomes 3.14.
• nfFixedTh: similar to nfFixed, but adds a thousand separator when the number is displayed as a string: The number 3.141592654 would remain like in the previous example because it is too small to show thousand separators. But the number 314159.2654 would become 314,159.26, for 2 decimals.
• nfExp selects exponential presentation, i.e. splits off the exponent. The parameter ADecimals in WriteNumber determines how many decimal places are used. (The number 3.141592654 becomes 3.14E+00 in case of two decimals).
• nfPercentage displays the number as a percentage. This means that the value is multiplied by 100, and a percent sign is added. Again, specify in ADecimals how many decimal places are to be shown. (The number 3.141592654 is displayed as 314.92%, in case of 2 decimals).
• nfCurrency displays the number together with a currency symbol, and there are special rules how to display negative values (in brackets, or minus sign before or after the number). The FormatSettings of the workbook are used to define the currency sign and the way numbers are displayed (FormatSettings.CurrencyString for the currency symbol, FormatSettings.CurrencyFormat for positive, FormatSettings.NegCurrFormat for negative values). These settings can be overridden by specifying them in the call to WriteCurrency directly.
• nfCurrendyRed like nfCurrency, in addition negative values are displayed in red.
• nfShortDateTime presents the DateTimeValue of the cell in "short date/time format", i.e. days + two digit months + two digit year + hours + minutes, no seconds. The order of the date parts is taken from the workbook's FormatSettings. This applies also to the other date/time formats.
• nfShortDate creates a date string showing day + two-digit month + two-digit year
• nfShortTime creates a time string showing hours + minutes.
• nfLongTime, similar, but includes seconds as well
• nfShortTimeAM, similar to nfShortTime, but uses the AM/PM time format, i.e. hours go up to 12, and AM or PM is added to specify morning or evening/afternoon.
• nfLongTimeAM, like nfShortTimeAM, but includes seconds
• nfTimeInterval, like nfLongTime, but there can be more than 24 hours. The interval can also be expressed in minutes or seconds, if the format strings [n]:ss, or [s], respectively, are used.
• nfCustom allows to specify a dedicated formatting string.

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.

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 the syntax of fpc's FormatFloat and FormatDateTime commands (see the online-help for these functions).

Here are two examples:

• A date/time cell is to be displayed such that it shows only "month" (full name) and "year" (2 digits); in this case the format string would be
     mmmm/yy
  Note that the slash character (/) will be replaced by the DateSeparator of the workbook's FormatSettings.
• A number cell should use the exponential format with three decimal places and at least two digits in the exponent for positive numbers. For negative numbers and the zero value, the text "not defined" should be shown:
     0.000E+00;"not defined";"not defined"
  (it is a little-known feature of fpc's FormatFloat function that the format string can be separated by semicolons into sections applied to positive, negative and zero values).

Colors

FPSpreadsheet supports colors for text and for cell background. The color is specified by means of an index (type TsColor) into a palette. There are descriptive constants for the indexes into the default palette, but the numerical value can be used as well:

const
  scBlack = $00;
  scWhite = $01;
  scRed = $02;
  scGreen = $03;
  scBlue = $04;
  scYellow = $05;
  scMagenta = $06;
  scCyan = $07;
  scDarkRed = $08;
  scDarkGreen = $09;
  scDarkBlue = $0A;    scNavy = $0A;
  scOlive = $0B;
  scPurple = $0C;
  scTeal = $0D;
  scSilver = $0E;
  scGrey = $0F;        scGray = $0F;       // redefine to allow different kinds of writing
  scGrey10pct = $10;   scGray10pct = $10;
  scGrey20pct = $11;   scGray20pct = $11;
  scOrange = $12;
  scDarkbrown = $13;
  scBrown = $14;
  scBeige = $15;
  scWheat = $16;

In addition to the default palette, there are other palettes which are predefined in the units xlsbiff2, xlsbiff5, and xlsbiff8, PALETTE_BIFF2, PALETTE_BIFF5, PALETTE_BIFF8, respectively. The following code shows how to replace the current palette of the worksheet by PALETTE_BIFF8:

  MyWorksheet.UsePalette(@PALETTE_BIFF8, Length(PALETTE_BIFF8));

The palette entries are of type TsColorValue which is a synonym for DWord. They contain color rgb values given in little-endian byte order which is compatible with TColor of the graphics unit. Use the procedure MakeLEPalette(APalette: PsPalette; APaletteSize: Integer) to convert a "big-endian" type of byte-order to the required format. The color rgb value can be queried by calling GetPaletteColor(ColorIndex: TsColor). The following code snippet fills the background of a series of cells with a color which has the same index as the corresponding row; additionally the color name is displayed in the neighboring cell (correct only when the default palette is used); in this way a list of all available colors can be created:

  for i:=0 to MyWorkbook.GetPaletteSize-1 do begin
    MyWorksheet.WriteBlank(i, 0);
    Myworksheet.WriteBackgroundColor(i, 0, TsColor(i));
    MyWorksheet.WriteUTF8Text(i, 1, MyWorkbook.GetColorName(i));
  end;

The file created by this code is loaded into Excel and displayed above.

Palette items can be modified by calling SetPaletteColor. The following example replaces the color with index 10 by the color value $00123456

  MyWorksheet.SetPaletteColor(10, $00123456);

Note: You must be aware that the color names given above may lose their meaning after changing the default palette.
The various file formats have limitations in palette size: BIFF2 supports only 16 colors which cannot be changed. BIFF5 and BIFF8 allow up to 64 colors, the upper 56 can be modified by the user.

Cell background color

Call the worksheet method WriteBackgroundColor to set the color of a given cell. As described above, the color is specified by the index into the color palette of the workbook. The background color is used in a solid fill of the cell rectangle, other fill patterns are not supported. Note also that background colors are lost when saving in BIFF2 format (sfExcel2) and replaced by a shaded background pattern.

type
  TsWorksheet = class
  public
    procedure WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor);
    // ...
  end;

  // Example: set background color of cell A1 (row 0, column 0) to gray
  MyWorksheet.WriteBackgroundColor(0, 0, 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:

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:

  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:

type
  TsCellBorderStyle = record
    LineStyle: TsLineStyle;    // (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair = densely dotted)
    Color: TsColor;
  end;

  TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;   

  TsWorksheet = class
  public
    procedure WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder;
      AStyle: TsCellBorderStyle); overload;
    procedure WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder;
      ALineStyle: TsLineStyle; AColor: TsColor); overload;

    procedure WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor);
    procedure WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder;
      ALineStyle: TsLineStyle);
    procedure WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles);      

    procedure WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders);
    // ...
  end;

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

• WriteBorderStyle assigns a cell border style record to one border of the cell. There are two overloaded versions of this method: one takes an entire TsCellBorderStyle record, the other one takes the individual record elements.
• WriteBorderColor changes the color of a given border without affecting the line style of this border.
• WriteBorderLineStyle sets the line style of the border only, but leaves the color unchanged.
• WriteBorderStyles sets the border style of all borders of a given cell at once. Useful for copying border styles from one cell to other cells.

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

  MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]);    // cell A1: row 0, column 0
  MyWorksheet.WriteBorderStyle(0, 0, lsThin, scBlack);  
  MyWorksheet.WriteBorderStyle(0, 0, lsThick, scBlue);

  MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]);    // cell B1: row 0, column 1
  MyWorksheet.WriteBorderStyles(0, 1, MyWorksheet.FindCell(0, 0).BorderStyles); 
    // copy 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. }

Fonts

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

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

• The font name corresponds to the name of the font as used by the operational system. In Windows, an example would be "Times New Roman".
• The font size is given in "points", i.e. units 1/72 inch which are commonly used in Office applications.
• The font style is a set of the items fssBold, fssItalic, fssStrikeout, and fssUnderline which form the enumeration type TsFontStyle. The "normal" font corresponds to an empty set.
• The color, finally, is an index into the workbook's color palette discussed above and determines the foreground color of the text characters.

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:

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 assigns a font to the cell. If the font does not yet exist in the font list a new entry is created. The function returns the index of the font in the font list. In addition, there is an overloaded version which only takes the font index as a parameter.
• WriteFontColor replaces the color of the font that is currently assigned to the cell by a new one. Again, a new font list item is created if the font with the new color does not yet exist. The function returns the index of the font in the list.
• WriteFontSize replaces the size of the currently used font of the cell.
• WriteFontStyle replaces the style (normal, bold, italic, etc.) of the currently used cell font.

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:

var
  cell: PCell;
  font: TsFont;
begin
  cell := MyWorksheet.GetFirstCell;
  while cell <> nil then begin
    font := MyWorkbook.GetFont(cell^.FontIndex);
    if (font.FontName = 'Arial') and (font.Size = 10.0) then
      MyWorksheet.WriteFontSize(cell^.Row, cell^.Col, 9.0);
      // Don't modify font.Size directly because this will not update the font list!
    cell := MyWorksheet.GetNextCell;
  end;
end;

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

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

  TsWorksheet = class
  public
    procedure WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation);
    // ...
  end;

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

Please be aware that 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:

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

  TsWorkbook = class
  public
    procedure WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment);
    procedure WriteVertAlignment(ARow, ACol: Cardinal; AValue: 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:

type
  TsWorksheet = class
  public
    procedure WriteWordwrap(ARow, ACol: Cardinal; AValue: 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:

  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. Except for this corner cell, there must not be any other cells in the range. If there are their contents will be lost.

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:

  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 Excel2 and Excel5 which do not support this feature natively.

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'):

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:

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;

• The boolean value SortByCols determines wether sorting occurs along columns (true) or rows (false). The ColRowIndex specified in the sorting keys, accordingly, corresponds to a column or row index, respectively (see below).
• Priority determines in mixed content cell ranges whether an ascending sort puts numerical values in front of text values or not. Empty cells are always moved to the end of the sorted column or row. In Excel, the priority is "numbers first" (spNumAlpha).
• The array Keys specifies multiple sorting parameters. They consist of the index of the column or row to be sorted (ColRowIndex) and a set of Options for sorting direction (spoDescending) and character case (spCaseInsensitive). If Options is empty, cell comparison is case-sensitive, and cells are arranged in ascending order. If two cells are found to be "equal" on the basis of the first key (sortParams.Keys[0]) comparison proceeds with the next conditions in the Keys array until a difference is found or all conditions are used up.

InitSortParams is a handy utility to initialize the sorting parameters:

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

The next code fragment shows a typical sorting call:

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;

Virtual mode

Since fpspreadsheet holds all data in memory, it may run out of memory if very large files (e.g. tens of thousands of rows) are to be saved to file. 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.

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 properties are VirtualRowCount and VirtualColCount.
• Write an event handler for the event OnWriteCellData of the workbook. 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.

If there is still a memory issue you can also activate the option boBufStream of the workbook's Options. In this case, temporary data are written to a "buffered stream" which keeps data in memory only until a predefined buffer size is filled and swaps to disk afterwards.

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 below.

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. In addition, if MultipleSheets is set to true it is possible to combine several sheets into individual worksheets of 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;

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
• 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 writing a line of code.
• TsWorksheetChartSource interfaces a worksheet to the TAChart library. It is not yet fully integrated into the environment of FPSpreadsheet controls.

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.

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, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;

const OUTPUT_FORMAT = sfExcel5;

var
  MyWorkbook: TsWorkbook;
  MyWorksheet: TsWorksheet;
  MyFormula: TsRPNFormula;
  MyDir: string;
begin
  // Initialization
  MyDir := ExtractFilePath(ParamStr(0));

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

  // Write some number cells
  MyWorksheet.WriteNumber(0, 0, 1.0);
  MyWorksheet.WriteNumber(0, 1, 2.0);
  MyWorksheet.WriteNumber(0, 2, 3.0);
  MyWorksheet.WriteNumber(0, 3, 4.0);

  // Write the formula E1 = A1 + B1
  // or, in RPN: A1, B1, +
  SetLength(MyFormula, 3);
  MyFormula[0].ElementKind:=fekCell; {A1}
  MyFormula[0].Col := 0;
  MyFormula[0].Row := 0;
  MyFormula[1].ElementKind:=fekCell; {B1}
  MyFormula[1].Col := 1;
  MyFormula[1].Row := 0;
  MyFormula[2].ElementKind:=fekAdd;;  {+}
  MyWorksheet.WriteRPNFormula(0, 4, MyFormula);

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

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

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

Opening an existing spreadsheet

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

MyWorkbook.ReadFromFile(AFileName, sfExcel5);

If ReadFromFile is called with only one parameter then it will use the extension to auto-detect the file format. In case of ambiguous formats like Excel 2-8 it will simply try various possibilities until one works. When run from the IDE an exception will be raised for each incorrect format; this does not occur at runtime. In the future it might make a more efficient check for the Excel format.

MyWorkbook.ReadFromFile(AFileName);

Writing a spreadsheet to file based on extension

Similar to the ReadFromFile routine, I've implemented a WriteToFile procedure (also with suggestions and enhancements from Felipe Monteiro de Carvalho) 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;

Converting a database to a spreadsheet

The easiest solution is to use #Dataset export.

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

program db5xls;
 
{$mode delphi}{$H+}
 
uses
  Classes, SysUtils, 
  // add database units
  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.WriteUTF8Text(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.WriteUTF8Text(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

Note: 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
      MyWorkbook.VirtualColCount := MyDatabase.FieldCount;

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

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

      // Write all cells to an Excel8 file
      // The data to be written are specified in the OnWriteCellData event handler.
      MyWorksheet.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: TObject; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
  end;

procedure TDataProvider.WriteCellData(Sender: TObject; 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

Note that this is only meta-code, not tested.

program ods2xls;
 
{$mode delphi}{$H+}
 
uses
  Classes, SysUtils, 
  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 a plain text files without metadata. Therefore, additional information for correct reading of writing from/to a worksheet. For this purpose, the global record CSVParams makes available fundamental settings:

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:

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;

Note: If you want to give the user the possibility to interactively modify the CSVParams record have a look at the 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.
• 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.x xls files.
• excel8demo, like excel2demo, but for Excel 97-2003 xls files.
• csvdemo, like excel2demo, but for CSV files.
• fpschart shows the application of the TsWorksheetChartSource 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.
• 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.
• ooxmldemo, like excel2demo, but for the new Excel xlsx files.
• opendocdemo, like excel2demo, but for OpenOffice/LibreOffice ods files
• other: simple commandline programs showing various aspects of the fpspreadsheet package. Have a look at readme.txt for more details.
• spready: an extended application of the TsWorksheetGrid showing spreadsheet files with formatting, editing of cells, etc. Note that this demo can be simplified by taking advantage of the FPSpreadsheet controls.
• wikitabledemo, like excel2demo, but for wiki table files. Note that the write example currently writes a format that the read example cannot understand.
• 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.

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

Incompatible changes

After version 1.4

• ---

SVN change log

• ---

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

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).

Stable releases

You can find all releases of FPSpreadsheet on sourceforge: https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/.

Version 1.4

The latest stable release is version 1.4.

Change log (with respect to v1.2/1.2.1)

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

• Version 1.4: http://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85298
• Version 1.2 and 1.2.1: http://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375

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.

Compiler options

Here is a list of conditional defines which can be activated in order to tweak some operating modes of the packages:

• 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 [1]. If this is not wanted, define FPS_DONT_USE_CLOCALE.

Support and Bug Reporting

The recommended place to discuss FPSpreadsheet and obtain support is asking in the Lazarus Forum: http://www.lazarus.freepascal.org/index.php/board,42.0.html

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

Current Progress

Support by file format

Progress by supported format:

Format	Multiple sheet support	Unicode support	Reader support	Writer support	Text	Number	String Formula	RPN Formula	Date/Time
CSV files	No	Yes +	Working ++	Working ++	Working ++	Working ++	N/A	N/A	Working ++
Excel 2.x	No	No *	Working **	Working	Working	Working	Working	Working ***	Working ****
Excel 5.0 (Excel 5.0 and 95)	Yes	No *	Working **	Working	Working	Working	Working	Working ***	Working ****
Excel 8.0 (Excel 97- 2003)	Yes	Yes	Working **	Working	Working	Working	Working	Working ***	Working ****
Microsoft OOXML	Yes	Yes	Working **	Working	Working	Working	Working ***	Working	Working ****
OpenDocument	Yes	Yes	Working **	Working	Working	Working	Working ***	Working	Working ****
Wikitable files (Mediawiki)	No	Yes	planned	Working ++	Working ++	Working ++	N/A	N/A	Working ++

(+) Depends on file.
(++) No "true" format support because the file does not containg formatting information. But the current format can be understood.
(*) 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	Bold	Border	Color support	Word-wrap	Col&Row size	Number format	Merged cells
CSV files	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	Working	Working	Working **	N/A	Working	Working	N/A
Excel 5.0 (Excel 5.0 and 95)	Working	Working	Working	Working	Working	Working	Working	Working	Working	N/A
Excel 8.0 (Excel 97 - XP)	Working	Working	Working	Working	Working	Working	Working	Working	Working	Working
Microsoft OOXML	Working	Working	Working	Working	Working	Working	Working	Working	Working	Working
OpenDocument	Working	Working	Working	Working	Working	Working	Working	Working	Working	Working
Wikitable (Mediawiki)	Working	N/A	Working	Working	Working	Working	Working	Working	N/A	Working

(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.

Progress of workbook/worksheet user-interface options

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

Format	Hide grid lines	Hide headers	Frozen Panes
Excel 2.x	Working	Working	not working
Excel 5.0 (Excel 5.0 and 95)	Working	Working	Working
Excel 8.0 (Excel 97 - XP)	Working	Working	Working
Microsoft OOXML	Working	Working	Working
OpenDocument	Working	Working	Working

Changelog

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

3 September 2014

• Version 1.2.1 released. This is a bugfix release for 1.2 that fixes a bug in spreadsheetGrid (mantis issue 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

• Office Automation
• CsvDocument

External Links

• Microsoft OLE Document Format - http://sc.openoffice.org/compdocfileformat.pdf
• Excel file format description - http://sc.openoffice.org/excelfileformat.pdf
• Excel xls and PowerPoint ppt file dumper written in Python - very handy to list all contents of BIFF files (e.g. ./xls-dump.py file.xls) - http://cgit.freedesktop.org/libreoffice/contrib/mso-dumper/. A similar application is the "BIFFExplorer" which can be found in the references folder of the fpspreadsheet installation.
• Icons used in the demo programs:
  • silk icons: http://www.famfamfam.com/lab/icons/silk/
  • fugue icons: http://p.yusukekamiyamane.com/