esCalc Basics: Cells and Data Types

Basics 557 0

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:

esProc_basic_cell_datatype_1

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:

esProc_basic_cell_datatype_2

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:

esProc_basic_cell_datatype_3

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:

esProc_basic_cell_datatype_4

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:

esProc_basic_cell_datatype_5

At this point, you can view or even modify the formula on the formula bar:

esProc_basic_cell_datatype_6

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:

esProc_basic_cell_datatype_7

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:

esProc_basic_cell_datatype_8

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.

esProc_basic_cell_datatype_9

Enter the above strings and formula and you will get cell values as follows:

esProc_basic_cell_datatype_10

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:

esProc_basic_cell_datatype_11

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:

esProc_basic_cell_datatype_12

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:

esProc_basic_cell_datatype_13

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:

esProc_basic_cell_datatype_14

  • 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:

esProc_basic_cell_datatype_15

Enter these strings and you will get cell values as follows:

esProc_basic_cell_datatype_16

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

esProc_basic_cell_datatype_17

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.

esProc_basic_cell_datatype_18

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:

esProc_basic_cell_datatype_19

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:

esProc_basic_cell_datatype_20

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:

esProc_basic_cell_datatype_21

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:

esProc_basic_cell_datatype_22

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:

esProc_basic_cell_datatype_23

And C1’s appearance becomes:

esProc_basic_cell_datatype_24

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:

esProc_basic_cell_datatype_25

And the C1 will be displayed as follows:

esProc_basic_cell_datatype_26

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website