A band is the elementary structure unit of an esCalc spreadsheet. So editing a band is the basic operation on a sheet. Different from editing records during which you add or delete data, editing a band means reconstructing it. In examples of this article, Ctrl+Enter will be used to insert records. You can learn more from esCalc Spreadsheet Editing: Records.
1. The structure of bands
A band consists of a master row, slave rows and their sub-rows. Editing the structure of a band means various operations on the master row, the slave rows and the sub-rows. Here is a newly-created esCalc spreadsheet:
In this new sheet, the first row and the third row are level-0 rows with their slave row numbers respectively being 1 and 2; the second row is a level-1 row with slave row number as 1 and it is the sub-row of row 1.
To edit the structure of a certain row’s band, right-click on the first cell or any other cell of this row and select one from the various operations in the Structure item in the context menu:
Or select a cell or this row, click the Structure option on the menu bar and choose your desired operation.
As homo-rows are necessarily of homo-structure, in performing editing operation on a row, if it has homo-rows, all of its homo-rows execute the same change together.
2. Appending sub-rows and dismantling the maser row
2.1 Appending sub-rows
In the new table, select the second row and press Ctrl+Enter to insert another row. Then enter population data in them as follows:
Now add population data of some big cities to the data of each state. The city population records are sub-rows of state population records. You can add them with Append sub row. One execution of Append sub row adds one sub-row to the current row. If the current row has already had sub-rows, they will move down one level to become the sub-rows of the newly added sub-row.
Choose a cell in the second or the third row and select Append sub row from the context menu. Here is the result:
You can see that all the cells in the appended sub-rows are in its original blankness. Select the fifth row and insert another row to the state of Illinois with Ctrl+Enter. And then change the background color of the cells of city data and enter data to them, as shown below:
Select the second row and execute Append sub row again to add another sub-row under the state data:
As can be seen, the sub-rows that the second row and the fourth row have already had go down to a lower level to become the sub-rows of the newly added sub-rows.
2.2 Dismantling the master row
The Dismantle master row operation is an inverse process of the Append sub row operation. It deletes a master row that has sub-rows but no more slave rows, upgrading its sub-rows to the sub-rows of its parent row. Select Structure>Dismantle master row on the context menu or the menu bar to dismantle a master row.
Note that only a row that qualifies itself for the operation is allowed to select the Dismantle master row option. Such a row must be a master row having at least one sub-row and the parent row. For example, only row 2, row 3, row 5 and row 6 in the above cellset are eligible for the operation. If performing the operation on the third row, you’ll get the following result:
It can be seen that the third row as well as the sixth row, its homo-row, are removed. Their sub-rows move up to a higher level to become the sub-rows of their parent rows, the second row and the fourth row respectively. If a master row has slave rows, it is forbidden to be dismantled before the slave rows are deleted. The editing of slave rows will be covered in the next section.
3. Adding, moving and deleting slave rows
3.1 Appending slave rows
Appending a sub-row into which sub-data can be entered will increase the number of levels under a specified row, but appending a slave row will add a new row to the current level. For instance, you want to add a slave row to be the title row of the state data in the preceding table. To do this, you can select Structure>Append slave row on the menu bar or the context menu to add a slave row after the current row. The sub-rows of the current row, if any, will move down under this slave row.
Select the fourth row, say, and execute Append slave row, and you’ll get this:
You see that a slave row is added after both the second row and the fourth row of the original table. Each is in the same level with the original row 2 and row 4, and has the same appearance properties, such as background color, as the cells in the selected row that sit on the same columns. The operation sends the sub-rows of the second row and the fourth row respectively down under the new slave row.
If you select multiple continuous slave rows of the same level (master row is a kind of slave row) together and perform appending slave rows, the same number of slave rows will be added.
3.2 Inserting slave rows
After you change appearance properties of the new slave rows, type column names and new data into them, the table will like this:
Now you want to add a summary row for each state to summarize the city data, such as calculating the total population in the cities. Suppose you don’t want to move the sub-rows of the existing slave row, you can use Insert slave row. To do this, choose Structure> Insert slave row on the menu bar or the context menu.
Select, say, the third row in the above table and execute Insert slave row, you’ll get the following result:
You see that before both the selected row and its homo-row, a new slave row is created. This row takes on the same appearance as the selected row and possesses no sub-rows.
If you select several continuous slave rows of the same level simultaneously and perform the insert operation, the same number of slave rows will be inserted.
Change the background color of the slave rows just inserted and enter formulas ={A5}.count(),={B5}.sum() and ={C5}.sum() respectively into A3, B3 and C3. Here is the result you’ll get:
3.3 Moving slave rows
You may find that though the newly inserted slave rows haven’t their own sub-rows, they sit before the rows of city data. To move a slave row within its band, choose Structure>Move slave row up/down on the context menu or the menu bar. By doing so, you can move a slave row up or down, along with its descendant rows.
Suppose you select the eighth row in the above table and execute Move slave row up to move the city data up, you’ll get a result as follows:
As can be seen from the resulting table, the eighth row, joined by its descendant rows, moves one position up within the band and its slave row number changes from 3 to 2. Its homo-row – the fourth row, together with its own descendant row, also moves up one position within the band. The sequence number of a slave row becomes smaller after the row is moved up, so you cannot execute the moving up operation on a master row.
You can get the same result by selecting the third row, or the summary row of the state data, and executing Move slave row down in the table before the previous one. Contrary to the moving up operation, the moving down operation increases the slave row number. You cannot perform the moving down operation on the last slave row in a band.
Check the formulas in the cells in the fifth row and the tenth row and you can find that they still reference the correct cells, such as C10’s formula ={C8}.sum(). Actually they can intelligently adjust themselves during adding or moving slave rows.
3.4 Duplicating slave rows
Besides appending and inserting slave rows, you can add slave rows by duplicating them. On the menu bar or the context menu, select Structure>Dup slave row to copy the selected slave row and its descendant rows.
In the preceding table, select the seventh row and execute Dup slave row and you’ll get the following result:
The seventh row and its homo-row, the third row, are copied to become the new slave rows. Their descendant rows are copied too. The slave row numbers in a band will change accordingly after the addition of a new slave row.
3.5 Deleting slave rows
To delete a slave row and its descendant rows in a band, choose Structure>Remove slave row on the menu bar or the context menu. The selection of several slave rows in a band (this may include the master row or their descendant rows) will result in the deletion of all of them.
In the above table, select the second row and the third row together and execute Remove slave row, and the result will be like this:
The selected rows, their descendant rows and homo-rows have all been deleted. If a slave row is deleted, the slave rows after it in the same band of the same level will move a position up in sequence. Here, for instance, the slave row numbers of the fifth row and the twelfth row move up from 3 to 1; those of the seventh row and the fifteenth row move up from 4 to 2.
Different from dismantling a master row, removing a slave row does not require that there is only the master row of the same level besides this slave row in the band. The descendant rows of a selected slave row will be removed together.
You may notice that, in the fourth row and the eighth row, there is a red triangle on the bottom right corner of every cell that has a formula, indicating that the cell referenced by it has been deleted. But since a normal calculation cell only calculates when the formula is entered or modified, those cells are able to retain their data.
4/ Operations on rows and columns
4.1 Setting basic properties
In a cellset, click the first cell of a row to select the whole row and the first cell of a column to select the whole column; right-click the first row cell or column cell to select an operation from the context menu, as shown below:
To edit the row/column properties, you can also select a row/column, click Edit>Row & column on the menu bar and select the operation you want to perform.
Select Property to edit the properties of a row or a column and set the row height, column width and page-break after row (or column). This was discussed in esCalc Structure: Functions.
Select Autosize to set the row height or column width automatically, defining a size that is just enough to hold the text in the selected row/column. If the cells in the selected row/column are empty, instead of setting the value as 0, the execution of Autosize will not change the row height or column width. If the height of a row is changed, its homo-rows will undergo the same change. The row height of the current row is generally set as the most suitable one.
4.2 Hiding and reshowing slave rows
Select Hide or Show from the list of row properties to hide or reshow a selected slave row.
In the following table, let’s look at how the two options are used:
In this cellset, the formulas in A5, B5 and C5 are respectively =={A4}.count(),=={B4}.sum() and =={C4}.sum(). They are related calculation cells with blue triangles at their bottom right corners. Their calculations change if values of the cells referenced by these formulas change. For instance, select the fourth row and press Ctrl+Enter to add a city record to the data of the state of Florida and modify the statistics of Chicago city into 0.Then you’ll get the following result:
The results of the related calculation cells have changed accordingly. Now select the second row and the third row to hide them. This is the result you’ll get:
You can see that the two rows, as well as their homo-rows – the seventh and the eighth row, are hidden. In hiding a slave row, the selected row and all it homo-rows will be hidden at the same time, but their descendant rows will not be hidden.
The results of related calculation cells in the sixth row and the eleventh row remain consistent, because the hiding will not make the slave rows vanished and their row numbers and slave row numbers stay the same. When a slave row is hidden, it will not be presented, printed out or exported. But the data is still there and can be called by the formulas.
In the above table, select the sixth row and the ninth row together and choose Show on the context menu to cancel the hide operation, and you’ll get this result:
Through this operation, you reshow the seventh and eighth row between the sixth and ninth row, as well as their homo-rows – the second and the third row. This means the show operation will cancel not only the hiding of the specified row but that of its homo-rows.
4.3 Operations on columns
Without considering the hierarchy or homo-rows, it seems simple to handle the structure of columns. You can hide or reshow a selected column by choosing Hide or Show in the column property list.
In addition, you can choose Insert column or Append column from the Structure item on the menu bar to add a new column before or after a selected column. The new column has the same appearance properties and width as those of the selected one.
Choose Structure>Move column left/right on the menu bar to move a selected column left or right.
To delete columns that are not needed, choose Structure>Delete column.