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.
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;
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>]>;
|
This X-Function is used to split one worksheet's columns into multiple sheets, according to the method specified.