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 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 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 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.

Writing a spreadsheet application

Enough of theory, let's get started. Let's write a small spreadsheet application. Sure - it cannot compete with the spreadsheets of the main Office applications like Excel or Open/LibreOffice, but it has all the main ingredients due to FPSpreadsheet. And using the FPSpreadsheet controls allows to achieve this with minimum lines of code.

Preparations

Create a new project and store it in a folder of your liking.

Since Office applications have a menu and a toolbar add a TMainMenu and a TToolbar component to the form. (You could even mimic the ribbon user interface of the new Microsoft applications by adding a TSpkToolbar from Lazarus Code and Components Repository, but note that this component does not provide all the features of a standard toolbar).

The toolbar of the completed application will contain a lot of of icons. Therefore, we need a TImageList component which has to be linked to the Images property of the TMainMenu and the TToolbar. Where to get icons? You can have a look in the folder images of your Lazarus installation where you'll find standard icons for loading and saving etc. This is a subset of the famfamfam SILK icon library. Another huge icon set is the Fugue icon collection. Both collections are licensed as "Creative commons" and are free even for commercial usage, provided that appropriate reference is given in the created programs. When selecting icons prefer the png image format, and make sure to use always the same size, usually 16x16 pixels.

Since menu and toolbar will have to handle same user actions it is advantageous to provide a TActionList to store all possible actions. If assigned to the menu items and toolbuttons both will react on user interaction in the same way without any additional coding. And: The FPSpreadsheet visual controls package contains a bunch of spreadsheet-related standard actions ready to use.

Setting up the visual workbook

TsWorkbookSource

