4 User Notes
4.1 Basic Concepts
mtCellEdit is a spreadsheet program. It is designed to handle tables of numbers and text. Mathematical formulas can be used within the table to do various calculations.
4.1.1 The Book
The book is the container for everything that mtCellEdit can edit. A book can contain any number of sheets as well as storing various user preferences. A book can be saved using the CED file format as outline in section A.2. This file format is simply a ZIP file containing various data files.
4.1.2 The Sheet
A sheet is a table of cells which can be edited by the user. A sheet can be loaded and saved using the TSV file format as outline in section A.2. It should be noted that this doesn't store any of the cell or sheet preferences such as column width or text colour so under most circumstances it is better to save files as a book. The benefit of the TSV format is that it can be edited by any text editor and most spreadsheet programs.
4.1.3 The Cell
Each cell contains some text input by the user, which can be interpreted as a number, a date, a formula, or simply as text. A cell that starts with the ' character is interpreted as text. A formula always starts with the = character. A number is any decimal number such as -1.23. Numbers can also be written in hex form using an 0x prefix such as 0x12DE or 0x12de. An infix style formula is entered like this example:
= 1 + 2 + 3^2 - 5.123 * 4
A cell can also be parsed as a date/time if input is in the form DAY-MONTH-YEAR or DAY/MONTH/YEAR. If DAY is greater than 31 then it is interpreted as YEAR-MONTH-DAY or YEAR/MONTH/DAY. The time is parsed when using HOUR:MINUTE or HOUR:MINUTE:SECOND which can appear on its own such as 12:30 (with the date being 1-1-0) or after a date such as 29-12-1873 12:30.
Each cell in the sheet is referenced by its row and column in R1C1 notation. For example r1c1 refers the cell in the top left of the sheet. If the cell r1c1 contains the number -1.23, and r2c1 contains the formula =r1c1, then -1.23 will be displayed in r2c1. This is called an absolute reference because whatever cell contains the r1c1 reference it will always refer to that cell.
Relative references are made by using the form r[-1]c[-1] which is a reference to the row to the left, and column above. A reference to the current row would be rc[-1] and the current column would be r[-1]c.
A useful shorthand is r_c_ which refers to the last row and column in the sheet. This is handy for sum functions when a row or column continues with data for a lot of cells.
All formulas, functions, and cell references return numerical values (or possibly an error). mtCellEdit functions and formulas never return any text, so if cell r1c1 contains "text" and r1c2 contains "=r1c1" then "0" will be displayed in r1c2.
4.2 Running mtCellEdit for the first time
When you run mtCellEdit for the first time you should take the time to customize the preferences to suit your own way of working. Once you do this the preferences are stored in you home directory, such as /home/mark/.config/mtcelledit/prefs.txt
4.2.1 Program Preferences
Whenever you change the size and position of the main window these details are recalled for next time you use them. These preferences are usually stored in the file ~/.config/mtcelledit-qt5/prefs.txt - However this can be changed on startup by using:
mtcelledit-qt5 -prefs ~/.config/mtcelledit/prefs_profile_A.txt
This is a useful way of having different profiles for different jobs, such as using a different font or having different recently used files in the File menu.
4.2.2 Selecting a Font
If you don't like the default font that mtCellEdit uses, open the program preferences via the Options menu, or simply use Ctrl+P. Then type in the text font in the filter entry and press the enter key. You should then see each of the font options. mtCellEdit uses Pango for font rendering so if you want further control over the output you will need to configure fontconfig on your system. Typically this is in a file located at /etc/fonts/local.conf or ~/fontconfig/fonts.conf - see your OS documentation for more details, or perhaps do a web search for "fontconfig settings".
4.3 User Interface
The mtCellEdit GUI is minimal in order to maximize the viewable area. Many of the menu items have keyboard shortcuts which makes certain activities quicker. See Appendix A for further keyboard shortcuts.
When the cursor focus resides in the cell reference entry, the cell text entry, or the find area then you can return the focus to the sheet by pressing the Escape key. This is a simple shortcut that avoids any wasteful and inefficient mouse activity.
4.4 The Clipboard
The clipboard is probably the simplest and quickest way to move data around in mtCellEdit or between mtCellEdit and other programs. I have designed this feature so that mtCellEdit is seemlessly interoperable with Gnumeric, LibreOffice Calc, and other programs that use the system clipboard.
When copying data between two mtCellEdit instances cell preferences are maintained. However when pasting data to and from other programs, cell preferences are lost.
4.5 Operations
The various operations that mtCellEdit can do all require the user to set the cell area first and then request the operation. For example to search for some text in column 5 of a sheet you would do this:
- Select column 5 by clicking on the column header.
- Use the Options->Find menu item to find the text you want.
4.5.1 Sorting
The selected rows or columns can be sorted by selecting the relevant menu item. For example if you wish to sort some rows by two column comparisons you would select the row range and then select two columns. The exact columns used in the comparison for sorting is set in the dialog by the user, along with other options such as sort direction.
4.5.2 Insertion and Deletion
Rows and columns can easily be inserted or deleted by selecting the number of rows or columns and then choosing the relevant menu item. Be aware that mtCellEdit does not change any of the formulas in the cells after such actions, unlike some other spreadsheets. The principle that mtCellEdit works on is that if the user inputs some data into a cell then this data must remain intact until the user explicitly changes it.
4.5.3 Recalculation
When a sheet is recalculated every active formula is recalculated going from the top left down to the bottom right, and then from the bottom right to the top left.
When a book is recalculated every active formula in each sheet is recalculated going from the top left down to the bottom right, and then all sheets from the bottom right to the top left.
By default each sheet is automatically recalculated after each action. This can be changed by adjusting the book preferences so that no recalculation occurs or the whole book is recalculated.
4.5.4 Exporting Sheet Output
Occasionally it is useful to have sheet output in a different context outside mtCellEdit. For example you may want to create a PDF file for printing, an SVG file for embedding into a desktop publishing document, or a raw TSV file to use the exact textual output in another spreadsheet program. In order to create these output files you use the menu item Sheet->Export Output.
4.6 Graphs
mtCellEdit provides support for basic graphing of the data from a sheet. There are three basic graph types:
- Bar graph.
- Line graph.
- Manually created graphs using boxes, ellipses, lines, and text.
The quickest way to understand how graphs work in mtCellEdit is to load the test suite and study the graphs that it contains. To display the graphs you must switch on the graph section of the interface by using the menu item Options->Graph, and then drag the vertical pane slider to enlarge or shrink the graph area as required. You select a graph by using the button menu which appears to the right of the sheet button menu at the top of the main mtCellEdit window.
A graph is defined in a CED file as a block of text. This text contains a sequence of instructions that are carried out in sequence to draw the graph as the user requires. The technical details of what instructions are available, and what attributes each instruction acts upon are outlined in the Appendix section of the handbook.
4.6.1 Creating a Graph from scratch
Once you have a sheet full of data and you want to display this data in a graph, you must switch on the graph section of the interface by using the menu item Options->Graph, and then drag the vertical pane slider to enlarge or shrink the graph area as required.
A Graph menu title should then appear which allows you to create and manipulate graphs.
4.6.2 General Workflow
Once you have created a new graph, a skeleton is created for you to build from. You can now change the title, adjust the axis limits, change the axis titles.
At this point if you want to create a simple bar graph you would do the following:
- Select the data you want to graph in the sheet area.
- Select the menu item Graph->Sheet selection to clipboard.
- Click on the graph text area, move the cursor to the bottom of the text chunk and press Ctrl-V to paste the data reference.
- Press Ctrl-S to save your work, and the graph should automatically be redrawn.
Manual redraws can also be executed by using the menu item Graph->Redraw. If there are any errors then the graph area will go grey and the selection in the text area will display the code that has not been executed.
4.6.3 Exporting a Graph
Once you have created a graph you are happy with, you may wish to use the output in another context such as a desktop publishing document, or possibly to print it. To do this you use the menu item Graph->Export which allows you to export the graph to various file formats such as PDF, SVG, and PNG.