COZYROC Excel components, part of COZYROC SSIS+ suite since 2010, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from Microsoft Excel worksheets. The COZYROC components support Excel versions 1997 – 2019. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for Excel. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components. In addition, COZYROC also provides short and informative demonstration videos, providing step-by-step instructions on how to get started. COZYROC's adapters are not dependent on any Microsoft modules or Office and, therefore, the adapters do not have the same limitations as the adapters provided by Microsoft.
The Excel integration package consists of five parts:
- Excel Connection Manager
- Stores the credentials and functionality for connecting to Excel workbooks, whether they are on-premise or on Excel Mobile.
- Well documented API for use by user-defined scripts.
- Excel Source component
- Retrieves data from an Excel worksheet.
- The source workbook and worksheet can be dynamically modified at runtime using an SSIS expression.
- The standard Excel component only executes in 32-bit mode. The COZYROC component executes in both 32-bit and 64-bit modes.
- The standard Excel component tries to assign a data type for the columns (number, string, etc.) and sometimes gets it wrong, causing your package execution to fail. COZYROC’s component does not try to guess the data type and, therefore, handles mixed text and numbers in the same column without any problem.
- The standard Excel component requires you to use a SQL-like language to specify the columns and rows you want processed. COZYROC provides a point-and-click interface to specify this.
- Excel Destination component
- Inserts data into an Excel worksheet.
- Supports three actions relative to existing data in the worksheet: Clear, Overwrite or Append.
- The destination workbook and worksheet can be dynamically modified at runtime using an SSIS expression.
- Provides information about rows with errors.
- The standard Excel component only executes in 32-bit mode. The COZYROC component executes in both 32-bit and 64-bit modes.
- Excel Task
- Can be used to manage Excel workbooks. The task provides the following actions:
- Load from stream
- Save to stream
- Get worksheets list
- New worksheet
- Rename worksheet
- Delete worksheet
- Clear worksheet
- Delete Columns
- Delete Rows
- Find cell
- Replace cell
- Set password
- Set worksheet visible
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional Excel integration scenarios.
- Support Microsoft Excel 97-2019 and Pocket Excel workbook files.
- Support 32 and 64 bit execution.
- Support password-protected workbooks.
- No need to install any other component or Microsoft frameworks.
Technical Specifications
Case Studies
Overview
Excel Connection Manager is SSIS Connection Manager for processing data in Microsoft Excel workbook. Use the API to build components based on Excel Connection Manager.
Parameters
Excel
Use the Excel page of the Excel Connection Manager dialog to specify properties for opening Excel workbook.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify Excel workbook file to open.
Specify if all clients should use the same connection. This parameter can be accessed from the Properties editor and has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Advanced
Use the Advanced page of the Excel Connection Manager dialog to specify additional properties if the default configuration is not working.
Specify Excel workbook format. This property has the options listed in the following table.
Value Description Automatic Automatically determines Excel workbook format from specified file extension (default). Excel 97-2003 97-2003 Excel workbook. Excel 2007 2007-2010 Excel workbook. Requires installation of .NET 3.0 or later. Pocket Excel Pocket Excel. Specify password to open Excel workbook.
Specify password to modify Excel workbook.
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). Optional. Specify custom string format for writing date/time values. The format used by the standard Excel connection is :
yyyy-MM-dd HH:mm:ss.fffffff
Knowledge Base
- Where can I find the documentation for the Excel Connection?
- How can I set the Date Format for dates in the Excel Destination?
- How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.
- Error System.Exception 'SheetName' object not found.
- Why does my Excel Destination not overwrite the data correctly?
What's New
- New: Support for Excel 2019.
- New: A new parameter DateTimeFormat to specify string format when writing DateTime values.
- Fixed: Failed to open Excel files stored in SharePoint (Thank you, Kevin).
- Fixed: Failed to open certain Excel files (Thank you, Ada).
- Fixed: Failed to open password-protected Excel 2007 files.
- New: Introduced connection.
Overview
The Excel Source Component is an SSIS Data Flow Component for consuming data from Excel worksheets.
Demonstration
Case Study
Parameters
General
Use the General page of the Excel Source dialog to specify the source Excel worksheet and region.
- Reset
- Click the Reset button to re-initialize the component metadata.
Select the COZYROC Excel connection manager. Note: This will not work if you select the standard Excel connection manager.
Related Topics: Excel Connection Manager
Select Excel worksheet as the source. If the specified sheet starts with the colon (:) symbol, the component will treat the remaining definition as a sheet index 2.0.
Indicate that the first row contains column names.
Specify a named range within the source Excel file.
Specify which columns from the source are to be used as the input region.
- Start - start column. The column numbering is 1-based.
- Width - number of columns.
Specify which rows from the source are to be used as the input region.
- Start - start row. The row numbering is 1-based.
- Height - number of rows. If set to -1, the component will read all used rows from the worksheet.
Specify the output type. This parameter has the options listed in the following table.
Value Description Formatted Value Output formatted cell value. Raw Value Output raw cell value. Formula Output cell formula. Specify whether to remove whitespace from start/end of column names.
Columns
Use the Columns tab to specify which columns are to be processed from the source Excel file and to specify new names for the columns being processed, if desired.
This table lists the columns, by name, that are available to be processed from the source Excel file. If there are columns that you do not want to process, uncheck them from the list.
You cannot use this table to add or delete columns in the source Excel file. To manipulate data in an external Excel file, use the COZYROC Excel Task.Shows the columns that will be processed from the source Excel file based on the columns selected in the Available Input Columns list.
This shows the column name that will be used during processing. The name of any column may be changed by typing over the text in this list. Each name must be unique.
Error Output
Use the Error Output page to select error handling options for the columns.
View the name of the data source.
View the selected source column.
Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.
Related Topics: Handling Errors in Data
Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.
View a description of the error.
Knowledge Base
- Where can I find the documentation for the Excel Source?
- How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.
- Error System.Exception 'SheetName' object not found.
- How to configure the Excel Source Plus component to use particular worksheet when the sheet name is unknown?
- Excel Source Plus: How to modify the source region dynamically at runtime
What's New
- New: TrimColumnNames parameter to specify whether to remove whitespace from start/end of column names.
- Fixed: Processing of columns with type different from string failed for cultures that are not invariant (Thank you, John).
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- New: Cell value format can now be configured with LocaleID property.
- New: Component can now read from named ranges pointing to tables.
- Fixed: Component failed with error when input cell value was larger than the output column length and the column truncation metadata was set to ignore (Thank you, Dave). For further information check this post.
- Fixed: Component failed with error "The type initializer for '#c.#rnf' threw an exception." when reading certain files (Thank you, Scott).
- Fixed: Component failed to process sheet with name containing white space at start or end (Thank you, John).
- Fixed: Component is now multithread-safe.
- New: Component now supports named ranges.
- Fixed: Component did not activate selected worksheet at runtime.
- New: Introduced component.
Overview
The Excel Destination Component is an SSIS Data Flow Component for inserting data into Microsoft Excel worksheets.
Demonstration
Parameters
General
Use the General page of the Excel Destination component dialog to specify the destination Excel worksheet and options.
Select the COZYROC Excel connection manager. Note: This will not work if you select the standard Excel connection manager.
Related Topics: Excel Connection Manager
Select the destination Excel worksheet. If the specified sheet starts with the colon (:) symbol, the component will treat the remaining definition as a sheet index 2.0.
Specify the destination start column.
Specify the destination start row.
Indicate that the first row contains column names.
Indicate what data does the input contain. This parameter has the options listed in the following table.
Option Description Formatted Value The input contains regular cell data. Formula The input contains cell formula. Indicate that the worksheet is to be cleared before inserting data.
DEPRECATED: Use ExistingData parameter instead.Indicate that the workbook is to be saved at the end.
Indicate how to handle any existing data in the Excel file. This parameter has the options listed in the following table.
Options Description Clear Clear worksheet before inserting data. Overwrite Overwrite existing data with new data. Append Append input data to existing data.
Columns
Use the Columns page to select the columns to be written to the output Excel file and their order.
This lists the columns, by name, that are available to be written to the output Excel file. If you do not make any changes, the columns will be placed in the output Excel file in the order displayed. Input Column shows the order in which the columns will appear in the output Excel file.
To change the order of the columns in the output file, uncheck all the columns in the Available Input Columns list. Then, check the column you would like to appear first. You will see it appear under Input Column and Output Alias. Then, check the column you want to appear second in the output file. Again, you will see the column appear under Input Column and Output Alias. Continue this process until all of the columns you want in the output file are organized in the order you want them to appear.
Shows the current order in which the columns will appear in the output Excel file. This order can be changed by following the procedure described under the Available Input Columns parameter.
The text under Output Alias will be used as the column headers in the output Excel file. The name of any column may be changed by typing over the text in this list.
Error Output
Use the Error Output page to select error handling options.
View the name of the data source.
Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.
Related Topics: Handling Errors in Data
Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.
View the description of the error.
Knowledge Base
- Where can I find the documentation for the Excel Destination?
- Why does the Excel Destination Plus Component ignore the new values entered into the editor?
- How can I set the Date Format for dates in the Excel Destination?
- Why does my Excel Destination not overwrite the data correctly?
- Can I use Data Flow Task Plus to write data into multiple worksheets in an Excel file?
Related scripts
What's New
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- Fixed: Incorrect inclusion of excluded columns in a dynamic data flow.
- Fixed: Failed when multiple instances executed simultaneously against same connection (Thank you, Manasi).
- Fixed: Component UI didn't handle correctly output column modification (Thank you, Daniel).
- Fixed: Component failed to process when used in dynamic data flow (Thank you, Dan).
- New: A new parameter ExistingData specifying what to do with existing data - Clear, Overwrite or Append. The existing Clear parameter is deprecated.
- Fixed: Component failed to process input columns of type DT_TEXT and DT_NTEXT (Thank you, Marcy).
- Fixed: Component failed with "Unable to determine the identity of domain." error, when trying to create larger xlsx file (Thank you, Cory).
- Fixed: Component is now multithread-safe.
- New: Introduced component.
Overview
The Excel Task is an SSIS Control Flow task for management of Excel workbooks.
Demonstration
Parameters
General
Use the General page of the Excel Task Editor dialog to configure the parameters needed to process an Excel workbook.
Specify the task name.
Specify the task description.
Specify whether or not the Excel Task should terminate if the chosen action fails.
Specify the task action. This parameter has the options listed in the following table.
Option Description Load from stream Loads an Excel workbook from a stream object at runtime. Selecting this action displays the dynamic parameter SourceStream. Save to stream Saves an Excel workbook to a stream object at runtime. Selecting this action displays the dynamic parameter TargetStream. Get worksheets list Gets a list of worksheets from an Excel workbook. The result is a regular array. Selecting this action displays the dynamic parameter ResultVariable. New worksheet Creates a new worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Rename worksheet Renames a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsOldNameVariable, OldName, OldNameVariable, IsNewNameVariable, NewName, NewNameVariable. Delete worksheet Deletes a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Clear worksheet Clears a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable. Delete Columns Deletes columns in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsCountVariable, Count, CountVariable. Delete Rows Deletes rows in a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartRowVariable, StartRow, StartRowVariable, IsCountVariable, Count, CountVariable. Find cell Finds a cell with a specific value in a worksheet. Selecting this action displays the dynamic parameters FoundColumnVariable, FoundRowVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Replace cell Finds a cell with a specific value (old value) in a worksheet and replaces it with another value (new value). Selecting this action displays the dynamic parameters SaveAtEnd, ReplaceCountVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsOldValueVariable, OldValue, OldValueVariable, IsNewValueVariable, NewValue, NewValueVariable. Set password 1.5 SR-1 Set an open or modify password for an Excel workbook. Selecting this action displays the dynamic parameters SaveAtEnd, IsOpenPasswordVariable, OpenPassword, OpenPasswordVariable, IsModifyPasswordVariable, ModifyPassword, ModifyPasswordVariable. Get cell 1.6 SR-3 Get cell value. Selecting this action displays the dynamic parameters ResultVariable, IsWorksheetVariable, Worksheet, WorksheetVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Set cell 1.6 SR-3 Set cell value. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable, IsStartColumnVariable, StartColumn, StartColumnVariable, IsStartRowVariable, StartRow, StartRowVariable. Set worksheet visible 1.6 SR-4 Set worksheet visibility. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsValueVariable, Value, ValueVariable. Copy worksheet 1.8 Copies a worksheet. Selecting this action displays the dynamic parameters SaveAtEnd, IsWorksheetVariable, Worksheet, WorksheetVariable, IsNewNameVariable, NewName, NewNameVariable. Specify the stream object from which to load an Excel workbook at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Specify a stream object to which the Excel workbook should be saved at runtime. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Specify the variable where the list of worksheets from the specified Excel workbook should be stored as a regular array. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the workbook should be saved at the end of the execution of this task. This parameter has the options listed in the following table.
Option Description True The workbook should be saved when execution of the task is complete. False The workbook should not be saved to a file. The changes will only be kept in memory. Indicate whether or not the worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The worksheet name is stored in a variable. Selecting this option displays the dynamic parameter WorksheetVariable. False The worksheet name is specified explicitly. Selecting this option displays the dynamic parameter Worksheet. Explicitly specify the worksheet name.
Specify the variable containing the worksheet name. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the old worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The old worksheet name is stored in a variable. Selecting this option displays the dynamic parameter OldNameVariable. False The old worksheet name is specified explicitly. Selecting this option displays the dynamic parameter OldName. Specify the old worksheet name.
Specify the variable in which the old worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the new worksheet name is stored in a variable. This parameter has the options listed in the following table.
Option Description True The new worksheet name is stored in a variable. Selecting this option displays the dynamic parameter NewNameVariable. False The new worksheet name is specified explicitly. Selecting this option displays the dynamic parameter NewName. Specify the new worksheet name.
Specify the variable in which the new worksheet name is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the start column for the specified action is stored in a variable. This parameter has the options listed in the following table.
Option Description True The start column is stored in a variable. Selecting this option displays the dynamic parameter StartColumnVariable. False The start column is specified explicitly. Selecting this option displays the dynamic parameter StartColumn. Explicitly specify the start column. The value is 1-based.
Specify the variable in which the start column is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the start row is stored in a variable. This parameter has the options listed in the following table.
Option Description True The start row is stored in a variable. Selecting this option displays the dynamic parameter StartRowVariable. False The start row is specified explicitly. Selecting this option displays the dynamic parameter StartRow. Explicitly specify the start row. The value is 1-based.
Specify the variable in which the start row is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the row or column count is stored in a variable. This parameter has the options listed in the following table.
Option Description True The row or column count is stored in a variable. Selecting this option displays the dynamic parameter CountVariable. False The row or column count is specified explicitly. Selecting this option displays the dynamic parameter Count. Explicitly specify the number of rows or columns to be deleted. Set the count to -1 to remove all used columns or rows after the specified start position.
Specify the variable containing the number of rows or columns to be deleted. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the column of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the row of the cell that was found will be stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the value is stored in a variable. This parameter has the options listed in the following table.
Option Description True The value is stored in a variable. Selecting this option displays the dynamic parameter ValueVariable. False The value is specified explicitly. Selecting this option displays the dynamic parameter Value. Explicitly specify the value.
Specify the variable in which the value is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate the variable where the resulting number of replacements is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the old value (i.e. the value being replaced) is stored in a variable. This parameter has the options listed in the following table.
Option Description True The old value is stored in a variable. Selecting this option displays the dynamic parameter OldValueVariable. False The old value is specified explicitly. Selecting this option displays the dynamic parameter OldValue. Explicitly specify the old value to be found and replaced.
Specify the variable where the old value (the one being found and replaced) is stored. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the new value is stored in a variable. This parameter has the options listed in the following table.
Option Description True The new value is stored in a variable. Selecting this option displays the dynamic parameter NewValueVariable. False The new value is specified explicitly. Selecting this option displays the dynamic parameter NewValue. Explicitly specify the new value which will replace the old value.
Specify the variable containing the new value which will replace the old value. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the open password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.
Option Description True The open password is stored in a variable. Selecting this option displays the dynamic parameter OpenPasswordVariable. False The open password is specified explicitly. Selecting this option displays the dynamic parameter OpenPassword. Explicitly specify the open password for the Excel workbook.
Specify the variable containing the open password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Indicate whether or not the modify password for the Excel workbook is stored in a variable. This parameter has the options listed in the following table.
Option Description True The modify password is stored in a variable. Selecting this option displays the dynamic parameter ModifyPasswordVariable. False The modify password is specified explicitly. Selecting this option displays the dynamic parameter ModifyPassword. Explicitly specify the modify password for the Excel workbook.
Specify the variable containing the modify password for the Excel workbook. Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Knowledge Base
- Where can I find the documentation for the Excel Task?
- How can I get the name of a worksheet from an Excel file and rename it to a known name?
Samples
What's New
- New: A new 'Copy worksheet' action.
- Fixed: 'Find Cell' action returned incorrect result (Thank you, John).
- New: A new ‘Set worksheet visible’ action.
- New: A new 'Get cell' action.
- New: A new 'Set cell' action.
- New: A new 'Set password' action.
- New: Introduced task.
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
Excel Connection
- New: Support for Excel 2019.
- New: A new parameter DateTimeFormat to specify string format when writing DateTime values.
- Fixed: Failed to open Excel files stored in SharePoint (Thank you, Kevin).
- Fixed: Failed to open certain Excel files (Thank you, Ada).
- Fixed: Failed to open password-protected Excel 2007 files.
- New: Introduced connection.
Excel Destination
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- Fixed: Incorrect inclusion of excluded columns in a dynamic data flow.
- Fixed: Failed when multiple instances executed simultaneously against same connection (Thank you, Manasi).
- Fixed: Component UI didn't handle correctly output column modification (Thank you, Daniel).
- Fixed: Component failed to process when used in dynamic data flow (Thank you, Dan).
- New: A new parameter ExistingData specifying what to do with existing data - Clear, Overwrite or Append. The existing Clear parameter is deprecated.
- Fixed: Component failed to process input columns of type DT_TEXT and DT_NTEXT (Thank you, Marcy).
- Fixed: Component failed with "Unable to determine the identity of domain." error, when trying to create larger xlsx file (Thank you, Cory).
- Fixed: Component is now multithread-safe.
- New: Introduced component.
Excel Source
- New: TrimColumnNames parameter to specify whether to remove whitespace from start/end of column names.
- Fixed: Processing of columns with type different from string failed for cultures that are not invariant (Thank you, John).
- New: Ability to reference sheet by index (:index) when the sheet name is unknown.
- New: Cell value format can now be configured with LocaleID property.
- New: Component can now read from named ranges pointing to tables.
- Fixed: Component failed with error when input cell value was larger than the output column length and the column truncation metadata was set to ignore (Thank you, Dave). For further information check this post.
- Fixed: Component failed with error "The type initializer for '#c.#rnf' threw an exception." when reading certain files (Thank you, Scott).
- Fixed: Component failed to process sheet with name containing white space at start or end (Thank you, John).
- Fixed: Component is now multithread-safe.
- New: Component now supports named ranges.
- Fixed: Component did not activate selected worksheet at runtime.
- New: Introduced component.
Excel Task
- New: A new 'Copy worksheet' action.
- Fixed: 'Find Cell' action returned incorrect result (Thank you, John).
- New: A new ‘Set worksheet visible’ action.
- New: A new 'Get cell' action.
- New: A new 'Set cell' action.
- New: A new 'Set password' action.
- New: Introduced task.
Knowledge Base
- How can I set the Date Format for dates in the Excel Destination?
- How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.
- Error System.Exception 'SheetName' object not found.
- Why does my Excel Destination not overwrite the data correctly?
- Where can I find the documentation for the Excel Destination?
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.