3.3.2.68 WorksheetWorksheet-cmd
Use this command to set various formatting options for, and plot data from, the active worksheet.
Syntax:
worksheet option argument
Options:
-a; Add n columns to the active worksheet
Syntax: worksheet -a n
Add n columns to the active worksheet.
newbook; //Create a new workbook, default 1 sheet with 2 columns.
worksheet -a 3; //Add 3 columns to the workseet, now there are 5 columns in sheet1.
-b; Get the first workbook that begins with winName
Syntax: worksheet -b winName [TemplateName]
Get the first worksheet that begins with winName.
- If the named window can be found, TemplateName will be ignored.
- If the TemplateName is omitted, winName will be regarded as template name too.
- If the named window cannot be found, look for a template file called TemplateName and open that.
- If no template file is found, open ORIGIN.OTW.
- If ORIGIN.OTW is not found, create a window from scratch, name it winName, and enumerate it.
//Create three workbooks named "Book1", "Test1" and "Test2", active "Test1"
worksheet -b book1; //Workbook book1 is activated.
worksheet -b t; //Workbook Test1 is activated.
worksheet -b AA; //A new workbook named "AA" is created and activated.
-c; Create a new Y column named colName and add it to the end of the worksheet
Syntax: worksheet -c colName
-cdf; Clean up range style and name range format settings in active worksheet
Syntax: worksheet -cdf
Minimum Origin Version Required: 2015 SR0
In the active worksheet, clean up the format settings with the whole worksheet or entire column(s).
Note that this command does not clear the format settings for individual worksheet cell(s).
-cof; Convert non-SCN column formula into SCN in active worksheet
Syntax: worksheet -cof
Minimum Origin Version Required: 2023
In the active worksheet, convert non-SCN (spreadsheet cell notation) column formula into SCN. It will turn on SCN if it's not.
Note that this command only converts the formula but does not execute it.
-cp Copy selected data in the active worksheet to the Clipboard for plotting
Syntax: worksheet -cp
This is equivalent to clicking the Mini Toolbar button Copy for Plotting while worksheet data are selected. Then paste data to graph window as new plot by layer -pcp or layer -pcpu.
-crr Clear old banded row format in table
Syntax: worksheet -crr
Clear the old banded row format (by filling cell background color) in table (worksheet table or intermediate table inserted in Graph window).
Note: in case formats of an intermediate table mess up after clearing, you can set @WEF = 1 to open the Edit Formatting dialog (right click on the table window title bar) to reset it.
-d; Duplicate the active workbook into a new workbook named wksName and new wks becomes active
Syntax: worksheet -d wksName
Duplicate the active workbook into a new workbook named wksName (new wks becomes active window).
If wksName is not specified, an enumerating name of the original workbook is used.
worksheet -d Test; //Duplicate the active workbook into a new workbook named ''Test''
//and activated it.
-da; Duplicate the active workbook into a new workbook named wksName while original workbook remains active
Syntax: worksheet -da wksName
Duplicate the active workbook into a new workbook name wksName (original workbook remains active).
If wksName is not specified, an enumerating name of the original workbook is used.
-dr; Duplicate the active workbook into a new workbook and execute the Worksheet Script of the active worksheet
Syntax: worksheet -dr wksName
Duplicate the active workbook into a new workbook name wksName and execute the Worksheet Script of the active worksheet.
If wksName is not specified, an enumerating name of the original workbook is used.
-e; Open the specified worksheet dialogbox
Syntax: worksheet -e dialogbox
Open the specified worksheet dialogbox.Values for dialogbox are listed below.
Note: For Format, a column must be selected. Use the wks.colSel() object method.
|
ASCII -- ASCII Import Options for Datan dialog box.
DisplayControl -- Worksheet Display Control dialog box.
Format -- Worksheet Column Format dialog box (first select a column).
Par -- Import Verification dialog box.
Script -- Worksheet Script dialog box. If the user clicks on the Do It button, i is set to 1. If the user clicks on the Update button, i is set to 0.
worksheet -e script; //Open Worksheet Script Dialog
//Type the following script into the dialog
// worksheet -c;
// nn=wks.ncols;
// wcol(nn)=data(1,10);
//Press "Update" button
worksheet -dr Test; //A new workbook named "Test" is created and has three columns
//with Col(C)={1,2,...,10}
-es; Reset worksheet range selection behavior when selection fails
Syntax: worksheet -es
Occasionally, for reasons not fully understood, range selection in the worksheet becomes disabled. The user is not able to highlight a column by double-clicking on a column header nor is the user able to select a range of cells in the worksheet. Opening the Script Window (Window: Script Window) and running the following command with the problem worksheet active, should correct the problem and restore correct selection behavior.
Beginning with Origin 2018b, this command runs on loading a previously-saved window or project, and should prevent the problem from occurring.
-f; Set the colRef column to the given Format
Syntax: worksheet -f colRef colFormat
Set the colRef column to the given Format. colRef must be the column number.colFormat values: 1 = numeric, 2 = text, 3 = time, 4 = date, 5 = month, 6 = day of week, 7 = text & numeric.
col(B)=data(1,10); //Set col(B) value to be 1~10
worksheet -f 2 6; //Set col(B) format to be "day of week", see the change of the data.
-fd; Set the number of decimal digits to be used by the colRef column
Syntax: worksheet -fd colRef nDigits
Set the number of decimal digits (nDigits) to be used by the colRef column. ColRef must be the column number.
nDigits = -3 specifies free format.
Note: This option is only available for columns set to Numeric (see the -f option, above).
|
// New a workbook, fill col(B) with row #s
worksheet -f 2 1; // Change col(B) format to be ''Numeric''
worksheet -fd 2 2; // Set number of decimal digits to be 2
-fi; Set the colRef column's internal type to the specified value
Syntax: worksheet -fi colRef internalType
Set the colRef column's internal type to the specified value. ColRef must be the column number. internalType: 1 = double, 2 = Real, 3 = short, 4 = long, 5 = Char, 6 = Byte, 7 = uShort, 8 = uLong, 9 = Complex.
Note: This option is only available for columns set to Numeric (see the -f option, above).
|
-fp; Toggle frozen panes on or off
Syntax: worksheet -fp
Toggle frozen panes set by GUI or by wks.frozen$.
work -fp; // toggle frozen panes on
work -fp 0; // toggle frozen panes off
-frd; Fix duplicates in the active sheet
Syntax: worksheet -frd
All the duplicates except the one whose name matches the column in which it is located get new empty datasets
-gd pb; Custom Page Breaks
Syntax: worksheet -gb pb -i/m(r/c) rowcol [rowcol2]
Must first set worksheet to view page breaks from the View menu, or using the script wo -q 1
//must first enable page break from View menu
//to add a custom break, insert -ir -ic
wo -gd pb -ir 12;//insert at row 12
//to move a break at known location
wo -gd pb -mr 12 14;//move from row 12 to row 14
-i; Insert the name column as the n + 1 column in the worksheet
Syntax: worksheet -i n name
Insert the name column as the n + 1 column in the worksheet.
worksheet -i 1 New; //Insert a column named "New" between col(A) and col(B)
-k; Skip n header lines when importing ASCII data
Syntax: worksheet -k n
Skip n header lines when importing ASCII data. Determines the number of lines of header to skip when importing an ASCII file. When n = 0, skip 0 lines. When n = 1, skip 1 line, etc.
string fname$ = system.path.program$ + "Samples\Import and Export\F1.dat";
newbook;
wo -k 1;
open -w %(fname$);
Note: This option is only affects "open -w" command. Default label rows display setting is the show Long Name, Units and Comments. "open -w" just puts first header row into LN, next into U, next into C. After "wo -k 1;", the import would ignore the first header line in the file and start with the second, third, etc. But those still get directed to LN, U and C.
|
-kl; Store a maximum of n lines of header information in column labels when importing ASCII data
Syntax: worksheet -kl n
Store a maximum of n lines of header information in column labels when importing ASCII data. Determines the maximum number of lines of header to store as column labels when importing an ASCII file.
Note: This option is only affects "open -w" command, similar to -k
|
-n; Change the name of the colRef column to newName
Syntax: worksheet -n colRef newName
Change the name of the colRef column to newName. ColRef must be the column number.
worksheet -n 2 NewNameB; // Rename col(B) to be "NewNameB"
-p; Plot the current worksheet selection in the nth graph type into the template
Syntax: worksheet -p n template
Plot the current worksheet selection in the nth graph type into the template. Opens the Select Columns for Plotting dialog box if there is no current worksheet selection. Plots into the ORIGIN.OTP template if no template is specified. Possible n values are listed in the Plot Type IDs table.
Certain templates are included with the Origin software, and the template name is provided where applicable. You are not restricted to using the Provided Template.
// Fill col(A) and col(B) with Row #s
worksheet -s 1 7 2 14; // Select 8 pairs of data, see -s command for more detials
worksheet -p 201; // Create a scatter plot with selected 8 data point.
// Fill col(A) and col(B) with Row #s and col(C) with random #s
worksheet -s 1 0 3 0; // Select all 3 columns
worksheet -p 214 StackAreaP; //create a 100% Stacked Area plot
worksheet -p 112 ellipse; //create an ellipse plot
-pa; Plot data in current workbook/worksheet into the specified cloneable template
Syntax: worksheet -pa ? template
Plot data in current worksheet/workbook into the specified cloneable template if the data structure matches up with template. Note: the template must be a cloneable template to make this command work.
// With desired worksheet/workbook window active
worksheet -pa ? My3DScatter; // Plot data in current sheet into My3DScatter.otp
worksheet -pa ? "E:\\My3DScatter.otp"; // Plot data in current sheet to a cloneable template saved in E drive
//loop over everysheet in workbook. plot the sheet with user-defined template and rename graph window name with preset names in template
doc -e LB //loop over all sheets
{
worksheet -pa ? doubleY2; //plot with cloneable template
win -rp %H strNewName$;//rename graph window name with preset names in tempalte
}
-pc; Plot data by specifying the start column and increment in the current worksheet
Syntax: worksheet -pc plottype template startcolInd increment
Start column startcolInd, plot every increment columns in the current workbook in the nth graph type plottype into the specified template template. Other columns are skipped. If a negative increment is specified, Origin will span the whole worksheet and evenly pick the total number of -increment columns to plot.
Note: possible plottype values are listed in the Plot Type IDs table. Certain templates are included with the Origin software, and the template name is provided where applicable. You are not restricted to using the Provided Template.
// With desired worksheet window active
worksheet -pc 201 Scatter 2 3; //col(2), col(5), col(8), col(11)... should be plotted into Scatter.otpu
worksheet -pc 200 line 2 -3; //start from col(2) and pick 3 columns to plot.
-q; View Page Breaks (9.1)
Syntax: worksheet -q n
Minimum Origin Version Required: 9.1 SR0
Turn on/off view page breaks for the current worksheet.
wo -q 1;//show page breaks
wo -q 0;//hide page breaks
-qa; Extract columns satisfied with specified SQL query and put to active book
Syntax: worksheet -qa Select QueryString
Minimum Origin Version Required: 2020
Extract columns satisfied with specified SELECT SQL query and then put them to the active book. Note that it will overwrite the existing column if it is not emtyp.
Refer to this page for details of SELECT query.
//extract Y columns with LN containing "Value" from Sheet1 of Book1
wo -qa SELECT Column FROM [Book1]Sheet1! WHERE Column_LName LIKE "Value*" AND Column_Type = Y;
-qn; Extract columns satisfied with specified SQL query and put to a new book
Syntax: worksheet -qn Select QueryString
Minimum Origin Version Required: 2020
Extract columns satisfied with specified SELECT SQL query and then put them to a new book. Refer to this page for details of SELECT query.
//extract all columns from current project
wo -qn SELECT Column From Project;
//extract columns from current folder whose LoneNmae includes string "type"
wo -qn SELECT Column FROM ACTIVEFOLDER WHERE Column_LName LIKE "*type*";
-r; Run the script in the Worksheet Script dialog box
Syntax: worksheet -r
With a workbook active, press F4 key to open Worksheets Properties dialog. Switch to the Script tab and enter the following script command in the edit box:
worksheet -a 3;
Press OK to close the dialog and save the script. Now go to the script window and type:
worksheet -r;
The worksheet script will execute, and three columns will be added to the worksheet.
-rf; Repair Set Values formula for the worksheet column
Syntax: worksheet -rf columns
Projects with many Set Values operations sometimes develop problems. This command can re-establish correct input and restore such things as missing operations locks.
work -rf *; //repair formula for all
work -rf G; //repair formula for col(G)
-rr; Recover the range with empty UID data reference
Syntax: worksheet -rr
With a report sheet active, run this command to recover the ranges with empty UID data reference in report sheets
-rri; Recover the range with empty UID data reference and inputs
Syntax: worksheet -rri
With a report sheet active, run this command to recover the ranges with empty UID data reference in report sheets, plus recover also the inputs of operations whose output is found in the same sheet (it is not always reliable because it involves some guessing).
-s; Set the specified range to be the current worksheet selection
Syntax: worksheet -s c1 r1 c2 r2
Set the specified range to be the current worksheet selection. To select entire columns, set r1 and r2 to zero. To select entire rows, set c1 and c2 to zero. If range is not specified, deselect all.
-scf; Clear cell formats of the selected range
Syntax: worksheet -scf [FILTERSTYLE]
Clear specified cell formats FILTERSTYLE of the selected range(s). If no FILTERSTYLE is specified, all formats will be cleared. The FILTERSTYLE list can be found in oc_const.h flie (which locates in <exe folder>/OriginC/system/). The number indexes from 0. For example, index of fill color (OFST_Interior) is 6 and font (OFST_Font) is 8. Multiple FILTERSTYLE separate by space.
work -scf 6 8; // clear fill color and font
-sl; Select label row cell range
Syntax: worksheet -sl c1 r1 c2 r2
Select a range of label row cells. If range is not specified, clear the current selection.
worksheet -sl 0 1 0 1; // select first label row
worksheet -sl 1 1 3 3; // select label row c1,r1 to c3,r3
worksheet -sl // clear the current selection
-so D; Delete a worksheet column
Syntax: worksheet -so D
Delete the selected worksheet column.
-so I; Insert a worksheet column
Syntax: worksheet -so I
Insert a column in front of selected worksheet column.
-sou db; Delete rows before the selected row
Syntax: worksheet -sou db
Delete worksheet rows above the selected row and move current row to the top (i.e. set it as row 1).
-sou extend; Extend current selection to the bottom of the column
Syntax: worksheet -sou extend
Extend current selection of cell(s) to the last of the column.
-sou mr; Move selected rows(s) up or down n rows
Syntax: worksheet -sou mr n
Move current selected row(s) up or down n rows. Mowv down when n > 0; while move up when n < 0.
work -sou mr 1; //move down to next row
work -sou mr -1; //move up to last row
-sou Y; Set column designation
Syntax: worksheet -sou Y X/Y/Z, etc.
Set the column designation of a single, selected column to X, Y, Z, etc. For designations, List Control Symbols in this table.
-t; Set the colRef column as the given column type
Syntax: worksheet -t colRef colType
Set the colRef column as the given column type. ColRef must be the column number.
colType: 1 = Y, 2 = Disregard, 3 = Y Error, 4 = X, 5 = Label, 6 = Z, 7 = X Error, 8 = Grouping and 9 = Subject. If enter colType larger than 9, col would be set as ?? which is meaningless.
Note: If colRef does not exist currently, it would NOT add a new column automatically, instead, a command error message would shown. User should use existed columns only or add new column before run this command.
|
worksheet -a 1; // Add a third column to a new-created worksheet.
worksheet -t 3 3; // Set col(C) as Y-Error
-tc; Copy the active worksheet as HTML/EMF table
Syntax: worksheet -tc
Copy the activa worksheet as HTML/EMF table. See also this page.
-v; Verify that a named column exists
Syntax: worksheet -v colName
Verify that a named column exists. Create the column if it does not exist. Otherwise, do nothing.
Note: If the column is created, the end display range is set to row 0.
|
worksheet -v NewCol; // Create a new column named "NewCol" if there was no such one.
worksheet -v NewCol; // Because there is already such a col, nothing happens.
Examples:
Example 1
The following script adds 20 columns to the worksheet.
work -a 20;
Example 2
The next script renames each column as C1, C2, C3, etc., and fills each column with 100 values.
for (ii = 1; ii <= 20; ii++)
{
work -n $(ii) C$(ii);
work -t $(ii) 1;
wcol(ii)=data(ii, 100, ii);
}
Example 3
The next script sets the selection range in the data1 worksheet and plots this data into the scatter graph template. The script then sets a new selection range in the worksheet and plots the data into a second graph window.
win -a data1; //activate data1
worksheet -s 2 0 2 0; //select column 2
worksheet -p 201 scatter; //plot into scatter template
win -a data1; //re-activate data1
worksheet -s 3 0 3 0; //select column 3
worksheet -p 201 scatter; //plot into scatter template
Example 4
The last script sets column 3 as a text column.
work -f 3 2;
Example 5
To select an entire row or column.
worksheet -s 0 2 0 2;//Highlight row 2
worksheet -s 2 0 2 0;//Highlight column 2
Example 6
Generator some data and plot a XY-Error Scatter with selected data range.
newbook;
worksheet -a 1; // add a new column
wcol(1) = data(0,100,1);
wcol(2) = (wcol(1)^2)-20*wcol(1)+3;
csetvalue formula:=10*abs(grnd())+2 col:=3;
wsort bycol:=3 c1:=3 c2:=3; // sort col(C)
for (ii=1; ii<=wks.ncols; ii++)
{
switch (ii)
{
case 1 2:
worksheet -n $(ii) Data$(ii); // remane col(A) and col(B)
break;
case 3:
worksheet -t $(ii) 3; // set col(C) as Y-Error
worksheet -f $(ii) 1; // set col(C) type to be Numeric
worksheet -fd $(ii) 2; // set the number of decimal digits in col(C) be 2
worksheet -n $(ii) YError; // rename col(C)
break;
default:
break 1;
};
};
worksheet -v XError; // create a new column "XError"
csetvalue formula:=10*abs(grnd()) col:=4;
worksheet -t 4 7; // set it type to be X-Error
worksheet -s 1 6 4 10; // Highlight cell(6,1) to cell(10,4)
worksheet -p 201; // Plot a scatter
See Also:
Worksheet (object)
Document {-cw, -e W } (command),
doc.wksNamen$ (object),
Mark -d (command),
Page (object),
Plot -a (command),
%() Substitution Notation,
Undo -w* (command),
Wks.Col_(object)
|