3.7.5.102 Wks.Col

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

range rr = col(A); // Define a range notation
rr.index = 2; // Move the column as the 2nd column

worksheet -s 2 0 2 0;
menu -e 35153;

wks.col.missing=0/0;//to turn off custom missing value

Methods:

Layer method

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