meidu2021/daju/WebRoot/webpage/cn/com/lzt/budget/tempsheet/budgetTempSheet-leader.js

// var sheet = spread.getActiveSheet();var t = sheet.getTag(4,2);console.log(t);

var headerCount = 4;//头部行高
var nextDataRowIndex;//下条数据所在行
var iceColor = '#aaaaaa';
var bgColor1 = 'rgb(210,218,228)';
var bgColor2 = 'rgb(255,242,203)';
var bgColor3 = 'rgb(194,214,155)';//绿的
var title_bar = [
    {name: '序号', width: 80, backColor: '#b8cce4'},
    {name: '项目名称', width: 160, backColor: '#b8cce4'},
    {name: '经营类年度预算', width: 160, backColor: '#b8cce4'},
    {name: '经营类年度执行数', width: 160, backColor: '#b8cce4'},
    {name: '经营类年度执行比例', width: 170, backColor: '#b8cce4'}
]
var month_title_bar = [
    {name: '月执行总数', width: 150, backColor: '#a9d08e'},
    {name: '月执行总数占比', width: 150, backColor: '#a9d08e'},
    {name: '月人工费用执行数', width: 150, backColor: '#bdd7ee'},
    {name: '月人工费用执行占比', width: 150, backColor: '#bdd7ee'},
    {name: '月采购费用执行数', width: 150, backColor: '#ffc9c9'},
    {name: '月采购费用执行占比', width: 150, backColor: '#ffc9c9'},
    {name: '月维保费用执行数', width: 150, backColor: '#fff2cb'},
    {name: '月维保费用执行占比', width: 150, backColor: '#fff2cb'},
    {name: '月其他费执行用数', width: 150, backColor: '#d6cef2'},
    {name: '月其他费用执行占比', width: 150, backColor: '#d6cef2'},
    {name: '月管理费用执行数', width: 150, backColor: '#f8cbad'},
    {name: '月管理费用执行占比', width: 150, backColor: '#f8cbad'},
];
var month_str_arr = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '十一', '十二'];

$(function () {
    spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 1});
    initSpread();
});

function initSpread() {
    spread.suspendPaint();
    if (dbJson) {//数据库
        // spread.fromJSON(JSON.parse(dbJson));
        spread.fromJSON(dbJson);
    } else {
        nextDataRowIndex = headerCount;
        initData();
    }

    var sheet = spread.getActiveSheet();
    var setting = sheet.tag();
    if (setting) {
        if (setting.nextDataRowIndex) {
            nextDataRowIndex = setting.nextDataRowIndex;
        }
    }
    if (!nextDataRowIndex) {
        nextDataRowIndex = headerCount;
    }

    spread.options.newTabVisible = false;//取消新增sheet按钮
    spread.options.allowExtendPasteRange = true;//扩展粘贴区域
    bindEvent(spread);
    addContextMenu(spread);
    addDataCellMenu(spread)
    setMenuView(spread);
    setDefaultLock(sheet);
    // sheet.selectionPolicy(GC.Spread.Sheets.SelectionPolicy.single);//不让多选
    spread.bind(GC.Spread.Sheets.Events.RowChanging, function (e, info) {
        //info.propertyName="deleteRows" //删除行
        if (info.propertyName = "deleteRows") {
            var deleteRow = info.row;
            var deleteCount = info.count;
            var sheetTag = sheet.tag();
            var ids = sheetTag.ids;
            for (var i = deleteRow; i < deleteRow + deleteCount; i++) {
                var rowTag = sheet.getTag(i, -1);
                if (!rowTag) continue;
                for (var j = 0; j < ids.length; j++) {
                    if (ids[j] == rowTag.id) {
                        if (nextDataRowIndex > headerCount) nextDataRowIndex--;
                        // delete  ids[j];
                        ids.splice(j, 1);
                    }
                }
            }
            addSheetTagValue(sheet, {ids: ids, nextDataRowIndex: nextDataRowIndex});
        }
    });
    //设置冻结线
    sheet.frozenRowCount(4);
    sheet.frozenColumnCount(3);
    sheet.options.frozenlineColor = iceColor;
    spread.resumePaint();
}


function setOneCell(span1, span2, bgColor, fontSize, fontWeight, value, cellWidth, cellHeight) {
    var sheet = spread.getActiveSheet();
    sheet.getCell(span1, span2).text(value);
    sheet.getCell(span1, span2).backColor(bgColor)
    sheet.getCell(span1, span2).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.thin), {all: true});
    sheet.getCell(span1, span2).vAlign(spreadNS.VerticalAlign.center).hAlign(spreadNS.HorizontalAlign.center);
    sheet.getCell(span1, span2).font(fontWeight + ' normal ' + fontSize + 'px normal');
    if (cellWidth)
        sheet.setColumnWidth(span2, cellWidth);
    if (cellHeight)
        sheet.setRowHeight(span1, cellHeight);
}

function setManyCell(span1, span2, span3, span4, bgColor, fontSize, fontWeight, value, cellWidth, cellHeight) {
    var sheet = spread.getActiveSheet();
    sheet.addSpan(span1, span2, span3, span4);
    sheet.getRange(span1, span2, span3, span4).backColor(bgColor)
    sheet.getRange(span1, span2, span3, span4).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.thin), {all: true});
    sheet.getRange(span1, span2, span3, span4).vAlign(spreadNS.VerticalAlign.center).hAlign(spreadNS.HorizontalAlign.center);
    sheet.getRange(span1, span2, span3, span4).font(fontWeight + ' normal ' + fontSize + 'px normal');
    sheet.setValue(span1, span2, value);
    sheet.setColumnWidth(span2, cellWidth);
    sheet.setRowHeight(span1, cellHeight);
}

function initData() {


    var jsonHeader;
    /*if(actualStaKind!='main'){
        jsonHeader = actual_sta_branch;
    }else {
        jsonHeader =actual_sta_main;
    }
    spread.fromJSON(jsonHeader);*/
    var sheet = spread.getActiveSheet();
    /*if(actualStaKind=='main'){
        initMainHeadData(sheet);
    }else {
        initBranchHeadData(sheet);
    }*/
    sheet.setColumnCount(149);

    //设置左侧列
    for (let i = 0; i < title_bar.length; i++) {
        setManyCell(1, i, 2, 1, title_bar[i].backColor, 18, 'bold', title_bar[i].name, title_bar[i].width, 20);
        sheet.setRowHeight(2, 40);
    }
    setManyCell(3, 0, 1, 2, '#ffffff', 20, '', '合计', 80, 45);
    //设置右侧标头
    for (let i = 1; i <= 12; i++) {
        setManyCell(1, 5 + 12 * (i - 1), 1, 12, '#ffffff', 14, 'bold', i + '月执行数', 80, 20);
        setManyCell(1, 5 + 12 * (i - 1), 1, 12, '#ffffff', 14, '', month_str_arr[i - 1] + '月', 80, 20);
        for (let j = 0; j < month_title_bar.length; j++) {
            setOneCell(2, 5 + 12 * (i - 1) + j, month_title_bar[j].backColor, 14, 'bold', i + month_title_bar[j].name, month_title_bar[j].width, 40);
        }
    }
    //设置每一行
    for (var i = 0; i < entityList.length; i++) {
        var oneEntity = entityList[i];
        addOne(oneEntity);
    }
    //统计
    summarySill();
    sheet.autoFitColumn(1);

}

//统计
function summarySill() {
    var sheet = spread.getActiveSheet();
    var m;
    var mCell;
    for (let i = 0; i < 12 * 12 + 3; i++) {
        var frontCell = getCellStr(sheet, 4, 2 + i);
        var colCellStr = frontCell.toString().split("$")[1];

        if (i == 0 || i % 2 == 1) {
            sheet.getCell(3, 2 + i).formula("=SUM(" + frontCell + ":" + colCellStr + nextDataRowIndex + ")").formatter("0.00");
        }
        if (i == 2) {
            //设置方法变量，以后轮询都需要除以m作为分母
            m = sheet.getCell(3, i).value();
            mCell = getCellStr(sheet, 3, i);
            sCell = getCellStr(sheet, 3, 1 + i);
            let sm = sCell + "/" + mCell;
            let formula = "=IF(ISERROR(" + sm + "),0," + sm + ")";
            sheet.getCell(3, 2 + i).formula(formula).formatter("0.00%");
        }
        if (i > 2 && i % 2 == 0) {
            let sCell = getCellStr(sheet, 3, 1 + i);
            let sm = sCell + "/" + mCell;
            let formula = "=IF(ISERROR(" + sm + "),0," + sm + ")";
            sheet.getCell(3, 2 + i).formula(formula).formatter("0.00%");
        }
    }
}


function addOne(one) {
    var sheet = spread.getActiveSheet();
    var sheetTag = sheet.tag();
    if (!sheetTag) sheetTag = {};
    if (!sheetTag.ids) sheetTag.ids = [];
    var id = one.id;
    if ($.inArray(id, sheetTag.ids) != -1) {
        return;
    }
    sheetTag.ids.push(id);
    addSheetTagValue(sheet, {ids: sheetTag.ids});
    var row = nextDataRowIndex;
    sheet.addRows(row + 1, 1);
    var rowSetting = {};//行变量
    var col = 0;
    var bindEntityCol = 0;

    if (actualStaKind != 'main') {
        // 序号
        sheet.getCell(row, col).formula("row()-" + headerCount).backColor(bgColor1);
        col++;
        //名称
        sheet.getCell(row, col).backColor(bgColor1).value(one.name).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        bindEntityCol = col;
        col++;
        //经营类年度预算
        var yearAmountCellStr = "0";
        var yearAmountCellStrCell = getCellStr(sheet, row, col);
        sheet.getCell(row, col).value(yearAmountCellStr).backColor(bgColor1);
        col++;
        //经营类年度执行数
        // var yearExecAmountCellStrCell = "=SUM(" + getCellStr(sheet, row, col +2) + "," + getCellStr(sheet, row, col +14) + "," + getCellStr(sheet, row, col +26) + "," + getCellStr(sheet, row, col +38) + "," + getCellStr(sheet, row, col +50) + "," + getCellStr(sheet, row, col +62) + "," + getCellStr(sheet, row, col +74) + "," + getCellStr(sheet, row, col +86) + "," + getCellStr(sheet, row, col +98) + "," + getCellStr(sheet, row, col +110) + "," + getCellStr(sheet, row, col +122) + "," + getCellStr(sheet, row, col +134) + ")";
        var yearExecAmountCellStrCell = "=SUM(";
        for (let i = 0; i < 12; i++) {
            yearExecAmountCellStrCell += getCellStr(sheet, row, col + 2 + i * 12);
            if (i == 11) {
                yearExecAmountCellStrCell += ")";
            } else {
                yearExecAmountCellStrCell += ",";
            }
        }


        sheet.getCell(row, col).formula(yearExecAmountCellStrCell).backColor(bgColor1).formatter("0.00");
        col++;
        //经营类年度比例
        var divFoumula = yearExecAmountCellStrCell + "/" + yearAmountCellStrCell;
        var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
        sheet.getCell(row, col).formula(formula).backColor(bgColor1).formatter("0.00%");
        col++;

        for (let j = 0; j < 12; j++) {
            for (var i = 0; i < 6; i++) {
                if (i == 0) {
                    col += 2;
                } else if (i == 1) {
                    //执行人工费用总数
                    var personAmountCellStr = "0";
                    var personAmountCellStrCell = getCellStr(sheet, row, col);
                    sheet.getCell(row, col).value(personAmountCellStr).backColor(month_title_bar[i * 2].backColor);
                    col++;
                    //执行人工费用总数占比
                    divFoumula = personAmountCellStrCell + "/" + yearAmountCellStrCell;
                    formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col).formula(formula).backColor(month_title_bar[i * 2].backColor).formatter("0.00%");
                    col++;
                } else if (i == 2) {
                    //执行采购费用总数
                    var buyAmountCellStr = "0";
                    var buyAmountCellStrCell = getCellStr(sheet, row, col);
                    ;
                    sheet.getCell(row, col).value(buyAmountCellStr).backColor(month_title_bar[i * 2].backColor);
                    col++;
                    //执行采购费用总数占比
                    let divFoumula = buyAmountCellStrCell + "/" + yearAmountCellStrCell;
                    let formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col).formula(formula).backColor(month_title_bar[i * 2].backColor).formatter("0.00%");
                    col++;
                } else if (i == 3) {
                    //执行维保费用总数
                    var repairAmountCellStr = "0";
                    var repairAmountCellStrCell = getCellStr(sheet, row, col);
                    ;
                    sheet.getCell(row, col).value(repairAmountCellStr).backColor(month_title_bar[i * 2].backColor);
                    col++;
                    //执行维保费用总数占比
                    divFoumula = repairAmountCellStrCell + "/" + yearAmountCellStrCell;
                    formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col).formula(formula).backColor(month_title_bar[i * 2].backColor).formatter("0.00%");
                    col++;
                } else if (i == 4) {
                    //执行其他费用总数
                    var otherAmountCellStr = "0";
                    var otherAmountCellStrCell = getCellStr(sheet, row, col);
                    sheet.getCell(row, col).value(otherAmountCellStr).backColor(month_title_bar[i * 2].backColor);
                    col++;
                    //执行其他费用总数占比
                    divFoumula = otherAmountCellStrCell + "/" + yearAmountCellStrCell;
                    formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col).formula(formula).backColor(month_title_bar[i * 2].backColor).formatter("0.00%");
                    col++;
                } else if (i == 5) {
                    //执行管理费用总数占比
                    var manageAmountCellStr = "0";
                    var manageAmountCellStrCell = getCellStr(sheet, row, col);
                    sheet.getCell(row, col).value(manageAmountCellStr).backColor(month_title_bar[i * 2].backColor);
                    col++;
                    //执行人工费用总数占比
                    divFoumula = manageAmountCellStrCell + "/" + yearAmountCellStrCell;
                    formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col).formula(formula).backColor(month_title_bar[i * 2].backColor).formatter("0.00%");
                    col++;

                    //重新设置1月执行总数
                    var monthAmountCellStrCell = "=SUM(" + getCellStr(sheet, row, col - 2) + "," + getCellStr(sheet, row, col - 4) + "," + getCellStr(sheet, row, col - 6) + "," + getCellStr(sheet, row, col - 8) + "," + getCellStr(sheet, row, col - 10) + ")";
                    sheet.getCell(row, col - 12).formula(monthAmountCellStrCell).backColor(month_title_bar[(i - 5) * 2].backColor).formatter("0.00");
                    //重新设置1月执行百分比
                    divFoumula = monthAmountCellStrCell + "/" + yearAmountCellStrCell;
                    formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
                    sheet.getCell(row, col - 11).formula(formula).backColor(month_title_bar[(i - 5) * 2].backColor).formatter("0.00%");

                }
            }
        }
        // console.log(amountCellArray)
        // for (let i = 0; i < amountIndex.length; i++) {
        //     sheet.getCell(row, amountIndex[i]).formula(amountCellArray.join('+')).backColor(bgColor2);
        // }

        /*sheet.getCell(row, amountIndex).formula(amountCellArray.join('+')).backColor(bgColor2);
        var entityYearAmountCellStr = getCellStr(sheet, row, entityYearAmountIndex);
        var divFoumula = entityYearAmountCellStr + "/" + yearAmountCellStr;
        var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
        sheet.getCell(row, entityYearAmountPerIndex).formula(formula).backColor(bgColor2).formatter("0.00%");*/
    } else {
        /*console.log('other')
        // 序号
        sheet.getCell(row, col).formula("row()-" + headerCount).backColor(bgColor1);
        col++;
        // sheet.setValue(row,colIndex,one.name);
        //项目名称
        sheet.getCell(row, col).backColor(bgColor1).value(one.name).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        bindEntityCol = col;
        col++;
        //度预算数
        sheet.getCell(row, col).backColor(bgColor1);
        var yearAmountCellStr = getCellStr(sheet, row, col);
        col++;
        var entityYearAmountIndex = col++;
        var entityYearAmountPerIndex = col++;
        var yearAmountCellArray = [];
        for (var i = 0; i < 5; i++) {
            //发生额
            var monthAmountCellStr = getCellStr(sheet, row, col);
            yearAmountCellArray.push(monthAmountCellStr);
            sheet.getCell(row, col).backColor(bgColor2);
            col++;
            //占比
            var divFoumula = monthAmountCellStr + "/" + yearAmountCellStr;
            var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
            sheet.getCell(row, col).formula(formula).backColor(bgColor2).formatter("0.00%");
            col++;
        }
        sheet.getCell(row, entityYearAmountIndex).formula(yearAmountCellArray.join('+')).backColor(bgColor3);
        var entityYearAmountCellStr = getCellStr(sheet, row, entityYearAmountIndex);
        var divFoumula = entityYearAmountCellStr + "/" + yearAmountCellStr;
        var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
        sheet.getCell(row, entityYearAmountPerIndex).formula(formula).backColor(bgColor3).formatter("0.00%");*/
    }

    //
    setBorder(sheet, row);
    nextDataRowIndex++;
    addSheetTagValue(sheet, {nextDataRowIndex: nextDataRowIndex});
    rowSetting.id = id;
    rowSetting.entityId = id;
    addCellTagValue(sheet, row, -1, rowSetting);
    sheet.setSelection(row, bindEntityCol, 1, 1);
    setEntityValue(sheet, 'row', {entityId: one.id, entityName: one.name, expect: 0, includeTax: 1});
    sheet.clearSelection();
}

