Excel XLSX format
The OpenXML Excel Spreadsheet provider in Data Sync is based on the OpenXML file format and does not have any dependencies on OleDB and can run in 64 and 32 bit environments.
This provider supports read-write operations and delete operations.
DELETE operations will remove the row and shift the rows below up 1, these operations can be quite slow and do not support re-writing formula references so only use with data only spreadsheets.
Connection
To connect to an excel file open the connection window, expand the Excel folder, and select OpenXML Excel Spreadsheet.
Then click onto the ellipsis in the FileName field to open the file explorer and browse for your excel file. Once you are done click Connect to load the file into the datasource.
An alternative option to connect is to drag the excel file onto the datasource window directly from the file explorer.
You must ensure the file is not open when it is being used within Data Sync otherwise an error will be thrown.
Properties
| Property | Description |
|---|---|
| FileName | The Excel path and filename. |
| HeaderRowIndex | The row in the worksheet that contains the Column Names, the Data will be the row after the Header Row. |
| Worksheet | The Sheet in the Excel document to connect. |
| DateTimeFormatStyle | The Excel Display Style for columns containing DateTime values. |
| GuidFormatStyle | The .NET Guid DataType Format Style. |
Delete All Rows
The OpenXML Provider provides a method DeleteAllRows() that you can call from the project automation Start() method to clear out the Spreadsheet prior to a sync.
