FPSpreadsheet: what changed

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

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

Back to history