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();
workbook.lastPrinted = new Date(2016, 9, 27);

// Set workbook dates to 1904 date system
workbook.properties.date1904 = true;

workbook.views = [
  {
    x: 0, y: 0, width: 10000, height: 20000,
    firstSheet: 0, activeTab: 1, visibility: 'visible'
  }
]

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

// create a sheet with red tab colour
var sheet = workbook.addWorksheet('My Sheet', {properties:{tabColor:{argb:'FFC0000'}}});
 
// create a sheet where the grid lines are hidden
var sheet = workbook.addWorksheet('My Sheet', {properties: {showGridLines: false}});
 
// create a sheet with the first row and column frozen
var sheet = workbook.addWorksheet('My Sheet', {views:[{xSplit: 1, ySplit:1}]});

// Create a worksheet
var sheet = workbook.addWorksheet('My Sheet');
 
// Remove the worksheet using worksheet id
workbook.removeWorksheet(sheet.id)

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

// make worksheet visible
worksheet.state = 'show';
 
// make worksheet hidden
worksheet.state = 'hidden';

// create new sheet with properties
var worksheet = workbook.addWorksheet('sheet', {properties:{tabColor:{argb:'FF00FF00'}}});
 
// create a new sheet writer with properties
var worksheetWriter = workbookWriter.addSheet('sheet', {properties:{outlineLevelCol:1}});
 
// adjust properties afterwards (not supported by worksheet-writer)
worksheet.properties.outlineLevelCol = 2;
worksheet.properties.defaultRowHeight = 15;

// create new sheet with pageSetup settings for A4 - landscape
var worksheet =  workbook.addWorksheet('sheet', {
  pageSetup:{paperSize: 9, orientation:'landscape'}
});
 
// create a new sheet writer with pageSetup settings for fit-to-page
var worksheetWriter = workbookWriter.addSheet('sheet', {
  pageSetup:{fitToPage: true, fitToHeight: 5, fitToWidth: 7}
});
 
// adjust pageSetup settings afterwards
worksheet.pageSetup.margins = {
  left: 0.7, right: 0.7,
  top: 0.75, bottom: 0.75,
  header: 0.3, footer: 0.3
};
 
// Set Print Area for a sheet
worksheet.pageSetup.printArea = 'A1:G20';
 
// Repeat specific rows on every printed page
worksheet.pageSetup.printTitlesRow = '1:3';
 

worksheet.views = [
    {state: 'frozen', xSplit: 2, ySplit: 3, topLeftCell: 'G10', activeCell: 'A1'}
];

worksheet.views = [
    {state: 'split', xSplit: 2000, ySplit: 3000, topLeftCell: 'G10', activeCell: 'A1'}
];

worksheet.autoFilter = 'A1:C1';

// Set an auto filter from A1 to C1
worksheet.autoFilter = {
    from: 'A1',
    to: 'C1',
}
 
// Set an auto filter from the cell in row 3 and column 1
// to the cell in row 5 and column 12
worksheet.autoFilter = {
    from: {
        row: 3,
        column: 1
    },
    to: {
        row: 5,
        column: 12
    }
}
 
// Set an auto filter from D3 to the
// cell in row 7 and column 5
worksheet.autoFilter = {
    from: 'D3',
    to: {
        row: 7,
        column: 5
    }
}

// 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, outlineLevel: 1 }
];
 
// 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;
 
// Hide the column if you'd like
dobCol.hidden = true;
 
// set an outline level for columns
worksheet.getColumn(4).outlineLevel = 0;
worksheet.getColumn(5).outlineLevel = 1;
 
// columns support a readonly field to indicate the collapsed state based on outlineLevel
expect(worksheet.getColumn(4).collapsed).to.equal(false);
expect(worksheet.getColumn(5).collapsed).to.equal(true);
 
// 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 column of new values
worksheet.getColumn(6).values = [1,2,3,4,5];
 
// add a sparse column of values
worksheet.getColumn(7).values = [,,2,3,,5,,7,,,,11];
 
// cut one or more columns (columns to the right are shifted left)
// If column properties have been definde, they will be cut or moved accordingly
// Known Issue: If a splice causes any merged cells to move, the results may be unpredictable
worksheet.spliceColumns(3,2);
 
// remove one column and insert two more.
// Note: columns 4 and above will be shifted right by 1 column.
// Also: If the worksheet has more rows than values in the colulmn inserts,
//  the rows will still be shifted as if the values existed
var newCol3Values = [1,2,3,4,5];
var newCol4Values = ['one', 'two', 'three', 'four', 'five'];
worksheet.spliceColumns(3, 1, newCol3Values, newCol4Values);
 

