elxml
A minimalistic Excel OOXML writer.
The main purpose is to create simple Excel files via JavaScript. The current implementation supports
- Multiple sheets
- Creation of rows and cells
- Column width definition
- PatternFills for cells
- Borders for cells
- Number formats for cells
- Fonts for cells
- Merge cells
- AutoFilter
elxml allows to use string tables to save memory.
Uses xmlbuilder-js, archiver and underscore
Usage
Create a workbook:
var excel = ;var wb = excel;
Create a sheet within the workbook:
var sheet = wb;
Add a row with a single cell and set a value for the cell
var row = sheet;var cell = row; // the value is a date in ISO standard notationcell;
Last step is to save the workbook:
wb;
Advanced usage
Number formats
The above example creates an Excel file with a date cell but without a proper number format.
To apply the number format to the cell we have to create a cell style. Cell styles are based on a default cell style which defines the number format, fill, borders and the font for those cells which don't use any cell style. In elxml the default is: Calibri with a size of 11, no borders, no fill, no number format.
Lets set a date number format to see a better formatted date in Excel:
// create a date formatvar dateFrmt = wb; // get the predefined default stylevar defStyle = wb; // derive a new style from the default stylevar dateStyle = wb; // apply the stylecell;
Another Example
// create a date formatvar dateFrmt = wb; // create a default stylevar defStyle = wb;// derive a new style from the default stylevar dateStyle = wb; // format value to MSDATE formatvar value = ; // set cell typevar cell = row; // apply the stylecell; // write data to cellcell;
To see which number formats are available take a look at the OOXML spec.
Strings
By default strings are saved as inline strings.
// add a cell with a string valuevar cell1 = row; // excel.CELL_TYPE_STRING is defaultcell1; var cell2 = row; // set typecell2;
In case you have tables with many similar string values you can enable the "shared string" function. Note that this must be done for every cell as inline strings are the default.
// add a cell with a string valuevar cell = row;cell;
This will save memory while saving and speed up loading of the created Excel file.
Fills
You can define pattern fills. Possible options for a pattern fill are: fgColor
, bgColor
and type
.
Lets create a red solid fill:
// create a color (RGBA)var red = wbcolor255000;// create a pattern fillvar redFill = wb;// create a cell style with the red fillvar redFillStyle = wb;// apply the stylecell;
Borders
You can define the borders for a cell. First you have to create a border representation object which is used to define the border style.
// create a color (RGBA)var black = wbcolor0000;// create a thick border presentation with a black colorvar thickBorderPr = wb;// create a border type, bottom line is set to thickBorderPrvar border = wb;// create a cell style with the bordervar borderStyle = wb;// apply the stylecell;
Fonts
You can create new fonts which are derived from the default font. You can set the size and whether the font is bold or not (default).
// create a bold font, the font is derived from the default fontvar boldFont = wb;// create a cell style with the bold fontvar boldFontStyle = wb;// apply the stylecell;
Column width
Define the width for one or more columns:
// set the width of the first column to 30sheet;// set the width of columns 2 - 5 to 50sheet;
Merge cells
// merge cells horizontalsheet;// merge cells verticalsheet;
AutoFilter
// use a range which contains data! sheet;
Change history
0.1.3 - add callback to Workbook.save method
0.1.4 - fixed issues #8, #9
0.1.5 - add support for italics, text rotation and text wrapping
0.1.6 - a 'Standard' style is created by default, access it via the getStyle function
0.2.0 - add Workbook.saveToStream to save the resulting ZIP directly to a file stream
0.2.1 - fixed issue with wrong style IDs
0.3.0 - add basic support for AutoFilter, fixed error in string table
0.3.1 - add Row.setStyleForAllCells convenience method
0.3.2 - added font underline property