2.7.3.1 Basic Worksheet Column OperationTo perform operations on worksheet columns, in most situations, you can use wks.col object, or the Range Notation to the column object.
Add ColumnInsert ColumnAdd or Insert Column
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
Insert Worksheet RowsDelete Worksheet RowsRows, InsertRows, Delete
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 ColumnMove 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 ColumnRename ColumnColumn Label
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 ColumnHide 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$;
fname$ += "Samples\Matrix Conversion and Gridding\XYZ Random Gaussian.dat";
impasc;
// Set column designation (column type)
wks.col = 3; // Set column 3 to be current column
wks.col.type = 6; // Z
// Select the 3rd column (Z column)
worksheet -s 3 1 3 -1;
// Make a color map surface with the template based on OpenGL
worksheet -p 103 glcmap;
Column Width
To set column width, the wcolwidth X-Function is available, or use wks.col.width.
// Open a workbook
string strPath$ = system.path.program$;
strPath$ += "Samples\Graphing\Automobile Data.ogw";
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.
- Numeric
// 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;
- Date
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;
- Time
Please refer to the description about Date above.
// Import data
newbook;
string fname$ = system.path.program$;
fname$ += "Samples\Import and Export\IRIG Time.dat";
impasc;
// Set format of column 1 to be Time
wks.col1.format = 3; // Time = 3
// Display IRIG Time format DDD:HH:mm:ss.##
wks.col1.subformat = 16;
- Month
// Set column 1 format as Month
// And show the whole name of month
wks.col1.format = 5; // Month = 5
wks.col1.subformat = 2; // Show the whole month's name
- Day of Week
// Set column 1 format as Day of Week
// And show only the first letter of each day of week
wks.col1.format = 6; // Day of Week = 6
wks.col1.subformat = 3; // Show the first letter of each day of week
Add Sparkline to Column
The sparklines X-Function is used to add sparklines to the specified columns in the worksheet.
// Open a workbook
string strPath$ = system.path.program$;
strPath$ += "Samples\Graphing\Automobile Data.ogw";
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";
doc -o %(strPath$);
// Delete last 20 columns from the opened worksheet
wks.nCols = wks.nCols-20;
|