// 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);
 
// Add an array of rows
var rows = [
    [5,'Bob',new Date()], // row by array
    {id:6, name: 'Barbara', dob: new Date()}
];
worksheet.addRows(rows);
 
// 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;
 
// make row hidden
row.hidden = true;
 
// set an outline level for rows
worksheet.getRow(4).outlineLevel = 0;
worksheet.getRow(5).outlineLevel = 1;
 
// rows support a readonly field to indicate the collapsed state based on outlineLevel
expect(worksheet.getRow(4).collapsed).to.equal(false);
expect(worksheet.getRow(5).collapsed).to.equal(true);
 
 
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()
};
 
// Insert a page break below the row
row.addPageBreak();
 
// 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);
});
 
// Cut one or more rows (rows below are shifted up)
// Known Issue: If a splice causes any merged cells to move, the results may be unpredictable
worksheet.spliceRows(4,3);
 
// remove one row and insert two more.
// Note: rows 4 and below will be shifted down by 1 row.
var newRow3Values = [1,2,3,4,5];
var newRow4Values = ['one', 'two', 'three', 'four', 'five'];
worksheet.spliceRows(3, 1, newRow3Values, newRow4Values);
 
// Cut one or more cells (cells to the right are shifted left)
// Note: this operation will not affect other rows
row.splice(3,2);
 
// remove one cell and insert two more (cells to the right of the cut cell will be shifted right)
row.splice(4,1,'new value 1', 'new value 2');
 
// Commit a completed row to stream
row.commit();
 
// row metrics
var rowSize = row.cellCount;
var numValues = row.actualCellCount;

var cell = worksheet.getCell('C3');
 
// Modify/Add individual cell
cell.value = new Date(1968, 5, 1);
 
// query a cell's type
expect(cell.type).toEqual(Excel.ValueType.Date);
 
// use string value of cell
myInput.value = cell.text;
 
// use html-safe string for rendering...
var html = '<div>' + cell.html + '</div>';
 

// 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 (or get) a name for a cell (will overwrite any other names that cell had)
worksheet.getCell('A1').name = 'PI';
expect(worksheet.getCell('A1').name).to.equal('PI');
 
// assign (or get) an array of names for a cell (cells can have more than one name)
worksheet.getCell('A1').names = ['thing1', 'thing2'];
expect(worksheet.getCell('A1').names).to.have.members(['thing1', 'thing2']);
 
// remove a name from a cell
worksheet.getCell('A1').removeName('thing1');
expect(worksheet.getCell('A1').names).to.have.members(['thing2']);

// Specify list of valid values (One, Two, Three, Four).
// Excel will provide a dropdown with these values.
worksheet.getCell('A1').dataValidation = {
    type: 'list',
    allowBlank: true,
    formulae: ['"One,Two,Three,Four"']
};
 
// Specify list of valid values from a range.
// Excel will provide a dropdown with these values.
    worksheet.getCell('A1').dataValidation = {
        type: 'list',
        allowBlank: true,
        formulae: ['$D$5:$F$5']
};
 
// Specify Cell must be a whole number that is not 5.
// Show the user an appropriate error message if they get it wrong
worksheet.getCell('A1').dataValidation = {
    type: 'whole',
    operator: 'notEqual',
    showErrorMessage: true,
    formulae: [5],
    errorStyle: 'error',
    errorTitle: 'Five',
    error: 'The value must not be Five'
};
 
// Specify Cell must be a decomal number between 1.5 and 7.
// Add 'tooltip' to help guid the user
worksheet.getCell('A1').dataValidation = {
    type: 'decimal',
    operator: 'between',
    allowBlank: true,
    showInputMessage: true,
    formulae: [1.5, 7],
    promptTitle: 'Decimal',
    prompt: 'The value must between 1.5 and 7'
};
 
// Specify Cell must be have a text length less than 15
worksheet.getCell('A1').dataValidation = {
    type: 'textLength',
    operator: 'lessThan',
    showErrorMessage: true,
    allowBlank: true,
    formulae: [15]
};
 
// Specify Cell must be have be a date before 1st Jan 2016
worksheet.getCell('A1').dataValidation = {
    type: 'date',
    operator: 'lessThan',
    showErrorMessage: true,
    allowBlank: true,
    formulae: [new Date(2016,0,1)]
};

// assign a style to a cell
ws.getCell('A1').numFmt = '0.00%';
 
// Apply styles to worksheet columns
ws.columns = [
    { 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.00;[Red]\-"£"#,##0.00';
 
// 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