// 列ヘッダのスタイルを定義し、addView()メソッドを使用してビューを追加するときに割り当てます。
var headerStyle = {
    font: "italic bold 13pt Calibri",
    borderTop: {
        color: "red",
        style: "thick"
    },
    borderLeft: {
        color: "red",
        style: "thin"
    },
    borderRight: {
        color: "red",
        style: "thin"
    },
    borderBottom: {
        color: "red",
        style: "thin"
    }
};


// ビューをテーブルシートに連結します。 
customerTable.fetch().then(function () {
    var view = customerTable.addView("myView", [
        { value: "customerKey", width: 130, headerStyle: headerStyle, caption: "Customer Key" },// headerStyleプロパティを使用して列ヘッダのスタイルを設定します。
        { value: "customer", width: 200, headerStyle: headerStyle, caption: "Customer" }, 
        { value: "billToCustomer", width: 200, headerStyle: headerStyle, caption: "Bill To Customer" },
        { value: "category", width: 120, headerStyle: headerStyle, caption: "Category" },
        { value: "buyingGroup", width: 180, headerStyle: headerStyle, caption: "Buying Group" },
        { value: "primaryContact", width: 170, headerStyle: headerStyle, caption: "Primary Contact" },
        { value: "postalCode", width: 120, headerStyle: headerStyle, caption: "Postal Code" },
        { value: "validFrom", width: 150, headerStyle: headerStyle, caption: "Valid From" },
        { value: "validTo", width: 180, headerStyle: headerStyle, caption: "Valid To" }

    ]);
    // ビューには、テーブルのすべてのデフォルト列があります。 
    sheet.setDataView(view);
});

// ビューをテーブルシートに連結します。 
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [
        { value: "saleKey", caption: "Sale Key", headerFit: "stack", headerStyle: headerStyle },// set headerFit to stack
        { value: "cityKey", caption: "City Key", headerFit: "stack", headerStyle: headerStyle },
        { value: "stockItemKey", width: 50, caption: "Stock Item Key", headerFit: "stack", headerStyle: headerStyle },
        { value: "invoiceDateKey", width: 120, caption: "Invoice Date Key", headerFit: "vertical", headerStyle: headerStyle },
        { value: "deliveryDateKey", width: 120, caption: "Delivery Date Key", headerFit: "vertical", headerStyle: headerStyle },
        { value: "salesPerson", width: 150, caption: "Sales Person", headerFit: "vertical", headerStyle: headerStyle },
        { value: "address", caption: "Address", width: 300, headerFit: "normal", headerStyle: headerStyle }

    ]);
    sheet.setDataView(view);
    sheet.options.defaultStackRowHeight = 30;
    sheet.setDefaultRowHeight(220, 1);
});

// ラジオボタンリストのコード。
var radioButtonListStyle = new GC.Spread.Sheets.Style();
var cellTypeRadioButtonList = new GC.Spread.Sheets.CellTypes.RadioButtonList();
cellTypeRadioButtonList.items([
    { text: "Tailspin Toys (Head Office)", value: "Tailspin Toys (Head Office)" },
    { text: "Wingtip Toys (Head Office)", value: "Wingtip Toys (Head Office)" },
]);
radioButtonListStyle.cellType = cellTypeRadioButtonList;

// ボタンリストのコード。
var buttonListStyle = new GC.Spread.Sheets.Style();
var buttonListCellType = new GC.Spread.Sheets.CellTypes.ButtonList();
buttonListCellType.items([
    { text: "Tailspin Toys", value: "Tailspin Toys" },
    { text: "Wingtip Toys", value: "Wingtip Toys" },
]);
buttonListStyle.cellType = buttonListCellType;

// 日付ドロップダウンのコード。
var dateStyle = {};
dateStyle.cellButtons = [
    {
        imageType: "dropdown",
        command: "openDateTimePicker",
        useButtonStyle: true,
    }
];
dateStyle.dropDowns = [
    {
        type: "dateTimePicker",
        option: {
            showTime: true
        }
    }
];

// ビューをテーブルシートに連結します。 
customerTable.fetch().then(function () {
    var view = customerTable.addView("myView", [
        { value: "customerKey", width: 120, caption: "Customer Key" },
        { value: "billToCustomer", width: 400, caption: "Bill To Customer", style: radioButtonListStyle },
        { value: "buyingGroup", width: 250, caption: "Buying Group", style: buttonListStyle },
        { value: "primaryContact", width: 170, caption: "Primary Contact" },
        { value: "validFrom", width: 190, caption: "Valid From", style: dateStyle }
    ]);
    // ビューには、テーブルのすべてのデフォルト列があります。 
    sheet.setDataView(view);
});

// ビューをテーブルシートに連結します。
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [
        { value: "customerKey", width: 130, caption: ["Customer Information", "Customer Key"] }, // 文字列配列をキャプションに設定します。
        { value: "customer", width: 200, caption: ["Customer Information", "Customer"] },
        { value: "billToCustomer", width: 200, caption: ["Customer Information", "Bill To Customer"] },
        { value: "primaryContact", width: 170, caption: ["Customer Information", "Primary Contact"] },
        { value: "validFrom", width: 150, caption: ["Validity", "Valid From"], style: { formatter: "MM/dd/yyyy"} },
        { value: "validTo", width: 180, caption: ["Validity", "Valid To"], style: { formatter: "MM/dd/yyyy"} }
    ]);
    sheet.setDataView(view);
});

