2.7.33 impExcel



Contents

Brief Information

Import Excel 97-2003 files directly and import Excel 2007 or later with COM component.

Note: This is an older routine for importing Excel files. Users are encouraged to use the newer impMSExcel X-Function (COM not used, therefore MS Excel need not be installed) or Data Connectors (supports import of both local or web files).

Command Line Usage

1. fname$="sample.xls"; impExcel; // import the file sample.xls

2. dlgfile g:=*.xls;impExcel cell:=0 l:=1 d:=NN; //import without format, 1st row as longname and set all cols to have no designation

3. impExcel fname:="E:\Sample.xls" template:=MyTemplate.ogw; // Import the file sample.xls into the analysis template MyTemplate

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
File Name
fname

Input

string

fname$

Filename of the file to be imported.

File Info And Data Selection
info

Input

TreeNode

<unassigned>

This is used in the dialog for displaying the basic information of the file.

1st File Import Mode
firstmode

Input

int

0

Specify the import mode of the first imported file.

Option list

  • replace{0}:Replace Existing Data
Import data by replacing the existing Excel data in target window.
Note: It will detect if the target workbook has ever been imported Excel file(s). If yes, the exsiting worksheets will be replaced from first sheet(redundant sheets will be deleted, new sheets will be inserted if existing number of sheets is not enough.) ; If no, only the active worksheet will be replaced by first sheet of imported Excel file, other existing worksheets will be kept and other sheets of imported Excel file will be imported by starting new sheets.
  • column{1}:Start New Columns for each Sheet
Create new columns for each sheet in the first Excel file that will be imported.
  • book{3}:Start New Books
Create new book for the first Excel file.
  • sheet{4}:Start New Sheets
Create new sheet for the first Excel file.
Multiple Files (except 1st) Import Mode
impmode

Input

int

3

This is used to specify the import mode for the imported files except for the first one.

Option list

  • column{1}:Start New Columns for Each Sheet
Create new columns for each sheet in the Excel file(s) that will be imported.
  • book{3}:Start New Books
Create new book for each file that will be imported. If the Excel file has multiple sheets, each sheet will be imported into a new sheet of the same book.
  • sheet{4}:Start New Sheets
Create new sheets for each sheet that will be imported.
Template Name
template

Input

string

<default>

During importing data, a workbook template (*.otw) or an analysis template (*.ogw) can be used. You can select a template from the drop-down list, or click the browse button to locate one. By default, an Origin prototype workbook template <default> is used. Origin also provides a special template named <clone>, which allows importing data files into the worksheet/workbook cloned from active worksheet/workbook. Note: when importing a multi-sheets Excel book into the specified workbook template which contains empty worksheet(s), you can refer to system variable @ISE for additional control over importing behavior.

Use Excel COM Component to Import
excel

Input

int

1

Specify whether to use the COM component provided by Excel to import the file. You must have Excel installed to use this option.

Import Cell Formats
cellformats

Input

int

1

This is used to keep format of the imported cells the same as in Excel. This option is not available when using COM and Excel does not need to be installed on your computer. This option will fail if you try to import .XLSX or .XLSM files - both of which can only be imported using COM.

Maximum Number of Empty Columns (-1 for all)
emptycols

Input

int

0

This is used to specify the maximum number of empty columns.

Exclude Empty Sheets
excludeempty

Input

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 and Data Selection branch.

Number of Main Header Lines
mainheader

Input

int

0

Specify the number of the main header lines, which will be skipped upon importing.

Number of Subheader Lines
subheader

Input

int

<auto>

Specify the number of the subheader lines. When it is <auto>, it means that the total number of subheader lines is decided by following node:

  • Index of Rows for Short Name
  • Index of Rows for Long Name
  • Index of Rows for Unit
  • Index of Rows for Comment From
  • Index of Rows for Comment To
Index of Rows for Short Name
sname

Input

int

<none>

Specify the row index for column short name.

Note:

From Origin 2017 column Short Name is restricted to alphabetical order and it cannot be changed by default. So Index of Rows for Short Name option is hidden from GUI. If you want to set column Short Names while importing, you need to go to the target workbook, uncheck Spreadsheet Cell Notation checkbox in the Properties tab of Window Properties dialog to enable editing column Short Name.

