const ExcelJS = require('exceljs');

const ExcelJS = require('exceljs/dist/es5');

// exceljs 所需的 polyfills
require('core-js/modules/es.promise');
require('core-js/modules/es.string.includes');
require('core-js/modules/es.object.assign');
require('core-js/modules/es.object.keys');
require('core-js/modules/es.symbol');
require('core-js/modules/es.symbol.async-iterator');
require('regenerator-runtime/runtime');

const ExcelJS = require('exceljs/dist/es5');

const workbook = new ExcelJS.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);

// 将工作簿日期设置为 1904 年日期系统
workbook.properties.date1904 = true;

// 在加载时强制工作簿计算属性
workbook.calcProperties.fullCalcOnLoad = true;

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

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

// 创建带有红色标签颜色的工作表
const sheet = workbook.addWorksheet('My Sheet', {
  properties: { tabColor: { argb: 'FFC0000' } },
});

// 创建一个隐藏了网格线的工作表
const sheet = workbook.addWorksheet('My Sheet', {
  views: [{ showGridLines: false }],
});

// 创建一个第一行和列冻结的工作表
const sheet = workbook.addWorksheet('My Sheet', {
  views: [{ xSplit: 1, ySplit: 1 }],
});

// 使用A4设置的页面设置设置创建新工作表 - 横向
const worksheet = workbook.addWorksheet('My Sheet', {
  pageSetup: { paperSize: 9, orientation: 'landscape' },
});

// 创建一个具有页眉页脚的工作表
const sheet = workbook.addWorksheet('My Sheet', {
  headerFooter: { firstHeader: 'Hello Exceljs', firstFooter: 'Hello World' },
});

// 创建一个冻结了第一行和第一列的工作表
const sheet = workbook.addWorksheet('My Sheet', {
  views: [{ state: 'frozen', xSplit: 1, ySplit: 1 }],
});

// 创建工作表
const sheet = workbook.addWorksheet('My Sheet');

// 使用工作表 id 删除工作表
workbook.removeWorksheet(sheet.id);

// 遍历所有工作表
// 注意： workbook.worksheets.forEach 仍然是可以正常运行的， 但是以下的方式更好
workbook.eachSheet(function (worksheet, sheetId) {
  // ...
});

// 按 name 提取工作表
const worksheet = workbook.getWorksheet('My Sheet');

// 按 id 提取工作表
const worksheet = workbook.getWorksheet(1);

// 使工作表可见
worksheet.state = 'visible';

// 隐藏工作表
worksheet.state = 'hidden';

// 从“隐藏/取消隐藏”对话框中隐藏工作表
worksheet.state = 'veryHidden';

// 创建具有属性的新工作表
const worksheet = workbook.addWorksheet('sheet', {
  properties: { tabColor: { argb: 'FF00FF00' } },
});

// 创建一个具有属性的可写的新工作表
const worksheetWriter = workbookWriter.addWorksheet('sheet', {
  properties: { outlineLevelCol: 1 },
});

// 之后调整属性（工作表读写器不支持该操作）
worksheet.properties.outlineLevelCol = 2;
worksheet.properties.defaultRowHeight = 15;

// 使用 A4 横向的页面设置创建新工作表
const worksheet = workbook.addWorksheet('sheet', {
  pageSetup: { paperSize: 9, orientation: 'landscape' },
});

// 使用适合页面的pageSetup设置创建一个新的工作表编写器
const worksheetWriter = workbookWriter.addWorksheet('sheet', {
  pageSetup: { fitToPage: true, fitToHeight: 5, fitToWidth: 7 },
});

// 之后调整页面设置配置
worksheet.pageSetup.margins = {
  left: 0.7,
  right: 0.7,
  top: 0.75,
  bottom: 0.75,
  header: 0.3,
  footer: 0.3,
};

// 设置工作表的打印区域
worksheet.pageSetup.printArea = 'A1:G20';

// 通过使用 `&&` 分隔打印区域来设置多个打印区域
worksheet.pageSetup.printArea = 'A1:G10&&A11:G20';

// 在每个打印页面上重复特定的行
worksheet.pageSetup.printTitlesRow = '1:3';

// 在每个打印页面上重复特定列
worksheet.pageSetup.printTitlesColumn = 'A:C';

// 创建一个带有页眉和页脚的工作表
var sheet = workbook.addWorksheet('My Sheet', {
  headerFooter: { firstHeader: 'Hello Exceljs', firstFooter: 'Hello World' },
});

// 创建一个带有页眉和页脚可写的工作表
var worksheetWriter = workbookWriter.addWorksheet('sheet', {
  headerFooter: { firstHeader: 'Hello Exceljs', firstFooter: 'Hello World' },
});
// 代码中出现的&开头字符对应变量，相关信息可查阅下文的变量表
// 设置页脚(默认居中),结果：“第 2 页，共 16 页”
worksheet.headerFooter.oddFooter = '第 &P 页，共 &N 页';

// 将页脚（默认居中）设置为粗体，结果是：“第2页，共16页”
worksheet.headerFooter.oddFooter = 'Page &P of &N';

// 将左页脚设置为 18px 并以斜体显示。 结果：“第2页，共16页”
worksheet.headerFooter.oddFooter = '&LPage &P of &N';

// 将中间标题设置为灰色Aril，结果为：“ 52 exceljs”
worksheet.headerFooter.oddHeader = '&C&KCCCCCC&"Aril"52 exceljs';

// 设置页脚的左，中和右文本。 结果：页脚左侧为“ Exceljs”。 页脚中心的“ demo.xlsx”。 页脚右侧的“第2页”
worksheet.headerFooter.oddFooter = '&Lexceljs&C&F&RPage &P';

// 在首页添加不同的页眉和页脚
worksheet.headerFooter.differentFirst = true;
worksheet.headerFooter.firstHeader = 'Hello Exceljs';
worksheet.headerFooter.firstFooter = 'Hello World';

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

// 将自动筛选器设置为从 A1 到 C1
worksheet.autoFilter = {
  from: 'A1',
  to: 'C1',
};

// 将自动筛选器设置为从第3行第1列的单元格到第5行第12列的单元格
worksheet.autoFilter = {
  from: {
    row: 3,
    column: 1,
  },
  to: {
    row: 5,
    column: 12,
  },
};

// 将自动筛选器设置为从D3到第7行第5列中的单元格
worksheet.autoFilter = {
  from: 'D3',
  to: {
    row: 7,
    column: 5,
  },
};

// 添加列标题并定义列键和宽度
// 注意：这些列结构仅是构建工作簿的方便之处，除了列宽之外，它们不会完全保留。
worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 },
];

// 通过键，字母和基于1的列号访问单个列
const idCol = worksheet.getColumn('id');
const nameCol = worksheet.getColumn('B');
const dobCol = worksheet.getColumn(3);

// 设置列属性

// 注意：将覆盖 C1 单元格值
dobCol.header = 'Date of Birth';

// 注意：这将覆盖 C1:C2 单元格值
dobCol.header = ['Date of Birth', 'A.K.A. D.O.B.'];

// 从现在开始，此列将以 “dob” 而不是 “DOB” 建立索引
dobCol.key = 'dob';

dobCol.width = 15;

// 如果需要，隐藏列
dobCol.hidden = true;

// 为列设置大纲级别
worksheet.getColumn(4).outlineLevel = 0;
worksheet.getColumn(5).outlineLevel = 1;

// 列支持一个只读字段，以指示基于 `OutlineLevel` 的折叠状态
expect(worksheet.getColumn(4).collapsed).to.equal(false);
expect(worksheet.getColumn(5).collapsed).to.equal(true);

// 遍历此列中的所有当前单元格
dobCol.eachCell(function (cell, rowNumber) {
  // ...
});

// 遍历此列中的所有当前单元格，包括空单元格
dobCol.eachCell({ includeEmpty: true }, function (cell, rowNumber) {
  // ...
});

// 添加一列新值
worksheet.getColumn(6).values = [1, 2, 3, 4, 5];

// 添加稀疏列值
worksheet.getColumn(7).values = [, , 2, 3, , 5, , 7, , , , 11];

// 剪切一列或多列（右边的列向左移动）
// 如果定义了列属性，则会相应地对其进行切割或移动
// 已知问题：如果拼接导致任何合并的单元格移动，结果可能是不可预测的
worksheet.spliceColumns(3, 2);

// 删除一列，再插入两列。
// 注意：第4列及以上的列将右移1列。
// 另外：如果工作表中的行数多于列插入项中的值，则行将仍然被插入，就好像值存在一样。
const newCol3Values = [1, 2, 3, 4, 5];
const newCol4Values = ['one', 'two', 'three', 'four', 'five'];
worksheet.spliceColumns(3, 1, newCol3Values, newCol4Values);

// 获取一个行对象。如果尚不存在，则将返回一个新的空对象
const row = worksheet.getRow(5);

// Get multiple row objects. If it doesn't already exist, new empty ones will be returned
const rows = worksheet.getRows(5, 2); // start, length (>0, else undefined is returned)

// 获取工作表中的最后一个可编辑行（如果没有，则为 `undefined`）
const row = worksheet.lastRow;

// 设置特定的行高
row.height = 42.5;

// 隐藏行
row.hidden = true;

// 为行设置大纲级别
worksheet.getRow(4).outlineLevel = 0;
worksheet.getRow(5).outlineLevel = 1;

// 行支持一个只读字段，以指示基于 `OutlineLevel` 的折叠状态
expect(worksheet.getRow(4).collapsed).to.equal(false);
expect(worksheet.getRow(5).collapsed).to.equal(true);

row.getCell(1).value = 5; // A5 的值设置为5
row.getCell('name').value = 'Zeb'; // B5 的值设置为 “Zeb” - 假设第2列仍按名称键入
row.getCell('C').value = new Date(); // C5 的值设置为当前时间

// 获取行并作为稀疏数组返回
// 注意：接口更改：worksheet.getRow(4) ==> worksheet.getRow(4).values
row = worksheet.getRow(4).values;
expect(row[5]).toEqual('Kyle');

// 通过连续数组分配行值（其中数组元素 0 具有值）
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);

// 通过稀疏数组分配行值（其中数组元素 0 为 `undefined`）
const 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!');

// 使用列键按对象分配行值
row.values = {
  id: 13,
  name: 'Thing 1',
  dob: new Date(),
};

// 在该行下方插入一个分页符
row.addPageBreak();

// 遍历工作表中具有值的所有行
worksheet.eachRow(function (row, rowNumber) {
  console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});

// 遍历工作表中的所有行（包括空行）
worksheet.eachRow({ includeEmpty: true }, function (row, rowNumber) {
  console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});

// 连续遍历所有非空单元格
row.eachCell(function (cell, colNumber) {
  console.log('Cell ' + colNumber + ' = ' + cell.value);
});

// 遍历一行中的所有单元格（包括空单元格）
row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
  console.log('Cell ' + colNumber + ' = ' + cell.value);
});

// 提交给流一个完成的行
row.commit();

// 行尺寸
const rowSize = row.cellCount;
const numValues = row.actualCellCount;

// 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)
const rowValues = [];
rowValues[1] = 4;
rowValues[5] = 'Kyle';
rowValues[9] = new Date();
worksheet.addRow(rowValues);

// Add a row with inherited style
// This new row will have same style as last row
// And return as row object
const newRow = worksheet.addRow(rowValues, 'i');

// Add an array of rows
const rows = [
  [5, 'Bob', new Date()], // row by array
  { id: 6, name: 'Barbara', dob: new Date() },
];
// add new rows and return them as array of row objects
const newRows = worksheet.addRows(rows);

// Add an array of rows with inherited style
// These new rows will have same styles as last row
// and return them as array of row objects
const newRowsStyled = worksheet.addRows(rows, 'i');

const cell = worksheet.getCell('C3');

// 修改/添加单个单元格
cell.value = new Date(1968, 5, 1);

// 查询单元格的类型
expect(cell.type).toEqual(Excel.ValueType.Date);

// 使用单元格的字符串值
myInput.value = cell.text;

// 使用 html 安全的字符串进行渲染...
const html = '<div>' + cell.html + '</div>';

// 合并一系列单元格
worksheet.mergeCells('A4:B5');

// ...合并的单元格被链接起来了
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'));

// ...合并的单元格共享相同的样式对象
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);

