Settings
Designer Settings
Allow Delete Rows
Uncheck the Allow Delete Rows to prevent users from being able to delete rows in the table.
Allow Insert new rows
Uncheck the Allow Insert new rows to prevent users from being able to insert new rows in the table.
Auto-size Excel table columns
By default, SQL Spreads will auto-size the Excel columns to fit the table column content, each time a change is done in the SQL Spreads Designer. If you prefer to manually set the column-width, uncheck this checkbox.
Change tracking
You can track changes by inserting the username and the date/time when a row was created or changed into columns in the SQL Server table.
Use the four drop down lists in the change tracking group to select in which columns to insert the username and date and time when rows are created or modified. Your columns in your table will need to have specific data types to allow the change tracking to work correctly. CreatedBy/ModifiedBy columns require either DATETIME, DATETIME2 or SMALLDATETIME data types. CreatedOn/ModifiedOn columns require either TEXT, VARCHAR, NVARCHAR or CHAR data types.
When both the ModifiedBy and ModifiedOn change tracking columns are used, SQL Spreads will automatically turn on conflict detection, see details below.
Conflict Detection
When both the ModifiedBy and ModifiedOn change tracking columns are in use, SQL Spreads will automatically detection conflicts before changes are written to the database. If the timestamp when the row was last updated is newer in the database then in the Excel document, a conflict warning is shown with the possibility to select which rows to overwrite.
Note: When Conflict Detection is enabled, insertion and updating of rows will take longer time. If you are inserting or updating more than a thousand or a few thousand rows at the same time, when having Conflict Detection turned on, update times will typically exceed tens of seconds. If you turn off conflict detection, update times for a few thousand rows are a few seconds.
Document Settings
Import and Export settings
Refresh data from SQL Server, when the document is opened
By default SQL Spreads will reload the data from the SQL Server table into Excel when the Excel document is opened. To turn off the refresh of data from SQL Server when the document is opened, uncheck the “Refresh import data when the document is opened”.
Refresh data from SQL Server after the document is saved
To automatically reload the data from the SQL Server table into Excel right after the updates in Excel are save to SQL Server, check the “Refresh import data after data is saved to database”.
Save database changes when the Excel document is saved
To automatically save updates to SQL Server whenever the Excel document is saved, check the “Save changes to database when Excel file is saved.
Save Excel file after changes are saved to the database
Check this option to automatically save the Excel file after updates are written to the SQL Server database.
Confirm changes before saving to database
By default, SQL Spreads will show a confirmation dialog before updates are saved to the SQL Server database. You can turn off the confirmation dialog by unchecking this option.
Scroll worksheet to top after data has been imported
When using a Database Import filters, the number of rows loaded from SQL Server may vary for different filter selections. Use this option to always scroll to the top of the spreadsheet when new data is loaded from the database.
Other settings
Working with protected Excel Sheets
If you use the Excel feature to protect the Excel worksheet in the Review tab in Excel, you must also enter the worksheet protection password into the Document Settings under the Other Settings tab.
Identifier for NULL values
The NULL value identifier decides which text that is used in cells for NULL values.
Executing SQL queries after updates are save to SQL Server
You can execute a custom SQL query directly after the updates in Excel are save to SQL Server. To enter a SQL query, click the Edit Post-Save SQL Query button.
Note: The Post-Save SQL Query is executed in the same transaction as the update of the data. This means that if the Post-Save SQL Query fails to execute, the updates in SQL Server will not be committed to the database. This can be used to create custom validation and restriction for updating data.