Overview
These public interfaces are used in Excel components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll.
IExcelConnection
When you call Excel Connection Manager AcquireConnection method, it returns object implementing IExcelConnection interface. This is the interface used for interaction with Excel workbook.
Methods
Connects to Excel workbook.
Connects to Excel workbook, loading from specified source stream. These are the method parameters:
- stream (Stream) - Excel workbook source stream.
Closes the connection to an Excel workbook. Use always after you finish working with the workbook.
Returns true if connected to Excel workbook.
Saves Excel workbook.
Saves Excel workbook to stream. These are the method parameters:
- stream (Stream) - Target stream to store Excel workbook. If value is Nothing, the workbook is stored to the file specified in ExcelFilePath property.
Returns formatted cell value. These are the method parameters:
- sheet (int) - sheet index. Use GetSheetIndex method to retrieve sheet index.
- col (int) - cell column. The column is 1-based.
- row (int) - cell row. The row is 1-based.
Returns raw cell value. These are the method parameters:
- sheet (int) - sheet index. Use GetSheetIndex method to retrieve sheet index.
- col (int) - cell column. The column is 1-based.
- row (int) - cell row. The row is 1-based.
Returns cell formula. These are the method parameters:
- sheet (int) - sheet index. Use GetSheetIndex method to retrieve sheet index.
- col (int) - cell column. The column is 1-based.
- row (int) - cell row. The row is 1-based.
Sets cell value. These are the method parameters:
- sheet (int) - sheet index. Use GetSheetIndex method to retrieve sheet index.
- col (int) - cell column. The column is 1-based.
- row (int) - cell row. The row is 1-based.
- value (object) - value to set in cell.
Sets cell formula. These are the method parameters:
- sheet (int) - sheet index. Use GetSheetIndex method to retrieve sheet index.
- col (int) - cell column. The column is 1-based.
- row (int) - cell row. The row is 1-based.
- formula (string) - cell formula to set.
Returns list of Excel worksheets.
Creates new Excel worksheet. Returns worksheet index. These are the method parameters:
- name (string) - name of new worksheet.
Renames Excel worksheet. These are the method parameters:
- oldName (string) - worksheet old name.
- newName (string) - worksheet new name.
Deletes Excel worksheet. These are the method parameters:
- name (string) - name of worksheet to delete.
Clears active Excel worksheet.
- GetColumnsCount method
-
Returns number of used columns in Excel worksheet. These are the method parameters:
- name (string) - name of worksheet.
Returns number of used columns in Excel worksheet. These are the method parameters:
- name (string) - name of worksheet.
Returns number of used rows in Excel worksheet. These are the method parameters:
- name (string) - name of worksheet.
Delete columns in Excel worksheet. These are the method parameters:
- sheet (string) - sheet name.
- start (int) - delete start column. The column is 1-based.
- count (int) - number of columns to delete.
Delete rows in Excel worksheet. These are the method parameters:
- sheet (string) - sheet name.
- start (int) - delete start row. The row is 1-based.
- count (int) - number of rows to delete.
Finds cell value in worksheet. Returns false if value not found. These are the method parameters:
- value (object) - value to find.
- sheet (string) - sheet name.
- startCol (int) - start column. The column is 1-based.
- startRow (int) - start row. The row is 1-based.
- caseSensitive (bool) - set to true to perform case-sensitive search.
- foundCol (int) - returns found cell column. If value not found, result is -1.
- foundRow (int) - returns found cell row. If value not found, result is -1.
Replaces cell value in worksheet. Returns the number of replacements. These are the method parameters:
- sheet (string) - sheet name.
- oldValue (object) - value to find.
- newValue (object) - replacement value.
- caseSensitive (bool) - set to true to perform case-sensitive search.
Returns worksheet index. These are the method parameters:
- name (string) - name of worksheet.
Returns list of named ranges in Excel worksheet. These are the method parameters:
- sheet (string) - name of worksheet.
Returns named range region start position, width and height. These are the method parameters:
- sheet (string) - sheet name.
- rangeName (string) - range name.
- x (int) - returns range start column. The column is 1-based.
- y (int) - returns range start row. The row is 1-based.
- width (int) - returns range width.
- height (int) - returns range height.
Copies worksheet from one Excel workbook to another. These are the method parameters:
- connection (IExcelConnection) - source Excel connection.
- sheet (string) - sheet name.
Properties
Specify path to Microsoft Excel file workbook to open.
Specify Excel workbook format. This property has the options listed in the following table.
Value | Description |
---|---|
0 | Automatically determines Excel workbook format from specified file extension (default). |
1 | 97-2003 Excel workbook. |
2 | 2007 Excel workbook. |
3 | Pocket Excel. |
Specify password to open Excel workbook.
Specify password to modify Excel workbook.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value | Description |
---|---|
True | All clients use same connection (default). |
False | Every client use different connection. |
Specify to recalculate and update external references in Excel workbook on open. This property has the options listed in the following table.
Value | Description |
---|---|
True | Excel workbook is recalculated on open. |
False | Excel workbook is not recalculated on open (default). |
Knowledge Base
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.