GtCalc: A C++ Spreadsheet Control for GT Graphical User Interface Library

GtCalc: A C++ Spreadsheet control for GT Graphical User Interface Library
Author: Anthony Daniels
email: AnthonyDaniels99@gmail.com

Download source

Download GtCalcApp_20170203.zip

Introduction

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)

Name	Signature	Name	Signature
Add	+	Boolean And	&&
Subtract	-	Boolean Equal	==
Multiply	*	Boolean Not	!
Divide	/	Boolean Not Equal	!=
Exponential	exp	Boolean Or	||
Power	pow	Greater Than	>
Natural Log	ln	Greater Than or Equal	>=
Log2	log2	Less Than	<
Log10	log10	Less Than or Equal	<=
Absolute Value	abs	Ceiling	ceil
Square Root	sqrt	Floor	floor
Truncate	trunc

Trigonometric Functions (CalcStar)

Name	Signature	Name	Signature
Sine	sin	Arc Sine	asin
Cosine	cos	Arc Cosine	acos
Tangent	tan	Arc Tangent	atan
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)

Name	Signature	Name	Signature
Count	count	Odd	odd
Degrees	deg	PI	pi
E Number	e	Radians	rad
Even	even	RowCol (cell)	rc
Factorial	fact	Sign	sign
Maximum	max	St. Deviation	stdev
Mean	mean	Sum	sum
Median	median	Variance	var
Minimum	min
Modulo	mod

Using GtCalc

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

               //in dialog header file
               GtSheetView * m_ptrSpreadSheet;
      
//in dialog cpp file InitializeControls() method
m_ptrSpreadSheet = new GtSheetView(this);
GtRectI rectNew;
       rectNew.xMin = 50; rectNew.xMax = 900; rectNew.yMin = 25; rectNew.yMax = 650;
       m_ptrSpreadSheet->Set_objFrame(rectNew);
//resize the spreadsheet to the desired number of cells
//remember to add 1 because it is 0 based index
       m_ptrSpreadSheet->Resize(21, 21);
       this->AddSubWidget(m_ptrSpreadSheet);

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);
GtRectI rectNew;
       rectNew.xMin = 25; rectNew.xMax = 400; rectNew.yMin = 25; rectNew.yMax = 400;
       m_ptrSpreadSheet->Set_objFrame(rectNew);
//resize the spreadsheet to the desired number of cells
//remember to add 1 because it is 0 based index
       m_ptrSpreadSheet->Resize(1001, 21);
       m_ptrPage1->AddSubWidget(m_ptrSpreadSheet);

GtSheetView is the main object that contains a spreadsheet. GtSheetView has collections of GtSheetRow, GtSheetColumn, and 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 CSFunctionEvaluator from 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.

Equation Sytax

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;

       m_ptrWorkbook->Set_objFrame(rectNew);

       m_ptrBookFrame->AddSubWidget(m_ptrWorkbook);

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.