13,668,134 members
Article
alternative version

#### Stats

11.1K views
11 bookmarked
Posted 22 Dec 2016
Licenced MIT

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

, 3 Feb 2017
GtCalc is a spreadsheet library for the GT Graphical User Interface Library

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

## Introduction

`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

 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

//in dialog cpp file InitializeControls() method
GtRectI rectNew;
rectNew.xMin = 50; rectNew.xMax = 900; rectNew.yMin = 25; rectNew.yMax = 650;
//resize the spreadsheet to the desired number of cells
//remember to add 1 because it is 0 based index

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;
//resize the spreadsheet to the desired number of cells
//remember to add 1 because it is 0 based index

`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);

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.

## Share

 United States
No Biography provided

## You may also be interested in...

 Pro Pro