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 jszip options. More to come.

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

var wbOpts = {
	jszip:{
		compression:'DEFLATE'
	}
}
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.

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',
  },
}
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'
	]
});

Add a cell to a WorkSheet with some data.
Cell can take 4 data types: String, Number, Formula, Date, Link.
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');

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);