// ビューをテーブルシートに連結します。
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [

        { value: "stockItem", width: 300, caption: "Stock Item", headerStyle: headerStyle },
        { value: "unitPrice", width: 120, caption: "Unit Price", headerStyle: headerStyle },
        { value: "taxRate", width: 120, caption: "Tax Rate", headerStyle: headerStyle },
        { value: "recommendedRetailPrice", width: 190, caption: "Recommended Retail Price", style: currencyFormatter, headerStyle: headerStyle, conditionalFormats: [dataBarRule1] },
        { value: "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]", caption: "Actual Retail Price", style: currencyFormatter, width: 190, headerStyle: headerStyle, conditionalFormats: [dataBarRule2] }, // 計算した列。

    ]);

    // フリーヘッダー領域のテンプレートシートを作成します。
    var templateSheet = new GC.Spread.Sheets.Worksheet();
    templateSheet.options.keepUnknownFormulas = true;
    var currencyFormatterStyle = new GC.Spread.Sheets.Style();
    currencyFormatterStyle.formatter = formatter;
    currencyFormatterStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
    templateSheet.setRowCount(6);
    var freeHeaderAreaStyle = new GC.Spread.Sheets.Style();
    freeHeaderAreaStyle.backColor = Colors.white;

    templateSheet.setValue(0, 0, "Retail Price Analysis");
    templateSheet.getCell(0, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 30px \"Calibri\"");
    templateSheet.addSpan(0, 0, 1, 5);
    var titleStyle = new GC.Spread.Sheets.Style();
    titleStyle.backColor = Colors.lightGreen;
    titleStyle.foreColor = Colors.black;
    templateSheet.setStyle(0, -1, titleStyle);
    templateSheet.setRowHeight(0, 80);

    templateSheet.getCell(1, 0)
        .value("Whether a Stock Item's Actual Retail Price is either under or over Recommended Retail Price is automatically calculated.")
        .font("italic 12px \"Calibri\"")
        .foreColor("rgb(120,120,120)")
        .textIndent(2);
    templateSheet.setStyle(1, -1, titleStyle);
    templateSheet.addSpan(1, 0, 1, 5);

    var headerDescriptionStyle = new GC.Spread.Sheets.Style();
    headerDescriptionStyle.backColor = Colors.lightGreen;
    headerDescriptionStyle.borderBottom = new GC.Spread.Sheets.LineBorder(Colors.middleGreen, GC.Spread.Sheets.LineStyle.thick);
    templateSheet.setStyle(2, -1, headerDescriptionStyle);
    templateSheet.addSpan(2, 0, 1, 5);
    templateSheet.setRowHeight(2, 16);

    templateSheet.setValue(3, 0, "Total Recommended Retail Price");
    templateSheet.getCell(3, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").foreColor(Colors.darkGreen);
    templateSheet.addSpan(3, 0, 1, 2);
    templateSheet.setFormula(3, 2, '=SUM(TableSheet1[Recommended Retail Price])');
    templateSheet.getCell(3, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").foreColor(Colors.darkGreen).formatter(formatter);
    templateSheet.setFormula(3, 3, '=HBARSPARKLINE(ROUND(C4/MAX(C4,C5),2),"' + Colors.darkGreen + '",false)');
    templateSheet.addSpan(3, 3, 1, 2);
    templateSheet.setStyle(3, -1, freeHeaderAreaStyle);

    templateSheet.setValue(4, 0, "Total Actual Retail Price");
    templateSheet.getCell(4, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").foreColor(Colors.brown);
    templateSheet.addSpan(4, 0, 1, 2);
    templateSheet.setFormula(4, 2, '=SUM(TableSheet1[Actual Retail Price])');
    templateSheet.getCell(4, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").foreColor(Colors.brown).formatter(formatter);
    var contentBorderStyle = new GC.Spread.Sheets.Style();
    contentBorderStyle.backColor = Colors.white;
    contentBorderStyle.borderBottom = new GC.Spread.Sheets.LineBorder(Colors.brown, GC.Spread.Sheets.LineStyle.thick);
    templateSheet.setStyle(4, -1, contentBorderStyle);
    templateSheet.setFormula(4, 3, '=HBARSPARKLINE(ROUND(C5/MAX(C4,C5),2),"' + Colors.brown + '",false)');
    templateSheet.addSpan(4, 3, 1, 2);

    templateSheet.setFormula(5, 0, '=IF(C4>C5,"Budget is under Total Recommended Retail Price by","Budget is over Total Recommended Retail Price")');
    templateSheet.getCell(5, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").foreColor(Colors.black);
    templateSheet.addSpan(5, 0, 1, 2);
    templateSheet.setFormula(5, 2, '=C4-C5');
    templateSheet.getCell(5, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left).font("bold 14px \"Calibri\"").formatter(formatter).foreColor(Colors.black);
    templateSheet.setStyle(5, -1, freeHeaderAreaStyle);
    templateSheet.addSpan(5, 2, 1, 3);

    let template = templateSheet.toJSON();
    sheet.applyFreeHeaderArea(template);
    sheet.setDataView(view);
});