FPSpreadsheet/es: what changed

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

Old	New		Line
1	1		El contenido que sigue a continuación es una traducción de la versión en inglés como fuente primaria, por lo que ante la duda aconsejo visitar esa página. Procuraré manter el contenido actualizado/sincronizado. Ultima fecha edición 21/septiembre/2016.
2	2
3	3		La librería fpSpreadsheet ofrece un modo práctico de generar y leer documentos spreadsheet (hosas de cálculo) en varios formatos. Está escrita en una manera muy flexible, capaz de ser extendida para soportar cualquier número de formatos facilmente.
4	4
5	5		Aquí se muestra un pantallazo del programa de demo "spready" que viene con fpspreadsheet, que muestra un fichero XLS.
6	6
7	7		![](/wiki-images/b/b7/Fpsgrid.png)
8	8
9	9		## Documentación
10	10
11	11		Esta página wiki cubre las últimas versión de desarrollo/trunk de FPSpreadsheet disponibles vía subversin. Por favor mira la sección Stable releases para la documentación de la versión estable que puedes descargar.
12	12
13	13		## Documentación API
14	14
15	15		### Referencia de la API
16	16
17	17		Hay disponible un fichero de ayuda en formato CHM dentro de la carpeta docs al descomprimir el paquete descargado (e.g. \fpspreadsheet-1.6.2\docs). Si todavía no has instalado el paquete sigue: [<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) para fpspreadsheet-api.chm.
18	18
19	19		Hay también disponible un segundo fichero CHM de ayuda en la misma carpeta docs, fpspreadsheet-wiki.chm, que es una instantanea de las páginas wiki relativas a FPSpreadsheet.
20	20
21	21		### Conceptos básicos
22	22
23	23		Las entidades más pequeñas en un spreadsheet son las cells (celdas), las cuales contienen datos. Las celdas (Cells) pueden contener varios tipos de datos, tales como cadenas, números, fechas, horas, valores booleanos o fórmulas. Adicionalmente, las celdas pueden contener información de formato, i.e. estilo de fuente, color de fondo, alineación de texto, etc.
24	24
25	25		Las celdas están dispuestas en una estructura a modo de cuadrícula o rejilla llamada worksheet, or spreadsheet consistente en rows (filas) y columns (columnas). Cada celda tiene una dirección única dada por el índice de fila y columna.
26	26
27	27		Las Worksheets (hojas del libro de trabajo) se ponen juntas para formar un workbook (libro de trabajo), el cual representa el documento de la aplicación spreadsheet (hoja de cálculo). El workbook almacena además la información de todos los worksheets, i.e. listado de fuentes, listados de formato de número, etc.
28	28
29	29		FPSpreadsheet sigue la misma estructura - hay un `TCell`, un `TsWorksheet`, y un `TsWorkbook`.
30	30
31	31		#### Workbook
32	32
33	33		La clase `TsWorkbook` es la clase principal visible para el usuario. Provee métodos de lectura y escritura a ficheros. La estructura versátil de la librería provee acceso a una variedad de formatos de fichero populares tales como Excel .xls o .xlsx, así como de OpenOffice/LibreOffice .ods.
34	34
35	35		El formato de fichero se especifica mediante el tipo `TsSpreadsheetFormat` definido en la unit `fpstypes`
36	36
37	37		``` pascal
38	38		type
39	39		TsSpreadsheetFormat = (sfExcel2, sfExcel5, sfExcel8, sfExcelXML, sfOOXML,
40	40		sfOpenDocument, sfCSV, sfHTML, sfWikiTable_Pipes, sfWikiTable_WikiMedia, sfUser);
41	41		```
42	42
43	43		Donde:
44	44
45	45		- `sfExcel2, sfExcel5, sfExcel8` soporta versiones del formato binario xls utilizado por Excel ("BIFF" = "Binary Interchange File Format / Formato de Fichero de Intercambio Binario") siendo `sfExcel8` el más moderno.
46	46		- `sfOOXLM` Corresponde al formato más actual xlsx introducido para Excel2007.
47	47		- `sfExcelXML` es el formato xml que fué introducido por Microsoft con Office XP/2003. No muy popular.
48	48		- `sfOpenDocument` es el formato spreadsheet utilizado por OpenOffice/LibreOffice.
49	49		- `sfCSV` referido a ficheros de texto conteniendo valores delimitados por comas; es entendido por cualquier editor de texto y por todos los programas de hojas de cálculo (spreadsheet), pero no contienen información de formateado.
50	50		- `sfHTML` denota el formato estandar HTML tal como se utiliza en visores web.
51	51		- `sfWikiTable_Pipes` y `sfWikiTable_WikiMedia` es el formato utilizado por las tablas de los sitios web en formato wiki.
52	52		- `sfUser` se necesita para registrar un formato definido por el usuario. No hay planes de implementar formatos de fichero antiguos tales como Excel3.0/4.0 o Lotus. Es posible sin embargo aportar tus propias clases de lectura y escritura para extender la funcionalidad de FPSpreadsheet - Ver la sección más abajo sobre Añadiendo nuevos formatos de fichero
53	53
54	54		Cuando se aplica un fpspreadsheet la primera tarea es crear una instancia de workbook:
55	55
56	56		``` pascal
57	57		var
58	58		MyWorkbook: TsWorkbook;
59	59		begin
60	60		MyWorkbook := TsWorkbook.Create;
61	61		...
62	62		```
63	63
64	64		La lectura de ficheros spreadsheets se realiza (entre otros) por los siguientes métodos de workbook:
65	65
66	66		- `procedure ReadFromFile(AFileName: string):`\
67	67		Lee el fichero especificado y determina automáticamente su formato correcto.
68	68		- `procedure ReadFromFile(AFileName: string; AFormat: TsSpreadsheetFormat):`\
69	69		Lee el fichero, pero asume que su formato viene especificado en `AFormat`.
70	70
71	71		Se pueden utilizar los siguientes métodos de workbook para salvar a fichero:
72	72
73	73		- `procedure WriteToFile(const AFileName: string; const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False)`:\
74	74		Escribe un workbook al fichero dado utilizando el formato dado a spreadsheet. En caso de que el fichero ya exista entonces se sobreescribe automáticamente si `AOverwriteExisting` vale `true`:
75	75		- `procedure WriteToFile(const AFileName: String; const AOverwriteExisting: Boolean = False)`:\
76	76		mismo, pero el formato de fichero se determina a partir de la extensión dada (en el caso de Excel con extensión xls y para la versión más reciente se utiliza `sfExcel8`).
77	77
78	78		Después de llamar a estos métodos es conveniente revisar la propiedad `ErrorMsg` de workbook para verificar si existen mensajes de error o advertencias generadas durante el proceso de lectura o escritura. Esta propiedad retorna cadenas multi-línea por lo que se mostrarán mejor en un componente tipo memo; en caso de que el proceso de lectura o escritura se realice correctamente entonces nos retornará una cadena vacía.
79	79
80	80		Note: FPSpreadsheets provee units especializadas para la lectura / escritura de cada formato de fichero. Estas unidades no se aplican automáticamente, deben ser añadidas explícitamente a la claúsula uses. FPSpreadsheet will complain about "unsupported file format" si no encuentra la lectura / escritura solicitada. Aquí se muestra un listado de los nombres de las units.
81	81
82	82		- xlsbiff2, xlsbiff5 y xlsbiff8 para el formato de fichero binario correspondiente a xls `sfExcel2`, `sfExcel5` y `sfExcel8`, respectivamente,
83	83		- xlsOOXML para el formato de fichero xlsx `sfOOXML` o Excel 2007 y posteriores,
84	84		- xlsXMLpara el formato de fichero xml de Excel XP/2003,
85	85		- fpsopendocument para el formato de fichero `sfOpenDocument` de OpenOffice/LibreOffice,
86	86		- fpsCSV para ficheros de texto con valores separados por comas (csv),
87	87		- fpsHTML para fichero HTML,
88	88		- wikitables para `sfWikiTable_Pipes` y `sfWikiTable_WikiMedia`,
89	89		- o, simplemente añade fpsallformats para tener soporte de lectura / escritura para todos los tipos de formato soportados.
90	90
91	91		#### Worksheet
92	92
93	93		El workbook contiene un listado de instancias `TsWorksheet`. Estas corresponde con las solapas (tabs) que puedes ver en Excel o en OpenOffice/LibreOffice. Cuando se realiza la lectura de un fichero spreadsheet (hoja de cálculo) entonces se crean automáticamente las hojas del libro (worksheets) de acuerdo al contenido del fichero. Cuando se crea una hoja de cálculo manualmente para guardarlo en un fichero, entonces hay que crear un worksheet añadiendolo al libro:
94	94
95	95		``` pascal
96	96		var
97	97		MyWorkbook: TsWorkbook;
98	98		MyWorksheet: TsWorksheet;
99	99		begin
100	100		MyWorkbook := TsWorkbook.Create;
101	101		MyWorksheet := MyWorkbook.AddWorksheet('My_Table');
102	102		// 'My_Table' is the "name" of the worksheet
103	103		...
104	104		```
105	105
106	106		Already existing worksheets can be accessed by using the `TsWorkbook` methods
107	107
108	108		- `function GetFirstWorksheet: TsWorksheet`: retrieves the first worksheet of the workbook.
109	109		- `function GetWorksheetByIndex(AIndex: Cardinal): TsWorksheet`: returns the worksheet with the given index (starting at 0).
110	110		- `function GetWorksheetByName(AName: String): TsWorksheet`: returns the worksheet with the given name which was used when the worksheet was added.
111	111
112	112		La cuenta de los worksheets que contiene se puede consultar llamando a `GetWorksheetCount`.
113	113
114	114		#### Celda (cell)
115	115
116	116		El worksheet, finalmente, permite acceder a las celdas. Un worksheet creado de cero, como en el ejemplo mostrado, se encuentra vacío y no contiene celdas. Las celdas se añaden mediante la asignación de datos o atributos utilizando métodos `WriteXXXX` del worksheet. Como ya se ha mencionado, la celda "A1" pertenece a la fila 0, columna 0 (row 0, col 0). Debe tenerse en cuenta que el índice de fila y columna siempre se especifican en este orden, lo cual es "diferente de la convención de" `TStringGrid`. El siguiente ejemplo crea una celda en la dirección A1 y le asigna el número `1.0`.
117	117
118	118		``` pascal
119	119		var
120	120		MyWorkbook: TsWorkbook;
121	121		MyWorksheet: TsWorksheet;
122	122		begin
123	123		MyWorkbook := TsWorkbook.Create;
124	124		MyWorksheet := MyWorkbook.AddWorksheet('Mi_Tabla');
125	125		MyWorksheet.WriteNumber(0, 0, 1.0); // "A1" tiene row=0 y col=0
126	126		...
127	127		```
128	128
129	129		Además es posible acceder a las celdas directamente por medio de los métodos `FindCell(ARow, ACol)` o `GetCell(ARow, ACol)` del workbook. Ambas funciones existen además en la versión overloaded en la cual la dirección de la celda se puede pasar en notación Excel: `FindCell(ACellStr: String)` o `GetCell(ACellStr: String)`. Por favor, estar al tanto de que estas funciones retornan un `puntero` al dato de la celda (tipo `PCell`). ¡ No olvidarse de dereferenciar los punteros !. La diferencia entre `FindCell` y `GetCell` es que la modalidad uno retorna `nil`, si la celda no existe todavía, mientras que la última crea en este caso una celda vacía.
130	130
131	131		``` pascal
132	132		if MyWorksheet.FindCell('A1') = nil then
133	133		WriteLn('Cell A1 does not exist.');
134	134		```
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		FormulaValue: String;
155	155		case ContentType: TCellContentType of // must be at the end of the declaration
156	156		cctEmpty : (); // has no data at all
157	157		cctFormula : (); // UTF8StringValue is outside the variant record
158	158		cctNumber : (Numbervalue: Double);
159	159		cctUTF8String : (); // FormulaValue is outside the variant record
160	160		cctDateTime : (DateTimevalue: TDateTime);
161	161		cctBool : (BoolValue: boolean);
162	162		cctError : (ErrorValue: TsErrorValue);
163	163		end;
164	164		PCell = ^TCell;
165	165		```
166	166
167	167		The field `ContentType` indicates which data type is stored in the cell:
168	168
169	169		``` pascal
170	170		type
171	171		TCellContentType = (cctEmpty, cctFormula, cctNumber, cctUTF8String, cctDateTime, cctBool, cctError);
172	172		```
173	173
174	174		According to this field the corresponding data can be found in the fields
175	175
176	176		- `FormulaValue` (for `ContentType=cctFormula`),
177	177		- `NumberValue` (for `ContentType=cctNumber`), or
178	178		- `UTF8StringValue` (for `ContentType=cctUTF8String`), or
179	179		- `DateTimeValue` (for `ContentType=cctDateTime`), or
180	180		- `BoolValue` (for `ContentType=cctBool`), i.e. `TRUE` or `FALSE`, or
181	181		- `ErrorValue` (for `ContentType=cctError`).
182	182
183	183		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).
184	184
185	185		> Note: After calculation of a formula or after reading of a file, the `ContentType` of the formula cell is converted to that of the result. Then the presence of a formula can only be detetected from the `FormulaValue` string (or call `HasFormula(cell)` of unit fpsUtils).
186	186
187	187		Adding values to a cell is most easily accompished by using one of the `WriteXXXX` methods of the worksheet. The most important ones are:
188	188
189	189		``` pascal
190	190		type
191	191		TsWorksheet = class
192	192		...
193	193		{ Writing of currency values }
194	194		function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
195	195		ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
196	196		ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
197	197		ANegCurrFormat: Integer = -1): PCell; overload;
198	198		procedure WriteCurrency(ACell: PCell; AValue: Double;
199	199		ANumFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
200	200		ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
201	201		ANegCurrFormat: Integer = -1); overload;
202	202		function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
203	203		ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
204	204		procedure WriteCurrency(ACell: PCell; AValue: Double;
205	205		ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
206	206
207	207		{ Writing of date/time values }
208	208		function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime): PCell; overload;
209	209		procedure WriteDateTime(ACell: PCell; AValue: TDateTime); overload;
210	210		function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
211	211		ANumFormat: TsNumberFormat; ANumFormatStr: String = ''): PCell; overload;
212	212		procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
213	213		ANumFormat: TsNumberFormat; ANumFormatStr: String = ''); overload;
214	214		function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
215	215		ANumFormatStr: String): PCell; overload;
216	216		procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
217	217		ANumFormatStr: String); overload;
218	218
219	219		{ Writing of number values }
220	220		function WriteNumber(ARow, ACol: Cardinal; ANumber: double): PCell; overload;
221	221		procedure WriteNumber(ACell: PCell; ANumber: Double); overload;
222	222		function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
223	223		ANumFormat: TsNumberFormat; ADecimals: Byte = 2): PCell; overload;
224	224		procedure WriteNumber(ACell: PCell; ANumber: Double;
225	225		ANumFormat: TsNumberFormat; ADecimals: Byte = 2); overload;
226	226		function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
227	227		ANumFormat: TsNumberFormat; ANumFormatString: String): PCell; overload;
228	228		procedure WriteNumber(ACell: PCell; ANumber: Double;
229	229		ANumFormat: TsNumberFormat; ANumFormatString: String); overload;
230	230
231	231		{ Writing of string values }
232	232		function WriteText(ARow, ACol: Cardinal; AText: ansistring;
233	233		ARichTextParams: TsRichTextParams = nil): PCell; overload;
234	234		procedure WriteText(ACell: PCell; AText: String;
235	235		ARichTextparams: TsRichTextParams = nil); overload;
236	236
237	237		// the old string methods "WriteUTF8Text" are deprecated now
238	238		...
239	239		```
240	240
241	241		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:
242	242
243	243		``` pascal
244	244		type
245	245		TsWorksheet = class
246	246		...
247	247		{ Reading cell content as a string }
248	248		function ReadAsText(ARow, ACol: Cardinal): string; overload;
249	249		function ReadAsText(ACell: PCell): string; overload;
250	250		function ReadAsText(ACell: PCell; AFormatSettings: TFormatSettings): string; overload;
251	251
252	252		{ Reading cell content as a number }
253	253		function ReadAsNumber(ARow, ACol: Cardinal): Double; overload;
254	254		function ReadAsNumber(ACell: PCell): Double; overload;
255	255		function ReadNumericValue(ACell: PCell; out AValue: Double): Boolean;
256	256
257	257		{ Reading cell content as a date/time value }
258	258		function ReadAsDateTime(ARow, ACol: Cardinal; out AResult: TDateTime): Boolean; overload;
259	259		function ReadAsDateTime(ACell: PCell; out AResult: TDateTime): Boolean; overload;
260	260		...
261	261		```
262	262
263	263		`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:
264	264
265	265		``` pascal
266	266		type
267	267		TsCellFormat = record
268	268		FontIndex: Integer;
269	269		TextRotation: TsTextRotation;
270	270		HorAlignment: TsHorAlignment;
271	271		VertAlignment: TsVertAlignment;
272	272		Border: TsCellBorders;
273	273		BorderStyles: TsCellBorderStyles;
274	274		Background: TsFillPattern;
275	275		NumberFormat: TsNumberFormat;
276	276		NumberFormatStr: String;
277	277		UsedFormattingFields: TsUsedFormattingFields;
278	278		//uffTextRotation, uffFont, uffBold, uffBorder, uffBackground, uffNumberFormat, uffWordWrap, uffHorAlign, uffVertAlign
279	279		end;
280	280		```
281	281
282	282		- `FontIndex`: text font by specifying the index in the workbook's font list
283	283		- `TextRotation`: specifies whether the cell text is written horizontally or vertically
284	284		- `HorAlignment`: left-aligned, horizontally centered, or right-aligned text
285	285		- `VertAlignment`: top, bottom or vertically centered text
286	286		- `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.
287	287		- `Background`: a record defining the background fill of a cell (pattern style, pattern color, and background color - see chapter on cell background below).
288	288		- `NumberFormat` and `NumberFormatStr` specify how number or date/time values are formatted (e.g., number of decimal places, long or short date format, etc.).
289	289		- 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.
290	290
291	291		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.
292	292
293	293		See cell formatting below for a more detailed description.
294	294
295	295		#### Columns and rows
296	296
297	297		Column and row records are added for each column and row having a non-default size:
298	298
299	299		``` pascal
300	300		type
301	301		TCol = record
302	302		Col: Cardinal;
303	303		Width: Single;
304	304		end;
305	305		PCol = ^TCol;
306	306
307	307		TRow = record
308	308		Row: Cardinal;
309	309		Height: Single;
310	310		end;
311	311		PRow = ^TRow;
312	312		```
313	313
314	314		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`).
315	315
316	316		The Office applications usually adjust the row heights automatically according to the font or text rotation of the cell content, and empty rows have a predefined default row height. In order to overrun this and to set a predefined row height use the worksheet method `WriteRowHeight`. In the same way the width of columns can be set to a specific value by calling `WriteColWidth`. The height/width of a particular row/column can be retrieved by means of the methods `GetRowHeight` or `GetColHeight`. Note that these methods return the default row heights/column widths if there are no `TRow`/`TCol` records.
317	317
318	318		``` pascal
319	319		type TsWorksheet = class
320	320		...
321	321		{ Set row height }
322	322		procedure WriteRowHeight(ARowIndex: Cardinal; AHeight: Single; AUnits: TsSizeUnits);
323	323		{ Set column width }
324	324		procedure WriteColWidth(AColIndex: Cardinal; AWidth: Single; AUnits: TsSizeUnits);
325	325		{ Set default row height }
326	326		procedure WriteDefaultRowHeight(AHeight: Single; AUnits: TsSizeUnits);
327	327		{Set default cokumn width }
328	328		procedure WriteDefaultColWidth(AWidth: Single; AUnits: TsSizeUnits);
329	329
330	330		{ Return row height }
331	331		function GetRowHeight(ARowIndex: Cardinal; AUnits: TsSizeUnits): Single;
332	332		{ Return column width }
333	333		function GetColWidth(AColIndex: Cardinal; AUnits: TsSizeUnits): Single;
334	334		{ Return default row height }
335	335		function ReadDefaultRowHeight(AUnits: TsSizeUnits): Single;
336	336		{ Return default column width }
337	337		function ReadDefaultColWidth(AUnits: TsSizeUnits): Single;
338	338
339	339		property DefaultRowHeight: Single;
340	340		property DefaultColWidht: Single;
341	341		```
342	342
343	343		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 widthts are defined in terms of character count. Note that these variants are from previos versions and are deprecated now.
344	344
345	345		The default row height and default column width is used unless a specific value is assigned to individual rows or columns. The property `DefaultRowHeight` expects the value to be in line count, and `DefaultColWidth` expects it to be in "0" character count. Other size units can be applied if using the corresponding WriteXXXX and ReadXXXX methods.
346	346
347	347		### Formulas
348	348
349	349		Two kinds of formulas are supported by FPSpreadsheet:
350	350
351	351		- 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.
352	352		- 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`.
353	353
354	354		The current trunk version of FPSpreadsheet can convert between string and rpn formulas. Formulas in both types can be calculated. The cell record stores the string formula in the field `FormulaValue`. If a spreadsheet containing formulas is to be saved in a binary Excel format, the RPN formulas required are generated automatically.
355	355
356	356		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.
357	357
358	358		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`.
359	359
360	360		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`.
361	361
362	362		The most general setting regarding formulas, therefore, is
363	363
364	364		``` pascal
365	365		MyWorkbook.Options := MyWorkbook.Options + [boReadFormulas, boCalcBeforeSaving, boAutoCalc];
366	366		```
367	367
368	368		#### String formulas
369	369
370	370		String formulas are written in the same way as in the Office applications. The worksheet method for creating a string formula is `WriteFormula`:
371	371
372	372		``` pascal
373	373		var
374	374		MyWorksheet: TsWorksheet;
375	375		//...
376	376		MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1.215,0)');
377	377		// By default, use dot as decimal and comma as list separator!
378	378		```
379	379
380	380		A few notes on syntax:
381	381
382	382		- 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.
383	383		- The case of the formula name is ignored.
384	384		- Spaces can be added for better readability, but they will be lost when saving.
385	385		- Strings must be enclosed in double quotes.
386	386		- Normally, floating point numbers must be entered with a dot as decimal separator, and a comma must be used to separate function arguments.
387	387		- 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.
388	388
389	389		``` pascal
390	390		var
391	391		MyWorksheet: TsWorksheet;
392	392		//...
393	393		MyWorksheet.WriteFormula(0, 1, '=ROUND(A1+B2+1,215;0)', true);
394	394		// Because of the "true" the formula parser accepts the comma as decimal and the
395	395		// semicolon as list separator if the workbook's FormatSettings are set up like this.
396	396		```
397	397
398	398		#### RPN formulas
399	399
400	400		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"]].
401	401
402	402		#### Shared formulas and array formulas
403	403
404	404		- Shared formulas are only supported for reading (from Excel files).
405	405		- Array formulas are not supported, currently.
406	406
407	407		#### List of built-in formulas
408	408
409	409		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"]].
410	410
411	411		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.
412	412
413	413		#### Extending FPSpreadsheet by user-defined formulas
414	414
415	415		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:
416	416
417	417		``` pascal
418	418		procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
419	419		const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprFunctionCallBack); overload;
420	420
421	421		procedure RegisterFunction(const AName: ShortString; const AResultType: Char;
422	422		const AParamTypes: String; const AExcelCode: Integer; ACallBack: TsExprEventCallBack); overload;
423	423		```
424	424
425	425		- `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.
426	426		- `AResultType` is a character which identifies the data type of the function result:
427	427		- `'F'` - floating point number
428	428		- `'I'` - integer
429	429		- `'D'` - date/time
430	430		- `'B'` - boolean
431	431		- `'S'` - string
432	432		- `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:
433	433		- `'?'` - any type
434	434		- `'+'` - 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.
435	435		- lowercase `'f', 'i', 'd', 'b', 's'` indicate optional parameters of the type explained above. Of course, uppercase symbols cannot follow lower-case symbols.
436	436		- `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.
437	437		- `ACallback` identifies which function is called by FPSpreadsheet for calculation of the formula. It can either be a procedure or an event handler.
438	438
439	439		``` pascal
440	440		type
441	441		TsExprFunctionCallBack = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray);
442	442		TsExprFunctionEvent = procedure (var Result: TsExpressionResult; const Args: TsExprParameterArray) of object;
443	443		```
444	444
445	445		The `TsExpressionResult` is a variant record containing result or argument data of several types:
446	446
447	447		``` pascal
448	448		type
449	449		TsResultType = (rtEmpty, rtBoolean, rtInteger, rtFloat, rtDateTime, rtString,
450	450		rtCell, rtCellRange, rtError, rtAny);
451	451
452	452		TsExpressionResult = record
453	453		Worksheet : TsWorksheet;
454	454		ResString : String;
455	455		case ResultType : TsResultType of
456	456		rtEmpty : ();
457	457		rtError : (ResError : TsErrorValue);
458	458		rtBoolean : (ResBoolean : Boolean);
459	459		rtInteger : (ResInteger : Int64);
460	460		rtFloat : (ResFloat : TsExprFloat);
461	461		rtDateTime : (ResDateTime : TDatetime);
462	462		rtCell : (ResRow, ResCol : Cardinal);
463	463		rtCellRange : (ResCellRange : TsCellRange);
464	464		rtString : ();
465	465		end;
466	466
467	467		TsExprParameterArray = array of TsExpressionResult;
468	468		```
469	469
470	470		As an example we show here the code for the `CONCATENATE()` formula which joins two or more strings:
471	471
472	472		``` pascal
473	473		const
474	474		INT_EXCEL_SHEET_FUNC_CONCATENATE = 336;
475	475		...
476	476		RegisterFunction('CONCATENATE', 'S', 'S+', INT_EXCEL_SHEET_FUNC_CONCATENATE, @fpsCONCATENATE);
477	477
478	478		procedure fpsCONCATENATE(var Result: TsExpressionResult; const Args: TsExprParameterArray);
479	479		// CONCATENATE( text1, text2, ... text_n )
480	480		var
481	481		s: String;
482	482		i: Integer;
483	483		begin
484	484		s := '';
485	485		for i:=0 to Length(Args)-1 do
486	486		begin
487	487		if Args[i].ResultType = rtError then
488	488		begin
489	489		Result := ErrorResult(Args[i].ResError);
490	490		exit;
491	491		end;
492	492		s := s + ArgToString(Args[i]);
493	493		// "ArgToString" simplifies getting the string from a TsExpressionResult as
494	494		// a string may be contained in the ResString and in the ResCell fields.
495	495		// There is such a function for each basic data type.
496	496		end;
497	497		Result := StringResult(s);
498	498		// "StringResult" stores the string s in the ResString field of the
499	499		// TsExpressionResult and sets the ResultType to rtString.
500	500		// There is such a function for each basic data type.
501	501		end;
502	502		```
503	503
504	504		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.
505	505
506	506		### Cell formatting
507	507
508	508		#### Number and date/time formats
509	509
510	510		Numbers and date/time values can be displayed in a variety of formats. In FPSpreadsheet this can be achieved in two ways:
511	511
512	512		- using built-in number formats by specifiying a value for the `NumberFormat` of the cell
513	513		- using a custom format string.
514	514
515	515		Number formats can be specified by these worksheet methods:
516	516
517	517		``` pascal
518	518		type
519	519		TsWorksheet = class
520	520		public
521	521		// Set number formats alone
522	522		function WriteNumberFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
523	523		const AFormatString: String = ''): PCell; overload;
524	524		procedure WriteNumberFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
525	525		const AFormatString: String = ''); overload;
526	526
527	527		function WriteNumberFormat(ARow, ACol: Cardinal; ANumFormat: TsNumberFormat;
528	528		ADecimals: Integer; ACurrencySymbol: String = ''; APosCurrFormat: Integer = -1;
529	529		ANegCurrFormat: Integer = -1): PCell; overload;
530	530		procedure WriteNumberFormat(ACell: PCell; ANumFormat: TsNumberFormat;
531	531		ADecimals: Integer; ACurrencySymbol: String = '';
532	532		APosCurrFormat: Integer = -1; ANegCurrFormat: Integer = -1); overload;
533	533
534	534		function WriteFractionFormat(ARow, ACol: Cardinal; AMixedFraction: Boolean;
535	535		ANumeratorDigits, ADenominatorDigits: Integer): PCell; overload;
536	536		procedure WriteFractionFormat(ACell: PCell; AMixedFraction: Boolean;
537	537		ANumeratorDigits, ADenominatorDigits: Integer); overload;
538	538
539	539		// Set date/time formats alone
540	540		function WriteDateTimeFormat(ARow, ACol: Cardinal; ANumberFormat: TsNumberFormat;
541	541		const AFormatString: String = ''): PCell; overload;
542	542		procedure WriteDateTimeFormat(ACell: PCell; ANumberFormat: TsNumberFormat;
543	543		const AFormatString: String = ''); overload;
544	544
545	545		// Set cell values and number formats in one call
546	546
547	547		// number values
548	548		function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
549	549		AFormat: TsNumberFormat = nfGeneral; ADecimals: Byte = 2;
550	550		ACurrencySymbol: String = ''): PCell; overload;
551	551		procedure WriteNumber(ACell: PCell; ANumber: Double; AFormat: TsNumberFormat = nfGeneral;
552	552		ADecimals: Byte = 2; ACurrencySymbol: String = ''); overload;
553	553		function WriteNumber(ARow, ACol: Cardinal; ANumber: double;
554	554		AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
555	555		procedure WriteNumber(ACell: PCell; ANumber: Double;
556	556		AFormat: TsNumberFormat; AFormatString: String); overload;
557	557
558	558		// date/time values
559	559		function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
560	560		AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''): PCell; overload;
561	561		procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
562	562		AFormat: TsNumberFormat = nfShortDateTime; AFormatStr: String = ''); overload;
563	563		function WriteDateTime(ARow, ACol: Cardinal; AValue: TDateTime;
564	564		AFormatStr: String): PCell; overload;
565	565		procedure WriteDateTime(ACell: PCell; AValue: TDateTime;
566	566		AFormatStr: String); overload;
567	567
568	568		// currency values
569	569		function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
570	570		AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = 2;
571	571		ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
572	572		ANegCurrFormat: Integer = -1): PCell; overload;
573	573		procedure WriteCurrency(ACell: PCell; AValue: Double;
574	574		AFormat: TsNumberFormat = nfCurrency; ADecimals: Integer = -1;
575	575		ACurrencySymbol: String = '?'; APosCurrFormat: Integer = -1;
576	576		ANegCurrFormat: Integer = -1); overload;
577	577		function WriteCurrency(ARow, ACol: Cardinal; AValue: Double;
578	578		AFormat: TsNumberFormat; AFormatString: String): PCell; overload;
579	579		procedure WriteCurrency(ACell: PCell; AValue: Double;
580	580		AFormat: TsNumberFormat; AFormatString: String); overload;
581	581		...
582	582		```
583	583
584	584		##### Built-in number formats
585	585
586	586		Los formatos internos están definidos por la enumeración `TsNumberFormat`. A pesar de su nombre, los elementos cubren tanto valores de número como la fecha/hora:
587	587
588	588		``` pascal
589	589		type
590	590		TsNumberFormat = (
591	591		// De propósito general para todos los números.
592	592		nfGeneral,
593	593		// numbers
594	594		nfFixed, nfFixedTh, nfExp, nfPercentage, nfFraction,
595	595		// currency
596	596		nfCurrency, nfCurrencyRed,
597	597		// dates and times
598	598		nfShortDateTime, nfShortDate, nfLongDate, nfShortTime, nfLongTime,
599	599		nfShortTimeAM, nfLongTimeAM, nfDayMonth, nfMonthYear, nfTimeInterval,
600	600		// other (using format string)
601	601		nfCustom);
602	602		```
603	603
604	604		- nfGeneral corresponde al formato por defecto mostrando tantos decimales como es posible (el número `3.141592654` would be unchanged.)
605	605		- nfFixed limita los decimales. El número de posiciones decimales se tiene que especificar en la llamada a `WriteNumber`. Example: with 2 decimals, the number `3.141592654` becomes `3.14`.
606	606		- nfFixedTh: similar a `nfFixed`, pero añade un separador de miles cuando el número se muestra como una cadena: 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.
607	607		- 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).
608	608		- 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).
609	609		- 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`.
610	610		- 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.
611	611		- nfCurrendyRed like `nfCurrency`, in addition negative values are displayed in red.
612	612		- 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.
613	613		- nfShortDate creates a date string showing day + two-digit month + two-digit year
614	614		- nfShortTime creates a time string showing hours + minutes.
615	615		- nfLongTime, similar, but includes seconds as well
616	616		- 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.
617	617		- nfLongTimeAM, like `nfShortTimeAM`, but includes seconds
618	618		- 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.
619	619		- nfCustom allows to specify a dedicated formatting string.
620	620
621	621		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.
622	622
623	623		##### Number format strings
624	624
625	625		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).
626	626
627	627		Here is a basic list of the symbols used:
628	628
629		-	~~<thead>~~
630		-
631		-	~~</thead>~~
632		-	~~<tbody>~~
633		-
634		-	~~1.2345678 --> <code>'1.2345678'</code></td>~~
635		-
636		-	~~<code>'0.0'</code>: 1 --> <code>'1.0'</code><br />~~
637		-	~~<code>'0.0'</code>: 1.2345678 --> <code>'1.2'</code></td>~~
638		-
639		-	~~<code>'0.*'</code>: 1.2345678 --> <code>'1.2'</code></td>~~
640		-
641		-	~~<code>'0.0??'</code>: 1 --> <code>'1.0 '</code></td>~~
642		-
643		-	~~<code>'0.00,'</code>: 1200 --> <code>'1.20'</code></td>~~
644		-
645		-	~~<code>'# #/#'</code>: 1.5 --> <code>'1 1/2'</code><br />~~
646		-	~~<code>'# #/16'</code>: 1.5 --> <code>'1 8/16'</code><br />~~
647		-	~~also: see date/time examples below</td>~~
648		-
649		-	~~Jan 3, 2012 --> <code>'2012-01-03'</code><br />~~
650		-	~~In this example, the <code>DateSeparator</code> is a dash character (<code>-</code>).</td>~~
651		-
652		-	~~Jan 3, 2012 --> <code>'12-01-03'</code></td>~~
653		-
654		-	~~Please note that the <code>m</code> code can also be interpreted as the "minutes" of a time value (see below).</td>~~
655		-
656		-	~~Jan 3, 2012 --> <code>'2012-1-03'</code></td>~~
657		-
658		-	~~Please note that the <code>mm</code> code can also be interpreted as the "minutes" of a time value (see below).</td>~~
659		-
660		-	~~Jan 3, 2012 --> <code>'2012-01-03'</code></td>~~
661		-
662		-	~~Jan 3, 2012 --> <code>'2012-Jan-03'</code></td>~~
663		-
664		-	~~Jan 3, 2012 --> <code>'2012-January-03'</code></td>~~
665		-
666		-	~~Jan 3, 2012 --> <code>'2012-01-3'</code></td>~~
667		-
668		-	~~Jan 3, 2012 --> <code>'2012-01-03'</code></td>~~
669		-
670		-	~~Jan 03, 2012 --> <code>'Tue 2012-01-03'</code></td>~~
671		-
672		-	~~Jan 03, 2012 --> <code>'Tuesday 2012-01-03'</code></td>~~
673		-
674		-	~~0.25 --> <code>'6:00'</code></td>~~
675		-
676		-	~~0.25 --> <code>'06:00'</code></td>~~
677		-
678		-	~~1.25 --> <code>'30:00'</code></td>~~
679		-
680		-	~~0.25 --> <code>'6:0'</code></td>~~
681		-
682		-	~~0.25 --> <code>'6:00'</code></td>~~
683		-
684		-	~~1.25 --> <code>'1800:00'</code></td>~~
685		-
686		-	~~0.25 --> <code>'06:00:0'</code></td>~~
687		-
688		-	~~0.25 --> <code>'06:00:00'</code></td>~~
689		-
690		-	~~1.25 --> <code>'108000'</code></td>~~
691		-
692		-	~~0.25 --> <code>'6:00:00 AM'</code></td>~~
693		-
694		-	~~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>~~
695		-
696		-	~~Jan 3, 2012 --> <code>'2012/01/03'</code></td>~~
697		-
698		-	~~1200 --> <code>'1,200$'</code><br />~~
699		-	~~-1200 --> <code>'1,200$'</code><br />~~
700		-	~~0 --> <code>'-'</code></td>~~
701		-
702		-	~~-1200 --> <code>'(1200)$'</code></td>~~
703		-
704		-	~~-1200 --> '<code>($ 1200.00)</code>'</td>~~
705		-
706		-	~~</tbody>~~
	629	+	\| Symbol \| Meaning \| Format string: Number --> Output string \|
	630	+	\|---\|---\|---\|
	631	+	\| `General` \| Displays all decimal places of the number \| `'General'`: · 1.2345678 --> `'1.2345678'` \|
	632	+	\| `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'` \|
	633	+	\| `` \| Like "0" above, but does not display insignificant zeros. \| `'0.'`: 1 --> `'1.'` · `'0.*'`: 1.2345678 --> `'1.2'` \|
	634	+	\| `?` \| 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 '` \|
	635	+	\| `.` \| Decimal separator; will be replaced by the value used in the `DecimalSeparator` of the workbook's `FormatSettings` \| `'0.00'`: 8.9 --> `'8.90'` \|
	636	+	\| `,` \| 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'` \|
	637	+	\| `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` \|
	638	+	\| `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` \|
	639	+	\| `%` \| 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%` \|
	640	+	\| `/` \| 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 \|
	641	+	\| `:` \| 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 \|
	642	+	\| `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 (`-`). \|
	643	+	\| `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'` \|
	644	+	\| `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'` \|
	645	+	\| `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'` \|
	646	+	\| `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'` \|
	647	+	\| `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'` \|
	648	+	\| `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'` \|
	649	+	\| `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'` \|
	650	+	\| `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'` \|
	651	+	\| `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'` \|
	652	+	\| `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'` \|
	653	+	\| `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'` \|
	654	+	\| `[hh]`, or `[h]` \| Displays elapsed time such that the hour part can become greater than 23 \| `'[h]:mm'`: · 1.25 --> `'30:00'` \|
	655	+	\| `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'` \|
	656	+	\| `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'` \|
	657	+	\| `[mm]`, or `[m]` \| Displays elapsed time such that the minute part can become greater than 59 \| `'[mm]:ss'`: · 1.25 --> `'1800:00'` \|
	658	+	\| `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'` \|
	659	+	\| `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'` \|
	660	+	\| `[ss]`, or `[s]` \| Displays elapsed time such that the seconds part can become greater than 59 \| `'[ss]'`: · 1.25 --> `'108000'` \|
	661	+	\| `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'` \|
	662	+	\| `"` \| 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). \|
	663	+	\| `\` \| 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'` \|
	664	+	\| `;` \| 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 --> `'-'` \|
	665	+	\| `(`, and `)` \| Sometimes used for currency values to indicate negative numbers, instead of minus sign \| `'#,##0"$";(#,##0)"$"'`: · -1200 --> `'(1200)$'` \|
	666	+	\| `[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)`' \|
