esCalc Basics: Installation and Usage discussed how to install esCalc and use it to make spreadsheets. The spreadsheet data may be of any data type and can be displayed in various appearances. This article mainly covers most used data types in esCalc and the setting of cell properties.
1. Data in the cell
In esCalc, data can be stored in the form of a string in a cell. In this case, the cell is termed constant cell. Select a cell and you can set its Data type in the cell property section on the right:
If the data type is set as Text, cell values will be parsed directly as strings.
If the data type is set as default Auto, cell values will be parsed automatically into various data types according to different value types, and the unrecognized will be parsed into the strings. For example:
In the cells above, cell values in A1, B1, C1 and D1 will all be parsed into real numbers. Among them C1 is a real number displayed in scientific notation and D1 is a percentage. Both values in A2 and B2 will be parsed into strings. A3’s value will be parsed into a date and B3’s value will be parsed into a time. Notice that the -4.45E3 in C1 will turn into a normal real number -4450.0 and the 3% in D1 will turn into 0.03.
– Note: the per cent “%” is allowed only in a constant cell; it is forbidden in an expression.
You can also use the constant reserved words in cells. Note that they are case-sensitive and can only use the lower case!
- null Null value. The value of an empty cell is also represented by the null.
- true True
- false False
The handling of data or expression in a cell is irrelevant to the hierarchical structure of the esCalc spreadsheet. So the hierarchy will not be shown in the rest of the examples.
String constant cell
You can define a string constant cell by changing a cell’s data type to Text, or get one when the data type is Auto. The cell that contains a string starting with an English-style single quotation mark ‘ is a constant cell whose cell value is of string type. The cell value consists of the string after the single quotation mark, in which all characters will be parsed into string data. In this case it is not necessary to use special characters like quotation marks or escape character.
As soon as the string for the string constant cell is entered, the actual cell value is automatically calculated and displayed, that is to say, the ‘ heading the string is removed. But the cell value displayed in the formula bar on the top and the in the cell property section on the right is still the originally set string that starts with ‘. For example:
In the previous example, A1, B1 and C1 are all string constant cells. A string constant cell is very convenient for defining a string containing special character, starting with an equal sign, or consisting of numbers. It appears that C1 and D1 display the same value. Actually C1 contains a string but D1 contains a real number.
Calculation cell
In a cell, if the string starts with “=”, the string after “=” will be parsed into an expression, whose calculation result is the cell value. This cell value expression is also known as the formula, and the cell is termed calculation cell. For example:
Cell value will be calculated and displayed in the cell after you enter the formula. Both A1 and B1 will display the calculation results of the formulas as soon as they are entered:
At this point, you can view or even modify the formula on the formula bar:
All formulas in this article are simple. A deeper discussion about formulas will be covered in esCalc Basics: Formulas.
2. esCalc data types
In esCalc, there are the following several data types:
- Integer
Any integer between -231 and 231-1, i.e. the value range is -2147483648~2147483647. You can use the type conversion function int() to convert other data types to integer.
- Long integer
The value range is -263~263-1, which is bigger than the integer type. You can use the type conversion function long() to convert other data types to long integer.
Particularly, long integer can be represented by appending a capital letter L to the integer. Compared with an integer, a long integer has a bigger value range. For example:
Since the value range of normal integers is -231~231-1, i.e. -2147483648~2147483647, B1’s result is beyond the range. While by using a long integer in A1, the value range is increased to -263~263-1 and correct result can be obtained. 12345678900 in C1 is beyond the value range of a normal integer and will be automatically parsed into a long integer to do multiplication, and a correct result can also be obtained. Then enter the formulas and you can get cell values as follows:
During the execution, if one of the operands involved is a long integer, the result will be a long integer.
- Floating-point number
64-bit floating-point number is the most commonly used data type in esCalc. All decimal related calculations are performed with this data type. You can use the type conversion function float() to convert other data types to floating-point number. Because the floating-point number is used to store data according to the binary rules, there could be errors in the calculation.
Enter the above strings and formula and you will get cell values as follows:
B1 contains the floating-point number type data represented in scientific notation. C1 is represented by a percentage. The per cent % can only be used in a constant cell and is not allowed in a calculation cell.
- Big decimal
Big decimal can be used to store any real number error-freely, but more memory could be consumed when using big decimal in computation and the computational efficiency is relatively low. You can use the type conversion function decimal() to convert other data types (like a string) to the big decimal. For example:
In calculation cells B1 and D1, the A1 and C1 in the formulas are used to reference the cell values of A1 and C1. The 5.2 in A1 is a floating-point number and C1 converts the string “5.2” to a big decimal. Enter all these strings and you get cell values as follows:
The displayed value in B1 is the same as that in D1, but double-click B1 and you may find that the actual value is not that. This is caused by the use of binary processing that may result in accuracy error in floating-point number arithmetic. However, this does not happen in the big decimal arithmetic.
- Real number
The real number covers four data types: integer, long integer, floating-point number and big decimal. You can use the type conversion function number() to convert other data types to real number.
- Boolean
It includes true/false. For example:
C1 calculates true or false for 4>2. D1 calculates if 13 are divisible by 3. Enter these strings and their cell values are as follows:
- String
Use double quotation marks for an expression, in which the escape character is \. If the string is defined in a constant cell directly, the double quotation marks are omitted. You can use the string() function to convert other data types to the string. To concatenate two strings x and y in an expression, add the space between them, like x y. The string escape rules are the same as JAVA’s. You can see User Reference for details. For example:
Enter these strings and you will get cell values as follows:
- Date/time
Data is in the yyyy-mm-dd or hh:mm:ss format. You can use type conversion functions date(),time(), and datetime() to convert the string or long integer to date, time, or datetime, like values of A3 and B3 in this article’s first example.
Click Tool -> Options on the menu bar to set the format of time/date data, the character code, and etc. on the Environment tab.
3. Cell properies
esCalc cells have a lot of properties. You can view and configure them in the cell property section on the right side.
Cell properties mainly include three categories: cell value setting, cell appearance and text setting.
In cell value setting section, you can view and modify Value, Data type, Format and Code table of the cell, as well as other properties related to cell values and value entry. Set cell values and expressions, for example, behind Value property. Data type is for controlling the way of parsing cell values, which was explained in the first section. Format controls the value format displayed in a cell, especially the format of numeric values. For example:
Set display format for A1 and B1as #,###.#, which means separating the integer part with commas every three digits and retaining one decimal place at most. By doing so, cell values will be displayed in accordance with the format, without changing the actual values. The results are as follows:
To use the code table, first configure the Cellset code table. Click Tool>CellSet code table on the menu bar, name the newly created code table, and then enter data in the window below, using carriage return as the separator. For example:
If setting Code table as table 1, the newly created table, for C1, you can select a value for C1 from the data in code table:
In cell appearance section, you can view and modify cell properties like foreground/background color, font name, font size, and font style. Modify C1 foreground color, background color, font and font style as follows:
And C1’s appearance becomes:
In paragraph setting section, you can view and modify the properties of text in the cell such as wrap text, horizontal alignment, vertical alignment, and indent. Modify C1’s text as middle alignment:
And the C1 will be displayed as follows:
