FPSpreadsheet tutorial: Writing a mini spreadsheet application/es

Introducción

FPSpreadsheet es un paquete potente para la lectura y escritura de ficheros spreadsheet (Hoja de cálculo). La intención principal es aportar una plataforma capaz de exportar e importar de forma nativa datos a/desde los más importantes formatos de fichero de hojas de cálculo sin tener que instalar aplicaciones adicionales.

Soon, however, the wish arises to use this package also for editing of file content or formatting. For this purpose, the library contains a dedicated grid control, the FPSpreadsheetGrid, which closely resembles the features of a worksheet of a spreadsheet application. The demo "spready" which comes along with FPSpreadsheet demonstrates usage of this grid. Along with a bunch of formatting options, this demo still comes up to more than 1400 lines of code in the main form unit. Therefore, a set of visual controls was developed which greatly simplify creation of spreadsheet applications.

It is the intention of this tutorial to write a simple spreadsheet program on the basis of these controls.

Although most of the internal structure of the FPSpreadsheet library is covered by the visual controls it is recommended that you have some knowledge of FPSpreadsheet. Of course, you should not have a basic understanding of Lazarus and FPC, and you must know how to work with the object inspector of Lazarus.

Visual FPSpreadsheet Controls

FPSpreadsheet exposes non-visual classes, such as TsWorkbook, TsWorksheet etc. This keeps the library general enough for all kind of Pascal programs. For GUI programs, on the other hand, some infrastructure is needed which relates the spreadsheets to forms, grids, and other controls.

TsWorkbookSource

The heart of the visual FPSpreadsheet controls is the TsWorkbookSource class. This provides a link between the non-visual spreadsheet data and the visual controls on the form. Its purpose is similar to that of a TDataSource component in database applications which links database tables or queries to dedicated "data-aware" controls.

All visual FPSpreadsheet controls have a property WorkbookSource which links them into the information chain provided by the TsWorkbookSource. The WorkbookSource keeps a list of all controls attached. Internally, these controls are called "listeners" because they listen to information distributed by the WorkbookSource.

The workbook and worksheets use events to notify the WorkbookSource of all relevant changes: changes in cell content or formatting, selecting other cells, adding or deleting worksheet etc. Information on these changes is passed on to the listening controls, and they react in their own specialized way on these changes. If, for example, a new worksheet is added to a workbook the visual TsWorkbookTabControl creates a new tab for the new worksheet, and the TsWorksheetGrid loads the new worksheet into the grid.

TsWorkbookTabControl

This is a tabcontrol which provides a tab for each worksheet of the current workbook. The tab names are identical with the names of the worksheets. Selecting another tab is communicated to the other visual spreadsheet controls via the WorkbookSource.

TsWorksheetGrid

This is a customized DrawGrid descendant of the LCL and displays cells of the currently selected worksheet. The texts are not stored in the grid (like a StringGrid would do), but are taken from the TsWorksheet data structure. Similarly, the worksheet provides the information of how each cell is formatted. Like any LCL grid it has a bunch of properties and can be tuned for many applications by adapting its Options. The most important one will be described below.

  Nota: The TsWorksheetGrid can also be operated without a TsWorkbookSource. For this purpose it provides its own set of methods for reading and writing files.

TsCellEdit

The typical spreadsheet applications provide a line for editing formulas or cell content. This is the purpose of the TsCellEdit. It displays the content of the active cell of the worksheet which is the same as the active cell of the WorksheetGrid. If editing is finished (by pressing ↵ Enter, or by selecting another cell in the WorksheetGrid) the new cell value is transferred to the worksheet. Internally, the TsCellEdit is a memo control, i.e. it is able to process multi-line text correctly. Use Ctrl+↵ Enter to insert a forced line-break.

TsCellIndicator

This is a TEdit control which displays the address of the currently selected cell in Excel notation, e.g. 'A1' if the active cell is in the first row and first column (row = 0, column = 0). Conversely, if a valid cell address is entered into this control the corresponding cell becomes active.

TsCellCombobox

This combobox can be used to modify various cell properties by selecting values from the dropdown list. The property affected is determined by the CellFormatItem of the combobox:

• cfiFontName: the list contains he names of all fonts available on the current system. If an item is selected the corresponding font is used to format the cell of the currently selected cells.
• cfiFontSize: the list contains the most typical font sizes used in spreadsheets. Selecting an item sets the font size of the currently selected cells accordingly.
• cfiFontColor: the list contains all colors of the workbook's palette. The selected color is assigned to the font of the selected cells.
• cfiBackgroundColor: like cfiFontColor - the selected color is used as background fill color of the selected cells.

TsSpreadsheetInspector

Inherits from TValueListEditor and displays name-value pairs for properties of the workbook, the selected worksheet, and the content and formatting of the active cell. It's main purpose is to help with debugging.