3.7.5.95 Wks


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.

Properties

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

Property Access Description
wks.AutoAddRows

(8.0 SR0)

Read/write
integer
Auto add rows when sheet is resized.

Example:

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.c1, c2, r1, r2 Read only
integer
Selection range. First and last columns and rows.
wks.cNamen$ Read only
string
The nth worksheet column short name. See wks.cnamemode to operate on specific column types. (See also: Wks.Col (Object))
wks.cNameMode Read/write
integer
Its value determines the columns that wks.cnamen$ operates on. Set wks.cnamemode to the following values: 0 = all columns, 1 = numeric columns, 2 = text columns, 4 = text and numeric (mixed) columns, and 64 = columns in the selection range. Set wks.cnamemode = 128 to return the full dataset name to wks.cnamen$.
wks.col Read/write
integer
Current column. See also: The Wks.Col object properties.
wks.colWidth Read/write
integer
Column width. example:
wks.col2.width=10;

Or use the wcolwidth X-Function to update column width.

wks.epd Read/write
specify if exclude current worksheet when do plotting with Layer Contents and Plot Setup etc graphing related dialogs. 1= tag this sheet as Exclude from plotting dialog; 0 = relief the exclude tag.

You use the system variable @TCE to indicate the tagged(excluded) sheet name with desired color, such as @TCE=Color(255, 60, 60);.

wks.export Read/write
Worksheet export settings; enter wks.export.= for sub-methods.
wks.font Read/write
integer
Font (by index) of the Standard name style in the sheet. You can use the font(name) function to get a font's index, like wks.font = font(Courier New);
wks.fSize Read/write
float
Font size of the Standard name style in the sheet, like wks.fsize = 12;
wks.hGrids
wks.vGrids
Read/write
integer
Display horizontal and vertical grid: 1 = enable, 0 = disable.
wks.hierarchical

(9.1 SR0)

Read Only
integer
Read whether the worksheet is hierarchical (i.e. contains collapsible nodes and tables such as an analysis report sheet does) or not: 1=hierarchical, 0=flat sheet.
wks.ignorehidden Read/write
Treatment of hidden rows in plotting and analysis operations:

0 = Include data in hidden rows on plotting and analysis.
1 = (default) Ignore hidden rows on plotting and analysis.

wks.import Read/write
Worksheet import settings; enter wks.import.= for sub-methods.
wks.index Read/write
integer
Worksheet index in workbook, i.e. 1,2,3, etc.

Use this property to reorder worksheets. For example:

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

Note: This property is Read Only before 8.5.0 SR1.

wks.joinMode Read/write
integer
Set/get the worksheet join mode. Values may be the following:

0 = enumerate when column names match. Append when matching rows are not found.

1 = drop when column names match. Append when matching rows are not found.

2 = enumerate when column names match. Drop when matching rows are not found.
3 = drop when column names match. Drop when matching rows are not found.
See the wks.join() method.

wks.khra

(2018 SR0)

Read/write
bool
When the X column of a column/bar graph contains text, this text is used to label major ticks, ordered by row index. Prior to Origin 2018, when applying a worksheet data filter, plots registered the vacant ticks and labels of filtered data, though the data points were not plotted. This was changed in Origin 2018 so that ticks associated with filtered data no longer display. This only applies to X columns that contain text and are NOT Set as Categorical. 0 (default) = hide filtered data labels, 1 = restore old behavior and show data labels even though data are filtered.
wks.loadedgrid Read/write
integer
0 if grid not loaded; 1 if grid loaded.
wks.longname$

(9.1 SR0)

Read/write
string
Long name of worksheet.
wks.maxRows Read only
integer
Scan all columns and find the largest row index that has value. You can setup a worksheet with wks.nRows, but before filling it with values, wks.maxRows will still be zero. To reduce the size of a worksheet, use wks.nRows, as this property is only to get the longest column row size.
wks.multiX Read only
integer
Multiple X columns: 1 = Yes, 0 = No.
wks.name$ Read/write
string
Worksheet name.
wks.nMats

(8.5.0)

Read/write
integer
Number of matrix objects in a matrix sheet.
wks.nCols Read/write
integer
Number of columns in the worksheet. Before Origin 8, this property was Read-Only
wks.nRows Read/write
integer
Number of rows in the worksheet. Before Origin 8, this property was Read-Only. See also: wks.maxRows.
wks.rhw Read/write
integer
Row heading width in units of 1/10 of cell height.

Example:

// Set to about 5 char height
range aa=2!; // 2nd sheet of active book
aa.rhw=50;
wks.sel Read only
integer
Selection flags. The hex return number indicates what is selected in the worksheet. Values may be the following, or a combination of these bits: 0 = none, 1 = editing cell, 2 = column, 4 = row, 8 = range, and 16 = 1 column.
wks.useFont Read/write
integer
Font usage: 1 = use selected font, 0 = use system font.
wks.userParamn

(8.0 SR0)

Read/write
integer
Show/hide specified User Parameter. For example:
wks.UserParam1=1; // Show the first user parameter
wks.userParamn$

(8.0 SR0)

Read/write
string
Access the User Parameter's name. For example:
// Set parameter name as "Site Index"
wks.UserParam1$="Site Index";
wks.VisibleCols

