Fig A
Fig B
Workbook is very useful to store, format and manipulate data. The workbook can contain several worksheets and each worksheet can contain several ranges. Except the deletion of a worksheet, the workbook supports undo/redo for all operations.
The data in a workbook can be saved into the project file, namely sproj, using the commit button. Once the project is saved, any work committed from the workbook will be saved as well. In order to load the project file, just open it from recents or locate it on the drive.
Data in a worksheet can be formatted and the formatted data can be transferred to MS Excel. The formatting panels are shown below:
Following are the possible formatting options:
As you move the cursor to different cells, the panel will automatically be updated with the format of the active cell.
Once a data range is selected, the dimension of the selection and some essential measures of the data is immediately shown to the user at the status bar.
Although, within a Worksheet, the selection can be moved/copied to another location using the mouse, the selected data set in the worksheet can also be carried to a different location or to a different worksheet using copy-cut/paste options. Regardless of the dimension of the data, if "Copy/Cut" is selected, 3 different formats are loaded to OS's clipboard:
Besides the "regular" copy command, Workbook also supports copying a range as bitmap using "Copy As Image" as shown in the following figure:
Copying a range such as shown (Fig A) in the following figures will produce an image similar to Fig B. It is seen that the format and alignments are preserved.
Fig A
Fig B
Upon using the "copy" command, there are 3 paste options as seen in the following figure:
By default, a Worksheet contains 1000 rows and 100 columns. In order to insert or delete rows or columns, at least 1 row or 1 column must be selected. Right-clicking with the mouse will show the following menu:
For example, if n rows are selected, then n rows will be inserted or deleted. Similar rational holds for columns as well.
Every now and then we need to separate (tokenize) data in a text to be able to use the particular data entries separately. However, in a text the data is combined by many delimiters, such as tab, comma etc.
For example, observe the data that is in a text file (such as Notepad) shown in the following figure. Here, if we would like to have, and most of the time it is indeed useful to have, "student" and "score" in different columns in a worksheet, then we need to tokenize the text. Please also note that, in the following figure the data is mostly separated by tab characters except "A,B" which is separated by comma:
In cases, where text needs to be tokenized, ScienceSuit will show a wizard as shown below. Each line in the text will be written in a row, such as first line to row 1, second line to row 2 and so on.
"Tokenize the Whole Text Using Delimiter": The selected delimiter will be used to tokenize the whole text file. If a different delimiter is selected, then the text will be separated based on the newly selected delimiter and the outlook of the grid will also change.
In the above figure, although the initial outlook looks fine, we observe that "A,B", which is separated by comma, is not separated as the main tokenizer is the "tab". If we change the main delimiter from tab to comma then we will have the following outlook:
Note that, although this time "A,B" is separated well, the rest of the text is not well separated at all.
>Note:For convenience of visualization, when data is separated by tab characters and the main delimiter is different than the tab, the tab characters in the text are replaced by space characters. Otherwise we would have seen the entry, the second row, "1 18" as "118" which would have made it difficult to make any inferences.
Let's go back to the initial selection and select the delimiter as "tab" and select the cell containing the text "A,B" and then right-click. As shown in the following figure, a menu will pop-up:
If we select the comma option for this particular cell, then the content of the cell will be separated by using comma and each separated text will be inserted starting as of the selected cell's column's position.
The idea can be applied to any cell containing text. Therefore, we have full control over the text.
After separating using comma, we will have the following:
Once the OK button is clicked, a new Worksheet will be added with the name "data.txt" as shown in the following figure:
It is used when you have text in the rows of a column and you wish to separate the text to multiple columns. For example, say, you have the following rows of text in a worksheet:
To be able to separate the above shown text into columns:
1) Select the rows you wish to tokenize and click on the "Text to Columns" button in the Workbook page as shown below:
2) This will show the following wizard (please see the Tokenizing Text for more information)
3) After tokenizing the way you wish, it will be converted to multiple columns, possibly, as shown below:
Note: Initially the selection was tokenized using the comma delimiter and then the cell A3 was selected and was tokenized using equal sign delimiter.
Finally, please note that, before converting the text to multiple columns, if you already have data, say in B1, then a confirmation would be required, as shown below, before overwriting the existing data.
If confirmed, the data in cell B1 will be overwritten, otherwise, the operation will be canceled.
It might sometimes be needed that after performing some calculations the variable holding the data should be saved or visualized in Workbook or exported to some other file. Importing the variable from workspace to the worksheet also enables analyzing the variable(s) using the apps.
Although this could have been done using some scripting and the functions provided by std library, it is not convenient to do so every time. Clicking import button shows two options as shown in the following figure:
After selecting the "From Workspace", the following dialog is shown:
The variable name must be relative to the global table. For example consider the following table declaration:
>>var1={a=1, b=2, 3}
If you would like to import contents of var1, then the variable name entered should be var1.
However, in the following case, var2 is a table inside another table, namely tbl:
>>tbl={}
>>tbl.var2={x=1, y=2, z=3}
if you would like to import contents of var2, then the variable name entered should be tbl.var2.
Notes:
By simple tables, it is meant that, tables without any metamethod. For example:
>>simple={a=1, b=2, c="a string", 3, 4}
The table, namely simple, will be imported as shown in the following figure:
| Column A: | The keys of the table |
| Column B: | Corresponding values to the keys. |
| Note that the numbers 3 and 4 in the table, do not have explicit keys, but keys have been assigned to these values automatically. | |
When a table have metamethods, there are two types of scenarios that can happen:
Case 1: __tostring() function not implemented:
Consider the following table, namely tbl, implemented as follows:
>>tbl={a=1, b=2}
>>meta={}
>>setmetatable(tbl, meta)
>>function meta:__name() return "justtable" end
Here, we can see that the table, namely tbl, has a metatable, namely meta and has implemented a single metamethod, namely __name().
However, since __tostring() metamethod is not implemented, if we attempt to import the table, tbl, an error message will be issued and nothing will be imported.
Case 2: __tostring() function implemented:
Behind the scenes, the Food data structure is actually a Lua table with many metamethods. If we create a Food variable, namely food, as shown below (for brevity only 2 metamethods is shown):
>>food=std.Food.new{CHO=80, water=20}
>>getmetatable(food)
__tostring=function
__name=function
and notice that the __tostring metamethod is implemented. Therefore, if we import the variable food, it will be imported as shown in the following figure:
Note: If __tostring() metamethod uses "\n" for to separate the lines then each line will be written on a row as shown in the figure.
Two userdata types, namely Vector and Matrix are handled specially. For the other userdata types, if the userdata implements __tostring() metamethod, the string returned by the __tostring() will be imported. Otherwise, an error will be issued.
For example, behind the scenes Workbook data structure is actually of type userdata with metamethods implemented. If we create a variable, namely wb, of type Workbook:
>>wb=std.activeworkbook()
>>getmetatable(wb)
and then import the variable, namely wb, it will be imported as shown in the following figure:
Only the Vectors with a size less than 100 000 elements can be imported. When the elements of the vector are imported to the workbook, contents of the vector will be written to cells in a row-fashion, for example, first 100 elements to column A, second 100 elements to column B and so on.
>>vector=std.rand(10)
Now consider another Vector, again namely vector, which has 325 elements (bigger than the previous):
>>vector=std.rand(325)
As seen below (please note that only part of the image is shown and the rows from 10 to 101 is not shown), when imported an extra information is printed on the worksheet.
Only Matrix with number of rows≤1000 and number of columns≤100 can be imported. Unlike Vector, there is no special pattern to import a matrix.
>>matrix = std.rand(4, 3)
When imported, the variable, namely matrix, will be imported as shown below:
Data files can come in different formats and it is essential to be able to import to/export from ScienceSuit workbook. The import and export functionality is located under "Workbook" Ribbon Page.
When data is imported, ScienceSuit will add new worksheet(s) to the active workbook. If there is already an existing worksheet with the same name, the current date will be appended to the imported worksheet's name.
In order to import a file, on the Workbook ribbon page, click Import button. This will show the following dialog box:
As shown in the following image, currently there are 3 types of files that can be imported:
The difference between opening and importing ScienceSuit workbook is when imported the imported Workbook's worksheets will be added to the current active Workbook. However, when opened, a new workbook will be opened.
Tip: If you want to work only with a single workbook at a time, instead of opening a workbook, importing it will be more convenient.
CSV files are among the portable file formats among different software and operating systems. For example, if you have data in Excel as shown in the following figure (notice the existence of non-ASCII character, μ) :
If it is exported as UTF-8 supported CSV file using Excel (filename is Book1.csv), then it can be imported using ScienceSuit as seen in the following figure:
Notice that the Worksheet name is automatically assigned as the filename, Book1.csv.
Similar to CSV files, text files are among the portable file formats among different software and operating systems. For example, data in a text file (such as Notepad) is shown in the following figure (note that the data is mostly separated by tab characters except "A,B" which is separated by comma):
Unlike CSV files, in which separation is well-defined with a single delimiter, in text files, the data can be separated by many delimiters, such as tab, comma etc. Therefore, when you import a text file, initially an import text wizard is shown (please see tokenizing text for more details). Once the text is tokenized, a new Worksheet will be added with the name "data.txt" as shown in the following figure:
When data is exported from the active worksheet, the relative positions of the cells are preserved; however, the absolute position is not. Therefore, a more compact text file is obtained.
For example, consider the layout in the following worksheet:
After exporting Sheet 1 as text file and opening it with Notepad, the following layout will be observed:
Note that, although the relative positions of the cells are preserved, the absolute positions are not. For example, B4 is placed directly at the first column in the notepad.
Similar to text files, when data is exported as CSV, the relative positions of the cells are preserved; however, the absolute positions are not. Should the absolute positions be preserved, a single data point in a worksheet might have caused large file size when exported.
Consider the following layout of cells in ScienceSuit:
Exporting Sheet 1 as CSV file and then importing the CSV file using Excel, will lead to the following layout:
