OXML.JS implementation examples to export or create open xml documents

Create / Export Workbook and Worksheet documents

To create a workbook, oxml.js expose xlsx method. This method does not take any argument and the output of this method is a new workbook object to work with. This object have a method “worksheet()”, which facilitate creating / adding a worksheet to workbook. A workbook can have multiple worksheets and these worksheets can be identified by a unique name. Thus, this worksheet method requires a string parameter, i.e., worksheetName. Worksheet method returns an object with lots of methods, which we will go through later. Another method exposed in workbook object is download. “download()” method let user export or save workbook file created with oxml.js. Again, just like worksheet() method, download() also takes a string argument “fileName”. download() method returns a promise object, or it may also used with an additional parameter, i.e., a callback method to execute when download is complete.

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Values

Values as the name suggest are JSON object representation of value to be filled in each cell. User can define or get value of cell in these format. A value JSON object have attributes varying on value type. Oxml.JS Excel supports 4 different types of values to be filled in cells.

String Values: Strings have two defined attributes, i.e., type and value. A JSON object with type set to "string" represents string value. Usually when you style a cell which do not have defined value. OXML.JS automatically sets it's value to string type with blank value. User can also define a string value with javascript string literals rather than defining the JSON object. In Excel, string type of value is defined as inline string. Some of the examples of string values are:

{type: "string", value: "Hello World"}; {type: "string", value: ""}; "Hello World"; "";
Numeric Values: Numbers again have two defined attributes, i.e., type and value. A JSON object type as "numeric" represents numeric value. User can also define numeric value with javascript numeric literals rather than defining the JSON object. Some of the examples of numeric values are:

{type: "numeric", value: 9}; {type: "numeric", value: 0}; 9; 0;
Shared String: Shared String: Shared strings are special type of string values which are usually used for repeated strings in Excel. They are optimal way to save string with multiple occurrence in Excel. A JSON object with type set as "sharedString". The only way to represent shared strings is:

{type: "sharedString", value: "Total"};
Formula: Formulas are calculated values using other cells. They can be defined with JSON object type set as "formula”". Unlike numbers and strings, formula is defined using attribute "formula" of JSON. This value is same as the value defined in formula bar. An additional attribute "value" is used to define cached result of calculated value. Some applications use this cached value when document is opened in read only mode. Formula when defined with cell is saved as single cell formula and when defined with row, or column formula is saved as shared formula. Some of the example of defining formula are:

{type: "formula", formula: "A1+D4"}; {type: "formula", formula: "(A2-A1) * A3", value: 30};

Cell

Cell is a method available to worksheet object, they can be used to work with a single cell of worksheet. The behaviour of this method depends on the parameters defined and passed. Two required parameters of cell method are rowIndex and columnIndex. They should be strictly numeric and represent row or column starting from 1. A Cell method with only rowIndex and columnIndex returns cell saved in worksheet of oxlx. User can get all details of cell using this method, the output of cell method is JSON object with following attributes:

value: This attribute represents the value saved in cell.
type: This attribute represents the type of value in cell.
rowIndex: rowIndex is represented with numeric value defined with 1 as first row, 2 as second row, and so on.
columnIndex: columnIndex is represented with uppercase letter defined with ‘A’ as first column, ‘B’ as second column, and so on.
cellIndex: This attribute represents the cell index. Cell Index is represented with column and row representation combined, i.e., ‘A1’ is defined as first cell of excel, ‘A2’ is defined as second cell of first column, ‘B1’ is defined as second cell of first row, and so on.
style(): This is a method available to define styling of the cell. Styling can be defined using JSON attribute explained later.
set(): This is a method available to set a value of the cell. Any value passed in this method is set on the cell. Value can be of any type as described above.

An example to get a cell, and set its value is:

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.cell(2,3).set('Hello World');
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.cell(2, 3).set({value: 'Hello World', type: 'string'});
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Another usage of cell() is to set a value in excel workbook. Cell accepts additional parameters value and style to update the selected cell. Or it may also be called without a value and just style object. Value object can also be a javascript string literal, number literal, or it can be a JSON object with value and type defined. Style will be explained later in examples. The only way by which oxml.js differentiate value and style is value and type defined in JSON object. cell() returns JSON object with cell details as explained above. User can update the value of cell later using set(), however this time set() will update the value. Therefore, a complete syntax of cell method is:

worksheet.cell(rowIndex, columnIndex, <value>, <style>);

Example to use cell method to set a value:

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.cell(2, 3, 'Hello World');
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.cell(2, 3, {value: 'Hello World', type: 'string'});
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Row

Row is another useful method exposed with worksheet object. As the name suggest this method let Tuser set value / style a row in worksheet. Parameters passed to a row method are rowIndex, columnIndex, values and style. RowIndex is the index of row starting from 1, and columnIndex is the index of column starting from 1, they indicate the position of cell, from which to start filling the values. Ex, with rowIndex 3 and columnIndex 2, values will be filled from cell B3 in a row. Values passed is an array collection of value type of objects, they can be combination of different value types defined earlier. The last parameter passed, but not required, is the style JSON value, which will be defined later. Syntax of row method is:

worksheet.row(rowIndex, columnIndex, <values>, <style>);

row() returns the JSON object with following attributes:

cellIndices: This is the array collection of all the cell indices defined with row, these cell indices are same as defined in cell().
cells: This is the array collection of all the cell defined with row.
style(): This method let user update the value defined with row(). This method accepts an argument values collection of value type JSON. set() will update only the cells present in cellIndices and cells array object.
set(): This method let user update the style of selected cells by row(), and cells present in cell array collection.

Example to use Row method to set values:

Another use of row() is to get a row defined in excel, however unlike cell, row() returns only cell which are already defined or set. row() with parameters only rowIndex and columnIndex will return all the already defined cells in row after rowIndex and columnIndex. This let user update or get reference to a row object. The output of row() is similar to the cells attribute with setting the values, user can set or style the selected cells with set() and style methods or row object.

Examples to get row reference:

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.row(2, 3, ['Hello', 'Wold']);
var row = worksheet.row(2, 3);
row.set(['Greetings']);
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Column

Just like row() we have another method with worksheet object, i.e., column(). As the name suggests column() selects cells in a column of worksheet. Parameters passed in row() and column() are same and thus the output, the only difference is the way they fill or select the cells. column() can be easily understood with below examples:

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.column(2, 3, ['Cost', 'Sale', 'Profit']);
worksheet.column(2, 4, [9, 12, 3]);
worksheet.column(2, 5, [10, 12, 2]);
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.column(2, 3, ['Hello', 'Wold']);
var column = worksheet.column(2, 3);
column.set(['Greetings']);
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Grid

Another useful method exposed with worksheet object is grid(). grid() let user define values in combination of rows and columns. Parameters passed in grid is similar to row() and column(), but values passed in grid() is a two dimensional array with row as the outer array, and column as the inner array. Some of the examples of grid() is:

var workbook = oxml.xlsx();
var worksheet = workbook.sheet('sheet1');
worksheet.grid(2, 3, [['Hello', 'World']]);
worksheet.grid(2, 3).set([['Greetings'], ['Jon']]);
workbook.download('workbook.xslx').then(function(){ console.log('Downloaded.'); });

Shared Formula

Another method exposed with worksheet object is sharedFormula. Although user can set a formula type cell with formula values, but sometimes for optimization, formula is shared among a range of cell. This range can be a column or row of cells. sharedFormula accepts four arguments, i.e., formula to set, startCellIndex, endCellIndex and style. Formula is the string written in formula bar of any workbook editor, startCellIndex and endCellIndex are cell indices to start the formula from and end formula to. Cell Index is the string value of appended column index and row index. style is used to apply styling which will be discussed later. When using formula type of values, we usually define type attribute as “formula” and formula attribute as the formula string in JSON. Apart from these two, user can also define value attribute, this value attribute is used to represent the cached value. Usually Excel editors show this cached value when workbook is opened in read only mode. Value here can also be a function type with rowIndex and columnIndex as parameters. This let user set cached value dynamically.