# 2.7.3.1 Basic Worksheet Column Operation

To perform operations on worksheet columns, in most situations, you can use wks.col object, or the Range Notation to the column object.

To add a column to the end of the worksheet, you can use the wks.addCol() method, which will add a column with the specified name, if the specified name is used or ignored, a generic name is chosen for the newly added column.

// Create a new workbook
newbook;
//Turn off "Spreadsheet Cell Notation" (SCN)
page.xlcolname = 0;
// Add a new column to the end, with name of Result
wks.addCol(Result);

The method above is only able to add one column to the end at a time. If you are going to add a multiple columns, you can add columns by setting the number of columns in the worksheet with the wks.nCols property. For example, the script below will add 3 columns to the end of the active worksheet with the generic names (Note: it is not able to specify the names in this way, please refer to Rename and Label Column section below).

// Create a new workbook
newbook;

// Add 3 columns to the end of worksheet
wks.nCols = wks.nCols + 3;

Besides adding columns to the end of the worksheet, it is also capable of inserting numbers of columns before the current column. First of all, it needs to specify which column (by 1-based index) is the current column using wks.col property, and then using wks.insert() method to insert column(s) before the current column. In the method, you need to specify a list of column names separated by space.

// Create a new workbook
newbook;

// Set column 2 to be the current column
wks.col = 2;
//Turn off "Spreadsheet Cell Notation" (SCN)
page.xlcolname = 0;
// Insert 3 column before column 2, with the specify column names
wks.insert(DataX DataY Result);

## Insert or Delete Rows in Columns

To delete or insert rows in worksheet columns, you can use the wks.deleteRows() or wks.insertRows() methods.

The syntax is as follows ...

wks.deleteRows(rowBegin[,numRows, colBegin, colEnd])
wks.insertRows(rowBegin[,numRows, colBegin, colEnd])

... with arguments inside the square brackets being optional.

Examples are given below:

wks.deleteRows(3); // Delete the third row in all columns
wks.deleteRows(3, 5); // Delete 5 rows beginning with the third row in all columns:
wks.deleteRows(3, 5, 2); // Delete 5 rows beginning with the third row in columns from the second to the end
wks.deleteRows(3, 5, 2, 4); // Delete 5 rows beginning with the third row in columns 2 to 4

wks.insertRows(3); // Insert a row in front of the third row in all columns
wks.insertRows(3, 5); // Insert 5 rows in front of the third row in all columns
wks.insertRows(3, 5, 2); // Insert 5 rows in front of the third row in columns from the second to the end
wks.insertRows(3, 5, 2, 4); // Insert 5 rows in front of the third row in columns 2 to 4

Note that the wdelrows X-Function can also be used to delete worksheet rows.

## Move Column

The colmove X-Function allows you to move column(s) of data within a worksheet. It accepts an explicitly stated range (as opposed to a range variable), and the type of move operation as inputs.

// Make the first column the last (left to right) in the worksheet:
colmove rng:=col(1) operation:=last;

// Move columns 2-4 to the leftmost position in the worksheet:
colmove rng:=Col(2):Col(4) operation:=first;

## Rename and Label Column

To rename (short name) a column, Origin provides the wks.col object with the name$property. Also, the Column Label Row Characters, G, is able to rename column short name. // Create a new workbook newbook; // Rename column 1 to DataX wks.col1.name$ = DataX;
// Rename column 2 to DataY by using range
range rY = 2;  // range to column 2
rY.name$= DataY; // Add a new column wks.addCol(); // Turn off "Spreadsheet Cell Notation" (SCN) page.xlcolname = 0; // Rename it with "G" col(3)[G]$ = "Result";

The Column Label Row Characters are the convenient way to access the column labels, including Long Name, Units, Comments, Column Parameters, User-Defined Parameters, etc.

// Create a new workbook
newbook result:=BkName$; // Show the following label rows: // Long Name, Units, Comments, 1st Column Parameter // and 1st User-Defined Parameter wks.labels(LUCP1D1); // Ranges to column 1 and 2 range r1 = [%(BkName$)]1!1;
range r2 = [%(BkName$)]1!2; // Set Long Name by using col col(1)[L]$ = Time;
col(2)[L]$= Voltage; // Set Units by using range r1[U]$ = Sec;
r2[U]$= V; // Set Comments by using range r1[C]$ = Sample1;
r2[C]$= Sample1; // Set Column Parameters by using range r1[P1]$ = "Machine1";
r2[P1]$= "Machine1"; // Rename the 1st User-Defined Parameter wks.UserParam1$ = Current;

// Set Current label row
r1[Current]$= 1mA; r2[Current]$ = 1mA;

