2.7.2.1 Basic Worksheet Operation

The basic worksheet operations include adding worksheet to workbook, activating a worksheet, getting and setting worksheet properties, deleting worksheet, etc. And these operations can be done by using Page and Wks objects, together with some Data Manipulation X-Functions. Some practical examples are provided below.

Add New Worksheet

The newsheet X-Function can be used to add new worksheets to a workbook. 

// Create a new workbook with 3 worksheets, 
// and use "mydata" as long name and short name
newbook name:="mydata" sheet:=3 option:=lsname;
// Add a worksheet named "source" with 4 columns to current workbook
newsheet name:=source cols:=4;

Activate a Worksheet

Workbook is an Origin object that contains worksheets which then contain columns. Worksheets in a workbook are internally layers in a page. In other words, a worksheet is derived from a layer object and a workbook derived from a page object. The active layer in a page is represented by the page.active or page.active$ property, and thus it is used to active a worksheet. 

// Create a new workbook with 4 worksheets
newbook sheet:=4;

page.active = 2;	// Active worksheet by index
page.active$ = sheet3;	// Active worksheet by name

Modify Worksheet Properties

Using Worksheet Object

When a worksheet is active, you can type wks.= and press Enter to list all worksheet properties. Most of these properties are writable so you can modify it directly. For example: 

// Rename the active worksheet
wks.name$ = Raw Data;
// Set the number of columns to 4
wks.ncols = 4;
// Modify the column width to 8 character
wks.colwidth = 8;
// Show the first user-defined parameter on worksheet header
wks.userparam1 = 1;

Two properties, wks.maxRows and wks.nRows are similar. The former one find the largest row index that has value in the worksheet, while the later set or read the number of rows in the worksheet. You can see the different in the following script. 

newbook;  // Create a new workbook
col(b) = {1:10};  // Set column B with 1-10 for the first ten rows
wks.maxRows = ;
wks.nRows = ;

Origin outputs 10 for wks.maxRows; while outputs 32 for wks.nRows.

If the worksheet is not the active one, you can specify the full worksheet name (including workbook name) before wks object, the syntax is

[WorkbookName]WorksheetNameOrIndex!wks

Or you can use the range of the worksheet. For example 

// Open a project
string strOpj$ = system.path.program$;
strOpj$ += "Samples\COM Server and Client\Basic Stats on Data.opj";
doc -o %(strOpj$);

wks.nCols = ;  // Output number of columns in the active worksheet
// Output number of columns in the worksheet [RawData]Data!
[RawData]Data!wks.nCols = ;  
// Output the name of the first worksheet in RawData workbook
[RawData]1!wks.name$ = ;  

// Use range
range rWks = [RawData]Data!;  // Range for the Data worksheet in RawData workbook
rWks.userparam1 = 1;  // Show the first user-defined parameter in worksheet

Using X-Functions

Besides wks object, you can also use X-Functions to modify worksheet properties. These X-Function names are usually with the starting letter "w". Such as wcolwidth, wcellformat and wclear, etc. So we can also resize the column with as below without using wks.colwidth: 

wcolwidth 2 10; // Set the 2nd column width to 10

Delete Worksheet

The layer -d command can be used to delete a worksheet or graph layer. 

// Create a new workbook with 6 worksheets
// Workbook name is stored into MyBook$
// And the first worksheet will be the active one
newbook sheet:=6 result:=MyBook$;

// Add a new worksheet with name of "My Sheet"
newsheet name:="My Sheet";

page.active = 1;  // Activate the first worksheet
layer -d;  // Delete the active worksheet

// Delete a worksheet by index
// Delete the third worksheet (or layer) in the active workbook (or graph)
layer -d 3;

// Delete a worksheet by name
layer -d "Sheet5";

// Delete a specified worksheet by range
range rs = [%(MyBook$)]"My Sheet"!;  // Define a range to a specified worksheet
layer -d rs;

// Delete a worksheet whose name is stored in a string variable
string strSheet$ = "Sheet3";
layer -d %(strSheet$);

To delete a worksheet whose name is stored in a string variable, there are some special string variables for some special worksheets, for example: 

//__report$ holds the name of the last report sheet Origin created
layer -d %(__report$);

The variable __report$ is an example of a system-created string variable that records the last-used instance of a particular object. A list of such variables can be found in Reference Tables.

English | Deutsch | 日本語