Excel API

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

Connect method

Connects to Excel workbook.

Connect method

Connects to Excel workbook, loading from specified source stream. These are the method parameters:

  • stream (Stream) - Excel workbook source stream.
Close method

Closes the connection to an Excel workbook. Use always after you finish working with the workbook.

IsConnected method

Returns true if connected to Excel workbook.

Save method

Saves Excel workbook.

Save method

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.
GetCellValue method

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.
GetCellRawValue method

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.
GetCellFormula method

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.
SetCellValue method

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.
SetCellFormula method

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.
GetSheetsList method

Returns list of Excel worksheets.

NewSheet method

Creates new Excel worksheet. Returns worksheet index. These are the method parameters:

  • name (string) - name of new worksheet.
RenameSheet method

Renames Excel worksheet. These are the method parameters:

  • oldName (string) - worksheet old name.
  • newName (string) - worksheet new name.
DeleteSheet method

Deletes Excel worksheet. These are the method parameters:

  • name (string) - name of worksheet to delete.
ClearSheet method

Clears active Excel worksheet.

GetColumnsCount method

Returns number of used columns in Excel worksheet. These are the method parameters:

  • name (string) - name of worksheet.
GetRowsCount method

Returns number of used rows in Excel worksheet. These are the method parameters:

  • name (string) - name of worksheet.
DeleteColumns method

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.
DeleteRows method

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.
FindCell method

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.
ReplaceCell method

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.
GetSheetIndex method

Returns worksheet index. These are the method parameters:

  • name (string) - name of worksheet.
GetNamedRanges method

Returns list of named ranges in Excel worksheet. These are the method parameters:

  • sheet (string) - name of worksheet.
GetNamedRangeRegion method

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.
CopySheet method (1.5 SR-4)

Copies worksheet from one Excel workbook to another. These are the method parameters:

  • connection (IExcelConnection) - destination Excel connection.
  • sheet (string) - sheet name.

Properties

ExcelFilePath property (string)

Specify path to Microsoft Excel file workbook to open.

FormatType property (int)

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.
OpenPassword property (string)

Specify password to open Excel workbook.

ModifyPassword property (string)

Specify password to modify Excel workbook.

RetainSameConnection property (bool)

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.
Recalculate property (bool)

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).