Use this command to set various formatting options for, and plot data from, the active worksheet.
worksheet option argument
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.
Syntax: worksheet -b winName [TemplateName]
Get the first worksheet that begins with winName.
//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.
Syntax: worksheet -c colName
This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information. |
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).
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.
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.
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.
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.
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.
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.
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}
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.
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.
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
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). |
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
Syntax: worksheet -frd
All the duplicates except the one whose name matches the column in which it is located get new empty datasets
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
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)
This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information. |
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. |
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 |
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"
This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information. |
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
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 }
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.
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
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;
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*";
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.
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)
Syntax: worksheet -rr
With a report sheet active, run this command to recover the ranges with empty UID data reference in report sheets
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).
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.
Notes: To select an entire column, refer to use the wks.colSel(colnumber,n) method (only for column selection). |
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
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
Syntax: worksheet -so D
Delete the selected worksheet column.
Syntax: worksheet -so I
Insert a column in front of selected worksheet column.
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).
Syntax: worksheet -sou extend
Extend current selection of cell(s) to the last of the column.
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
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.
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
Syntax: worksheet -tc
Copy the activa worksheet as HTML/EMF table. See also this page.
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.
This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information. |
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); }
This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information. |
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
Document {-cw, -e W } (command), doc.wksNamen$ (object), Mark -d (command), Page (object), Plot -a (command), %() Substitution Notation, Undo -w* (command), Wks.Col_(object)