Saving Formulas and Comments
Microsoft Excel allows using formulas and comments.
However, it does not keep such formulas and comments for refreshed data.
SaveToDB 8 has an option, Keep Formulas, that saves formulas internally and restores them after refreshing data.
SaveToDB 10 has the Keep Comments option that does the same with comments.
Database developers can turn on or off these features on the server-side in the xls.handlers table using the following event types:
- DoNotKeepFormulas
- DoNotKeepComments
- KeepFormulas
- KeepComments
The SaveToDB add-in uses the primary key columns, identity columns, or columns with delete object parameter names as row indexes to save and restore formulas and comments.
Developers can define columns used as indexes in the HANDLER_CODE field.
You can use a special rownum value if the row number is a good solution.
For example:
| 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 |
KeepFormulas and KeepComments handlers also allow specifying columns to save and restore formulas and comments from a database (in the JSON format) in the COLUMN_NAME field.
For example:
| 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 allows sharing source formulas and comments across all users.