// 取消单元格合并将打破链接的样式
worksheet.unMergeCells('A4');
expect(worksheet.getCell('B5').style).not.toBe(worksheet.getCell('A4').style);
expect(worksheet.getCell('B5').style.font).not.toBe(myFonts.arial);

// 按左上，右下合并
worksheet.mergeCells('K10', 'M12');

// 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
worksheet.mergeCells(10, 11, 12, 13);

insertRow(pos, value, (style = 'n'));
insertRows(pos, values, (style = 'n'));

// Insert a couple of Rows by key-value, shifting down rows every time
worksheet.insertRow(1, { id: 1, name: 'John Doe', dob: new Date(1970, 1, 1) });
worksheet.insertRow(1, { id: 2, name: 'Jane Doe', dob: new Date(1965, 1, 7) });

// Insert a row by contiguous Array (assign to columns A, B & C)
worksheet.insertRow(1, [3, 'Sam', new Date()]);

// Insert a row by sparse Array (assign to columns A, E & I)
var rowValues = [];
rowValues[1] = 4;
rowValues[5] = 'Kyle';
rowValues[9] = new Date();
// insert new row and return as row object
const insertedRow = worksheet.insertRow(1, rowValues);

// Insert a row, with inherited style
// This new row will have same style as row on top of it
// And return as row object
const insertedRowInherited = worksheet.insertRow(1, rowValues, 'i');

// Insert a row, keeping original style
// This new row will have same style as it was previously
// And return as row object
const insertedRowOriginal = worksheet.insertRow(1, rowValues, 'o');

// Insert an array of rows, in position 1, shifting down current position 1 and later rows by 2 rows
var rows = [
  [5, 'Bob', new Date()], // row by array
  { id: 6, name: 'Barbara', dob: new Date() },
];
// insert new rows and return them as array of row objects
const insertedRows = worksheet.insertRows(1, rows);

// Insert an array of rows, with inherited style
// These new rows will have same style as row on top of it
// And return them as array of row objects
const insertedRowsInherited = worksheet.insertRows(1, rows, 'i');

// Insert an array of rows, keeping original style
// These new rows will have same style as it was previously in 'pos' position
const insertedRowsOriginal = worksheet.insertRows(1, rows, 'o');

// 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.
const newRow3Values = [1, 2, 3, 4, 5];
const 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');

duplicateRow(start, (amount = 1), (insert = true));

const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet('duplicateTest');
ws.getCell('A1').value = 'One';
ws.getCell('A2').value = 'Two';
ws.getCell('A3').value = 'Three';
ws.getCell('A4').value = 'Four';

// 该行将重复复制第一行两次，但将替换第二行和第三行
// 如果第三个参数为 true，则它将插入2个新行，其中包含行 “One” 的值和样式
ws.duplicateRow(1, 2, false);

// 为单元格分配（或获取）名称（将覆盖该单元具有的其他任何名称）
worksheet.getCell('A1').name = 'PI';
expect(worksheet.getCell('A1').name).to.equal('PI');

// 为单元格分配（或获取）一组名称（单元可以具有多个名称）
worksheet.getCell('A1').names = ['thing1', 'thing2'];
expect(worksheet.getCell('A1').names).to.have.members(['thing1', 'thing2']);

// 从单元格中删除名称
worksheet.getCell('A1').removeName('thing1');
expect(worksheet.getCell('A1').names).to.have.members(['thing2']);

// 指定有效值的列表（One，Two，Three，Four）。
// Excel 将提供一个包含这些值的下拉列表。
worksheet.getCell('A1').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['"One,Two,Three,Four"'],
};

// 指定范围内的有效值列表。
// Excel 将提供一个包含这些值的下拉列表。
worksheet.getCell('A1').dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['$D$5:$F$5'],
};

// 指定单元格必须为非5的整数。
// 向用户显示适当的错误消息（如果他们弄错了）
worksheet.getCell('A1').dataValidation = {
  type: 'whole',
  operator: 'notEqual',
  showErrorMessage: true,
  formulae: [5],
  errorStyle: 'error',
  errorTitle: 'Five',
  error: 'The value must not be Five',
};

// 指定单元格必须为1.5到7之间的十进制数字。
// 添加“工具提示”以帮助指导用户
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',
};

