GtCalc: A C++ Spreadsheet control for GT Graphical User Interface Library
Author: Anthony Daniels
GT is a compact, efficient, and customizable graphical user interface (GUI) library for the Windows environment (eventually Linux and Mac will be added). The majority of the code is platform independent, only relying directly on the Operating System’s graphics, threading, and clipboard APIs (these features are encapsulated). With the number of GUI systems growing, one might ask why make another system. GT was made to address shortcomings in some of the major competitors in addition to being really compact (GT currently has approx. 90,000 lines of code, or LOC, including comments). With the LOC count this low, GT is possible to embed directly into your application solution or project files. Its major competitors are MFC, QT, GIMP Toolkit, wxWidgets, Fox Toolkit. GT most closely tracks with QT in design and function. For more information on GT, read this article.
GtCalc is a new addition to the greater GT library. As an engineer, I am constantly in need of embedding spreadsheet functionality into my applications.
GtCalc is intended to provide the user a Microsoft Excel equivalent spreadsheet control and function evaluator. The function evaluator used in this library is derived from the
CalcStar engine. The function set covers most of the major Excel math functions that operate on either individual cells or a range of cells. The functions provided are as follows:
Basic Functions (Calcstar)
|Divide||/||Boolean Not Equal||!=|
|Natural Log||ln||Greater Than or Equal||>=|
|Log10||log10||Less Than or Equal||<=|
|Truncate||trunc|| || |
Trigonometric Functions (CalcStar)
|Hyp Sine||sinh||Arc Hyp Sine||asinh|
|Hyp Cosine||cosh||Arc Hyp Cosine||acosh|
|Hyp Tangent||tanh||Arc Hyp Tangent||atanh|
Additional Math Functions (Excel)
|Minimum||min|| || |
|Modulo||mod|| || |
GtCalc is very simple. In any application that uses GT as the user interface, insert the following code into the
InitializeControls() method of the
GtDialog that is intended as the parent of the spreadsheet. You set the control size and add it to the subwidget collection for that dialog.
GtSheetView * m_ptrSpreadSheet;
m_ptrSpreadSheet = new GtSheetView(this);
rectNew.xMin = 50; rectNew.xMax = 900; rectNew.yMin = 25; rectNew.yMax = 650;
Like with the rest of GT controls, the
GtSheetView is nestable. So if you want to insert it into a tab page, frame, etc. you can do so. Just set the parent object pointer and the
AddSubWidget method accordingly. For example, inserting the spreadsheet into a tab page would look something like this:
m_ptrSpreadSheet = new GtSheetView(m_ptrPage1);
rectNew.xMin = 25; rectNew.xMax = 400; rectNew.yMin = 25; rectNew.yMax = 400;
GtSheetView is the main object that contains a spreadsheet.
GtSheetView has collections of
GtCellData. The rows, columns, and cells are contained in maps that are index based. There are accessor functions for getting individual cells. The user can also access selected ranges of cells through the
GtSheetSelection object that the
GtSheetView has. Spreadsheet recalculation is done automatically whenever a cell edit is completed, but you can manually trigger a spreadsheet re-calc with the
EvaluateSheet() method. When this method is triggered, the sheet cycles through the cells doing full tokenization, compilation, and evaluation of each cell. Each cell has its own
GtFunctionEvaluator responsible for all calculations of that cell. The
GtFunctionEvaluator is a modified version of the
CalcStar. Modifications needed to be made to allow for evaluation of ranges of cells. Remember,
CalcStar compiles infix notation equations into a RPN calculation stack for single pass evaluation.
GtCalc is built on top of
CalcStar. So any math function in
CalcStar is valid. For more information on
Calcstar, please refer to this article.
If functions like sum refer to a range of cells, the syntax is
sum(r1c1:r2c2). Single cell ranges are allowed but are the full range syntax (e.g. r1c1:r1c1). You can also use cell references directy in a function like Excel does. For example =r1c23 + r2c2 is a legal function. Referencing different pages would look like =sum(SheetName:r1c1:r15c1). Below is a screenshot of a single spreadsheet test application provided.
There are two menus Sheet (Save, Load, and Properties) and Cell (Properties, Background Color, and Calc Visualizer). You can save and load the spreadsheets to XML files. The serialization used is the HPC Template Library serialization engine. The calculation visualizer allows you to see the tokenized and compiled math expression. This is useful when debugging new functions. The calculation visualizer is a modeless dialog so you can keep it open and watch it change when you select different cells. Editing of cells can be done via Double click on the cell, or selecting a cell and then editing in the function bar at the top. In either case, the Enter key ends the edit of the cell and triggers a spreadsheet recalc.
A multiple spreadsheet workbook class is also provided in GtCalc. Use of the workbook class is similar to the single GtSheetView.
m_ptrWorkbook = new GtWorkbookView(m_ptrBookFrame);
rectNew.xMin = 0; rectNew.xMax = 900; rectNew.yMin = 0; rectNew.yMax = 650;
The workbook class pictured below has a ribbon control at the top with the command buttons. There is File manipulation commands (New, Open, Save, SaveAs, Print), Sheet commands (Add Sheet, Delete Sheet, Cut, Copy, Paste), and Charting controls will be added in the future. It should be noted that Printing has not been added yet.
The workbook class now supports baseline charting for line, bar, column, pie and scatter plots. The plots are serializable so when you save your workbook the plots are saved. Like the GtSheetView class, GtChartView has a menu for manipulating the chart in the upper left hand corner. For example, after a chart is created by clicking one of the add chart buttons, the user selects Chart->Data Series and gets a popup dialog for typing in the X and Y series for the chart. The series follow the range syntax (e.g. MySheet01:r1c1:r10c1). The editor is pictured below.
The Chart menu also has formatting for the various chart components. Right now only Format->Chart is operational. Formatting for the horizontal and vertical axis will be added at a later date. The plotting has been stress tested to 2000 data points. The below plot and corresponding spreadsheet takes one minute to load. The reason for the delay is that the entire workbook is loaded into memory from a single XML file. These performance issues are being looked at for future development.
Here is an example Pie Plot. It should be noted that pie plot labels are yet to be added. Callout labels will be implemented and will be customizable in position. The system also has an existing Legend capability. For examples of this go to the GT web page. For sample workbook files look for the xml files in the App directory download.
GtCalc was intended to give GT developers a ready to use spreadsheet control with calculation abilities. The number of cells has been tested up to 50000 cells. Larger spreadsheets experience a slowdown in performance in scrolling. These performance issues are being looked at. The entire project is released under LGPL and MIT license in the hopes that the entire GT system is useful to those developing. The binary project is compiled as debug mode because the developer has had issues with popup dialogs in release mode. If you fix this or any other bug, feel free to send me the code to your changes and I will integrate them into the code base.