(9.0 SR0)

Read only
integer
Number of visible columns (not include the hidden columns) in the worksheet.
wks.VisibleRows

(9.0 SR0)

Read only
integer
Number of visible rows (not include the hidden rows) in the worksheet.

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

Method Description
wks.addCol(name) Add a single named column to the end of the worksheet. If name is not specified, a generic name is chosen.
wks.colSel(colNum, n) Column selection. If n = 1, select the colNum column. If n = 0, deselect the colNum column.
wks.copy(strRegister, Col, Row) Copy(Z): Copy entire wks into string register %Z. (It is recommended that you use %Z which can hold up to 6,290 characters. If the text is too large, it is not copied and no error occurs.) See also: wks.paste().
Copy(Z, n): copy all rows of column n.
Copy(Z, 0, n): copy all columns of row n.
See the colcopy, colcopy, wcopy and wrcopy X-Functions for more options.
wks.deleteRows(rowBegin[,numRows, colBegin, colEnd])

(2016 SR0)

Delete a range of rows. Specifying only rowBegin deletes rowBegin in all columns in the worksheet.
Adding option numRows deletes numRows from rowBegin, in all columns.
Use colBegin and colEnd to limit deletion of rows to specified columns, from (a) colBegin to the last column in the sheet (colEnd not specified), or (b) from colBegin to colEnd.
See examples.
Also, see wks.insertRows, below.
wks.findLabels(ind, K[,n]) Finds an apparent label in a column of data (Origin worksheet or Excel workbook. If an Excel worksheet is active, make sure that the internal data has been updated (as with layer -s) before use).
ind = (required) index of the column in which to find label;
K = (required) global string variable letter to store the found label string;
n = (optional) 0 to disregard selection, 1 to consider selection inside the column if only a range of rows inside the column is selected (if nothing in the column is selected or if the whole column is selected, treat as 0) By default (i.e. if n is omitted), it is considered to be 0.
wks.hasfilter()

(9.0 SR0)

Test whether there are filters applied in the worksheet. If yes, return 1, else return 0. For more details about filter property scripts, please see wks.col.filter.
wks.insert(name list) Insert the list of columns at the current location. The current column position is specified by wks.col. The list consists of one or more desired column names separated by spaces. If a column name is already used, it is automatically enumerated.
wks.insertRows(rowBegin[,numRows, colBegin, colEnd])

(2016 SR0)

Insert a range of rows. Specifying only rowBegin inserts one row before rowBegin in all columns in the worksheet.
Adding option numRows inserts numRows from rowBegin, in all columns.
Use colBegin and colEnd to limit insertion of rows to specified columns, from (a) colBegin to the last column in the sheet (colEnd not specified), or (b) from colBegin to colEnd.
See examples.
Also, see wks.deleteRows, above.
wks.isColHidden(colNum)

(9.0 SR0)

Test whether the column (specified by column number, colNum), is hidden. If hidden, return 1, and 0 for else.
wks.isColSel([colNum]) If colNum is included as an argument, the method returns the selection state of colNum. 0 = the column isn't selected. 1 = entire column is selected. 2 = a range of the column is selected.
If colNum is not included as an argument, this method returns the number of columns selected (partial and entire selections).
wks.isRowHidden(rowNum)

(9.0 SR0)

Test whether the row (specified by row number, rowNum), is hidden. If hidden, return 1, and 0 for else.
[ToWks!]wks.join(FromWks) Join the worksheet specified by FromWks to the worksheet specified by ToWks. This method adds the columns of FromWks to ToWks according to the method specified by wks.joinmode.

If ToWks is not specified, then the currently active worksheet is used.

wks.labels(str)

(8.0 SR1)

Control the display of worksheet column labels. No argument = do not show any labels, otherwise a string containing column label row characters, for example:
// 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)

The prefixes +, - and * were added in Origin 8 SR2.
The prefixes < and > were added in Origin 2017.

// 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);

Note that you can also use + and * to "move" (add) a label row to top or bottom. The characters < and > will do nothing if the label row is not already shown.

wks.paste(strRegister, Col, Row) Paste the contents of a string register (specified without the %) into the cell beginning at (Col, Row).
wks.runfilter()

(9.0 SR0)

Run or re-apply filter. For more details on filter property scripts, please see wks.col.filter.
wks.setaslabel(type, rowNum, label, append)

(8.5.1Sr0)

Set or append one row as LongName, Unit, Comment, etc.

type : Label type , L, C, U, P, etc.

rowNum : The Number of the row to set as label (This row will not be removed); -1 = remove the active row when you set it as the label (Note: you need to select the row before running this script).

label : 1 = select the label row, 0 = select the data row.

append : 1 = append the content in the selected row to label (Only works for Long Name and Comments); 0 = use the content in the selected row to replace the original content.

//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);
wks.template( FileName[,[WinName],NumRows]) Apply the template named FileName to <NumRows> rows of window WinName
wks.GetNextVisibleRow(n) Find the next visible row starting from the given row n. The GetNextVisibleRow(n) will check the rows after row n one by one, it outputs the row index if a visible row was found. So wks.GetNextVisibleRow(0) will give you the first visible row.

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:

Bookhide.png

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