var Excel = require("exceljs");

var workbook = new Excel.Workbook();

var sheet = workbook.addWorksheet("My Sheet");

// Iterate over all sheets
// Note: workbook.worksheets.forEach will still work but this is better
workbook.eachSheet(function(worksheet, sheetId) {
    // ...
});

// fetch sheet by name
var worksheet = workbook.getWorksheet("My Sheet");

// fetch sheet by id
var worksheet = workbook.getWorksheet(1);

// Add column headers and define column keys and widths
// Note: these column structures are a workbook-building convenience only,
// apart from the column width, they will not be fully persisted.
worksheet.columns = [
    { header: "Id", key: "id", width: 10 },
    { header: "Name", key: "name", width: 32 },
    { header: "D.O.B.", key: "DOB", width: 10 }
];

// Access an individual columns by key, letter and 1-based column number
var idCol = worksheet.getColumn("id");
var nameCol = worksheet.getColumn("B");
var dobCol = worksheet.getColumn(3);
    
// set column properties
dobCol.header = "Date of Birth"; // Note: will overwrite cell value C1
dobCol.header = ["Date of Birth", "A.K.A. D.O.B."]; // Note: this will overwrite cell values C1:C2
dobCol.key = "dob"; // from this point on, this column will be indexed by "dob" and not "DOB"
dobCol.width = 15;

// Add a couple of Rows by key-value, after the last current row, using the column keys
worksheet.addRow({id: 1, name: "John Doe", dob: new Date(1970,1,1)});
worksheet.addRow({id: 2, name: "Jane Doe", dob: new Date(1965,1,7)});

// Add a row by contiguous Array (assign to columns A, B & C)
worksheet.addRow([3, "Sam", new Date()]);

// Add a row by sparse Array (assign to columns A, E & I)
var rowValues = [];
rowValues[1] = 4;
rowValues[5] = "Kyle";
rowValues[9] = new Date();
worksheet.addRow(rowValues);

// Get a row object. If it doesn't already exist, a new empty one will be returned
var row = worksheet.getRow(5);

// Set a specific row height
row.height = 42.5;

row.getCell(1).value = 5; // A5's value set to 5
row.getCell("name").value = "Zeb"; // B5's value set to "Zeb" - assuming column 2 is still keyed by name
row.getCell("C").value = new Date(); // C5's value set to now

// Get a row as a sparse array
// Note: interface change: worksheet.getRow(4) ==> worksheet.getRow(4).values
row = worksheet.getRow(4).values;
expect(row[5]).toEqual("Kyle");

// assign row values by contiguous array (where array element 0 has a value)
row.values = [1,2,3];
expect(row.getCell(1).value).toEqual(1);
expect(row.getCell(2).value).toEqual(2);
expect(row.getCell(3).value).toEqual(3);

// assign row values by sparse array  (where array element 0 is undefined)
var values = []
values[5] = 7;
values[10] = "Hello, World!";
row.values = values;
expect(row.getCell(1).value).toBeNull();
expect(row.getCell(5).value).toEqual(7);
expect(row.getCell(10).value).toEqual("Hello, World!");

// assign row values by object, using column keys
row.values = {
    id: 13,
    name: "Thing 1",
    dob: new Date()
};

// Iterate over all rows that have values in a worksheet
// Note: interface change - argument order is now row, rowNumber
worksheet.eachRow(function(row, rowNumber) {
    console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
});

// Iterate over all non-null cells in a row
row.eachCell(function(cell, colNumber) {
    console.log("Cell " + colNumber + " = " + cell.value);
});

## Handling Individual Cells

```javascript
// Modify/Add individual cell
worksheet.getCell("C3").value = new Date(1968, 5, 1);

// query a cell's type
expect(worksheet.getCell("C3").type).toEqual(Excel.ValueType.Date);

// merge a range of cells
worksheet.mergeCells("A4:B5");

// merge by top-left, bottom-right
worksheet.mergeCells("G10", "H11");
worksheet.mergeCells(10,11,12,13); // top,left,bottom,right

// ... merged cells are linked
worksheet.getCell("B5").value = "Hello, World!";
expect(worksheet.getCell("A4").value).toBe(worksheet.getCell("B5").value);
expect(worksheet.getCell("A4")).toBe(worksheet.getCell("B5").master);

// display value as "1 3/5"
ws.getCell("A1").value = 1.6;
ws.getCell("A1").numFmt = "# ?/?";

// display value as "1.60%"
ws.getCell("B1").value = 0.016;
ws.getCell("B1").numFmt = "0.00%";

// for the wannabe graphic designers out there
ws.getCell("A1").font = {
    name: "Comic Sans MS",
    family: 4,
    size: 16,
    underline: true,
    bold: true
};

// for the graduate graphic designers...
ws.getCell("A2").font = {
    name: "Arial Black",
    color: { argb: "FF00FF00" },
    family: 2,
    size: 14,
    italic: true
};

// note: the cell will store a reference to the font object assigned.
// If the font object is changed afterwards, the cell font will change also...
var font = { name: "Arial", size: 12 };
ws.getCell("A3").font = font;
font.size = 20; // Cell A3 now has font size 20!

// Cells that share similar fonts may reference the same font object after
// the workbook is read from file or stream

// set cell alignment to top-left, middle-center, bottom-right
ws.getCell("A1").alignment = { vertical: "top", horizontal: "left" };
ws.getCell("B1").alignment = { vertical: "middle", horizontal: "center" };
ws.getCell("C1").alignment = { vertical: "bottom", horizontal: "right" };

// set cell to wrap-text
ws.getCell("D1").alignment = { wrapText: true };

// set cell indent to 1
ws.getCell("E1").alignment = { indent: 1 };

// set cell text rotation to 30deg upwards, 45deg downwards and vertical text
ws.getCell("F1").alignment = { textRotation: 30 };
ws.getCell("G1").alignment = { textRotation: -45 };
ws.getCell("H1").alignment = { textRotation: "vertical" };

// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
    .then(function() {
        // use workbook
    });

// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());

// write to a file
var workbook = createAndFillWorkbook();
workbook.xlsx.writeFile(filename)
    .then(function() {
        // done
    });

// write to a stream
workbook.xlsx.write(stream)
    .then(function() {
        // done
    });