function initBranchHeadData(sheet) {
    //边框
    var row = 1;
    setBorder(sheet, row);

    var col = 2;
    var cellStr = getCellStr(sheet, row, col);
    var formula = sheet.getFormula(row, col);
    sheet.getCell(row, col).formula("round(" + formula + ",2)");
    var yearBudgetCell = "C2";
    for (var i = 0; i < 13; i++) {
        col++;
        var cellStr = getCellStr(sheet, row, col);
        formula = sheet.getFormula(row, col);
        sheet.getCell(row, col).formula("round(" + formula + ",2)");
        col++;
        var divFoumula = cellStr + "/" + yearBudgetCell;
        var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
        sheet.getCell(row, col).formula(formula).formatter("0.00%");
    }
}

function initMainHeadData(sheet) {
    var row = 1;
    setBorder(sheet, row);

    var col = 2;
    var cellStr = getCellStr(sheet, row, col);
    var formula = sheet.getFormula(row, col);
    sheet.getCell(row, col).formula("round(" + formula + ",2)");
    var yearBudgetCell = "C2";
    for (var i = 0; i < 6; i++) {
        col++;
        var cellStr = getCellStr(sheet, row, col);
        formula = sheet.getFormula(row, col);
        sheet.getCell(row, col).formula("round(" + formula + ",2)");
        col++;
        var divFoumula = cellStr + "/" + yearBudgetCell;
        var formula = "=IF(ISERROR(" + divFoumula + "),0," + divFoumula + ")";
        sheet.getCell(row, col).formula(formula).formatter("0.00%");
    }
}

function setBorder(sheet, row) {
    var lineStyle = GC.Spread.Sheets.LineStyle.thin;
    var lineBorder = new GC.Spread.Sheets.LineBorder('black', lineStyle);
    var sheetArea = GC.Spread.Sheets.SheetArea.viewport;
    var columnCount = sheet.getColumnCount();
    for (var col = 0; col < columnCount; col++) {
        sheet.getRange(row, col).setBorder(lineBorder, {left: true, right: true, top: true, bottom: true}, sheetArea);
    }
}

function bindEvent(spread) {
    bindCheckNum(spread);
    spread.bind(GC.Spread.Sheets.Events.EnterCell, function (event, data) {//点击cell事件
        var row = data.row;
        var col = data.col;
        var sheet = data.sheet;
        var cellTag = sheet.getTag(row, col);
        disableBindButton(true);
        if (readOnly == 'false') {
            $('#rowInfo').show();
            $('#colInfo').show();
            $('#info3').show();
            $('#info2').hide();
            if (cellTag) {
                var bindDirection = cellTag.bindDirection;
                if (bindDirection) {//表头
                    if (bindDirection == 'row') {
                        $('#rowTimeInput').val(cellTag.periodDetailName);
                        $('#rowEntityInput').val(cellTag.entityName);
                        if (cellTag.measureName) {
                            $('#rowGoalsInput').val(cellTag.customCode + " " + cellTag.measureName);
                        }
                        $('input[name="includeTaxRow"][value=' + cellTag.includeTax + ']').attr('checked', 'checked');
                        $('input[name="expectRow"][value=' + cellTag.expect + ']').attr('checked', 'checked');
                    } else if (bindDirection == 'col') {
                        $('#colTimeInput').val(cellTag.periodDetailName);
                        $('#colEntityInput').val(cellTag.entityName);
                        if (cellTag.measureName) {
                            $('#colGoalsInput').val(cellTag.customCode + " " + cellTag.measureName);
                        }
                        $('input[name="includeTaxCol"][value=' + cellTag.includeTax + ']').attr('checked', 'checked');
                        $('input[name="expectCol"][value=' + cellTag.expect + ']').attr('checked', 'checked');
                    }
                } else {//非表头
                    showCellBindInfo(sheet, row, col);
                }
            }
        } else if (readOnly == 'true') {
            showCellBindInfo(sheet, row, col);
        }
        //单元格属性
        if (cellTag) {
            var datatype = cellTag.datatype;
            var datatypeValue = measureDataTypeList[datatype];
            if (!datatypeValue) datatypeValue = '';
            $('#goalsDataType').html(datatypeValue);
            if (cellTag.readOnly == 1) {
                $('#readOnly').html('是');
            } else {
                $('#readOnly').html('否');
            }
            var showFuntionTxt = $('#showFuntionTxt');
            if (cellTag.functionId) {
                showFuntionTxt.show();
                showFuntionTxt.find('span').html(cellTag.functionTxt);
            } else {
                showFuntionTxt.hide();
            }
            var controlFlagNameDiv = $('#controlFlagName');
            if (cellTag.controlFlagName) {
                controlFlagNameDiv.show();
                controlFlagNameDiv.find('span').html(cellTag.controlFlagName);
            } else {
                controlFlagNameDiv.hide();
            }
        } else {
            $('#readOnly').html('');
            $('#showFuntionTxt').hide();
        }

        // sheet.startEdit(false); //单击修改
    });
    /*  spread.bind(GC.Spread.Sheets.Events.ButtonClicked, function (e, args) {//点击按钮事件
          var sheet = args.sheet, row = args.row, col = args.col;
          var cellType = sheet.getCellType(row, col);
      });*/

    spread.bind(GC.Spread.Sheets.Events.CellChanged, function (e, info) {
        // console.log(info)
        if (info.sheetArea === GC.Spread.Sheets.SheetArea.viewport) {
            var sheet = info.sheet;
            var row = info.row;
            var col = info.col;
            if (info.propertyName == 'value') {//值变化
            }
            if (info.propertyName == 'formula') {//公式
                /*if(info.newValue==null){
                    sheet.getCell(row,col).backColor('white');
                }else{
                    sheet.getCell(row,col).backColor('yellow');
                }*/
            }

        }
    });
    // alert("Cell index (" + info.row + "," + info.col + ")");

}

