FPSpreadsheet tutorial: Writing a mini spreadsheet application

Introduction

FPSpreadsheet is a powerful package for reading and writing spreadsheet files. The main intention is to provide a platform which is capable of native export/import of an application's data to/from the most important spreadsheet file formats without having these spreadsheet applications installed.

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.

The heart of the Visual FPSpreadsheet Controls is the TsWorkbookSource class. This provives 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 tab into the grid.

Here is a list of all Visual FPSpreadsheet Controls:

• TsWorkbookTabControl: 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 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. Here are the most important ones:
  • goEditing: Grid cells can be edited. After editing is finished (by pressing ENTER, or by selecting a different cell) the new cell content is transferred to the underlying worksheet.
  • goColSizing: Adjusts the column width by dragging the right cell border in the grid's header with the left mouse button down. The new column width is stored in the current worksheet.
  • goRowSizing: The same with the row heights
  • goDblClickAutoSize: A double click at the right border of a grid column header adjusts the width of a column such that all cell content fits into this column.
  • goHeaderHotTracking: Highlights the column and row headers if touched by the mouse.
  • goThumbTracking: When dragging a scrollbar with the mouse the displayed content is automatically updated to the visible view of the grid.
  • goRangeSelect: Allows to select a range of cells by dragging the mouse or using the arrow keys with holding SHIFT down. In addition, it is possible to select several cell ranges by holding the CTRL key down (Lazarus trunk only).
• 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 controls, 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.
• TsFontNameCombobox: This combobox contains the 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.
• TsFontSizeCombobox: This combobox contains the most typical font sizes used in spreadsheets. Selecting an item sets the font size of the currently selected cells accordingly.
• TsSpreadsheetInspector: Inherits from TValueListEditor and displays name-value pairs of properties of the workbook, the selected worksheet, and the content and formatting of the selected cells. It's main purpose is to help with debugging.