3.3.2.63 Worksheet

Use this command to set various formatting options for, and plot data from, the active worksheet.

Contents

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

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

-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

-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

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

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

Notes: To select an entire column, refer to use the wks.colSel(colnumber,n) method (only for column selection).

-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

-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

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

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)