function setDefaultLock(sheet) {
    var option = {
        allowSelectLockedCells: true,
        allowSelectUnlockedCells: true,
        allowFilter: true,
        allowSort: false,
        allowResizeRows: true,
        allowResizeColumns: true,
        allowEditObjects: false,
        allowDragInsertRows: false,
        allowDragInsertColumns: false,
        allowInsertRows: true,
        allowInsertColumns: true,
        allowDeleteRows: true,
        allowDeleteColumns: true
    };
    sheet.options.protectionOptions = option;
    sheet.options.isProtected = true;
    var defaultStyle = sheet.getDefaultStyle() //默认的样式，
    defaultStyle.locked = false;
    defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    sheet.setDefaultStyle(defaultStyle);
}

function addContextMenu() {
    for (var i = 0; i < spread.contextMenu.menuData.length; i++) {
        var menu = spread.contextMenu.menuData[i];
        var menuName = menu.name;
        if (menuName == 'gc.spread.filter' || menuName == 'gc.spread.sort') {//去掉过滤、排序
            delete spread.contextMenu.menuData[i];
        }
    }
    // command: showLoginDialog,

}

//固定后 右键菜单
function addDataCellMenu(spread) {
    var sheet = spread.getActiveSheet();
    var readOnly = {text: '只读', name: 'readOnly', workArea: 'viewport', dataCellMenu: 1};
    readOnly.subMenu = [{text: '是', name: 'readOnly_yes', workArea: 'viewport', command: setReadonly}
        , {text: '否', name: 'readOnly_no', workArea: 'viewport', command: setReadonly}]
    spread.contextMenu.menuData.push(readOnly);

    var clearNotSelectMenu = {text: '清空其他区域', name: 'clearNotSelect', workArea: 'viewport', command: clearNotSelect};
    spread.contextMenu.menuData.push(clearNotSelectMenu);
    addDimFunctionMenu(spread);
    addDimFunctionCostbreakdownMenu(spread);

    var dimMatchMenu = {text: '维度匹配', name: 'dimLike', workArea: 'viewport', command: dimMatch};
    dimMatchMenu.subMenu = [{text: '匹配行维度', name: 'dimLikeRow', workArea: 'viewport', command: dimMatchRow}
        , {text: '匹配列维度', name: 'dimLikeCol', workArea: 'viewport', command: dimMatchCol}]
    spread.contextMenu.menuData.push(dimMatchMenu);

}


function setMenuView(spread) {
    function CustomMenuView() {
    }

    CustomMenuView.prototype = new GC.Spread.Sheets.ContextMenu.MenuView();
    CustomMenuView.prototype.createMenuItemElement = function (menuItemData) {
        var self = this;
        if (menuItemData.name === "bind") {
            /* var containers = GC.Spread.Sheets.ContextMenu.MenuView.prototype.createMenuItemElement.call(self, menuItemData);
             var supMenuItemContainer = containers[0];
             while (supMenuItemContainer.firstChild) {
                 supMenuItemContainer.removeChild(supMenuItemContainer.firstChild);
             }
             var colorPicker = createColorpicker();
             supMenuItemContainer.appendChild(colorPicker);

             return supMenuItemContainer;*/
        } else {
            var menuItemView = GC.Spread.Sheets.ContextMenu.MenuView.prototype.createMenuItemElement.call(self, menuItemData);
            return menuItemView;
        }
    };
    CustomMenuView.prototype.getCommandOptions = function (menuItemData, host, event) {
        var option = {};
        if (menuItemData) {
            var name = menuItemData.name;
            if (name && name.indexOf('timeRowMenu') == 0) {
                var text = menuItemData.text;
                for (var i = 0; i < periodDetailList.length; i++) {
                    var detail = periodDetailList[i];
                    if (detail.name == text) {
                        option.id = detail.id;
                        option.name = detail.name;
                        break;
                    }
                }
            }
            if (name && name == 'controlFlag1') {
                option.controlFlag = 1;
            }
            if (name && name == 'readOnly_yes') {
                option.readOnly = 1;
            } else if (name && name == 'readOnly_no') {
                option.readOnly = 0;
            }
            /*if(name && name.indexOf('goalsDataType')==0){
                var code = name.substr('goalsDataType_'.length);
                for(var i=0;i<goalsDataTypeList.length;i++){
                    var dataType = goalsDataTypeList[i];
                    if(dataType.code==code){
                        option.goalsDataType=code;
                        option.goalsDataTypeName=dataType.name;
                        break;
                    }
                }
            }*/
        }
        // console.log(option);
        return option;
    };
    spread.contextMenu.menuView = new CustomMenuView();
}

//添加常用style
function addNameStyle(sheet) {
    var lockStyle = new GC.Spread.Sheets.Style();
    lockStyle.name = 'lock';
    lockStyle.locked = true;
    sheet.addNamedStyle(lockStyle);
    var unLockStyle = new GC.Spread.Sheets.Style();
    unLockStyle.name = 'unLock';
    unLockStyle.locked = false;
    sheet.addNamedStyle(unLockStyle);
}

function save(afterSaveFunction) {

    var sheet = spread.getActiveSheet();
    var tag = sheet.tag();
    var hasFix = false;
    if (tag) {
        if (tag.fix == true) {
            hasFix = true;
        }
    }
    if (!hasFix) {
        _alert('请先生成数据单元格');
        return;
    }
    _commonSave({type: 'normal'}, afterSaveFunction);
}

function SheetSetting() {
    this.bindTime = false;
    this.bindIncludeTax = false;
    this.bindGoals = false;
}

