Configuring Table Formats

Configuring Table Formats

Preface

Users can change Excel data table queries using the Query List or the Data Connection Wizard.

When the query is being closed, SaveToDB saves the query table format in the active workbook and then restore it when the query is activated again.

The table format includes the following properties:

  • Cell formats.
  • Conditional formatting.
  • Applied auto-filters.
  • Table totals.
  • Data validation.
  • Active window properties.
  • Page setup properties.
  • User formula columns.

So the user sees the reopened query table format as it was when the user closed it.

Database developers can configure a database to store the table formats.
Moreover, application developers or advanced users can save the table formats in the database, and when a regular user opens a new query, he gets the completely formatted data.

It is a good idea to distribute applications as empty workbooks with the Readme worksheet only. When a new user connects to a database, he gets the superior quality tables.

The Table Format Wizard is used to manage the table formats.

View to Select Table Formats

SaveToDB reads the table formats from database tables* and views with the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_EXCEL_FORMAT_XML

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration view format:

TABLE_SCHEMATABLE_NAMETABLE_EXCEL_FORMAT_XML
<Table schema><Table name><Excel table format>
<View schema><View name><Excel table format>
<Procedure schema><Procedure name><Excel table format>
CODE<SQL code name><Excel table format>
HTTP<HTTP-query name><Excel table format>
TEXT<text-query name><Excel table format>

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

The table formats are stored in a database as XML. The maximum data size should be greater than 32K.

Recommended data types for TABLE_EXCEL_FORMAT_XML field:

  • XML for Microsoft SQL Server
  • NTEXT for Microsoft SQL Server Compact
  • NCLOB for Oracle Database
  • CLOB(200000) for IBM DB2
  • MEDIUMTEXT for MySQL and MariaDB
  • STRING for NuoDB

There is an example of the configuration view:

Procedure to Save Table Formats

To save the table formats to a database, SaveToDB uses the first available stored procedure* with the following parameters:

  1. Schema
  2. Name
  3. ExcelFormatXML

* SaveToDB uses internal procedures to save table formats for Microsoft SQL Server Compact.

Also, SaveToDB 7 can use INSERT and DELETE commands to save table formats loaded from the tables.

A user must have permission to execute the procedure to manage formats; otherwise, Save and Clear operations in the Table Format Wizard are unavailable.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add this feature quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.