3.3.2.65 Worksheet

Worksheet-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.

1. If the named window can be found, TemplateName will be ignored.
2. If the TemplateName is omitted, winName will be regarded as template name too.
3. If the named window cannot be found, look for a template file called TemplateName and open that.
4. If no template file is found, open ORIGIN.OTW.
5. 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

This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information.

-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).

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

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.

// 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.

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

This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information.

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

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

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

This does not work if Spreadsheet Cell Notation is enabled in the workbook. See FAQ-849 for more information.

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

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.

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.

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

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

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)