2.2.3.69 wsplit


Contents

Menu Information

Restructure: Split Worksheet

Brief Information

Split worksheet by rows/columns or values in reference column

Additional Information

Minimum Origin Version Required: Origin 8.6.

Split partial worksheet, split by Reference Columns and Preset Result Sheet Name require 2017b.

Split by Reference Column according to the value and condition both requires 2021.

Command Line Usage

1. wsplit mode:=row row:=5;

2. wsplit mode:=label label:=L keep:=1;

3. wsplit mode:=ref ref:=[Book1]Sheet1!A name:=<[<%N_%V>]>;

4. wsplit mode:=ref ref:=[Book1]Sheet1!D refmode:=change turnpoint:=pregroup refsort:=desc;

X-Function Execution Options

Please refer to the page for additional option switches when accessing the x-function from script

Variables

Display
Name 		Variable
Name 		I/O
and
Type 		Default
Value 		Description
Input Worksheet iw

Input

Worksheet

<active>
 Specify the range to be split. May be an entire worksheet or a column range.
Split Mode mode

Input

int

0
 Specify the way in which the input worksheet is split.

Option list:

  • col:By Number of Columns
    Every n columns will be split into a new worksheet. Specify the number n in the col.
  • row:By Number of Rows
    Every n rows will be split into a new worksheet. Specify the number n in the row.
  • label:By Column Label
    Columns having the same label in a specified label row will be split into the same worksheet. Specify the column label row in the label.
  • ref: By Reference Columns
    Rows will be grouped and split to new worksheets, by value(s) and conditions in reference column(s).
Number of Columns col

Input

int

2
 Available when Split Mode is By Number of Columns. Specify every n columns to create a new worksheet.
Number of Rows row

Input

int

2
 Available when Split Mode is By Number of Rows. Specify every n rows to create a new worksheet
Column Label label

Input

string

Available when By Column Label is selected for Split Mode. Specify the label row using its Label Row character. Please refer to Column Label Row Characters for the single character of each column label row.
Reference Columns ref

Input

Range

<unassigned>
 Available when Split Mode is By Reference Columns. Specify column(s) containing grouping variable(s).
Split refmode

Input

int

0

Specify how to split the worksheet rows according to the selected reference column.

This control in only available when you selected single reference column in the Reference Columns box. Option list:

  • 0=val:By Value
    Split the worksheet rows according to the values in the reference column. That means the rows with same values in the reference column will be split and extracted to the result worksheets. Tolerance is allowed. This is selected by default.
  • 1=change:By Direction Change
    Split the worksheet rows when the direction of the values in reference column changes, from ascending to descending, or vice versa.
  • 2=reset:By Direction Reset
    Split the worksheet rows when the ascending/descending values in reference column have been reset to the base.
  • 3=refval:By Value Change
    Split the worksheet rows when the value in reference column changes. Tolerance is allowed.
Tolerance tol

Input

double

1E-8
 Available when Split Mode is By Reference Columns and Split is By Value/By Value Change. Specify the tolerance which will take into account when dertmine if the numeric values change.
Include Turning Point in turnpoint

Input

int

1
 Specify how to deal with the turning points, to include them in the previous group or next group, when you selected By Direction Change for the Split control.

Option list:

  • 0=pregroup:Previous Group
    Include the turning points into the previous group when split the worksheet rows.
  • 1=nextgroup:Next Group
    Include the turning points into the next group when split the worksheet rows. This is selected by default.
Keep Reference Columns in Result keepref

Input

int

1
 Output Reference Column data with split result.
Sort Result by Reference Columns refsort

Input

int

0
 Specify whether to sort the result rows according to the corresponding values in the reference column when you checked Keep Reference Columns in Result. Please note, this check box is only available for the Split control set to By Direction Change or By Direction Reset.

Option list:

  • 0=unsorted:Unsorted
    Keep the original order of the rows, do not sort them.
  • 1=asc:Ascending
    Sort the result rows in the result worksheet in ascending order of the corresponding values in the reference column.
  • 2=desc:Descending
    Sort the result rows in the result worksheet in descending order of the corresponding values in the reference column.
Exclude Missing Value missing

Input

int

1
 Exclude empty cells or cells containing Origin's missing value character ("--"), from output.
Copy Column Formula to Result copyform

Input

int

0
 If original worksheet has column formula, specify how to deal with the formula.

Option list:

  • 0=none:None
    Do not copy the column formula
  • 1=copyexc:Copy and Execute
    Copy column formula to the result sheets and execute it.
  • 2=copy:Copy Formula Text
    Copy the text of column formula to the result sheets but not execute it. You will need to manually execute it later.
Keep Short Name sn

Input

int

0
 Determine whether to keep the column short names after splitting to new worksheets.

Note that from Origin 2017, column Short Name is restricted to alphabetical order and it cannot be edited, by default. So the Keep Short Name option is hidden in the GUI (see note at the bottom of the page). The script user can output customized Short Names using sn:=1, regardless of the value of @SSG.

Retain 1st N Columns fixcol

Input

int

0
 Available when Split Mode is By Number of Columns or By Column Label. Specify first n columns of the source Worksheet to keep them in all split result sheet. And these first n columns are excluded from splitting By Number of Columns or By Column Label.
Keep Source Worksheet keep

Input int

1
 Keep the source worksheet intact. Otherwise the source worksheet is deleted.
Show Sparklines sparkline

Input int

1
 Show Sparklines for the columns in the output worksheets.
Output Worksheet owp

Output

WorksheetPage

<new>
 Specify the output worksheet
Preset Result Sheet Name name

Input

string

%V

%N = ref name, LN if present, otherwise SN
%C = ref dataset name
%M = ref Comment
%U = ref Units
%V = ref cell values (grouping variable)

Variables can be used in combination and incorporate arbitrary text (e.g. %N=%V). Whether used singly, in combination or with arbitrary text, variables must be protected as in the following example: 

wsplit mode:=ref ref:=[Book1]Sheet1!A name:=<[<%N_%V>]>;

Description

This X-Function is used to split one worksheet's columns into multiple sheets, according to the method specified.

Related X-Functions

wsplit_book