As described in the introductory section the TsWorkbookSource component is the interface between workbook and controls on the user interface. Add this component to the form and give it a decent name (we'll keep the default name (sWorkbookSource1) here, though). As you will see shortly, this component will have to be assigned to the property WorkbookSource of all controls of the FPSpreadsheet_visual package.

TsWorkbookTabControl

The first visual control used in the form is a TsWorkbookTabControl - click it onto the form (into the space not occupied by the toolbar). Client-align it within the form, this shows the TabControl as a bright rectangle only. Now link its WorkbookSource property to the TsWorkbookSource component that we have added just before. Now the TabControl shows a tab labelled "Sheet1". This is because the TsWorkbookSource has created a dummy workkbook containing a single worksheet "Sheet1". The WorkbookSource synchronizes this internal workbook with the TabControl (and the other visual controls to come) such that it displays this worksheet as a tab.

In Excel the worksheet tabs are at the bottom of the form - to achieve this effect you can set the property TabPosition of the TabControl to tpBottom; there are some painting issues of the LCL with this TabPosition, though, therefore, I prefer the default setting, tpTop.

The screenshot shows how far we've got.

TsWorksheetGrid

Now we add a TsWorksheetGrid control. Click it somewhere into the space occupied by the TabControl such that it becomes a child a of the TabControl. You see a standard stringgrid-like component. Link its WorkbookSource property to the source added at the beginning, and the grid looks more like a spreadsheet: there are the column headers labelled by letters "A", "B", etc, and the row headers labelled by numbers "1", "2", etc; the active cell, A1, is marked by a thick border.

You may want to switch the grid's TitleStyle to tsNative in order to achieved themed painting of the row and column headers. And here is a good place to adapt the grid's Options in order to activate many features well-known to spreadsheets:

• goColSizing enables changing of the column width by dragging the dividing line between adjacent column headers. Dragging occurs with the left mouse button pressed.
• goDblAutoResize activates the feature that a optimum column width can be set by double-clicking in the header on its dividing line to the next column. The "optimum" column width is such that no cell content is truncated and no extra space is shown in the column.
• goEditing must be active, otherwise the grid contents cannot be modified.
• goHeaderHotTack gives visual feedback if the mouse is above a header cell.
• goRangeSelect (which is on by default) enables selection of a rectangular range of cells by dragging the mouse between cells at opposite corners of the rectangle. If you have Lazarus trunk you can even select multiple rectangles by holding the CTRL key down before the next rectangle is dragged - in the release version of Lazarus (1.2.6 at the time of this writing) only a single range can be selected.
• goRowSizing makes it possible to change the row height by dragging the dividing line between adjacent row headers.
• goThumbTracking activates immediate scrolling of the worksheet if one of the scrollbars is dragged with the mouse. The Office applications usually scroll by lines; you can achieve this by turning off goSmoothScroll.

When you compile the program you'll be able to enter data into the grid. Just select the cell that you want to edit by clicking or using the arrow keys - the active cell is highlighted by a thick border. Then begin typing. When finished press ther ENTER key or select the next cell. Using ENTER automatically selects the next cell in the grid. The grid's property AutoAdvance defines what is understood as being the "next cell": by default, ENTER moves the active cell down (aaDown), but you can also move to the right (aaRight), or turn this feature off (aaNone):

type
  TAutoAdvance = (aaNone, aaDown, aaRight, aaLeft, aaRightDown, aaLeftDown, aaRightUp, aaLeftUp);

Formatting a cell by using standard actions

FPSpreadsheet supports a lot of formats that can be applied to cells, such as text alignment, text rotation, text font, or cell borders or background colors. Typical gui applications contain menu commands and/or toolbar buttons which are assigned to each of these properties and allow to set them by a simple mouse click. In addition, the state of these controls often reflects the properties of the active cell. For example, if there is a button for using a bold type-face this button should be drawn as being pressed if the active cell is bold, but as released if it is not. To simplify the coding of these tasks a large number of standard actions has been added to the library.

If you have never worked with standard actions before here are some detailed step-by-step instructions. Let us stick to above example and provide the possibility to switch the font of the active cell to bold. The standard action which is responsible for this feature is the TsFontStyleAction.

At first, we add this action to the form: Double-click on the TActionList to open the "ActionList Editor". Click on the down-arrow next to the "+" button, and select the item "New standard action" from the drop-down menu. This opens a dialog with the list of registered "Standard Action Classes". Scroll down until you find a group named "FPSpreadsheet". In this group, select the item "TsFontStyleAction" by double-clicking. Now TsFontStyleAction appears in the ActionListEditor. Select it there to bring it up in the Object Inspector where you can set its properties:

• Use the text "bold" for the Caption - this is the text that will be assigned to the corresponding menu item.
• Similarly, assign "bold font" to the Hint property.
• Set the ImageIndex to the index of the icon in the form's ImageList that you want to see in the toolbar.
• Select the item fssBold from the dropdown list of the property FontStyle (it should be selected already by default). Since TsFontStyleAction can handle several font decorations (bold, italic, underline, strikeout) we have to tell the action which font attribute it sould control.
• Like with the visual controls, don't forget to assign the TsWorkbookSource to the corresponding property WorkbookSource of the action. This activates the communication between the worksheet/workbook on the one hand, and with the action and the related controls on the other hand.

Having set up the standard action we add a menu item to the form's MainMenu. Double-click on the TMainMenu of the form to bring up the "Menu Editor". Since the menu is empty so far there is only a dummy item, "New item1". This will become our "Format" menu. Select the item, and type "Format" into the Caption property field. Now the dummy item is re-labelled as "Format". Right-click on this "Format" item, and select "Create submenu" from the popup menu which brings up another new menu item, "New item2". Select it. In the dropdown list of the property "Action" of the Object Inspector, pick the sFontStyle1 action - this is the action that we have just set up - and the menu item automatically shows the caption provided by the action component, "Bold".

Finally we add a toolbar button for the "bold" action. Right-click onto the TToolbar, and add a new toolbutton by selecting the popup menu item "New button". Go to the property "Action" in the Object Inspector again, pick the "sFontStyle1" item, and this gives the tool button the ability to set a cell font to bold!

Test the program by compiling. Type some text into cells. Select one of them and click the "Bold" toolbutton - voila, the cell is in bold font. Select another cell. Note that the toolbutton is automatically drawn in the down state if the cell has bold font.

Saving a file

After having entered data into the grid you will certainly want to save the grid to a spreadsheet file. Lazarus makes all the necessary infrastructure for saving available in the standard action TFileSaveAs. Double-click on the TActionList to open the ActionList editor, click on the down-arrow next to the "+" button and select the item "New standard action" from the dropdown menu. This opens a dialog with the list of standard action classes. Scroll down a bit until you find the item TFileSaveAs. Select it. The standard action already contains a usable caption ("Save as") and hint text. You just have to assign an image index for the assosciated icon. If the ImageIndex is empty you have certainly forgotten to assign the Images property of the ActionList to the ImageList.

<syntaxhighlight>

procedure TForm1.AcFileSaveAsAccept(Sender: TObject); var

 fmt: TsSpreadsheetFormat;

begin

 Screen.Cursor := crHourglass;
 try
   case AcFileSaveAs.Dialog.FilterIndex of
     1: fmt := sfOOXML;
     2: fmt := sfExcel8;
     3: fmt := sfExcel5;
     4: fmt := sfExcel2;
     5: fmt := sfOpenDocument;
     6: fmt := sfCSV;
     7: fmt := sfWikiTable_WikiMedia;
   end;
   WorkbookSource.SaveToSpreadsheetFile(AcFileSaveAs.Dialog.FileName, fmt);
 finally
   Screen.Cursor := crDefault;
 end;

end;

procedure TsWorkbookSource.SaveToSpreadsheetFile(AFileName: string; AFormat: TsSpreadsheetFormat;

 AOverwriteExisting: Boolean = true); overload;

procedure TsWorkbookSource.SaveToSpreadsheetFile(AFileName: string;

     AOverwriteExisting: Boolean = true); overload;

procedure TsWorksheetGrid.SaveToSpreadsheetFile(AFileName: string; AFormat: TsSpreadsheetFormat;

AOverwriteExisting: Boolean = true); overload;

procedure TsWorkbook.WriteToFile(const AFileName: string;

const AFormat: TsSpreadsheetFormat; const AOverwriteExisting: Boolean = False);

To be continued...