var Excel = require("exceljs");

var workbook = new Excel.Workbook();

workbook.creator = "Me";
workbook.lastModifiedBy = "Her";
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();

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

// Note: will overwrite cell value C1
dobCol.header = "Date of Birth";

// Note: this will overwrite cell values C1:C2
dobCol.header = ["Date of Birth", "A.K.A. D.O.B."];

// from this point on, this column will be indexed by "dob" and not "DOB"
dobCol.key = "dob";

dobCol.width = 15;

// iterate over all current cells in this column
dobCol.eachCell(function(cell, rowNumber) {
    // ...
});

// iterate over all current cells in this column including empty cells
dobCol.eachCell({ includeEmpty: true }, function(cell, rowNumber) {
    // ...
});

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

// Get the last editable row in a worksheet (or undefined if there are none)
var row = worksheet.lastRow;

// 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
worksheet.eachRow(function(row, rowNumber) {
    console.log("Row " + rowNumber + " = " + JSON.stringify(row.values));
});

// Iterate over all rows (including empty rows) in a worksheet
worksheet.eachRow({ includeEmpty: true }, 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);
});

// Iterate over all cells in a row (including empty cells)
row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
    console.log("Cell " + colNumber + " = " + cell.value);
});

// Commit a completed row to stream
row.commit();

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

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

// ... merged cells share the same style object
expect(worksheet.getCell("B5").style).toBe(worksheet.getCell("A4").style);
worksheet.getCell("B5").style.font = myFonts.arial;
expect(worksheet.getCell("A4").style.font).toBe(myFonts.arial);

// unmerging the cells breaks the style links
worksheet.unMergeCells("A4");
expect(worksheet.getCell("B5").style).not.toBe(worksheet.getCell("A4").style);
expect(worksheet.getCell("B5").style.font).not.toBe(myFonts.arial);

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

// assign a style to a cell
ws.getCell("A1").numFmt = "0.00%";

// Apply styles to worksheet columns
ws.columnscolumns = [
    { header: "Id", key: "id", width: 10 },
    { header: "Name", key: "name", width: 32, style: { font: { name: "Arial Black" } } },
    { header: "D.O.B.", key: "DOB", width: 10, style: { numFmt: "dd/mm/yyyy" } }
];

// Set Column 3 to Currency Format
ws.getColumn(3).numFmt = "£#,##0;[Red]-£#,##0";

// Set Row 2 to Comic Sans.
ws.getRow(2).font = { name: "Comic Sans MS", family: 4, size: 16, underline: "double", bold: true };

// 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" };

// set single thin border around A1
ws.getCell("A1").border = {
    top: {style:"thin"},
    left: {style:"thin"},
    bottom: {style:"thin"},
    right: {style:"thin"}
};

// set double thin green border around A3
ws.getCell("A3").border = {
    top: {style:"double", color: {argb:"FF00FF00"}},
    left: {style:"double", color: {argb:"FF00FF00"}},
    bottom: {style:"double", color: {argb:"FF00FF00"}},
    right: {style:"double", color: {argb:"FF00FF00"}}
};

// set thick red cross in A5
ws.getCell("A5").border = {
    diagonal: {up: true, down: true, style:"thick", color: {argb:"FFFF0000"}}
};

// fill A1 with red darkVertical stripes
ws.getCell("A1").fill = {
    type: "pattern",
    pattern:"darkVertical",
    fgColor:{argb:"FFFF0000"}
};

// fill A2 with yellow dark trellis and blue behind
ws.getCell("A2").fill = {
    type: "pattern",
    pattern:"darkTrellis",
    fgColor:{argb:"FFFFFF00"},
    bgColor:{argb:"FF0000FF"}
};

// fill A3 with blue-white-blue gradient from left to right
ws.getCell("A3").fill = {
    type: "gradient",
    gradient: "angle",
    degree: 0,
    stops: [
        {position:0, color:{argb:"FF0000FF"}},
        {position:0.5, color:{argb:"FFFFFFFF"}},
        {position:1, color:{argb:"FF0000FF"}}
    ]
};


// fill A4 with red-green gradient from center
ws.getCell("A2").fill = {
    type: "gradient",
    gradient: "path",
    center:{left:0.5,top:0.5},
    stops: [
        {position:0, color:{argb:"FFFF0000"}},
        {position:1, color:{argb:"FF00FF00"}}
    ]
};

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

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

// read from a stream
var workbook = new Excel.Workbook();
workbook.csv.read(stream)
    .then(function(worksheet) {
        // use workbook or worksheet
    });

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

// read from a file with European Dates
var workbook = new Excel.Workbook();
var options = {
    dateFormats: ["DD/MM/YYYY"]
};
workbook.csv.readFile(filename, options)
    .then(function(worksheet) {
        // use workbook or worksheet
    });


// read from a file with custom value parsing
var workbook = new Excel.Workbook();
var options = {
    map: function(value, index) {
        switch(index) {
            case 0:
                // column 1 is string
                return value;
            case 1:
                // column 2 is a date
                return new Date(value);
            case 2:
                // column 3 is JSON of a formula value
                return JSON.parse(value);
            default:
                // the rest are numbers
                return parseFloat(value);
        }
    }
};
workbook.csv.readFile(filename, options)
    .then(function(worksheet) {
        // use workbook or worksheet
    });

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

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


// read from a file with European Date-Times
var workbook = new Excel.Workbook();
var options = {
    dateFormat: "DD/MM/YYYY HH:mm:ss"
};
workbook.csv.readFile(filename, options)
    .then(function(worksheet) {
        // use workbook or worksheet
    });


// read from a file with custom value formatting
var workbook = new Excel.Workbook();
var options = {
    map: function(value, index) {
        switch(index) {
            case 0:
                // column 1 is string
                return value;
            case 1:
                // column 2 is a date
                return moment(value).format("YYYY-MM-DD");
            case 2:
                // column 3 is a formula, write just the result
                return value.result;
            default:
                // the rest are numbers
                return value;
        }
    }
};
workbook.csv.readFile(filename, options)
    .then(function(worksheet) {
        // use workbook or worksheet
    });

// construct a streaming XLSX workbook writer with styles and shared strings
var options = {
    filename: "./streamed-workbook.xlsx",
    useStyles: true,
    useSharedStrings: true
};
var workbook = new Excel.stream.xlsx.WorkbookWriter(options);

worksheet.addRow({
   id: i,
   name: theName,
   etc: someOtherDetail
}).commit();

worksheet.mergeCells("A1:B2");
worksheet.getCell("A1").value = "I am merged";
worksheet.getCell("C1").value = "I am not";
worksheet.getCell("C2").value = "Neither am I";
worksheet.getRow(2).commit(); // now rows 1 and two are committed.

// Finished adding data. Commit the worksheet
worksheet.commit();

// Finished the workbook.
workbook.commit();

    invalid signature: 0x80014