Index of Rows for Long Name
lname

Input

int

<none>

Specify the row index for column long name.

Index of Rows for Unit
unit

Input

int

<none>

Specify the row index for column unit.

Index of Rows for Comment from
cmt1

Input

int

<none>

Specify the start row index for column comments.

Index of Rows for Comment to
cmt2

Input

int

<none>

Specify the end row index for column comments.

Index of Rows for Parameter from
para1

Input

int

<none>

Specify the starting row index for system parameters.

Index of Rows for Parameter to
para2

Input

int

<none>

Specify the end row index for system parameters.

Index of Rows for User Parameter from
udp1

Input

int

<none>

Specify the starting row index for user parameters.

Index of Rows for User Parameter to
udp2

Input

int

<none>

Specify the end row index for user parameters.

Remove Extra Rows
removerow

Input

int

1

Specify whether to remove the heading rows (short name, long name, unit and comment) from the data rows. If this is selected, the heading rows will not be imported as data, but used only for setting the data rows. When Number of Subheader Lines is not <auto>, this control will be ignored.

Column Designations
desig

Input

string

Specify the column designations. The specified column designation will be applied to all sheet automatically.

Apply Header to All Sheets
allsheets

Input

int

0

Specify whether apply the header to all Excel sheets.

Import Options
options

Input

TreeNode

<unassigned>

The branch (Re)Naming Worksheet and Workbook(Options.Names) is used to specify how to rename the worksheet and workbook.
Another branch Partial Import(Options.PartImp) is used to specify how to partially import the Excel files. For this X-Function, available sub tree nodes are: partial, FirstCol, ReadCols, SkipCols,LastCol.

Output
orng

Output

Range

<active>

This is used to specify the output range.

Header Information
finfo

Output

TreeNode

<optional>

This is for outputting the header information. It is hidden from the GUI. Users are not advised to use this variable.

Sheet Names or Indexes

sheet

Input

string

Specify the Excel sheets to import. Use the colons to divide the sheet names or indexes. It is hidden from the GUI. Use of this variable is not advised.

Repeat Import

reimp

Input

int

0

Specify whether the current import is a reimporting process. It is hidden from the GUI. Use of this variable is not advised.

Description

This X-Function is used for importing Microsoft Excel files. This works for .XLS files even if Excel is not installed, in which case COM is not used. Newer file formats ( .XLSX & .XLSM ) require Excel.

- Menu: Select Data: Import from File: Excel (XLS, XLSX, XLSM)...

- Command window: type impExcel -d in command window.

The imported Excel workbook can have more than one sheet. All sheets will be imported into separate Origin worksheets in the target workbook, using the same import settings.

The first few rows of the imported Excel worksheet can be used to set the short names, long names, units and comments of the columns in Origin worksheet. You can specify these rows by setting non-zero row index numbers for them.

From Origin 2015, re-import one or multiple sheets from Excel to Origin is supported. During Excel re-importing, a link is maintained so that Origin is able to use up-to-date data.

To re-import Excel files, go to Data: Re-Import Directly or use Ctrl+4 keys to re-import Excel sheets with same settings as before. Or you can go to Data: Re-Import... to bring up dialog box to change import settings for files to be re-imported.

You can also type command reimport -d in command window to re-import Excel files.

From Origin 2016, you can import Excel workbook(s) into a prepared workbook template or analysis template to batch customize/analyze data in Excel books.

Examples

Example 1

The following example imports an Excel file using factory default options:

  1. In the Command Window, type the following command 
    impExcel -d;
    This opens the Excel Files dialog for you to choose the import file.
  2. Browse to the folder where your data file is. Select the file and click the Add File(s) button. Click OK to close the dialog box.
  3. In the impExcel dialog box, load <default> for theme. Click Ok to close the dialog.

Example 2

The following script command imports an Excel file (c:\test.xls) without opening the dialog: 

fname$="c:\test.xls";
impexcel;

Example 3

The following script command imports the specified Excel sheets: 

newbook;
fname$ = "c:\test.xls";
impExcel sheet:=1:3:5; //Import the first, the third and the fifth Excel sheets only
newbook;
impExcel sheet:=SheetName1:SheetName2; //Import the Excel sheets named SheetName1 and SheetName2

Related X-Functions

impfile, impinfo