2.16.3.22 Details of TreeNodes in Import MSExcel
Options
The Options tree specifies all import options for the impMSExcel X-Function.
Syntax: Options..Treenode:=<value>
Example:
- Options.sparklines:=1
- Options.ColDesignations:=3(XYZ)
| Treenode
|
Label
|
Type
|
Default
|
Description
|
| FirstMode
|
1st File Import Mode
|
int
|
0
|
Specify the import mode of the first imported file. Option list:
- 0: Replace Existing Data
- The data existing in the target window will be deleted, and the new data will be imported.
- 3: Start New Books
- Create a new book for the first file imported.
- 4: Start New Sheets
- Create a new sheet for the first file imported.
- 1: Start New Columns for Each Sheet
- Append new columns to the target worksheet for each sheet of the first imported file.
- 7: Start New Books for Each Sheet
- Import each sheet in the first Excel file into a new book.
|
| Mode
|
Multi-File (except 1st) Import Mode
|
int
|
3
|
Specify the import mode of files except the 1st one. Option list:
- 3: Start New Books
- Create a new book for the file that that will be imported.
- 4: Start New Sheets
- Create a new sheet for the file that will be imported.
- 1: Start New Columns for Each Sheet
- Append new columns to the target worksheet for the imported file.
- 7: Start New Books for Each Sheet
- Import each sheet in file(s) following the first Excel file, into a new book.
|
| PageTemplate
|
Template Name
|
String
|
<default>
|
Specify the template to import the file to a new book or new sheet. This option is available when the Import Mode is set to Replace Existing Data, Start New Sheets or Start New Books. By default, an Origin prototype workbook template <default> is used. A special template named <clone> is also available from the drop-down list which uses active workbook as the template to import files into the duplications of current worksheet/workbook.
|
| ColDesignations
|
Column Designations
|
string
|
|
Specify the plot designations. Refer to this help page for more details of column designation syntax (including as how to repeat designations). The specified column designation will be applied to all imported sheets automatically.
|
| sparklines
|
Add Sparklines
|
int
|
1
|
Specify whether to add sparklines for the data. Option list:
- 0: No
- Do not add sparklines.
- 1: Yes
- Add sparklines.
- 2: Yes(if less than 50 columns)
- Add sparklines if there are less than 50 columns.
|
| cellfmts
|
Import Cell Formats
|
int
|
0
|
Specify whether to import cell formats (text color, font, cell height, .etc) in the Excel file.
|
| emptycols
|
Maximum Number of Empty Columns (-1 for all)
|
int
|
0
|
Specify the maximum number of empty columns.
|
| excludeempty
|
Exclude Empty Sheets
|
int
|
1
|
Specifies whether input the empty sheets into target window. If it is chekced, empty sheets will not appear in the File Sheet(s) branch in the File Info branch.
|
| sheet
|
Note that this tree node is hidden from the GUI
|
string
|
|
Specify the Excel sheets to import. Use colons (":") to divide the sheet names or indexes. See Example 3.
|
| allsheets
|
Apply Header to All Sheets
|
int
|
0
|
Specify whether apply the header settings to all Excel sheets.
|
Names
The Names branch of the Options tree specifies how to name the worksheet and workbook.
Syntax: Options.Names.Treenode:= <value>
Example:
//Rename workbook with partial filename :
Options.Names.FNameToBk:=1
Options.Names.FNameToBkFrom:=1
Options.Names.FNameToBkTo:=4
| Treenode
|
Label
|
Type
|
Default
|
Description
|
| AutoNames
|
Auto Rename using file name
|
int
|
0
|
If it is checked, Origin will use the default settings to rename the worksheet and workbook name, which is using the whole filename (includes the file extension) as workbook name.
|
| RenameSht
|
Rename Sheet with
|
int
|
1
|
Specify use what to rename Origin worksheet, None = 0, Excel Sheet Name = 1, Excel File Name = 2, Excel File and Sheet Name = 3.
|
| FNameToBk
|
Rename Book with (Partial) Filename
|
int
|
1
|
Enable this to use part of the file name as the workbook name.
|
| FNameToBkFrom
|
Trim Filename From
|
int
|
1
|
Specify the start of the name.
|
| FNameToBkTo
|
Trim Filename To
|
int
|
0
|
Specify the end of the name.
|
| FNameToBkLnOnly
|
Rename Long Name for Book only
|
int
|
0
|
Check this to rename the long name for book only.
|
| FPathToBk
|
Include File Path when Renaming Book
|
int
|
0
|
Specify if the file path should be included for the workbook name.
|
| FNameToBkComm
|
Append Filename to Workbook Comment
|
int
|
1
|
Specify if the filename should be appended to the workbook comment.
|
| FNameToWksComm
|
Append Filename to Worksheet Comment
|
int
|
0
|
Specify if the filename should be appended to the worksheet comment.
|
| FNameToColComm
|
Append Filename to Column Comment
|
int
|
0
|
Specify if the filename should be appended to the column comment.
|
| FNameToUDL
|
Add Filename User Parameters Row
|
int
|
0
|
Specify if the file name should be put to the column User Parameter row "SourceFile".
|
| FPathToComm
|
Include File Path when Appending Filename
|
int
|
0
|
When this is enabled, the import file path is included to the appended file name (so as file extension).
|
| sheetname2col
|
Add Sheetname User Parameter Row
|
int
|
0
|
Specify if the sheet name should be put to the column User Parameter row "SourceSheet".
|
| Notes:
From Origin 8.6, minus value is supported in Trim Filename From and Trim Filename To to count the end of the name from the last character of the file name. It can be used to trim the file extension. For example, if you want to import an Excel book named sample.xls, you can specify as follow to trim the extension ".xls":
- Trim Filename From: 1
- Trim Filename To: -4
|
Headers
The Headers branch of the Options tree specifies the header information of the imported file.
Syntax: Options.Hdr.Treenode:= <value>
Example:
//Specify the 1st row of the imported file as Long Name:
Options.Headers.LongName:= 1;
| Treenode
|
Label
|
Type
|
Default
|
Description
|
| MainHeaderLines
|
Number of Main Header Lines(exclude subheader lines)
|
int
|
0
|
Some files, particularly those generated by lab instrumentation, contain header information identfying, for example, the instrument, operator, date, and sample number. These are the main header lines. MainHeaderLines is used to specify where the main header lines are located.
|
| SubHeaderLines
|
Number of Subheader Lines
|
int
|
0
|
Specify the number of subheader lines in the file.
|
| LongName
|
Long Name
|
int
|
0
|
Specify the lines for long name.
|
| Unit
|
Units
|
int
|
0
|
Specify the line for unit.
|
| CommentFrom
|
Comments From
|
int
|
0
|
Specify the line from which the comments start.
|
| CommentTo
|
Comments To
|
int
|
0
|
Specify the line at which the comments end.
|
| ParametersFrom
|
System Parameters From
|
int
|
0
|
Specify the line from which the system parameters start.
|
| ParametersTo
|
System Parameters To
|
int
|
0
|
Specify the line at which the system parameters end.
|
| UserParametersFrom
|
User Parameters From
|
int
|
0
|
Specify the line from which the user parameters start.
|
| UserParametersTo
|
User Parameters To
|
int
|
0
|
Specify the line at which the user parameters end.
|
Partials
The Partials branch of the Options tree specifies how to partially import the file.
Syntax: Options.Partials.Treenode:= <value>
Example:
- Options.Partials.Partial:=1
- Options.Partials.FirstCol:=1
- Options.Partials.LastCol:=3
- Options.Partials.Partial_Cols.ColRanges:="1 3 5 7:10 12:0"
| Treenode
|
Label
|
Type
|
Default
|
Description
|
| Partial
|
Partial Import
|
int
|
1
|
Specify whether to enable partial import. Option list:
- 1: All Files
- Apply partial import settings to all files.
- 2: From 2nd File On
- Apply partial import settings to all files except the 1st file.
|
| FirstCol
|
From Column
|
int
|
1
|
Specify the beginning column for a partial import.
|
| LastCol
|
To Column
|
int
|
0
|
Specify the end column for a partial import. Note that "0" = last column.
|
| Partial_Cols.ColRanges
|
Custom
|
string
|
<optional>
|
Enter a range of columns enclosed in quotes, using this notation:
For Custom, use the following characters in building your import string:
- spaces separate individual column or row indices.
- colon(:) separates From and To.
- vertical bar(|) = "read n cols/rows".
- dash(-) = "skip".
- tilde(~) = "except".
- comma(,) = "and"
- Examples
- 1 3 7 import cols/rows 1,3 and 7.
- 1:100,~50:60 import cols/rows from 1 to 100, except 50 to 60.
- 1:200|3-2 import cols/rows from 1 to 200 by reading 3 then skipping 2.
- 1:1000,~200:300,250:260 import cols/rows 1 to 1000, skipping 200 to 300 with the exception of 250 to 260.
- 1:100|3-7,5:100|2-8 imports cols/rows 1 to 100, read 3 then skip 7 AND import cols/rows 5 to 100, read 2 then skip 8. The "AND" combination generates a repeating pattern of read 3, skip 1, read 2, skip 4 for every 10 cols/rows.
|
| FirstRow
|
From Row
|
int
|
1
|
Specify the start row for a partial import.
|
| LastRow
|
To Row
|
int
|
0
|
Specify the end row for a partial import. Note that "0" = last row.
|
| Partial_Rows.RowRanges
|
Custom
|
string
|
<optional>
|
See partial_cols.ColRanges
|
Scripts
The Scripts branch of the Options tree specifies the LabTalk scripts to run after file(s) being imported.
Syntax: options.scripts.Treenode:= <string>
Example:
//Plot second column for each imported file :
options.scripts.ScriptAfterEachImport:="plotxy 2;";
//When all files being imported, loop over all worksheets in all workbooks and delete first 500 rows from column 1 to column 4.
options.scripts.ScriptAfterAllImport:=" doc -ef W { doc -e LW { range aa =1[1]:4[500]; del aa;}}";
| Treenode
|
Label
|
Type
|
Default
|
Description
|
| ScriptAfterEachImportt
|
Script after Each File Imported
|
string
|
"?"
|
Specify LabTalk scripts that will be executed after each file imported.
|
| ScriptAfterAllImport
|
Script after All Files Imported
|
string
|
"?"
|
Specify LabTalk scripts that will be executed only after all files being imported.
|
|