Sign in Join
Wiki copytemporary

FPSpreadsheet: what changed

Revision 3 to 4. Removed lines have a minus sign; added lines have a plus sign.

OldNewLine
1-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.
2-
3-Screenshot of spready demo program provided with fpspreadsheet showing an XLS file:
4-
5-![](/wiki-images/b/b7/Fpsgrid.png)
6-
7-## Documentation
8-
9-This wiki page covers the latest development/trunk version of FPSpreadsheet available via subversion. Please see section Stable releases for documentation on the stable version that you can download.
10-
11-## API Documentation
12-
13-### API Reference
14-
15-A help file in CHM format can be found in the FPSpreadsheet installation folder *docs*. If you did not yet install the package follow [<http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/docs/fpspreadsheet-api.chm>](http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/docs/fpspreadsheet-api.chm) to *fpspreadsheet-api.chm*.
16-
17-The second CHM file available in the folder *docs*, *fpspreadsheet-wiki.chm*, is a snapshot of the FPSpreadsheet-related wiki pages bundled into a single help file.
18-
19-### Basics
20-
21-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. In addition, cells can contain information on formatting, i.e. font style, background color, text alignment etc.
22-
23-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.
24-
25-Worksheets are bound together to form a **workbook** which represents the document of the spreadsheet application. The workbook also stores information that is needed from all worksheets, i.e. font list, cell and number format lists, etc.
26-
27-FPSpreadsheet follows this same structure - there is a `TCell`, a `TsWorksheet`, and a `TsWorkbook`.
28-
29-#### Workbook
30-
31-The class `TsWorkbook` is the main class visible to the user. It provides methods for reading data from and writing to files. The versatile structure of the library provides access to various popular file formats, like Excel *.xls* or *.xlsx*, or OpenOffice/LibreOffice *.ods*.
32-
33-The file format is specified by the type `TsSpreadsheetFormat` defined in the unit `fpstypes`
34-
35-``` pascal
36-type
37- TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML,
38- sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);
39-```
40-
41-where
42-
43-- `sfExcel2, sfExcel5, sfExcel8` stands for versions of the binary xls format used by Excel ("BIFF" = "Binary Interchange File Format") with `sfExcel8` being the most modern one.
44-- `sfOOXML` corresponds to the newer xlsx format introduced by Excel2007
45-- `sfExcelXML` is the xml format which was introduced by Microsoft for Office XP and 2003. Not very popular.
46-- `sfOpenDocument` is the spreadsheet format used by OpenOffice/LibreOffice; by default, the files have the extension *.ods*.
47-- `sfCSV` refers to comma-delimited text files (default extension *.csv*); they can be understood by any text editor and all spreadsheet programs, but do not contain formatting information.
48-- `sfHTML` denotes the standard HTML format as used in web browsers.
49-- `sfWikiTable_Pipes` and `sfWikiTable_WikiMedia` is the format used by tables in wiki websites.
50-- `sfUser` is needed to register a user-defined format. There are no plans to implement "ancient" file formats like Excel3.0/4.0 or Lotus. It is possible, however, to provide your own reading and writing classes to extend the functionality of FPSpreadsheet - see the section below on Adding new file formats
51-
52-When applying fpspreadsheet the first task is to create an instance of the workbook:
53-
54-``` pascal
55-var
56- MyWorkbook: TsWorkbook;
57-begin
58- MyWorkbook := TsWorkbook.Create;
59- ...
60-```
61-
62-Reading of spreadsheet files is accomplished (among others) by the workbook methods
63-
64-- `procedure ReadFromFile(AFileName: string):`\
65- Reads the file with the given name and automatically determines the correct file format.
66-- `procedure ReadFromFile(AFileName: string; AFormat: TsSpreadsheetFormat):`\
67- Reads the file, but assumes that the file format is as specified by `AFormat`.
68-
69-The following workbook methods can be used for saving to file:
70-
71-- `procedure WriteToFile(const AFileName: string; const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False)`:\
72- 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`:
73-- `procedure WriteToFile(const AFileName: String; const AOverwriteExisting: Boolean = False)`:\
74- ditto, but the file format is determined from the file extension provided (in case of Excel's xls the most recent version, `sfExcel8`, is used).
75-
76-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.
77-
78-Note: FPSpreadsheets provides specialized units 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:
79-
80-- *xlsbiff2*, *xlsbiff5* and *xlsbiff8* for the binary xls file formats `sfExcel2`, `sfExcel5` and `sfExcel8`, respectively,
81-- *xlsxOOXML* for the xlsx file format `sfOOXML` of Excel 2007 and later,
82-- *xlsXML* for the xml format of Excel XP and 2003,
83-- *fpsopendocument* for the file format `sfOpenDocument` of OpenOffice/LibreOffice,
84-- *fpsCSV* for text files with comma-separated values (csv),
85-- *fpsHTML* for HTML files,
86-- *wikitables* for `sfWikiTable_Pipes` and `sfWikiTable_WikiMedia`,
87-- or, simply add *fpsallformats* to get read/write support for all file formats supported.
88-
89-#### Worksheet
90-
91-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:
92-
93-``` pascal
94-var
95- MyWorkbook: TsWorkbook;
96- MyWorksheet: TsWorksheet;
97-begin
98- MyWorkbook := TsWorkbook.Create;
99- MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
100- // 'My_Table' is the "name" of the worksheet
101- ...
102-```
103-
104-Already existing worksheets can be accessed by using the `TsWorkbook` methods
105-
106-- `function GetFirstWorksheet: TsWorksheet`: retrieves the first worksheet of the workbook.
107-- `function GetWorksheetByIndex(AIndex: Cardinal): TsWorksheet`: returns the worksheet with the given index (starting at 0).
108-- `function GetWorksheetByName(AName: String): TsWorksheet`: returns the worksheet with the given name which was used when the worksheet was added.
109-
110-The count of already existing worksheets can be queried by calling `GetWorksheetCount`.
111-
112-#### Cell
113-
114-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.
115-
116-``` pascal
117-var
118- MyWorkbook: TsWorkbook;
119- MyWorksheet: TsWorksheet;
120-begin
121- MyWorkbook := TsWorkbook.Create;
122- MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
123- MyWorksheet.WriteNumber(0, 0, 1.0); // "A1" has row=0 and col=0
124- ...
125-```
126-
127-It is also possible to access cells directly by means of the methods `FindCell(ARow, ACol)` or `GetCell(ARow, ACol)` of the workbook. Both funtions exist also in an overloaded version to which the cell address can be passed in Excel notation: `FindCell(ACellStr: String)` or `GetCell(ACellStr: String)`. 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.
128-
129-``` pascal
130-if MyWorksheet.FindCell('A1') = nil then
131- WriteLn('Cell A1 does not exist.');
132-```
133-
134-##### The basic TCell record
135-
136-This is the declaration of the cell's data type:
137-
138-``` pascal
139-type
140- TCell = record
141- { Location of the cell }
142- Worksheet: TsWorksheet;
143- Col: Cardinal; // zero-based
144- Row: Cardinal; // zero-based
145-
146- { Index of format record }
147- FormatIndex: Integer;
148-
149- { Status flags }
150- Flags: TsCellFlags; // (cfHasComment, cfMerged, cfHyperlink, ...)
151-
152- { Cell content }
153- UTF8StringValue: String; // strings cannot be part of a variant record
154- case ContentType: TCellContentType of // must be at the end of the declaration
155- cctEmpty : (); // has no data at all
156- cctFormula : (); // UTF8StringValue is outside the variant record
157- cctNumber : (Numbervalue: Double);
158- cctUTF8String : (); // FormulaValue is outside the variant record
159- cctDateTime : (DateTimevalue: TDateTime);
160- cctBool : (BoolValue: boolean);
161- cctError : (ErrorValue: TsErrorValue);
162- end;
163- PCell = ^TCell;
164-```
165-
166-The field `ContentType` indicates which data type is stored in the cell:
167-
168-``` pascal
169-type
170- TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);
171-```
172-
173-According to this field the corresponding data can be found in the fields
174-
175-- `NumberValue` (for `ContentType=cctNumber`), or
176-- `UTF8StringValue` (for `ContentType=cctUTF8String`), or
177-- `DateTimeValue` (for `ContentType=cctDateTime`), or
178-- `BoolValue` (for `ContentType=cctBool`), i.e. `TRUE` or `FALSE`, or
179-- `ErrorValue` (for `ContentType=cctError`).
180-
181-Due to usage of a variant record most of these values are overlapping, i.e. modification of `NumberValue` affects also the other values. Therefore, always respect the `ContentType` when accessing the `TCell` record directly (the worksheet methods discussed below consider this automatically).
182-
183-The field `Flags` tells whether additional data are associated with the cell which are not included in the cell record usually to save memory:
184-
185-``` pascal
186-type
187- TsCellFlag = (cfHasComment, cfHyperlink, cfMerged, cfHasFormula, cf3dFormula);
188- TsCellFlags = set of TsCellFlag;
189-```
190-
191-- `cfHasComment`: A comment record can be found in the `Comments` of the worksheet.
192-- `cfHyperlink`: The cell is contains a hyperlink stored in the `Hyperlinks` of the worksheet.
193-- `cfMerged`: The cell belongs to a merged block and extends across several cells.
194-- `cfHasFormula`: The cell is associated with a formula which is stored in the `Formulas` of the worksheet.
195-- `cf3dFormula`: The formula associated with the cell contains elements referencing other sheets of the same workbook.
196-
197-> **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 by calling the function `HasFormula(cell)` for the cell to be queried (`cell: PCell`) in unit *fpsUtils*; this function checks the presence of the element `cfHasFormula` in the cell flags.
198-
199-##### How to add and read data to/from a cell
200-
201-Adding values to a cell is most easily accompished by using one of the `WriteXXXX` methods of the worksheet. The most important ones are:
202-
203-``` pascal
204-type
205- TsWorksheet = class
206- ...
207- { Writing of currency values }
208- function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
209- ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
210- ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
211- ANegCurrFormat: Integer = -1): PCell; overload;
212- procedure WriteCurrency(ACell: PCell; AValue: Double;
213- ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
214- ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
215- ANegCurrFormat: Integer = -1); overload;
216- function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
217- ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
218- procedure WriteCurrency(ACell: PCell; AValue: Double;
219- ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
220-
221- { Writing of date/time values }
222- function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
223- procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
224- function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
225- ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
226- procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
227- ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
228- function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
229- ANumFormatStr: String): PCell; overload;
230- procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
231- ANumFormatStr: String); overload;
232-
233- { Writing of number values }
234- function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
235- procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
236- function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
237- ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;
238- procedure WriteNumber(ACell: PCell; ANumber: Double;
239- ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;
240- function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
241- ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
242- procedure WriteNumber(ACell: PCell; ANumber: Double;
243- ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
244-
245- { Writing of string values }
246- function WriteText(ARow, ACol: Cardinal; AText: ansistring;
247- ARichTextParams: TsRichTextParams = nil): PCell; overload;
248- procedure WriteText(ACell: PCell; AText: String;
249- ARichTextparams: TsRichTextParams = nil); overload;
250-
251- // the old string methods "WriteUTF8Text" are deprecated now
252- ...
253-```
254-
255-Some of these methods exist in overloaded versions in which cell formatting parameters can be added together with the cell value. Correspondingly to writing, there is also a number of `worksheet` methods for reading the cell values:
256-
257-``` pascal
258-type
259- TsWorksheet = class
260- ...
261- { Reading cell content as a string }
262- function ReadAsText(ARow, ACol: Cardinal): string; overload;
263- function ReadAsText(ACell: PCell): string; overload;
264- function ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;
265-
266- { Reading cell content as a number }
267- function ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
268- function ReadAsNumber(ACell: PCell): Double; overload;
269- function ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;
270-
271- { Reading cell content as a date/time value }
272- function ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
273- function ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
274- ...
275-```
276-
277-##### Index to Cell format
278-
279-`FormatIndex` is the index of the cell format record. It describes the formatting attributes of a cell. These records are collected by an internal list of the workbook and are defined like this:
280-
281-``` pascal
282-type
283- TsCellFormat = record
284- FontIndex: Integer;
285- TextRotation: TsTextRotation;
286- HorAlignment: TsHorAlignment;
287- VertAlignment: TsVertAlignment;
288- Border: TsCellBorders;
289- BorderStyles: TsCellBorderStyles;
290- Background: TsFillPattern;
291- NumberFormatIndex: Integer;
292- NumberFormat: TsNumberFormat;
293- NumberFormatStr: String;
294- BiDiMode: TsBiDiMode; // bdDefault, bdLTR {left-to-right}, bdRTL {right-to-left)
295- Protection: TsCellProtection; // cpLockCell, cpHideFormulas
296- UsedFormattingFields: TsUsedFormattingFields;
297- //uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign, uffBiDi, uffProtection, uffDoNotPrint
298- end;
299-```
300-
301-- `FontIndex`: text font by specifying the index in the workbook's font list
302-- `TextRotation`: specifies whether the cell text is written horizontally or vertically
303-- `HorAlignment`: left-aligned, horizontally centered, or right-aligned text
304-- `VertAlignment`: top, bottom or vertically centered text
305-- `Border`: a set of flags indicating that - if set - a border line is drawn at the left, top, right, or bottom cell edge. The lines are drawn according to the `BorderStyles` which define the linestyle and color of the border.
306-- `Background`: a record defining the background fill of a cell (pattern style, pattern color, and background color - see chapter on cell background below).
307-- `NumberFormat` and `NumberFormatStr` specify how number or date/time values are formatted (e.g., number of decimal places, long or short date format, etc.).
308-- Only those format attributes for which a flag is set in the `UsedFormattingFields` are considered when formatting a cell. If a flag is not included then the corresponding attribute is ignored and replaced by its default value.
309-
310-For specifying a format for a given cell call the corresponding the worksheet method `WriteXXXX`, for retrieving a format call `ReadXXXX`. These methods usually get a pointer to the cell as a parameter, but there are also overloaded versions which accept the row and column index. Moreover, formatting styles can also be applied directly to the cell by using a record helper implemented in unit *fpsCell*.
311-
312-See cell formatting below for a more detailed description.
313-
314-#### Columns and rows
315-
316-Column and row records are added for each column and row having a non-default properties:
317-
318-``` pascal
319-type
320- TCol = record
321- Col: Cardinal;
322- Width: Single;
323- ColWidthType: TsColWidthType; // = (cwtDefault, cwtCustom)
324- FormatIndex: Integer;
325- Options: TsColRowOptions; // set of [croHidden, croPageBreak]
326- end;
327- PCol = ^TCol;
328-
329- TRow = record
330- Row: Cardinal;
331- Height: Single;
332- RowHeightType: TsRowHeightType; // = (rhtDefault, rhtCustom, rhtAuto)
333- FormatIndex: Integer;
334- Options: TsColRowOptions; // set of [croHidden, croPageBreak]
335- Hidden: Boolean;
336- PageBreak: Boolean;
337- end;
338- PRow = ^TRow;
339-```
340-
341-##### Column width / row height
342-
343-**Column widths** and **row heights** can be specified in a variety of units defined by the type `TsSizeUnits = (suChars, suLines, suMillimeters, suCentimeters, suPoints, suInches)`. `suChars` refers to the count of `0` characters fitting into the column width - this is the way how Excel defines column widths. `suLines` is the number of lines fitting into the row height. Both units are based on the character size of the workbook's default font. The other units are conventional physical length units (1 cm = 10 mm, 1 inch = 25.4 mm = 72 pt). Fractional values are accepted. The workbook and worksheets store lengths internally in millimeters (`MyWorkbook.Units`).
344-
345-The Office applications usually adjust the row heights automatically according to the font or text rotation of the cell content. This case is identified by `RowHeightType` having the value `rhtAuto`. Since the worksheet cannot calculate text size very accurately automatic row heights are not written by FPSpreadsheet; they are replaced by the **default row height**. The default row height is also used if a row is empty, i.e. does not contain any data cells. Its value can be changed by calling the worksheet's `WriteDefaultRowHeight()` or by using the worksheet property `DefaultRowHeight`. In `WriteDefaultRowHeight`, the units must be specified while in `DefaultRowHeight` they are assumed to be lines. Similarly, the **default column width** can be specified by `WriteDefaultColWidth()` or the property `DefaultColWidth` (in characters).
346-
347-In order to overrun automatic and default row heights call the worksheet method `WriteRowHeight()`. These row records are identified by `RowHeightType` having the value `rhtCustom`. In the same way the width of columns can be set to a specific value by calling `WriteColWidth()`. `ColWidthType` of these columns is `cwtCustom`.
348-
349-The height/width of a particular row/column can be retrieved by means of the methods `GetRowHeight` or `GetColWidth`. Note that these methods return the default row heights/column widths if there are no `TRow`/`TCol` records.
350-
351-``` pascal
352-type TsWorksheet = class
353- ...
354- { Set row height }
355- procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
356- { Set column width }
357- procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
358- { Set default row height }
359- procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
360- {Set default cokumn width }
361- procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);
362-
363- { Return row height }
364- function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
365- { Return column width }
366- function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
367- { Return default row height }
368- function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
369- { Return default column width }
370- function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;
371-
372- property DefaultRowHeight: Single; // in lines
373- property DefaultColWidht: Single; // in characters
374-```
375-
376-There are also overloaded versions of these methods which do not require the `AUnits` parameter. In this case, row heights are defined in terms of line count, and column widths are defined in terms of character count. Note that these variants are from previos versions and are deprecated now.
377-
378-##### Column and row formats
379-
380-The `FormatIndex` element of the row and column records format applied to the entire row or column. Together with cells, these formats are stored as `TsCellFormat` records in an internal workbook list, `FormatIndex` is the index of the format properties into this list. See details in section Cell formatting. **Row and column formats** are primarily applied to empty cells, but if a new cell is added it will automatically get the format of the row or column. (If both row and column have different formats then the row format will be used).
381-
382-Here is a listing of the worksheet methods available for column and row formatting:
383-
384-``` Pascal
385-type TsWorksheet = class
386- // Assign a format to a column or row
387- procedure WriteColFormatIndex(ACol: Cardinal; AFormatIndex: Integer);
388- procedure WriteRowFormatIndex(ARow: Cardinal; AFormatIndex: Integer);
389-
390- // Query the format of a column or row
391- function GetColFormatIndex(ACol: Cardinal): Integer;
392- function GetRowFormatIndex(ARow: Cardinal): Integer;
393-
394- // Returns the font assigned to a column or row
395- function ReadColFont(ACol: PCol): TsFont;
396- function ReadRowFont(ARow: PRow): TsFont;
397-
398- // Check whether any column or row uses a special format
399- function HasRowFormats: Boolean;
400- function HasColFormats: Boolean;
401-```
402-
403-##### Hidden columns / rows
404-
405-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**:
406-
407-``` pascal
408-type TsWorksheet = class
409- ...
410- { Hide column/row }
411- procedure HideCol(ACol: Cardinal);
412- procedure HideRow(ARow: Cardinal);
413-
414- { Show a previously hidden column/row }
415- procedure ShowCol(ACol: Cardinal);
416- procedure ShowRow(ARow: Cardinal);
417-
418- { Check whether column/row is hidden }
419- function ColHidden(ACol: Cardinal): Boolean;
420- function RowHidden(ARow: Cardinal): Boolean;
421-```
422-
423-##### Page breaks
424-
425-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:
426-
427-``` pascal
428-type TsWorksheet = class
429- ...
430- { Enforce a page break before the specified column/row }
431- procedure AddPageBreakToCol(ACol: Cardinal);
432- procedure AddPageBreakToRow(ARow: Cardinal);
433-
434- { Removes a page break previously added to a column/row }
435- procedure RemovePageBreakFromCol(ACol: Cardinal);
436- procedure RemovePageBreakFromRow(ARow: Cardinal);
437-
438- { Checks whether a page break is forced before the specified column/row }
439- function IsPageBreakCol(ACol: Cardinal): Boolean;
440- function IsPageBreakRow(ARow: Cardinal): Boolean;
441-```
442-
443-#### Events
444-
445-Worksheets and workbooks fire a series of events such as OnChangeCell, OnChangeFont, etc. The events usually are intended for interaction with the [[FPSpreadsheet tutorial: Writing a mini spreadsheet application|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. [[TsWorksheetGrid|WorksheetGrid]]`.OnEditingDone` instead of `WorksheetGrid.Worksheet.OnChangeCell`.
446-
447-### Formulas
448-
449-Two kinds of formulas are supported by FPSpreadsheet:
450-
451-- **String formulas**: These are written in strings just like in the office applications, for example `"=ROUND(A1+B1,0)"`. They are used internally in the files of Open/LibreOffice and Excel *.xlsx*.
452-- **RPN formulas** are used internally by the binary *.xls* Excel files. They are written in Reverse Polish Notation (RPN), for example: `A1, B1, Add, 0, ROUND`. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
453-
454-FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated.
455-
456-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.
457-
458-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`.
459-
460-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.
461-
462-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`.
463-
464-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`.
465-
466-The most general setting regarding formulas, therefore, is
467-
468-``` pascal
469- MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];
470-```
471-
472-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.
473-
474-#### String formulas
475-
476-String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is `WriteFormula`:
477-
478-``` pascal
479-var
480- MyWorksheet: TsWorksheet;
481-//...
482- MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
483- // By default, use dot as decimal and comma as list separator!
484-```
485-
486-A few notes on **syntax**:
487-
488-- 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.
489-- The case of the formula name is ignored.
490-- Spaces can be added for better readability, but they will be lost when saving.
491-- Strings must be enclosed in double quotes.
492-- The corner points of a cell range must be separated by a colon (":"), e.g. `A1:C3`. Unordered ranges will be rearranged when the formula is parsed, i.e. `C3:A1` becomes `A1:C3`.
493-- Links to other worksheets must follow the Excel syntax which separates the sheetname and cell address by a "!". An single cell, for example, can be linked by `Sheet1!A1`. An range of sheets must be placed before the cell or cell range, e.g. `Sheet1:Sheet2!A1:C3`. Note that the Open/LibreOffice syntax with a separating point and reference to the corner points of the 3d box (i.e., `Sheet1.A1:Sheet2.C3`) is not supported. Note also that the worksheet(s) to which the formula links must exist at the time when the formula is added; otherwise the link will be replaced by the error code `#REF!`, and the formula will not be usable even if the missing sheet is added later.
494-- Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
495-- 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.
496-
497-``` pascal
498-var
499- MyWorksheet: TsWorksheet;
500-//...
501- MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
502- // Because of the "true" the formula parser accepts the comma as decimal and the
503- // semicolon as list separator if the workbook's FormatSettings are set up like this.
504-```
505-
506-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.
507-
508-``` pascal
509-var
510- MyWorksheet: TsWorksheet;
511- cell: PCell;
512-//...
513- cell := MyWorksheet.FindCell(0, 1);
514- WriteLn('The formula in internal format is ', MyworkSheet.ReadFormula(cell));
515- WriteLn('The localized formula is ', MyWorksheet.ReadFormulaAsString(cell, true));
516-//-------------------------------------------------------------------
517-// For the previous example, this will result in the following output
518-The formula in internal format is ROUND(A1+B2+1.215,0)
519-The localized formula is ROUND(A1+B2+1,215;0)
520-```
521-
522-#### RPN formulas
523-
524-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|"RPN Formulas in FPSpreadsheet"]].
525-
526-#### Shared formulas and array formulas
527-
528-- **Shared formulas** are only supported for reading (from Excel files).
529-- **Array formulas** are not supported, currently.
530-
531-#### List of built-in formulas
532-
533-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 [[FPSpreadsheet: List of formulas|separate document "List of formulas"]].
534-
535-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.
536-
537-#### Extending FPSpreadsheet by user-defined formulas
538-
539-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*:
540-
541-``` pascal
542-procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
543- const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;
544-
545-procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
546- const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;
547-```
548-
549-- `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.
550-- `AResultType` is a character which identifies the data type of the function result:
551- - `'F'` - floating point number
552- - `'I'` - integer
553- - `'D'` - date/time
554- - `'B'` - boolean
555- - `'S'` - string
556- - `'C'` - cell address, e.g. 'A1'
557- - `'R'` - cell range address, e.g. 'A1:C3'
558-- `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:
559- - `'?'` - any type
560- - `'+'` - 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.
561- - lowercase `'f', 'i', 'd', 'b', 's'` indicate optional parameters of the type explained above. Of course, uppercase symbols cannot follow lower-case symbols.
562-- `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.
563-- `ACallback` identifies which function is called by FPSpreadsheet for calculation of the formula. It can either be a procedure or an event handler.
564-
565-``` pascal
566-type
567- TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
568- TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;
569-```
570-
571-The `TsExpressionResult` is a variant record containing result or argument data of several types:
572-
573-``` pascal
574-type
575- TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
576- rtCell, rtCellRange, rtError, rtAny);
577-
578- TsExpressionResult = record
579- Worksheet : TsWorksheet;
580- ResString : String;
581- case ResultType : TsResultType of
582- rtEmpty : ();
583- rtError : (ResError : TsErrorValue);
584- rtBoolean : (ResBoolean : Boolean);
585- rtInteger : (ResInteger : Int64);
586- rtFloat : (ResFloat : TsExprFloat);
587- rtDateTime : (ResDateTime : TDatetime);
588- rtCell : (ResRow, ResCol : Cardinal);
589- rtCellRange : (ResCellRange : TsCellRange);
590- rtString : ();
591- end;
592-
593- TsExprParameterArray = array of TsExpressionResult;
594-```
595-
596-As an example we show here the code for the `CONCATENATE()` formula which joins two or more strings:
597-
598-``` pascal
599-const
600- INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;
601-...
602- RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);
603-
604-procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
605-// CONCATENATE( text1, text2, ... text_n )
606-var
607- s: String;
608- i: Integer;
609-begin
610- s := '';
611- for i:=0 to Length(Args)-1 do
612- begin
613- if Args[i].ResultType = rtError then
614- begin
615- Result := ErrorResult(Args[i].ResError);
616- exit;
617- end;
618- s := s + ArgToString(Args[i]);
619- // "ArgToString" simplifies getting the string from a TsExpressionResult as
620- // a string may be contained in the ResString and in the ResCell fields.
621- // There is such a function for each basic data type.
622- end;
623- Result := StringResult(s);
624- // "StringResult" stores the string s in the ResString field of the
625- // TsExpressionResult and sets the ResultType to rtString.
626- // There is such a function for each basic data type.
627-end;
628-```
629-
630-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.
631-
632-#### Unsupported formulas
633-
634-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.
635-
636-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.
637-
638-### Cell formatting
639-
640-#### Number and date/time formats
641-
642-Numbers and date/time values can be displayed in a variety of formats. In FPSpreadsheet this can be achieved in two ways:
643-
644-- using **built-in number formats** by specifiying a value for the `NumberFormat` of the cell
645-- using a custom **format string**.
646-
647-Number formats can be specified by these worksheet methods:
648-
649-``` pascal
650-type
651- TsWorksheet = class
652- public
653- // Set number formats alone
654- function WriteNumberFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
655- const AFormatString: String = ''): PCell; overload;
656- procedure WriteNumberFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
657- const AFormatString: String = ''); overload;
658-
659- function WriteNumberFormat(ARow, ACol: Cardinal; ANumFormat: TsNumberFormat;
660- ADecimals: Integer; ACurrencySymbol: String = ''; APosCurrFormat: Integer = -1;
661- ANegCurrFormat: Integer = -1): PCell; overload;
662- procedure WriteNumberFormat(ACell: PCell; ANumFormat: TsNumberFormat;
663- ADecimals: Integer; ACurrencySymbol: String = '';
664- APosCurrFormat: Integer = -1; ANegCurrFormat: Integer = -1); overload;
665-
666- function WriteFractionFormat(ARow, ACol: Cardinal; AMixedFraction: Boolean;
667- ANumeratorDigits, ADenominatorDigits: Integer): PCell; overload;
668- procedure WriteFractionFormat(ACell: PCell; AMixedFraction: Boolean;
669- ANumeratorDigits, ADenominatorDigits: Integer); overload;
670-
671- // Set date/time formats alone
672- function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
673- const AFormatString: String = ''): PCell; overload;
674- procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
675- const AFormatString: String = ''); overload;
676-
677- // Set cell values and number formats in one call
678-
679- // number values
680- function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
681- AFormat: TsNumberFormat = nfGeneral; ADecimals: Byte = 2;
682- ACurrencySymbol: String = ''): PCell; overload;
683- procedure WriteNumber(ACell: PCell; ANumber: Double; AFormat: TsNumberFormat = nfGeneral;
684- ADecimals: Byte = 2; ACurrencySymbol: String = ''); overload;
685- function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
686- AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
687- procedure WriteNumber(ACell: PCell; ANumber: Double;
688- AFormat: TsNumberFormat; AFormatString: String); overload;
689-
690- // date/time values
691- function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
692- AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''): PCell; overload;
693- procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
694- AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''); overload;
695- function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
696- AFormatStr: String): PCell; overload;
697- procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
698- AFormatStr: String); overload;
699-
700- // currency values
701- function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
702- AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
703- ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
704- ANegCurrFormat: Integer = -1): PCell; overload;
705- procedure WriteCurrency(ACell: PCell; AValue: Double;
706- AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
707- ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
708- ANegCurrFormat: Integer = -1); overload;
709- function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
710- AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
711- procedure WriteCurrency(ACell: PCell; AValue: Double;
712- AFormat: TsNumberFormat; AFormatString: String); overload;
713- ...
714-```
715-
716-##### Built-in number formats
717-
718-The **built-in formats** are defined by the enumeration `TsNumberFormat`. In spite of its name, the elements cover both number and date/time values:
719-
720-``` pascal
721-type
722- TsNumberFormat = (
723- // general-purpose for all numbers
724- nfGeneral,
725- // numbers
726- nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
727- // currency
728- nfCurrency, nfCurrencyRed,
729- // dates and times
730- nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
731- nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
732- // other (using format string)
733- nfCustom);
734-```
735-
736-- **nfGeneral** corresponds to the default formatting showing as many decimals as possible (the number `3.141592654` would be unchanged.)
737-- **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`.
738-- **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.
739-- **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).
740-- **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).
741-- **nfFraction** presents a number as a fraction. Details (mixed fraction?, maximum digit count for numerator or denominator) for can be specified in the worksheet method `WriteFractionFormat`.
742-- **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.
743-- **nfCurrendyRed** like `nfCurrency`, in addition negative values are displayed in red.
744-- **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.
745-- **nfShortDate** creates a date string showing day + two-digit month + two-digit year
746-- **nfShortTime** creates a time string showing hours + minutes.
747-- **nfLongTime**, similar, but includes seconds as well
748-- **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.
749-- **nfLongTimeAM**, like `nfShortTimeAM`, but includes seconds
750-- **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.
751-- **nfCustom** allows to specify a dedicated formatting string.
752-
753-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.
754-
755-##### Number format strings
756-
757-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).
758-
759-Here is a basic list of the symbols used:
760-
761-| Symbol | Meaning | Format string: Number --> Output string |
762-|---|---|---|
763-| `General` | Displays all decimal places of the number | `'General'`: · 1.2345678 --> `'1.2345678'` |
764-| `0` | Displays insignificant zeros if a number has less digits than there are zeros in the format. If used for decimal places then the number is rounded to as many decimal places as 0s are found. | `'000'`: 1 --> `'001'` · `'0.0'`: 1 --> `'1.0'` · `'0.0'`: 1.2345678 --> `'1.2'` |
765-| `*` | Like "0" above, but does not display insignificant zeros. | `'0.*'`: 1 --> `'1.'` · `'0.*'`: 1.2345678 --> `'1.2'` |
766-| `?` | Like "0" above, but insignificant zeros are replaced by space characters. Good for aligning decimal points and fractions | `'??0'`: 1 --> `' 1'` · `'0.0??'`: 1 --> `'1.0 '` |
767-| `.` | Decimal separator; will be replaced by the value used in the `DecimalSeparator` of the workbook's `FormatSettings` | `'0.00'`: 8.9 --> `'8.90'` |
768-| `,` | Thousand separator; will be replaced by the value used in the `ThousandSeparator` of the workbook's `FormatSettings`. If at the end of a number formatting sequence the displayed value is divided by 1000. | `'#,##0.00'`: 1200 --> `'1,200.00'` · `'0.00,'`: 1200 --> `'1.20'` |
769-| `E+`, `e+` | Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown for positive and negative exponents. | `'0.00E+00'`: 1200 --> `1.20E+03` |
770-| `E-`, `e-` | Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown only for negative exponents. | `'0.00e-000'`: 1200 --> `1.20e003` |
771-| `%` | Displays the number as a "percentage", i.e. the number is multiplied by 100 and a `%` sign is added. | `'0.0%'`: 0.75 --> `75.0%` |
772-| `/` | This symbol has two meanings: if the cell represents a "number" then the slash indicates formatting as fraction, place holders for numerator and denominator must follow. If the cell represents a "date/time" then the slash indicates the date separator which will be replaced by the `DateSeparator` of the workbook's `FormatSettings` | `'#/#'`: 1.5 --> `'3/2'` · `'# #/#'`: 1.5 --> `'1 1/2'` · `'# #/16'`: 1.5 --> `'1 8/16'` · also: see date/time examples below |
773-| `:` | Separator between hours, minutes and seconds of a date/time value. Will be replaced by the `TimeSeparator` of the workbook's `FormatSettings`. | see examples below |
774-| `yyyy` | Place holder for the year of a date/time value. The year is displayed as a four-digit number. | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` · In this example, the `DateSeparator` is a dash character (`-`). |
775-| `yy` | Place holder for the year of a date/time value. The year is displayed as a two-digit number. | `'yy/mm/dd'`: · Jan 3, 2012 --> `'12-01-03'` |
776-| `m` | Place holder for the month of a date/time value. The month is shown as a number without extra digits. · Please note that the `m` code can also be interpreted as the "minutes" of a time value (see below). | `'yyyy/m/dd'`: · Jan 3, 2012 --> `'2012-1-03'` |
777-| `mm` | Place holder for the month of a date/time value. The month is shown as a two-digit number, i.e. a leading zero is added for January to September. · Please note that the `mm` code can also be interpreted as the "minutes" of a time value (see below). | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` |
778-| `mmm` | Place holder for the month of a date/time value. The month is displayed by its abbreviated name. | `'yyyy/mmm/dd'`: · Jan 3, 2012 --> `'2012-Jan-03'` |
779-| `mmmm` | Place holder for the month of a date/time value. The month is displayed by its full name. | `'yyyy/mmm/dd'`: · Jan 3, 2012 --> `'2012-January-03'` |
780-| `d` | Place holder for the day of a date/time value to be displayed as a number. The day is displayed as a simple number, without adding a leading zero. | `'yyyy/mm/d'`: · Jan 3, 2012 --> `'2012-01-3'` |
781-| `dd` | Place holder for the day of a date/time value to be displayed as a number. `dd` adds a leading zero to single-digit day numbers. | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` |
782-| `ddd` | Place holder for the day of a date/time value. The day is displayed as its abbreviated name. | `'dddd, yyyy/mm/ddd'`: · Jan 03, 2012 --> `'Tue 2012-01-03'` |
783-| `dddd` | Place holder for the day of a date/time value. The day is displayed as its full name. | `'dddd, yyyy/mmmm/dd'`: · Jan 03, 2012 --> `'Tuesday 2012-01-03'` |
784-| `h` | Place holder of the hour part of a date/time value. The hour is displayed as a simple number, without adding a leading zero. | `'h:mm'`: · 0.25 --> `'6:00'` |
785-| `hh` | Place holder of the hour part of a date/time value. The hour is displayed with a leading zero if the hour is less than 10. | `'hh:mm'`: · 0.25 --> `'06:00'` |
786-| `[hh]`, or `[h]` | Displays elapsed time such that the hour part can become greater than 23 | `'[h]:mm'`: · 1.25 --> `'30:00'` |
787-| `m` | Place holder of the minutes part of a date/time value. The minutes are shown as a simple number without adding a leading zero. Note that if the `m` codes are surrounded by date symbols (`y`, `d`) then they are interpreted as "month". | `'h:m'`: · 0.25 --> `'6:0'` |
788-| `mm` | Place holder of the minutes part of a date/time value. Single-digit minutes are displayed with a leading zero. Note that if the `mm` code is surrounded by date symbols (`y`, `d`) then it is interpreted as "month". | `'h:mm'`: · 0.25 --> `'6:00'` |
789-| `[mm]`, or `[m]` | Displays elapsed time such that the minute part can become greater than 59 | `'[mm]:ss'`: · 1.25 --> `'1800:00'` |
790-| `s` | Place holder of the seconds part of a date/time value. The seconds are displayed as a simple number, without adding a leading zero. | `'hh:mm:s'`: · 0.25 --> `'06:00:0'` |
791-| `ss` | Place holder of the seconds part of a date/time value. Single-digit seconds are displayed with a leading zero. | `'hh:mm:ss'`: · 0.25 --> `'06:00:00'` |
792-| `[ss]`, or `[s]` | Displays elapsed time such that the seconds part can become greater than 59 | `'[ss]'`: · 1.25 --> `'108000'` |
793-| `AM/PM`, `am/pm`, `A/P`, or `a/p` | Displays the time in the 12-hour format. | `'hh:mm:ss AM/PM'`: · 0.25 --> `'6:00:00 AM'` |
794-| `"` | The text enclosed by quotation marks is inserted into the formatted strings literally. | `'yyyy"/"mm"/"dd'`: · Jan 3, 2012 --> `'2012/01/03'` (i.e. the `/` is not replaced by the `DateSeparator` of the workbook). |
795-| `\` | The next character of the format string appears in the result string literally. The `\` itself does not show up. | `'yyyy\/mm\/dd'`: · Jan 3, 2012 --> `'2012/01/03'` |
796-| `;` | A format string can contain up to three sections separated by the semicolon. The first section is used for positive numbers, the second section for negative numbers, and the third section for zero numbers. If the third section is missing then a zero value is formatted as specified in the first section. If the second section is missing as well then all values are formatted according to the first section. | `'"#,##0"$"';-#,##0"$";"-"'`: · 1200 --> `'1,200$'` · -1200 --> `'1,200$'` · 0 --> `'-'` |
797-| `(`, and `)` | Sometimes used for currency values to indicate negative numbers, instead of minus sign | `'#,##0"$";(#,##0)"$"'`: · -1200 --> `'(1200)$'` |
798-| `[red]` | The formatted string is displayed in the specified color. Instead of `[red]`, you can use accordingly `[black]`, `[white]`, `[green]`, `[blue]`, `[magenta]`, `[yellow]`, or `[cyan]`. Often used to highlight negative currency values. | `'"$" #,##0.00;[red]("$" #,##0.00)'`: · -1200 --> '`($ 1200.00)`' |
799-
800-##### A note on Excel dates
801-
802-There is a discrepancy between FPSpreadsheet and Excel presentation of dates: Excel incorrectly assumes that the year 1900 was a leap year and thus allows for the date Febr 29 1900. FPSpreadsheet does the date calculation correctly. As consequence, dates before March 1 1900 are off by one! BTW, FPSpreadsheet handles this issue in the same way as LibreOffice Calc.
803-
804-#### Colors
805-
806-FPSpreadsheet supports colors for text, cell background, and cell borders. The basic EGA colors are declared in unit *fpstypes* as constants:
807-
808-![](/wiki-images/7/7b/DefaultPalette.png)
809-
810-``` pascal
811-type
812- TsColor = DWORD;
813-
814-const
815- scBlack = $00000000;
816- scWhite = $00FFFFFF;
817- scRed = $000000FF;
818- scGreen = $0000FF00;
819- scBlue = $00FF0000;
820- scYellow = $0000FFFF;
821- scMagenta = $00FF00FF;
822- scCyan = $00FFFF00;
823- scDarkRed = $00000080;
824- scDarkGreen = $00008000;
825- scDarkBlue = $00800000;
826- scOlive = $00008080;
827- scPurple = $00800080;
828- scTeal = $00808000;
829- scSilver = $00C0C0C0;
830- scGray = $00808080;
831- scGrey = scGray; // redefine to allow different spelling
832-
833- // Identifier for undefined color
834- scNotDefined = $40000000;
835-
836- // Identifier for transparent color
837- scTransparent = $20000000;
838-```
839-
840-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.)
841-
842-The high order byte is usually zero but is used internally to identify special color values, such as for undefined or transparent colors.
843-
844-> **Note:** In older versions of the library colors were defined as indexes into a color palette. **THIS IS NO LONGER WORKING.**
845-
846-Unit *fpsutils* contains some useful functions for **modification of colors**:
847-
848-- `function GetColorName(AColor: TsColor): String;`\
849- returns the name of the colors defined above, or a string showing the rgb components for other colors.
850-- `function HighContrastColor(AColor: TsColor): TsColor;`\
851- returns `scBlack` for a "bright", `scWhite` for a "dark" input color.
852-- `function TintedColor(AColor: TsColor; tint: Double): TsColor;`\
853- brightens or darkens a color by applying a factor `tint = -1..+1`, where `-1` means "100% darken", `+1` means "100% brighten", and `0` means "no change". The hue of the color is preserved.
854-
855-#### Cell background
856-
857-The cell background can be filled by predefined patterns which are identified by the record `TsFillPattern`:
858-
859-``` pascal
860-type
861- TsFillPattern = record
862- Style: TsFillStyle; // fill style pattern as defined below
863- FgColor: TsColor; // forground color of the fill pattern
864- BgColor: TsColor; // background color of the fill pattern
865- end;
866-
867- TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
868- fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
869- fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
870- fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
871-```
872-
873-- Use the worksheet method `WriteBackground` to assign a fill pattern to a specific cell. Besides the cell address, this method requires the type of the fill pattern (`TsFillStyle`), and the foreground and background colors as specified by their `TsColor` values.
874-- The fill pattern of a particular cell can be retrieved by calling the workbook method `ReadBackground`.
875-- The simplified method `WriteBackgroundColor` can be used to achieve a uniform background color.
876-- **Limitations:**
877- - OpenDocument files support only uniform fills. The background color is a mixture of the foreground and background rgb components in a ratio defined by the fill pattern.
878- - BIFF2 files support only a 12.5% black-and-white shaded pattern.
879-
880-``` pascal
881-type
882- TsWorksheet = class
883- public
884- function WriteBackground(ARow, ACol: Cardinal; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor): PCell; overload;
885- procedure WriteBackground(ACell: PCell; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor); overload;
886-
887- function WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor): PCell; overload;
888- procedure WriteBackgroundColor(ACell: PCell; AColor: TsColor); overload;
889-
890- function ReadBackground(ACell: PCell): TsFillPattern;
891- function ReadBackgroundColor(ACell: PCell): TsColor; overload;
892- // ...
893- end;
894-
895-var
896- cell: PCell;
897-...
898- // Example 1: Assign a pattern of thin, horizontal, yellow stripes on a blue background to empty cell A1 (row 0, column 0)
899- MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);
900-
901- // Example 2: Uniform gray background color of cell B1 (row 0, column 1) containing the number 3.14
902- cell := MyWorksheet.WriteNumber(0, 1, 3.14);
903- MyWorksheet.WriteBackgroundColor(cell, clSilver);
904-```
905-
906-#### Cell borders
907-
908-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`:
909-
910-``` pascal
911-type
912- TsCellBorder = (cbNorth, cbWest, cbEast, cbSouth, cbDiagUp, cbDiagDown);
913- TsCellBorders = set of TsCellBorder;
914-```
915-
916-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:
917-
918-``` pascal
919- MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // A1: row 0, column 0
920- MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: row 0, column 1
921-```
922-
923-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:
924-
925-``` pascal
926-type
927- TsLineStyle = (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair,
928- lsMediumDash, lsDashDot, lsMediumDashDot, lsDashDotDot, lsMediumDashDotDot, lsSlantDashDot);
929-
930- TsCellBorderStyle = record
931- LineStyle: TsLineStyle;
932- Color: TsColor;
933- end;
934-
935- TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;
936-
937- TsWorksheet = class
938- public
939- function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; AStyle: TsCellBorderStyle): PCell; overload;
940- procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; AStyle: TsCellBorderStyle); overload;
941-
942- function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor): PCell; overload;
943- procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor); overload;
944-
945- function WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
946- procedure WriteBorderColor(ACell: PCell; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
947-
948- function WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
949- procedure WriteBorderLineStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
950-
951- function WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles): PCell; overload;
952- procedure WriteBorderStyles(ACell: PCell; const AStyles: TsCellBorderStyles); overload;
953-
954- function WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders): PCell; overload
955- procedure WriteBorders(ACell: PCell; ABorders: TsCellBorders); overload
956-
957- function ReadCellBorders(ACell: PCell): TsCellBorders;
958- function ReadCellBorderStyle(ACell: PCell; ABorder: TsCellBorder): TsCellBorderStyle;
959- function ReadCellBorderStyles(ACell: PCell): TsCellBorderStyles;
960- ...
961- end;
962-```
963-
964-The style of a given cell border can be specified by the following methods provided by the worksheet:
965-
966-- **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.
967-- **WriteBorderColor** changes the color of a given border without affecting the line style of this border.
968-- **WriteBorderLineStyle** sets the line style of the border only, but leaves the color unchanged.
969-- **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.
970-
971-This example adds a thin black border to the top, and a thick blue border to the bottom of cells A1 and B1:
972-
973-``` pascal
974-var
975- cellA1, cellB1: PCell;
976-...
977- cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // cell A1: row 0, column 0
978- MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);
979- MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);
980-
981- cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]); // cell B1: row 0, column 1
982- MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles); // copy all border styles from cell A1 to B1
983-```
984-
985-> **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.
986-
987-#### Fonts
988-
989-The cell text can displayed in various fonts. For this purpose, the workbook provides a list of `TsFont` items:
990-
991-``` pascal
992-type
993- TsFont = class
994- FontName: String;
995- Size: Single;
996- Style: TsFontStyles;
997- Color: TsColor;
998- Position: TsFontPosition;
999- end;
1000-```
1001-
1002-- The **FontName** corresponds to the name of the font as used by the operational system. In Windows, an example would be "Times New Roman".
1003-- The **FontSize** is given in "points", i.e. units 1/72 inch which are commonly used in Office applications.
1004-- The **FontStyle** 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.
1005-- The **Color** determines the foreground color of the text characters given in rgb presentation as discussed above.
1006-- The **Position** is either `fpNormal`, `fpSuperscript`, or `fpSubscript` and indicates whether the font size should be decreased by about 1/3 and the characters should be displaced up (superscript) or down (subscript).
1007-
1008-Every cell is provided with an **index into the font list**.
1009-
1010-In order to assign a particular font to a cell, use one of the following methods of `TsSpreadsheet`:
1011-
1012-``` pascal
1013-type
1014- TsSpreadsheet = class
1015- public
1016- function WriteFont(ARow, ACol: Cardinal; const AFontName: String;
1017- AFontSize: Single; AFontStyle: TsFontStyles; AFontColor: TsColor): Integer; overload;
1018- procedure WriteFont(ARow, ACol: Cardinal; AFontIndex: Integer); overload;
1019- function WriteFontColor(ARow, ACol: Cardinal; AFontColor: TsColor): Integer;
1020- function WriteFontSize(ARow, ACol: Cardinal; ASize: Integer): Integer;
1021- function WriteFontStyle(ARow, ACol: Cardinal; AStyle: TsFontStyles): Integer;
1022- // plus: overloaded versions accepting a pointer to a cell record instead of the row and column index as parameter
1023- // ...
1024- end;
1025-```
1026-
1027-- **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.
1028-- **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.
1029-- **WriteFontSize** replaces the size of the currently used font of the cell.
1030-- **WriteFontStyle** replaces the style (normal, bold, italic, etc.) of the currently used cell font.
1031-
1032-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.
1033-
1034-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`.
1035-
1036-Here is an **example** which decreases the size of all 10-point "Arial" fonts to 9-point:
1037-
1038-``` pascal
1039-var
1040- i: Integer;
1041- font: TsFont;
1042-begin
1043- for i := 0 to MyWorkbook.GetFontCount-1 do
1044- begin
1045- font := MyWorkbook.GetFont(i);
1046- if (font.FontName = 'Arial') and (font.Size = 10.0) then
1047- font.Size := 9.0;
1048- end;
1049-end;
1050-```
1051-
1052-#### Rich-text formatting
1053-
1054-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).
1055-
1056-For this purpose, unit *fpstypes* declares the type `TsRichTextParams` which is an array of `TsRichTextParam` records:
1057-
1058-``` pascal
1059-type
1060- TsRichTextParam = record
1061- FontIndex: Integer;
1062- FirstIndex: Integer;
1063- end;
1064-
1065- TsRichTextParams = array of TsRichTextParam;
1066-```
1067-
1068-`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.
1069-
1070-There are two ways to add "rich-text" formatting to a cell text:
1071-
1072-- Embed corresponding HTML format codes into the cell text. This can be done using the method `WriteTextAsHTML` of the worksheet. In order to add the text "Area (m<sup>2</sup>)" to cell A1, pass the following HTML-coded string to this function
1073-
1074-``` pascal
1075- MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
1076-```
1077-
1078-- Alternatively, the standard text writing method, `WriteText` can be called with an additional parameter specifiying the rich-text formatting parameters to be used directly:
1079-
1080-``` pascal
1081-var
1082- richTextParams: TsRichTextParams;
1083- fnt: TsFont;
1084- cell: PCell;
1085-begin
1086- SetLength(rtp, 2);
1087- cell := MyWorksheet.GetFont(0, 0);
1088- fnt := MyWorksheet.ReadCellFont(cell);
1089- richTextParams[0].FirstIndex := 8; // The superscript groups begins with "2" which is the (1-based) character #8 of the cell text.
1090- richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
1091- richTextParams[1].FirstIndex := 9; // Normal font again beginning with character #9.
1092- richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
1093- MyWorksheet.WriteUTF8Text(cell, 'Area (m2)', richTextParams);
1094-end;
1095-```
1096-
1097-Use the worksheet method `DeleteRichTextParams` to remove rich-text formatting from a previously formatted cell.
1098-
1099-> **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.
1100-
1101-#### Text rotation
1102-
1103-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.
1104-
1105-If you need this feature use the worksheet method `WriteTextRotation` and specify the text direction by an element of the enumeration type `TsTextRotation`:
1106-
1107-``` pascal
1108-type
1109- TsTextRotation = (trHorizontal, rt90DegreeClockwiseRotation,
1110- rt90DegreeCounterClockwiseRotation, rtStacked);
1111-
1112- TsWorksheet = class
1113- public
1114- function WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation): PCell; overload;
1115- procedure WriteTextRotation(ACell: PCell; ARotation: TsTextRotation); overload;
1116-
1117- function ReadTextRotation(ACell: PCell): TsTextRotation;
1118- // ...
1119- end;
1120-
1121- // example for counter-clockwise rotated text in cell A1:
1122- WriteTextRotation(0, 0, rt90DegreeCounterClockwiseRotation);
1123-```
1124-
1125-> **Warning:** Finer degrees of rotation which may be supported by some spreadsheet file formats are ignored.
1126-
1127-#### Text alignment
1128-
1129-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`:
1130-
1131-``` pascal
1132-type
1133- TsHorAlignment = (haDefault, haLeft, haCenter, haRight, haJustified, haDistributed, haFilled);
1134- TsVertAlignment = (vaDefault, vaTop, vaCenter, vaBottom);
1135-
1136- TsWorksheet = class
1137- public
1138- function WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment): PCell; overload;
1139- procedure WriteHorAlignment(ACell: PCell; AValue: TsHorAlignment); overload;
1140- function ReadHorAlignment(ACell: PCell): TsHorAlignment;
1141-
1142- function WriteVertAlignment(ARow, ACol: Cardinal; AValue: TsVertAlignment): PCell; overload;
1143- procedure WriteVertAlignment(ACell: PCell; AValue: TsVertAlignment); overload;
1144- function ReadVertAlignment(ACell: PCell): TsVertAlignment;
1145- // ...
1146- end;
1147-
1148- // Example: Center the text in cell A1 both horizontally and vertically
1149- MyWorksheet.WriteHorAlignment(0, 0, haCenter);
1150- MyWorksheet.WriteVertAlignment(0, 0, vaCenter);
1151-```
1152-
1153-Explanation of special horizontal alignments:
1154-
1155-- `haDistributed`: The cell text is word-wrapped and its spaces are expanded so that the entire width of the cell is occupied. The text is both left- and right-justified.
1156-- `haJustified`: like `haDistributed`, expect for the last line which remains left-justified.
1157-- `haFilled`: the text is repeated to fill the entire cell.
1158-
1159-#### Word wrap
1160-
1161-Text which is longer than the width of a cell can wrap into several lines by calling the method `WriteWordwrap` of the spreadsheet:
1162-
1163-``` pascal
1164-type
1165- TsWorksheet = class
1166- public
1167- function WriteWordwrap(ARow, ACol: Cardinal; AValue: Boolean): PCell; overload;
1168- procedure WriteWordwrap(ACell: PCell; AValue: Boolean); overload;
1169- function ReadWordwrap(ACell: PCell): Boolean;
1170- //...
1171- end;
1172-
1173- // Example: activate wordwrap in cell A1
1174- MyWorksheet.WriteWordwrap(0, 0, true);
1175-```
1176-
1177-#### Merged cells
1178-
1179-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:
1180-
1181-``` pascal
1182- MyWorksheet.MergeCells('A1:D5');
1183- // or: MyWorksheet.MergeCells(0, 0, 4, 3); // first row, first column, last row, last column
1184-```
1185-
1186-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.
1187-
1188-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:
1189-
1190-``` pascal
1191- MyWorksheet.UnmergeCells('B1');
1192- // or: MyWorksheet.UnmergeCells(0, 1); // row, column of any cell within the range
1193-```
1194-
1195-Merged cells can be read from/written to all file formats except for `sfCSV`, `sfExcel2` and `sfExcel5` which do not support this feature natively.
1196-
1197-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:
1198-
1199-``` pascal
1200-var
1201- cell, base: PCell;
1202- r1,c1,r2,c2: Cardinal;
1203-...
1204- cell := MyWorksheet.FindCell('B1');
1205- if MyWorksheet.IsMerged(cell) then
1206- begin
1207- WriteLn('Cell B1 is merged.');
1208-
1209- MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
1210- WriteLn('The merged range is ' + GetCellRangeString(r1, c1, r2, c2));
1211-
1212- base := MyWorksheet.FindMergeBase(cell);
1213- WriteLn('The merge base is cell ' + GetCellString(base^.Row, base^.Col));
1214- end;
1215-```
1216-
1217-#### Cell protection
1218-
1219-This is described in a separate section below.
1220-
1221-#### Miscellaneous
1222-
1223-##### Disable printing of specific cells
1224-
1225-Specific cells can be disabled from printing:
1226-
1227-``` pascal
1228-type
1229- TsWorksheet = class
1230- public
1231- function WriteDoNotPrintCell(ARow, ACol: Cardinal; AValue: boolean): PCell; overload;
1232- procedure WriteDoNotPrintCell(ACell: PCell; AValue: Boolean); overload;
1233- function ReadDoNotPrintCell(ACell: PCell): Boolean;
1234-```
1235-
1236-### Additional data
1237-
1238-#### Cell comments
1239-
1240-Comments can be attached to any cell by calling
1241-
1242-``` pascal
1243- MyWorksheet.WriteComment(0, 0, 'This is a comment for cell A1');
1244-```
1245-
1246-They are stored in an internal list of the worksheet. Use the corresponding worksheet methods to access comments:
1247-
1248-- If you want to know whether a particular cell contains a comment call the worksheet method `HasComment(cell)`.
1249-- For retrieving a cell comment use the method `ReadComment(cell)`, or its overloaded companion `ReadComment(ARow, ACol)`.
1250-- The total number of comments can be retrieved from `worksheet.Comments.Count`.
1251-
1252-#### Hyperlinks
1253-
1254-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
1255-
1256-``` pascal
1257- procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');
1258-```
1259-
1260-- The hyperlink target, passed as parameter `ATarget`, must be a fully qualified [URI (Uniform resource identifier)](http://en.wikipedia.org/wiki/Uniform_Resource_Identifier) consisting of a protocol phrase (e.g., [`http://`](http://), [`file:///`](file:///), `mailto:`, etc.) followed by specific information such as web URL, filename, or e-mail address and an optional bookmark identification separated by the character `'#'`. An exception are internal hyperlinks which enable to jump to a cell in the current workbook; they consist of the optional worksheet name and the cell address separated by the character `'!'`.
1261-- The optional `Tooltip` parameter is evaluated by Excel to display it in a hint window if the mouse if above the hyperlink.
1262-
1263-> **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.
1264-
1265-**Examples:**
1266-
1267-``` pascal
1268- // Open the web site www.google.com
1269- MyWorksheet.WriteText(0, 0, 'Open google');
1270- MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');
1271-
1272- // Open the local file with the absolute path "C:\readme.txt" (assuming Windows)
1273- MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');
1274-
1275- // Open the mail client to send a mail
1276- MyWorksheet.WriteText('Send mail');
1277- MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');
1278-
1279- // Jump to a particular cell
1280- MyWorksheet.WriteText(5, 0, 'Jump to cell A10 on sheet2');
1281- MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');
1282-
1283- // Jump to cell A10 on the current sheet and display a popup hint
1284- MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Go to cell A10');
1285-```
1286-
1287-> **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.
1288-
1289-#### Images
1290-
1291-FPSpreadsheet supports embedding of images in worksheets. Use one of the worksheet methods `WriteImage()` to add an image to the worksheet:
1292-
1293-``` pascal
1294- MyWorksheet.WriteImage(row, col, filename, offsetX, offsetY, scaleX, scaleY);
1295- MyWorksheet.WriteImage(row, col, stream, offsetX, offsetY, scaleX, scaleY);
1296- MyWorksheet.WriteImage(row, col, imageindex, offsetX, offsetY, scaleX, scaleY);
1297-```
1298-
1299-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.
1300-
1301-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:
1302-
1303-``` pascal
1304-type
1305- TsImageType = integer;
1306- TGetImageSizeFunc = function (AStream: TStream; out AWidth, AHeight: DWord; out dpiX, dpiY: Double): Boolean;
1307-
1308-function RegisterImageType(AMimeType, AExtension: String; AGetImageSize: TGetImageSizeFunc): TsImageType;
1309-```
1310-
1311-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:
1312-
1313-- Predefine the widths of all columns at least up to the one containing the right edge of the image.
1314-- Predefine the heights of all rows at least up to the one containing the lower edge of the image.
1315-- If the workbook is to be saved in OpenDocument format add the image after changing column widths and row heights because ods anchors the image to the sheet, not to the cell (like Excel and FPSpreadsheet).
1316-- If the exact size of the image is important make sure that it fits into a single cell.
1317-
1318-### Sorting
1319-
1320-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'`):
1321-
1322-``` pascal
1323-type
1324- TsWorksheet = class
1325- // ...
1326- procedure Sort(const ASortParams: TsSortParams;
1327- ARowFrom, AColFrom, ARowTo, AColTo: Cardinal); overload;
1328- procedure Sort(ASortParams: TsSortParams; ARange: String); overload;
1329- // ...
1330- end;
1331-```
1332-
1333-The sorting criteria are defined by a record of type `TsSortParams`:
1334-
1335-``` pascal
1336-type
1337- TsSortParams = record
1338- SortByCols: Boolean;
1339- Priority: TsSortPriority; // spNumAlpha ("Numbers first"), or spAlphaNum ("Text first")
1340- Keys: TsSortKeys;
1341- end;
1342-
1343- TsSortKey = record
1344- ColRowIndex: Integer;
1345- Options: TsSortOptions; // set of [spDescending, spCaseInsensitive]
1346- end;
1347-```
1348-
1349-- 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).
1350-- `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`).
1351-- 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.
1352-
1353-`InitSortParams` is a handy utility to initialize the sorting parameters:
1354-
1355-``` pascal
1356-function InitSortParams(ASortByCols: Boolean = true; ANumSortKeys: Integer = 1;
1357- ASortPriority: TsSortPriority = spNumAlpha): TsSortParams;
1358-```
1359-
1360-The next code fragment shows a typical sorting call:
1361-
1362-``` pascal
1363-var
1364- sortParams: TsSortParams;
1365-begin
1366- sortParams := InitSortParams(true, 2); // sorting along columns, 2 sorting keys
1367-
1368- // primary sorting key: column 3, ascending, case-insensitive
1369- sortParams.Keys[0].ColRowIndex := 3;
1370- sortParams.Keys[0].Options := [ssoCaseInsensitive];
1371-
1372- // secondary sorting key: colum 1, descending
1373- sortParams.Keys[1].ColRowIndex := 1;
1374- sortParams.Keys[1].Options := [ssoDescending];
1375-
1376- // The sorted block extends between cells A1 (row=0, col=0) and F10 (row=9, col=5)
1377- MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
1378- // or: MyWorksheet.Sort(sortParams, 'A1:F10');
1379-end;
1380-```
1381-
1382-### Searching and replacing
1383-
1384-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.
1385-
1386-**Example:**
1387-
1388-``` pascal
1389-uses
1390- fpsTypes, fpSpreadsheet, fpsUtils, fpsSearch, fpsAllFormats;
1391-var
1392- MyWorkbook: TsWorkbook;
1393- foundWorksheet: TsWorksheet;
1394- foundRow, foundCol: Cardinal;
1395- MySearchParams: TsSearchParams;
1396-begin
1397- MyWorkbook := TsWorkbook.Create;
1398- try
1399- MyWorkbook.ReadFromFile(AFileName);
1400-
1401- // Specify search criteria
1402- MySearchParams.SearchText := 'Hallo';
1403- MySearchParams.Options := [soEntireDocument];
1404- MySearchParams.Within := swWorkbook;
1405-
1406- // or: MySearchParaams := InitSearchParams('Hallo', [soEntireDocument], swWorkbook);
1407-
1408- // Create search engine and execute search
1409- with TsSearchEngine.Create(MyWorkbook) do begin
1410- if FindFirst(MySearchParams, foundWorksheet, foundRow, foundCol) then begin
1411- WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
1412- while FindNext(MySeachParams, foundWorksheet, foundRow, foundCol) do
1413- WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
1414- end;
1415- Free;
1416- end;
1417- finally
1418- MyWorkbook.Free;
1419- end;
1420-end;
1421-```
1422-
1423-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.
1424-
1425-The record `TsSearchParams` specifies the criteria used for searching:
1426-
1427-``` pascal
1428-type
1429- TsSearchParams = record
1430- SearchText: String;
1431- Options: TsSearchOptions;
1432- Within: TsSearchWithin;
1433- end;
1434-```
1435-
1436-Besides the text to be searched (`SearchText`) it provides a set of options to narrow the search:
1437-
1438-- `soCompareEntireCell`: Compares the `SearchText` with the entire cell content. If not contained in the `Options` then the cell text is compared only partially.
1439-- `soMatchCase`: Perform a case-sensitive search
1440-- `soRegularExpr`: The `SearchText` is considered as a regular expression
1441-- `soAlongRows`: The search engine proceeds first along the rows. If not contained in the `Options` then the search proceeds along the columns.
1442-- `soBackward`: The search begins at the end of the document, or runs backward from the active cell. If not contained in the `Options` then the search starts at the beginning of the document, or runs forward from the active cell.
1443-- `soWrapDocument`: If a search has reached the end of the document the search is resumed at its beginning (or vice versa, if `soBackward` is used).
1444-- `soEntireDocument`: Search begins at the first cell (or last cell if `soBackward` is used). If not contained in the `Options` then the search begins at the active cell of the worksheet. Ignored by `FindNext`.
1445-
1446-The record field `Within` identifies the part of the spreadsheet to be searched:
1447-
1448-- `swWorkbook`: The entire workbook is searched. If the search phrase is not found on the first worksheet (or last worksheet if `soBackward` is used) then the search continues with the next (previous) sheet.
1449-- `swWorksheet`: The search is limited to the currently active worksheet
1450-- `swColumn`: Search is restricted to the column of the active cell
1451-- `swRow`: Search is restricted to the row of the active cell.
1452-
1453-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.
1454-
1455-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:
1456-
1457-``` pascal
1458-type
1459- TsReplaceParams = record
1460- ReplaceText: String;
1461- Options: TsReplaceOptions;
1462- end;
1463-```
1464-
1465-The `ReplaceText` identifies the string which will replace the found text pattern. The `Options` define a set of criteria how the replacement is done:
1466-
1467-- `roReplaceEntirecell`: Replaces the entire cell text by the `ReplaceText`. If not contained in the `Options` then only the part matching the `SearchText` is replaced.
1468-- `roReplaceAll`: Performs the replacement in all found cells (i.e., simply call `ReplaceFirst` to replace all automatically).
1469-- `roConfirm`: Calls an event handler for the `OnConfirmReplacement` event in which the user must specify whether the replacement is to be performed or not. Note that this event handler is mandatory if `roConfirm` is set.
1470-
1471-Use the function `InitReplaceParams` (in unit *fpsutils*) to initialize the replace parameters record with the provided (but optional) values.
1472-
1473-### Column and row operations
1474-
1475-The worksheet provides these methods for inserting, deleting, hiding or unhiding columns and rows:
1476-
1477-``` pascal
1478-type
1479- TsWorksheet = class
1480- ...
1481- procedure DeleteCol(ACol: Cardinal);
1482- procedure DeleteRow(ARow: Cardinal);
1483-
1484- procedure InsertCol(ACol: Cardinal);
1485- procedure InsertRow(ARow: Cardinal);
1486-
1487- procedure RemoveCol(ACol: Cardinal);
1488- procedure RemoveRow(ARow: Cardinal);
1489-
1490- procedure RemoveAllCols;
1491- procedure RemoveAllRows
1492-
1493- procedure HideCol(ACol: Cardinal);
1494- procedure HideRow(ARow: Cardinal);
1495-
1496- procedure ShowCol(ACol: Cardinal);
1497- procedure ShowRow(ARow: Cardinal);
1498-
1499- function ColHidden(ACol: Cardinal): Boolean;
1500- function RowHidden(ARow: Cardinal): Boolean;
1501-```
1502-
1503-- When a column or row is **deleted** by `DeleteCol` or `DeleteRow`, any data assigned to this column or row are removed, i., cells, comments, hyperlinks, TCol or TRow records. Data at the right of or below the deleted column/row move to the left or up.
1504-
1505-- `RemoveCol` and `RemoveRow`, in contract, **remove** only the column or row record, i.e. reset column width and row height to their default values. Cell, comment, and hyperlink data are not affected.
1506-
1507-- `RemoveAllCols` **removes all** column records, i.e. resets all column widths; `RemoveAllRows` does the same with the row records and row heights.
1508-
1509-- A column or row is **inserted** before the index specified as parameter of the `InsertXXX` method.
1510-
1511-### Page layout
1512-
1513-#### General
1514-
1515-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.
1516-
1517-``` pascal
1518-type
1519- TsPageOrientation = (spoPortrait, spoLandscape);
1520-
1521- TsPrintOption = (poPrintGridLines, poPrintHeaders, poPrintPagesByRows,
1522- poMonochrome, poDraftQuality, poPrintCellComments, poDefaultOrientation,
1523- poUseStartPageNumber, poCommentsAtEnd, poHorCentered, poVertCentered,
1524- poDifferentOddEven, poDifferentFirst, poFitPages);
1525-
1526- TsPrintOptions = set of TsPrintOption;
1527-
1528- TsHeaderFooterSectionIndex = (hfsLeft, hfsCenter, hfsRight);
1529-
1530- TsCellRange = record
1531- Row1, Col1, Row2, Col2: Cardinal;
1532- end;
1533-
1534- TsPageLayout = class
1535- ...
1536- public
1537- ...
1538- { Methods }
1539- // embedded header/footer images
1540- procedure AddHeaderImage(AHeaderIndex: Integer;
1541- ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1542- procedure AddFooterImage(AFooterIndex: Integer;
1543- ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1544- procedure GetImageSections(out AHeaderTags, AFooterTags: String);
1545- function HasHeaderFooterImages: Boolean;
1546-
1547- // Repeated rows and columns
1548- function HasRepeatedCols: Boolean;
1549- function HasRepeatedRows: Boolean;
1550- procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
1551- procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);
1552-
1553- // print ranges
1554- function AddPrintRange(ARow1, ACol1, ARow2, ACol2: Cardinal): Integer; overload;
1555- function AddPrintRange(const ARange: TsCellRange): Integer; overload;
1556- function GetPrintRange(AIndex: Integer): TsCellRange;
1557- function NumPrintRanges: Integer;
1558- procedure RemovePrintRange(AIndex: Integer);
1559-
1560- { Properties }
1561- property Orientation: TsPageOrientation read FOrientation write FOrientation;
1562- property PageWidth: Double read FPageWidth write FPageWidth;
1563- property PageHeight: Double read FPageHeight write FPageHeight;
1564- property LeftMargin: Double read FLeftMargin write FLeftMargin;
1565- property RightMargin: Double read FRightMargin write FRightMargin;
1566- property TopMargin: Double read FTopMargin write FTopMargin;
1567- property BottomMargin: Double read FBottomMargin write FBottomMargin;
1568- property HeaderMargin: Double read FHeaderMargin write FHeaderMargin;
1569- property FooterMargin: Double read FFooterMargin write FFooterMargin;
1570- property StartPageNumber: Integer read FStartPageNumber write SetStartPageNumber;
1571- property ScalingFactor: Integer read FScalingFactor write SetScalingFactor;
1572- property FitHeightToPages: Integer read FFitHeightToPages write SetFitHeightToPages;
1573- property FitWidthToPages: Integer read FFitWidthToPages write SetFitWidthToPages;
1574- property Copies: Integer read FCopies write FCopies;
1575- property Options: TsPrintOptions read FOptions write FOptions;
1576- property Headers[AIndex: Integer]: String read GetHeaders write SetHeaders;
1577- property Footers[AIndex: Integer]: String read GetFooters write SetFooters;
1578- property RepeatedCols: TsRowColRange read FRepeatedCols;
1579- property RepeatedRows: TsRowColRange read FRepeatedRows;
1580- property PrintRange[AIndex: Integer]: TsCellRange read GetPrintRange;
1581- property FooterImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetFooterImages;
1582- property HeaderImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetHeaderImages;
1583- end;
1584-
1585- TsWorksheet = class
1586- ...
1587- public
1588- property PageLayout: TsPageLayout;
1589- ...
1590- end;
1591-```
1592-
1593-> **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.
1594-
1595-- **Orientation** defines the orientation of the printed paper, either portrait or landscape.
1596-
1597-- **Page width** and **page height** refer to the standard orientation of the paper, usually portrait orientation.
1598-
1599-- **Left**, **top**, **right** and **bottom margins** are self-explanatory and are given in millimeters.
1600-
1601-- **HeaderMargin** is understood - like in Excel - as the distance between the paper top edge and the top of the header, and **TopMargin** correspondingly is the distance between the top paper edge and the top of the first table row, i.e. if the header contains several line breaks it can reach into the the table part of the print-out. This is different from OpenDocument files where the header can grow accordingly.
1602-
1603-- **StartPageNumber** should be altered if the print-out should not begin with page 1. This setting requires to add the option `poUseStartPageNumber` to the PageLayout's `Options` - but this is normally done automatically.
1604-
1605-- The **ScalingFactor** is given in percent and can be used to reduce the number of printed pages. Modifying this property clear the option `poFitToPages` from the PageLayout's `Options`.
1606-
1607-- Alternatively to ScalingFactor, you can also use **FitHeightToPages** or **FitWidthToPages**. The option `poFitToPages` must be active in order to override the `ScalingFactor`setting. `FitHeightToPages` specifies the number of pages onto which the entire height of the printed worksheet should fit. Accordingly, `FitWidthToPages` can be used to define the number of pages on which the entire width of the worksheet has to fit. The value `0` has the special meaning of "use as many pages as needed". In this way, the setting "Fit all columns on one page" of Excel, for example, can be achieved by this code:
1608-
1609-``` pascal
1610- MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
1611- MyWorksheet.PageLayout.FitWidthToPages := 1; // all columns on one page width
1612- MyWorksheet.PageLayout.FitHeightToPages := 0; // use as many pages as needed
1613-```
1614-
1615-- **Header rows and columns repeated on every printed page** can be defined by the `RepeatedCols` and `RepeatedRows` records; their elements `FirstIndex` and `LastIndex` refer to the indexes of the first and last column or row, respectively, to be repeated. Use the methods `SetRepeatedCols` and `SetRepeatedRows` to define these numbers. Note that the second parameter for the last index can be omitted to use only a single header row or column.
1616-
1617-- **Print ranges** or **print areas** (using Excel terminology) can be used to restrict printing only to a range of cells. Use the methods `AddPrintRange` to define a cell range for printing: specify the indexes of the left column, top row, right column and bottom row of the range to be printed. A worksheet can contain several print ranges.
1618-
1619-- **Copies** specifies how often the worksheet will be printed.
1620-
1621-- The **Options** define further printing properties, their names are self-explaining. They were defined according to Excel files, some of them do not exist in ODS files and are ignored there.
1622-
1623-#### Headers and footers
1624-
1625-**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:
1626-
1627-- `&L`: begins the **left-aligned section** of a header or a footer text definition
1628-- `&C`: begins the **centered section** of a header or a footer text definition
1629-- `&R`: begins the **right-aligned section** of a header or a footer text definition
1630-- `&P`: **page number**
1631-- `&N`: **page count**
1632-- `&D`: current **date** of printing
1633-- `&T`: current **time** of printing
1634-- `&A`: **worksheet name**
1635-- `&F`: **file name** without path
1636-- `&Z`: **file path** without file name
1637-- `&G`: embedded **image** - use the methods `AddHeaderImage` or `AddFooterImage` to specify the image file; this also appends the `&G` to the other codes of the current header/footer section. Note that not all image types known by the Office application may be accepted. Currently the image can be *jpeg*, *png*, *gif*, *bmp*, *tiff*, *pcx*, *svg*, *wmf* or *emf*.
1638-- `&B`: **bold** on/off
1639-- `&I`: **italic** on/off
1640-- `&U`: **underlining** on/off
1641-- `&E`: **double-underlining** on/off
1642-- `&S`: **strike-out** on/off
1643-- `&H`: **shadow** on/off
1644-- `&O`: **outline** on/off
1645-- `&X`: **superscript** on/off
1646-- `&Y`: **subscript** on/off
1647-- `&"font"`: begin using of the **font** with the specified name, e.g. `&"Arial"`
1648-- `&number`: begin using of the specified **font size** (in points), e.g. `&16`
1649-- `&Krrggbb`: switch to the **font color** specified to the binary value of the specified color, e.g. use `&KFF0000` for red.
1650-
1651-The arrays `Headers[]`/`Footers[]` provide space for usage of three different headers or footers:
1652-
1653-- `Headers[0]` refers to the header used on the **first page** only, similarly for `Footers[0]`. Instead of index `0` you can use the constant `HEADER_FOOTER_INDEX_FIRST`. Leave this string empty if there is no special first-page header/footer.
1654-- `Headers[1]` refers to the header on pages with **odd page numbers**, similarly for `Footers[1]`. Instead of index `1` you may want to use the constant `HEADER_FOOTER_INDEX_ODD`.
1655-- `Headers[2]` refers to the header on lages with **even page nubmers**, similarly for `Footers[2]`. Instead of index `2` you may want to use the constant `HEADER_FOOTER_INDEX_EVEN`.
1656-
1657-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:
1658-
1659-``` pascal
1660- MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T'; // centered "date time" on all pages as header
1661- MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N'; // right-aligned "Page .. of ..." on odd pages as footer
1662- MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N'; // dto, but left-aligned on even pages
1663-```
1664-
1665-### Protection
1666-
1667-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.
1668-
1669-Protection is handled at three levels: workbook protection, worksheet protection and cell protection.
1670-
1671-#### Workbook protection
1672-
1673-`TsWorkbook` contains a set of workbook or document protection options:
1674-
1675-- `bpLockRevision`: specifies that the workbook is locked for revisions
1676-- `bpLockStructure`: if this option is set then worksheets in the workbook cannot be moved, deleted, hidden, unhidden, or renamed, and new worksheets cannot be inserted.
1677-- `bpLockWindows`: indicates that the workbook windows in the Office application are locked. Windows are the same size and position each time the workbook is opened by the Office application.
1678-
1679-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.
1680-
1681-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.
1682-
1683-Office applications are able to encrypt their spreadsheet files. The readers of FPSpreadsheet basically are not able to open these files. However, there exists a related package, **laz_fpspreadsheet_crypto.lpk**, which has access to decryption routines and is able to read xlsx and ods files. These readers are in the units *xlsxOOXML_crypto* and *fpsOpendocument_crypto*, respectively. Adding them to the uses clause of the application installs user-provided readers with `FormatID` `sfidOOXML_crypto` and `sfidOpenDocument_crypto`, respectively. If the password is known it can be specified as a parameter to the workbook's `LoadFromFile`/`ReadFromStream` methods. If no password is specified the workbook fires the event `OnQueryPassword` in which the user can provide the password, e.g. like this:
1684-
1685-``` pascal
1686-function TForm1.QueryPasswordHandler: String;
1687-begin
1688- Result := InputBox('Password required', 'Password', '');
1689-end;
1690-
1691-procedure TForm1.LoadWorkbook(AFileName: String);
1692-var
1693- workbook: TsWorkbook;
1694-begin
1695- workbook := TsWorkbook.Create;
1696- try
1697- workbook.OnQueryPassword := @QueryPasswordHandler;
1698- workbook.ReadFromFile(AFileName);
1699- ...
1700- finally
1701- workbook.Free;
1702- end;
1703-end;
1704-
1705-// or
1706-
1707-procedure TForm1.LoadWorkbookWithPassword(AFileName, APassword: STring);
1708-var
1709- workbook: TsWorkbook;
1710-begin
1711- workbook := TsWorkbook.Create;
1712- try
1713- workbook.ReadFromFile(AFileName, APassword);
1714- ...
1715-```
1716-
1717-#### Worksheet protection
1718-
1719-`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:
1720-
1721-- `spCells`: the cells in the sheet are protected. It depends on the level of cell protection whether a particular cell can be changed or not. By default, no cell can be changed.
1722-- `spDeleteColumns`: deleting of columns is not be allowed
1723-- `spDeleteRows`: it is not possible to delete rows
1724-- `spFormatCells`: formatting of cells is not allowed
1725-- `spFormatColumns`: columns cannot be formatted.
1726-- `spFormatRows`: rows cannot be formatted
1727-- `spInsertColumns`: it is not allowed to insert columns
1728-- `spInsertRows`: rows cannot be inserted
1729-- `spInsertHyperlinks`: it is not possible to insert new hyperlinks
1730-- `spSort`: the worksheet is not allowed to be sorted.
1731-- `spSelectLockedCells`: Cells which are locked cannot be selected any more.
1732-- `spSelectUnlockedCells`: Even cells which are unlocked cannot be selected. Together with `spSelectLockedCells` this means that the selection in the worksheet is frozen.
1733-
1734-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)`.
1735-
1736-#### Cell protection
1737-
1738-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:
1739-
1740-- `cpLockCell`: This option determines whether cell content can be modified by the user. Since it is on by default cells of a protected worksheet normally cannot be edited. In order to unlock some cells for user input the option `cpLockCell` must be removed from the protection of these cells.
1741-- `cpHideFormulas`: prevents formulas from being shown in the Office application.
1742-
1743-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:
1744-
1745-``` pascal
1746-// query and modifiy the protection state of cell A1 (row=0, col=0)
1747-var
1748- cell: PCell;
1749- cellprot: TsCellProtections;
1750-...
1751- // Find the cell
1752- cell := worksheet.FindCell(0, 0);
1753- // query cell protection
1754- cellprot := worksheet.ReadCellProtection(cell);
1755- // Unlock the cell for editing, don't change the visibility of formulas
1756- worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
1757- // Hide formula of the cell and unlock the cell.
1758- worksheet.WriteCellProtection(cell, [cpHideFormulas]);
1759- // Activate protection
1760- worksheet.Protect(true);
1761-```
1762-
1763-#### Passwords
1764-
1765-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:
1766-
1767-``` pascal
1768-type
1769- TsCryptoInfo = record
1770- PasswordHash: String;
1771- Algorithm: TsCryptoAlgorithm; // caExcel, caSHA1, caSHA256, etc.
1772- SaltValue: String;
1773- SpinCount: Integer;
1774- end;
1775-```
1776-
1777-> **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.
1778-
1779-### Loading and saving
1780-
1781-#### Adding new file formats
1782-
1783-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.
1784-
1785-- Write a unit implementing a reader and a writer for the new file format. They must inherit from the basic `TsCustomSpreadReader` and `TsCustomWriter`, respectively, - both are implemented in unit *fpsReaderWriter* -, or from one of the more advanced ones belonging to the built-in file formats.
1786-- Register the new reader/writer by calling the function `RegisterSpreadFileFormat` in the `initialization` section of this unit (implemented in unit *fpsRegFileFormats*):
1787-
1788-``` pascal
1789-function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
1790- AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;
1791-```
1792-
1793-- `AFormat` must have the value `sfUser` to register an external file format.
1794-- `AReaderClass` is the class of the reader (or nil, if reading functionality is not implemented).
1795-- `AWriterClass` is the class of the writer (or nil, if writing functionality is not implemented).
1796-- `AFormatName` defines the name of the format as used for example in the filter list of file-open dialogs.
1797-- `ATechnicalName` defines a shorter format name.
1798-- `AFileExtensions` is an array of file extensions used in the files. The first array element denotes the default extension. The extensions must begin with a period as in `.xls`.
1799-- The registration function returns a numerical value (`TsSpreadFormatID`) which can be used as format identifier in the workbook reading and writing functions which exist in overloaded version accepting a numerical value for the format specifier. In contract to the built-in formats the FormatID is negative.
1800-- Finally, in your application, add the new unit to the `uses` clause. This will call the registrations function when the unit is loaded and make the new file format available to FPSpreadsheet.
1801-
1802-#### Stream selection
1803-
1804-Workbooks are loaded and saved by means of the `ReadFromFile` and `WriteToFile` methods, respectively (or by their stream counterparts, `ReadFromStream` and `WriteToStream`).
1805-
1806-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.
1807-
1808-- Add the element `boBufStream` to the workbook's `Options`. In this case, a **"buffered" stream** is used for accessing the data. This kind of stream holds a memory buffer of a given size and swaps data to file if the buffer becomes too small.
1809-- Add the element `boFileStream` to the workbook's `Options`. This option avoids memory streams altogether and creates temporary **files** if needed. This is, however, the slowest method of data access.
1810-- If both options are set then `boBufStream` is ignored.
1811-- In practice, however, the effect of the selected streams is not very large if memory is to be saved.
1812-
1813-#### Virtual mode
1814-
1815-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.
1816-
1817-These are the steps required to use this mode:
1818-
1819-- Activate virtual mode by adding the option `boVirtualMode` to the `Options` of the workbook.
1820-- Tell the spreadsheet writer how many rows and columns are to be written. The corresponding worksheet properties are `VirtualRowCount` and `VirtualColCount`.
1821-- Write an event handler for the event `OnWriteCellData` of the worksheet. This handler gets the index of row and column of the cell currently being saved. You have to return the value which will be saved in this cell. You can also specify a template cell that physically exists in the workbook from which the formatting style is copied to the destination cell. Please be aware that when exporting a database, you are responsible for advancing the dataset pointer to the next database record when writing of a row is complete.
1822-- Call the `WriteToFile` method of the workbook.
1823-
1824-Virtual mode also works for reading spreadsheet files.
1825-
1826-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.
1827-
1828-### Dataset export
1829-
1830-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).
1831-
1832-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.
1833-
1834-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.
1835-
1836-A simple example of how this works:
1837-
1838-``` pascal
1839-uses
1840-...
1841-fpsexport
1842-...
1843-var
1844- Exp: TFPSExport;
1845- ExpSettings: TFPSExportFormatSettings;
1846- TheDate: TDateTime;
1847-begin
1848- FDataset.First; //assume we have a dataset called FDataset
1849- Exp := TFPSExport.Create(nil);
1850- ExpSettings := TFPSExportFormatSettings.Create(true);
1851- try
1852- ExpSettings.ExportFormat := efXLS; // choose file format
1853- ExpSettings.HeaderRow := true; // include header row with field names
1854- Exp.FormatSettings := ExpSettings; // apply settings to export object
1855- Exp.Dataset:=FDataset; // specify source
1856- Exp.FileName := 'c:\temp\datadump.xls';
1857- Exp.Execute; // run the export
1858- finally
1859- Exp.Free;
1860- ExpSettings.Free;
1861- end;
1862-```
1863-
1864-#### Export to DBF
1865-
1866-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](http://forum.lazarus.freepascal.org/index.php/topic,41636.0.html)
1867-
1868-``` pascal
1869-procedure TForm1.ExportToDBF(AWorksheet: TsWorksheet; AFileName: String);
1870-var
1871- i: Integer;
1872- f: TField;
1873- r, c: Cardinal;
1874- cell: PCell;
1875-begin
1876- DbfGlobals.DefaultCreateCodePage := 1251; //default encoding of the dbf
1877- DbfGlobals.DefaultOpenCodePage := 1251; //default encoding of the dbf
1878- if Dbf1.Active then Dbf1.Close;
1879- if FileExists(AFileName) then DeleteFile(AFileName);
1880-
1881- Dbf1.FilePathFull := ExtractFilePath(AFileName);
1882- Dbf1.TableName := ExtractFileName(AFileName);
1883- Dbf1.TableLevel := 25; // DBase IV: 4 - most widely used; or 25 = FoxPro supports nfCurrency
1884- Dbf1.LanguageID := $C9; //russian language by default
1885- Dbf1.FieldDefs.Clear;
1886- //below are the fields in excel
1887- Dbf1.FieldDefs.Add('fam', ftString);
1888- //add other fields you want to save
1889-
1890- Dbf1.CreateTable;
1891- Dbf1.Open;
1892-
1893- for f in Dbf1.Fields do
1894- f.OnGetText := @DbfGetTextHandler;
1895-
1896- // Skip row 0 which contains the headers
1897- for r := 1 to AWorksheet.GetLastRowIndex do begin
1898- Dbf1.Append;
1899- for c := 0 to Dbf1.FieldDefs.Count-1 do begin
1900- f := Dbf1.Fields[c];
1901- cell := AWorksheet.FindCell(r, c);
1902- if cell = nil then
1903- f.Value := NULL
1904- else
1905- case cell^.ContentType of
1906- cctUTF8String: f.AsString := UTF8ToCP1251(cell^.UTF8StringValue);
1907- cctNumber: f.AsFloat := cell^.NumberValue;
1908- cctDateTime: f.AsDateTime := cell^.DateTimeValue;
1909- else f.AsString := UTF8ToCP1251(AWorksheet.ReadAsText(cell));
1910- end;
1911- end;
1912- Dbf1.Post;
1913- end;
1914-end;
1915-
1916-//This procedure is called so the text in the cells could be correctly dispayed on WorkSheetGrid on the form, otherwise '?' symbols showing
1917-procedure TForm1.DbfGetTextHandler(Sender: TField; var AText: string; DisplayText: Boolean);
1918-begin
1919- if DisplayText then
1920- AText := CP1251ToUTF8(Sender.AsString);
1921-end;
1922-```
1923-
1924-## Visual controls for FPSpreadsheet
1925-
1926-The package **laz_fpspreadsheet_visual** implements a series of controls which simiplify creation of visual GUI applications:
1927-
1928-- **TsWorkwookSource** links the controls to a workbook and notifies the controls of changes in the workbook.
1929-- **[[TsWorksheetGrid]]** implements a grid control with editing and formatting capabilities; it can be applied in a similar way to TStringGrid.
1930-- **TsWorkbookTabControl** provides a tab sheet for each worksheet of the workbook. It is the ideal container for a [[TsWorksheetGrid]].
1931-- **TsWorksheetIndicator**: a combobox which lists all worksheets of the workbook.
1932-- **TsCellEdit** corresponds to the editing line in Excel or Open/LibreOffice. Direct editing in the grid, however, is possible as well.
1933-- **TsCellIndicator** displays the name of the currently selected cell; it can be used for navigation purposes by entering a cell address string.
1934-- **TsCellCombobox** offers to pick cell properties for a selection of formatting attributes: font name, font size, font color, background color.
1935-- **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.
1936-- Various standard actions are provided in the unit **fpsActions**. Applied to menu or toolbar, they simplify typical formatting and editing tasks without having to write a line of code.
1937-- **TsWorkbookChartSource** interfaces a workbook with the [[TAChart]] library. It defines the cell ranges from which a Chart series can get its data. There is also an older component, [[TsWorksheetChartSource]], which requires all cells to be from the same worksheet. For this reason, it has not been fully integrated into the environment of FPSpreadsheet controls and will be removed from the library in the future.
1938-
1939-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.
1940-
1941-## Examples
1942-
1943-Please see [[FPSpreadsheet: Examples]] for a variety of code examples.
1944-
1945-## Download
1946-
1947-### Subversion
1948-
1949-You can download FPSpreadsheet using the subversion software and the following command line:
1950-
1951-``` bash
1952-svn checkout https://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet
1953-```
1954-
1955-### Stable releases
1956-
1957-The most current release is distributed by the Online-Package-Manger which is integrated in the Lazarus IDE (menu "Package" \> "Online package manager"): Scroll down the list of available packages, check the entry "FPSpreadsheet" and click "Install". Confirm the prompt to rebuild the IDE. When the process is complete you find the new components in palette "FPSpreadsheet".
1958-
1959-Older releases of FPSpreadsheet cann still be found on [sourceforge](https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/).
1960-
1961-## Installation
1962-
1963-- 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.
1964-- 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.
1965-- 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.
1966-- FPSpreadsheet is developed with the latest stable fpc version (currently fpc 3.0.2). We only occasionally check older versions.
1967-- The basic spreadsheet functionality works with Lazarus versions back to version 1.0. Some visual controls or demo programs, however, require newer versions. Please update your Lazarus if you have an older version and experience problems.
1968-
1969-### Conditional defines
1970-
1971-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:
1972-
1973-- `FPS_DONT_USE_CLOCALE`: In Unix systems, the unit `clocale` is automatically added to the uses clause of *fpspreadsheet.pas*. This unit sets up localization settings needed for locale-dependent number and date/time formats. However, this adds a dependence on the C library to the package [2](http://lazarus-ccr.sourceforge.net/docs/rtl/clocale/index.html). If this is not wanted, define `FPS_DONT_USE_CLOCALE`.
1974-- `FPS_VARISBOOL`: fpspreadsheet requires the function VarIsBool which was introduced by fpc 2.6.4. If an older FPC versions is used define `FPS_VARISBOOL`. Keep undefined for the current FPC version.
1975-- `FPS_LAZUTF8`: fpspreadsheet requires some functions from the unit LazUTF8 which were introduced by Lazarus 1.2. If an older Lazarus version is used define `FPS_LAZUTF8`. Keep undefined for the current Lazarus version.
1976-- `FPS_NO_GRID_MULTISELECT`: In order to allow selection of multiple ranges in the WorksheetGrid a sufficiently new version of the basic TCustomGrid is needed. The required property "RangeSelect" was introduced in Lazarus 1.4. In order to compile the package with older versions activate the define `FPS_NO_GRID_MULTISELECT`.
1977-- `FPS_PTRINT`: In order to provide safe casting of integers to pointers new version of FPC provide the types PtrInt and IntPtr. This is not yet available in fpc 2.6.0.
1978-- `FPS_NEED_STRINGHASHLIST`: Unit stringhashlist belongs to LCL before Lazarus 1.8. To avoid a requirement of LCL in laz_fpspreadsheet.lpk a copy in the fps directory is provided. This copy is used when the define FPS_NEED_STRINGHASHLIST is active. The define is not needed for Lazarus versions \>= 1.8.
1979-- `FPS_NO_NEW_UTF8_ROUTINES`: In Lazarus 2.0+ some UTF8 routines in unit LazUTF8 were renamed from UTF8CharacterXXX to UTF8CodePointXXX. Activate the following define when the new routines are not available, i.e. for Lazarus version \< 2.0.
1980-- `FPS_NO_LAZUTF16`: Lazarus 1.8+ has unit LazUTF16 for special access to widestring. This define must be active when this unit is not available, i.e. for Lazarus versions before 1.8.0.
1981-- `FPS_NO_STRING_SPLIT`: Activate the following define if FPS does not have the string Split helper, e.g. before v3.0
1982-
1983-All these defines are collected in the include file *fps.inc*.
1984-
1985-In the most recent test (Feb 2023), all fpspreadsheet packages compile fine with Laz 1.6+ / fpc 3.0+ (some defines in *fps.inc* may have to be adapted, though). With Laz 1.4 / fpc 2.6.4 or older, *fpspreadsheet_dataset.lpk* and *fpspreadsheet_crypto.lpk* cannot be used any more.
1986-
1987-## Support and Bug Reporting
1988-
1989-The recommended place to discuss FPSpreadsheet and obtain support is asking in the [Lazarus Forum](http://forum.lazarus.freepascal.org/index.php?board=49.0).
1990-
1991-Bug reports should be sent to the [Lazarus/Free Pascal Bug Tracker](http://bugs.freepascal.org/); please specify the "Lazarus-CCR" project.
1992-
1993-## Current Progress
1994-
1995-### Progress by supported cell content
1996-
1997-| Format | Multiple · sheets | Unicode | Reader · support | Writer · support | Text | Number | String · Formula | RPN · Formula | 3D cell · references | Date/Time | Comments | Hyperlinks | Images +++ | Protection | Conditional · formats |
1998-|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1999-| CSV files | No | Yes + | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | N/A | N/A | N/A | N/A |
2000-| Excel 2.x | No | No * | Working ** | Working | Working | Working | Working | Working *** | N/A | Working | Working | N/A | N/A | Working | N/A |
2001-| Excel 5.0 (Excel 5.0 and 95) | Yes | No * | Working ** | Working | Working | Working | Working | Working *** | Working | Working | Working | N/A | N/A | Working | N/A |
2002-| Excel 8.0 (Excel 97- 2003) | Yes | Yes | Working ** | Working | Working | Working | Working | Working *** | Working | Working | Reading only | Working | Not working | Working | Not working |
2003-| Excel 2003/XML | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working | Working | Working | N/A | Working | Working |
2004-| Excel OOXML | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working | Working | Working | Working | Working | Working ++++ |
2005-| OpenDocument | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working | Working | Working | Working | Working | Working |
2006-| HTML | No | Yes | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | Working | Not working | N/A | N/A |
2007-| Wikitable files (Mediawiki) | No | Yes | planned | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | N/A | Not working | N/A | N/A |
2008-
2009-(+) Depends on file.\
2010-(++) No "true" number format support because the file does not containg number formatting information. But the number format currently used in the spreadsheet understood.\
2011-(+++) Only very basic image support: no transformations, no cropping, no image manipulation.\
2012-(++++) Extended OOXML formatting options not supported.\
2013-(*) 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.\
2014-(**) Some cell could be returned blank due to missing or non ready implemented number and text formats.\
2015-(***) This is the format in which the formulas are written to file (determined by design of the file format).\
2016-(****) Writing of all formats is supported. Some rare custom formats, however, may not be recognized correctly. BIFF2 supports only built-in formats by design.
2017-
2018-### Progress of the formatting options
2019-
2020-The following formatting options are available:
2021-
2022-| Format | Text · alignment | Text · rotation | Font | Rich · text | Border | Color · support | Back · ground | Word · wrap | Col&Row · size | Number · format | Merged · cells | Page · layout | Print · ranges | Header/footer · images | Column/row · format | Hide · cols/rows | Page · breaks |
2023-|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2024-| CSV files | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
2025-| Excel 2.x | Working * | N/A | Working | N/A | Working | Working | Working** | N/A | Working | Working | N/A | Working | N/A | N/A | Working | N/A | Working |
2026-| Excel 5.0 · (Excel 5.0 and 95) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | N/A | Working | Working | N/A | Working | Working | Working |
2027-| Excel 8.0 · (Excel 97 - XP) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Not working | Working | Working | Working |
2028-| Excel 2003/XML | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | N/A | Working | Wworking | Working |
2029-| Excel OOXML (xlsx) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working |
2030-| OpenDocument | Working | Working | Working | Working | Working | Working | Working*** | Working | Working | Working | Working | Working | Working | Working | Working | Working | |
2031-| HTML (+) | Working | bugs | Working | Working | bugs | Working | Working | Writing only | Writing only | N/A | Working | N/A | N/A | Not working | Writing only | to be done | N/A |
2032-| Wikitable (Mediawiki) | Writing only | N/A | Writing only | Not working | Writing only | Writing only | | Writing only | Writing only | N/A | Writing only | N/A | N/A | Not working | Writing only | to be done | N/A |
2033-
2034-(N/A) Feature is not available for this format intrinsically.\
2035-(*) BIFF2 supports only horizontal text alignment, vertical alignment is ignored.\
2036-(**) BIFF2 does not support a background color; a dotted black&white background is used instead.\
2037-(***) OpenDocument supports only uniform backgrounds; a fill color interpolated between foreground and background colors is written instead.\
2038-(****) Only uniform background color, no fill styles.\
2039-(+) HTML reader does not support styles. Since the writer does use styles these files are not read back correctly.
2040-
2041-### Progress of workbook/worksheet user-interface options
2042-
2043-Some additional options were added to interface the file contents with the `TsWorksheetGrid`:
2044-
2045-| Format | Hide grid lines | Hide headers | Frozen Panes | Active sheet/cell | Zooming | BiDi mode | Tab color |
2046-|----|----|----|----|----|----|----|----|
2047-| Excel 2.x | Working | Working | not working | N/A | N/A | N/A | N/A |
2048-| Excel 5.0 (Excel 5.0 and 95) | Working | Working | Working | Working | Working | N/A | N/A |
2049-| Excel 8.0 (Excel 97 - XP) | Working | Working | Working | Working | Working | Working | Working |
2050-| Excel2003/XML | Working | Working | Working | Working | Working \|\| class="working\|Working | N/A | |
2051-| Excel OOXML | Working | Working | Working | Working | Working | Working | Working |
2052-| OpenDocument | Working | Working | Working | Working | Working | Working | Working |
2053-| HTML | Writing only | Writing only | N/A | N/A | N/A | not working | N/A |
2054-
2055-### To do list
2056-
2057-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!
2058-
2059-- Find out why BIFF2 files are corrupt when saved with frozen rows/cols activated.
2060-- Add row and column formats
2061-- Add reading support for wikitable (Mediawiki) files
2062-- 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>](http://forum.lazarus.freepascal.org/index.php/topic,25624.0.html).
2063-- Improve color support: due to palette support colors may change from file to file currently.
2064-- Add embedded images.
2065-- Fix writing of cell comments to BIFF8 files.
2066-
2067-**Long-term:**
2068-
2069-- 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.
2070-- Store formatting in a format list of the workbook, not in the cell, to reduce memory usage.
2071-- Use BIFF8 file-wide string storage instead of storing strings in cells (should reduce memory usage in case of many duplicate strings)
2072-- Redo ooxml and ods readers based upon SAX/TXmlReader instead of DOM to reduce memory usage of large files.
2073-- 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).
2074-
2075-## Changelog
2076-
2077-### SVN
2078-
2079-**Changes with respect to current release**
2080-
2081-- (currently none)
2082-
2083-**Incompatible changes**
2084-
2085-- (currently none)
2086-
2087-### Version 1.16
2088-
2089-This is the latest stable release.
2090-
2091-**Changes with respect to current release**
2092-
2093-- XLSX reader can handle embedded as well as header/footer images now.
2094-- Less strict format check by Excel2 reader since there are BIFF2 files with BOF record of Excel8 which Excel can read.
2095-- Reading support for the Excel3 and Excel4 formats (unit *xlsbiff34*)
2096-- ODS reader supports the \<text:s\> node
2097-- ODS reader is able to open encrypted files.
2098-
2099-**Incompatible changes**
2100-
2101-- (none)
2102-
2103-### Version 1.14
2104-
2105-**Changes with respect to v1.12**
2106-
2107-- Implementation of **conditional formats** for ods, xlsx and Excel xml.
2108-- Implementation of **meta data** for ods, xlsx and Excel xml.
2109-- Add a worksheet-based dataset in separate package (**TsWorksheetDataset**).
2110-
2111-**Incompatible changes**
2112-
2113-- Removed the deprecated worksheet properties DefaultRowHeight and DefaultColWidth. Use the corresponding Read/Write routines instead (ReadDefaultColWidth(units), WriteDefaultColWidth(units)).
2114-
2115-### Version 1.12
2116-
2117-**Changes with respect to v1.10x**
2118-
2119-- **Hide** (and unhide) rows and columns.
2120-- Enforce **Page breaks** before rows and columns during printing by the Office applications.
2121-- Full support (reading and writing) of the **ExcelXML** xml format (Excel 2003 and XP).
2122-- Support of the color of worksheet tabs (Worksheet.TabColor) in xls biff8, xlsx and ods formats. The property is ignored by the visual WorkbookTabControl, though.
2123-- Add TsWorksheetIndicator to visual controls.
2124-
2125-### Version 1.10.1
2126-
2127-**Changes with respect to v1.8x**
2128-
2129-- **Workbook, worksheet and cell protection** (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).
2130-- New package **laz_fpspreadsheet_crypto** to decipher the encryption for worksheet-protection in xls files. Requires DCPcrypt.
2131-- TsWorksheetGrid can **display embedded images**.
2132-- **Drag and drop** in TsWorksheetGrid
2133-- New, **highDPI-aware** component palette icons.
2134-- New workbook optiond **boAbortReadingOnFormulaError** and **boIgnoreFormulas**.
2135-- Formulas with **references to other sheets**, i.e. `'=Sheet1!A1+Sheet2!A2'`
2136-
2137-**Incompatible changes**
2138-
2139-- The field **FormulaValue** has been removed from the cell record. The cell formula now can be retrieved by calling `Worksheet.ReadFormula(cell)`.
2140-
2141-### Version 1.8
2142-
2143-**Changes with respect to v1.6x**
2144-
2145-- **"Rich-text" formatting** of label cells, i.e. assignment different fonts to groups of characters within the cell text. For this purpose, HTML codes (such as `<B>...</B>`) can be embedded in the cell text to identify the parts with different font (--\> `TsWorksheet.WriteTextAsHTML`).
2146-- **Searching** for cells with specified content in worksheet or workbook.
2147-- Support for reading and writing of **HTML format**
2148-- Support for writing of the **ExcelXML format** (Excel XP and 2003)
2149-- Ability to use **user-provided file reader/writer classes** to extend FPSpreadsheet to new file formats.
2150-- Readers and writers now support all the **line styles** of Excel8 and OOXML.
2151-- xls, xlsx and ods readers/writers now support the **active worksheet** and **selected cell**.
2152-- Ability to write to/read from the system's **clipboard** for copy & paste using the visual controls.
2153-- Support for **print ranges** and **repeated header rows and columns** in the Office applications.
2154-- Support for **embedded images** (currently only writing to xlsx and ods, no reading).
2155-- Improved compatibility of **TsWorksheetGrid** with TStringGrid (`Cells[Col,Row]` property). Standalone application as an advanced StringGrid replacement.
2156-- Support for **Right-to-left mode** in TsWorksheetGrid. In addition to the system-wide RTL mode, there are also parameters `BiDiMode` in the Worksheet and cells allowing to controls text direction at worksheet and cell level individually, like in Excel or LibreOffice Calc.
2157-- Support of several **units** for specification of column widths and row heights.
2158-- The library now supports **localization** using po files. Translations are welcome.
2159-- **Zoom factor** read and written by the worksheet, and applied by the TsWorksheetGrid.
2160-- Support of **column and row formats**
2161-- Support of **hidden** worksheets
2162-
2163-**Incompatible changes**
2164-
2165-- VirtualMode was changed in order to be able to treat worksheets of the same workbook differently. `VirtualRowCount` and `VirtualColCount` are now properties of the worksheet, and similarly, the event handler `OnWriteCellData`. In older versions, these properties had belonged to the workbook.
2166-- The worksheet methods `ReadAsUTF8Text` and `WriteUTF8Text` have been renamed to `ReadAsText` and `WriteText`, respectively. The old ones are still available and marked as deprecated; they will be removed in later versions.
2167-- The public properties of `TsWorksheetGrid` using a `TRect` as parameter were modified to use the Left, Top, Right, Bottom values separately.
2168-- The `PageLayout` is a class now, no longer a record. As a consequence, some array properties cannot be set directly any more, use the corresponding methods instead.
2169-- Most of the predefined color constants were marked as deprecated; only the basic EGA colors will remain.
2170-- Unit fpsNumFormatParser is integrated in fpsNumFormat. Old code which "uses" fpsNumFormatParser must "use" fpsNumFormat now.
2171-- The source files of the laz_fpspreadsheet, laz_fpspreadsheet_visual and laz_fpspreadsheetexport_visual packages have been moved to separate folders in order to resolve some occasional compilation issues. Projects which do not use the packages but the path to the sources must adapt the paths.
2172-
2173-### Version 1.6
2174-
2175-**Changes with respect to v1.4.x**
2176-
2177-- TsWorkbookChartSource is a new component which facilitates creation of charts from non-contiguous spreadsheet data in various worksheets. It interfaces to a workbook via tha WorkbookSource component. In the long run, it will replace the older TsWorksheetChartSource which required contiguous x/y data blocks in the same worksheet.
2178-- Major reconstruction of the cell record resulting in strong reduction of memory consumption per cell (from about 160 bytes per cell down to about 50)
2179-- Implementation of a record helper for the TCell which simplifies cell formatting (no need to set a bit in UsedFormattingFields any more, automatic notification of visual controls)
2180-- Comments in cells
2181-- Background fill patterns
2182-- Hyperlinks
2183-- Enumerators for worksheet's internal AVLTrees for faster iteration using a for-in loop.
2184-- Formatting of numbers as fractions.
2185-- Improved number format parser for better recognition of Excel-like number formats.
2186-- Page layout (page margins, headers, footer, used for only when printing in the Office applications - no direct print support in fpspreadsheet!)
2187-- Improved color management: no more palettes, but direct rgb colors. More pre-defined colors.
2188-- A snapshot of the wiki documentation is added to the library as chm help file.
2189-
2190-**Incompatible changes**
2191-
2192-- All type declarations and constants are moved from fpspreadsheet.pas to the new unit **fpstypes.pas**. Therefore, most probably, this unit has to be added to the uses clause.
2193-- Because fpspreadsheet supports now **background** fill patterns the cell property `BackgroundColor` has been replaced by `Background`. Similarly, the `UsedFormattingFields` flag `uffBackgroundColor` is called `uffBackground` now.
2194-- Another `UsedFormattingFields` flag has been dropped: **`uffBold`**. It is from the early days of fpspreadsheet and has become obsolete since the introduction of full font support. For achieving a bold type-face, now call `MyWorksheet.WriteFont(row, col, BOLD_FONTINDEX)`, or `Myworksheet.WriteFontStyle(row, col, [fssBold])`.
2195-- **Iteration through cells** using the worksheet methods `GetFirstCell` and `GetNextCell` has been removed - it failed if another iteration of this kind was called within the loop. Use the new for-in syntax instead.
2196-- Support for **shared formulas** has been reduced. The field `SharedFormulaBase` has been deleted from the TCell record, and methods related to shared formulas have been removed from TsWorksheet. Files containing shared formulas can still be read, the shared formulas are converted to multiple normal formulas.
2197-- The **color palettes** of previous versions have been abandoned. `TsColor` is now a `DWord` representing the rgb components of a color (just like TColor does in the *graphics* unit), it is not an index into a color palette any more. The values of pre-defined colors, therefore, have changed, their names, however, are still existing. The workbook functions for palette access have become obsolete and were removed.
2198-
2199-### Version 1.4
2200-
2201-**Changes with respect to v1.2.x**
2202-
2203-- 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`).
2204-- **Shared formulas** (reading for sfExcel5, sfExcel8, sfOOXML; writing for sfExcel2, sfExcel5, sfExcel8).
2205-- Significant **speed-up** of writing of large spreadsheets for the xml-based formats (ods and xlsx), speed up for biff2; **speedtest demo** program
2206-- **VirtualMode** allowing to read and write very large spreadsheet files without loading entire document representation into memory. Formatting of cells in VirtualMode.
2207-- Demo program for database export using virtual mode and TFPSExport.
2208-- Added db export unit allowing programmatic exporting datasets using **TFPSExport**. Similar export units are e.g. [[fpdbfexport]], [[fpXMLXSDExport]].
2209-- Reader for **xlsx** files, now fully supporting the same features as the other readers.
2210-- Reader/writer for **CSV files** based on [[CsvDocument]].
2211-- **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
2212-- **Insertion and deletion of rows and columns** into a worksheet containing data.
2213-- Implementation of **sorting** of a worksheet.
2214-- Support of **diagonal "border" lines**
2215-- **Logging of non-fatal error messages** during reading/writing (TsWorksheet.ErrorMsg)
2216-- **Merged** cells
2217-- **Registration** of currency strings for automatic conversion of strings to **currency values**
2218-- 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 [[FPSpreadsheet tutorial: Writing a mini spreadsheet application|creation of GUI applications]].
2219-- **Overflow cells** in TsWorksheetGrid: label cells with text longer than the cell width extend into the neighboring cell(s).
2220-
2221-**Incompatible changes**
2222-
2223-- 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).
2224-- 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.
2225-- With full support of string formulas some features related to RPN formulas were removed:
2226- - The field `RPNFormulaResult` of TCell was dropped, as well as the element `cctRPNFormula` in the `TsContentType` set.
2227- - Sheet function identifiers were removed from the `TsFormulaElement` set, which was truncated after `fekParen`.
2228- - 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`.
2229- - 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).
2230- - Code related to RPN formulas was moved to a separate unit, `fpsRPN`. Add this unit to the uses clause if you need RPN features.
2231-
2232-## License
2233-
2234-LGPL with static linking exception. This is the same license as is used in the Lazarus Component Library.
2235-
2236-## References
2237-
2238-### Documentation
2239-
2240-FPSpreadsheet is documented in this wiki file.
2241-
2242-Every release of FPSpreadsheet is accompanied by a snapshot of the current wiki files. There is also an api documentation created from the embedded comments in the source files. Both files are in the chm format.
2243-
2244-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:
2245-
2246-- Version 1.16: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=157438](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=157438)
2247-- Version 1.14: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=149828](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=149828)
2248-- Version 1.12: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=136886](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771)
2249-- Version 1.10.1: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771)
2250-- Version 1.10: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118441](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118441)
2251-- Version 1.8: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=107616](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=107616)
2252-- Version 1.6.2: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=100723](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=100723)
2253-- Version 1.6: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=91469](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=91469)
2254-- Version 1.4: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299)
2255-- Version 1.2 and 1.2.1: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375](https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375)
2256-
2257-### Wiki links
2258-
2259-- [[Office Automation]]
2260-- [[CsvDocument]]
2261-- [[FPSpreadsheet: Examples]]
2262-- [[RPN Formulas in FPSpreadsheet|RPN formulas in FPSpreadsheet]]
2263-- [[FPSpreadsheet: List of formulas]]
2264-- [[FPSpreadsheet tutorial: Writing a mini spreadsheet application]]
2265-
2266-### External Links
2267-
2268-- [Microsoft OLE Document Format](http://sc.openoffice.org/compdocfileformat.pdf)
2269-- [Excel file format description](http://sc.openoffice.org/excelfileformat.pdf)
2270-- 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/>](http://cgit.freedesktop.org/libreoffice/contrib/mso-dumper/). A similar application is the "BIFFExplorer" which can be found in the *applications* folder of ccr.
2271-- Icons used in the demo programs:
2272- - [silk icons](http://www.famfamfam.com/lab/icons/silk/)
2273- - [fugue icons](http://p.yusukekamiyamane.com/)
2274-
2275----
2276-*From the Free Pascal wiki, page "FPSpreadsheet", as it stood on 2024-05-10 after a change by Wp, 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](/wiki-contact).*
2277-Category: Components
2278-Category: Data import and export
2279-Category: FPSpreadsheet
2280-Category: Grids
2281-Category: Lazarus-CCR
1+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.
2+
3+Screenshot of spready demo program provided with fpspreadsheet showing an XLS file:
4+
5+[![fpsgrid.png](/wiki-images/0/02/fpsgrid.png)](</File:fpsgrid.png>)
6+
7+## Documentation
8+
9+This wiki page covers the latest development/trunk version of FPSpreadsheet available via subversion. Please see section Stable releases for documentation on the stable version that you can download.
10+
11+## API Documentation
12+
13+### API Reference
14+
15+A help file in CHM format can be found in the FPSpreadsheet installation folder _docs_. If you did not yet install the package follow <http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr/components/fpspreadsheet/docs/fpspreadsheet-api.chm> to _fpspreadsheet-api.chm_.
16+
17+The second CHM file available in the folder _docs_ , _fpspreadsheet-wiki.chm_ , is a snapshot of the FPSpreadsheet-related wiki pages bundled into a single help file.
18+
19+### Basics
20+
21+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. In addition, cells can contain information on formatting, i.e. font style, background color, text alignment etc.
22+
23+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.
24+
25+Worksheets are bound together to form a **workbook** which represents the document of the spreadsheet application. The workbook also stores information that is needed from all worksheets, i.e. font list, cell and number format lists, etc.
26+
27+FPSpreadsheet follows this same structure - there is a `TCell`, a `TsWorksheet`, and a `TsWorkbook`.
28+
29+#### Workbook
30+
31+The class `TsWorkbook` is the main class visible to the user. It provides methods for reading data from and writing to files. The versatile structure of the library provides access to various popular file formats, like Excel _.xls_ or _.xlsx_ , or OpenOffice/LibreOffice _.ods_.
32+
33+The file format is specified by the type `TsSpreadsheetFormat` defined in the unit `fpstypes`
34+```
35+type
36+ TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML,
37+ sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);
38+```
39+
40+where
41+
42+ * `sfExcel2, sfExcel5, sfExcel8` stands for versions of the binary xls format used by Excel ("BIFF" = "Binary Interchange File Format") with `sfExcel8` being the most modern one.
43+ * `sfOOXML` corresponds to the newer xlsx format introduced by Excel2007
44+ * `sfExcelXML` is the xml format which was introduced by Microsoft for Office XP and 2003. Not very popular.
45+ * `sfOpenDocument` is the spreadsheet format used by OpenOffice/LibreOffice; by default, the files have the extension _.ods_.
46+ * `sfCSV` refers to comma-delimited text files (default extension _.csv_); they can be understood by any text editor and all spreadsheet programs, but do not contain formatting information.
47+ * `sfHTML` denotes the standard HTML format as used in web browsers.
48+ * `sfWikiTable_Pipes` and `sfWikiTable_WikiMedia` is the format used by tables in wiki websites.
49+ * `sfUser` is needed to register a user-defined format. There are no plans to implement "ancient" file formats like Excel3.0/4.0 or Lotus. It is possible, however, to provide your own reading and writing classes to extend the functionality of FPSpreadsheet - see the section below on Adding new file formats
50+
51+When applying fpspreadsheet the first task is to create an instance of the workbook:
52+```
53+var
54+ MyWorkbook: TsWorkbook;
55+ begin
56+ MyWorkbook := TsWorkbook.Create;
57+ ...
58+```
59+
60+Reading of spreadsheet files is accomplished (among others) by the workbook methods
61+
62+ * `procedure ReadFromFile(AFileName: string):`
63+Reads the file with the given name and automatically determines the correct file format.
64+ * `procedure ReadFromFile(AFileName: string; AFormat: TsSpreadsheetFormat):`
65+Reads the file, but assumes that the file format is as specified by `AFormat`.
66+
67+The following workbook methods can be used for saving to file:
68+
69+ * `procedure WriteToFile(const AFileName: string; const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False)`:
70+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`:
71+ * `procedure WriteToFile(const AFileName: String; const AOverwriteExisting: Boolean = False)`:
72+ditto, but the file format is determined from the file extension provided (in case of Excel's xls the most recent version, `sfExcel8`, is used).
73+
74+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.
75+
76+**Note:** FPSpreadsheets provides specialized units 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:
77+
78+ * _xlsbiff2_ , _xlsbiff5_ and _xlsbiff8_ for the binary xls file formats `sfExcel2`, `sfExcel5` and `sfExcel8`, respectively,
79+ * _xlsxOOXML_ for the xlsx file format `sfOOXML` of Excel 2007 and later,
80+ * _xlsXML_ for the xml format of Excel XP and 2003,
81+ * _fpsopendocument_ for the file format `sfOpenDocument` of OpenOffice/LibreOffice,
82+ * _fpsCSV_ for text files with comma-separated values (csv),
83+ * _fpsHTML_ for HTML files,
84+ * _wikitables_ for `sfWikiTable_Pipes` and `sfWikiTable_WikiMedia`,
85+ * or, simply add _fpsallformats_ to get read/write support for all file formats supported.
86+
87+#### Worksheet
88+
89+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:
90+```
91+var
92+ MyWorkbook: TsWorkbook;
93+ MyWorksheet: TsWorksheet;
94+ begin
95+ MyWorkbook := TsWorkbook.Create;
96+ MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
97+ // 'My_Table' is the "name" of the worksheet
98+ ...
99+```
100+
101+Already existing worksheets can be accessed by using the `TsWorkbook` methods
102+
103+ * `function GetFirstWorksheet: TsWorksheet`: retrieves the first worksheet of the workbook.
104+ * `function GetWorksheetByIndex(AIndex: Cardinal): TsWorksheet`: returns the worksheet with the given index (starting at 0).
105+ * `function GetWorksheetByName(AName: String): TsWorksheet`: returns the worksheet with the given name which was used when the worksheet was added.
106+
107+The count of already existing worksheets can be queried by calling `GetWorksheetCount`.
108+
109+#### Cell
110+
111+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.
112+```
113+var
114+ MyWorkbook: TsWorkbook;
115+ MyWorksheet: TsWorksheet;
116+ begin
117+ MyWorkbook := TsWorkbook.Create;
118+ MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
119+ MyWorksheet.WriteNumber(0, 0, 1.0); // "A1" has row=0 and col=0
120+ ...
121+```
122+
123+It is also possible to access cells directly by means of the methods `FindCell(ARow, ACol)` or `GetCell(ARow, ACol)` of the workbook. Both funtions exist also in an overloaded version to which the cell address can be passed in Excel notation: `FindCell(ACellStr: String)` or `GetCell(ACellStr: String)`. 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.
124+```
125+if MyWorksheet.FindCell('A1') = nil then
126+ WriteLn('Cell A1 does not exist.');
127+```
128+
129+##### The basic TCell record
130+
131+This is the declaration of the cell's data type:
132+```
133+type
134+ TCell = record
135+ { Location of the cell }
136+ Worksheet: TsWorksheet;
137+ Col: Cardinal; // zero-based
138+ Row: Cardinal; // zero-based
139+
140+ { Index of format record }
141+ FormatIndex: Integer;
142+
143+ { Status flags }
144+ Flags: TsCellFlags; // (cfHasComment, cfMerged, cfHyperlink, ...)
145+
146+ { Cell content }
147+ UTF8StringValue: String; // strings cannot be part of a variant record
148+ case ContentType: TCellContentType of // must be at the end of the declaration
149+ cctEmpty : (); // has no data at all
150+ cctFormula : (); // UTF8StringValue is outside the variant record
151+ cctNumber : (Numbervalue: Double);
152+ cctUTF8String : (); // FormulaValue is outside the variant record
153+ cctDateTime : (DateTimevalue: TDateTime);
154+ cctBool : (BoolValue: boolean);
155+ cctError : (ErrorValue: TsErrorValue);
156+ end;
157+ PCell = ^TCell;
158+```
159+
160+The field `ContentType` indicates which data type is stored in the cell:
161+```
162+type
163+ TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);
164+```
165+
166+According to this field the corresponding data can be found in the fields
167+
168+ * `NumberValue` (for `ContentType=cctNumber`), or
169+ * `UTF8StringValue` (for `ContentType=cctUTF8String`), or
170+ * `DateTimeValue` (for `ContentType=cctDateTime`), or
171+ * `BoolValue` (for `ContentType=cctBool`), i.e. `TRUE` or `FALSE`, or
172+ * `ErrorValue` (for `ContentType=cctError`).
173+
174+Due to usage of a variant record most of these values are overlapping, i.e. modification of `NumberValue` affects also the other values. Therefore, always respect the `ContentType` when accessing the `TCell` record directly (the worksheet methods discussed below consider this automatically).
175+
176+The field `Flags` tells whether additional data are associated with the cell which are not included in the cell record usually to save memory:
177+```
178+type
179+ TsCellFlag = (cfHasComment, cfHyperlink, cfMerged, cfHasFormula, cf3dFormula);
180+ TsCellFlags = set of TsCellFlag;
181+```
182+
183+ * `cfHasComment`: A comment record can be found in the `Comments` of the worksheet.
184+ * `cfHyperlink`: The cell is contains a hyperlink stored in the `Hyperlinks` of the worksheet.
185+ * `cfMerged`: The cell belongs to a merged block and extends across several cells.
186+ * `cfHasFormula`: The cell is associated with a formula which is stored in the `Formulas` of the worksheet.
187+ * `cf3dFormula`: The formula associated with the cell contains elements referencing other sheets of the same workbook.
188+
189+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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 by calling the function `HasFormula(cell)` for the cell to be queried (`cell: PCell`) in unit _fpsUtils_ ; this function checks the presence of the element `cfHasFormula` in the cell flags.
190+
191+##### How to add and read data to/from a cell
192+
193+Adding values to a cell is most easily accompished by using one of the `WriteXXXX` methods of the worksheet. The most important ones are:
194+```
195+type
196+ TsWorksheet = class
197+ ...
198+ { Writing of currency values }
199+ function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
200+ ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
201+ ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
202+ ANegCurrFormat: Integer = -1): PCell; overload;
203+ procedure WriteCurrency(ACell: PCell; AValue: Double;
204+ ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
205+ ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
206+ ANegCurrFormat: Integer = -1); overload;
207+ function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
208+ ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
209+ procedure WriteCurrency(ACell: PCell; AValue: Double;
210+ ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
211+
212+ { Writing of date/time values }
213+ function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
214+ procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
215+ function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
216+ ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
217+ procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
218+ ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
219+ function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
220+ ANumFormatStr: String): PCell; overload;
221+ procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
222+ ANumFormatStr: String); overload;
223+
224+ { Writing of number values }
225+ function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
226+ procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
227+ function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
228+ ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;
229+ procedure WriteNumber(ACell: PCell; ANumber: Double;
230+ ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;
231+ function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
232+ ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
233+ procedure WriteNumber(ACell: PCell; ANumber: Double;
234+ ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
235+
236+ { Writing of string values }
237+ function WriteText(ARow, ACol: Cardinal; AText: ansistring;
238+ ARichTextParams: TsRichTextParams = nil): PCell; overload;
239+ procedure WriteText(ACell: PCell; AText: String;
240+ ARichTextparams: TsRichTextParams = nil); overload;
241+
242+ // the old string methods "WriteUTF8Text" are deprecated now
243+ ...
244+```
245+
246+Some of these methods exist in overloaded versions in which cell formatting parameters can be added together with the cell value. Correspondingly to writing, there is also a number of `worksheet` methods for reading the cell values:
247+```
248+type
249+ TsWorksheet = class
250+ ...
251+ { Reading cell content as a string }
252+ function ReadAsText(ARow, ACol: Cardinal): string; overload;
253+ function ReadAsText(ACell: PCell): string; overload;
254+ function ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;
255+
256+ { Reading cell content as a number }
257+ function ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
258+ function ReadAsNumber(ACell: PCell): Double; overload;
259+ function ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;
260+
261+ { Reading cell content as a date/time value }
262+ function ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
263+ function ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
264+ ...
265+```
266+
267+##### Index to Cell format
268+
269+`FormatIndex` is the index of the cell format record. It describes the formatting attributes of a cell. These records are collected by an internal list of the workbook and are defined like this:
270+```
271+type
272+ TsCellFormat = record
273+ FontIndex: Integer;
274+ TextRotation: TsTextRotation;
275+ HorAlignment: TsHorAlignment;
276+ VertAlignment: TsVertAlignment;
277+ Border: TsCellBorders;
278+ BorderStyles: TsCellBorderStyles;
279+ Background: TsFillPattern;
280+ NumberFormatIndex: Integer;
281+ NumberFormat: TsNumberFormat;
282+ NumberFormatStr: String;
283+ BiDiMode: TsBiDiMode; // bdDefault, bdLTR {left-to-right}, bdRTL {right-to-left)
284+ Protection: TsCellProtection; // cpLockCell, cpHideFormulas
285+ UsedFormattingFields: TsUsedFormattingFields;
286+ //uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign, uffBiDi, uffProtection, uffDoNotPrint
287+ end;
288+```
289+
290+ * `FontIndex`: text font by specifying the index in the workbook's font list
291+ * `TextRotation`: specifies whether the cell text is written horizontally or vertically
292+ * `HorAlignment`: left-aligned, horizontally centered, or right-aligned text
293+ * `VertAlignment`: top, bottom or vertically centered text
294+ * `Border`: a set of flags indicating that - if set - a border line is drawn at the left, top, right, or bottom cell edge. The lines are drawn according to the `BorderStyles` which define the linestyle and color of the border.
295+ * `Background`: a record defining the background fill of a cell (pattern style, pattern color, and background color - see chapter on cell background below).
296+ * `NumberFormat` and `NumberFormatStr` specify how number or date/time values are formatted (e.g., number of decimal places, long or short date format, etc.).
297+ * Only those format attributes for which a flag is set in the `UsedFormattingFields` are considered when formatting a cell. If a flag is not included then the corresponding attribute is ignored and replaced by its default value.
298+
299+For specifying a format for a given cell call the corresponding the worksheet method `WriteXXXX`, for retrieving a format call `ReadXXXX`. These methods usually get a pointer to the cell as a parameter, but there are also overloaded versions which accept the row and column index. Moreover, formatting styles can also be applied directly to the cell by using a record helper implemented in unit _fpsCell_.
300+
301+See cell formatting below for a more detailed description.
302+
303+#### Columns and rows
304+
305+Column and row records are added for each column and row having a non-default properties:
306+```
307+type
308+ TCol = record
309+ Col: Cardinal;
310+ Width: Single;
311+ ColWidthType: TsColWidthType; // = (cwtDefault, cwtCustom)
312+ FormatIndex: Integer;
313+ Options: TsColRowOptions; // set of [croHidden, croPageBreak]
314+ end;
315+ PCol = ^TCol;
316+
317+ TRow = record
318+ Row: Cardinal;
319+ Height: Single;
320+ RowHeightType: TsRowHeightType; // = (rhtDefault, rhtCustom, rhtAuto)
321+ FormatIndex: Integer;
322+ Options: TsColRowOptions; // set of [croHidden, croPageBreak]
323+ Hidden: Boolean;
324+ PageBreak: Boolean;
325+ end;
326+ PRow = ^TRow;
327+```
328+
329+##### Column width / row height
330+
331+**Column widths** and **row heights** can be specified in a variety of units defined by the type `TsSizeUnits = (suChars, suLines, suMillimeters, suCentimeters, suPoints, suInches)`. `suChars` refers to the count of `0` characters fitting into the column width - this is the way how Excel defines column widths. `suLines` is the number of lines fitting into the row height. Both units are based on the character size of the workbook's default font. The other units are conventional physical length units (1 cm = 10 mm, 1 inch = 25.4 mm = 72 pt). Fractional values are accepted. The workbook and worksheets store lengths internally in millimeters (`MyWorkbook.Units`).
332+
333+The Office applications usually adjust the row heights automatically according to the font or text rotation of the cell content. This case is identified by `RowHeightType` having the value `rhtAuto`. Since the worksheet cannot calculate text size very accurately automatic row heights are not written by FPSpreadsheet; they are replaced by the **default row height**. The default row height is also used if a row is empty, i.e. does not contain any data cells. Its value can be changed by calling the worksheet's `WriteDefaultRowHeight()` or by using the worksheet property `DefaultRowHeight`. In `WriteDefaultRowHeight`, the units must be specified while in `DefaultRowHeight` they are assumed to be lines. Similarly, the **default column width** can be specified by `WriteDefaultColWidth()` or the property `DefaultColWidth` (in characters).
334+
335+In order to overrun automatic and default row heights call the worksheet method `WriteRowHeight()`. These row records are identified by `RowHeightType` having the value `rhtCustom`. In the same way the width of columns can be set to a specific value by calling `WriteColWidth()`. `ColWidthType` of these columns is `cwtCustom`.
336+
337+The height/width of a particular row/column can be retrieved by means of the methods `GetRowHeight` or `GetColWidth`. Note that these methods return the default row heights/column widths if there are no `TRow`/`TCol` records.
338+```
339+type TsWorksheet = class
340+ ...
341+ { Set row height }
342+ procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
343+ { Set column width }
344+ procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
345+ { Set default row height }
346+ procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
347+ {Set default cokumn width }
348+ procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);
349+
350+ { Return row height }
351+ function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
352+ { Return column width }
353+ function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
354+ { Return default row height }
355+ function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
356+ { Return default column width }
357+ function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;
358+
359+ property DefaultRowHeight: Single; // in lines
360+ property DefaultColWidht: Single; // in characters
361+```
362+
363+There are also overloaded versions of these methods which do not require the `AUnits` parameter. In this case, row heights are defined in terms of line count, and column widths are defined in terms of character count. Note that these variants are from previos versions and are deprecated now.
364+
365+##### Column and row formats
366+
367+The `FormatIndex` element of the row and column records format applied to the entire row or column. Together with cells, these formats are stored as `TsCellFormat` records in an internal workbook list, `FormatIndex` is the index of the format properties into this list. See details in section Cell formatting. **Row and column formats** are primarily applied to empty cells, but if a new cell is added it will automatically get the format of the row or column. (If both row and column have different formats then the row format will be used).
368+
369+Here is a listing of the worksheet methods available for column and row formatting:
370+```
371+type TsWorksheet = class
372+ // Assign a format to a column or row
373+ procedure WriteColFormatIndex(ACol: Cardinal; AFormatIndex: Integer);
374+ procedure WriteRowFormatIndex(ARow: Cardinal; AFormatIndex: Integer);
375+
376+ // Query the format of a column or row
377+ function GetColFormatIndex(ACol: Cardinal): Integer;
378+ function GetRowFormatIndex(ARow: Cardinal): Integer;
379+
380+ // Returns the font assigned to a column or row
381+ function ReadColFont(ACol: PCol): TsFont;
382+ function ReadRowFont(ARow: PRow): TsFont;
383+
384+ // Check whether any column or row uses a special format
385+ function HasRowFormats: Boolean;
386+ function HasColFormats: Boolean;
387+```
388+
389+##### Hidden columns / rows
390+
391+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** :
392+```
393+type TsWorksheet = class
394+ ...
395+ { Hide column/row }
396+ procedure HideCol(ACol: Cardinal);
397+ procedure HideRow(ARow: Cardinal);
398+
399+ { Show a previously hidden column/row }
400+ procedure ShowCol(ACol: Cardinal);
401+ procedure ShowRow(ARow: Cardinal);
402+
403+ { Check whether column/row is hidden }
404+ function ColHidden(ACol: Cardinal): Boolean;
405+ function RowHidden(ARow: Cardinal): Boolean;
406+```
407+
408+##### Page breaks
409+
410+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:
411+```
412+type TsWorksheet = class
413+ ...
414+ { Enforce a page break before the specified column/row }
415+ procedure AddPageBreakToCol(ACol: Cardinal);
416+ procedure AddPageBreakToRow(ARow: Cardinal);
417+
418+ { Removes a page break previously added to a column/row }
419+ procedure RemovePageBreakFromCol(ACol: Cardinal);
420+ procedure RemovePageBreakFromRow(ARow: Cardinal);
421+
422+ { Checks whether a page break is forced before the specified column/row }
423+ function IsPageBreakCol(ACol: Cardinal): Boolean;
424+ function IsPageBreakRow(ARow: Cardinal): Boolean;
425+```
426+
427+#### Events
428+
429+Worksheets and workbooks fire a series of events such as OnChangeCell, OnChangeFont, etc. The events usually are intended for interaction with the [[FPSpreadsheet tutorial: Writing a mini spreadsheet application|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. `[[TsWorksheetGrid|WorksheetGrid]].OnEditingDone` instead of `WorksheetGrid.Worksheet.OnChangeCell`.
430+
431+### Formulas
432+
433+Two kinds of formulas are supported by FPSpreadsheet:
434+
435+ * **String formulas** : These are written in strings just like in the office applications, for example `"=ROUND(A1+B1,0)"`. They are used internally in the files of Open/LibreOffice and Excel _.xlsx_.
436+ * **RPN formulas** are used internally by the binary _.xls_ Excel files. They are written in Reverse Polish Notation (RPN), for example: `A1, B1, Add, 0, ROUND`. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
437+
438+FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated.
439+
440+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.
441+
442+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`.
443+
444+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.
445+
446+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`.
447+
448+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`.
449+
450+The most general setting regarding formulas, therefore, is
451+```
452+MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];
453+```
454+
455+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.
456+
457+#### String formulas
458+
459+String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is `WriteFormula`:
460+```
461+var
462+ MyWorksheet: TsWorksheet;
463+ //...
464+ MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
465+ // By default, use dot as decimal and comma as list separator!
466+```
467+
468+A few notes on **syntax** :
469+
470+ * 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.
471+ * The case of the formula name is ignored.
472+ * Spaces can be added for better readability, but they will be lost when saving.
473+ * Strings must be enclosed in double quotes.
474+ * The corner points of a cell range must be separated by a colon (":"), e.g. `A1:C3`. Unordered ranges will be rearranged when the formula is parsed, i.e. `C3:A1` becomes `A1:C3`.
475+ * Links to other worksheets must follow the Excel syntax which separates the sheetname and cell address by a "!". An single cell, for example, can be linked by `Sheet1!A1`. An range of sheets must be placed before the cell or cell range, e.g. `Sheet1:Sheet2!A1:C3`. Note that the Open/LibreOffice syntax with a separating point and reference to the corner points of the 3d box (i.e., `Sheet1.A1:Sheet2.C3`) is not supported. Note also that the worksheet(s) to which the formula links must exist at the time when the formula is added; otherwise the link will be replaced by the error code `#REF!`, and the formula will not be usable even if the missing sheet is added later.
476+ * Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
477+ * 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.
478+
479+```
480+var
481+ MyWorksheet: TsWorksheet;
482+ //...
483+ MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
484+ // Because of the "true" the formula parser accepts the comma as decimal and the
485+ // semicolon as list separator if the workbook's FormatSettings are set up like this.
486+```
487+
488+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.
489+```
490+var
491+ MyWorksheet: TsWorksheet;
492+ cell: PCell;
493+ //...
494+ cell := MyWorksheet.FindCell(0, 1);
495+ WriteLn('The formula in internal format is ', MyworkSheet.ReadFormula(cell));
496+ WriteLn('The localized formula is ', MyWorksheet.ReadFormulaAsString(cell, true));
497+ //-------------------------------------------------------------------
498+ // For the previous example, this will result in the following output
499+ The formula in internal format is ROUND(A1+B2+1.215,0)
500+ The localized formula is ROUND(A1+B2+1,215;0)
501+```
502+
503+#### RPN formulas
504+
505+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|"RPN Formulas in FPSpreadsheet"]].
506+
507+#### Shared formulas and array formulas
508+
509+ * **Shared formulas** are only supported for reading (from Excel files).
510+ * **Array formulas** are not supported, currently.
511+
512+#### List of built-in formulas
513+
514+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 [[FPSpreadsheet: List of formulas|separate document "List of formulas"]].
515+
516+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.
517+
518+#### Extending FPSpreadsheet by user-defined formulas
519+
520+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_ :
521+```
522+procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
523+ const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;
524+
525+ procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
526+ const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;
527+```
528+
529+ * `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.
530+ * `AResultType` is a character which identifies the data type of the function result:
531+ * `'F'` \- floating point number
532+ * `'I'` \- integer
533+ * `'D'` \- date/time
534+ * `'B'` \- boolean
535+ * `'S'` \- string
536+ * `'C'` \- cell address, e.g. 'A1'
537+ * `'R'` \- cell range address, e.g. 'A1:C3'
538+ * `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:
539+ * `'?'` \- any type
540+ * `'+'` \- 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.
541+ * lowercase `'f', 'i', 'd', 'b', 's'` indicate optional parameters of the type explained above. Of course, uppercase symbols cannot follow lower-case symbols.
542+ * `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.
543+ * `ACallback` identifies which function is called by FPSpreadsheet for calculation of the formula. It can either be a procedure or an event handler.
544+
545+```
546+type
547+ TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
548+ TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;
549+```
550+
551+The `TsExpressionResult` is a variant record containing result or argument data of several types:
552+```
553+type
554+ TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
555+ rtCell, rtCellRange, rtError, rtAny);
556+
557+ TsExpressionResult = record
558+ Worksheet : TsWorksheet;
559+ ResString : String;
560+ case ResultType : TsResultType of
561+ rtEmpty : ();
562+ rtError : (ResError : TsErrorValue);
563+ rtBoolean : (ResBoolean : Boolean);
564+ rtInteger : (ResInteger : Int64);
565+ rtFloat : (ResFloat : TsExprFloat);
566+ rtDateTime : (ResDateTime : TDatetime);
567+ rtCell : (ResRow, ResCol : Cardinal);
568+ rtCellRange : (ResCellRange : TsCellRange);
569+ rtString : ();
570+ end;
571+
572+ TsExprParameterArray = array of TsExpressionResult;
573+```
574+
575+As an example we show here the code for the `CONCATENATE()` formula which joins two or more strings:
576+```
577+const
578+ INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;
579+ ...
580+ RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);
581+
582+ procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
583+ // CONCATENATE( text1, text2, ... text_n )
584+ var
585+ s: String;
586+ i: Integer;
587+ begin
588+ s := '';
589+ for i:=0 to Length(Args)-1 do
590+ begin
591+ if Args[i].ResultType = rtError then
592+ begin
593+ Result := ErrorResult(Args[i].ResError);
594+ exit;
595+ end;
596+ s := s + ArgToString(Args[i]);
597+ // "ArgToString" simplifies getting the string from a TsExpressionResult as
598+ // a string may be contained in the ResString and in the ResCell fields.
599+ // There is such a function for each basic data type.
600+ end;
601+ Result := StringResult(s);
602+ // "StringResult" stores the string s in the ResString field of the
603+ // TsExpressionResult and sets the ResultType to rtString.
604+ // There is such a function for each basic data type.
605+ end;
606+```
607+
608+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.
609+
610+#### Unsupported formulas
611+
612+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.
613+
614+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.
615+
616+### Cell formatting
617+
618+#### Number and date/time formats
619+
620+Numbers and date/time values can be displayed in a variety of formats. In FPSpreadsheet this can be achieved in two ways:
621+
622+ * using **built-in number formats** by specifiying a value for the `NumberFormat` of the cell
623+ * using a custom **format string**.
624+
625+Number formats can be specified by these worksheet methods:
626+```
627+type
628+ TsWorksheet = class
629+ public
630+ // Set number formats alone
631+ function WriteNumberFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
632+ const AFormatString: String = ''): PCell; overload;
633+ procedure WriteNumberFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
634+ const AFormatString: String = ''); overload;
635+
636+ function WriteNumberFormat(ARow, ACol: Cardinal; ANumFormat: TsNumberFormat;
637+ ADecimals: Integer; ACurrencySymbol: String = ''; APosCurrFormat: Integer = -1;
638+ ANegCurrFormat: Integer = -1): PCell; overload;
639+ procedure WriteNumberFormat(ACell: PCell; ANumFormat: TsNumberFormat;
640+ ADecimals: Integer; ACurrencySymbol: String = '';
641+ APosCurrFormat: Integer = -1; ANegCurrFormat: Integer = -1); overload;
642+
643+ function WriteFractionFormat(ARow, ACol: Cardinal; AMixedFraction: Boolean;
644+ ANumeratorDigits, ADenominatorDigits: Integer): PCell; overload;
645+ procedure WriteFractionFormat(ACell: PCell; AMixedFraction: Boolean;
646+ ANumeratorDigits, ADenominatorDigits: Integer); overload;
647+
648+ // Set date/time formats alone
649+ function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
650+ const AFormatString: String = ''): PCell; overload;
651+ procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
652+ const AFormatString: String = ''); overload;
653+
654+
655+ // Set cell values and number formats in one call
656+
657+ // number values
658+ function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
659+ AFormat: TsNumberFormat = nfGeneral; ADecimals: Byte = 2;
660+ ACurrencySymbol: String = ''): PCell; overload;
661+ procedure WriteNumber(ACell: PCell; ANumber: Double; AFormat: TsNumberFormat = nfGeneral;
662+ ADecimals: Byte = 2; ACurrencySymbol: String = ''); overload;
663+ function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
664+ AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
665+ procedure WriteNumber(ACell: PCell; ANumber: Double;
666+ AFormat: TsNumberFormat; AFormatString: String); overload;
667+
668+ // date/time values
669+ function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
670+ AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''): PCell; overload;
671+ procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
672+ AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''); overload;
673+ function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
674+ AFormatStr: String): PCell; overload;
675+ procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
676+ AFormatStr: String); overload;
677+
678+ // currency values
679+ function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
680+ AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
681+ ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
682+ ANegCurrFormat: Integer = -1): PCell; overload;
683+ procedure WriteCurrency(ACell: PCell; AValue: Double;
684+ AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
685+ ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
686+ ANegCurrFormat: Integer = -1); overload;
687+ function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
688+ AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
689+ procedure WriteCurrency(ACell: PCell; AValue: Double;
690+ AFormat: TsNumberFormat; AFormatString: String); overload;
691+ ...
692+```
693+
694+##### Built-in number formats
695+
696+The **built-in formats** are defined by the enumeration `TsNumberFormat`. In spite of its name, the elements cover both number and date/time values:
697+```
698+type
699+ TsNumberFormat = (
700+ // general-purpose for all numbers
701+ nfGeneral,
702+ // numbers
703+ nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
704+ // currency
705+ nfCurrency, nfCurrencyRed,
706+ // dates and times
707+ nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
708+ nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
709+ // other (using format string)
710+ nfCustom);
711+```
712+
713+ * **nfGeneral** corresponds to the default formatting showing as many decimals as possible (the number `3.141592654` would be unchanged.)
714+ * **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`.
715+ * **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.
716+ * **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).
717+ * **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).
718+ * **nfFraction** presents a number as a fraction. Details (mixed fraction?, maximum digit count for numerator or denominator) for can be specified in the worksheet method `WriteFractionFormat`.
719+ * **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.
720+ * **nfCurrendyRed** like `nfCurrency`, in addition negative values are displayed in red.
721+ * **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.
722+ * **nfShortDate** creates a date string showing day + two-digit month + two-digit year
723+ * **nfShortTime** creates a time string showing hours + minutes.
724+ * **nfLongTime** , similar, but includes seconds as well
725+ * **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.
726+ * **nfLongTimeAM** , like `nfShortTimeAM`, but includes seconds
727+ * **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.
728+ * **nfCustom** allows to specify a dedicated formatting string.
729+
730+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.
731+
732+##### Number format strings
733+
734+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).
735+
736+Here is a basic list of the symbols used:
737+
738+| Symbol | Meaning | Format string: Number --> Output string |
739+|---|---|---|
740+| `General` | Displays all decimal places of the number | `'General'`: · 1.2345678 --> `'1.2345678'` |
741+| `0` | Displays insignificant zeros if a number has less digits than there are zeros in the format. If used for decimal places then the number is rounded to as many decimal places as 0s are found. | `'000'`: 1 --> `'001'` · `'0.0'`: 1 --> `'1.0'` · `'0.0'`: 1.2345678 --> `'1.2'` |
742+| `*` | Like "0" above, but does not display insignificant zeros. | `'0.*'`: 1 --> `'1.'` · `'0.*'`: 1.2345678 --> `'1.2'` |
743+| `?` | Like "0" above, but insignificant zeros are replaced by space characters. Good for aligning decimal points and fractions | `'??0'`: 1 --> `' 1'` · `'0.0??'`: 1 --> `'1.0 '` |
744+| `.` | Decimal separator; will be replaced by the value used in the `DecimalSeparator` of the workbook's `FormatSettings` | `'0.00'`: 8.9 --> `'8.90'` |
745+| `,` | Thousand separator; will be replaced by the value used in the `ThousandSeparator` of the workbook's `FormatSettings`. If at the end of a number formatting sequence the displayed value is divided by 1000. | `'#,##0.00'`: 1200 --> `'1,200.00'` · `'0.00,'`: 1200 --> `'1.20'` |
746+| `E+`, `e+` | Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown for positive and negative exponents. | `'0.00E+00'`: 1200 --> `1.20E+03` |
747+| `E-`, `e-` | Displays a number in exponential format. The digits used for the exponent are defined by the number of zeros added the this symbol. The sign of the exponent is shown only for negative exponents. | `'0.00e-000'`: 1200 --> `1.20e003` |
748+| `%` | Displays the number as a "percentage", i.e. the number is multiplied by 100 and a `%` sign is added. | `'0.0%'`: 0.75 --> `75.0%` |
749+| `/` | This symbol has two meanings: if the cell represents a "number" then the slash indicates formatting as fraction, place holders for numerator and denominator must follow. If the cell represents a "date/time" then the slash indicates the date separator which will be replaced by the `DateSeparator` of the workbook's `FormatSettings` | `'#/#'`: 1.5 --> `'3/2'` · `'# #/#'`: 1.5 --> `'1 1/2'` · `'# #/16'`: 1.5 --> `'1 8/16'` · also: see date/time examples below |
750+| `:` | Separator between hours, minutes and seconds of a date/time value. Will be replaced by the `TimeSeparator` of the workbook's `FormatSettings`. | see examples below |
751+| `yyyy` | Place holder for the year of a date/time value. The year is displayed as a four-digit number. | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` · In this example, the `DateSeparator` is a dash character (`-`). |
752+| `yy` | Place holder for the year of a date/time value. The year is displayed as a two-digit number. | `'yy/mm/dd'`: · Jan 3, 2012 --> `'12-01-03'` |
753+| `m` | Place holder for the month of a date/time value. The month is shown as a number without extra digits. · Please note that the `m` code can also be interpreted as the "minutes" of a time value (see below). | `'yyyy/m/dd'`: · Jan 3, 2012 --> `'2012-1-03'` |
754+| `mm` | Place holder for the month of a date/time value. The month is shown as a two-digit number, i.e. a leading zero is added for January to September. · Please note that the `mm` code can also be interpreted as the "minutes" of a time value (see below). | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` |
755+| `mmm` | Place holder for the month of a date/time value. The month is displayed by its abbreviated name. | `'yyyy/mmm/dd'`: · Jan 3, 2012 --> `'2012-Jan-03'` |
756+| `mmmm` | Place holder for the month of a date/time value. The month is displayed by its full name. | `'yyyy/mmm/dd'`: · Jan 3, 2012 --> `'2012-January-03'` |
757+| `d` | Place holder for the day of a date/time value to be displayed as a number. The day is displayed as a simple number, without adding a leading zero. | `'yyyy/mm/d'`: · Jan 3, 2012 --> `'2012-01-3'` |
758+| `dd` | Place holder for the day of a date/time value to be displayed as a number. `dd` adds a leading zero to single-digit day numbers. | `'yyyy/mm/dd'`: · Jan 3, 2012 --> `'2012-01-03'` |
759+| `ddd` | Place holder for the day of a date/time value. The day is displayed as its abbreviated name. | `'dddd, yyyy/mm/ddd'`: · Jan 03, 2012 --> `'Tue 2012-01-03'` |
760+| `dddd` | Place holder for the day of a date/time value. The day is displayed as its full name. | `'dddd, yyyy/mmmm/dd'`: · Jan 03, 2012 --> `'Tuesday 2012-01-03'` |
761+| `h` | Place holder of the hour part of a date/time value. The hour is displayed as a simple number, without adding a leading zero. | `'h:mm'`: · 0.25 --> `'6:00'` |
762+| `hh` | Place holder of the hour part of a date/time value. The hour is displayed with a leading zero if the hour is less than 10. | `'hh:mm'`: · 0.25 --> `'06:00'` |
763+| `[hh]`, or `[h]` | Displays elapsed time such that the hour part can become greater than 23 | `'[h]:mm'`: · 1.25 --> `'30:00'` |
764+| `m` | Place holder of the minutes part of a date/time value. The minutes are shown as a simple number without adding a leading zero. Note that if the `m` codes are surrounded by date symbols (`y`, `d`) then they are interpreted as "month". | `'h:m'`: · 0.25 --> `'6:0'` |
765+| `mm` | Place holder of the minutes part of a date/time value. Single-digit minutes are displayed with a leading zero. Note that if the `mm` code is surrounded by date symbols (`y`, `d`) then it is interpreted as "month". | `'h:mm'`: · 0.25 --> `'6:00'` |
766+| `[mm]`, or `[m]` | Displays elapsed time such that the minute part can become greater than 59 | `'[mm]:ss'`: · 1.25 --> `'1800:00'` |
767+| `s` | Place holder of the seconds part of a date/time value. The seconds are displayed as a simple number, without adding a leading zero. | `'hh:mm:s'`: · 0.25 --> `'06:00:0'` |
768+| `ss` | Place holder of the seconds part of a date/time value. Single-digit seconds are displayed with a leading zero. | `'hh:mm:ss'`: · 0.25 --> `'06:00:00'` |
769+| `[ss]`, or `[s]` | Displays elapsed time such that the seconds part can become greater than 59 | `'[ss]'`: · 1.25 --> `'108000'` |
770+| `AM/PM`, `am/pm`, `A/P`, or `a/p` | Displays the time in the 12-hour format. | `'hh:mm:ss AM/PM'`: · 0.25 --> `'6:00:00 AM'` |
771+| `"` | The text enclosed by quotation marks is inserted into the formatted strings literally. | `'yyyy"/"mm"/"dd'`: · Jan 3, 2012 --> `'2012/01/03'` (i.e. the `/` is not replaced by the `DateSeparator` of the workbook). |
772+| `\` | The next character of the format string appears in the result string literally. The `\` itself does not show up. | `'yyyy\/mm\/dd'`: · Jan 3, 2012 --> `'2012/01/03'` |
773+| `;` | A format string can contain up to three sections separated by the semicolon. The first section is used for positive numbers, the second section for negative numbers, and the third section for zero numbers. If the third section is missing then a zero value is formatted as specified in the first section. If the second section is missing as well then all values are formatted according to the first section. | `'"#,##0"$"';-#,##0"$";"-"'`: · 1200 --> `'1,200$'` · -1200 --> `'1,200$'` · 0 --> `'-'` |
774+| `(`, and `)` | Sometimes used for currency values to indicate negative numbers, instead of minus sign | `'#,##0"$";(#,##0)"$"'`: · -1200 --> `'(1200)$'` |
775+| `[red]` | The formatted string is displayed in the specified color. Instead of `[red]`, you can use accordingly `[black]`, `[white]`, `[green]`, `[blue]`, `[magenta]`, `[yellow]`, or `[cyan]`. Often used to highlight negative currency values. | `'"$" #,##0.00;[red]("$" #,##0.00)'`: · -1200 --> '`($ 1200.00)`' |
776+
777+##### A note on Excel dates
778+
779+There is a discrepancy between FPSpreadsheet and Excel presentation of dates: Excel incorrectly assumes that the year 1900 was a leap year and thus allows for the date Febr 29 1900. FPSpreadsheet does the date calculation correctly. As consequence, dates before March 1 1900 are off by one! BTW, FPSpreadsheet handles this issue in the same way as LibreOffice Calc.
780+
781+#### Colors
782+
783+FPSpreadsheet supports colors for text, cell background, and cell borders. The basic EGA colors are declared in unit _fpstypes_ as constants:
784+
785+[![Predefined colors](/wiki-images/7/7b/DefaultPalette.png)](</File:DefaultPalette.png> "Predefined colors")
786+```
787+type
788+ TsColor = DWORD;
789+
790+ const
791+ scBlack = $00000000;
792+ scWhite = $00FFFFFF;
793+ scRed = $000000FF;
794+ scGreen = $0000FF00;
795+ scBlue = $00FF0000;
796+ scYellow = $0000FFFF;
797+ scMagenta = $00FF00FF;
798+ scCyan = $00FFFF00;
799+ scDarkRed = $00000080;
800+ scDarkGreen = $00008000;
801+ scDarkBlue = $00800000;
802+ scOlive = $00008080;
803+ scPurple = $00800080;
804+ scTeal = $00808000;
805+ scSilver = $00C0C0C0;
806+ scGray = $00808080;
807+ scGrey = scGray; // redefine to allow different spelling
808+
809+ // Identifier for undefined color
810+ scNotDefined = $40000000;
811+
812+ // Identifier for transparent color
813+ scTransparent = $20000000;
814+```
815+
816+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.)
817+
818+The high order byte is usually zero but is used internally to identify special color values, such as for undefined or transparent colors.
819+
820+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **Note:** In older versions of the library colors were defined as indexes into a color palette. **THIS IS NO LONGER WORKING.**
821+
822+Unit _fpsutils_ contains some useful functions for **modification of colors** :
823+
824+ * `function GetColorName(AColor: TsColor): String;`
825+returns the name of the colors defined above, or a string showing the rgb components for other colors.
826+ * `function HighContrastColor(AColor: TsColor): TsColor;`
827+returns `scBlack` for a "bright", `scWhite` for a "dark" input color.
828+ * `function TintedColor(AColor: TsColor; tint: Double): TsColor;`
829+brightens or darkens a color by applying a factor `tint = -1..+1`, where `-1` means "100% darken", `+1` means "100% brighten", and `0` means "no change". The hue of the color is preserved.
830+
831+#### Cell background
832+
833+The cell background can be filled by predefined patterns which are identified by the record `TsFillPattern`:
834+```
835+type
836+ TsFillPattern = record
837+ Style: TsFillStyle; // fill style pattern as defined below
838+ FgColor: TsColor; // forground color of the fill pattern
839+ BgColor: TsColor; // background color of the fill pattern
840+ end;
841+
842+ TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
843+ fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
844+ fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
845+ fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
846+```
847+
848+ * Use the worksheet method `WriteBackground` to assign a fill pattern to a specific cell. Besides the cell address, this method requires the type of the fill pattern (`TsFillStyle`), and the foreground and background colors as specified by their `TsColor` values.
849+ * The fill pattern of a particular cell can be retrieved by calling the workbook method `ReadBackground`.
850+ * The simplified method `WriteBackgroundColor` can be used to achieve a uniform background color.
851+ * **Limitations:**
852+ * OpenDocument files support only uniform fills. The background color is a mixture of the foreground and background rgb components in a ratio defined by the fill pattern.
853+ * BIFF2 files support only a 12.5% black-and-white shaded pattern.
854+
855+```
856+type
857+ TsWorksheet = class
858+ public
859+ function WriteBackground(ARow, ACol: Cardinal; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor): PCell; overload;
860+ procedure WriteBackground(ACell: PCell; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor); overload;
861+
862+ function WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor): PCell; overload;
863+ procedure WriteBackgroundColor(ACell: PCell; AColor: TsColor); overload;
864+
865+ function ReadBackground(ACell: PCell): TsFillPattern;
866+ function ReadBackgroundColor(ACell: PCell): TsColor; overload;
867+ // ...
868+ end;
869+
870+ var
871+ cell: PCell;
872+ ...
873+ // Example 1: Assign a pattern of thin, horizontal, yellow stripes on a blue background to empty cell A1 (row 0, column 0)
874+ MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);
875+
876+ // Example 2: Uniform gray background color of cell B1 (row 0, column 1) containing the number 3.14
877+ cell := MyWorksheet.WriteNumber(0, 1, 3.14);
878+ MyWorksheet.WriteBackgroundColor(cell, clSilver);
879+```
880+
881+#### Cell borders
882+
883+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`:
884+```
885+type
886+ TsCellBorder = (cbNorth, cbWest, cbEast, cbSouth, cbDiagUp, cbDiagDown);
887+ TsCellBorders = set of TsCellBorder;
888+```
889+
890+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:
891+```
892+MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // A1: row 0, column 0
893+ MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: row 0, column 1
894+```
895+
896+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:
897+```
898+type
899+ TsLineStyle = (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair,
900+ lsMediumDash, lsDashDot, lsMediumDashDot, lsDashDotDot, lsMediumDashDotDot, lsSlantDashDot);
901+
902+ TsCellBorderStyle = record
903+ LineStyle: TsLineStyle;
904+ Color: TsColor;
905+ end;
906+
907+ TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;
908+
909+ TsWorksheet = class
910+ public
911+ function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; AStyle: TsCellBorderStyle): PCell; overload;
912+ procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; AStyle: TsCellBorderStyle); overload;
913+
914+ function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor): PCell; overload;
915+ procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor); overload;
916+
917+ function WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
918+ procedure WriteBorderColor(ACell: PCell; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
919+
920+ function WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
921+ procedure WriteBorderLineStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
922+
923+ function WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles): PCell; overload;
924+ procedure WriteBorderStyles(ACell: PCell; const AStyles: TsCellBorderStyles); overload;
925+
926+ function WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders): PCell; overload
927+ procedure WriteBorders(ACell: PCell; ABorders: TsCellBorders); overload
928+
929+ function ReadCellBorders(ACell: PCell): TsCellBorders;
930+ function ReadCellBorderStyle(ACell: PCell; ABorder: TsCellBorder): TsCellBorderStyle;
931+ function ReadCellBorderStyles(ACell: PCell): TsCellBorderStyles;
932+ ...
933+ end;
934+```
935+
936+The style of a given cell border can be specified by the following methods provided by the worksheet:
937+
938+ * **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.
939+ * **WriteBorderColor** changes the color of a given border without affecting the line style of this border.
940+ * **WriteBorderLineStyle** sets the line style of the border only, but leaves the color unchanged.
941+ * **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.
942+
943+This example adds a thin black border to the top, and a thick blue border to the bottom of cells A1 and B1:
944+```
945+var
946+ cellA1, cellB1: PCell;
947+ ...
948+ cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // cell A1: row 0, column 0
949+ MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);
950+ MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);
951+
952+ cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]); // cell B1: row 0, column 1
953+ MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles); // copy all border styles from cell A1 to B1
954+```
955+
956+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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.
957+
958+#### Fonts
959+
960+The cell text can displayed in various fonts. For this purpose, the workbook provides a list of `TsFont` items:
961+```
962+type
963+ TsFont = class
964+ FontName: String;
965+ Size: Single;
966+ Style: TsFontStyles;
967+ Color: TsColor;
968+ Position: TsFontPosition;
969+ end;
970+```
971+
972+ * The **FontName** corresponds to the name of the font as used by the operational system. In Windows, an example would be "Times New Roman".
973+ * The **FontSize** is given in "points", i.e. units 1/72 inch which are commonly used in Office applications.
974+ * The **FontStyle** 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.
975+ * The **Color** determines the foreground color of the text characters given in rgb presentation as discussed above.
976+ * The **Position** is either `fpNormal`, `fpSuperscript`, or `fpSubscript` and indicates whether the font size should be decreased by about 1/3 and the characters should be displaced up (superscript) or down (subscript).
977+
978+Every cell is provided with an **index into the font list**.
979+
980+In order to assign a particular font to a cell, use one of the following methods of `TsSpreadsheet`:
981+```
982+type
983+ TsSpreadsheet = class
984+ public
985+ function WriteFont(ARow, ACol: Cardinal; const AFontName: String;
986+ AFontSize: Single; AFontStyle: TsFontStyles; AFontColor: TsColor): Integer; overload;
987+ procedure WriteFont(ARow, ACol: Cardinal; AFontIndex: Integer); overload;
988+ function WriteFontColor(ARow, ACol: Cardinal; AFontColor: TsColor): Integer;
989+ function WriteFontSize(ARow, ACol: Cardinal; ASize: Integer): Integer;
990+ function WriteFontStyle(ARow, ACol: Cardinal; AStyle: TsFontStyles): Integer;
991+ // plus: overloaded versions accepting a pointer to a cell record instead of the row and column index as parameter
992+ // ...
993+ end;
994+```
995+
996+ * **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.
997+ * **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.
998+ * **WriteFontSize** replaces the size of the currently used font of the cell.
999+ * **WriteFontStyle** replaces the style (normal, bold, italic, etc.) of the currently used cell font.
1000+
1001+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.
1002+
1003+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`.
1004+
1005+Here is an **example** which decreases the size of all 10-point "Arial" fonts to 9-point:
1006+```
1007+var
1008+ i: Integer;
1009+ font: TsFont;
1010+ begin
1011+ for i := 0 to MyWorkbook.GetFontCount-1 do
1012+ begin
1013+ font := MyWorkbook.GetFont(i);
1014+ if (font.FontName = 'Arial') and (font.Size = 10.0) then
1015+ font.Size := 9.0;
1016+ end;
1017+ end;
1018+```
1019+
1020+#### Rich-text formatting
1021+
1022+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).
1023+
1024+For this purpose, unit _fpstypes_ declares the type `TsRichTextParams` which is an array of `TsRichTextParam` records:
1025+```
1026+type
1027+ TsRichTextParam = record
1028+ FontIndex: Integer;
1029+ FirstIndex: Integer;
1030+ end;
1031+
1032+ TsRichTextParams = array of TsRichTextParam;
1033+```
1034+
1035+`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.
1036+
1037+There are two ways to add "rich-text" formatting to a cell text:
1038+
1039+ * Embed corresponding HTML format codes into the cell text. This can be done using the method `WriteTextAsHTML` of the worksheet. In order to add the text "Area (m2)" to cell A1, pass the following HTML-coded string to this function
1040+
1041+```
1042+MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
1043+```
1044+
1045+ * Alternatively, the standard text writing method, `WriteText` can be called with an additional parameter specifiying the rich-text formatting parameters to be used directly:
1046+
1047+```
1048+var
1049+ richTextParams: TsRichTextParams;
1050+ fnt: TsFont;
1051+ cell: PCell;
1052+ begin
1053+ SetLength(rtp, 2);
1054+ cell := MyWorksheet.GetFont(0, 0);
1055+ fnt := MyWorksheet.ReadCellFont(cell);
1056+ richTextParams[0].FirstIndex := 8; // The superscript groups begins with "2" which is the (1-based) character #8 of the cell text.
1057+ richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
1058+ richTextParams[1].FirstIndex := 9; // Normal font again beginning with character #9.
1059+ richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
1060+ MyWorksheet.WriteUTF8Text(cell, 'Area (m2)', richTextParams);
1061+ end;
1062+```
1063+
1064+Use the worksheet method `DeleteRichTextParams` to remove rich-text formatting from a previously formatted cell.
1065+
1066+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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.
1067+
1068+#### Text rotation
1069+
1070+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.
1071+
1072+If you need this feature use the worksheet method `WriteTextRotation` and specify the text direction by an element of the enumeration type `TsTextRotation`:
1073+```
1074+type
1075+ TsTextRotation = (trHorizontal, rt90DegreeClockwiseRotation,
1076+ rt90DegreeCounterClockwiseRotation, rtStacked);
1077+
1078+ TsWorksheet = class
1079+ public
1080+ function WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation): PCell; overload;
1081+ procedure WriteTextRotation(ACell: PCell; ARotation: TsTextRotation); overload;
1082+
1083+ function ReadTextRotation(ACell: PCell): TsTextRotation;
1084+ // ...
1085+ end;
1086+
1087+ // example for counter-clockwise rotated text in cell A1:
1088+ WriteTextRotation(0, 0, rt90DegreeCounterClockwiseRotation);
1089+```
1090+
1091+[![Warning-icon.png](/wiki-images/b/b2/Warning-icon.png)](</File:Warning-icon.png>)
1092+
1093+**Warning:** Finer degrees of rotation which may be supported by some spreadsheet file formats are ignored.
1094+
1095+#### Text alignment
1096+
1097+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`:
1098+```
1099+type
1100+ TsHorAlignment = (haDefault, haLeft, haCenter, haRight, haJustified, haDistributed, haFilled);
1101+ TsVertAlignment = (vaDefault, vaTop, vaCenter, vaBottom);
1102+
1103+ TsWorksheet = class
1104+ public
1105+ function WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment): PCell; overload;
1106+ procedure WriteHorAlignment(ACell: PCell; AValue: TsHorAlignment); overload;
1107+ function ReadHorAlignment(ACell: PCell): TsHorAlignment;
1108+
1109+ function WriteVertAlignment(ARow, ACol: Cardinal; AValue: TsVertAlignment): PCell; overload;
1110+ procedure WriteVertAlignment(ACell: PCell; AValue: TsVertAlignment); overload;
1111+ function ReadVertAlignment(ACell: PCell): TsVertAlignment;
1112+ // ...
1113+ end;
1114+
1115+ // Example: Center the text in cell A1 both horizontally and vertically
1116+ MyWorksheet.WriteHorAlignment(0, 0, haCenter);
1117+ MyWorksheet.WriteVertAlignment(0, 0, vaCenter);
1118+```
1119+
1120+Explanation of special horizontal alignments:
1121+
1122+ * `haDistributed`: The cell text is word-wrapped and its spaces are expanded so that the entire width of the cell is occupied. The text is both left- and right-justified.
1123+ * `haJustified`: like `haDistributed`, expect for the last line which remains left-justified.
1124+ * `haFilled`: the text is repeated to fill the entire cell.
1125+
1126+#### Word wrap
1127+
1128+Text which is longer than the width of a cell can wrap into several lines by calling the method `WriteWordwrap` of the spreadsheet:
1129+```
1130+type
1131+ TsWorksheet = class
1132+ public
1133+ function WriteWordwrap(ARow, ACol: Cardinal; AValue: Boolean): PCell; overload;
1134+ procedure WriteWordwrap(ACell: PCell; AValue: Boolean); overload;
1135+ function ReadWordwrap(ACell: PCell): Boolean;
1136+ //...
1137+ end;
1138+
1139+ // Example: activate wordwrap in cell A1
1140+ MyWorksheet.WriteWordwrap(0, 0, true);
1141+```
1142+
1143+#### Merged cells
1144+
1145+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:
1146+```
1147+MyWorksheet.MergeCells('A1:D5');
1148+ // or: MyWorksheet.MergeCells(0, 0, 4, 3); // first row, first column, last row, last column
1149+```
1150+
1151+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.
1152+
1153+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:
1154+```
1155+MyWorksheet.UnmergeCells('B1');
1156+ // or: MyWorksheet.UnmergeCells(0, 1); // row, column of any cell within the range
1157+```
1158+
1159+Merged cells can be read from/written to all file formats except for `sfCSV`, `sfExcel2` and `sfExcel5` which do not support this feature natively.
1160+
1161+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:
1162+```
1163+var
1164+ cell, base: PCell;
1165+ r1,c1,r2,c2: Cardinal;
1166+ ...
1167+ cell := MyWorksheet.FindCell('B1');
1168+ if MyWorksheet.IsMerged(cell) then
1169+ begin
1170+ WriteLn('Cell B1 is merged.');
1171+
1172+ MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
1173+ WriteLn('The merged range is ' + GetCellRangeString(r1, c1, r2, c2));
1174+
1175+ base := MyWorksheet.FindMergeBase(cell);
1176+ WriteLn('The merge base is cell ' + GetCellString(base^.Row, base^.Col));
1177+ end;
1178+```
1179+
1180+#### Cell protection
1181+
1182+This is described in a separate section below.
1183+
1184+#### Miscellaneous
1185+
1186+##### Disable printing of specific cells
1187+
1188+Specific cells can be disabled from printing:
1189+```
1190+type
1191+ TsWorksheet = class
1192+ public
1193+ function WriteDoNotPrintCell(ARow, ACol: Cardinal; AValue: boolean): PCell; overload;
1194+ procedure WriteDoNotPrintCell(ACell: PCell; AValue: Boolean); overload;
1195+ function ReadDoNotPrintCell(ACell: PCell): Boolean;
1196+```
1197+
1198+### Additional data
1199+
1200+#### Cell comments
1201+
1202+Comments can be attached to any cell by calling
1203+```
1204+MyWorksheet.WriteComment(0, 0, 'This is a comment for cell A1');
1205+```
1206+
1207+They are stored in an internal list of the worksheet. Use the corresponding worksheet methods to access comments:
1208+
1209+ * If you want to know whether a particular cell contains a comment call the worksheet method `HasComment(cell)`.
1210+ * For retrieving a cell comment use the method `ReadComment(cell)`, or its overloaded companion `ReadComment(ARow, ACol)`.
1211+ * The total number of comments can be retrieved from `worksheet.Comments.Count`.
1212+
1213+#### Hyperlinks
1214+
1215+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
1216+```
1217+procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');
1218+```
1219+
1220+ * The hyperlink target, passed as parameter `ATarget`, must be a fully qualified [URI (Uniform resource identifier)](<http://en.wikipedia.org/wiki/Uniform_Resource_Identifier>) consisting of a protocol phrase (e.g., `http://`, `file:///`, `mailto:`, etc.) followed by specific information such as web URL, filename, or e-mail address and an optional bookmark identification separated by the character `'#'`. An exception are internal hyperlinks which enable to jump to a cell in the current workbook; they consist of the optional worksheet name and the cell address separated by the character `'!'`.
1221+ * The optional `Tooltip` parameter is evaluated by Excel to display it in a hint window if the mouse if above the hyperlink.
1222+
1223+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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.
1224+
1225+**Examples:**
1226+```
1227+// Open the web site www.google.com
1228+ MyWorksheet.WriteText(0, 0, 'Open google');
1229+ MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');
1230+
1231+ // Open the local file with the absolute path "C:\readme.txt" (assuming Windows)
1232+ MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');
1233+
1234+ // Open the mail client to send a mail
1235+ MyWorksheet.WriteText('Send mail');
1236+ MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');
1237+
1238+ // Jump to a particular cell
1239+ MyWorksheet.WriteText(5, 0, 'Jump to cell A10 on sheet2');
1240+ MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');
1241+
1242+ // Jump to cell A10 on the current sheet and display a popup hint
1243+ MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Go to cell A10');
1244+```
1245+
1246+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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.
1247+
1248+#### Defined Names
1249+
1250+FPSpreadsheet allows to assign names to cells and cell ranges, simular to Excel and Calc. Since such "defined names" can be used in formulas the readability of formulas can be greatly improved.
1251+
1252+A defined name is a simple class of type `TsDefinedName` with a string property `Name` and a record property `Range` of type `TsCellRange3D` which lists the row and column indices of the top/left and bottom/right corners of the cell range, as well as the worksheet indices of these two corner points.
1253+```
1254+type
1255+ TsDefinedName = class
1256+ ...
1257+ public
1258+ function RangeAsString(AWorkbook: TsBasicWorkbook): String;
1259+ function RangeAsString_ODS(AWorkbook: TsBasicWorkbook): String;
1260+ class function ValidName(AName: String): Boolean;
1261+ property Name: String;
1262+ property Range: TsCellRange3D;
1263+ end;
1264+```
1265+
1266+A useful function may be the class function `ValidName` which checks the validity of a name:
1267+
1268+ * less than 256 characters long
1269+ * contains no spaces and no punctuation characters
1270+ * begins with a letter, with '_' or with '\'
1271+ * must not collide with cell references
1272+ * must be unique (case-insensitive)
1273+
1274+The `TsDefinedName` methods `RangeAsString` and `RangeAsString_ODS` return the defined range's Range parameters as a string in Excel and ODS syntax, respectively.
1275+
1276+
1277+The workbook and each worksheet have a list property `DefinedNames` which collects the defined names. The defined names stored in the workbook have **global** scope while those stored in worksheets are valid only **locally** within that sheet.
1278+
1279+ * There are overloaded methods `Add` for adding a single cell or a cell range in which the name as well as the cell/cell range parameters are provided. The methods return the index of the new item in the list.
1280+
1281+```
1282+var
1283+ idxCell, idxRange: Integer;
1284+ ...
1285+ // Global defined name
1286+ idxCell := workbook.DefinedNames.Add(AName: string; ASheetIndex: Integer; ARow, ACol: Cardinal);
1287+ idxRange := workbook.DefinedNames.Add(AName: String; ASheetIndex1, ASheetIndex2, ARow1, ACol1, ARow2, ACol2: Cardinal);
1288+ // Local defined name
1289+ idxCell := worksheet.DefinedNames.Add(AName: string; ASheetIndex: Integer; ARow, ACol: Cardinal);
1290+ idxRange := worksheet.DefinedNames.Add(AName: String; ASheetIndex1, ASheetIndex2, ARow1, ACol1, ARow2, ACol2: Cardinal);
1291+```
1292+
1293+ * The default property `Items[AIndex]` allows to read a defined name from the list. The following code lists all global defined names:
1294+
1295+```
1296+var
1297+ defName: TsDefinedName;
1298+ i: Integer;
1299+ ...
1300+ for i := 0 to workbook.DefinedNames.Count-1 do
1301+ begin
1302+ defName := workbook.DefinedNames[i];
1303+ WriteLn('Index ', i, ': Name="', defName.Name, '", Range=', defName.RangeAsString(workbook));
1304+ end;
1305+```
1306+
1307+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **Note:** Unlike Excel and ODS, local defined names are not allowed to have the same names as global defined names. Cross-worksheet local defined names are not allowed either.
1308+
1309+#### Images
1310+
1311+FPSpreadsheet supports embedding of images in worksheets. Use one of the worksheet methods `WriteImage()` to add an image to the worksheet:
1312+```
1313+MyWorksheet.WriteImage(row, col, filename, offsetX, offsetY, scaleX, scaleY);
1314+ MyWorksheet.WriteImage(row, col, stream, offsetX, offsetY, scaleX, scaleY);
1315+ MyWorksheet.WriteImage(row, col, imageindex, offsetX, offsetY, scaleX, scaleY);
1316+```
1317+
1318+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.
1319+
1320+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:
1321+```
1322+type
1323+ TsImageType = integer;
1324+ TGetImageSizeFunc = function (AStream: TStream; out AWidth, AHeight: DWord; out dpiX, dpiY: Double): Boolean;
1325+
1326+ function RegisterImageType(AMimeType, AExtension: String; AGetImageSize: TGetImageSizeFunc): TsImageType;
1327+```
1328+
1329+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:
1330+
1331+ * Predefine the widths of all columns at least up to the one containing the right edge of the image.
1332+ * Predefine the heights of all rows at least up to the one containing the lower edge of the image.
1333+ * If the workbook is to be saved in OpenDocument format add the image after changing column widths and row heights because ods anchors the image to the sheet, not to the cell (like Excel and FPSpreadsheet).
1334+ * If the exact size of the image is important make sure that it fits into a single cell.
1335+
1336+### Sorting
1337+
1338+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'`):
1339+```
1340+type
1341+ TsWorksheet = class
1342+ // ...
1343+ procedure Sort(const ASortParams: TsSortParams;
1344+ ARowFrom, AColFrom, ARowTo, AColTo: Cardinal); overload;
1345+ procedure Sort(ASortParams: TsSortParams; ARange: String); overload;
1346+ // ...
1347+ end;
1348+```
1349+
1350+The sorting criteria are defined by a record of type `TsSortParams`:
1351+```
1352+type
1353+ TsSortParams = record
1354+ SortByCols: Boolean;
1355+ Priority: TsSortPriority; // spNumAlpha ("Numbers first"), or spAlphaNum ("Text first")
1356+ Keys: TsSortKeys;
1357+ end;
1358+
1359+ TsSortKey = record
1360+ ColRowIndex: Integer;
1361+ Options: TsSortOptions; // set of [spDescending, spCaseInsensitive]
1362+ end;
1363+```
1364+
1365+ * 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).
1366+ * `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`).
1367+ * 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.
1368+
1369+`InitSortParams` is a handy utility to initialize the sorting parameters:
1370+```
1371+function InitSortParams(ASortByCols: Boolean = true; ANumSortKeys: Integer = 1;
1372+ ASortPriority: TsSortPriority = spNumAlpha): TsSortParams;
1373+```
1374+
1375+The next code fragment shows a typical sorting call:
1376+```
1377+var
1378+ sortParams: TsSortParams;
1379+ begin
1380+ sortParams := InitSortParams(true, 2); // sorting along columns, 2 sorting keys
1381+
1382+ // primary sorting key: column 3, ascending, case-insensitive
1383+ sortParams.Keys[0].ColRowIndex := 3;
1384+ sortParams.Keys[0].Options := [ssoCaseInsensitive];
1385+
1386+ // secondary sorting key: colum 1, descending
1387+ sortParams.Keys[1].ColRowIndex := 1;
1388+ sortParams.Keys[1].Options := [ssoDescending];
1389+
1390+ // The sorted block extends between cells A1 (row=0, col=0) and F10 (row=9, col=5)
1391+ MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
1392+ // or: MyWorksheet.Sort(sortParams, 'A1:F10');
1393+ end;
1394+```
1395+
1396+
1397+
1398+### Searching and replacing
1399+
1400+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.
1401+
1402+**Example:**
1403+```
1404+uses
1405+ fpsTypes, fpSpreadsheet, fpsUtils, fpsSearch, fpsAllFormats;
1406+ var
1407+ MyWorkbook: TsWorkbook;
1408+ foundWorksheet: TsWorksheet;
1409+ foundRow, foundCol: Cardinal;
1410+ MySearchParams: TsSearchParams;
1411+ begin
1412+ MyWorkbook := TsWorkbook.Create;
1413+ try
1414+ MyWorkbook.ReadFromFile(AFileName);
1415+
1416+ // Specify search criteria
1417+ MySearchParams.SearchText := 'Hallo';
1418+ MySearchParams.Options := [soEntireDocument];
1419+ MySearchParams.Within := swWorkbook;
1420+
1421+ // or: MySearchParaams := InitSearchParams('Hallo', [soEntireDocument], swWorkbook);
1422+
1423+ // Create search engine and execute search
1424+ with TsSearchEngine.Create(MyWorkbook) do begin
1425+ if FindFirst(MySearchParams, foundWorksheet, foundRow, foundCol) then begin
1426+ WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
1427+ while FindNext(MySeachParams, foundWorksheet, foundRow, foundCol) do
1428+ WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(foundRow, foundCol), ' of worksheet ', foundWorksheet.Name);
1429+ end;
1430+ Free;
1431+ end;
1432+ finally
1433+ MyWorkbook.Free;
1434+ end;
1435+ end;
1436+```
1437+
1438+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.
1439+
1440+The record `TsSearchParams` specifies the criteria used for searching:
1441+```
1442+type
1443+ TsSearchParams = record
1444+ SearchText: String;
1445+ Options: TsSearchOptions;
1446+ Within: TsSearchWithin;
1447+ end;
1448+```
1449+
1450+Besides the text to be searched (`SearchText`) it provides a set of options to narrow the search:
1451+
1452+ * `soCompareEntireCell`: Compares the `SearchText` with the entire cell content. If not contained in the `Options` then the cell text is compared only partially.
1453+ * `soMatchCase`: Perform a case-sensitive search
1454+ * `soRegularExpr`: The `SearchText` is considered as a regular expression
1455+ * `soAlongRows`: The search engine proceeds first along the rows. If not contained in the `Options` then the search proceeds along the columns.
1456+ * `soBackward`: The search begins at the end of the document, or runs backward from the active cell. If not contained in the `Options` then the search starts at the beginning of the document, or runs forward from the active cell.
1457+ * `soWrapDocument`: If a search has reached the end of the document the search is resumed at its beginning (or vice versa, if `soBackward` is used).
1458+ * `soEntireDocument`: Search begins at the first cell (or last cell if `soBackward` is used). If not contained in the `Options` then the search begins at the active cell of the worksheet. Ignored by `FindNext`.
1459+
1460+The record field `Within` identifies the part of the spreadsheet to be searched:
1461+
1462+ * `swWorkbook`: The entire workbook is searched. If the search phrase is not found on the first worksheet (or last worksheet if `soBackward` is used) then the search continues with the next (previous) sheet.
1463+ * `swWorksheet`: The search is limited to the currently active worksheet
1464+ * `swColumn`: Search is restricted to the column of the active cell
1465+ * `swRow`: Search is restricted to the row of the active cell.
1466+
1467+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.
1468+
1469+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:
1470+```
1471+type
1472+ TsReplaceParams = record
1473+ ReplaceText: String;
1474+ Options: TsReplaceOptions;
1475+ end;
1476+```
1477+
1478+The `ReplaceText` identifies the string which will replace the found text pattern. The `Options` define a set of criteria how the replacement is done:
1479+
1480+ * `roReplaceEntirecell`: Replaces the entire cell text by the `ReplaceText`. If not contained in the `Options` then only the part matching the `SearchText` is replaced.
1481+ * `roReplaceAll`: Performs the replacement in all found cells (i.e., simply call `ReplaceFirst` to replace all automatically).
1482+ * `roConfirm`: Calls an event handler for the `OnConfirmReplacement` event in which the user must specify whether the replacement is to be performed or not. Note that this event handler is mandatory if `roConfirm` is set.
1483+
1484+Use the function `InitReplaceParams` (in unit _fpsutils_) to initialize the replace parameters record with the provided (but optional) values.
1485+
1486+### Column and row operations
1487+
1488+The worksheet provides these methods for inserting, deleting, hiding or unhiding columns and rows:
1489+```
1490+type
1491+ TsWorksheet = class
1492+ ...
1493+ procedure DeleteCol(ACol: Cardinal);
1494+ procedure DeleteRow(ARow: Cardinal);
1495+
1496+ procedure InsertCol(ACol: Cardinal);
1497+ procedure InsertRow(ARow: Cardinal);
1498+
1499+ procedure RemoveCol(ACol: Cardinal);
1500+ procedure RemoveRow(ARow: Cardinal);
1501+
1502+ procedure RemoveAllCols;
1503+ procedure RemoveAllRows
1504+
1505+ procedure HideCol(ACol: Cardinal);
1506+ procedure HideRow(ARow: Cardinal);
1507+
1508+ procedure ShowCol(ACol: Cardinal);
1509+ procedure ShowRow(ARow: Cardinal);
1510+
1511+ function ColHidden(ACol: Cardinal): Boolean;
1512+ function RowHidden(ARow: Cardinal): Boolean;
1513+```
1514+
1515+ * When a column or row is **deleted** by `DeleteCol` or `DeleteRow`, any data assigned to this column or row are removed, i., cells, comments, hyperlinks, TCol or TRow records. Data at the right of or below the deleted column/row move to the left or up.
1516+
1517+ * `RemoveCol` and `RemoveRow`, in contract, **remove** only the column or row record, i.e. reset column width and row height to their default values. Cell, comment, and hyperlink data are not affected.
1518+
1519+ * `RemoveAllCols` **removes all** column records, i.e. resets all column widths; `RemoveAllRows` does the same with the row records and row heights.
1520+
1521+ * A column or row is **inserted** before the index specified as parameter of the `InsertXXX` method.
1522+
1523+### Page layout
1524+
1525+#### General
1526+
1527+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.
1528+```
1529+type
1530+ TsPageOrientation = (spoPortrait, spoLandscape);
1531+
1532+ TsPrintOption = (poPrintGridLines, poPrintHeaders, poPrintPagesByRows,
1533+ poMonochrome, poDraftQuality, poPrintCellComments, poDefaultOrientation,
1534+ poUseStartPageNumber, poCommentsAtEnd, poHorCentered, poVertCentered,
1535+ poDifferentOddEven, poDifferentFirst, poFitPages);
1536+
1537+ TsPrintOptions = set of TsPrintOption;
1538+
1539+ TsHeaderFooterSectionIndex = (hfsLeft, hfsCenter, hfsRight);
1540+
1541+ TsCellRange = record
1542+ Row1, Col1, Row2, Col2: Cardinal;
1543+ end;
1544+
1545+ TsPageLayout = class
1546+ ...
1547+ public
1548+ ...
1549+ { Methods }
1550+ // embedded header/footer images
1551+ procedure AddHeaderImage(AHeaderIndex: Integer;
1552+ ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1553+ procedure AddFooterImage(AFooterIndex: Integer;
1554+ ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1555+ procedure GetImageSections(out AHeaderTags, AFooterTags: String);
1556+ function HasHeaderFooterImages: Boolean;
1557+
1558+ // Repeated rows and columns
1559+ function HasRepeatedCols: Boolean;
1560+ function HasRepeatedRows: Boolean;
1561+ procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
1562+ procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);
1563+
1564+ // print ranges
1565+ function AddPrintRange(ARow1, ACol1, ARow2, ACol2: Cardinal): Integer; overload;
1566+ function AddPrintRange(const ARange: TsCellRange): Integer; overload;
1567+ function GetPrintRange(AIndex: Integer): TsCellRange;
1568+ function NumPrintRanges: Integer;
1569+ procedure RemovePrintRange(AIndex: Integer);
1570+
1571+ { Properties }
1572+ property Orientation: TsPageOrientation read FOrientation write FOrientation;
1573+ property PageWidth: Double read FPageWidth write FPageWidth;
1574+ property PageHeight: Double read FPageHeight write FPageHeight;
1575+ property LeftMargin: Double read FLeftMargin write FLeftMargin;
1576+ property RightMargin: Double read FRightMargin write FRightMargin;
1577+ property TopMargin: Double read FTopMargin write FTopMargin;
1578+ property BottomMargin: Double read FBottomMargin write FBottomMargin;
1579+ property HeaderMargin: Double read FHeaderMargin write FHeaderMargin;
1580+ property FooterMargin: Double read FFooterMargin write FFooterMargin;
1581+ property StartPageNumber: Integer read FStartPageNumber write SetStartPageNumber;
1582+ property ScalingFactor: Integer read FScalingFactor write SetScalingFactor;
1583+ property FitHeightToPages: Integer read FFitHeightToPages write SetFitHeightToPages;
1584+ property FitWidthToPages: Integer read FFitWidthToPages write SetFitWidthToPages;
1585+ property Copies: Integer read FCopies write FCopies;
1586+ property Options: TsPrintOptions read FOptions write FOptions;
1587+ property Headers[AIndex: Integer]: String read GetHeaders write SetHeaders;
1588+ property Footers[AIndex: Integer]: String read GetFooters write SetFooters;
1589+ property RepeatedCols: TsRowColRange read FRepeatedCols;
1590+ property RepeatedRows: TsRowColRange read FRepeatedRows;
1591+ property PrintRange[AIndex: Integer]: TsCellRange read GetPrintRange;
1592+ property FooterImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetFooterImages;
1593+ property HeaderImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetHeaderImages;
1594+ end;
1595+
1596+ TsWorksheet = class
1597+ ...
1598+ public
1599+ property PageLayout: TsPageLayout;
1600+ ...
1601+ end;
1602+```
1603+
1604+![Light bulb](https://upload.wikimedia.org/wikipedia/commons/d/d8/Nuvola_apps_ktip.png) **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.
1605+
1606+ * **Orientation** defines the orientation of the printed paper, either portrait or landscape.
1607+
1608+ * **Page width** and **page height** refer to the standard orientation of the paper, usually portrait orientation.
1609+
1610+ * **Left** , **top** , **right** and **bottom margins** are self-explanatory and are given in millimeters.
1611+
1612+ * **HeaderMargin** is understood - like in Excel - as the distance between the paper top edge and the top of the header, and **TopMargin** correspondingly is the distance between the top paper edge and the top of the first table row, i.e. if the header contains several line breaks it can reach into the the table part of the print-out. This is different from OpenDocument files where the header can grow accordingly.
1613+
1614+ * **StartPageNumber** should be altered if the print-out should not begin with page 1. This setting requires to add the option `poUseStartPageNumber` to the PageLayout's `Options` \- but this is normally done automatically.
1615+
1616+ * The **ScalingFactor** is given in percent and can be used to reduce the number of printed pages. Modifying this property clear the option `poFitToPages` from the PageLayout's `Options`.
1617+
1618+ * Alternatively to ScalingFactor, you can also use **FitHeightToPages** or **FitWidthToPages**. The option `poFitToPages` must be active in order to override the `ScalingFactor`setting. `FitHeightToPages` specifies the number of pages onto which the entire height of the printed worksheet should fit. Accordingly, `FitWidthToPages` can be used to define the number of pages on which the entire width of the worksheet has to fit. The value `0` has the special meaning of "use as many pages as needed". In this way, the setting "Fit all columns on one page" of Excel, for example, can be achieved by this code:
1619+
1620+```
1621+MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
1622+ MyWorksheet.PageLayout.FitWidthToPages := 1; // all columns on one page width
1623+ MyWorksheet.PageLayout.FitHeightToPages := 0; // use as many pages as needed
1624+```
1625+
1626+ * **Header rows and columns repeated on every printed page** can be defined by the `RepeatedCols` and `RepeatedRows` records; their elements `FirstIndex` and `LastIndex` refer to the indexes of the first and last column or row, respectively, to be repeated. Use the methods `SetRepeatedCols` and `SetRepeatedRows` to define these numbers. Note that the second parameter for the last index can be omitted to use only a single header row or column.
1627+
1628+ * **Print ranges** or **print areas** (using Excel terminology) can be used to restrict printing only to a range of cells. Use the methods `AddPrintRange` to define a cell range for printing: specify the indexes of the left column, top row, right column and bottom row of the range to be printed. A worksheet can contain several print ranges.
1629+
1630+ * **Copies** specifies how often the worksheet will be printed.
1631+
1632+ * The **Options** define further printing properties, their names are self-explaining. They were defined according to Excel files, some of them do not exist in ODS files and are ignored there.
1633+
1634+#### Headers and footers
1635+
1636+**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:
1637+
1638+ * `&L`: begins the **left-aligned section** of a header or a footer text definition
1639+ * `&C`: begins the **centered section** of a header or a footer text definition
1640+ * `&R`: begins the **right-aligned section** of a header or a footer text definition
1641+ * `&P`: **page number**
1642+ * `&N`: **page count**
1643+ * `&D`: current **date** of printing
1644+ * `&T`: current **time** of printing
1645+ * `&A`: **worksheet name**
1646+ * `&F`: **file name** without path
1647+ * `&Z`: **file path** without file name
1648+ * `&G`: embedded **image** \- use the methods `AddHeaderImage` or `AddFooterImage` to specify the image file; this also appends the `&G` to the other codes of the current header/footer section. Note that not all image types known by the Office application may be accepted. Currently the image can be _jpeg_ , _png_ , _gif_ , _bmp_ , _tiff_ , _pcx_ , _svg_ , _wmf_ or _emf_.
1649+ * `&B`: **bold** on/off
1650+ * `&I`: **italic** on/off
1651+ * `&U`: **underlining** on/off
1652+ * `&E`: **double-underlining** on/off
1653+ * `&S`: **strike-out** on/off
1654+ * `&H`: **shadow** on/off
1655+ * `&O`: **outline** on/off
1656+ * `&X`: **superscript** on/off
1657+ * `&Y`: **subscript** on/off
1658+ * `&"font"`: begin using of the **font** with the specified name, e.g. `&"Arial"`
1659+ * `&number`: begin using of the specified **font size** (in points), e.g. `&16`
1660+ * `&Krrggbb`: switch to the **font color** specified to the binary value of the specified color, e.g. use `&KFF0000` for red.
1661+
1662+The arrays `Headers[]`/`Footers[]` provide space for usage of three different headers or footers:
1663+
1664+ * `Headers[0]` refers to the header used on the **first page** only, similarly for `Footers[0]`. Instead of index `0` you can use the constant `HEADER_FOOTER_INDEX_FIRST`. Leave this string empty if there is no special first-page header/footer.
1665+ * `Headers[1]` refers to the header on pages with **odd page numbers** , similarly for `Footers[1]`. Instead of index `1` you may want to use the constant `HEADER_FOOTER_INDEX_ODD`.
1666+ * `Headers[2]` refers to the header on lages with **even page nubmers** , similarly for `Footers[2]`. Instead of index `2` you may want to use the constant `HEADER_FOOTER_INDEX_EVEN`.
1667+
1668+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:
1669+```
1670+MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T'; // centered "date time" on all pages as header
1671+ MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N'; // right-aligned "Page .. of ..." on odd pages as footer
1672+ MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N'; // dto, but left-aligned on even pages
1673+```
1674+
1675+### Protection
1676+
1677+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.
1678+
1679+Protection is handled at three levels: workbook protection, worksheet protection and cell protection.
1680+
1681+#### Workbook protection
1682+
1683+`TsWorkbook` contains a set of workbook or document protection options:
1684+
1685+ * `bpLockRevision`: specifies that the workbook is locked for revisions
1686+ * `bpLockStructure`: if this option is set then worksheets in the workbook cannot be moved, deleted, hidden, unhidden, or renamed, and new worksheets cannot be inserted.
1687+ * `bpLockWindows`: indicates that the workbook windows in the Office application are locked. Windows are the same size and position each time the workbook is opened by the Office application.
1688+
1689+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.
1690+
1691+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.
1692+
1693+Office applications are able to encrypt their spreadsheet files. The readers of FPSpreadsheet basically are not able to open these files. However, there exists a related package, **laz_fpspreadsheet_crypto.lpk** , which has access to decryption routines and is able to read xlsx and ods files. These readers are in the units _xlsxOOXML_crypto_ and _fpsOpendocument_crypto_ , respectively. Adding them to the uses clause of the application installs user-provided readers with `FormatID` `sfidOOXML_crypto` and `sfidOpenDocument_crypto`, respectively. If the password is known it can be specified as a parameter to the workbook's `LoadFromFile`/`ReadFromStream` methods. If no password is specified the workbook fires the event `OnQueryPassword` in which the user can provide the password, e.g. like this:
1694+```
1695+function TForm1.QueryPasswordHandler: String;
1696+ begin
1697+ Result := InputBox('Password required', 'Password', '');
1698+ end;
1699+
1700+ procedure TForm1.LoadWorkbook(AFileName: String);
1701+ var
1702+ workbook: TsWorkbook;
1703+ begin
1704+ workbook := TsWorkbook.Create;
1705+ try
1706+ workbook.OnQueryPassword := @QueryPasswordHandler;
1707+ workbook.ReadFromFile(AFileName);
1708+ ...
1709+ finally
1710+ workbook.Free;
1711+ end;
1712+ end;
1713+
1714+ // or
1715+
1716+ procedure TForm1.LoadWorkbookWithPassword(AFileName, APassword: STring);
1717+ var
1718+ workbook: TsWorkbook;
1719+ begin
1720+ workbook := TsWorkbook.Create;
1721+ try
1722+ workbook.ReadFromFile(AFileName, APassword);
1723+ ...
1724+```
1725+
1726+#### Worksheet protection
1727+
1728+`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:
1729+
1730+ * `spCells`: the cells in the sheet are protected. It depends on the level of cell protection whether a particular cell can be changed or not. By default, no cell can be changed.
1731+ * `spDeleteColumns`: deleting of columns is not be allowed
1732+ * `spDeleteRows`: it is not possible to delete rows
1733+ * `spFormatCells`: formatting of cells is not allowed
1734+ * `spFormatColumns`: columns cannot be formatted.
1735+ * `spFormatRows`: rows cannot be formatted
1736+ * `spInsertColumns`: it is not allowed to insert columns
1737+ * `spInsertRows`: rows cannot be inserted
1738+ * `spInsertHyperlinks`: it is not possible to insert new hyperlinks
1739+ * `spSort`: the worksheet is not allowed to be sorted.
1740+ * `spSelectLockedCells`: Cells which are locked cannot be selected any more.
1741+ * `spSelectUnlockedCells`: Even cells which are unlocked cannot be selected. Together with `spSelectLockedCells` this means that the selection in the worksheet is frozen.
1742+
1743+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)`.
1744+
1745+#### Cell protection
1746+
1747+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:
1748+
1749+ * `cpLockCell`: This option determines whether cell content can be modified by the user. Since it is on by default cells of a protected worksheet normally cannot be edited. In order to unlock some cells for user input the option `cpLockCell` must be removed from the protection of these cells.
1750+ * `cpHideFormulas`: prevents formulas from being shown in the Office application.
1751+
1752+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:
1753+```
1754+// query and modifiy the protection state of cell A1 (row=0, col=0)
1755+ var
1756+ cell: PCell;
1757+ cellprot: TsCellProtections;
1758+ ...
1759+ // Find the cell
1760+ cell := worksheet.FindCell(0, 0);
1761+ // query cell protection
1762+ cellprot := worksheet.ReadCellProtection(cell);
1763+ // Unlock the cell for editing, don't change the visibility of formulas
1764+ worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
1765+ // Hide formula of the cell and unlock the cell.
1766+ worksheet.WriteCellProtection(cell, [cpHideFormulas]);
1767+ // Activate protection
1768+ worksheet.Protect(true);
1769+```
1770+
1771+#### Passwords
1772+
1773+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:
1774+```
1775+type
1776+ TsCryptoInfo = record
1777+ PasswordHash: String;
1778+ Algorithm: TsCryptoAlgorithm; // caExcel, caSHA1, caSHA256, etc.
1779+ SaltValue: String;
1780+ SpinCount: Integer;
1781+ end;
1782+```
1783+
1784+[![Warning-icon.png](/wiki-images/b/b2/Warning-icon.png)](</File:Warning-icon.png>)
1785+
1786+**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.
1787+
1788+### Loading and saving
1789+
1790+#### Adding new file formats
1791+
1792+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.
1793+
1794+ * Write a unit implementing a reader and a writer for the new file format. They must inherit from the basic `TsCustomSpreadReader` and `TsCustomWriter`, respectively, - both are implemented in unit _fpsReaderWriter_ -, or from one of the more advanced ones belonging to the built-in file formats.
1795+ * Register the new reader/writer by calling the function `RegisterSpreadFileFormat` in the `initialization` section of this unit (implemented in unit _fpsRegFileFormats_):
1796+
1797+```
1798+function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
1799+ AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;
1800+```
1801+
1802+ * `AFormat` must have the value `sfUser` to register an external file format.
1803+ * `AReaderClass` is the class of the reader (or nil, if reading functionality is not implemented).
1804+ * `AWriterClass` is the class of the writer (or nil, if writing functionality is not implemented).
1805+ * `AFormatName` defines the name of the format as used for example in the filter list of file-open dialogs.
1806+ * `ATechnicalName` defines a shorter format name.
1807+ * `AFileExtensions` is an array of file extensions used in the files. The first array element denotes the default extension. The extensions must begin with a period as in `.xls`.
1808+ * The registration function returns a numerical value (`TsSpreadFormatID`) which can be used as format identifier in the workbook reading and writing functions which exist in overloaded version accepting a numerical value for the format specifier. In contract to the built-in formats the FormatID is negative.
1809+ * Finally, in your application, add the new unit to the `uses` clause. This will call the registrations function when the unit is loaded and make the new file format available to FPSpreadsheet.
1810+
1811+#### Stream selection
1812+
1813+Workbooks are loaded and saved by means of the `ReadFromFile` and `WriteToFile` methods, respectively (or by their stream counterparts, `ReadFromStream` and `WriteToStream`).
1814+
1815+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.
1816+
1817+ * Add the element `boBufStream` to the workbook's `Options`. In this case, a **"buffered" stream** is used for accessing the data. This kind of stream holds a memory buffer of a given size and swaps data to file if the buffer becomes too small.
1818+ * Add the element `boFileStream` to the workbook's `Options`. This option avoids memory streams altogether and creates temporary **files** if needed. This is, however, the slowest method of data access.
1819+ * If both options are set then `boBufStream` is ignored.
1820+ * In practice, however, the effect of the selected streams is not very large if memory is to be saved.
1821+
1822+#### Virtual mode
1823+
1824+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.
1825+
1826+These are the steps required to use this mode:
1827+
1828+ * Activate virtual mode by adding the option `boVirtualMode` to the `Options` of the workbook.
1829+ * Tell the spreadsheet writer how many rows and columns are to be written. The corresponding worksheet properties are `VirtualRowCount` and `VirtualColCount`.
1830+ * Write an event handler for the event `OnWriteCellData` of the worksheet. This handler gets the index of row and column of the cell currently being saved. You have to return the value which will be saved in this cell. You can also specify a template cell that physically exists in the workbook from which the formatting style is copied to the destination cell. Please be aware that when exporting a database, you are responsible for advancing the dataset pointer to the next database record when writing of a row is complete.
1831+ * Call the `WriteToFile` method of the workbook.
1832+
1833+Virtual mode also works for reading spreadsheet files.
1834+
1835+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.
1836+
1837+### Dataset export
1838+
1839+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).
1840+
1841+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.
1842+
1843+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.
1844+
1845+A simple example of how this works:
1846+```
1847+uses
1848+ ...
1849+ fpsexport
1850+ ...
1851+ var
1852+ Exp: TFPSExport;
1853+ ExpSettings: TFPSExportFormatSettings;
1854+ TheDate: TDateTime;
1855+ begin
1856+ FDataset.First; //assume we have a dataset called FDataset
1857+ Exp := TFPSExport.Create(nil);
1858+ ExpSettings := TFPSExportFormatSettings.Create(true);
1859+ try
1860+ ExpSettings.ExportFormat := efXLS; // choose file format
1861+ ExpSettings.HeaderRow := true; // include header row with field names
1862+ Exp.FormatSettings := ExpSettings; // apply settings to export object
1863+ Exp.Dataset:=FDataset; // specify source
1864+ Exp.FileName := 'c:\temp\datadump.xls';
1865+ Exp.Execute; // run the export
1866+ finally
1867+ Exp.Free;
1868+ ExpSettings.Free;
1869+ end;
1870+```
1871+
1872+#### Export to DBF
1873+
1874+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]](<http://forum.lazarus.freepascal.org/index.php/topic,41636.0.html>)
1875+```
1876+procedure TForm1.ExportToDBF(AWorksheet: TsWorksheet; AFileName: String);
1877+ var
1878+ i: Integer;
1879+ f: TField;
1880+ r, c: Cardinal;
1881+ cell: PCell;
1882+ begin
1883+ DbfGlobals.DefaultCreateCodePage := 1251; //default encoding of the dbf
1884+ DbfGlobals.DefaultOpenCodePage := 1251; //default encoding of the dbf
1885+ if Dbf1.Active then Dbf1.Close;
1886+ if FileExists(AFileName) then DeleteFile(AFileName);
1887+
1888+ Dbf1.FilePathFull := ExtractFilePath(AFileName);
1889+ Dbf1.TableName := ExtractFileName(AFileName);
1890+ Dbf1.TableLevel := 25; // DBase IV: 4 - most widely used; or 25 = FoxPro supports nfCurrency
1891+ Dbf1.LanguageID := $C9; //russian language by default
1892+ Dbf1.FieldDefs.Clear;
1893+ //below are the fields in excel
1894+ Dbf1.FieldDefs.Add('fam', ftString);
1895+ //add other fields you want to save
1896+
1897+ Dbf1.CreateTable;
1898+ Dbf1.Open;
1899+
1900+ for f in Dbf1.Fields do
1901+ f.OnGetText := @DbfGetTextHandler;
1902+
1903+ // Skip row 0 which contains the headers
1904+ for r := 1 to AWorksheet.GetLastRowIndex do begin
1905+ Dbf1.Append;
1906+ for c := 0 to Dbf1.FieldDefs.Count-1 do begin
1907+ f := Dbf1.Fields[c];
1908+ cell := AWorksheet.FindCell(r, c);
1909+ if cell = nil then
1910+ f.Value := NULL
1911+ else
1912+ case cell^.ContentType of
1913+ cctUTF8String: f.AsString := UTF8ToCP1251(cell^.UTF8StringValue);
1914+ cctNumber: f.AsFloat := cell^.NumberValue;
1915+ cctDateTime: f.AsDateTime := cell^.DateTimeValue;
1916+ else f.AsString := UTF8ToCP1251(AWorksheet.ReadAsText(cell));
1917+ end;
1918+ end;
1919+ Dbf1.Post;
1920+ end;
1921+ end;
1922+
1923+ //This procedure is called so the text in the cells could be correctly dispayed on WorkSheetGrid on the form, otherwise '?' symbols showing
1924+ procedure TForm1.DbfGetTextHandler(Sender: TField; var AText: string; DisplayText: Boolean);
1925+ begin
1926+ if DisplayText then
1927+ AText := CP1251ToUTF8(Sender.AsString);
1928+ end;
1929+```
1930+
1931+## Visual controls for FPSpreadsheet
1932+
1933+The package **laz_fpspreadsheet_visual** implements a series of controls which simiplify creation of visual GUI applications:
1934+
1935+ * **TsWorkwookSource** links the controls to a workbook and notifies the controls of changes in the workbook.
1936+ * **[[TsWorksheetGrid]]** implements a grid control with editing and formatting capabilities; it can be applied in a similar way to TStringGrid.
1937+ * **TsWorkbookTabControl** provides a tab sheet for each worksheet of the workbook. It is the ideal container for a [[TsWorksheetGrid]].
1938+ * **TsWorksheetIndicator** : a combobox which lists all worksheets of the workbook.
1939+ * **TsCellEdit** corresponds to the editing line in Excel or Open/LibreOffice. Direct editing in the grid, however, is possible as well.
1940+ * **TsCellIndicator** displays the name of the currently selected cell; it can be used for navigation purposes by entering a cell address string.
1941+ * **TsCellCombobox** offers to pick cell properties for a selection of formatting attributes: font name, font size, font color, background color.
1942+ * **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.
1943+ * Various standard actions are provided in the unit **fpsActions**. Applied to menu or toolbar, they simplify typical formatting and editing tasks without having to write a line of code.
1944+ * **TsWorkbookChartSource** interfaces a workbook with the [[TAChart]] library. It defines the cell ranges from which a Chart series can get its data. There is also an older component, [[TsWorksheetChartSource]], which requires all cells to be from the same worksheet. For this reason, it has not been fully integrated into the environment of FPSpreadsheet controls and will be removed from the library in the future.
1945+
1946+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.
1947+
1948+## Examples
1949+
1950+Please see [[FPSpreadsheet: Examples]] for a variety of code examples.
1951+
1952+## Download
1953+
1954+### Subversion
1955+
1956+You can download FPSpreadsheet using the subversion software and the following command line:
1957+```
1958+svn checkout https://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet
1959+```
1960+
1961+### Stable releases
1962+
1963+The most current release is distributed by the Online-Package-Manger which is integrated in the Lazarus IDE (menu "Package" > "Online package manager"): Scroll down the list of available packages, check the entry "FPSpreadsheet" and click "Install". Confirm the prompt to rebuild the IDE. When the process is complete you find the new components in palette "FPSpreadsheet".
1964+
1965+Older releases of FPSpreadsheet cann still be found on [sourceforge](<https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/>).
1966+
1967+## Installation
1968+
1969+ * 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.
1970+ * 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.
1971+ * 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.
1972+ * FPSpreadsheet is developed with the latest stable fpc version (currently fpc 3.0.2). We only occasionally check older versions.
1973+ * The basic spreadsheet functionality works with Lazarus versions back to version 1.0. Some visual controls or demo programs, however, require newer versions. Please update your Lazarus if you have an older version and experience problems.
1974+
1975+### Conditional defines
1976+
1977+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:
1978+
1979+ * `FPS_DONT_USE_CLOCALE`: In Unix systems, the unit `clocale` is automatically added to the uses clause of _fpspreadsheet.pas_. This unit sets up localization settings needed for locale-dependent number and date/time formats. However, this adds a dependence on the C library to the package [[2]](<http://lazarus-ccr.sourceforge.net/docs/rtl/clocale/index.html>). If this is not wanted, define `FPS_DONT_USE_CLOCALE`.
1980+ * `FPS_VARISBOOL`: fpspreadsheet requires the function VarIsBool which was introduced by fpc 2.6.4. If an older FPC versions is used define `FPS_VARISBOOL`. Keep undefined for the current FPC version.
1981+ * `FPS_LAZUTF8`: fpspreadsheet requires some functions from the unit LazUTF8 which were introduced by Lazarus 1.2. If an older Lazarus version is used define `FPS_LAZUTF8`. Keep undefined for the current Lazarus version.
1982+ * `FPS_NO_GRID_MULTISELECT`: In order to allow selection of multiple ranges in the WorksheetGrid a sufficiently new version of the basic TCustomGrid is needed. The required property "RangeSelect" was introduced in Lazarus 1.4. In order to compile the package with older versions activate the define `FPS_NO_GRID_MULTISELECT`.
1983+ * `FPS_PTRINT`: In order to provide safe casting of integers to pointers new version of FPC provide the types PtrInt and IntPtr. This is not yet available in fpc 2.6.0.
1984+ * `FPS_NEED_STRINGHASHLIST`: Unit stringhashlist belongs to LCL before Lazarus 1.8. To avoid a requirement of LCL in laz_fpspreadsheet.lpk a copy in the fps directory is provided. This copy is used when the define FPS_NEED_STRINGHASHLIST is active. The define is not needed for Lazarus versions >= 1.8.
1985+ * `FPS_NO_NEW_UTF8_ROUTINES`: In Lazarus 2.0+ some UTF8 routines in unit LazUTF8 were renamed from UTF8CharacterXXX to UTF8CodePointXXX. Activate the following define when the new routines are not available, i.e. for Lazarus version < 2.0.
1986+ * `FPS_NO_LAZUTF16`: Lazarus 1.8+ has unit LazUTF16 for special access to widestring. This define must be active when this unit is not available, i.e. for Lazarus versions before 1.8.0.
1987+ * `FPS_NO_STRING_SPLIT`: Activate the following define if FPS does not have the string Split helper, e.g. before v3.0
1988+ * `FPS_CHARTS`: Activate chart support by fpspreadsheet and their reading/writing in xlsx and ods. This feature is behind this define since many spreadsheets do not use charts.
1989+
1990+All these defines are collected in the include file _fps.inc_.
1991+
1992+In the most recent test (Feb 2023), all fpspreadsheet packages compile fine with Laz 1.6+ / fpc 3.0+ (some defines in _fps.inc_ may have to be adapted, though). With Laz 1.4 / fpc 2.6.4 or older, _fpspreadsheet_dataset.lpk_ and _fpspreadsheet_crypto.lpk_ cannot be used any more.
1993+
1994+## Support and Bug Reporting
1995+
1996+The recommended place to discuss FPSpreadsheet and obtain support is asking in the [Lazarus Forum](<http://forum.lazarus.freepascal.org/index.php?board=49.0>).
1997+
1998+Bug reports should be sent to the [Lazarus/Free Pascal Bug Tracker](<http://bugs.freepascal.org/>); please specify the "Lazarus-CCR" project.
1999+
2000+## Current Progress
2001+
2002+### Progress by supported cell content
2003+
2004+| Format | Multiple · sheets | Unicode | Reader · support | Writer · support | Text | Number | String · Formula | RPN · Formula | 3D cell · references | Date/Time | Comments | Hyperlinks | Images +++ | Protection | Conditional · formats |
2005+|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2006+| CSV files | No | Yes + | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | N/A | N/A | N/A | N/A |
2007+| Excel 2.x | No | No * | Working ** | Working | Working | Working | Working | Working *** | N/A | Working **** | Working | N/A | N/A | Working | N/A |
2008+| Excel 5.0 (Excel 5.0 and 95) | Yes | No * | Working ** | Working | Working | Working | Working | Working *** | Working | Working **** | Working | N/A | N/A | Working | N/A |
2009+| Excel 8.0 (Excel 97- 2003) | Yes | Yes | Working ** | Working | Working | Working | Working | Working *** | Working | Working **** | Reading only | Working | Not working | Working | Not working |
2010+| Excel 2003/XML | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working **** | Working | Working | N/A | Working | Working |
2011+| Excel OOXML | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working **** | Working | Working | Working | Working | Working ++++ |
2012+| OpenDocument | Yes | Yes | Working ** | Working | Working | Working | Working *** | Working | Working | Working **** | Working | Working | Working | Working | Working |
2013+| HTML | No | Yes | Working ++ | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | Working | Not working | N/A | N/A |
2014+| Wikitable files (Mediawiki) | No | Yes | planned | Working ++ | Working ++ | Working ++ | N/A | N/A | N/A | Working ++ | N/A | N/A | Not working | N/A | N/A |
2015+
2016+(+) Depends on file.
2017+(++) No "true" number format support because the file does not containg number formatting information. But the number format currently used in the spreadsheet understood.
2018+(+++) Only very basic image support: no transformations, no cropping, no image manipulation.
2019+(++++) Extended OOXML formatting options not supported.
2020+(*) 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.
2021+(**) Some cell could be returned blank due to missing or non ready implemented number and text formats.
2022+(***) This is the format in which the formulas are written to file (determined by design of the file format).
2023+(****) Writing of all formats is supported. Some rare custom formats, however, may not be recognized correctly. BIFF2 supports only built-in formats by design.
2024+
2025+### Progress of the formatting options
2026+
2027+The following formatting options are available:
2028+
2029+| Format | Text · alignment | Text · rotation | Font | Rich · text | Border | Color · support | Back · ground | Word · wrap | Col&Row · size | Number · format | Merged · cells | Page · layout | Print · ranges | Header/footer · images | Column/row · format | Hide · cols/rows | Page · breaks |
2030+|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2031+| CSV files | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
2032+| Excel 2.x | Working * | N/A | Working | N/A | Working | Working | Working** | N/A | Working | Working | N/A | Working | N/A | N/A | Working | N/A | Working |
2033+| Excel 5.0 · (Excel 5.0 and 95) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | N/A | Working | Working | N/A | Working | Working | Working |
2034+| Excel 8.0 · (Excel 97 - XP) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Not working | Working | Working | Working |
2035+| Excel 2003/XML | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | N/A | Working | Wworking | Working |
2036+| Excel OOXML (xlsx) | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working | Working |
2037+| OpenDocument | Working | Working | Working | Working | Working | Working | Working*** | Working | Working | Working | Working | Working | Working | Working | Working | Working | |
2038+| HTML (+) | Working | bugs | Working | Working | bugs | Working | Working **** | Writing only | Writing only | N/A | Working | N/A | N/A | Not working | Writing only | to be done | N/A |
2039+| Wikitable (Mediawiki) | Writing only | N/A | Writing only | Not working | Writing only | Writing only | | Writing only | Writing only | N/A | Writing only | N/A | N/A | Not working | Writing only | to be done | N/A |
2040+
2041+(N/A) Feature is not available for this format intrinsically.
2042+(*) BIFF2 supports only horizontal text alignment, vertical alignment is ignored.
2043+(**) BIFF2 does not support a background color; a dotted black&white background is used instead.
2044+(***) OpenDocument supports only uniform backgrounds; a fill color interpolated between foreground and background colors is written instead.
2045+(****) Only uniform background color, no fill styles.
2046+(+) HTML reader does not support styles. Since the writer does use styles these files are not read back correctly.
2047+
2048+### Progress of workbook/worksheet user-interface options
2049+
2050+Some additional options were added to interface the file contents with the `TsWorksheetGrid`:
2051+
2052+| Format | Hide grid lines | Hide headers | Frozen Panes | Active sheet/cell | Zooming | BiDi mode | Tab color |
2053+|---|---|---|---|---|---|---|---|
2054+| Excel 2.x | Working | Working | not working | N/A | N/A | N/A | N/A |
2055+| Excel 5.0 (Excel 5.0 and 95) | Working | Working | Working | Working | Working | N/A | N/A |
2056+| Excel 8.0 (Excel 97 - XP) | Working | Working | Working | Working | Working | Working | Working |
2057+| Excel2003/XML | Working | Working | Working | Working | Working | Working | N/A |
2058+| Excel OOXML | Working | Working | Working | Working | Working | Working | Working |
2059+| OpenDocument | Working | Working | Working | Working | Working | Working | Working |
2060+| HTML | Writing only | Writing only | N/A | N/A | N/A | not working | N/A |
2061+
2062+### To do list
2063+
2064+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!
2065+
2066+ * Find out why BIFF2 files are corrupt when saved with frozen rows/cols activated.
2067+ * ~~Add row and column formats~~
2068+ * Add reading support for wikitable (Mediawiki) files
2069+ * 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>.
2070+ * ~~Improve color support: due to palette support colors may change from file to file currently.~~
2071+ * Add embedded images.
2072+ * Fix writing of cell comments to BIFF8 files.
2073+
2074+**Long-term:**
2075+
2076+ * 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.
2077+ * ~~Store formatting in a format list of the workbook, not in the cell, to reduce memory usage.~~
2078+ * Use BIFF8 file-wide string storage instead of storing strings in cells (should reduce memory usage in case of many duplicate strings)
2079+ * Redo ooxml and ods readers based upon SAX/TXmlReader instead of DOM to reduce memory usage of large files.
2080+ * ~~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).
2081+
2082+## Changelog
2083+
2084+### SVN
2085+
2086+**Changes with respect to current release**
2087+
2088+ * Supporting new formulas: INDEX, ROUNDDOWN, ROUNDUP, COUNTIF, SUMF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS
2089+ * Supporting horizontal alignments haDistributed and haFilled
2090+ * Supporting charts
2091+ * Supporting defined names (named cells and ranges).
2092+
2093+**Incompatible changes**
2094+
2095+ * (currently none)
2096+
2097+### Version 1.16
2098+
2099+This is the latest stable release.
2100+
2101+**Changes with respect to v1.14**
2102+
2103+ * XLSX reader can handle embedded as well as header/footer images now.
2104+ * Less strict format check by Excel2 reader since there are BIFF2 files with BOF record of Excel8 which Excel can read.
2105+ * Reading support for the Excel3 and Excel4 formats (unit _xlsbiff34_)
2106+ * ODS reader supports the <text:s> node
2107+ * ODS reader is able to open encrypted files.
2108+
2109+**Incompatible changes**
2110+
2111+ * (none)
2112+
2113+### Version 1.14
2114+
2115+**Changes with respect to v1.12**
2116+
2117+ * Implementation of **conditional formats** for ods, xlsx and Excel xml.
2118+ * Implementation of **meta data** for ods, xlsx and Excel xml.
2119+ * Add a worksheet-based dataset in separate package (**TsWorksheetDataset**).
2120+
2121+**Incompatible changes**
2122+
2123+ * Removed the deprecated worksheet properties DefaultRowHeight and DefaultColWidth. Use the corresponding Read/Write routines instead (ReadDefaultColWidth(units), WriteDefaultColWidth(units)).
2124+
2125+### Version 1.12
2126+
2127+**Changes with respect to v1.10x**
2128+
2129+ * **Hide** (and unhide) rows and columns.
2130+ * Enforce **Page breaks** before rows and columns during printing by the Office applications.
2131+ * Full support (reading and writing) of the **ExcelXML** xml format (Excel 2003 and XP).
2132+ * Support of the color of worksheet tabs (Worksheet.TabColor) in xls biff8, xlsx and ods formats. The property is ignored by the visual WorkbookTabControl, though.
2133+ * Add TsWorksheetIndicator to visual controls.
2134+
2135+### Version 1.10.1
2136+
2137+**Changes with respect to v1.8x**
2138+
2139+ * **Workbook, worksheet and cell protection** (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).
2140+ * New package **laz_fpspreadsheet_crypto** to decipher the encryption for worksheet-protection in xls files. Requires DCPcrypt.
2141+ * TsWorksheetGrid can **display embedded images**.
2142+ * **Drag and drop** in TsWorksheetGrid
2143+ * New, **highDPI-aware** component palette icons.
2144+ * New workbook optiond **boAbortReadingOnFormulaError** and **boIgnoreFormulas**.
2145+ * Formulas with **references to other sheets** , i.e. `'=Sheet1!A1+Sheet2!A2'`
2146+
2147+**Incompatible changes**
2148+
2149+ * The field **FormulaValue** has been removed from the cell record. The cell formula now can be retrieved by calling `Worksheet.ReadFormula(cell)`.
2150+
2151+### Version 1.8
2152+
2153+**Changes with respect to v1.6x**
2154+
2155+ * **"Rich-text" formatting** of label cells, i.e. assignment different fonts to groups of characters within the cell text. For this purpose, HTML codes (such as `<B>...</B>`) can be embedded in the cell text to identify the parts with different font (--> `TsWorksheet.WriteTextAsHTML`).
2156+ * **Searching** for cells with specified content in worksheet or workbook.
2157+ * Support for reading and writing of **HTML format**
2158+ * Support for writing of the **ExcelXML format** (Excel XP and 2003)
2159+ * Ability to use **user-provided file reader/writer classes** to extend FPSpreadsheet to new file formats.
2160+ * Readers and writers now support all the **line styles** of Excel8 and OOXML.
2161+ * xls, xlsx and ods readers/writers now support the **active worksheet** and **selected cell**.
2162+ * Ability to write to/read from the system's **clipboard** for copy & paste using the visual controls.
2163+ * Support for **print ranges** and **repeated header rows and columns** in the Office applications.
2164+ * Support for **embedded images** (currently only writing to xlsx and ods, no reading).
2165+ * Improved compatibility of **TsWorksheetGrid** with TStringGrid (`Cells[Col,Row]` property). Standalone application as an advanced StringGrid replacement.
2166+ * Support for **Right-to-left mode** in TsWorksheetGrid. In addition to the system-wide RTL mode, there are also parameters `BiDiMode` in the Worksheet and cells allowing to controls text direction at worksheet and cell level individually, like in Excel or LibreOffice Calc.
2167+ * Support of several **units** for specification of column widths and row heights.
2168+ * The library now supports **localization** using po files. Translations are welcome.
2169+ * **Zoom factor** read and written by the worksheet, and applied by the TsWorksheetGrid.
2170+ * Support of **column and row formats**
2171+ * Support of **hidden** worksheets
2172+
2173+**Incompatible changes**
2174+
2175+ * VirtualMode was changed in order to be able to treat worksheets of the same workbook differently. `VirtualRowCount` and `VirtualColCount` are now properties of the worksheet, and similarly, the event handler `OnWriteCellData`. In older versions, these properties had belonged to the workbook.
2176+ * The worksheet methods `ReadAsUTF8Text` and `WriteUTF8Text` have been renamed to `ReadAsText` and `WriteText`, respectively. The old ones are still available and marked as deprecated; they will be removed in later versions.
2177+ * The public properties of `TsWorksheetGrid` using a `TRect` as parameter were modified to use the Left, Top, Right, Bottom values separately.
2178+ * The `PageLayout` is a class now, no longer a record. As a consequence, some array properties cannot be set directly any more, use the corresponding methods instead.
2179+ * Most of the predefined color constants were marked as deprecated; only the basic EGA colors will remain.
2180+ * Unit fpsNumFormatParser is integrated in fpsNumFormat. Old code which "uses" fpsNumFormatParser must "use" fpsNumFormat now.
2181+ * The source files of the laz_fpspreadsheet, laz_fpspreadsheet_visual and laz_fpspreadsheetexport_visual packages have been moved to separate folders in order to resolve some occasional compilation issues. Projects which do not use the packages but the path to the sources must adapt the paths.
2182+
2183+### Version 1.6
2184+
2185+**Changes with respect to v1.4.x**
2186+
2187+ * TsWorkbookChartSource is a new component which facilitates creation of charts from non-contiguous spreadsheet data in various worksheets. It interfaces to a workbook via tha WorkbookSource component. In the long run, it will replace the older TsWorksheetChartSource which required contiguous x/y data blocks in the same worksheet.
2188+ * Major reconstruction of the cell record resulting in strong reduction of memory consumption per cell (from about 160 bytes per cell down to about 50)
2189+ * Implementation of a record helper for the TCell which simplifies cell formatting (no need to set a bit in UsedFormattingFields any more, automatic notification of visual controls)
2190+ * Comments in cells
2191+ * Background fill patterns
2192+ * Hyperlinks
2193+ * Enumerators for worksheet's internal AVLTrees for faster iteration using a for-in loop.
2194+ * Formatting of numbers as fractions.
2195+ * Improved number format parser for better recognition of Excel-like number formats.
2196+ * Page layout (page margins, headers, footer, used for only when printing in the Office applications - no direct print support in fpspreadsheet!)
2197+ * Improved color management: no more palettes, but direct rgb colors. More pre-defined colors.
2198+ * A snapshot of the wiki documentation is added to the library as chm help file.
2199+
2200+**Incompatible changes**
2201+
2202+ * All type declarations and constants are moved from fpspreadsheet.pas to the new unit **fpstypes.pas**. Therefore, most probably, this unit has to be added to the uses clause.
2203+ * Because fpspreadsheet supports now **background** fill patterns the cell property `BackgroundColor` has been replaced by `Background`. Similarly, the `UsedFormattingFields` flag `uffBackgroundColor` is called `uffBackground` now.
2204+ * Another `UsedFormattingFields` flag has been dropped: **`uffBold`**. It is from the early days of fpspreadsheet and has become obsolete since the introduction of full font support. For achieving a bold type-face, now call `MyWorksheet.WriteFont(row, col, BOLD_FONTINDEX)`, or `Myworksheet.WriteFontStyle(row, col, [fssBold])`.
2205+ * **Iteration through cells** using the worksheet methods `GetFirstCell` and `GetNextCell` has been removed - it failed if another iteration of this kind was called within the loop. Use the new `for-in` syntax instead.
2206+ * Support for **shared formulas** has been reduced. The field `SharedFormulaBase` has been deleted from the TCell record, and methods related to shared formulas have been removed from TsWorksheet. Files containing shared formulas can still be read, the shared formulas are converted to multiple normal formulas.
2207+ * The **color palettes** of previous versions have been abandoned. `TsColor` is now a `DWord` representing the rgb components of a color (just like TColor does in the _graphics_ unit), it is not an index into a color palette any more. The values of pre-defined colors, therefore, have changed, their names, however, are still existing. The workbook functions for palette access have become obsolete and were removed.
2208+
2209+### Version 1.4
2210+
2211+**Changes with respect to v1.2.x**
2212+
2213+ * 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`).
2214+ * **Shared formulas** (reading for sfExcel5, sfExcel8, sfOOXML; writing for sfExcel2, sfExcel5, sfExcel8).
2215+ * Significant **speed-up** of writing of large spreadsheets for the xml-based formats (ods and xlsx), speed up for biff2; **speedtest demo** program
2216+ * **VirtualMode** allowing to read and write very large spreadsheet files without loading entire document representation into memory. Formatting of cells in VirtualMode.
2217+ * Demo program for database export using virtual mode and TFPSExport.
2218+ * Added db export unit allowing programmatic exporting datasets using **TFPSExport**. Similar export units are e.g. [[fpdbfexport]], [[fpXMLXSDExport]].
2219+ * Reader for **xlsx** files, now fully supporting the same features as the other readers.
2220+ * Reader/writer for **CSV files** based on [[CsvDocument]].
2221+ * **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
2222+ * **Insertion and deletion of rows and columns** into a worksheet containing data.
2223+ * Implementation of **sorting** of a worksheet.
2224+ * Support of **diagonal "border" lines**
2225+ * **Logging of non-fatal error messages** during reading/writing (TsWorksheet.ErrorMsg)
2226+ * **Merged** cells
2227+ * **Registration** of currency strings for automatic conversion of strings to **currency values**
2228+ * 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 [[FPSpreadsheet tutorial: Writing a mini spreadsheet application|creation of GUI applications]].
2229+ * **Overflow cells** in TsWorksheetGrid: label cells with text longer than the cell width extend into the neighboring cell(s).
2230+
2231+**Incompatible changes**
2232+
2233+ * 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).
2234+ * 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.
2235+ * With full support of string formulas some features related to RPN formulas were removed:
2236+ * The field `RPNFormulaResult` of TCell was dropped, as well as the element `cctRPNFormula` in the `TsContentType` set.
2237+ * Sheet function identifiers were removed from the `TsFormulaElement` set, which was truncated after `fekParen`.
2238+ * 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`.
2239+ * 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).
2240+ * Code related to RPN formulas was moved to a separate unit, `fpsRPN`. Add this unit to the uses clause if you need RPN features.
2241+
2242+## License
2243+
2244+LGPL with static linking exception. This is the same license as is used in the Lazarus Component Library.
2245+
2246+## References
2247+
2248+### Documentation
2249+
2250+FPSpreadsheet is documented in this wiki file.
2251+
2252+Every release of FPSpreadsheet is accompanied by a snapshot of the current wiki files. There is also an api documentation created from the embedded comments in the source files. Both files are in the chm format.
2253+
2254+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:
2255+
2256+ * Version 1.16: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=157438](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=157438>)
2257+ * Version 1.14: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=149828](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=149828>)
2258+ * Version 1.12: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=136886](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771>)
2259+ * Version 1.10.1: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118771>)
2260+ * Version 1.10: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118441](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=118441>)
2261+ * Version 1.8: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=107616](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=107616>)
2262+ * Version 1.6.2: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=100723](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=100723>)
2263+ * Version 1.6: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=91469](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=91469>)
2264+ * Version 1.4: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299>)
2265+ * Version 1.2 and 1.2.1: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375](<https://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375>)
2266+
2267+### Wiki links
2268+
2269+ * [[Office Automation]]
2270+ * [[CsvDocument]]
2271+ * [[FPSpreadsheet: Examples]]
2272+ * [[RPN Formulas in FPSpreadsheet|RPN formulas in FPSpreadsheet]]
2273+ * [[FPSpreadsheet: List of formulas]]
2274+ * [[FPSpreadsheet tutorial: Writing a mini spreadsheet application]]
2275+
2276+### External Links
2277+
2278+ * [Microsoft OLE Document Format](<http://sc.openoffice.org/compdocfileformat.pdf>)
2279+ * [Excel file format description](<http://sc.openoffice.org/excelfileformat.pdf>)
2280+ * 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 _applications_ folder of ccr.
2281+ * Icons used in the demo programs:
2282+ * [silk icons](<http://www.famfamfam.com/lab/icons/silk/>)
2283+ * [fugue icons](<http://p.yusukekamiyamane.com/>)
2284+
2285+---
2286+*From the Free Pascal wiki, page "FPSpreadsheet", as the Internet Archive kept it on 2025-01-24, last changed on 2024-08-28. 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](/wiki-contact).*

Back to history