3.7.5.101 Wks


The WKS object has properties and methods related to an Origin sheet (Note: A sheet can be either Worksheet or Matrixsheet). 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 (matrixsheet) 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
page.xlcolname = 0;            // Turn off Spreadsheet Cell Notation firstly
rWb.AddCol(NewColumn);          // Add a new column
NumColumns = rWb.ncols;         // Find out how many columns

For the Spreadsheet Cell Notation in the workbook, please see FAQ-849 for more information.

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 matrixsheet, you can use wks.property to access sheet properties; otherwise, range notation should be used.

Property Access Description
wks.AutoAddRows 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.band

(2023)

Read/Write
integer
Turn on/off banded rows: 0 = turn off banded rows; 1= turn on banded rows.

Refer to system variable @WBC to change the banded color, and @WBE to switch the fill-color rows between odd and even.

wks.c1, c2, r1, r2 Read only
integer
Selection range. First and last columns and rows.
wks.catI Read/write
Toggle display of category indices or categories: 0 = display categories, 1 = display indices.
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
Active column (worksheet) or active object (matrix). See also: The Wks.Col (Object) properties.
wks.colWidth Read/write
integer

Column width of the active worksheet.

Also see the wcolwidth X-Function or wks.col.width both of which can be used to set the width of individual columns.

wks.DB

(2023b)

Read/write
integer
Database import. See also: The Wks.DB object properties.
wks.DC

(2019b SR0)

Read/write
integer
Data Connector. See also: The Wks.DC object properties.
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.frozen$ Read/write
string
Set or read the range of frozen cols and/or rows or unfreeze cols/rows:
// The following is equivalent to the user selecting cell D4 and clicking the Freeze Panes Mini Toolbar button.
wks.frozen$="C 3"; // freeze cols A to C, rows 1 to 3
wks.frozen$=""; // unfreeze cols A to C, rows 1 to 3
wks.fSize Read/write
float
Font size of the Standard name style in the sheet, like wks.fsize = 12;
wks.hasDC

(2019b SR0)

Read only
bool
Return whether the workbook/worksheet has a Data Connector:

0 = workbook doesn't have Data Connector,
1 = worksheet is used by a Data Connector as the output (destination sheet),
2 = worksheet is not used by any Data Connector as the output. Iin this case, the worksheet may contain a Data Connector but doesn't build a valid connection. To check if the worksheet is used by a Data Connector as output or not, use "wks.DC.Valid".

wks.hGrids
wks.vGrids
Read/write
integer
Display horizontal and vertical grid: 1 = enable, 0 = disable.
wks.hiddenRows

(2019 SR0)

Read Only
integer
Return the number of hidden rows in the worksheet. wks.hiddenRows == wks.maxRows - wks.visibleRows.
wks.hierarchical 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.gap

(2021b)

Read/write
integer
Gap between the window title bar and the top of the sheet. Use this area to do such things as place text objects or custom buttons that run script. Gap is measured in units of text point size (e.g. entering "20" creates a gap that will accommodate 20 point text).
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.image Read/write
Matrix view settings, 0 = Data Mode and 1 = Image Mode.
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.isc Read/write
Sheet-level control of column width on import for both Import Wizard and Data Connectors:

0 = Do not adjust column width on import.
1 = (default) Follow the value of system variable @ISC.

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

(2023)

Read/Write
integer
Protect/Lock worksheet: 1 = True, 0 = False.

Set exception for protected worksheet by system variable @WPO.

wks.longname$ Read/write
string
Long name of worksheet. Beginning with Origin 2020b, "Long Name" = "Label".
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.nancolor Read/write
integer
Matrix image view missing value color.
wks.nMats Read/write
integer
Number of matrix objects in a matrixsheet.
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.upc Read only
integer
Count of User Parameters
wks.useFont Read/write
integer
Font usage: 1 = use selected font, 0 = use system font.
wks.userParamn Read/write
integer
Show/hide specified User Parameter. For example:
wks.UserParam1=1; // Show the first user parameter
wks.userParamn$ Read/write
string
Access the User Parameter's name. For example:
// Set parameter name as "Site Index"
wks.UserParam1$="Site Index";
wks.View

(2020b)

Read/write
integer
Turn worksheet Column List View on or off: 0 = standard view, 1 = Column List View.
wks.VisibleCols Read only
integer
Number of visible columns (not include the hidden columns) in the worksheet.
wks.VisibleRows Read only
integer
Number of visible rows (not include the hidden rows) in the worksheet.
wks.x(y).comments$ Read/write
string
The Comments for X/Y coordinates of the matrix.
wks.x(y).longname$ Read/write
string
The Long Name for X/Y coordinates of the matrix.
wks.x(y).units$ Read/write
string
The Units for X/Y coordinates of the matrix.

wks.merge() is for label only , and 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.AddButton(objname, label) Add a button to the gap on the top of the worksheet
wks.addCol(name) Add a single named column to the end of the worksheet. If name is not specified, a generic name is chosen.

