Workbook Class

A workbook is a container for multiple worksheets. It can be saved to and opened from a particular directory. The extension of a workbook must be swb.

 

Creating & Closing

The following command creates a workbook and shows it to the user as shown in the following image.

>> wb=Workbook.new()


Fig 1: Creating a workbook using Workbook.new command

It should be noted that the newly created workbook, namely "Workbook 2", is owned by Lua since it was created using a command.

 

Ownership: There are two types of ownership for a workbook and the ownership is rather important when closing a workbook:

  1. Sytem: The Workbook is created using the Workbook ribbon page as shown below:

     

  2. Lua: The Workbook is created via command-line or using a script.

 

In some cases, the workbook can be shown after processing data (reading/writing). In order to create a workbook without showing it to the user:

>> wb=Workbook.new(false)

 

When closing a workbook, the ownership matters. Consider the two workbooks shown in Fig 1, where "Workbook 1" is owned by System and "Workbook 2" is owned by Lua. If we were to execute the following code, where wb1 is a system owned workbook and the code executes its close member function, an error will be thrown:

>>wb1=std.activeworkbook()
>>wb1:title()
Workbook 1
>>wb1:close()
ERROR: The workbook is not owned by a script. You can only close it via close button.

It should be noted that System owned workbooks can only be closed via the close button, as long as there is more than 1 System owned workbook. Furthermore, regardless of the number, Lua owned workbooks can always be closed through close button.

 

Member functions

 

add

add(name="", row=1000, col=100) → Worksheet

Adds a worksheet to the workbook.

If argument name is not provided, then the name of the worksheet is assigned automatically. Note that worksheets in a workbook must have a unique name.

 

 

close()

close()

Closes the workbook. If the workbook has any changes on it, then a Save prompt will be shown to be able to save the changes.

>>wb=Workbook.new()

>>type(wb)
Workbook

>>wb:close()
>>type(wb)
userdata

It should be noted that once a workbook is closed, there will be no access to the variable as type Workbook. The memory associated with it will be destroyed and its metatable will be stripped off. Please also read the section on closing a workbook.

 

 

count()

count() → integer

Returns the number of worksheets in the workbook.

 

 

cur()

cur() → Worksheet

Returns the active worksheet. It is the worksheet currently opened by either the user or the script.

 

 

del

del(arg)

where type arg can be string, integer or Worksheet.

 

After manually adding worksheets and running the following commands the workbook looks like the one shown in figure:

>>wb=std.activeworkbook()
>>ws=wb:add("script")

 

If type(arg):

  1. string: Deletes the worksheet with the given name.

    >>wb:del("ab")

     

  2. integer: Deletes the worksheet specified by its location at the tabbed bar. The worksheet at the very left is number 1 and the one on its right is number 2 and so on.

    >>wb:del(1)

     

  3. Worksheet: Deletes the worksheet specified by its variable. Here ws is a variable, of type worksheet.

    >>wb:del(ws)

 

 

id

id() → integer

Every workbook, regardless created by a Lua command or by using the new button, has a unique ID associated with the workbook. The function returns the ID of the workbook, which is an integer number.

>>wb=std.activeworkbook()

>>wb:id()
1

>>wb2=Workbook.new()
>>wb2:id()
2

 

 

open

open([path=])

Opens the file with .swb extension from the specified path. The path variable, if provided, is of type string and it might be either a fullpath or a path relative to the executable file, such as path="myfile.sswb". It might be a better approach to create a Workbook without initially showing it to the user and then opening the file at the specified path. After all these processed, then it might be shown to the user.

>>wb2=Workbook.new(false)

>>wb2:open("C:/Users/gbingol/Desktop/demo_data.sswb") -- Please note the directory separator is / and not \.
>>wb2:show()

 

Sometimes, typing the full path can be cumbersome or you might want user to choose the path. The following is doable too:

>>wb=Workbook.new(false)
>>wb:open()

It is seen that the user is prompted to select a Workbook file.

 

 

save

save([path=])

Saves the workbook. If the argument path, which must be of type string, is not provided, then a Save dialog is shown to the user. If the file was previously saved to or opened from a sswb file, the argument path must not be provided as this will throw an error.

>>wb=Workbook.new(false)

>>ws=wb:cur()

>>ws[1][1]="Hello"

>>wb:save()
>>wb:close()

After the command wb:save(), the Save dialog will be shown to the user. Once the user saves the file, the next line, wb:close(), will be executed.

 

 

show()

show()

Shows the workbook, changes its state from hidden to visible.

 

 

title()

title() → string

Returns the title of the Workbook. If the document is not opened from a directory then the title is the one that was automatically assigned, otherwise returns the full path.

 

>>wb2=Workbook.new(false)

>>wb2:title()
Workbook 2

>>wb2:open("C:/Users/gbingol/Desktop/demo_data.sswb")

>>wb2:title()
C:\Users\gbingol\Desktop\demo_data.sswb