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.