3.7.5.102 Wks.ColWks-Col-obj
Object, ColumnColumns in a worksheet or objects in a matrix are handled as objects in Origin. These objects can contain much information, such as column name, column numeric type, data formats, etc. The column attributes can be accessed and changed using the properties in the table below.
Properties and MethodsColumn Method
Property
|
Access
|
Description
|
wks.col.comment$
(8.0 SR0)
|
Read/write string
|
Column Comment row header string value.
|
wks.col.categorical.sort
(2017 SR0)
|
Read/Write numeric
|
Read or set the sort method for column n: 0 = unsorted, 1 = ascending, 2 = descending and 3 = customizable (arbitrary).
Notes: "Unsorted" follows order of appearance in the column. Sort method=3 is Read Only.
|
wks.col.categorical.type
(2017 SR0)
|
Read/Write numeric
|
Read or set the type for column n:
0 = Column type not Categorical.
1 = Auto off: Set as Categorical, with Categories manually managed. Categories can be added/deleted and are arbitrarily sorted (cannot be sorted by wks.col.categorical.sort ).
2 = Auto on: Set as Categorical, with Categories auto-generated by column data (can be sorted by wks.col.categorical.sort ). Categories updated automatically with data change.
|
wks.col.cmap.palette$
|
Read/Write string
|
Read or set the color palette for the matrix object.
|
wks.col.digitMode
|
Read/write numeric
|
Set the digit display mode: 0 = display all, 1 = set the decimal places, and 2 = set the significant digits. When wks.col[n].digitmode = 0 , wks.col[n].digits is set to 0. When wks.col[n].digitmode = 1 , wks.col[n].digits is set to 3. When wks.col[n].digitmode = 2 , wks.col[n].digits is set to 6.
|
wks.col.digits
|
Read/write numeric
|
Number of decimal places when wks.col.digitMode = 1 , number of significant digits when wks.col.digitmode = 2 : 0 = display all, n = display n.
|
wks.col.edit
(2019)
|
Read/write numeric
|
Adds one of the following controls to each cell in the column. Options =2 and =3 require the user to click into the cell for the control to show: 1 = unselected check box, 2 = list box (not editable), 3 = combobox (editable). Remove controls using wks.col.edit = 0 (e.g. wks.col2.edit=2; //add list box to column 2 , wks.col2.edit=0; // remove list box from column 2 ).
|
wks.col.evenx
|
Read/write numeric
|
Read or set column n to be using Sample Interval: 0 = not using Sample Interval, 1 = using Sample Interval with Internal X Initial value = 0 and Increment value = 1.
|
wks.col.filter
(9.0 SR0)
|
Read/write numeric
|
Add or remove a filter.
0 = remove filter
1 = add filter
2 = custom filter (read only)
Note: Filter properties are set by using these filter related scripts, but the filter will not run automatically, and the method wks.runfilter() needs to be called to run or re-apply the filter.
|
wks.col.filter$
(9.0 SR0)
|
Read/write string
|
Set or get filter query string. It must set the filter type to advance.
|
wks.col.filterenabled
(9.0 SR0)
|
Read/write numeric
|
Enable or disable a filter. 1 = enable filter, and 0 = disable filter.
|
wks.col.filterprescript$
(9.0 SR0)
|
Read/write string
|
Set or get before query script.
|
wks.col.filterx$
(9.0 SR0)
|
Read/write string
|
Set and get the variable used to represent the column.
|
wks.col.format
|
Read/write numeric
|
Column format: 1 = Numeric, 2 = Text, 3 = Time, 4 = Date, 5 = Month, 6 = Day of Week, 7 = Text and Numeric, and 8 = Color as listed in the Format drop-down list in the Worksheet Column Format dialog box.
|
wks.col.formula$
|
Read/write(9.8b) string
|
Column formula as in Set (Column) Values or in F(x)= label row (see also Column Label Row Characters).
|
wks.col.formularange$
|
Read string
|
Column formula range as in Set (Column) Values.
|
wks.col.index
|
Read/write numeric
|
Column index counting from left to right.
Use this property to reorder worksheet column. For example:
range rr = col(A); // Define a range notation
rr.index = 2; // Move the column as the 2nd column
Note: This property is Read Only before 8.5.0 SR1.
|
wks.col.iscat
|
Read/write numeric
|
Read or set column is Categorical or not. 0 = Column type is not Categorical, 1 = Column type is Categorical.
|
wks.col.justify
Deprecated
|
Read/write numeric
|
Set the justification for worksheet cell values. 1 = right, 2 = left, and 3 = center.
Note: This property was deprecated after 8.0. As an workaround, you can use the menu -e command instead. For example, to set the 2nd column as center:
worksheet -s 2 0 2 0;
menu -e 35153;
Where 35152 = left, 35153 = center, and 35154 = right.
|
wks.col.label$
|
Read/write string
|
Column label access for versions before 8.0. Still supporte to control long name, units and comments in a single call. For detailed access to column long name, units and comments, use col(a)[L]$, col(a)[C]$ etc notation instead.
|
wks.col.lname$
(8.5 SR0)
|
Read/write string
|
String value for the long name of column or matrix object.
|
wks.col.missing
(8.0 SR1)
|
Read/write numeric
|
Custom missing value, this is particularily important for data types other then double as by default, only Text & Numeric or double has built-in missing value support. This property allow setting custom missing value such that non-double columns can have missing value support. 0/0 means no custom missing value, i.e. wks.col.missing=0/0;//to turn off custom missing value
|
wks.col.name$
|
Read/write string
|
Column short name
|
wks.col.nCols
(8.0 SR1)
|
Read/write int
|
Number of columns for Matrix Object; for worksheet column, always 1.
|
wks.col.nRows
(8.0 SR1)
|
Read/write int
|
Number of rows
|
wks.col.numerictype
(8.0 SR1)
|
Read/write numeric
|
Column numeric type:
0 = non-numeric, 1 = double, 2 = float, 3 = short int, 4 = int, 5 = char, 8=byte, 9=unsigned short int, 10 = unsigned int, 11=complex
The following values are redundant, since they are the same as Column format
6 = Text, same as format = 2
7 = Text and Numeric, same as format = 7
|
wks.col.py$
|
Read/write(9.8b) string
|
Contents of Python Formula tab in Set (Column) Values
|
wks.col.script$
|
Read/write(9.8b) string
|
Contents of Before Formula Script tab in Set (Column) Values
|
wks.col.subformat
|
Read/write numeric
|
Subformat options as listed in the Display drop-down list of the Worksheet Column Format dialog box (select from main menu Format: Column ... to open this dialog). To get the appropriate number of the option you want, simply count down from the top of the list of Display options, starting from 1. For example, 1 stands for "7/30/2020", the 1st entry. and 22 is "Custom Display", the last entry. Please note that options "2020-W34" and "2020-W34-3" are newly added, so they are indexed after "Custom Display". That is, 23 for "2020-W34", and 24 for "2020-W34-3". Make sure the desired Format has been selected first.
|
wks.col.svrm
(8.0 SR0)
|
Read/write integer
|
Set Value Recalculate Mode for the column formula (in any) of the column. 0=none,1=auto,2=manual.
|
wks.col.tWidth
|
Read/write numeric
|
The number of characters that can be entered in a Text column. The default value = 49. The maximum value = 799. Note that a Text & Numeric column will hold a max of 3799 characters.
|
wks.col.type
|
Read/write numeric
|
Column type: 1 = Y, 2 = disregard, 3 = Y Error, 4 = X, 5 = Label, 6 = Z, and 7 = X Error. Example below.
|
wks.col.unit
|
Read/write string
|
Units row header
|
wks.col.width
|
Read/write numeric
|
Width of a single worksheet column (e.g. "col1"), in units of characters. Also see the wcolwidth X-Function which can be used to set the width of a column range or wks.colWidth which can be used to set the width of all columns in the active sheet.
|
wks.col.xinit
(8.0 SR2)
|
Read/write numeric
|
Internal X Initial value for column. The XINIT, XINC and XUNITS appear in the Sampling Interval row header if displayed. Example below.
|
wks.col.xinc
(8.0 SR2)
|
Read/write numeric
|
Internal X Increment value for column. Example below.
|
wks.col.xuints$
(8.0 SR2)
|
Read/write string
|
Internal X Units string for column. Example below.
|
wks.col.xname$
(8.0 SR2)
|
Read/write string
|
Internal X Name string for column. The XNAME and XUNITS appear as the X Axis Title on plots of this column. Example below.
|
Methods:
Layer method
Method
|
Description
|
wks.col.filter([Dialog])
|
Open the filter dialog, where Dialog is:
0 = (default) open the associated filter dialog (Top N, Simple Numeric Filter, etc.).
1 = open the Custom Data Filter dialog.
|
wks.col.setformat(format[, subformat , customFormat]) (8.0 SR2)
|
Setformat method combines functionalities of the format and subformat methods, additionally allowing custom formats to be specified. This object method takes up to 3 arguments, See format and subformat properties above. Only set subformat and customformat if format is 4 (date)
wks.col.setformat(2) //set to text
wks.col.setformat(2) //set to date using system date format
wks.col.setformat(4,10) //set to date using 11th built-in date format
wks.col.setformat(4, 22, dd-MMM-yyyy HH:mm:ss.###) //set to date using custom date format
Example below.
|
wks.col.setformula([string register], autoupdatemethod=au_none) (8.6)
|
The first is mandatory and contains the name of the system string containing the column formula (in the example above it is Z).
The second argument is optional and takes values from the enumeration AUTOUPDATEMODE (default= AU_NONE) to specify the autoupdate mode.
%Z = "col(1) + 100";
wks.col2.SetFormula(Z, 1);
|
wks.col.findX()
|
Returns corresponding X column index
wks.col3.findx()= will return 1 if the 3rd column's X column is 1.
|
Examples
Worksheet Column Access
Worksheet Column objects can be accessed in the following two ways:
Active sheet column access by indexAccess column by index
// Set col(1)'s short name in active sheet as "Time"
wks.col1.name$ = Time;
// You can reference another book, but the target sheet must still be active
// Set column 5 in the active sheet of Book5 to be an X column
Book5!wks.col5.type = 4;
// col0 to refer to last column
//set last column formula
wks.col0.formula$="A";
Range variable that represents a columnRange as column
range cc = [Book1]Sheet1!col("Room Temperature"); // Define a range
cc.width = 10; // Set the column width as 10
Designating variable column number
The column number may be omitted if you first set the wks.col property to the number of the column you wish to address.
// Set every odd column as an X column
for( ii = 1 ; ii <= wks.ncols ; ii+=2)
{
wks.col = ii;
wks.col.type = 4;
}
// which is equivalent to
for( ii = 1 ; ii <= wks.ncols ; ii+=2)
{
wks.col$(ii).type = 4;
}
For column labels, you can access by column label row characters.
Set Column Type
This script sets column 1 of the active window to be an X column.
wks.col1.type = 4;
Loop Over ColumnsLoop Over ColumnsColumns, Loop Over
This script loops over the all workbooks and columns, and then prints the names of the columns.
doc -e LB {
loop (var, 1, wks.ncols)
{
type "This is column %(wks.col$(var).name$) in %H, %(page.active$)";
};
};
Create Internal X Dataset
This script creates an internal X dataset for a Y column and plots the Y against this X.
newbook;
col(2) = uniform(10); // Fill Y column with random numbers
wks.col2.xinit = 600; // First X value will be 600
wks.col2.xinc = 10; // Subsequent X spacing of 10
wks.col2.xunits$ = nm;
wks.col2.xname$ = Wavelength;
plotxy iy:=(?,2); // Plot column 2 as Y using its internal X dataset as X
Set Custom Date FormatCustom Date FormatDate Format, Customization
This script sets the format of column 2 of the active worksheet to a custom date format.
// Surround non-date characters in specifier by single quotes:
wks.col2.SetFormat(4, 22, yyyy'.'MM'.'dd);
Note that all non-date characters included in the custom string specifier must be surrounded by single quotes.
Dates entered into this column will now be displayed in the specified custom format. Enter 07-29-09 into one or more cells and confirm the display to show 2009.07.29 .
Setting up a Worksheet Filter
In the following example we have a column of temperatures where we wish only to show rows where the temperature is greater than 75. The last line of script allows the user to manually modify and reapply filter.
newbook;
col(1) = data(1,31);
col(2)=uniform(31)*10+70;
wks.col2.filter = 1;
wks.col2.filterx$ = "temperature"; // variable name we will use
wks.col2.filter$ = "temperature > 75"; // our filter condition
wks.runfilter();
wks.col2.filter(); // open the associated filter dialog box to modify filter
Set Column as Categorical
This script imports data, filters the data, sets a column of data as categorical then sorts the filtered categories in ascending order using wks.col.categorical.sort . Ordering becomes important when creating the stack plot. Sorting in ascending order then plotting the stack "top to bottom" preserves alphabetical ordering of categories, from top to bottom.
// import automobile.dat to a new book
fn$ = system.path.program$ + "Samples\Statistics\automobile.dat";
newbook;
impASC fn$;
// apply filter to column = Make to show only Buick Chrysler GMC Lincoln Saturn
wks.col2.filter=1;
wks.col2.filterx$ = make;
wks.col2.filter$ = "make="Buick" or make="Chrysler" or make="GMC" or make="Lincoln" or make="Saturn"";
wks.runfilter();
// set col(2) as categorical and sort in ascending order
wks.col2.categorical.type=2;
wks.col2.categorical.sort=1;
//refresh column filter
wks.runfilter();
// unstack worksheet to columns by make
wunstackcol -r 2 irng1:=[%H]automobile!C"Power" irng2:=[%H]automobile!B"Make" nonstack:=1 other:=[%H]automobile!A"Year" extract:=0;
// create stack plot of col(1) vs. col(2) to end, type=scatter
plotstack iy:=(1,2:end) plottype:=scatter order:=0 link:=1 xlink:=1;
Remove Column Filter
wks.col2.filter = 0;
wks.runfilter(2); //force update even there is no filter
Access Python Function in Set (Column) Values
//define function in python field
wks.col2.py$=
"def add1(a):
return [val+1 for val in a]
";
//use the function above in column formula
wks.col2.formula$="py.add1(A)";
|