3.7.5.96 Wks.Col

Columns in a worksheet are handled as objects in Origin. Columns 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 Methods

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

(9.0 SR0)

Read/write
numeric
Add or remove a filter. 1 = add filter, and 0 = remove filter. Note: even the filter properties are setting by using these filter related scripts, 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, and 7 = Text and Numeric as listed in the Format drop-down list in the Worksheet Column Format dialog box.
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.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 means no custom missing value, which means that you can not use 0 for 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, used only if column format is numeric(1):

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 redonedent, since they are the same as Column format

6 = Text, same as format = 2

7 = Text and Numeric, same as format = 7

wks.col.setformat

(8.0 SR2)

Write
numeric
string
Setformat method combines functionalities of the format and subformat methods, additionally allowing custom formats to be specified. This object method takes 3 arguments, wks.col.setformat(format[, subformat , customFormat]). Example below.
wks.col.subformat Read/write
numeric
Subformat options as listed in the Display drop-down list of the Worksheet Column Format dialog box (in the Origin GUI, select Format pull-down menu, then select Column ... option 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. 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
Number of characters allowed in a text column.
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
Column width, in units of characters.

Please also see the wcolwidth X-Function which sets column width.

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.

Examples

Worksheet Column Access

Worksheet Column objects can be accessed in the following two ways:

Active sheet column access 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;

Range variable that represents a 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 Columns

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 Format

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 shows rows where the temperature is greater than 75:

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

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 descending 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;