ExcelJSでエクセルファイルを作成

ExcelJSで数式を入れたり塗りつぶしたり
自分用備忘録

var Excel = require('exceljs');
var workbook = new Excel.Workbook();
var sheet1 = workbook.addWorksheet("test");
var worksheet = workbook.getWorksheet("test");
var path = require('path');
path = path.join(__dirname)

var targetExcelPath = path + '/test.xlsx';
console.log(targetExcelPath)

//文字を入れる（行・列指定）
worksheet.getCell(1, 1).value = "商品";
worksheet.getCell(2, 1).value = "りんご";
worksheet.getCell(3,1 ).value = "みかん";
worksheet.getCell(4, 1).value = "ぶどう";

//文字を入れる２（セル指定）
worksheet.getCell('B1').value = "Item";
worksheet.getCell('C1').value = "Plice";

//配列から文字を入力
var Item = [["apple", 150], ["orange", 200], ["grape", 280]];

for (var　row = 0; row < Item.length; row++) {
    worksheet.getCell(row+2, 2).value = Item[row][0];
    worksheet.getCell(row+2, 3).value = Item[row][1];
}

//セルをマージ
worksheet.mergeCells('A5:C5');

//関数を入れる
worksheet.getCell('A5').value =  { formula: "SUM(C2:C4)" };

//日付を入れる
worksheet.getCell('D1').value = 42736;

//セルの書式を短い日付に変える
worksheet.getCell('D1').numFmt = 'm/d';

//塗りつぶし(solid　透明度ｾﾞﾛ)
var fillStyles = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFfabf8f' }
};

worksheet.getCell('A1').fill = fillStyles;
worksheet.getCell('B1').fill = fillStyles;
worksheet.getCell('C1').fill = fillStyles;
worksheet.getCell('D1').fill = fillStyles;

//罫線
var borderStyles = {
    top: { style: "thin" },
    left: { style: "thin" },
    bottom: { style: "thin" },
    right: { style: "thin" }
};

worksheet.getCell('A1').border = borderStyles;
worksheet.getCell('B1').border = borderStyles;
worksheet.getCell('C1').border = borderStyles;
worksheet.getCell('D1').border = borderStyles;

//幅
worksheet.getColumn(1).width = 30;

//折り返し
worksheet.getCell('D2').value ="あいうえおかきくけこさしすせそたちつてと";
worksheet.getCell('D2').alignment = { wrapText: true };

//テキスト位置
worksheet.getCell(1, 1).alignment = { vertical: 'middle', horizontal: 'center' };

//出力
workbook.xlsx.writeFile(targetExcelPath).then(function () {
    var a = document.createElement('a');
    a.href = targetExcelPath;
    a.setAttribute('download', name || 'test.xlsx');
    a.dispatchEvent(new CustomEvent('click'));
});