Excel sheets can contain multiple tables. Tables are defined for some specific type of data defined in a sheet. They can
be styled, and formulated seperately from other cells of sheet.
For defining a table in Excel, user have table method exposed on worksheet object. Table method requires four parameters
to be passed.
Table Name: Name of table to define. This name should be unique in one excel document.
From Cell: Cell index to start table from. This is combination of column and row indices, with column represented
in charachter and row represented in numbers.
To Cell: Cell index till table ends. This is combination of column and row indices, with column represented in
charachter and row represented in numbers.
Options: Table formating options. This parameter define extra formating in table. The table formating option is
described later in detail.
The table can be formatted with filtering and sorting of records. Oxml.js let user define filters and sorts on table using
simple JSON object passed as a parameter in table method. User can also format the already defined table using a "set"
method exposed with table object. Following are the way to filter and sort table:
Filter Table: Filtering a table can be done with passing a JSON object having filters attribute. By default table
is not filterd. To make filtering available to the user, filters attribute must have a truthy value. Thus setting
{filters: true} will make filtering available in excel table. To filter the records this attribute should be an array
of filters defined. A single filter object can be defined with value, values, type, operator, and "and" flag. If a
single value need to be defined to filter, than we can use value attribute, and for multiple values we can use values
attribute with array of values. Type let user define the custom filter. It can have values "default" and "custom"
for default and custom filters respectively. Operator is used with custom filters, they are some predefined operators
for performing filtering. The value of operator can be "greaterThan", "greaterThanOrEqual", "lessThan", "lessThanOrEqual",
"notEqual" or "equal". The "and" flag is used to identify if all the filters defined are inclusive or exclusive. The
use of filters can be more cleared from below examples.
Sort Table: Sorting defines the sort applied on the tables. This attribute does not actually sorts the values,
as for sorting values should be repositioned in excel. However, this will only let the excel know that a particular
columns contains sorted values. Sorting is applied with defining a sort attribute on table options. This attribute
can have a numeric value or a JSON object. When defined with a numeric value, the column representation with particular
number is sorted in ascending order. However, when defined with a JSON object, this value can be defined with attributes
"column", "direction", "caseSensitive". Column attribute is required to identify column, which have to be sorted.
This is numeric representation of column to perform sorting. Direction can have "ascending" and "descending" value,
identifying the sort applied on column respectively, which is "ascending" by default. CaseSensitive identifies if
sorting is case sensitive or not. This can be set true or false, which by default is true. Sorting can be more explained
with below examples.
Define Table
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[10, 12],
[9, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6');
workbook.download('workbook.xslx');
Filter Table
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[10, 12],
[9, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', {filters: true});
workbook.download('workbook.xslx');
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[10, 12],
[9, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', {filters: [{value: 9, column: 1}]});
workbook.download('workbook.xslx');
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[10, 12],
[9, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', {filters: [{values: [9, 10], column: 1}]});
workbook.download('workbook.xslx');
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[10, 12],
[9, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', {
filters: [{ value: 10, column: 1, type: "custom", operator: "greaterThanOrEqual", and: true },
{ value: 11, column: 1, type: "custom", operator: "lessThanOrEqual", and: true }]
});
workbook.download('workbook.xslx');
Sort Table
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[9, 12],
[10, 12],
[11, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', {sort: 1, filters: true});
workbook.download('workbook.xslx');
var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [
['Cost', 'Sales', 'Profit'],
[11, 12],
[10, 12],
[9, 12],
['Total']
]);
worksheet.sharedFormula('E3', 'E5', {
type: 'formula', formula: '(D3 - C3)', value: function (rowIndex, columnIndex) {
var sale = worksheet.cell(rowIndex, columnIndex - 1).value;
var cost = worksheet.cell(rowIndex, columnIndex - 2).value;
return sale - cost;
}
});
worksheet.sharedFormula('C6', 'D6', {
type: 'formula', formula: 'SUM(C3:C5)', value: function (rowIndex, columnIndex) {
var column = worksheet.column(3, 3), sum = 0;
for (var index = 0; index
< column.cells.length; index++) { if (column.cells[index].value && typeof column.cells[index].value === "number") sum += column.cells[index].value;
}
return sum;
}
});
worksheet.table('Table1', 'C2', 'E6', { sort: { direction: "descending", column: 1, caseSensitive: false }, filters: true });
workbook.download('workbook.xslx');