SheetSetting.prototype.toJSON = function () {
    var settings = {};
    var keys = Object.keys(this);
    for (var i = 0; i < keys.length; i++) {
        var key = keys[i];
        settings[key] = this[key];
    }
    return settings;
};
SheetSetting.prototype.fromJSON = function (settings) {
};

function CellSetting() {
}

CellSetting.prototype.toJSON = function () {
    var settings = {};
    var keys = Object.keys(this);
    for (var i = 0; i < keys.length; i++) {
        var key = keys[i];
        settings[key] = this[key];
    }
    return settings;
};
CellSetting.prototype.fromJSON = function (settings) {
    console.log('fromjson');
};


function RowSetting(measureId, measureName) {
    this.measureId = measureId;
    this.measureName = measureName;
}

RowSetting.prototype.toJSON = function () {
    var settings = {};
    var keys = Object.keys(this);
    for (var i = 0; i < keys.length; i++) {
        var key = keys[i];
        settings[key] = this[key];
    }
    return settings;
};
RowSetting.prototype.fromJSON = function (settings) {
};

function ColSetting() {
}

ColSetting.prototype.toJSON = function () {
    var settings = {};
    var keys = Object.keys(this);
    for (var i = 0; i < keys.length; i++) {
        var key = keys[i];
        settings[key] = this[key];
    }
    return settings;
};
ColSetting.prototype.fromJSON = function (settings) {
    console.log('fromjson');
};

function _openDialog(url, title, okCallback, width, height) {
    if (!width) {
        width = '500px';
    }
    if (!height) {
        height = '350px';
    }
    if (width == "100%") {
        width = window.top.document.body.offsetWidth;
    }
    if (height == "100%") {
        height = window.top.document.body.offsetHeight - 100;
    }
    var _selectDialog = $.dialog({
        content: 'url:' + url
        , zIndex: getzIndex()
        , title: title, lock: true, width: width, height: height, opacity: 0.4, button: [
            {
                name: '确定', callback: function () {
                    var iframe = _selectDialog.iframe.contentWindow;
                    if (okCallback) {
                        var b = okCallback(iframe);
                        return b;
                    }
                }
                , focus: true
            },
            {
                name: '取消', callback: function () {
                }
            }
        ]
    }).zindex();
    return _selectDialog;
}

function setIncludeTaxRow() {
    setIncludeTax('row');
}


function setIncludeTaxCol() {
    setIncludeTax('col');
}

function setIncludeTax(type) {
    var sheet = spread.getActiveSheet();
    var itemName = '';
    disableBindButton();

    if (type == 'row') {
        itemName = "includeTaxRow"
    } else {
        itemName = "includeTaxCol"
    }
    $('input[name="' + itemName + '"]').removeAttr('disabled');
    var include = $('input[name="' + itemName + '"]:checked').val();

    beforeBindDim(sheet, type);
    setSelectionTagValue(sheet, 'cell', {includeTax: include});
    setSelectionTagValue(sheet, 'cell', {bindDirection: type});
    setHeader(sheet, type, {includeTax: include});
}

function setTimeRow() {
    setTime('row');
}

function setTimeCol() {
    setTime('col');
}

function setTime(type) {
    var sheet = spread.getActiveSheet();
    var url = 'budgetPeriodDetailController.do?select&periodYear=' + periodYear;
    _select(url, '选择时间维度', function (selectedRows) {
        var one = selectedRows[0];
        var id = one.id;
        var name = one.name;
        var obj = {periodDetailId: id, periodDetailName: name};
        setTimeValue(sheet, type, obj);
    }, '800px', '600px');
}

function setTimeValue(sheet, type, valueObj) {
    var name = valueObj.periodDetailName;
    disableBindButton();
    if (type == 'row') {
        $('#rowTimeInput').val(name);
        $('#rowTimeBind').show();
    } else {
        $('#colTimeInput').val(name);
        $('#colTimeBind').show();
    }
    beforeBindDim(sheet, type);
    setSelectionTagValue(sheet, 'cell', valueObj);
    setSelectionTagValue(sheet, 'cell', {bindDirection: type});
    setHeader(sheet, type, valueObj);
}

function setMeasureRow() {
    setMeasure('row');
}

function setMeasureCol() {
    setMeasure('col');
}

function setMeasure(type) {
    var sheet = spread.getActiveSheet();
    var url = 'budgetMeasureController.do?select';
    _openDialog(url, '选择预算指标', function (iframe) {
        var selectedRows = iframe.getSelectedRow();
        if (selectedRows.length == 0) {
            iframe.tip('请选择预算指标');
            return false;
        }
        var one = selectedRows[0];
        var code = one.code;
        var id = one.id;
        var name = one.name;
        var o = {measureId: id, measureName: name};
        o.datatype = one.datatype;
        o.fractionNum = one.fractionNum;
        o.currencySymbol = one.currencySymbol;
        o.showType = one.showType;
        o.thousandth = one.thousandth;
        o.controlFlag = one.controlFlag;
        o.customCode = one.customCode;
        setMeasureValue(sheet, type, o);
    }, '800px', '600px');
}

function setMeasureValue(sheet, type, valueObj) {
    var name = valueObj.customCode + " " + valueObj.measureName;
    disableBindButton();
    if (type == 'row') {
        $('#rowGoalsInput').val(name);
        $('#rowGoalsBind').show();
    } else {
        $('#colGoalsInput').val(name);
        $('#colGoalsBind').show();
    }
    beforeBindDim(sheet, type);
    setSelectionTagValue(sheet, 'cell', valueObj);
    setSelectionTagValue(sheet, 'cell', {bindDirection: type});
    setHeader(sheet, type, valueObj);
}

