Wks

Wks-obj

The WKS object has properties and methods related to an Origin sheet (Note: A sheet can be either Worksheet or Matrix Sheet). You can use range notation to define a worksheet object:

range wksObject = [winName]sheetName!

If no range is specified, Origin will work on the active sheet. Once a worksheet (matrix sheet) object is defined, the object properties and methods can be accessed using the following syntax:

wksObject.property
wksObject.method(argument)

For example:

range rWa = [Book2]Sheet2!;     // Define a worksheet object range
rWa.colSel(2,1);                // Select the second column of that sheet
rWa.vGrids = 0;                 // Turn off vertical grid lines
range rWb = !;                  // Use the active worksheet as a range
rWb.AddCol(NewColumn);          // Add a new column
NumColumns = rWb.ncols;         // Find out how many columns

Please see the wproperties X-Function for Read/Write access to additional Worksheet properties. What's more, there are some X-Function can do the same things as wks object. For more information, please refer to worksheet manipulation X-Functions.

PropertiesWorksheet Property

When operating on the active worksheet or matrix sheet, you can use wks.property to access sheet properties; otherwise, range notation should be used.

range aa=[book1]sheet2!;
// Disable auto add rows to maintain fixed 
// number of rows and columns
aa.AutoAddRows=0; 
// Setup the wks with 3x2
aa.nCols = 2;aa.nRows = 3;
                

wks.col2.width=10;
                

newbook sheet:=4; // Create a 4 sheets workbook;
wks.index = 3; // Move "Sheet1" to the 3nd worksheet;
                

// Set to about 5 char height
range aa=2!; // 2nd sheet of active book
aa.rhw=50;
                

wks.UserParam1=1; // Show the first user parameter
                

// Set parameter name as "Site Index"
wks.UserParam1$="Site Index";
                

†There is no LabTalk property or command for merging selected worksheet cells but you can accomplish this by capturing the menu id of the Merge cells toolbar button and using it with the menu -e command.

Methods

Worksheet Method

// Show Long Name and Comments, if they are not empty
wks.labels();
// Do not show any label rows
wks.labels(0);
// Set to show long name, units and comments
wks.labels(LUC)
// Show Comments, User Parameter 1, and Long Name
wks.labels(CD1L)
                

// To remove Units
wks.labels(-U);
// To insert Sample Rate and Sparklines at the top
wks.labels(+ES);
// To append Units to the bottom
wks.labels(*U);
// To move F(x)= to the bottom
wks.labels(>O);
//To move Comments to the top
wks.labels(<C);
                

//Set the second label row as the Long name.
wks.SetAsLabel(L,2,1,0);
//Append the fourth data row to the Comment.
wks.SetAsLabel(C,4,0,1);
//Set the first data row as the Unit.
//(The first data row should be active, 
// and it will be removed after running the script).
worksheet -s 0 1 0 1;  
wks.SetAsLabel(U,-1,0,0);
                

Examples

Work with Worksheet Columns and Rows

When a new worksheet is created, there are 2 columns and 32 rows by default. To read or set the number of worksheet columns and rows, you can use the wks.ncols and wks.nrows properties.

newsheet; // Add a new worksheet
wks.ncols = 5; // Set the number of columns to 5
wks.nrows = 100; // Set the number of rows to 100

Note that Origin will delete columns beyond (i.e., to the right of) the number you specify. So, in general, it is safer to use the wks.addCol(name) method to add columns.

wks.addCol(Does); // Add a column with short name 'Does'

Regarding worksheet rows, two properties are similar, wks.maxRows and wks.nRows. The former finds the largest row index in the worksheet that has a value, while the latter sets or reads the number of rows in the worksheet. The following script illustrates how to use these two properties:

newbook; // Create a new workbook
col(b) = {1:10}; // Fill 10 numbers to column B
wks.maxRows = ; // Returns 10
wks.nRows = ; // Returns 32

Display Worksheet Column Labels

This script creates an empty table for the average temperature in different cities. In this example, we will create a user-defined parameter and show the worksheet long name, unit and the user-defined parameter.

range ww = !;  // Define a range, on active worksheet
ww.name$ = "Average Temperature";  // Rename the worksheet
ww.ncols = 13;  // Set total number of columns
ww.userParam1$ = Month;  // Define a new user parameter label
// Show the worksheet long name, unit and a user parameter
ww.labels(LUD1);  
Col(1)[L]$ = City;  // Set column long name
stringarray month = {"Jun.", "Feb.", "Mar.", "Apr.", "May.", "Jun.", 
                   "July", "Aug.", "Sep.", "Oct.", "Nov.", "Dec."};
loop(ii, 2, 13)
{
        Col($(ii))[L]$ = Temperature;  // Set column long name
        Col($(ii))[U]$ = \+(o)F;  // Set column unit
        // Set column user parameter
        Col($(ii))[D1]$ = month.getAt(ii-1)$;  
}

Get the unhidden value from the column in worksheet

1. Assume some of values are hidden by Filter in worksheet, the script below will list the unhidden value in the column.

//with the worksheet active
for (int ii=1; ii <= wks.nrows; ii++){
if(wks.isRowHidden(ii)){       //continue id there is a hidden row
  ii = wks.GetNextVisibleRow(ii);   //get the 1st not hidden row number after this hidden row
  col(A)[$(ii)]=;  
}
};

2. The second example shows how the function GetNextVisibleRow works, assume we have a worksheet as shown below:

Run the script with the worksheet active:

//with the worksheet active
type "The 1st visable row is $(wks.GetNextVisibleRow(0))";
loop(ii,1,3){
a$(ii)=wks.GetNextVisibleRow(ii);
type "The 1st visible row after row $(ii) is $(a$(ii))";
}

The results output:

The 1st visible row is 1
The 1st visible row after row 1 is 3
The 1st visible row after row 2 is 3
The 1st visible row after row 3 is 5

See Also

Worksheet Manipulation X-Functions