excel4node

An OOXML (xlsx) generator that supports formatting options

Installation:

npm install excel4node

Sample:

A sample.js script is provided in the code. Running this will output a sample excel workbook named Excel.xlsx

node sample.js

Usage:

Instantiate a new workook Takes optional params object to specify workbook options.
"jszip.compression" : change the zip compression method
"fileSharing" : equates to the "Password to modify" password option. This does not encrypt the workbook and users can still open the workbook as ReadOnly
"allowInterrupt" : uses an asynchronous forEach loop within code as to not block other operations if reports are being generated on the same thread as other processes that should take higher priority.

var xl = require('excel4node');
var wb = new xl.WorkBook();

var wbOpts = {
	jszip : {
		compression : 'DEFLATE'
	},
	fileSharing : {
		password : 'Password',
		userName : 'John Doe'
	},
	allowInterrupt : false
}
var wb2 = new xl.WorkBook(wbOpts);

Add a new WorkSheet to the workbook Takes optional params object to specify page margins, zoom and print view centering and whether to fit to page on print.
The sheetProtection options are the same as the "Protect Sheet" functions in the Review tab of Excel to prevent certain user editing. Setting a value to true means that that particular function is protected and the user will not be able to do that thing. All options are false by default except for 'sheet' which defaults to true if the sheetProtection attribute is set in the worksheet options, but false if it is not.

var ws = wb.WorkSheet('New Worksheet');

var wsOpts = {
	margins:{
		left : .75,
		right : .75,
		top : 1.0,
		bottom : 1.0,
		footer : .5,
		header : .5
	},
	printOptions:{
		centerHorizontal : true,
		centerVertical : false
	},
	view:{
		zoom : 100
	},
	outline:{
		summaryBelow : true
	},
	fitToPage:{
		fitToHeight: 100,
		orientation: 'landscape',
  	},
	sheetProtection : {
		autoFilter : false,
		deleteColumns : false,
		deleteRows : false,
		formatCells : false,
		formatColumns : false,
		formatRows : false,
		insertColumns : false,
		insertHyperlinks : false,
		insertRows : false,
		objects : false,
		password : 'Password',
		pivotTables : false,
		scenarios : false,
		sheet : true,
		sort : false
	}
}
var ws2 = wb.WorkSheet('New Worksheet', wsOpts);

Optionally, you can set validations for the WorkSheet

ws.setValidation({
	type: "list",
	allowBlank: 1,
	showInputMessage: 1,
	showErrorMessage: 1,
	sqref: "X2:X10",
	formulas: [
		'value1,value2'
	]
});

ws.setValidation({
	type: "list",
	allowBlank: 1,
	sqref: "B2:B10",
	formulas: [
		'=sheet2!$A$1:$A$2'
	]
});

Add a cell to a WorkSheet with some data.
Cell can take 6 data types: String, Number, Formula, Date, Link, Bool.
Cell takes two arguments: row, col

ws.Cell(1,1).String('My String');
ws.Cell(2,1).Number(5);
ws.Cell(2,2).Number(10);
ws.Cell(2,3).Formula("A2+B2");
ws.Cell(2,4).Formula("A2/C2");
ws.Cell(2,5).Date(new Date());
ws.Cell(2,6).Link('http://google.com'); or ws.Cell(2,6).Link('http://google.com','Link name');
ws.Cell(2,7).Bool(true);

Set Dimensions of Rows or Columns

ws.Row(1).Height(30);
ws.Column(1).Width(100);

Create a Style and apply it to a cell

• Font
  • Bold
    • Takes no arguments. Bolds text
  • Italics
    • Takes no arguments. Italicizes text
  • Underline
    • Takes no arguments. Underlines text
  • Family
    • Takes one argument: name of font family.
  • Color
    • Takes one argument: rbg color
  • Size
    • Takes one argument: size in Pts
  • WrapText
    • Takes no arguments. Set text wrapping to true.
  • Alignment
    • Vertical
      • Takes one argument of options top, center, bottom
    • Horizontal
      • Takes one argument of left, center, right
    • Rotation
      • Takes one argument as degrees to rotate
• Number
  • Format
    • Takes one argument: Number style string
• Fill
  • Color
    • Takes one argument: Color in rgb
  • Pattern
    • Takes one argument: pattern style (solid, lightUp, etc)
• Border
  • Takes one argument: object defining border
  • each ordinal (top, right, etc) are only required if you want to define a border. If omitted, no border will be added to that side.
  • style is required if oridinal is defined. if color is omitted, it will default to black.

 {
 		top:{
 			style:'thin',
 			color:'CCCCCC'
 		},
 		right:{
 			style:'thin',
 			color:'CCCCCC'
 		},
 		bottom:{
 			style:'thin',
 			color:'CCCCCC'
 		},
 		left:{
 			style:'thin',
 			color:'CCCCCC'
 		},
 		diagonal:{
 			style:'thin',
 			color:'CCCCCC'
 		}
 	}

var myStyle = wb.Style();
myStyle.Font.Bold();
myStyle.Font.Italics();
myStyle.Font.Underline();
myStyle.Font.Family('Times New Roman');
myStyle.Font.Color('FF0000');
myStyle.Font.Size(16);
myStyle.Font.Alignment.Vertical('top');
myStyle.Font.Alignment.Horizontal('left');
myStyle.Font.Alignment.Rotation('90');
myStyle.Font.WrapText(true);

var myStyle2 = wb.Style();
myStyle2.Font.Size(14);
myStyle2.Number.Format("$#,##0.00;($#,##0.00);-");

var myStyle3 = wb.Style();
myStyle3.Font.Size(14);
myStyle3.Number.Format("##%");
myStyle3.Fill.Pattern('solid');
mystyle3.Fill.Color('CCCCCC');
myStyle3.Border({
	top:{
		style:'thin',
		color:'CCCCCC'
	},
	bottom:{
		style:'thick'
	},
	left:{
		style:'thin'
	},
	right:{
		style:'thin'
	}
});

ws.Cell(1,1).Style(myStyle);
ws.Cell(1,2).String('My 2nd String').Style(myStyle);
ws.Cell(2,1).Style(myStyle2);
ws.Cell(2,2).Style(myStyle2);
ws.Cell(2,3).Style(myStyle2);
ws.Cell(2,4).Style(myStyle3);

Apply Formatting to Cell Syntax similar to creating styles

ws.Cell(1,1).Format.Font.Color('FF0000');
ws.Cell(1,1).Format.Fill.Pattern('solid');
ws.Cell(1,1).Format.Fill.Color('AEAEAE');

Merge Cells and apply Styles or Formats to ranges ws.Cell(row1,col1,row2,col2,merge)

ws.Cell(1,1,2,5,true).String('Merged Cells');
ws.Cell(3,1,4,5).String('Each Cell in Range Contains this String');
ws.Cell(3,1,4,5).Style(myStyle);
ws.Cell(1,1,2,5).Format.Font.Family('Arial');

Freeze Columns and Rows to prevent moving when scrolling horizontally
First example will freeze the first two columns (everything prior to the specified column);
Second example will freeze the first two columns and scroll to the 8th column.
Third example will freeze the first two rows (everything prior to the specified row);
Forth example will freeze the first two rows and scroll to the 8th row.
See "Series with frozen Row" tab in sample output workbook

ws.Column(3).Freeze();
ws.Column(3).Freeze(8);
ws.Row(3).Freeze();
ws.Row(3).Freeze(8);

Set a row to be a filter row Optionally specify start and end columns If no arguments passed, will add filter to any populated columns See "Departmental Spending Report" tab in sample output workbook

ws.Row(1).Filter();
ws.Row(1).Filter(1,8);

Hide a specific Row or Column

ws.Row(2).Hide();
ws.Column(2).Hide();

Set Groupings on Rows and optionally collapse them.
See "Groupings Summary Top" and "Groupings Summary Bottom" in sample output.

ws.Row(rowNum).Group(level,isCollapsed)
ws.Row(1).Group(1,true)

Insert an image into a WorkSheet for Image takes one argument which is relative path to image from node script
Image can be passed optional Position which takes 4 arguments
img.Position(row, col, [rowOffset], [colOffset])
row = top left corner of image will be anchored to top of this row
col = top left corner of image will be anchored to left of this column
rowOffset = offset from top of row in EMUs
colOfset = offset from left of col in EMUs

Currently images should be saved at a resolution of 96dpi.

var img1 = ws.Image(imgPath);
img1.Position(1,1);

var img2 = ws.Image(imgPath2).Position(3,3,1000000,2000000);

Write the Workbook to local file synchronously or Write the Workbook to local file asynchrously or Send file via node response

wb.write("My Excel File.xlsx");
wb.write("My Excel File.xlsx",function(err){ ... });
wb.write("My Excel File.xlsx",res);