707	667
708	668		#### Colores
709	669
710	670		FPSpreadsheet soporta colores de texto, fondo de celda y bordes de celda. En la unit fpstypes se declaran los colores EGA básicos como constantes.
711	671
712	672		![](/wiki-images/7/7b/DefaultPalette.png)
713	673
714	674		``` pascal
715	675		type
716	676		TsColor = DWORD; // ts = text style color
717	677
718	678		const
719	679		scBlack = $00000000; // Negro
720	680		scWhite = $00FFFFFF; // Blanco
721	681		scRed = $000000FF; // Rojo
722	682		scGreen = $0000FF00; // Verde
723	683		scBlue = $00FF0000; // Azul
724	684		scYellow = $0000FFFF; // Amarillo
725	685		scMagenta = $00FF00FF; // Magenta
726	686		scCyan = $00FFFF00; // Cian
727	687		scDarkRed = $00000080; // Rojo oscuro
728	688		scDarkGreen = $00008000; // Verde oscuro
729	689		scDarkBlue = $00800000; // Azul oscuro
730	690		scOlive = $00008080; // Oliva
731	691		scPurple = $00800080; // Púrpura
732	692		scTeal = $00808000; // Verde azulado
733	693		scSilver = $00C0C0C0; // Plateado
734	694		scGray = $00808080; // Gris
735	695		scGrey = scGray; // redefine to allow different spelling
736	696
737	697		// Identificador para color no definido
738	698		scNotDefined = $40000000;
739	699
740	700		// Identifier for transparent color
741	701		scTransparent = $20000000;
742	702		```
743	703
744	704		El `TsColor` representa el valor rgb (red,green,blue) de un color, un solo byte utilizado para el rojo (red), otro para el verde (green), y otro para el azul(blue). El número resultante tiene notación little endian, i.e. el valor rojo va primero en memoria: `$00BBGGRR`. (Esto es directamente compatible con los valores de color tales como están definidos en la unit graphics).
745	705
746	706		The high order byte is usually zero but is used internally to identify special color values, such as for undefined or transparent colors.
747	707
748	708		> Note: In older versions of the library colors were defined as indexes into a color palette. THIS IS NO LONGER WORKING.
749	709
750	710		Unit fpsutils contains some useful functions for modification of colors:
751	711
752	712		- `function GetColorName(AColor: TsColor): String;`\
753	713		returns the name of the colors defined above, or a string showing the rgb components for other colors.
754	714		- `function HighContrastColor(AColor: TsColor): TsColor;`\
755	715		returns `scBlack` for a "bright", `scWhite` for a "dark" input color.
756	716		- `function TintedColor(AColor: TsColor; tint: Double): TsColor;`\
757	717		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.
758	718
759	719		#### Cell background
760	720
761	721		The cell background can be filled by predefined patterns which are identified by the record `TsFillPattern`:
762	722
763	723		``` pascal
764	724		type
765	725		TsFillPattern = record
766	726		Style: TsFillStyle; // fill style pattern as defined below
767	727		FgColor: TsColor; // forground color of the fill pattern
768	728		BgColor: TsColor; // background color of the fill pattern
769	729		end;
770	730
771	731		TsFillStyle = (fsNoFill, fsSolidFill, fsGray75, fsGray50, fsGray25, fsGray12, fsGray6,
772	732		fsStripeHor, fsStripeVert, fsStripeDiagUp, fsStripeDiagDown,
773	733		fsThinStripeHor, fsThinStripeVert, fsThinStripeDiagUp, fsThinStripeDiagDown,
774	734		fsHatchDiag, fsThinHatchDiag, fsThickHatchDiag, fsThinHatchHor);
775	735		```
776	736
777	737		- 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.
778	738		- The fill pattern of a particular cell can be retrieved by calling the workbook method `ReadBackground`.
779	739		- The simplified method `WriteBackgroundColor` can be used to achieve a uniform background color.
780	740		- Limitations:
781	741		- 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.
782	742		- BIFF2 files support only a 12.5% black-and-white shaded pattern.
783	743
784	744		``` pascal
785	745		type
786	746		TsWorksheet = class
787	747		public
788	748		function WriteBackground(ARow, ACol: Cardinal; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor): PCell; overload;
789	749		procedure WriteBackground(ACell: PCell; AStyle: TsFillStyle; APatternColor, ABackgroundColor: TsColor); overload;
790	750
791	751		function WriteBackgroundColor(ARow, ACol: Cardinal; AColor: TsColor): PCell; overload;
792	752		procedure WriteBackgroundColor(ACell: PCell; AColor: TsColor); overload;
793	753
794	754		function ReadBackground(ACell: PCell): TsFillPattern;
795	755		function ReadBackgroundColor(ACell: PCell): TsColor; overload;
796	756		// ...
797	757		end;
798	758
799	759		var
800	760		cell: PCell;
801	761		...
802	762		// Example 1: Assign a pattern of thin, horizontal, yellow stripes on a blue background to empty cell A1 (row 0, column 0)
803	763		MyWorksheet.WriteBackground(0, 0, fsThinStripeHor, scYellow, scBlue);
804	764
805	765		// Example 2: Uniform gray background color of cell B1 (row 0, column 1) containing the number 3.14
806	766		cell := MyWorksheet.WriteNumber(0, 1, 3.14);
807	767		MyWorksheet.WriteBackgroundColor(cell, clSilver);
808	768		```
809	769
810	770		#### Cell borders
811	771
812	772		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`:
813	773
814	774		``` pascal
815	775		type
816	776		TsCellBorder = (cbNorth, cbWest, cbEast, cbSouth, dbDiagUp, dbDiagDown);
817	777		TsCellBorders = set of TsCellBorder;
818	778		```
819	779
820	780		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:
821	781
822	782		``` pascal
823	783		MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // A1: row 0, column 0
824	784		MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSourth]); // B1: row 0, column 1
825	785		```
826	786
827	787		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:
828	788
829	789		``` pascal
830	790		type
831	791		TsLineStyle = (lsThin, lsMedium, lsDashed, lsDotted, lsThick, lsDouble, lsHair,
832	792		lsMediumDash, lsDashDot, lsMediumDashDot, lsDashDotDot, lsMediumDashDotDot, lsSlantDashDot);
833	793
834	794		TsCellBorderStyle = record
835	795		LineStyle: TsLineStyle;
836	796		Color: TsColor;
837	797		end;
838	798
839	799		TsCellBorderStyles = array[TsCellBorder] of TsCellBorderStyle;
840	800
841	801		TsWorksheet = class
842	802		public
843	803		function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; AStyle: TsCellBorderStyle): PCell; overload;
844	804		procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; AStyle: TsCellBorderStyle); overload;
845	805
846	806		function WriteBorderStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor): PCell; overload;
847	807		procedure WriteBorderStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle; AColor: TsColor); overload;
848	808
849	809		function WriteBorderColor(ARow, ACol: Cardinal; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
850	810		proceure WriteBorderColor(ACell: PCell; ABorder: TsCellBorder; AColor: TsColor): PCell; overload;
851	811
852	812		function WriteBorderLineStyle(ARow, ACol: Cardinal; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
853	813		procedure WriteBorderLineStyle(ACell: PCell; ABorder: TsCellBorder; ALineStyle: TsLineStyle): PCell; overload;
854	814
855	815		function WriteBorderStyles(ARow, ACol: Cardinal; const AStyles: TsCellBorderStyles): PCell; overload;
856	816		procedure WriteBorderStyles(ACell: PCell; const AStyles: TsCellBorderStyles); overload;
857	817
858	818		function WriteBorders(ARow, ACol: Cardinal; ABorders: TsCellBorders): PCell; overload
859	819		procedure WriteBorders(ACell: PCell; ABorders: TsCellBorders); overload
860	820
861	821		function ReadCellBorders(ACell: PCell): TsCellBorders;
862	822		function ReadCellBorderStyle(ACell: PCell; ABorder: TsCellBorder): TsCellBorderStyle;
863	823		function ReadCellBorderStyles(ACell: PCell): TsCellBorderStyles;
864	824		...
865	825		end;
866	826		```
867	827
868	828		The style of a given cell border can be specified by the following methods provided by the worksheet:
869	829
870	830		- 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.
871	831		- WriteBorderColor changes the color of a given border without affecting the line style of this border.
872	832		- WriteBorderLineStyle sets the line style of the border only, but leaves the color unchanged.
873	833		- 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.
874	834
875	835		This example adds a thin black border to the top, and a thick blue border to the bottom of cells A1 and B1:
876	836
877	837		``` pascal
878	838		var
879	839		cellA1, cellB1: PCell;
880	840		...
881	841		cellA1 := MyWorksheet.WriteBorders(0, 0, [cbNorth, cbSouth]); // cell A1: row 0, column 0
882	842		MyWorksheet.WriteBorderStyle(cellA1, cbNorth, lsThin, scBlack);
883	843		MyWorksheet.WriteBorderStyle(cellA1, cbSouth, lsThick, scBlue);
884	844
885	845		cellB1 := MyWorksheet.WriteBorders(0, 1, [cbNorth, cbSouth]); // cell B1: row 0, column 1
886	846		MyWorksheet.WriteBorderStyles(cellB1, cellA1^.BorderStyles); // copy all border styles from cell A1 to B1
887	847		```
888	848
889	849		> 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.
890	850
891	851		#### Fonts
892	852
893	853		The cell text can displayed in various fonts. For this purpose, the workbook provides a list of `TsFont` items:
894	854
895	855		``` pascal
896	856		type
897	857		TsFont = class
898	858		FontName: String;
899	859		Size: Single;
900	860		Style: TsFontStyles;
901	861		Color: TsColor;
902	862		Position: TsFontPosition;
903	863		end;
904	864		```
905	865
906	866		- The FontName corresponds to the name of the font as used by the operational system. In Windows, an example would be "Times New Roman".
907	867		- The FontSize is given in "points", i.e. units 1/72 inch which are commonly used in Office applications.
908	868		- 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.
909	869		- The Color determines the foreground color of the text characters given in rgb presentation as discussed above.
910	870		- 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).
911	871
912	872		Every cell is provided with an index into the font list.
913	873
914	874		In order to assign a particular font to a cell, use one of the following methods of `TsSpreadsheet`:
915	875
916	876		``` pascal
917	877		type
918	878		TsSpreadsheet = class
919	879		public
920	880		function WriteFont(ARow, ACol: Cardinal; const AFontName: String;
921	881		AFontSize: Single; AFontStyle: TsFontStyles; AFontColor: TsColor): Integer; overload;
922	882		procedure WriteFont(ARow, ACol: Cardinal; AFontIndex: Integer); overload;
923	883		function WriteFontColor(ARow, ACol: Cardinal; AFontColor: TsColor): Integer;
924	884		function WriteFontSize(ARow, ACol: Cardinal; ASize: Integer): Integer;
925	885		function WriteFontStyle(ARow, ACol: Cardinal; AStyle: TsFontStyles): Integer;
926	886		// plus: overloaded versions accepting a pointer to a cell record instead of the row and column index as parameter
927	887		// ...
928	888		end;
929	889		```
930	890
931	891		- 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.
932	892		- 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.
933	893		- WriteFontSize replaces the size of the currently used font of the cell.
934	894		- WriteFontStyle replaces the style (normal, bold, italic, etc.) of the currently used cell font.
935	895
936	896		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.
937	897
938	898		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`.
939	899
940	900		Here is an example which decreases the size of all 10-point "Arial" fonts to 9-point:
941	901
942	902		``` pascal
943	903		var
944	904		i: Integer;
945	905		font: TsFont;
946	906		begin
947	907		for i := 0 to MyWorkbook.GetFontCount-1 do
948	908		begin
949	909		font := MyWorkbook.GetFont(i);
950	910		if (font.FontName = 'Arial') and (font.Size = 10.0) then
951	911		font.Size := 9.0;
952	912		end;
953	913		end;
954	914		```
955	915
956	916		#### Rich-text formatting
957	917
958	918		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).
959	919
960	920		For this purpose, unit fpstypes declares the type `TsRichTextParams` which is an array of `TsRichTextParam` records:
961	921
962	922		``` pascal
963	923		type
964	924		TsRichTextParam = record
965	925		FontIndex: Integer;
966	926		FirstIndex: Integer;
967	927		end;
968	928
969	929		TsRichTextParams = array of TsRichTextParam;
970	930		```
971	931
972	932		`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.
973	933
974	934		There are two ways to add "rich-text" formatting to a cell text:
975	935
976	936		- 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
977	937
978	938		``` pascal
979	939		MyWorksheet.WriteTextAsHTML(0, 0, 'Area (m<sup>2</sup>');
980	940		```
981	941
982	942		- Alternatively, the standard text writing method, `WriteText` can be called with an additional parameter specifiying the rich-text formatting parameters to be used directly:
983	943
984	944		``` pascal
985	945		var
986	946		richTextParams: TsRichTextParams;
987	947		fnt: TsFont;
988	948		begin
989	949		SetLength(rtp, 2);
990	950		fnt := MyWorksheet.ReadCellFont(0, 0);
991	951		richTextParams[0].FirstIndex := 8; // The superscript groups begins with "2" which is the (1-based) character #8 of the cell text.
992	952		richTextParams[0].FontIndex := MyWorkbook.AddFont(fnt.FontName, fnt.Size, fnt.Style, fnt.Color, fpSuperscript);
993	953		richTextParams[1].FirstIndex := 9; // Normal font again beginning with character #9.
994	954		richTextParams[1].FontIndex := MyWorksheet.ReadCellFontIndex(0, 0);
995	955		MyWorksheet.WriteUTF8Text(0, 0, 'Area (m2)', richTextParams);
996	956		end;
997	957		```
998	958
999	959		Use the worksheet method `DeleteRichTextParams` to remove rich-text formatting from a previously formatted cell.
1000	960
1001	961		> 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.
1002	962
1003	963		#### Text rotation
1004	964
1005	965		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.
1006	966
1007	967		If you need this feature use the worksheet method `WriteTextRotation` and specify the text direction by an element of the enumeration type `TsTextRotation`:
1008	968
1009	969		``` pascal
1010	970		type
1011	971		TsTextRotation = (trHorizontal, rt90DegreeClockwiseRotation,
1012	972		rt90DegreeCounterClockwiseRotation, rtStacked);
1013	973
1014	974		TsWorksheet = class
1015	975		public
1016	976		function WriteTextRotation(ARow, ACol: Cardinal; ARotation: TsTextRotation): PCell; overload;
1017	977		procedure WriteTextRotation(ACell: PCell; ARotation: TsTextRotation); overload;
1018	978
1019	979		function ReadTextRotation(ACell: PCell): TsTextRotation;
1020	980		// ...
1021	981		end;
1022	982
1023	983		// example for counter-clockwise rotated text in cell A1:
1024	984		WriteTextRotation(0, 0, rt90DegreeCounterClockwizeRotation);
1025	985		```
1026	986
1027	987		> Warning: Finer degrees of rotation which may be supported by some spreadsheet file formats are ignored.
1028	988
1029	989		#### Text alignment
1030	990
1031	991		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`:
1032	992
1033	993		``` pascal
1034	994		type
1035	995		TsHorAlignment = (haDefault, haLeft, haCenter, haRight);
1036	996		TsVertAlignment = (vaDefault, vaTop, vaCenter, vaBottom);
1037	997
1038	998		TsWorkbook = class
1039	999		public
1040	1000		function WriteHorAlignment(ARow, ACol: Cardinal; AValue: TsHorAlignment): PCell; overload;
1041	1001		procedure WriteHorAlignment(ACell: PCell; AValue: TsHorAlignment); overload;
1042	1002		function ReadHorAlignment(ACell: PCell): TsHorAlignment;
1043	1003
1044	1004		function WriteVertAlignment(ARow, ACol: Cardinal; AValue: TsVertAlignment): PCell; overload;
1045	1005		procedure WriteVertAlignment(ACell: PCell; AValue: TsVertAlignment); overload;
1046	1006		function ReadVertAlignment(ACell: PCell): TsVertAlignment;
1047	1007		// ...
1048	1008		end;
1049	1009
1050	1010		// Example: Center the text in cell A1 both horizontally and vertically
1051	1011		MyWorkbook.WriteHorAlignment(0, 0, haCenter);
1052	1012		MyWorkbook.WriteVertAlignment(0, 0, vaCenter);
1053	1013		```
1054	1014
1055	1015		#### Word wrap
1056	1016
1057	1017		Text which is longer than the width of a cell can wrap into several lines by calling the method `WriteWordwrap` of the spreadsheet:
1058	1018
1059	1019		``` pascal
1060	1020		type
1061	1021		TsWorksheet = class
1062	1022		public
1063	1023		function WriteWordwrap(ARow, ACol: Cardinal; AValue: Boolean): PCell; overload;
1064	1024		procedure WriteWordwrap(ACell: PCell; AValue: Boolean); overload;
1065	1025		function ReadWordwrap(ACell: PCell): Boolean;
1066	1026		//...
1067	1027		end;
1068	1028
1069	1029		// Example: activate wordwrap in cell A1
1070	1030		MyWorksheet.WriteWordwrap(0, 0, true);
1071	1031		```
1072	1032
1073	1033		#### Merged cells
1074	1034
1075	1035		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:
1076	1036
1077	1037		``` pascal
1078	1038		MyWorksheet.MergeCells('A1:D5');
1079	1039		// or: MyWorksheet.MergeCells(0, 0, 4, 3); // first row, first column, last row, last column
1080	1040		```
1081	1041
1082	1042		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.
1083	1043
1084	1044		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:
1085	1045
1086	1046		``` pascal
1087	1047		MyWorksheet.UnmergeCells('B1');
1088	1048		// or: MyWorksheet.UnmergeCells(0, 1); // row, column of any cell within the range
1089	1049		```
1090	1050
1091	1051		Merged cells can be read from/written to all file formats except for `sfCSV`, `sfExcel2` and `sfExcel5` which do not support this feature natively.
1092	1052
1093	1053		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:
1094	1054
1095	1055		``` pascal
1096	1056		var
1097	1057		cell, base: PCell;
1098	1058		r1,c1,r2,c2: Cardinal;
1099	1059		...
1100	1060		cell := MyWorksheet.FindCell('B1');
1101	1061		if MyWorksheet.IsMerged(cell) then
1102	1062		begin
1103	1063		WriteLn('Cell B1 is merged.');
1104	1064
1105	1065		MyWorksheet.FindMergedRange(cell, r1, c1, r2, c2);
1106	1066		WriteLn('The merged range is ' + GetCellRangeString(r1, c1, r2, c2));
1107	1067
1108	1068		base := MyWorksheet.FindMergeBase(cell);
1109	1069		WriteLn('The merge base is cell ' + GetCellString(base^.Row, base^.Col));
1110	1070		end;
1111	1071		```
1112	1072
1113	1073		### Additional data
1114	1074
1115	1075		#### Cell comments
1116	1076
1117	1077		Comments can be attached to any cell by calling
1118	1078
1119	1079		``` pascal
1120	1080		MyWorksheet.WriteComment(0, 0, 'This is a comment for cell A1');
1121	1081		```
1122	1082
1123	1083		They are stored in an internal list of the worksheet. Use the corresponding worksheet methods to access comments:
1124	1084
1125	1085		- If you want to know whether a particular cell contains a comment call the worksheet method `HasComment(cell)`.
1126	1086		- For retrieving a cell comment use the method `ReadComment(cell)`, or its overloaded companion `ReadComment(ARow, ACol)`.
1127	1087		- The total number of comments can be retrieved from `worksheet.Comments.Count`.
1128	1088
1129	1089		#### Hyperlinks
1130	1090
1131	1091		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
1132	1092
1133	1093		``` pascal
1134	1094		procedure TWorksheet.WriteHyperlink(ARow, ACol: Cardinal; ATarget: String; ATooltip: String = '');
1135	1095		```
1136	1096
1137	1097		- 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 `'!'`.
1138	1098		- The optional `Tooltip` parameter is evaluated by Excel to display it in a hint window if the mouse if above the hyperlink.
1139	1099
1140	1100		> 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.
1141	1101
1142	1102		Examples:
1143	1103
1144	1104		``` pascal
1145	1105		// Open the web site www.google.com
1146	1106		MyWorksheet.WriteText(0, 0, 'Open google');
1147	1107		MyWorksheet.WriteHyperlink(0, 0, 'http://www.google.com');
1148	1108
1149	1109		// Open the local file with the absolute path "C:\readme.txt" (assuming Windows)
1150	1110		MyWorksheet.WriteHyperlink(1, 0, 'file:///c:\readme.txt');
1151	1111
1152	1112		// Open the mail client to send a mail
1153	1113		MyWorksheet.WriteText('Send mail');
1154	1114		MyWorksheet.WriteHyperlink(3, 0, 'mailto:somebody@gmail.com?subject=Test');
1155	1115
1156	1116		// Jump to a particular cell
1157	1117		MyWorksheet.WriteText(5, 0, 'Jump to cell A10 on sheet2');
1158	1118		MyWorksheet.WriteHyperlink(5, 0, '#Sheet2!A10');
1159	1119
1160	1120		// Jump to cell A10 on the current sheet and display a popup hint
1161	1121		MyWorksheet.WriteHyperlink(5, 0, '#A10', 'Go to cell A10');
1162	1122		```
1163	1123
1164	1124		> 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.
1165	1125
1166	1126		#### Images
1167	1127
1168	1128		FPSpreadsheet supports embedding of images in worksheets. Use one of the worksheet methods `WriteImage()` to add an image to the worksheet:
1169	1129
1170	1130		``` pascal
1171	1131		MyWorksheet.WriteImage(row, col, filename, offsetX, offsetY, scaleX, scaleY);
1172	1132		MyWorksheet.WriteImage(row, col, stream, offsetX, offsetY, scaleX, scaleY);
1173	1133		MyWorksheet.WriteImage(row, col, imageindex, offsetX, offsetY, scaleX, scaleY);
1174	1134		```
1175	1135
1176	1136		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.
1177	1137
1178	1138		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:
1179	1139
1180	1140		``` pascal
1181	1141		type
1182	1142		TsImageType = integer;
1183	1143		TGetImageSizeFunc = function (AStream: TStream; out AWidth, AHeight: DWord; out dpiX, dpiY: Double): Boolean;
1184	1144
1185	1145		function RegisterImageType(AMimeType, AExtension: String; AGetImageSize: TGetImageSizeFunc): TsImageType;
1186	1146		```
1187	1147
1188	1148		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:
1189	1149
1190	1150		- Predefine the widths of all columns at least up to the one containing the right edge of the image.
1191	1151		- Predefine the heights of all rows at least up to the one containing the lower edge of the image.
1192	1152		- 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).
1193	1153		- If the exact size of the image is important make sure that it fits into a single cell.
1194	1154
1195	1155		### Sorting
1196	1156
1197	1157		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'`):
1198	1158
1199	1159		``` pascal
1200	1160		type
1201	1161		TsWorksheet = class
1202	1162		// ...
1203	1163		procedure Sort(const ASortParams: TsSortParams;
1204	1164		ARowFrom, AColFrom, ARowTo, AColTo: Cardinal); overload;
1205	1165		procedure Sort(ASortParams: TsSortParams; ARange: String); overload;
1206	1166		// ...
1207	1167		end;
1208	1168		```
1209	1169
1210	1170		The sorting criteria are defined by a record of type `TsSortParams`:
1211	1171
1212	1172		``` pascal
1213	1173		type
1214	1174		TsSortParams = record
1215	1175		SortByCols: Boolean;
1216	1176		Priority: TsSortPriority; // spNumAlpha ("Numbers first"), or spAlphaNum ("Text first")
1217	1177		Keys: TsSortKeys;
1218	1178		end;
1219	1179
1220	1180		TsSortKey = record
1221	1181		ColRowIndex: Integer;
1222	1182		Options: TsSortOptions; // set of [spDescending, spCaseInsensitive]
1223	1183		end;
1224	1184		```
1225	1185
1226	1186		- 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).
1227	1187		- `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`).
1228	1188		- 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.
1229	1189
1230	1190		`InitSortParams` is a handy utility to initialize the sorting parameters:
1231	1191
1232	1192		``` pascal
1233	1193		function InitSortParams(ASortByCols: Boolean = true; ANumSortKeys: Integer = 1;
1234	1194		ASortPriority: TsSortPriority = spNumAlpha): TsSortParams;
1235	1195		```
1236	1196
1237	1197		The next code fragment shows a typical sorting call:
1238	1198
1239	1199		``` pascal
1240	1200		var
1241	1201		sortParams: TsSortParams;
1242	1202		begin
1243	1203		sortParams := InitSortParams(true, 2); // sorting along columns, 2 sorting keys
1244	1204
1245	1205		// primary sorting key: column 3, ascending, case-insensitive
1246	1206		sortParams.Keys[0].ColRowIndex := 3;
1247	1207		sortParams.Keys[0].Options := [ssoCaseInsensitive];
1248	1208
1249	1209		// secondary sorting key: colum 1, descending
1250	1210		sortParams.Keys[1].ColRowIndex := 1;
1251	1211		sortParams.Keys[1].Options := [ssoDescending];
1252	1212
1253	1213		// The sorted block extends between cells A1 (row=0, col=0) and F10 (row=9, col=5)
1254	1214		MyWorksheet.Sort(sortParams, 0, 0, 9, 5);
1255	1215		// or: MyWorksheet.Sort(sortParams, 'A1:F10');
1256	1216		end;
1257	1217		```
1258	1218
1259	1219		### Searching and replacing
1260	1220
1261	1221		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.
1262	1222
1263	1223		Example:
1264	1224
1265	1225		``` pascal
1266	1226		var
1267	1227		MyWorkbook: TsWorkbook;
1268	1228		MyWorksheet: TsWorksheet;
1269	1229		MyRow, MyCol: Cardinal;
1270	1230		MySearchParams: TsSearchParams;
1271	1231		begin
1272	1232		MyWorkbook := TsWorkbook.Create;
1273	1233		try
1274	1234		MyWorkbook.ReadFromFile(AFileName);
1275	1235
1276	1236		// Specify search criteria
1277	1237		MySearchParams.SearchText := 'Hallo';
1278	1238		MySearchParams.Options := [soEntireDocument];
1279	1239
1280	1240		// Create search engine and execute search
1281	1241		with TSearchEngine.Create(MyWorkbook) do begin
1282	1242		if FindFirst(MySearchParams, MyWorksheet, MyRow, MyCol) then begin
1283	1243		WriteLn('First "', MySearchparams.SearchText, '" found in cell ', GetCellString(MyRow, MyCol));
1284	1244		while FindNext(MySeachParams, MyWorksheet, myRow, MyCol) do
1285	1245		WriteLn('Next "', MySearchParams.SearchText, '" found in cell ', GetCellString(MyRow, MyCol));
1286	1246		end;
1287	1247		Free;
1288	1248		end;
1289	1249		finally
1290	1250		MyWorkbook.Free;
1291	1251		end;
1292	1252		end;
1293	1253		```
1294	1254
1295	1255		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`.
1296	1256
1297	1257		The record `TsSearchParams` specifies the criteria used for searching:
1298	1258
1299	1259		``` pascal
1300	1260		type
1301	1261		TsSearchParams = record
1302	1262		SearchText: String;
1303	1263		Options: TsSearchOptions;
1304	1264		Within: TsSearchWithin;
1305	1265		end;
1306	1266		```
1307	1267
1308	1268		Besides the text to be searched (`SearchText`) it provides a set of options to narrow the search:
1309	1269
1310	1270		- `soCompareEntireCell`: Compares the `SearchText` with the entire cell content. If not contained in the `Options` then the cell text is compared only partially.
1311	1271		- `soMatchCase`: Perform a case-sensitive search
1312	1272		- `soRegularExpr`: The `SearchText` is considered as a regular expression
1313	1273		- `soAlongRows`: The search engine proceeds first along the rows. If not contained in the `Options` then the search proceeds along the columns.
1314	1274		- `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.
1315	1275		- `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).
1316	1276		- `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`.
1317	1277
1318	1278		The record field `Within` identifies the part of the spreadsheet to be searched:
1319	1279
1320	1280		- `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.
1321	1281		- `swWorksheet`: The search is limited to the currently active worksheet
1322	1282		- `swColumn`: Search is restricted to the column of the active cell
1323	1283		- `swRow`: Search is restricted to the row of the active cell.
1324	1284
1325	1285		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:
1326	1286
1327	1287		``` pascal
1328	1288		type
1329	1289		TsReplaceParams = record
1330	1290		ReplaceText: String;
1331	1291		Options: TsReplaceOptions;
1332	1292		end;
1333	1293		```
1334	1294
1335	1295		The `ReplaceText` identifies the string which will replace the found text pattern. The `Options` define a set of criteria how the replacement is done:
1336	1296
1337	1297		- `roReplaceEntirecell`: Replaces the entire cell text by the `ReplaceText`. If not contained in the `Options` then only the part matching the `SearchText` is replaced.
1338	1298		- `roReplaceAll`: Performs the replacement in all found cells (i.e., simply call `ReplaceFirst` to replace all automatically).
1339	1299		- `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.
1340	1300
1341	1301		### Column and row operations
1342	1302
1343	1303		The worksheet provides these methods for inserting and deleting columns a row:
1344	1304
1345	1305		``` pascal
1346	1306		type
1347	1307		TsWorksheet = class
1348	1308		...
1349	1309		procedure DeleteCol(ACol: Cardinal);
1350	1310		procedure DeleteRow(ARow: Cardinal);
1351	1311
1352	1312		procedure InsertCol(ACol: Cardinal);
1353	1313		procedure InsertRow(ARow: Cardinal);
1354	1314
1355	1315		procedure RemoveCol(ACol: Cardinal);
1356	1316		procedure RemoveRow(ARow: Cardinal);
1357	1317
1358	1318		procedure RemoveAllCols;
1359	1319		procedure RemoveAllRows
1360	1320		```
1361	1321
1362	1322		- When a column or row is deleted by DeleteCol\</t\> 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.
1363	1323
1364	1324		- `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.
1365	1325
1366	1326		- `RemoveAllCols` removes all column records, i.e. resets all column widths; `RemoveAllRows` does the same with the row records and row heights.
1367	1327
1368	1328		- A column or row is inserted before the index specified as parameter of the `InsertXXX` method.
1369	1329
1370	1330		### Page layout
1371	1331
1372	1332		#### General
1373	1333
1374	1334		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.
1375	1335
1376	1336		``` pascal
1377	1337		type
1378	1338		TsPageOrientation = (spoPortrait, spoLandscape);
1379	1339
1380	1340		TsPrintOption = (poPrintGridLines, poPrintHeaders, poPrintPagesByRows,
1381	1341		poMonochrome, poDraftQuality, poPrintCellComments, poDefaultOrientation,
1382	1342		poUseStartPageNumber, poCommentsAtEnd, poHorCentered, poVertCentered,
1383	1343		poDifferentOddEven, poDifferentFirst, poFitPages);
1384	1344
1385	1345		TsPrintOptions = set of TsPrintOption;
1386	1346
1387	1347		TsHeaderFooterSectionIndex = (hfsLeft, hfsCenter, hfsRight);
1388	1348
1389	1349		TsCellRange = record
1390	1350		Row1, Col1, Row2, Col2: Cardinal;
1391	1351		end;
1392	1352
1393	1353		TsPageLayout = class
1394	1354		...
1395	1355		public
1396	1356		...
1397	1357		{ Methods }
1398	1358		// embedded header/footer images
1399	1359		procedure AddHeaderImage(AHeaderIndex: Integer;
1400	1360		ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1401	1361		procedure AddFooterImage(AFooterIndex: Integer;
1402	1362		ASection: TsHeaderFooterSectionIndex; const AFilename: String);
1403	1363		procedure GetImageSections(out AHeaderTags, AFooterTags: String);
1404	1364		function HasHeaderFooterImages: Boolean;
1405	1365
1406	1366		// Repeated rows and columns
1407	1367		function HasRepeatedCols: Boolean;
1408	1368		function HasRepeatedRows: Boolean;
1409	1369		procedure SetRepeatedCols(AFirstCol, ALastCol: Cardinal);
1410	1370		procedure SetRepeatedRows(AFirstRow, ALastRow: Cardinal);
1411	1371
1412	1372		// print ranges
1413	1373		function AddPrintRange(ARow1, ACol1, ARow2, ACol2: Cardinal): Integer; overload;
1414	1374		function AddPrintRange(const ARange: TsCellRange): Integer; overload;
1415	1375		function GetPrintRange(AIndex: Integer): TsCellRange;
1416	1376		function NumPrintRanges: Integer;
1417	1377		procedure RemovePrintRange(AIndex: Integer);
1418	1378
1419	1379		{ Properties }
1420	1380		property Orientation: TsPageOrientation read FOrientation write FOrientation;
1421	1381		property PageWidth: Double read FPageWidth write FPageWidth;
1422	1382		property PageHeight: Double read FPageHeight write FPageHeight;
1423	1383		property LeftMargin: Double read FLeftMargin write FLeftMargin;
1424	1384		property RightMargin: Double read FRightMargin write FRightMargin;
1425	1385		property TopMargin: Double read FTopMargin write FTopMargin;
1426	1386		property BottomMargin: Double read FBottomMargin write FBottomMargin;
1427	1387		property HeaderMargin: Double read FHeaderMargin write FHeaderMargin;
1428	1388		property FooterMargin: Double read FFooterMargin write FFooterMargin;
1429	1389		property StartPageNumber: Integer read FStartPageNumber write SetStartPageNumber;
1430	1390		property ScalingFactor: Integer read FScalingFactor write SetScalingFactor;
1431	1391		property FitHeightToPages: Integer read FFitHeightToPages write SetFitHeightToPages;
1432	1392		property FitWidthToPages: Integer read FFitWidthToPages write SetFitWidthToPages;
1433	1393		property Copies: Integer read FCopies write FCopies;
1434	1394		property Options: TsPrintOptions read FOptions write FOptions;
1435	1395		property Headers[AIndex: Integer]: String read GetHeaders write SetHeaders;
1436	1396		property Footers[AIndex: Integer]: String read GetFooters write SetFooters;
1437	1397		property RepeatedCols: TsRowColRange read FRepeatedCols;
1438	1398		property RepeatedRows: TsRowColRange read FRepeatedRows;
1439	1399		property PrintRange[AIndex: Integer]: TsCellRange read GetPrintRange;
1440	1400		property FooterImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetFooterImages;
1441	1401		property HeaderImages[ASection: TsHeaderFooterSectionIndex]: TsHeaderFooterImage read GetHeaderImages;
1442	1402		end;
1443	1403
1444	1404		TsWorksheet = class
1445	1405		...
1446	1406		public
1447	1407		property PageLayout: TsPageLayout;
1448	1408		...
1449	1409		end;
1450	1410		```
1451	1411
1452	1412		> 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.
1453	1413
1454	1414		- Orientation defines the orientation of the printed paper, either portrait or landscape.
1455	1415
1456	1416		- Page width and page height refer to the standard orientation of the paper, usually portrait orientation.
1457	1417
1458	1418		- Left, top, right and bottom margins are self-explanatory and are given in millimeters.
1459	1419
1460	1420		- 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.
1461	1421
1462	1422		- 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.
1463	1423
1464	1424		- 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`.
1465	1425
1466	1426		- 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:
1467	1427
1468	1428		``` pascal
1469	1429		MyWorksheet.PageLayout.Options := MyWorksheet.PageLayout.Options + [poFitPages];
1470	1430		MyWorksheet.PageLayout.FitWidthToPages := 1; // all columns on one page width
1471	1431		MyWorksheet.PageLayout.FitHeightToPages := 0; // use as many pages as needed
1472	1432		```
1473	1433
1474	1434		- 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.
1475	1435
1476	1436		- 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.
1477	1437
1478	1438		- Copies specifies how often the worksheet will be printed.
1479	1439
1480	1440		- 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.
1481	1441
1482	1442		#### Headers and footers
1483	1443
1484	1444		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:
1485	1445
1486	1446		- `&L`: begins the left-aligned section of a header or a footer text definition
1487	1447		- `&C`: begins the centered section of a header or a footer text definition
1488	1448		- `&R`: begins the right-aligned section of a header or a footer text definition
1489	1449		- `&P`: page number
1490	1450		- `&N`: page count
1491	1451		- `&D`: current date of printing
1492	1452		- `&T`: current time of printing
1493	1453		- `&A`: worksheet name
1494	1454		- `&F`: file name without path
1495	1455		- `&P`: file path without file name
1496	1456		- `&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.
1497	1457		- `&B`: bold on/off
1498	1458		- `&I`: italic on/off
1499	1459		- `&U`: underlining on/off
1500	1460		- `&E`: double-underlining on/off
1501	1461		- `&S`: strike-out on/off
1502	1462		- `&H`: shadow on/off
1503	1463		- `&O`: outline on/off
1504	1464		- `&X`: superscript on/off
1505	1465		- `&Y`: subscript on/off
1506	1466		- `&"font"`: begin using of the font with the specified name, e.g. `&"Arial"`
1507	1467		- `&number`: begin using of the specified font size (in points), e.g. `&16`
1508	1468		- `&Krrggbb`: switch to the font color specified to the binary value of the specified color, e.g. use `&KFF0000` for red.
1509	1469
1510	1470		The arrays `Headers[]`/`Footers[]` provide space for usage of three different headers or footers:
1511	1471
1512	1472		- `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.
1513	1473		- `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`.
1514	1474		- `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`.
1515	1475
1516	1476		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:
1517	1477
1518	1478		``` pascal
1519	1479		MyWorksheet.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D &T'; // centered "date time" on all pages as header
1520	1480		MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_ODD] := '&RPage &P of &N'; // right-aligned "Page .. of ..." on odd pages as footer
1521	1481		MyWorksheet.PageLayout.Footers[HEADER_FOOTER_INDEX_EVEN] := '&LPage &P of &N'; // dto, but left-aligned on even pages
1522	1482		```
1523	1483
1524	1484		### Loading and saving
1525	1485
1526	1486		#### Adding new file formats
1527	1487
1528	1488		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.
1529	1489
1530	1490		- 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.
1531	1491		- Register the new reader/writer by calling the function `RegisterSpreadFileFormat` in the `initialization` section of this unit (implemented in unit fpsRegFileFormats):
1532	1492
1533	1493		``` pascal
1534	1494		function RegisterSpreadFormat(AFormat: TsSpreadsheetFormat; AReaderClass: TsSpreadReaderClass; AWriterClass: TsSpreadWriterClass;
1535	1495		AFormatName, ATechnicalName: String; const AFileExtensions: array of String): TsSpreadFormatID;
1536	1496		```
1537	1497
1538	1498		- `AFormat` must have the value `sfUser` to register an external file format.
1539	1499		- `AReaderClass` is the class of the reader (or nil, if reading functionality is not implemented).
1540	1500		- `AWriterClass` is the class of the writer (or nil, if writing functionality is not implemented).
1541	1501		- `AFormatName` defines the name of the format as used for example in the filter list of file-open dialogs.
1542	1502		- `ATechnicalName` defines a shorter format name.
1543	1503		- `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`.
1544	1504		- 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.
1545	1505		- 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.
1546	1506
1547	1507		#### Stream selection
1548	1508
1549	1509		Workbooks are loaded and saved by means of the `ReadFromFile` and `WriteToFile` methods, respectively (or by their stream counterparts, `ReadFromStream` and `WriteToStream`).
1550	1510
1551	1511		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.
1552	1512
1553	1513		- 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.
1554	1514		- 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.
1555	1515		- If both options are set then `boBufStream` is ignored.
1556	1516		- In practice, however, the effect of the selected streams is not very large if memory is to be saved.
1557	1517
1558	1518		#### Virtual mode
1559	1519
1560	1520		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.
1561	1521
1562	1522		These are the steps required to use this mode:
1563	1523
1564	1524		- Activate virtual mode by adding the option `boVirtualMode` to the `Options` of the workbook.
1565	1525		- Tell the spreadsheet writer how many rows and columns are to be written. The corresponding worksheet properties are `VirtualRowCount` and `VirtualColCount`.
1566	1526		- 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.
1567	1527		- Call the `WriteToFile` method of the workbook.
1568	1528
1569	1529		Virtual mode also works for reading spreadsheet files.
1570	1530
1571	1531		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.
1572	1532
1573	1533		### Dataset export
1574	1534
1575	1535		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).
1576	1536
1577	1537		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.
1578	1538
1579	1539		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.
1580	1540
1581	1541		A simple example of how this works:
1582	1542
1583	1543		``` pascal
1584	1544		uses
1585	1545		...
1586	1546		fpsexport
1587	1547		...
1588	1548		var
1589	1549		Exp: TFPSExport;
1590	1550		ExpSettings: TFPSExportFormatSettings;
1591	1551		TheDate: TDateTime;
1592	1552		begin
1593	1553		FDataset.First; //assume we have a dataset called FDataset
1594	1554		Exp := TFPSExport.Create(nil);
1595	1555		ExpSettings := TFPSExportFormatSettings.Create(true);
1596	1556		try
1597	1557		ExpSettings.ExportFormat := efXLS; // choose file format
1598	1558		ExpSettings.HeaderRow := true; // include header row with field names
1599	1559		Exp.FormatSettings := ExpSettings; // apply settings to export object
1600	1560		Exp.Dataset:=FDataset; // specify source
1601	1561		Exp.FileName := 'c:\temp\datadump.xls';
1602	1562		Exp.Execute; // run the export
1603	1563		finally
1604	1564		Exp.Free;
1605	1565		ExpSettings.Free;
1606	1566		end;
1607	1567		```
1608	1568
1609	1569		## Visual controls for FPSpreadsheet
1610	1570
1611	1571		The package laz_fpspreadsheet_visual implements a series of controls which simiplify creation of visual GUI applications:
1612	1572
1613	1573		- TsWorkwookSource links the controls to a workbook and notifies the controls of changes in the workbook.
1614	1574		- [[TsWorksheetGrid]] implements a grid control with editing and formatting capabilities; it can be applied in a similar way to TStringGrid.
1615	1575		- TsWorkbookTabControl provides a tab sheet for each worksheet of the workbook. It is the ideal container for a [[TsWorksheetGrid]].
1616	1576		- TsCellEdit corresponds to the editing line in Excel or Open/LibreOffice. Direct editing in the grid, however, is possible as well.
1617	1577		- TsCellIndicator displays the name of the currently selected cell; it can be used for navigation purposes by entering a cell address string.
1618	1578		- TsCellCombobox offers to pick cell properties for a selection of formatting attributes: font name, font size, font color, background color.
1619	1579		- 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.
1620	1580		- 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.
1621	1581		- 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.
1622	1582
1623	1583		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.
1624	1584
1625	1585		## Examples
1626	1586
1627	1587		To create a project which uses the fpspreadsheet library, add the fpspreadsheet_pkg package to it's Lazarus project, or add the base directory of fpspreadsheet to you compiler options if using another IDE.
1628	1588
1629	1589		### Units
1630	1590
1631	1591		The entire FPSpreadsheet package consists of several units. A spreadsheet application typically "uses" the following units:
1632	1592
1633	1593		- fpspreadsheet: implements TsWorkbook and TsWorksheet and the basic file reading/writing methods
1634	1594		- fpstypes: declares most of the data types and constants used throughout the package. Note: in older versions these declarations were contained in fpspreadsheet.pas.
1635	1595		- the unit(s) implementing the reader/writer for a given file format, e.g. xlsbiff8 for binary Excel files. If the application will be able to handle all formats "use" the unit fpsallformats.
1636	1596
1637	1597		The next units are required only occasionally:
1638	1598
1639	1599		- fpsutils: a collection of utility functions that are occasionally needed (e.g. conversion of col/row indexes to Excel-like cell address string).
1640	1600		- fpscell: this unit is required if you use direct cell formatting (e.g. `cell^.BackgroundColor := scYellow`) instead of calling the corresponding worksheet method (`MyWorksheet.WriteBackgroundColor(cell, scYellow)`, in this example).
1641	1601		- fpsnumformat: collects all utility functions related to number formats.
1642	1602
1643	1603		All other units are probably not needed at the application level. In case of the visual spreadsheet controls, the needed units usually are inserted at design-time automatically.
1644	1604
1645	1605		### Excel 5 example
1646	1606
1647	1607		Note: at least with fpspreadsheet from trunk (development version), this example requires (at least) Lazarus avglvltree.pas, lazutf8.pas, asiancodepagefunctions.inc, asiancodepages.inc and lconvencoding.pas (in the \$(LazarusDir)\components\lazutils\ directory)
1648	1608
1649	1609		``` pascal
1650	1610		{
1651	1611		excel5demo.dpr
1652	1612
1653	1613		Demonstrates how to write an Excel 5.x file using the fpspreadsheet library
1654	1614
1655	1615		You can change the output format by changing the OUTPUT_FORMAT constant
1656	1616
1657	1617		AUTHORS: Felipe Monteiro de Carvalho
1658	1618		}
1659	1619		program excel5demo;
1660	1620
1661	1621		{$mode delphi}{$H+}
1662	1622
1663	1623		uses
1664	1624		Classes, SysUtils, fpstypes, fpspreadsheet, fpsallformats, laz_fpspreadsheet;
1665	1625
1666	1626		const
1667	1627		OUTPUT_FORMAT = sfExcel5;
1668	1628
1669	1629		var
1670	1630		MyWorkbook: TsWorkbook;
1671	1631		MyWorksheet: TsWorksheet;
1672	1632		MyFormula: TsRPNFormula;
1673	1633		MyDir: string;
1674	1634		begin
1675	1635		// Initialization
1676	1636		MyDir := ExtractFilePath(ParamStr(0));
1677	1637
1678	1638		// Create the spreadsheet
1679	1639		MyWorkbook := TsWorkbook.Create;
1680	1640		try
1681	1641		MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
1682	1642
1683	1643		// Write some number cells
1684	1644		MyWorksheet.WriteNumber(0, 0, 1.0);
1685	1645		MyWorksheet.WriteNumber(0, 1, 2.0);
1686	1646		MyWorksheet.WriteNumber(0, 2, 3.0);
1687	1647		MyWorksheet.WriteNumber(0, 3, 4.0);
1688	1648
1689	1649		// Write the formula E1 = A1 + B1
1690	1650		MyWorksheet.WriteFormula(0, 4, 'A1+B1');
1691	1651
1692	1652		// Creates a new worksheet
1693	1653		MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');
1694	1654
1695	1655		// Write some string cells
1696	1656		MyWorksheet.WriteText(0, 0, 'First');
1697	1657		MyWorksheet.WriteText(0, 1, 'Second');
1698	1658		MyWorksheet.WriteText(0, 2, 'Third');
1699	1659		MyWorksheet.WriteText(0, 3, 'Fourth');
1700	1660
1701	1661		// Save the spreadsheet to a file
1702	1662		MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
1703	1663		finally
1704	1664		MyWorkbook.Free;
1705	1665		end;
1706	1666		end.
1707	1667		```
1708	1668
1709	1669		### Opening an existing spreadsheet
1710	1670
1711	1671		To open a spreadsheet while specifying a particular format to use use `ReadFromFile` with two parameters:
1712	1672
1713	1673		``` pascal
1714	1674		MyWorkbook.ReadFromFile(AFileName, sfExcel5);
1715	1675		```
1716	1676
1717	1677		It is also possible to call `ReadFromFile` with only one parameter, the filename. Then the workbook will use the extension to auto-detect the file format. In case of the ambiguous extension .xls (Excel 2-8) it will simply try various possibilities until one works. Although typical fingerprint byte patterns are checked now it is still possible that an exception will be raised for each incorrect format if run from the IDE at designtime; this does not occur at runtime.
1718	1678
1719	1679		``` pascal
1720	1680		MyWorkbook.ReadFromFile(AFileName);
1721	1681		```
1722	1682
1723	1683		### Writing a spreadsheet to file based on extension
1724	1684
1725	1685		Similar to the `ReadFromFile` routine, there is also a `WriteToFile` procedure to determine the spreadsheet's type based on the filename suffix. It uses the `GetFormatFromFileName` routine in the previous section's code, so the actual code is simple. However, it will always write files with a given extension using the latest format that uses that extension (e.g. Excel .xls files will be written as `sfExcel8`), so if you want to write them in an earlier format, you have to use the base routine.
1726	1686
1727	1687		As above, this code patches the fpspreadsheet.pas unit.
1728	1688
1729	1689		``` pascal
1730	1690		procedure TsWorkbook.WriteToFile(const AFileName: string; const AOverwriteExisting: Boolean = False); overload;
1731	1691		var SheetType: TsSpreadsheetFormat;
1732	1692		begin
1733	1693		if getFormatFromFileName(AFileName, SheetType) then
1734	1694		WriteToFile(AFileName, SheetType, AOverwriteExisting)
1735	1695		else raise Exception.Create(Format(
1736	1696		'[TsWorkbook.WriteToFile] Attempted to save a spreadsheet by extension, but the extension %s is invalid.', [ExtractFileExt(AFileName)]));
1737	1697		end;
1738	1698		```
1739	1699
1740	1700		### Iterating through all Worksheets
1741	1701
1742	1702		``` pascal
1743	1703		var
1744	1704		MyWorkbook: TsWorkbook;
1745	1705		MyWorksheet: TsWorksheet;
1746	1706		i: Integer;
1747	1707		begin
1748	1708		// Here load MyWorkbook from a file or build it
1749	1709
1750	1710		for i := 0 to MyWorkbook.GetWorksheetCount() - 1 do
1751	1711		begin
1752	1712		MyWorksheet := MyWorkbook.GetWorksheetByIndex(i);
1753	1713		// Do something with MyWorksheet
1754	1714		end;
1755	1715		```
1756	1716
1757	1717		### Iteration through cells
1758	1718
1759	1719		The first idea is to use a simple for-to loop:
1760	1720
1761	1721		``` pascal
1762	1722		var
1763	1723		MyWorksheet: TsWorksheet;
1764	1724		col, row: Cardinal;
1765	1725		cell: PCell;
1766	1726		begin
1767	1727		for row:=0 to MyWorksheet.GetLastRowIndex do
1768	1728		for col := 0 to MyWorksheet.GetLastColIndex do
1769	1729		begin
1770	1730		cell := MyWorksheet.FindCell(row, col);
1771	1731		WriteLn(MyWorksheet.ReadAsUTF8Text(cell));
1772	1732		end;
1773	1733		end;
1774	1734		```
1775	1735
1776	1736		`FindCell` initiates a search for a cell independently of the previously found cell. Cells, however, are are organized interally in a sorted tree structure, and each cell "knows" its previous and next neighbors. Moreover, `FindCell` wastes time on searching for non-existing cells in case of sparsely-occupied worksheets. In general, it is more efficient to use the for-in syntax which takes advantage of the internal tree structure by means of special enumerators. Note that there are also dedicated enumerators for searching along rows, columns or in cell ranges:
1777	1737
1778	1738		``` pascal
1779	1739		var
1780	1740		MyWorksheet: TsWorksheet;
1781	1741		cell: PCell;
1782	1742		begin
1783	1743		// Search in all cells
1784	1744		for cell in Myworksheet.Cells do
1785	1745		WriteLn(MyWorksheet.ReadAsText(cell));
1786	1746
1787	1747		// Search in column 0 only
1788	1748		for cell in MyWorksheet.Cells.GetColEnumerator(0) do
1789	1749		WriteLn(MyWorksheet.ReadAsText(cell));
1790	1750
1791	1751		// Search in row 2 only
1792	1752		for cell in MyWorksheet.Cells.GetRowEnumerator(2) do
1793	1753		WriteLn(MyWorksheet.ReadAsText(cell));
1794	1754
1795	1755		// Search in range A1:C2 only (rows 0..1, columns 0..2)
1796	1756		for cell in MyWorksheet.Cells.GetRangeEnumerator(0, 0, 1, 2) do
1797	1757		WriteLn(MyWorksheet.ReadAsText(cell));
1798	1758		end;
1799	1759		```
1800	1760
1801	1761		### Converting a database to a spreadsheet
1802	1762
1803	1763		The easiest solution is to use the DatasetExport component.
1804	1764
1805	1765		If you need to have more control over the process, use something like:
1806	1766
1807	1767		``` pascal
1808	1768		program db5xls;
1809	1769
1810	1770		{$mode delphi}{$H+}
1811	1771
1812	1772		uses
1813	1773		Classes, SysUtils,
1814	1774		// add database units
1815	1775		fpstypes, fpspreadsheet, fpsallformats;
1816	1776
1817	1777		const OUTPUT_FORMAT = sfExcel5;
1818	1778
1819	1779		var
1820	1780		MyWorkbook: TsWorkbook;
1821	1781		MyWorksheet: TsWorksheet;
1822	1782		MyDatabase: TSdfDataset;
1823	1783		MyDir: string;
1824	1784		i, j: Integer;
1825	1785		begin
1826	1786		// Initialization
1827	1787		MyDir := ExtractFilePath(ParamStr(0));
1828	1788
1829	1789		// Open the database
1830	1790		MyDatabase := TSdfDataset.Create;
1831	1791		MyDatabase.Filename := 'test.dat';
1832	1792		// Add table description here
1833	1793		MyDatabase.Active := True;
1834	1794
1835	1795		// Create the spreadsheet
1836	1796		MyWorkbook := TsWorkbook.Create;
1837	1797		MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
1838	1798
1839	1799		// Write the field names
1840	1800		for i := 0 to MyDatabase.Fields.Count - 1 do
1841	1801		MyWorksheet.WriteText(0, i, MyDatabase.Fields[i].FieldName);
1842	1802
1843	1803		// Write all cells to the worksheet
1844	1804		MyDatabase.First;
1845	1805		j := 0;
1846	1806		while not MyDatabase.EOF do
1847	1807		begin
1848	1808		for i := 0 to MyDatabase.Fields.Count - 1 do
1849	1809		MyWorksheet.WriteText(j + 1, i, MyDatabase.Fields[i].AsString);
1850	1810
1851	1811		MyDatabase.Next;
1852	1812		Inc(j);
1853	1813		end;
1854	1814
1855	1815		// Close the database
1856	1816		MyDatabase.Active := False;
1857	1817		MyDatabase.Free;
1858	1818
1859	1819		// Save the spreadsheet to a file
1860	1820		MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
1861	1821		MyWorkbook.Free;
1862	1822		end.
1863	1823		```
1864	1824
1865	1825		### Converting a large database table to a spreadsheet using virtual mode
1866	1826
1867	1827		> Note: The example program in examples\db_import_export shows a demonstration of using virtual mode to export datasets to spreadsheet files.
1868	1828
1869	1829		We want to write a large database table to a spreadsheet file. The first row of the spreadsheet is to show the field names in bold type face and with a gray background.
1870	1830
1871	1831		Normally, FPSpreadsheet would load the entire representation of the spreadsheet into memory, so we'll use virtual mode to minimize memory usage.
1872	1832
1873	1833		``` pascal
1874	1834		type
1875	1835		TDataProvider = class;
1876	1836
1877	1837		var
1878	1838		MyWorkbook: TsWorkbook;
1879	1839		MyWorksheet: TsWorksheet;
1880	1840		MyDatabase: TSdfDataset;
1881	1841		MyDir: string;
1882	1842		MyHeaderTemplateCell: PCell;
1883	1843		DataProvider: TDataProvider;
1884	1844
1885	1845		// Implement TDataProvider here - see below...
1886	1846
1887	1847		begin
1888	1848		// Initialization
1889	1849		MyDir := ExtractFilePath(ParamStr(0));
1890	1850
1891	1851		// Open the database
1892	1852		MyDatabase := TSdfDataset.Create;
1893	1853		try
1894	1854		MyDatabase.Filename := 'test.dat';
1895	1855		// Add table description here
1896	1856		MyDatabase.Active := True;
1897	1857
1898	1858		// Create the spreadsheet
1899	1859		MyWorkbook := TsWorkbook.Create;
1900	1860		try
1901	1861		MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
1902	1862
1903	1863		// Create the template cell for the header line, we want the
1904	1864		// header in bold type-face and gray background color
1905	1865		// The template cell can be anywhere in the workbook, let's just select cell A1.
1906	1866		MyWorksheet.WriteFontStyle(0, 0, [fssBold]);
1907	1867		MyWorksheet.WriteBackgroundColor(0, 0, scGray);
1908	1868		// We'll need this cell again and again, so let's save the pointer to it in a variable
1909	1869		MyHeaderTemplateCell := MyWorksheet.Find(0, 0);
1910	1870
1911	1871		// Enter virtual mode
1912	1872		MyWorkbook.Options := MyWorkbook.Options + [boVirtualMode];
1913	1873
1914	1874		// Define number of columns - we want a column for each field
1915	1875		MyWorksheet.VirtualColCount := MyDatabase.FieldCount;
1916	1876
1917	1877		// Define number of rows - we want every record, plus 1 row for the title row
1918	1878		MyWorksheet.VirtualRowCount := MyDatabase.RecordCount + 1;
1919	1879
1920	1880		// Link the event handler which passes data from database to spreadsheet writer
1921	1881		MyWorksheet.OnWriteCellData := @DataProvider.WriteCellData;
1922	1882
1923	1883		// Write all cells to an Excel8 file
1924	1884		// The data to be written are specified in the OnWriteCellData event handler.
1925	1885		MyWorkbook.WriteToFile(MyDir + 'test.xls', sfExcel8);
1926	1886
1927	1887		finally
1928	1888		// Clean-up
1929	1889		MyWorkbook.Free;
1930	1890		end;
1931	1891
1932	1892		finally
1933	1893		// Close the database & clean-up
1934	1894		MyDatabase.Active := False;
1935	1895		MyDatabase.Free;
1936	1896		end;
1937	1897
1938	1898		end.
1939	1899		```
1940	1900
1941	1901		What is left is to write the event handler for `OnWriteCellData`. For the command-line program above we setup a particular data provider class (in a gui program the event handler can also be a method of any form):
1942	1902
1943	1903		``` pascal
1944	1904		type
1945	1905		TDataProvider = class
1946	1906		procedure WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
1947	1907		end;
1948	1908
1949	1909		procedure TDataProvider.WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; AValue: variant; var: AStyleCell: PCell);
1950	1910		begin
1951	1911		// Let's handle the header row first:
1952	1912		if ARow = 0 then begin
1953	1913		// The value to be written to the spreadsheet is the field name.
1954	1914		AValue := MyDatabase.Fields[ACol].FieldName;
1955	1915		// Formatting is defined in the HeaderTemplateCell.
1956	1916		AStyleCell := MyHeaderTemplateCell;
1957	1917		// Move to first record
1958	1918		MyDatabase.First;
1959	1919		end else begin
1960	1920		// The value to be written to the spreadsheet is the record value in the field corresponding to the column.
1961	1921		// No special requirements on formatting --> leave AStyleCell at its default (nil).
1962	1922		AValue := MyDatabase.Fields[ACol].AsVariant;
1963	1923		// Advance database cursor if last field of record has been written
1964	1924		if ACol = MyDatabase.FieldCount-1 then MyDatabase.Next;
1965	1925		end;
1966	1926		end;
1967	1927		```
1968	1928
1969	1929		### Converting between two spreadsheet formats
1970	1930
1971	1931		``` pascal
1972	1932		program ods2xls;
1973	1933
1974	1934		{$mode delphi}{$H+}
1975	1935
1976	1936		uses
1977	1937		Classes, SysUtils,
1978	1938		fpstypes, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
1979	1939
1980	1940		const
1981	1941		INPUT_FORMAT = sfOpenDocument;
1982	1942		OUTPUT_FORMAT = sfExcel8;
1983	1943
1984	1944		var
1985	1945		MyWorkbook: TsWorkbook;
1986	1946		MyDir: string;
1987	1947		begin
1988	1948		// Initialization
1989	1949		MyDir := ExtractFilePath(ParamStr(0));
1990	1950
1991	1951		// Convert the spreadsheet
1992	1952		MyWorkbook := TsWorkbook.Create;
1993	1953		try
1994	1954		MyWorkbook.ReadFromFile(MyDir + 'test.ods', INPUT_FORMAT);
1995	1955		MyWorkbook.WriteToFile(MyDir + 'test.xls', OUTPUT_FORMAT);
1996	1956		finally
1997	1957		MyWorkbook.Free;
1998	1958		end;
1999	1959		end.
2000	1960		```
2001	1961
2002	1962		### Reading and writing of CSV files
2003	1963
2004	1964		CSV files (CSV = comma-separated values) are plain text files without metadata. Therefore, additional information for correct reading of writing from/to a worksheet is required. The global record `CSVParams` makes available fundamental settings for this purpose:
2005	1965
2006	1966		``` pascal
2007	1967		type
2008	1968		CSVParams: TsCSVParams = record // W = writing, R = reading, RW = reading/writing
2009	1969		SheetIndex: Integer; // W: Index of the sheet to be written
2010	1970		LineEnding: TsCSVLineEnding; // W: Specification for line ending to be written
2011	1971		Delimiter: Char; // RW: Column delimiter
2012	1972		QuoteChar: Char; // RW: Character for quoting texts
2013	1973		Encoding: String; // RW: Encoding of file
2014	1974		DetectContentType: Boolean; // R: try to convert strings to content types
2015	1975		NumberFormat: String; // W: if empty write numbers like in sheet, otherwise use this format
2016	1976		AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
2017	1977		TrueText: String; // RW: String for boolean TRUE
2018	1978		FalseText: String; // RW: String for boolean FALSE
2019	1979		FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
2020	1980		end;
2021	1981		```
2022	1982
2023	1983		This record contains fields which are evaluated for reading only, writing only, or for both - see the attached comments.
2024	1984
2025	1985		A common situation is to read a file using a number decimal separator which is different from the system's decimal separator: Suppose you are on a European system in which the decimal separator is a comma, but the csv file to be read originates from a machine which uses a decimal point. And suppose also, that the file contains tab characters as column separator instead of the default comma. In this case, simply set the `CSVParams.FormatSettings.DecimalSeparator` to `'.'`, and the `CSVParams.Delimiter` to `#9` (TAB character) before reading the file:
2026	1986
2027	1987		``` pascal
2028	1988		uses
2029	1989		fpstypes, fpspreadsheet, fpscsv;
2030	1990		var
2031	1991		MyWorkbook: TsWorkbook;
2032	1992		begin
2033	1993		CSVParams.FormatSettings.DecimalSeparator := '.';
2034	1994		CSVParams.Delimiter := #9;
2035	1995		MyWorkbook := TsWorkbook.Create;
2036	1996		try
2037	1997		MyWorkbook.ReadFromFile('machine-data.csv', sfCSV);
2038	1998		finally
2039	1999		MyWorkbook.Free;
2040	2000		end;
2041	2001		end;
2042	2002		```
2043	2003
2044	2004		> Note: If you want to give the user the possibility to interactively modify the `CSVParams` record have a look at the unit scsvparamsform.pas of the spready demo which provides a ready-to-use dialog.
2045	2005
2046	2006		### Sample projects in the fpspreadsheet installation folder
2047	2007
2048	2008		A bunch of sample projects accompanies the FPSpreadsheet installation. They can be found in the folder "examples". Here is a brief description of these sample projects
2049	2009
2050	2010		- db_import_export is an example showing how to export a large database table to a spreadsheet file using virtual mode or TFPSExport. It also shows importing the spreadsheet into a database using virtual mode.
2051	2011		- fpsspeedtest compares the effect of file format and various reading/writing parameters on the speed of writing and reading very large spreadsheet files. Again, please run the write test first which create the test files used for the read test.
2052	2012		- Folder read_write:
2053	2013		- excel2demo contains command-line programs for writing and reading Excel 2.x xls files. Please run the write demo before the read demo so the required spreadsheet file is generated.
2054	2014		- excel5demo, like excel2demo, but for Excel 5 xls files.
2055	2015		- excel8demo, like excel2demo, but for Excel 97-2003 xls files.
2056	2016		- csvdemo, like excel2demo, but for CSV files.
2057	2017		- htmldemo, like excel2demo, but for HTML file (currently writing only).
2058	2018		- ooxmldemo, like excel2demo, but for the new Excel xlsx files.
2059	2019		- opendocdemo, like excel2demo, but for OpenOffice/LibreOffice ods files
2060	2020		- wikitabledemo, like excel2demo, but for wiki table files. Note that the write example currently writes a format that the read example cannot understand.
2061	2021		- other: simple commandline programs showing various aspects of the fpspreadsheet package. Have a look at readme.txt for more details.
2062	2022		- Folder visual:
2063	2023		- fpschart shows the application of the [[TsWorksheetChartSource]] and TsWorkbookChartSource and the interaction with the [[TAChart]] plotting package.
2064	2024		- fpsctrls and visual/fpsctrls_no_install create a GUI spreadsheet application with a minimum amount of written code; the latter demo is good for testing because it does not require installation of the FPSpreadsheet packages. Step-by-step instructions on how fpsctrls is made can be found in the [[FPSpreadsheet tutorial: Writing a mini spreadsheet application\|FSpreadsheet tutorial]].
2065	2025		- fpsgrid and visual/fpsgrid_no_install show the basic application of the [[TsWorksheetGrid]] without using the TsWorkbookSource component; the latter demo does not require installation of any FPSpreadsheet package.
2066	2026		- spready: an extended application of the [[TsWorksheetGrid]] showing spreadsheet files with formatting, editing of cells, etc. Note that this demo can be simplified by taking advantage of the FPSpreadsheet controls.
2067	2027		- wikitablemaker is a small application for creation of code to be used for tables on wiki pages. Type the data into a [[TsWorksheetGrid]] (or load an existing spreadsheet file), go to page "Code" to see the generated wiki code, click "Copy to clipboard" and paste the code into the wiki page.
2068	2028
2069	2029		## Download
2070	2030
2071	2031		### Subversion
2072	2032
2073	2033		You can download FPSpreadsheet using the subversion software and the following command line:
2074	2034
2075	2035		``` bash
2076	2036		svn checkout svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet fpspreadsheet
2077	2037		```
2078	2038
2079	2039		#### SVN change log
2080	2040
2081	2041		- "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`).
2082	2042		- Searching for cells with specified content in worksheet or workbook.
2083	2043		- Support for reading and writing of HTML format
2084	2044		- Support for writing of the ExcelXML format (Excel XP and 2003)
2085	2045		- Ability to use user-provided file reader/writer classes to extend FPSpreadsheet to new file formats.
2086	2046		- Readers and writers now support all the line styles of Excel8 and OOXML.
2087	2047		- xls, xlsx and ods readers/writers now support the active worksheet and selected cell.
2088	2048		- Ability to write to/read from the system's clipboard for copy & paste using the visual controls.
2089	2049		- Support for print ranges and repeated header rows and columns in the Office applications.
2090	2050		- Support for embedded images (currently only writing to xlsx and ods, no reading).
2091	2051		- Improved compatibility of TsWorksheetGrid with TStringGrid (`Cells[Col,Row]` property). Standalone application as an advanced StringGrid replacement.
2092	2052		- 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.
2093	2053		- Support of several units for specification of column widths and row heights.
2094	2054		- The library now supports localization using po files. Translations are welcome.
2095	2055
2096	2056		#### Incompatible changes
2097	2057
2098	2058		- 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.
2099	2059		- 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.
2100	2060		- The public properties of `TsWorksheetGrid` using a `TRect` as parameter were modified to use the Left, Top, Right, Bottom values separately.
2101	2061		- 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.
2102	2062		- Most of the predefined color constants were marked as deprecated; only the basic EGA colors will remain.
2103	2063
2104	2064		#### To do list
2105	2065
2106	2066		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!
2107	2067
2108	2068		- Find out why BIFF2 files are corrupt when saved with frozen rows/cols activated.
2109	2069		- Find out how to write more than 21 number formats to a BIFF2 file (Office 97 can do this).
2110	2070		- Add row and column formats
2111	2071		- Add reading support for wikitable (Mediawiki) files
2112	2072		- 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).
2113	2073		- Improve color support: due to palette support colors may change from file to file currently.
2114	2074		- Add embedded images.
2115	2075		- Fix writing of cell comments to BIFF8 files.
2116	2076
2117	2077		Long-term:
2118	2078
2119	2079		- 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.
2120	2080		- Store formatting in a format list of the workbook, not in the cell, to reduce memory usage.
2121	2081		- Use BIFF8 file-wide string storage instead of storing strings in cells (should reduce memory usage in case of many duplicate strings)
2122	2082		- Redo ooxml and ods readers based upon SAX/TXmlReader instead of DOM to reduce memory usage of large files.
2123	2083		- 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).
2124	2084
2125	2085		### Stable releases
2126	2086
2127	2087		You can find all releases of FPSpreadsheet on [sourceforge](https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/).
2128	2088
2129	2089		#### Version 1.6
2130	2090
2131	2091		The latest stable release is version 1.6.2.
2132	2092
2133	2093		##### Change log (with respect to v1.4.x)
2134	2094
2135	2095		- 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.
2136	2096		- 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)
2137	2097		- 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)
2138	2098		- Comments in cells
2139	2099		- Background fill patterns
2140	2100		- Hyperlinks
2141	2101		- Enumerators for worksheet's internal AVLTrees for faster iteration using a for-in loop.
2142	2102		- Formatting of numbers as fractions.
2143	2103		- Improved number format parser for better recognition of Excel-like number formats.
2144	2104		- Page layout (page margins, headers, footer, used for only when printing in the Office applications - no direct print support in fpspreadsheet!)
2145	2105		- Improved color management: no more palettes, but direct rgb colors. More pre-defined colors.
2146	2106		- A snapshot of the wiki documentation is added to the library as chm help file.
2147	2107
2148	2108		##### Incompatible changes
2149	2109
2150	2110		- 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.
2151	2111		- 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.
2152	2112		- 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])`.
2153	2113		- 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.
2154	2114		- 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.
2155	2115		- 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.
2156	2116
2157	2117		#### Version 1.4
2158	2118
2159	2119		##### Change log (with respect to v1.2.x)
2160	2120
2161	2121		- 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`).
2162	2122		- Shared formulas (reading for sfExcel5, sfExcel8, sfOOXML; writing for sfExcel2, sfExcel5, sfExcel8).
2163	2123		- Significant speed-up of writing of large spreadsheets for the xml-based formats (ods and xlsx), speed up for biff2; speedtest demo program
2164	2124		- VirtualMode allowing to read and write very large spreadsheet files without loading entire document representation into memory. Formatting of cells in VirtualMode.
2165	2125		- Demo program for database export using virtual mode and TFPSExport.
2166	2126		- Added db export unit allowing programmatic exporting datasets using TFPSExport. Similar export units are e.g. [[fpdbfexport]], [[fpXMLXSDExport]].
2167	2127		- Reader for xlsx files, now fully supporting the same features as the other readers.
2168	2128		- Reader/writer for CSV files based on [[CsvDocument]].
2169	2129		- 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
2170	2130		- Insertion and deletion of rows and columns into a worksheet containing data.
2171	2131		- Implementation of sorting of a worksheet.
2172	2132		- Support of diagonal "border" lines
2173	2133		- Logging of non-fatal error messages during reading/writing (TsWorksheet.ErrorMsg)
2174	2134		- Merged cells
2175	2135		- Registration of currency strings for automatic conversion of strings to currency values
2176	2136		- 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]].
2177	2137		- Overflow cells in TsWorksheetGrid: label cells with text longer than the cell width extend into the neighboring cell(s).
2178	2138
2179	2139		##### Incompatible changes
2180	2140
2181	2141		- 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).
2182	2142		- 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.
2183	2143		- With full support of string formulas some features related to RPN formulas were removed:
2184	2144		- The field `RPNFormulaResult` of TCell was dropped, as well as the element `cctRPNFormula` in the `TsContentType` set.
2185	2145		- Sheet function identifiers were removed from the `TsFormulaElement` set, which was truncated after `fekParen`.
2186	2146		- 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`.
2187	2147		- 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).
2188	2148		- Code related to RPN formulas was moved to a separate unit, `fpsRPN`. Add this unit to the uses clause if you need RPN features.
2189	2149
2190	2150		#### Wiki documentation of old releases
2191	2151
2192	2152		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:
2193	2153
2194	2154		- Version 1.4: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299](http://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=85299)
2195	2155		- Version 1.2 and 1.2.1: [wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375](http://wiki.lazarus.freepascal.org/index.php?title=FPSpreadsheet&oldid=81375)
2196	2156
2197	2157		## Instalación
2198	2158
2199	2159		- Si únicamente necesitas componentes no gráficos: en Lazarus: Paquete -\> Abrir Archivo de Paquete (.lpk)...-\> Seleccionar laz_fpspreadsheet.lpk y hacer Click en \[Compilar\]. Con esto ahora el paquete es reconocible por Lazarus (y debería por ejemplo mostrarse en Paquetes/Enlaces de Paquete. Ya se puede añadir por tanto una dependencia en `laz_fpspreadsheet` dentro de tus opciones de proyecto y `fpspreadsheet` a la claúsula uses de las units del proyecto en las que se necesita utilizar.
2200	2160		- 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.
2201	2161		- 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.
2202	2162		- FPSpreadsheet is developed with the latest stable fpc version (currently fpc 2.6.4). We only occasionally check older versions.
2203	2163		- 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.
2204	2164
2205	2165		### Compiler options
2206	2166
2207	2167		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:
2208	2168
2209	2169		- `FPS_DONT_USE_CLOCALE`: In Unix systems, the unit `clocale` is automatically added to the uses clause of fpspreadsheet.pas. This unit sets up localization settings needed for locale-dependent number and date/time formats. However, this adds a dependence on the C library to the package [1](http://lazarus-ccr.sourceforge.net/docs/rtl/clocale/index.html). If this is not wanted, define `FPS_DONT_USE_CLOCALE`.
2210	2170		- `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.
2211	2171		- `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.
2212	2172
2213	2173		All these defines are collected in the include file fps.inc.
2214	2174
2215	2175		## Soporte y reporte de errores
2216	2176
2217	2177		El lugar reconmendado para discusión y soporte sobre FPSpreadsheet es consultado en el [foro de Lazarus](http://forum.lazarus.freepascal.org/index.php?board=49.0).
2218	2178
2219	2179		Los reportes de error deben enviarse a [Seguimiento de errores de Lazarus/Free Pascal](http://bugs.freepascal.org/); por favor, especificar el proyecto "Lazarus-CCR".
2220	2180
2221	2181		## Current Progress
2222	2182
2223	2183		### Progress by supported cell content
2224	2184
2225		-	~~<thead>~~
2226		-
2227		-	~~sheets</th>~~
2228		-
2229		-	~~support</th>~~
2230		-
2231		-	~~support</th>~~
2232		-
2233		-	~~Formula</th>~~
2234		-
2235		-	~~Formula</th>~~
2236		-
2237		-	~~</thead>~~
2238		-	~~<tbody>~~
2239		-
2240		-	~~</tbody>~~
	2185	+	\| Format \| Multiple · sheets \| Unicode \| Reader · support \| Writer · support \| Text \| Number \| String · Formula \| RPN · Formula \| Date/Time \| Comments \| Hyperlinks \| Images +++ \|
	2186	+	\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|
	2187	+	\| CSV files \| No \| Yes + \| Working ++ \| Working ++ \| Working ++ \| Working ++ \| N/A \| N/A \| Working ++ \| N/A \| N/A \| N/A \|
	2188	+	\| Excel 2.x \| No \| No * \| Working \| Working \| Working \| Working \| Working \| Working * \| Working \| Working \| N/A \| N/A \|
	2189	+	\| Excel 5.0 (Excel 5.0 and 95) \| Yes \| No * \| Working \| Working \| Working \| Working \| Working \| Working * \| Working \| Working \| N/A \| N/A \|
	2190	+	\| Excel 8.0 (Excel 97- 2003) \| Yes \| Yes \| Working \| Working \| Working \| Working \| Working \| Working * \| Working \| Reading only \| Working \| Not working \|
	2191	+	\| Microsoft OOXML \| Yes \| Yes \| Working \| Working \| Working \| Working \| Working * \| Working \| Working \| Working \| Working \| Writing only \|
	2192	+	\| OpenDocument \| Yes \| Yes \| Working \| Working \| Working \| Working \| Working * \| Working \| Working \| Working \| Working \| Working \|
	2193	+	\| HTML \| No \| Yes \| Working ++ \| Working ++ \| Working ++ \| Working ++ \| N/A \| N/A \| Working ++ \| N/A \| Working \| Not working \|
	2194	+	\| Wikitable files (Mediawiki) \| No \| Yes \| planned \| Working ++ \| Working ++ \| Working ++ \| N/A \| N/A \| Working ++ \| N/A \| N/A \| Not working \|
2241	2195
2242	2196		(+) Depends on file.\
2243	2197		(++) No "true" number format support because the file does not containg number formatting information. But the number format currently used in the spreadsheet understood.\
2244	2198		(+++) Only very basic image support: no transformations, no cropping, no image manipulation.\
2245	2199		(*) 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.\
2246	2200		(**) Some cell could be returned blank due to missing or non ready implemented number and text formats.\
2247	2201		(***) This is the format in which the formulas are written to file (determined by design of the file format).\
2248	2202		(****) Writing of all formats is supported. Some rare custom formats, however, may not be recognized correctly. BIFF2 supports only built-in formats by design.
2249	2203
2250	2204		### Progreso de las opciones de formateado
2251	2205
2252	2206		Quite some formatting options are available:
2253	2207
2254		-	~~<thead>~~
2255		-
2256		-	~~de texto</th>~~
2257		-
2258		-	~~de texto</th>~~
2259		-
2260		-	~~enriquecido</th>~~
2261		-
2262		-	~~de color</th>~~
2263		-
2264		-	~~wrap</th>~~
2265		-
2266		-	~~Col&Row</th>~~
2267		-
2268		-	~~numeros</th>~~
2269		-
2270		-	~~celdas</th>~~
2271		-
2272		-	~~página</th>~~
2273		-
2274		-	~~rangos</th>~~
2275		-
2276		-	~~images</th>~~
2277		-
2278		-	~~</thead>~~
2279		-	~~<tbody>~~
2280		-
2281		-	~~</tbody>~~
	2208	+	\| Formato \| Alineación · de texto \| Rotación · de texto \| Font \| Texto · enriquecido \| Borde \| Soporte · de color \| Fondo \| Word · wrap \| Tamaño · Col&Row \| Formato · numeros \| Unir · celdas \| Distribución · página \| Imprimirt · rangos \| Encabezado/pie página · images \|
	2209	+	\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|---\|
	2210	+	\| 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 \|
	2211	+	\| Excel 2.x \| Funciona * \| N/A \| Funciona \| N/A \| Funciona \| Funciona \| Funciona** \| N/A \| Funciona \| Funciona \| N/A \| Funciona \| N/A \| N/A \|
	2212	+	\| Excel 5.0 (Excel 5.0 and 95) \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| N/A \| Funciona \| Funciona \| N/A \|
	2213	+	\| Excel 8.0 (Excel 97 - XP) \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| No funciona \|
	2214	+	\| Microsoft OOXML (xlsx) \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Writing only \|
	2215	+	\| OpenDocument \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona*** \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \|
	2216	+	\| HTML \| Funciona \| Errores \| Funciona \| Funciona \| Errores \| Funciona \| Funciona \| Writing only \| Writing only \| N/A \| Funciona \| N/A \| N/A \| No funciona \|
	2217	+	\| Wikitable (Mediawiki) \| Funciona \| N/A \| Funciona \| No funciona \| Funciona \| Funciona \| \| Funciona \| Funciona \| N/A \| Funciona \| N/A \| N/A \| No funciona \|
2282	2218
2283	2219		(N/A) Característica no disponible intrínsecamente para este formato.\
2284	2220		(*) BIFF2 soporta solamente alineación horizontal de texto, la alineación vertical se ignora.\
2285	2221		(**) BIFF2 no soporta color de fondo; en su lugar se utiliza un fondo punteado en blanco y negro.\
2286	2222		(***) OpenDocument soporta únicamente fondos uniformes; en su lugar se escribe un color de relleno interpolado entre foreground y background.\
2287	2223		(****) Únicamente color de fondo uniforme, sin estilos de relleno.
2288	2224
2289	2225		### Progreso del las opciones del interface de usuario para workbook/worksheet
2290	2226
2291	2227		Se han añadido algunas opciones al interface de contenidos de fichero de `TsWorksheetGrid`:
2292	2228
2293	2229		\| Formato \| Ocultar líneas de la grid \| Ocultar cabeceras \| Inmovilizar paneles \| sheet/cell activa \| Modo BiDi \|
2294	2230		\|----\|----\|----\|----\|----\|----\|
2295	2231		\| Excel 2.x \| Funciona \| Funciona \| No Funciona \| N/A \| N/A \|
2296	2232		\| Excel 5.0 (Excel 5.0 and 95) \| Funciona \| Funciona \| Funciona \| Funciona \| N/A \|
2297	2233		\| Excel 8.0 (Excel 97 - XP) \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \|
2298	2234		\| Microsoft OOXML \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \|
2299	2235		\| OpenDocument \| Funciona \| Funciona \| Funciona \| Funciona \| Funciona \|
2300	2236		\| HTML \| Solo escritura \| Solo escritura \| N/A \| N/A \| No Funciona \|
2301	2237
2302	2238		## Log de cambios
2303	2239
2304	2240		Changes in the development version that have not yet been released are already documented on this page.
2305	2241
2306	2242		- 13 April 2016:\
2307	2243		Version 1.6.2 released, basado en svn revision 4619. Corrige el desbordamiento de enteros en las fórmulas ([Issue \#29999](https://bugs.freepascal.org/view.php?id=29999)).
2308	2244		- 28 June 2015:\
2309	2245		Version 1.6 released, based on svn release 4106. Consumo de memoria mejorado, comentarios, hiperenlaces, patrones de relleno de fondo, enumerador de celda, fraction number format, page layout, modified color management, versión fuera de línea de la wiki.
2310	2246		- 13 March 2015:\
2311	2247		Version 1.4.3 released. Corrige el cálculo de fórmula en una grid del worksheet.
2312	2248		- 31 January 2015:\
2313	2249		Version 1.4.2 released. Corrige la lectura incompleta de ficheros ods con celdas repetidas ([Issue \#0027389](https://bugs.freepascal.org/view.php?id=0027389))
2314	2250		- 26 January 2015:\
2315	2251		Version 1.4.1 released. A bugfix release para 1.4 que corrige el streaming incorrecto de TsWorksheetGrid y los errores de lectura para lectores biff5 y biff8.
2316	2252		- 31 December 2014:\
2317	2253		Version 1.4 released (based on subversion revision 3856 with full formula support, lector de xlsx, lectura y escritura de csv, wikitables writer, speed enhancement, virtual mode, db export component, row and column insertion/deletion, ordenación, fusión de celdas, controles visuales en adición a la grid de (mejorado) fpspreadsheet.
2318	2254		- 3 September 2014:\
2319	2255		Version 1.2.1 released. This is a bugfix release for 1.2 that fixes a bug in spreadsheetGrid ([Issue \#26521](https://bugs.freepascal.org/view.php?id=26521))
2320	2256		- 26 June 2014:\
2321	2257		Version 1.2 released (based on subversion revision 3241) with fpsgrid improvements, soporte en la celda para fecha/hora para .xls, .ods, vasta mejora en el soporte de formateado de celda, soporte mejorado de fórmulas, tests de la unit.
2322	2258		- Jan 2013:\
2323	2259		Version 1.1 released with all improvements up to revision 2621
2324	2260		- Nov 2011:\
2325	2261		Released version 1.0, which includes OO XML generation support with bold formatting supported and also a lot of improvements from the last years
2326	2262		- Jan 2009:\
2327	2263		Implemented a cross-platform support for OLE file. Now Excel 5.0 files can be created in any operating system. Adds read support for Excel 2.1
2328	2264		- Feb 2008:\
2329	2265		Initial commit to lazarus-ccr con soporte de escritura para Excel 2.1, Excel 5.0 (Windows solamente) y experimental para OOXML y OpenDocument.
2330	2266
2331	2267		## Licencia
2332	2268
2333	2269		LGPL with static linking exception. Es el mismo tipo de licecia que el utilizado por la librería de componentes de Lazarus.
2334	2270
2335	2271		## Referencias
2336	2272
2337	2273		### Enlaces Wiki
2338	2274
2339	2275		- [[Office Automation]]
2340	2276		- [[CsvDocument]]
2341	2277		- [[RPN Formulas in FPSpreadsheet\|RPN formulas in FPSpreadsheet]]
2342	2278		- [[FPSpreadsheet: List of formulas]]
2343	2279		- [[FPSpreadsheet tutorial: Writing a mini spreadsheet application]]
2344	2280
2345	2281		### Enlaces externos
2346	2282
2347	2283		- [Microsoft OLE Document Format](http://sc.openoffice.org/compdocfileformat.pdf)
2348	2284
2349	2285		- [Excel file format description](http://sc.openoffice.org/excelfileformat.pdf)
2350	2286
2351	2287		- 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.
2352	2288
2353	2289		- Iconos utilizados en los programas de demostración:
2354	2290
2355	2291		- - [silk icons](http://www.famfamfam.com/lab/icons/silk/)
2356	2292		- [fugue icons](http://p.yusukekamiyamane.com/)
2357	2293
2358	2294		---
2359	2295		From the Free Pascal wiki, page "FPSpreadsheet/es", as it stood on 2021-01-27 after a change by Slawek, 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).

Back to history