//生成数据单元格
function fix() {
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var changeCell = 0;
    for (var i = 0; i < selections.length; i++) {
        var select = selections[i];
        var row = select.row;
        var col = select.col;
        var rowCount = select.rowCount;
        var colCount = select.colCount;
        for (var r = row; r < row + rowCount; r++) {
            for (var c = col; c < col + colCount; c++) {
                var rs = sheet.getTag(r, -1);
                var cs = sheet.getTag(-1, c);

                var cellSetting = sheet.getTag(r, c);
                if (!cellSetting) {
                    cellSetting = new CellSetting();
                }
                if (rs && rs.dimValue) $.extend(cellSetting, rs.dimValue);
                if (cs && cs.dimValue) $.extend(cellSetting, cs.dimValue);
                $.extend(cellSetting, {'dataCell': true});
                var cellString = getCellStr(sheet, r, c);
                cellString = cellString.replace(/\$/g, '');
                if (!cellSetting.measureId) {
                    _alert("单元格:" + cellString + "未绑定指标维度")
                    return;
                }
                if (!cellSetting.periodDetailId) {
                    _alert("单元格:" + cellString + "未绑定时间维度")
                    return;
                }
                if (typeof cellSetting.includeTax == 'undefined') {
                    _alert("单元格:" + cellString + "未绑定含税维度")
                    return;
                }
                if (typeof cellSetting.expect == 'undefined') {
                    _alert("单元格:" + cellString + "未绑定预实维度")
                    return;
                }
                sheet.setTag(r, c, cellSetting);
                changeCell++;
                if (cellSetting.datatype == 'amount') {
                    var currencySymbol = cellSetting.currencySymbol;//货币符号
                    var thousandth = cellSetting.thousandth;//千分位
                    var fractionNum = cellSetting.fractionNum;//小数位
                    var format = '';
                    if (currencySymbol) format += currencySymbol + '* ';
                    if (thousandth) format += '#,###';
                    if (!fractionNum && fractionNum != 0) {
                        fractionNum = '2';
                    }
                    if (fractionNum != 0) {
                        format += '.';
                        for (var n = 0; n < fractionNum; n++) {
                            format += '0';
                        }
                    }
                    sheet.setFormatter(r, c, format);
                }
            }
        }
    }
    if (changeCell > 0) {
        setSheetTag(sheet, {fix: true})
        $.messager.alert('信息', '操作成功');
    } else {
        $.messager.alert('信息', '请选择单元格');
    }

}

function setControlFlag(spread, options) {
    var sheet = spread.getActiveSheet();
    setSelectionTagValue(sheet, 'cell', options);
}

function setSheetTag(sheet, value) {
    var setting = sheet.tag();
    if (!setting) {
        setting = new SheetSetting();
    }
    $.extend(setting, value);
    sheet.tag(setting);
}

function setDataStartIndex(sheet, type, row, col) {
}

function setReadonly(spread, options) {
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var select = selections[0];
    var row = select.row;
    var col = select.col;
    if (options.readOnly == 1) {
        sheet.getCell(row, col).backColor('grey');
    } else {
        sheet.getCell(row, col).backColor('white');
    }
    setSelectionTagValue(sheet, 'cell', options);
}

function setHeader(sheet, type, objValue) {
    var o = {};
    //inst 会用到 显示行表头 或 列表头
    if (type == 'row') {
        o[_vars.rowHeader] = 'true';
        loopSelection(sheet, function (row, col) {
            addCellTagValue(sheet, row, -1, {dimValue: objValue});
        });
    } else if (type == 'col') {
        o[_vars.colHeader] = 'true';
        loopSelection(sheet, function (row, col) {
            addCellTagValue(sheet, -1, col, {dimValue: objValue});
        });
    }
    setSelectionTagValue(sheet, 'cell', o);

}

function disableBindButton(clearValue) {
    if (clearValue) {
        $('#rowTimeInput').val('');
        $('#colTimeInput').val('');
        $('#rowGoalsInput').val('');
        $('#colGoalsInput').val('');
        $('#rowEntityInput').val('');
        $('#colEntityInput').val('');
        $('input[name="includeTaxRow"]').removeAttr('checked');
        $('input[name="includeTaxCol"]').removeAttr('checked');
        $('input[name="expectRow"]').removeAttr('checked');
        $('input[name="expectCol"]').removeAttr('checked');
    }
    /*
        $('#rowTimeBind').hide();
        $('#rowGoalsBind').hide();
        $('input[name="includeTaxRow"]').attr('disabled','disabled');
        $('input[name="expectRow"]').attr('disabled','disabled');
        $('#colTimeBind').hide();
        $('#colGoalsBind').hide();
        $('input[name="includeTaxCol"]').attr('disabled','disabled');
        $('input[name="expectCol"]').attr('disabled','disabled');
        */
}

function clearBind(sheet, type, row, col) {
    var cellTag = sheet.getTag(row, col);
    if (type == 'row') {
        removeDimValue(sheet, row, -1, cellTag);
        clearRowBindInput();
    } else {
        removeDimValue(sheet, -1, col, cellTag);
        clearColBindInput();
    }
    sheet.setTag(row, col, null);
}

function clearRowBindInput() {
    $('#rowTimeInput').val('');
    $('#rowGoalsInput').val('');
    $('#rowEntityInput').val('');
    $('input[name="includeTaxRow"]').removeAttr('checked');
    $('input[name="expectRow"]').removeAttr('checked');
}

function clearColBindInput() {
    $('#colTimeInput').val('');
    $('#colGoalsInput').val('');
    $('#colEntityInput').val('');
    $('input[name="includeTaxCol"]').removeAttr('checked');
    $('input[name="expectCol"]').removeAttr('checked');
}

function setExpectRow() {
    setExpect('row');
}

function setExpectCol() {
    setExpect('col');
}

