# 3.7.5.100 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
NumColumns = rWb.ncols;         // Find out how many columns

## 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

(8.0 SR0)

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
// 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.
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.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.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 (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.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.longname$

(9.1 SR0)

string
Long name of worksheet. Beginning with Origin 2020b, "Long Name" = "Label".
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.
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 (8.5.0) 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 (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)

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 (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. wks.x(y).comments$

(2015)

string
The Comments for X/Y coordinates of the matrix.
wks.x(y).longname$(2015) Read/write string The Long Name for X/Y coordinates of the matrix. wks.x(y).units$

(2015)

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.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])

(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.
 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])

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

(9.0 SR0)

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

wks.sortCols(L); // sort worksheet columns on column Long Name, in ascending order
wks.sortCols(D1L,,,ad,1); // nested sort User-defined Parameter (a) > Long Name (d), all cols, skip hidden
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

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'

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