FPSpreadsheet: Examples: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: FPSpreadsheet: Examples

New		Line
1	+	## Requirements to create an FPSpreadsheet application
2	+
3	+	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.
4	+
5	+	### Units
6	+
7	+	The entire FPSpreadsheet package consists of several units. A spreadsheet application typically "uses" the following units:
8	+
9	+	- fpspreadsheet: implements TsWorkbook and TsWorksheet and the basic file reading/writing methods
10	+	- fpstypes: declares most of the data types and constants used throughout the package. Note: in older versions these declarations were contained in fpspreadsheet.pas.
11	+	- 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.
12	+
13	+	The next units are required only occasionally:
14	+
15	+	- fpsutils: a collection of utility functions that are occasionally needed (e.g. conversion of col/row indexes to Excel-like cell address string).
16	+	- 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).
17	+	- fpsnumformat: collects all utility functions related to number formats.
18	+
19	+	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.
20	+
21	+	## Sample projects in the fpspreadsheet installation folder
22	+
23	+	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
24	+
25	+	- 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.
26	+	- 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.
27	+	- Folder read_write:
28	+	- 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.
29	+	- excel5demo, like excel2demo, but for Excel 5 xls files.
30	+	- excel8demo, like excel2demo, but for Excel 97-2003 xls files.
31	+	- csvdemo, like excel2demo, but for CSV files.
32	+	- htmldemo, like excel2demo, but for HTML file (currently writing only).
33	+	- ooxmldemo, like excel2demo, but for the new Excel xlsx files.
34	+	- opendocdemo, like excel2demo, but for OpenOffice/LibreOffice ods files
35	+	- wikitabledemo, like excel2demo, but for wiki table files. Note that the write example currently writes a format that the read example cannot understand.
36	+	- other: simple commandline programs showing various aspects of the fpspreadsheet package. Have a look at readme.txt for more details.
37	+	- Folder visual:
38	+	- fpschart shows the application of the [[TsWorksheetChartSource]] and TsWorkbookChartSource and the interaction with the [[TAChart]] plotting package.
39	+	- fpsctrls and fpsctrls_no_install create a GUI spreadsheet application with a minimum amount of written code; the latter demo is good for testing because it does not require installation of the FPSpreadsheet packages. Step-by-step instructions on how fpsctrls is made can be found in the [[FPSpreadsheet tutorial: Writing a mini spreadsheet application\|FSpreadsheet tutorial]].
40	+	- fpsgrid and fpsgrid_no_install show the basic application of the [[TsWorksheetGrid]] without using the TsWorkbookSource component; the latter demo does not require installation of any FPSpreadsheet package.
41	+	- 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.
42	+	- spready is an extended application of the the entire library showing spreadsheet files with formatting, editing of cells, etc. Since it is a stand-alone application it has been moved to the folder applications/spready' of theLazarus Components and Code Library''.
43	+
44	+	## Code examples
45	+
46	+	### Opening an existing spreadsheet
47	+
48	+	To open a spreadsheet while specifying a particular format to use use `ReadFromFile` with two parameters:
49	+
50	+	``` pascal
51	+	MyWorkbook.ReadFromFile(AFileName, sfExcel5);
52	+	```
53	+
54	+	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.
55	+
56	+	``` pascal
57	+	MyWorkbook.ReadFromFile(AFileName);
58	+	```
59	+
60	+	### Writing a spreadsheet to file based on extension
61	+
62	+	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.
63	+
64	+	As above, this code patches the fpspreadsheet.pas unit.
65	+
66	+	``` pascal
67	+	procedure TsWorkbook.WriteToFile(const AFileName: string; const AOverwriteExisting: Boolean = False); overload;
68	+	var SheetType: TsSpreadsheetFormat;
69	+	begin
70	+	if getFormatFromFileName(AFileName, SheetType) then
71	+	WriteToFile(AFileName, SheetType, AOverwriteExisting)
72	+	else raise Exception.Create(Format(
73	+	'[TsWorkbook.WriteToFile] Attempted to save a spreadsheet by extension, but the extension %s is invalid.', [ExtractFileExt(AFileName)]));
74	+	end;
75	+	```
76	+
77	+	### Converting between two spreadsheet formats
78	+
79	+	``` pascal
80	+	program ods2xls;
81	+
82	+	{$mode delphi}{$H+}
83	+
84	+	uses
85	+	Classes, SysUtils,
86	+	fpstypes, fpspreadsheet, fpsallformats, fpspreadsheet_pkg;
87	+
88	+	const
89	+	INPUT_FORMAT = sfOpenDocument;
90	+	OUTPUT_FORMAT = sfExcel8;
91	+
92	+	var
93	+	MyWorkbook: TsWorkbook;
94	+	MyDir: string;
95	+	begin
96	+	// Initialization
97	+	MyDir := ExtractFilePath(ParamStr(0));
98	+
99	+	// Convert the spreadsheet
100	+	MyWorkbook := TsWorkbook.Create;
101	+	try
102	+	MyWorkbook.ReadFromFile(MyDir + 'test.ods', INPUT_FORMAT);
103	+	MyWorkbook.WriteToFile(MyDir + 'test.xls', OUTPUT_FORMAT);
104	+	finally
105	+	MyWorkbook.Free;
106	+	end;
107	+	end.
108	+	```
109	+
110	+	### Iterating through all worksheets
111	+
112	+	``` pascal
113	+	var
114	+	MyWorkbook: TsWorkbook;
115	+	MyWorksheet: TsWorksheet;
116	+	i: Integer;
117	+	begin
118	+	// Here load MyWorkbook from a file or build it
119	+
120	+	for i := 0 to MyWorkbook.GetWorksheetCount() - 1 do
121	+	begin
122	+	MyWorksheet := MyWorkbook.GetWorksheetByIndex(i);
123	+	// Do something with MyWorksheet
124	+	end;
125	+	```
126	+
127	+	### Iterating through cells
128	+
129	+	The first idea is to use a simple for-to loop:
130	+
131	+	``` pascal
132	+	var
133	+	MyWorksheet: TsWorksheet;
134	+	col, row: Cardinal;
135	+	cell: PCell;
136	+	begin
137	+	for row:=0 to MyWorksheet.GetLastRowIndex do
138	+	for col := 0 to MyWorksheet.GetLastColIndex do
139	+	begin
140	+	cell := MyWorksheet.FindCell(row, col);
141	+	WriteLn(MyWorksheet.ReadAsUTF8Text(cell));
142	+	end;
143	+	end;
144	+	```
145	+
146	+	`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:
147	+
148	+	``` pascal
149	+	var
150	+	MyWorksheet: TsWorksheet;
151	+	cell: PCell;
152	+	begin
153	+	// Search in all cells
154	+	for cell in Myworksheet.Cells do
155	+	WriteLn(MyWorksheet.ReadAsText(cell));
156	+
157	+	// Search in column 0 only
158	+	for cell in MyWorksheet.Cells.GetColEnumerator(0) do
159	+	WriteLn(MyWorksheet.ReadAsText(cell));
160	+
161	+	// Search in row 2 only
162	+	for cell in MyWorksheet.Cells.GetRowEnumerator(2) do
163	+	WriteLn(MyWorksheet.ReadAsText(cell));
164	+
165	+	// Search in range A1:C2 only (rows 0..1, columns 0..2)
166	+	for cell in MyWorksheet.Cells.GetRangeEnumerator(0, 0, 1, 2) do
167	+	WriteLn(MyWorksheet.ReadAsText(cell));
168	+	end;
169	+	```
170	+
171	+	### Excel 5 example
172	+
173	+	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)
174	+
175	+	``` pascal
176	+	{
177	+	excel5demo.dpr
178	+
179	+	Demonstrates how to write an Excel 5.x file using the fpspreadsheet library
180	+
181	+	You can change the output format by changing the OUTPUT_FORMAT constant
182	+
183	+	AUTHORS: Felipe Monteiro de Carvalho
184	+	}
185	+	program excel5demo;
186	+
187	+	{$mode delphi}{$H+}
188	+
189	+	uses
190	+	Classes, SysUtils, fpstypes, fpspreadsheet, fpsallformats, laz_fpspreadsheet;
191	+
192	+	const
193	+	OUTPUT_FORMAT = sfExcel5;
194	+
195	+	var
196	+	MyWorkbook: TsWorkbook;
197	+	MyWorksheet: TsWorksheet;
198	+	MyFormula: TsRPNFormula;
199	+	MyDir: string;
200	+	begin
201	+	// Initialization
202	+	MyDir := ExtractFilePath(ParamStr(0));
203	+
204	+	// Create the spreadsheet
205	+	MyWorkbook := TsWorkbook.Create;
206	+	try
207	+	MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
208	+
209	+	// Write some number cells
210	+	MyWorksheet.WriteNumber(0, 0, 1.0);
211	+	MyWorksheet.WriteNumber(0, 1, 2.0);
212	+	MyWorksheet.WriteNumber(0, 2, 3.0);
213	+	MyWorksheet.WriteNumber(0, 3, 4.0);
214	+
215	+	// Write the formula E1 = A1 + B1
216	+	MyWorksheet.WriteFormula(0, 4, 'A1+B1');
217	+
218	+	// Creates a new worksheet
219	+	MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');
220	+
221	+	// Write some string cells
222	+	MyWorksheet.WriteText(0, 0, 'First');
223	+	MyWorksheet.WriteText(0, 1, 'Second');
224	+	MyWorksheet.WriteText(0, 2, 'Third');
225	+	MyWorksheet.WriteText(0, 3, 'Fourth');
226	+
227	+	// Save the spreadsheet to a file
228	+	MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
229	+	finally
230	+	MyWorkbook.Free;
231	+	end;
232	+	end.
233	+	```
234	+
235	+	### Reading and writing of CSV files
236	+
237	+	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:
238	+
239	+	``` pascal
240	+	type
241	+	CSVParams: TsCSVParams = record // W = writing, R = reading, RW = reading/writing
242	+	SheetIndex: Integer; // W: Index of the sheet to be written
243	+	LineEnding: TsCSVLineEnding; // W: Specification for line ending to be written
244	+	Delimiter: Char; // RW: Column delimiter
245	+	QuoteChar: Char; // RW: Character for quoting texts
246	+	Encoding: String; // RW: Encoding of file
247	+	DetectContentType: Boolean; // R: try to convert strings to content types
248	+	NumberFormat: String; // W: if empty write numbers like in sheet, otherwise use this format
249	+	AutoDetectNumberFormat: Boolean; // R: automatically detects decimal/thousand separator used in numbers
250	+	TrueText: String; // RW: String for boolean TRUE
251	+	FalseText: String; // RW: String for boolean FALSE
252	+	FormatSettings: TFormatSettings; // RW: add'l parameters for conversion
253	+	end;
254	+	```
255	+
256	+	This record contains fields which are evaluated for reading only, writing only, or for both - see the attached comments.
257	+
258	+	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:
259	+
260	+	``` pascal
261	+	uses
262	+	fpstypes, fpspreadsheet, fpscsv;
263	+	var
264	+	MyWorkbook: TsWorkbook;
265	+	begin
266	+	CSVParams.FormatSettings.DecimalSeparator := '.';
267	+	CSVParams.Delimiter := #9;
268	+	MyWorkbook := TsWorkbook.Create;
269	+	try
270	+	MyWorkbook.ReadFromFile('machine-data.csv', sfCSV);
271	+	finally
272	+	MyWorkbook.Free;
273	+	end;
274	+	end;
275	+	```
276	+
277	+	> 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.
278	+
279	+	### Converting a database to a spreadsheet
280	+
281	+	The easiest solution is to use the DatasetExport component.
282	+
283	+	If you need to have more control over the process, use something like:
284	+
285	+	``` pascal
286	+	program db5xls;
287	+
288	+	{$mode delphi}{$H+}
289	+
290	+	uses
291	+	Classes, SysUtils,
292	+	// add database units
293	+	fpstypes, fpspreadsheet, fpsallformats;
294	+
295	+	const OUTPUT_FORMAT = sfExcel5;
296	+
297	+	var
298	+	MyWorkbook: TsWorkbook;
299	+	MyWorksheet: TsWorksheet;
300	+	MyDatabase: TSdfDataset;
301	+	MyDir: string;
302	+	i, j: Integer;
303	+	begin
304	+	// Initialization
305	+	MyDir := ExtractFilePath(ParamStr(0));
306	+
307	+	// Open the database
308	+	MyDatabase := TSdfDataset.Create;
309	+	MyDatabase.Filename := 'test.dat';
310	+	// Add table description here
311	+	MyDatabase.Active := True;
312	+
313	+	// Create the spreadsheet
314	+	MyWorkbook := TsWorkbook.Create;
315	+	MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
316	+
317	+	// Write the field names
318	+	for i := 0 to MyDatabase.Fields.Count - 1 do
319	+	MyWorksheet.WriteText(0, i, MyDatabase.Fields[i].FieldName);
320	+
321	+	// Write all cells to the worksheet
322	+	MyDatabase.First;
323	+	j := 0;
324	+	while not MyDatabase.EOF do
325	+	begin
326	+	for i := 0 to MyDatabase.Fields.Count - 1 do
327	+	MyWorksheet.WriteText(j + 1, i, MyDatabase.Fields[i].AsString);
328	+
329	+	MyDatabase.Next;
330	+	Inc(j);
331	+	end;
332	+
333	+	// Close the database
334	+	MyDatabase.Active := False;
335	+	MyDatabase.Free;
336	+
337	+	// Save the spreadsheet to a file
338	+	MyWorkbook.WriteToFile(MyDir + 'test' + STR_EXCEL_EXTENSION, OUTPUT_FORMAT);
339	+	MyWorkbook.Free;
340	+	end.
341	+	```
342	+
343	+	### Converting a large database table to a spreadsheet using virtual mode
344	+
345	+	> Note: The example program in examples\db_import_export shows a demonstration of using virtual mode to export datasets to spreadsheet files.
346	+
347	+	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.
348	+
349	+	Normally, FPSpreadsheet would load the entire representation of the spreadsheet into memory, so we'll use virtual mode to minimize memory usage.
350	+
351	+	``` pascal
352	+	type
353	+	TDataProvider = class;
354	+
355	+	var
356	+	MyWorkbook: TsWorkbook;
357	+	MyWorksheet: TsWorksheet;
358	+	MyDatabase: TSdfDataset;
359	+	MyDir: string;
360	+	MyHeaderTemplateCell: PCell;
361	+	DataProvider: TDataProvider;
362	+
363	+	// Implement TDataProvider here - see below...
364	+
365	+	begin
366	+	// Initialization
367	+	MyDir := ExtractFilePath(ParamStr(0));
368	+
369	+	// Open the database
370	+	MyDatabase := TSdfDataset.Create;
371	+	try
372	+	MyDatabase.Filename := 'test.dat';
373	+	// Add table description here
374	+	MyDatabase.Active := True;
375	+
376	+	// Create the spreadsheet
377	+	MyWorkbook := TsWorkbook.Create;
378	+	try
379	+	MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
380	+
381	+	// Create the template cell for the header line, we want the
382	+	// header in bold type-face and gray background color
383	+	// The template cell can be anywhere in the workbook, let's just select cell A1.
384	+	MyWorksheet.WriteFontStyle(0, 0, [fssBold]);
385	+	MyWorksheet.WriteBackgroundColor(0, 0, scGray);
386	+	// We'll need this cell again and again, so let's save the pointer to it in a variable
387	+	MyHeaderTemplateCell := MyWorksheet.Find(0, 0);
388	+
389	+	// Enter virtual mode
390	+	MyWorkbook.Options := MyWorkbook.Options + [boVirtualMode];
391	+
392	+	// Define number of columns - we want a column for each field
393	+	MyWorksheet.VirtualColCount := MyDatabase.FieldCount;
394	+
395	+	// Define number of rows - we want every record, plus 1 row for the title row
396	+	MyWorksheet.VirtualRowCount := MyDatabase.RecordCount + 1;
397	+
398	+	// Link the event handler which passes data from database to spreadsheet writer
399	+	MyWorksheet.OnWriteCellData := @DataProvider.WriteCellData;
400	+
401	+	// Write all cells to an Excel8 file
402	+	// The data to be written are specified in the OnWriteCellData event handler.
403	+	MyWorkbook.WriteToFile(MyDir + 'test.xls', sfExcel8);
404	+
405	+	finally
406	+	// Clean-up
407	+	MyWorkbook.Free;
408	+	end;
409	+
410	+	finally
411	+	// Close the database & clean-up
412	+	MyDatabase.Active := False;
413	+	MyDatabase.Free;
414	+	end;
415	+
416	+	end.
417	+	```
418	+
419	+	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):
420	+
421	+	``` pascal
422	+	type
423	+	TDataProvider = class
424	+	procedure WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell);
425	+	end;
426	+
427	+	procedure TDataProvider.WriteCellData(Sender: TsWorksheet; ARow, ACol: Cardinal; AValue: variant; var: AStyleCell: PCell);
428	+	begin
429	+	// Let's handle the header row first:
430	+	if ARow = 0 then begin
431	+	// The value to be written to the spreadsheet is the field name.
432	+	AValue := MyDatabase.Fields[ACol].FieldName;
433	+	// Formatting is defined in the HeaderTemplateCell.
434	+	AStyleCell := MyHeaderTemplateCell;
435	+	// Move to first record
436	+	MyDatabase.First;
437	+	end else begin
438	+	// The value to be written to the spreadsheet is the record value in the field corresponding to the column.
439	+	// No special requirements on formatting --> leave AStyleCell at its default (nil).
440	+	AValue := MyDatabase.Fields[ACol].AsVariant;
441	+	// Advance database cursor if last field of record has been written
442	+	if ACol = MyDatabase.FieldCount-1 then MyDatabase.Next;
443	+	end;
444	+	end;
445	+	```
446	+
447	+	---
448	+	From the Free Pascal wiki, page "FPSpreadsheet: Examples", as it stood on 2020-06-02 after a change by Wp, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).
449	+	Category: FPSpreadsheet

Back to history