xlsgen > overview > Tables

Table objects in spreadsheets can be used to accomplish a number of processing and scenarios.

Tables can run SQL queries, can infer data types, can filter and sort columns or rows, can process business intelligence, can be switched to a chart and vice versa, can be formatted with styles, and so on.

Creating tables

Tables in xlsgen can be created in a variety of ways :

• a cell range (see below)
• a SQL query
• business intelligence inference

A IXlsTable interface is created upon which further processing and scenarios can be executed.

Table columns and rows can be formatting with a single line of code. This formatting makes a distinction between row headers, column headers, data columns and functional cells such as totals. A number of built-in styles are available.

XlsTable table = worksheet.NewRange("R3C2:R5C5").NewTable();

table.DataResults.Style.putBuiltinStyle(xlsgen.tablestyle_medium03);

Dim table As IXlsTable
Set table = worksheet.NewRange("R3C2:R5C5").NewTable

table.DataResults.Style.BuiltinStyle = enumTableStyle.tablestyle_medium03

IXlsTable table = worksheet.NewRange("R3C2:R5C5").NewTable();

table.DataResults.Style.BuiltinStyle = enumTableStyle::tablestyle_medium03;

xlsgen::IXlsTablePtr table = worksheet->NewRange(L"R3C2:R5C5")->NewTable();

table->DataResults->Style->BuiltinStyle = xlsgen::tablestyle_medium03;

Whenever spreadsheets are printed or exported (PDF, HTML, XPS, ...), table styles are rendered as such, which means xlsgen computes for any cell inside the table the actual style based on whether the cell is part of the headers, is part of totals, is on alternate rows or columns, etc., and combines it with any style that may be attached to it either because it is a custom style, or there is a conditional formatting. This computation is completely hidden to the client application, so there is no custom code to write for this to happen.

Existing tables can be edited, for instance the cell range that encompasses a table, including headers, data and footers can be read or changed.

To obtain the cell range,

_bstr_t range = worksheet->Tables->ItemByName[L"Table1"]->DataSource->Range; // returns a cell range of the form RxCx:RyCy

To change it,

worksheet->Tables->ItemByName[L"Table1"]->DataSource->Range = L"R1C1:R15C10";

Alternatively, if the scenario knows in advance that all it needs is to add a new row to the table,

worksheet->Tables->ItemByName[L"Table1"]->DataSource->AddRow();

Two R/W ranges are exposed :

• Range : includes header rows + data rows + total rows
• DataRange : data rows