// 指定单元格的文本长度必须小于15
worksheet.getCell('A1').dataValidation = {
  type: 'textLength',
  operator: 'lessThan',
  showErrorMessage: true,
  allowBlank: true,
  formulae: [15],
};

// 指定单元格必须是2016年1月1日之前的日期
worksheet.getCell('A1').dataValidation = {
  type: 'date',
  operator: 'lessThan',
  showErrorMessage: true,
  allowBlank: true,
  formulae: [new Date(2016, 0, 1)],
};

// 纯文字笔记
worksheet.getCell('A1').note = 'Hello, ExcelJS!';

// 彩色格式化的笔记
ws.getCell('B1').note = {
  texts: [
    {
      font: {
        size: 12,
        color: { theme: 0 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: 'This is ',
    },
    {
      font: {
        italic: true,
        size: 12,
        color: { theme: 0 },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'a',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' ',
    },
    {
      font: {
        size: 12,
        color: { argb: 'FFFF6600' },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'colorful',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' text ',
    },
    {
      font: {
        size: 12,
        color: { argb: 'FFCCFFCC' },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'with',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' in-cell ',
    },
    {
      font: {
        bold: true,
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: 'format',
    },
  ],
  margins: {
    insetmode: 'custom',
    inset: [0.25, 0.25, 0.35, 0.35],
  },
  protection: {
    locked: True,
    lockText: False,
  },
  editAs: 'twoCells',
};

ws.getCell('B1').note.margins = {
  insetmode: 'custom',
  inset: [0.25, 0.25, 0.35, 0.35],
};

ws.getCell('B1').note.protection = {
  locked: 'False',
  lockText: 'False',
};

ws.getCell('B1').note.editAs = 'twoCells';

// 将表格添加到工作表
ws.addTable({
  name: 'MyTable',
  ref: 'A1',
  headerRow: true,
  totalsRow: true,
  style: {
    theme: 'TableStyleDark3',
    showRowStripes: true,
  },
  columns: [
    { name: 'Date', totalsRowLabel: 'Totals:', filterButton: true },
    { name: 'Amount', totalsRowFunction: 'sum', filterButton: false },
  ],
  rows: [
    [new Date('2019-07-20'), 70.1],
    [new Date('2019-07-21'), 70.6],
    [new Date('2019-07-22'), 70.1],
  ],
});

const table = ws.getTable('MyTable');

// 打开标题行
table.headerRow = true;

// 关闭统计行
table.totalsRow = false;

// 将表更改提交到工作表中
table.commit();

const table = ws.getTable('MyTable');

// 表格左上移至 D4
table.ref = 'D4';

// 将表更改提交到工作表中
table.commit();

const table = ws.getTable('MyTable');

// 删除前两行
table.removeRows(0, 2);

// 在索引 5 处插入新行
table.addRow([new Date('2019-08-05'), 5, 'Mid'], 5);

// 在表格底部追加新行
table.addRow([new Date('2019-08-10'), 10, 'End']);

// 将表更改提交到工作表中
table.commit();

const table = ws.getTable('MyTable');

// 删除第二列
table.removeColumns(1, 1);

// 在索引 1 处插入新列（包含数据）
table.addColumn(
  {
    name: 'Letter',
    totalsRowFunction: 'custom',
    totalsRowFormula: 'ROW()',
    totalsRowResult: 6,
    filterButton: true,
  },
  ['a', 'b', 'c', 'd'],
  2
);

// 将表更改提交到工作表中
table.commit();

const table = ws.getTable('MyTable');

// 获取第二列的列包装器
const column = table.getColumn(1);

// 设置一些属性
column.name = 'Code';
column.filterButton = true;
column.style = { font: { bold: true, name: 'Comic Sans MS' } };
column.totalsRowLabel = 'Totals';
column.totalsRowFunction = 'custom';
column.totalsRowFormula = 'ROW()';
column.totalsRowResult = 10;

// 将表更改提交到工作表中
table.commit();

// 为单元格分配样式
ws.getCell('A1').numFmt = '0.00%';

// 将样式应用于工作表列
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' } },
];

// 将第3列设置为“货币格式”
ws.getColumn(3).numFmt = '"£"#,##0.00;[Red]-"£"#,##0.00';

// 将第2行设置为 Comic Sans。
ws.getRow(2).font = {
  name: 'Comic Sans MS',
  family: 4,
  size: 16,
  underline: 'double',
  bold: true,
};

// 将值显示为“ 1 3/5”
ws.getCell('A1').value = 1.6;
ws.getCell('A1').numFmt = '# ?/?';

// 显示为“ 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,
};

// 垂直对齐
ws.getCell('A3').font = {
  vertAlign: 'superscript',
};

// 注意：该单元格将存储对分配的字体对象的引用。
// 如果之后更改了字体对象，则单元字体也将更改。
const font = { name: 'Arial', size: 12 };
ws.getCell('A3').font = font;
font.size = 20; // 单元格 A3 现在具有20号字体！

// 从文件或流中读取工作簿后，共享相似字体的单元格可能引用相同的字体对象

// 将单元格对齐方式设置为左上，中间居中，右下
ws.getCell('A1').alignment = { vertical: 'top', horizontal: 'left' };
ws.getCell('B1').alignment = { vertical: 'middle', horizontal: 'center' };
ws.getCell('C1').alignment = { vertical: 'bottom', horizontal: 'right' };

// 将单元格设置为自动换行
ws.getCell('D1').alignment = { wrapText: true };

// 将单元格缩进设置为1
ws.getCell('E1').alignment = { indent: 1 };

// 将单元格文本旋转设置为向上30deg，向下45deg和垂直文本
ws.getCell('F1').alignment = { textRotation: 30 };
ws.getCell('G1').alignment = { textRotation: -45 };
ws.getCell('H1').alignment = { textRotation: 'vertical' };

// 在A1周围设置单个细边框
ws.getCell('A1').border = {
  top: { style: 'thin' },
  left: { style: 'thin' },
  bottom: { style: 'thin' },
  right: { style: 'thin' },
};

// 在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' } },
};

// 在A5中设置厚红十字边框
ws.getCell('A5').border = {
  diagonal: {
    up: true,
    down: true,
    style: 'thick',
    color: { argb: 'FFFF0000' },
  },
};

// 用红色深色垂直条纹填充A1
ws.getCell('A1').fill = {
  type: 'pattern',
  pattern: 'darkVertical',
  fgColor: { argb: 'FFFF0000' },
};

// 在A2中填充深黄色格子和蓝色背景
ws.getCell('A2').fill = {
  type: 'pattern',
  pattern: 'darkTrellis',
  fgColor: { argb: 'FFFFFF00' },
  bgColor: { argb: 'FF0000FF' },
};

// 从左到右用蓝白蓝渐变填充A3
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' } },
  ],
};

// 从中心开始用红绿色渐变填充A4
ws.getCell('A4').fill = {
  type: 'gradient',
  gradient: 'path',
  center: { left: 0.5, top: 0.5 },
  stops: [
    { position: 0, color: { argb: 'FFFF0000' } },
    { position: 1, color: { argb: 'FF00FF00' } },
  ],
};

ws.getCell('A1').value = {
  richText: [
    {
      font: {
        size: 12,
        color: { theme: 0 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: 'This is ',
    },
    {
      font: {
        italic: true,
        size: 12,
        color: { theme: 0 },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'a',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' ',
    },
    {
      font: {
        size: 12,
        color: { argb: 'FFFF6600' },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'colorful',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' text ',
    },
    {
      font: {
        size: 12,
        color: { argb: 'FFCCFFCC' },
        name: 'Calibri',
        scheme: 'minor',
      },
      text: 'with',
    },
    {
      font: {
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: ' in-cell ',
    },
    {
      font: {
        bold: true,
        size: 12,
        color: { theme: 1 },
        name: 'Calibri',
        family: 2,
        scheme: 'minor',
      },
      text: 'format',
    },
  ],
};

expect(ws.getCell('A1').text).to.equal(
  'This is a colorful text with in-cell format'
);
expect(ws.getCell('A1').type).to.equal(Excel.ValueType.RichText);

ws.getCell('A1').protection = {
  locked: false,
  hidden: true,
};

// 根据行和列为偶数或奇数向 A1:E7 添加一个棋盘图案
worksheet.addConditionalFormatting({
  ref: 'A1:E7',
  rules: [
    {
      type: 'expression',
      formulae: ['MOD(ROW()+COLUMN(),2)=0'],
      style: {
        fill: {
          type: 'pattern',
          pattern: 'solid',
          bgColor: { argb: 'FF00FF00' },
        },
      },
    },
  ],
});

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 },
];

worksheet.getColumn(3).outlineLevel = 1;
worksheet.getRow(3).outlineLevel = 1;

// 设置列大纲级别
worksheet.properties.outlineLevelCol = 1;

// 设置行大纲级别
worksheet.properties.outlineLevelRow = 1;

worksheet.properties.outlineLevelCol = 1;

worksheet.getColumn(3).outlineLevel = 1;
expect(worksheet.getColumn(3).collapsed).to.be.true;

worksheet.properties.outlineLevelCol = 2;
expect(worksheet.getColumn(3).collapsed).to.be.false;

worksheet.properties.outlineProperties = {
  summaryBelow: false,
  summaryRight: false,
};

// 通过文件名将图像添加到工作簿
const imageId1 = workbook.addImage({
  filename: 'path/to/image.jpg',
  extension: 'jpeg',
});

// 通过 buffer 将图像添加到工作簿
const imageId2 = workbook.addImage({
  buffer: fs.readFileSync('path/to.image.png'),
  extension: 'png',
});

// 通过 base64  将图像添加到工作簿
const myBase64Image = 'data:image/png;base64,iVBORw0KG...';
const imageId2 = workbook.addImage({
  base64: myBase64Image,
  extension: 'png',
});

// 设置背景
worksheet.addBackgroundImage(imageId1);

// 在 B2:D6 上插入图片
worksheet.addImage(imageId2, 'B2:D6');

// 在 B2:D6 的一部分上插入图像
worksheet.addImage(imageId2, {
  tl: { col: 1.5, row: 1.5 },
  br: { col: 3.5, row: 5.5 },
});

ws.addImage(imageId, {
  tl: { col: 0.1125, row: 0.4 },
  br: { col: 2.101046875, row: 3.4 },
  editAs: 'oneCell',
});

worksheet.addImage(imageId2, {
  tl: { col: 0, row: 0 },
  ext: { width: 500, height: 200 },
});

worksheet.addImage(imageId2, {
  tl: { col: 0, row: 0 },
  ext: { width: 500, height: 200 },
  hyperlinks: {
    hyperlink: 'http://www.somewhere.com',
    tooltip: 'http://www.somewhere.com',
  },
});

await worksheet.protect('the-password', options);

worksheet.unprotect();

// 从文件读取
const workbook = new Excel.Workbook();
await workbook.xlsx.readFile(filename);
// ... 使用 workbook

// 从流读取
const workbook = new Excel.Workbook();
await workbook.xlsx.read(stream);
// ... 使用 workbook

// 从 buffer 加载
const workbook = new Excel.Workbook();
await workbook.xlsx.load(data);
// ... 使用 workbook

// 写入文件
const workbook = createAndFillWorkbook();
await workbook.xlsx.writeFile(filename);

// 写入流
await workbook.xlsx.write(stream);

// 写入 buffer
const buffer = await workbook.xlsx.writeBuffer();

// 从文件读取
const workbook = new Excel.Workbook();
const worksheet = await workbook.csv.readFile(filename);
// ... 使用 workbook 或 worksheet

// 从流中读取
const workbook = new Excel.Workbook();
const worksheet = await workbook.csv.read(stream);
// ... 使用 workbook 或 worksheet

// 从带有欧洲日期的文件中读取
const workbook = new Excel.Workbook();
const options = {
  dateFormats: ['DD/MM/YYYY'],
};
const worksheet = await workbook.csv.readFile(filename, options);
// ... 使用 workbook 或 worksheet

// 从具有自定义值解析的文件中读取
const workbook = new Excel.Workbook();
const options = {
  map(value, index) {
    switch (index) {
      case 0:
        // 第1列是字符串
        return value;
      case 1:
        // 第2列是日期
        return new Date(value);
      case 2:
        // 第3列是公式值的JSON
        return JSON.parse(value);
      default:
        // 其余的是数字
        return parseFloat(value);
    }
  },
  // https://c2fo.io/fast-csv/docs/parsing/options
  parserOptions: {
    delimiter: '\t',
    quote: false,
  },
};
const worksheet = await workbook.csv.readFile(filename, options);
// ... 使用 workbook 或 worksheet

// 写入文件
const workbook = createAndFillWorkbook();
await workbook.csv.writeFile(filename);

// 写入流
// 请注意，您需要提供 sheetName 或 sheetId 以正确导入到 csv
await workbook.csv.write(stream, { sheetName: 'Page name' });

// 使用欧洲日期时间写入文件
const workbook = new Excel.Workbook();
const options = {
  dateFormat: 'DD/MM/YYYY HH:mm:ss',
  dateUTC: true, // 呈现日期时使用 utc
};
await workbook.csv.writeFile(filename, options);

// 使用自定义值格式写入文件
const workbook = new Excel.Workbook();
const options = {
  map(value, index) {
    switch (index) {
      case 0:
        // 第1列是字符串
        return value;
      case 1:
        // 第2列是日期
        return dayjs(value).format('YYYY-MM-DD');
      case 2:
        // 第3列是一个公式，只写结果
        return value.result;
      default:
        // 其余的是数字
        return value;
    }
  },
  // https://c2fo.io/fast-csv/docs/formatting/options
  formatterOptions: {
    delimiter: '\t',
    quote: false,
  },
};
await workbook.csv.writeFile(filename, options);

// 写入新 buffer
const buffer = await workbook.csv.writeBuffer();

// 使用样式和共享字符串构造流式 XLSX 工作簿编写器
const options = {
  filename: './streamed-workbook.xlsx',
  useStyles: true,
  useSharedStrings: true,
};
const 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.

// 完成添加数据。 提交工作表
worksheet.commit();

// 完成 workbook.
await workbook.commit();
// ... 流已被写入

worksheet.getCell('A1').value = null;

worksheet.getCell('A1').value = 5;
worksheet.getCell('A2').value = 3.14159;

worksheet.getCell('A1').value = 'Hello, World!';

worksheet.getCell('A1').value = new Date(2017, 2, 15);

// 链接到网络
worksheet.getCell('A1').value = {
  text: 'www.mylink.com',
  hyperlink: 'http://www.mylink.com',
  tooltip: 'www.mylink.com',
};

// 内部链接
worksheet.getCell('A1').value = { text: 'Sheet2', hyperlink: "#'Sheet2'!A1" };

worksheet.getCell('A3').value = { formula: 'A1+A2', result: 7 };

worksheet.getCell('A3').formula === 'A1+A2';
worksheet.getCell('A3').result === 7;

worksheet.getCell('A2').value = {
  formula: 'A1',
  result: 10,
  shareType: 'shared',
  ref: 'A2:B3',
};

worksheet.getCell('B2').value = { sharedFormula: 'A2', result: 10 };

expect(worksheet.getCell('B2').formula).to.equal('B1');

// 将 A1 设置为起始编号
worksheet.getCell('A1').value = 1;

// 从 A1 开始以递增计数将 A2 填充到 A10
worksheet.fillFormula('A2:A10', 'A1+1', [2, 3, 4, 5, 6, 7, 8, 9, 10]);

// 从A1开始以递增计数将 A2 填充到 A100
worksheet.fillFormula('A2:A100', 'A1+1', (row, col) => row);

worksheet.getCell('A3').formulaType === Enums.FormulaType.Master;
worksheet.getCell('B3').formulaType === Enums.FormulaType.Shared;

// 将数组公式分配给 A2:B3
worksheet.getCell('A2').value = {
  formula: 'A1',
  result: 10,
  shareType: 'array',
  ref: 'A2:B3',
};

// 可能没有必要填写工作表中的其余值

// 用数组公式 "A1" 填充 A2:B3
worksheet.fillFormula('A2:B3', 'A1', [1, 1, 1, 1], 'array');

worksheet.getCell('A1').value = {
  richText: [{ text: 'This is ' }, { font: { italic: true }, text: 'italic' }],
};

worksheet.getCell('A1').value = true;
worksheet.getCell('A2').value = false;

worksheet.getCell('A1').value = { error: '#N/A' };
worksheet.getCell('A2').value = { error: '#VALUE!' };

ExcelJS.config.setValue('promise', require('bluebird'));