function setExpect(type) {
    var sheet = spread.getActiveSheet();
    var itemName = '';
    disableBindButton();

    if (type == 'row') {
        itemName = "expectRow"
    } else {
        itemName = "expectCol"
    }
    $('input[name="' + itemName + '"]').removeAttr('disabled');
    var expect = $('input[name="' + itemName + '"]:checked').val();
    beforeBindDim(sheet, type);
    setSelectionTagValue(sheet, 'cell', {expect: expect});
    setSelectionTagValue(sheet, 'cell', {bindDirection: type});
    setHeader(sheet, type, {expect: expect});
}

function dimMatchRow(spread, options) {
    dimMatch(spread, options, 'row')
}

function dimMatchCol(spread, options) {
    dimMatch(spread, options, 'col')
}

function dimMatch(spread, options, type) {
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var select = selections[0];
    var row = select.row;
    var col = select.col;
    var rowCount = select.rowCount;
    var colCount = select.colCount;
    var params = {};
    var totalCol = col + colCount;
    var c = 0;
    for (var i = row; i < row + rowCount; i++) {
        for (var j = col; j < totalCol; j++) {
            var cellValue = sheet.getValue(i, j);
            if (cellValue) {
                params['list[' + c + '].row'] = i;
                params['list[' + c + '].col'] = j;
                params['list[' + c + '].cellValue'] = cellValue;
                c++;
            }
        }
    }
    params.periodId = periodId;
    $.post('budgetTempSheetController.do?dimMatch', params, function (d) {
        if (d.success) {
            sheet.clearSelection();
            var list = d.obj.list;
            for (var i = 0; i < list.length; i++) {
                var one = list[i];
                var row = one.row;
                var col = one.col;
                var bindDirection = type ? type : one.bindDirection;
                if (one.bindDim == 'measure') {
                    sheet.setSelection(row, col, 1, 1);
                    setMeasureValue(sheet, bindDirection, one.bindValue);
                }
                if (one.bindDim == 'period') {
                    sheet.setSelection(row, col, 1, 1);
                    setTimeValue(sheet, bindDirection, one.bindValue);
                }
            }
            sheet.clearSelection();
            tip('匹配成功');
        } else {
            tip('匹配失败');
        }
    }, 'json')

}

function beforeBindDim(sheet, type) {
    checkChangeDircetion(sheet, type);
}

//检查是否换方向绑定
function checkChangeDircetion(sheet, type) {
    loopSelection(sheet, function (row, col) {
        var cellTag = sheet.getTag(row, col);
        if (!cellTag) return;
        var oldBindDirection = cellTag.bindDirection;
        if (oldBindDirection && oldBindDirection != type) {
            clearBind(sheet, oldBindDirection, row, col);
        }
    });
}

function bindCheckNum(spread) {
    spread.bind(GC.Spread.Sheets.Events.EditEnding, function (sender, args) {
        var sheet = args.sheet;
        var row = args.row;
        var col = args.col;
        var cellTag = sheet.getTag(row, col);
        if (cellTag) {
            if (cellTag.dataCell == true && cellTag.datatype && cellTag.datatype != 'txt') {
                var value = args.editingText;
                var reg = /^(-?\d+)(\.\d+)?$/;
                if (value) {
                    if (value.indexOf('=') != 0) {
                        if (!reg.test(value)) {
                            args.cancel = true;
                        }
                    }
                }
            }
        }
    });
}


//清空行维度
function removeRowBind() {
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var select = selections[0];
    var row = select.row;
    var col = select.col;
    var cellTag = sheet.getTag(row, col);
    if (cellTag) {
        var bindDirection = cellTag.bindDirection;
        if (bindDirection) {
            if (bindDirection == 'row') {
                clearBind(sheet, 'row', row, col);
            }
        }
    }
}

//清空列维度
function removeColBind() {
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var select = selections[0];
    var row = select.row;
    var col = select.col;
    var cellTag = sheet.getTag(row, col);
    if (cellTag) {
        var bindDirection = cellTag.bindDirection;
        if (bindDirection) {
            if (bindDirection == 'col') {
                clearBind(sheet, 'col', row, col);
            }
        }
    }
}

function showCellBindInfo(sheet, row, col) {
    $('#rowInfo').hide();
    $('#colInfo').hide();
    $('#info3').show();
    $('#info2').show();
    var cellTag = sheet.getTag(row, col);
    var periodDetailName = cellTag.periodDetailName;
    var measureName = cellTag.customCode + " " + cellTag.measureName;
    var includeTax = cellTag.includeTax;
    var expect = cellTag.expect;
    var entityName = cellTag.entityName;

    $('#timeInput').val(periodDetailName);
    $('#goalsInput').val(measureName);
    $('#entityInput').val(entityName);
    $('input[name="includeTax"]').removeAttr('checked');
    $('input[name="includeTax"][value=' + includeTax + ']').attr('checked', 'checked');
    $('input[name="expect"]').removeAttr('checked');
    $('input[name="expect"][value=' + expect + ']').attr('checked', 'checked');
}

function setEntityRow() {
    setEntity('row');
}

function setEntityCol() {
    setEntity('col');
}

function setEntity(type) {
    var sheet = spread.getActiveSheet();
    _openDialog('budgetEntityController.do?select', '选择预算主体', function (iframe) {
        var selectRows = iframe.getSelectRows();
        var one = selectRows[0];
        spread.suspendPaint();
        setEntityValue(sheet, type, {entityId: one.id, entityName: one.entityName});
        spread.resumePaint();
    }, '800px', '600px');
}

function setEntityValue(sheet, type, valueObj) {
    var name = valueObj.entityName;
    disableBindButton();
    if (type == 'row') {
        $('#rowEntityInput').val(name);
        $('#rowEntityBind').show();
    } else {
        $('#colEntityInput').val(name);
        $('#colEntityBind').show();
    }
    beforeBindDim(sheet, type);
    setSelectionTagValue(sheet, 'cell', valueObj);
    setSelectionTagValue(sheet, 'cell', {bindDirection: type});
    setHeader(sheet, type, valueObj);
}