Essentials

Workbook is very useful to store, format and manipulate data. Each instance of ScienceSuit has only 1 workbook. 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.

 

1) Basics

A) Saving / Opening:

The work done in a workbook can be saved into the sproj file using the commit button. Once the project is saved, any work committed will be saved as well.
In order to retrieve the saved-contents, run ScienceSuit first and then opening the project file via Ope

 

B) Formatting Cells

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:

  1. Font: Family, size, color, style (italic), decoration (underline) and weight (bold).
  2. Color: Background color
  3. Alignment: Vertical (bottom, center, top) and horizontal (left, center, right)

As you move the cursor to different cells, the panel will automatically be updated with the format of the active cell.

 

C) Selection of Cells

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.

 

Depending on dimension of the selection selection (compare Fig A with B) clicking the right button of the mouse will show a menu to allow the user to create variables (such as Vector, Matrix, Array, Range) which can be manipulated by command-line. This feature brings a significant convenience when working with data sets (for more details).

 

D) Copy/Paste

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:

  1. HTML Code: Which provides convenience to embed in an HTML page.
  2. XML Code: When the data is pasted ScienceSuit parses the XML code and formats the data accordingly.
  3. Text Code: Tab separated format.

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:

  1. Directly clicking the Paste button will paste values and format
  2. Selecting "Paste Values" will only paste the values.
  3. Selecting "Paste Format" will only paste the format. Therefore, any value entered in the pasted region's cell will have the similar format from where it was copied.

 

E) Inserting/Deleting Rows and Columns

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.

 

 

2) Creating Data Structures

Although it is very straightforward to create an empty Matrix, Vector and Range using command line most of the time you will need to deal with data already entered to a spreadsheet, say Excel. The following steps will show you how to create a Vector, the rationale is the same for Matrix and Range.

  1. Copy and paste the data to a worksheet in ScienceSuit.
  2. Select data part of "Group 1" and right-click. You should see the following:
  3. Once you click on "Create Vector Variable" the following input box will popup. Enter the variable name as shown, here the variable is v1.
  4. You are ready to use v1 as a vector variable.

 

The steps for creating a Matrix or a Range is exactly the same as creating a Vector. However, needless to say, but for the Matrix data structure both the number of rows and columns selected must be greater than 1 in order to see the "Create Matrix Variable" option.

 

 

3) Tokenizing Text

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:

 

 

4) Convert Text to Columns

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.

 

 

5) Import Data from Workspace

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:

  1. The variable types that can be imported are Lua tables and userdata. Import tool handles tables and userdata differently.
  2. The depth of the import goes only single level. For example, if you have a variable such as level1={a=1, b=2, level2={x=3, y=4}}, the import tool will not import contents of level2 and will mark its as a "table". However, contents of level1 will be imported as shown in the following figure:


    Note that, contents of level2 is just shown as "table". If you would like to import contents of level2, then you would have entered the variable name as level1.level2

 

A) Lua Tables

A.1) Simple tables

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.

 

A.2) Tables with metamethods

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

 

B) Userdata

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:

 

B.1) Vector

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.

 

 

B.2) Matrix

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:

 

 

6) Import/Export Data from/to External Resources

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.

 

A) Importing Data

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:

 

A.1) ScienceSuit Workbook

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.

 

A.2) CSV Files

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.

 

A.3) Text Files

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:

 

B) Exporting Data

 

B.1) Text Files

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.

 

B.2) CSV Files

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: