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.

Pronto, como siempre, surge el deseo de utilizar este paquete también para editar el contenido y formato. Para este propósito, la librería contiene un control grid (FPSpreadSheetGrid) dedicado, que nos recuerda las características de una hoja de trabajo (WorkSheet)de las aplicaciones de hojas de cálculo. Junto a un conjunto de opciones de formato, esta demo todavía viene a tener 1400 líneas de código en su unidad de formulario principal. Por lo tanto, se ha diseñado un conjunto de controles visuales que simplifican ampliamente la creación de aplicaciones de hoja de cálculo (SpreadSheets).

La intención de este tutorial es escribir un programa de hoja de cálculo simple en base a estos controles.

Aunque la mayor parte de la estructura interna de la librería FPSpreadsheet está cubierta mediante los controles visuales sigue siendo recomendable tener algún conocimiento de FPSpreadsheet. Por supuesto debería tenerse un conocimiento básico de Lazarus o FPC y sobre como trabajar con el inspector de objetos de 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.