Saving Formulas and Comments
Microsoft Excel supports formulas and comments, but it doesn't retain them when refreshing data.
SaveToDB 8 introduces the Keep Formulas option, which saves formulas internally and restores them after data refreshes.
SaveToDB 10 adds the Keep Comments option, which functions similarly for comments.
Database developers can enable or disable these features on the server side in the xls.handlers table using the following event types:
DoNotKeepFormulasDoNotKeepCommentsKeepFormulasKeepComments
The SaveToDB add-in uses primary key columns, identity columns, or columns with delete object parameter names as row indexes to save and restore formulas and comments. Developers can specify which columns to use as indexes in the HANDLER_CODE field.
If the row number is a suitable index, you can use a special rownum value.
Example Configuration
| ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
|---|---|---|---|---|---|---|---|---|
| s03 | usp_budget_request | KeepFormulas | ATTRIBUTE | rownum | ||||
| s03 | usp_budget_request | KeepComments | ATTRIBUTE | rownum |
The KeepFormulas and KeepComments handlers also allow you to specify columns for saving and restoring formulas and comments from the database in JSON format within the COLUMN_NAME field.
Example with Specified Columns
| ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
|---|---|---|---|---|---|---|---|---|
| s03 | usp_budget_request | row_formulas | KeepFormulas | ATTRIBUTE | rownum | |||
| s03 | usp_budget_request | row_comments | KeepComments | ATTRIBUTE | rownum |
This feature enables sharing source formulas and comments across all users.