If you have specified the column name, you might need to turn off the Spreadsheet Cell Notation in the workbook, you can see FAQ-849 for more information.

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])
wks.deleteRows([rowBegin,Undo])

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.

If rowBegin is not specified or set to 0, selected range will be deleted. In this case, Undo will determine whether to support undo: 0 = do not support undo (default), 1 = support undo. For example,

// delete seleted rows without undo
wks.DeleteRows(); 

// select rows where column B < 5000 or contains missing, and then delete them
wxt test:="col(B)<5000 or IsNA(col(B))" sel:=1;
wks.DeleteRows(0,1);

See more examples here.
Also, see wks.insertRows, below.

wks.delHidden([RowCol, Undo])

(2023b)

Delete hidden row(s) or column(s) in current worksheet. RowCol determines whether to delete hidden rows or columns: 0 = row (default), 1 = column; while Undo determines whether to support undo: 0 = do not support undo (default), 1 = support undo. For example, following codes delete rows where col(B)<5000 in the current worksheet:
// add a filter to hide all rows where col(B) < 5000
wks.col2.filter = 1;
wks.col2.filter$="x>5000";
wks.runfilter();
// delete hidden rows
wks.DelHidden(0,1);
wks.col2.filter=0;
wks.nrows=wks.maxrows;

Note: wks.DelHidden() is disabled in column list view.

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.findX(col)
wks.findX(colIndex,0)
Finds the corresponding X column of specified Y column col', and returns X column index. If col is X column, it will returns value <=0. For example,
i=wks.findX(B);
i=;//return 1
i=wks.findX(A);
i=;//return -3 since Col(A) is X column

For any column (no matter Y column or not), use wks.findX(colInex,0) to ignore column designation and reture corresponding X column index.

wks.hasfilter() 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.

The specified column names might not able to be applied to the newly-added columns if Spreadsheet Cell Notation is enabled in the workbook. So if you want to apply the specified column names to columns' short name, you need turn off the Spreadsheet Cell Notation before running the wks.insert command. See FAQ-849 for more information.

wks.insertRows(rowBegin[,numRows, colBegin, colEnd]) 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) 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) 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) 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);
// When import adds Units, move Units between Long Name and Comments, F(x)= to bottom
wks.labels(@);
// 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.
wks.labels(@) added in Origin 2020b.

// 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.merge(Label, option) Merge adjacent label cells containing the same text.
wks.merge(L,1);//merge LN
wks.merge(L,0);//unmerge LN
wks.merge(L,-1)=;//return 1 if LN merged, 0 if not.
wks.paste(strRegister, Col, Row) Paste the contents of a string register (specified without the %) into the cell beginning at (Col, Row).
wks.runfilter() Run or re-apply filter. runfilter(2) to force run even there is no filter. For more details on filter property scripts, please see wks.col.filter.
wks.reorder(arg1, [arg2]) Reorder columns.

arg1 = -1: reverse current order

wks.reorder(-1); //reverse order

arg1 = column index, arg2 is its new index

wks.reorder(2,5) //moves 2nd column to be the 5th column
wks.reorder(2, 0) //moves 2nd column to be the last column
wks.reorder(2,-1) //moves 2nd column to be next-to-last column
wks.reorder(0, -1) //moves the last column to be next-to-last column
wks.reorder(-1, 1) //moves next-to-last column to be the first column

arg1 = dataset: specify orders

dataset vv = {1,4,3,2};
wks.reorder(vv);
wks.setaslabel(type, rowNum, label, append) 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.sortCols(char, c1, c2, order, skipHidden) Sort worksheet columns by string content of one or more column label rows. The method returns 0 if it succeeds.

All arguments except char are optional:

char = one or more of these column row label characters. If multiple char are given, sorting is nested.
c1 = starting column to sort. If c1 and c2 are omitted or if c1=0 and c2=-1, entire workhseet is sorted
c2 = ending column to sort
order = a (ascending order) or d (descending order). If order is omitted, sort ascending
skipHidden = a numeric value. If non-zero, skip hidden columns during sort. If 0 or omitted, do not skip hidden columns.

// sort worksheet columns on column Long Name, in ascending order
wks.sortCols(L); 
// nested sort User-defined Parameter (a) > Long Name (d), all cols, skip hidden
wks.sortCols(D1L,,,ad,1);
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.
wks.userparam(str) Return index of the specified User Parameter, 0 if not found. For example:
wks.userparam(Location)=;// return User Parameter "Location"'s index

Add new User Parameter.

wks.userparam(++Factor) // add User Parameter "Factor"

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() method to add columns.

page.xlcolname = 0; // Turn off Spreadsheet Cell Notation firstly
wks.addCol(Does); // Add a column with short name 'Does'

For the Spreadsheet Cell Notation in the workbook, please see FAQ-849 for more information.

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