## Hide/Unhide Column

To hide/unhide column(s), you can use the colHide X-Function.

// Create a new workbook
newbook;

// Set worksheet column number to 6
wks.nCols = 6;

// Hide the second column
colHide 2 hide;

// Hide the 3rd and 5th columns
colHide (3, 5) hide;

To show (unhide) column(s), it just changes the second argument from hide to unhide.

## Swap Column

The colSwap X-Function is used to swap two specified columns.

// Create a new workbook
newbook;

// Swap the position of the 1st and 2nd columns
colSwap (1, 2);

The specified two columns is not needed to be adjacent.

// Create a new workbook
newbook;

// Set number of columns to be 6
wks.ncols = 6;

// Swap the 2nd and 4th columns
colswap (2, 4);
 To swap two columns, if Spreadsheet Cell Notation is enabled in the workbook, you might not able to see the exchange of the column short names as the short names are always assigned from A~Z successively and automatically; if you want to exchange the column short names, you might need disable the Spreadsheet Cell Notation before running the colswap command. See FAQ-849 for more information.

## Modify Column Formats

### Plot Designation

Plot designation for a column determines how the selected data will be handled by default for plotting and data analysis. Plot designation includes X, Y, Z, Z Error, Y Error, Label, etc. And you can change it by using wks.col.type.

// Import data
newbook;
string fname$= system.path.program$;
doc -o %(strPath$); // To make column 2 show all the numbers but not ### // Set width of column 2 to 6 characters wcolwidth irng:=col(2) width:=6; ### Data Format and Display Setting a correct data format for a column helps to display the data in the column correctly, also helps to perform operations, such plotting, data analysis, etc. properly. There are many data format available for a column, such as Numeric, Text, Date, Time, Month, Day of Week, etc. To set format, please use wks.col object's format property. // Import data newbook; string fname$ = system.path.program$; fname$ += "Samples\Signal Processing\Average Sunspot.dat";
impasc;

// Set column 2 to Numeric (current is Text & Numeric)
wks.col2.format = 1;  // Numeric = 1

// Enable digit mode to be "Set Decimal Places"
// and set number of decimal places to 2
wks.col2.digitMode = 1;  // Set Decimal Places
wks.col2.digits = 2;  // Two decimal places

The following examples are showing the corresponding settings for different format.

1. Numeric
2. // Import data
newbook;
string fname$= system.path.program$;
fname$+= "Samples\Curve Fitting\Enzyme.dat"; impasc; // Set column 2 to Numeric (current is Text & Numeric) wks.col2.format = 1; // Numeric = 1 // Set display format with comma wks.col2.subformat = 4; // Display as Decimal: 1,000 // Data type to be short int wks.col2.numerictype = 3; // Do the same for column 3 wks.col3.format = 1; // Numeric = 1 // Set display format with comma wks.col3.subformat = 4; // Display as Decimal: 1,000 // Data type to be short int wks.col3.numerictype = 3; 3. Date 4. For Date and Time format, if the data stored in a column is not Julian day numbers (looks like Date and Time format, actually is text), we cannot set the format as Date or Time directly, or the look-like-Date-and-Time-format text will become missing value or something incorrect. To avoid this issue, Origin provides the wks.col.setformat() method. // Import data newbook; string fname$ = system.path.program$; fname$ += "Samples\Import and Export\Custom Date and Time.dat";
impasc;

// Set format of column 1 to be Date
// with a custom display format, which is like
// the current text display in the column
wks.col1.setformat(4, 22, dd'.'MM'.'yyyy HH':'mm':'ss'.'##);
// Set a familiar display format yyyy/MM/dd HH:mm:ss
wks.col1.subformat = 11;
5. Time
// Import data
string fname$= system.path.program$;
doc -o %(strPath$); // Turn on sparklines for all columns except the ones with "Year" Long Name for(ii = 2; ii <= wks.nCols; ii+=5) { sparklines sel:=0 c1:=ii c2:=ii+3; } ## Delete Column The delete command is capable of removing a column from worksheet. // Create a workbook newbook; // Delete column B delete col(B); // Add a new worksheet with 4 columns newsheet cols:=4; // Delete column 3 by using range range r1 = 3; // column 3 in the newly added worksheet delete r1; // Delete multiple columns by using range newsheet cols:=6; range r2 = (1,3,4); // assign multiple columns to the range delete r2; If the column(s) you want to delete is (are) at the end of the worksheet, you can just set the number of worksheet columns to delete it (them), by using wks.nCols. // Open a workbook string strPath$ = system.path.program$; strPath$ += "Samples\Graphing\Automobile Data.ogw";
wks.nCols = wks.nCols-20;