ConstructionOperation/web/users/js/col_setting.js

/**
 * Created by Mai on 2017/8/14.
 */

let ColSettingObj = {
    colSetting: null,
    DEFAULT_TITLE_STYLE: null,
    DEFAULT_DATA_STYLE: null,
    cellType: {
        getText: null,
        cellType: null,
        readOnly: null,
        checkBox: null
    },
    Rows: {data: 0, filedName: 0, getText: 1, wordWrap: 2, cellType: 3, width: 4, readOnly: 5, showHint: 6, visible: 7, customize: 8},
    columnValueChanged: function (e, info) {
        let that = ColSettingObj;
        info.colList.forEach(function (iCol) {
            info.sheet.setValue(that.colSetting.headRows + that.Rows.width, iCol, info.sheet.getColumnWidth(iCol), GC.Spread.Sheets.SheetArea.viewport);
        });
    },
    valueChanged: function (e, info) {
        let that = ColSettingObj;
        if (info.row === that.colSetting.headRows + that.Rows.width) {
            info.sheet.setColumnWidth(info.col, info.newValue, GC.Spread.Sheets.SheetArea.viewport);
        }
    },
    selectionChanged: function (e, info) {
        let that = ColSettingObj, sel = info.newSelections[0];
        if (sel.row <= that.colSetting.headRows) {
            $('.btn-toolbar').removeClass('disabled');
            $('#font').val(info.sheet.getCell(sel.row, sel.col, GC.Spread.Sheets.SheetArea.viewport).font());
        } else {
            $('.btn-toolbar').addClass('disabled');
        }
    },
    getCellStyle: function (font, hAlign, vAlign) {
        var style = new GC.Spread.Sheets.Style();
        style.font = font;
        style.hAlign = hAlign;
        style.vAlign = vAlign;
        style.wordWrap = true;
        return style;
    },
    initCellType: function () {
        this.cellType.readOnly = new GC.Spread.Sheets.CellTypes.ComboBox();
        this.cellType.readOnly.items([true, false,
            'readOnly.bills', 'readOnly.ration', 'readOnly.volumePrice',
            'readOnly.non_bills', 'readOnly.non_ration', 'readOnly.non_volumePrice',
            'readOnly.billsParent', 'readOnly.forCalcBase', 'readOnly.forUnitFee',
            'readOnly.forTotalFee', 'readOnly.leafBillsWithDetail'
        ]);

        this.cellType.getText = new GC.Spread.Sheets.CellTypes.ComboBox();
        this.cellType.getText.items(['getText.type']);

        this.cellType.cellType = new GC.Spread.Sheets.CellTypes.ComboBox();
        this.cellType.cellType.items(['cellType.units']);

        this.cellType.checkBox = new GC.Spread.Sheets.CellTypes.CheckBox();
    },
    initSheet: function (sheet, setting) {
        let initColProperty = function (iRow, title) {
            sheet.setText(setting.headRows + iRow, 0, title, GC.Spread.Sheets.SheetArea.rowHeader);
            sheet.addSpan(setting.headRows + iRow, 0, 1, 2, GC.Spread.Sheets.SheetArea.rowHeader);
        };

        sheet.setColumnCount(2, GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setColumnWidth(0, 80, GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setColumnWidth(1, 70, GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setRowCount(setting.headRows + this.Rows.customize + 1);

        sheet.setText(setting.headRows + this.Rows.data, 0, 'Data', GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setStyle(setting.headRows + this.Rows.data, -1, this.DEFAULT_DATA_STYLE);
        sheet.addSpan(setting.headRows + this.Rows.data, 0, this.Rows.wordWrap + 1, 1, GC.Spread.Sheets.SheetArea.rowHeader);

        sheet.setText(setting.headRows + this.Rows.filedName, 1, 'FieldName', GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setText(setting.headRows + this.Rows.getText, 1, 'getText', GC.Spread.Sheets.SheetArea.rowHeader);
        sheet.setText(setting.headRows + this.Rows.wordWrap, 1, 'wordWrap', GC.Spread.Sheets.SheetArea.rowHeader);

        initColProperty(this.Rows.width, 'Width');
        initColProperty(this.Rows.readOnly, 'ReadOnly');
        initColProperty(this.Rows.showHint, 'ShowHint');
        initColProperty(this.Rows.visible, 'Visible');
        initColProperty(this.Rows.cellType, 'CellType');
        initColProperty(this.Rows.customize, 'Customize');
    },
    initColSetting: function (setting) {
        this.DEFAULT_TITLE_STYLE = this.getCellStyle('Arial', GC.Spread.Sheets.HorizontalAlign.center, GC.Spread.Sheets.VerticalAlign.center);
        this.DEFAULT_DATA_STYLE = this.getCellStyle('Arial', GC.Spread.Sheets.HorizontalAlign.left, GC.Spread.Sheets.VerticalAlign.center);
        this.initCellType();

        $('#empty-rows').val(setting.emptyRows);
        $('#col-count').val(setting.cols ? setting.cols.length : 0);
        $('#header-row-count').val(setting.headRows);

        colEditSpread = new GC.Spread.Sheets.Workbook($('#colEditSpread')[0], {sheetCount: 1});
        colEditSpread.getActiveSheet().setRowCount(0);
        colEditSpread.getActiveSheet().setColumnCount(0);
        colEditSpread.options.tabStripVisible = false;
        colEditSpread.bind(GC.Spread.Sheets.Events.ColumnWidthChanged, this.columnValueChanged);
        colEditSpread.bind(GC.Spread.Sheets.Events.ValueChanged, this.valueChanged);
        colEditSpread.bind(GC.Spread.Sheets.Events.SelectionChanged, this.selectionChanged);

        colEditSpread.getActiveSheet().suspendPaint();

        this.initSheet(colEditSpread.getActiveSheet(), setting);
        this.setColCount(this.colSetting.cols.length);
        this.setHeaderRowCount(this.colSetting.headRows);
        // headerHeight
        for (let iRow in this.colSetting.headRowHeight) {
            colEditSpread.getActiveSheet().setRowHeight(iRow, this.colSetting.headRowHeight[iRow], GC.Spread.Sheets.SheetArea.viewport);
        }

        if (setting.treeCol >= 0) {
            $('#is-tree')[0].checked = true;
            $('#tree-col-div').removeClass('hidden');
            $('#tree-col').val(setting.treeCol);
        } else {
            $('#is-tree')[0].checked = false;
            $('#tree-col-div').addClass('hidden');
        }

        if (setting.cols) {
            let sheet = colEditSpread.getActiveSheet(), iRow;
            for (let iCol = 0; iCol < setting.cols.length; iCol++) {
                let col = setting.cols[iCol], iRow = 0;
                // header
                for (let i in col.head.spanCols) {
                    if (col.head.spanCols[i] !== 0) {
                        let cell = sheet.getCell(iRow, iCol, GC.Spread.Sheets.SheetArea.viewport);
                        cell.value(col.head.titleNames[i]).font(col.head.font[i]).hAlign(col.head.hAlign[i]).vAlign(col.head.vAlign[i]);
                    }
                    if (col.head.spanCols[i] > 1 || col.head.spanRows[i] > 1) {
                        sheet.addSpan(iRow, iCol, col.head.spanRows[i], col.head.spanCols[i], GC.Spread.Sheets.SheetArea.viewport);
                    }
                    iRow += col.head.spanRows[i];
                };
                // data
                // field
                let cell = sheet.getCell(this.colSetting.headRows + this.Rows.data, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.value(col.data.field).font(col.data.font).hAlign(col.data.hAlign).vAlign(col.data.vAlign);
                // getText
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.getText, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.getText).value(col.data.getText).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
                // wordWrap
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.wordWrap, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.checkBox).value(col.data.wordWrap).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
                // cellType
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.cellType, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.cellType).value(col.data.cellType).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
                // 列宽
                sheet.setColumnWidth(iCol, col.width);
                sheet.setValue(this.colSetting.headRows + this.Rows.width, iCol, sheet.getColumnWidth(iCol), GC.Spread.Sheets.SheetArea.viewport);
                // readonly
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.readOnly, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.readOnly).value(col.readOnly).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
                // showHint
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.showHint, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.checkBox).value(col.showHint).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
                // visible
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.visible, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.checkBox).value(col.visible).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
                // customize
                cell = sheet.getCell(this.colSetting.headRows + this.Rows.customize, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.cellType(this.cellType.checkBox).value(col.customize).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
            }
        }

        colEditSpread.getActiveSheet().resumePaint();

        let cell = colEditSpread.getActiveSheet().getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport);
        if (cell) {
            $('#font').val(cell.font());
        }
    },
    setColCount: function (count) {
        let sheet = colEditSpread.getActiveSheet();
        let orgCount = sheet.getColumnCount();
        sheet.setColumnCount(count);
        for (let iCol = orgCount; iCol < count; iCol++) {
            for (let iRow = 0; iRow < this.colSetting.headRows; iRow ++) {
                sheet.setStyle(iRow, iCol, this.DEFAULT_TITLE_STYLE);
            }
            sheet.getCell(this.colSetting.headRows + this.Rows.getText, iCol).cellType(this.cellType.getText).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
            sheet.getCell(this.colSetting.headRows + this.Rows.wordWrap, iCol).cellType(this.cellType.checkBox).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
            sheet.getCell(this.colSetting.headRows + this.Rows.cellType, iCol).cellType(this.cellType.cellType).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
            sheet.setValue(this.colSetting.headRows + this.Rows.width, iCol, sheet.getColumnWidth(iCol), GC.Spread.Sheets.SheetArea.viewport);
            sheet.getCell(this.colSetting.headRows + this.Rows.readOnly, iCol).cellType(this.cellType.readOnly).hAlign(GC.Spread.Sheets.HorizontalAlign.right);
            sheet.setValue(this.colSetting.headRows + this.Rows.readOnly, iCol, false, GC.Spread.Sheets.SheetArea.viewport);
            sheet.getCell(this.colSetting.headRows + this.Rows.showHint, iCol).cellType(this.cellType.checkBox).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
            sheet.getCell(this.colSetting.headRows + this.Rows.visible, iCol).cellType(this.cellType.checkBox).hAlign(GC.Spread.Sheets.HorizontalAlign.center).value(true);
            sheet.getCell(this.colSetting.headRows + this.Rows.customize, iCol).cellType(this.cellType.checkBox).hAlign(GC.Spread.Sheets.HorizontalAlign.center).value(false);
        }
    },
    setHeaderRowCount: function (count) {
        let sheet = colEditSpread.getActiveSheet(), orgCount = this.colSetting.headRows;
        if (count < orgCount) {
            sheet.deleteRows(count, orgCount - count);
        } else if (count > orgCount) {
            sheet.addRows(orgCount, count - orgCount);
            sheet.addSpan(0, 0, count, 1, GC.Spread.Sheets.SheetArea.rowHeader);
            for (let iRow = orgCount; iRow < count; iRow++) {
                sheet.setStyle(iRow, -1, this.DEFAULT_TITLE_STYLE);
            }
        }
    },
    getActualCellRange: function (cellRange, rowCount, columnCount) {
        if (cellRange.row == -1 && cellRange.col == -1) {
            return new spreadNS.Range(0, 0, rowCount, columnCount);
        }
        else if (cellRange.row == -1) {
            return new spreadNS.Range(0, cellRange.col, rowCount, cellRange.colCount);
        }
        else if (cellRange.col == -1) {
            return new spreadNS.Range(cellRange.row, 0, cellRange.rowCount, columnCount);
        }

        return cellRange;
    },
    // 批量操作Spread选择的单元格
    controlSelectCells: function (spread, control) {
        let sheet = colEditSpread.getActiveSheet();
        let sel = sheet.getSelections(), i, j;

        sel = this.getActualCellRange(sel[sel.length - 1], sheet.getRowCount(), sheet.getColumnCount());
        for (i = 0; i < sel.rowCount; i++) {
            for (j = 0; j < sel.colCount; j++) {
                control(sheet.getCell(sel.row + i, sel.col + j));
            }
        }
    },
    generateColSetting: function () {
        let setting = {}, sheet = colEditSpread.getActiveSheet();
        setting.emptyRows = parseInt($('#empty-rows').val());
        setting.headRows = parseInt($('#header-row-count').val());
        if ($('#is-tree')[0].checked) {
            setting.treeCol = parseInt($('#tree-col').val());
        }
        setting.headRowHeight = [];
        for (let iRow = 0; iRow < setting.headRows; iRow++) {
            setting.headRowHeight.push(sheet.getRowHeight(iRow, GC.Spread.Sheets.SheetArea.viewport));
        };
        setting.cols = [];
        for (let iCol = 0; iCol < sheet.getColumnCount(); iCol++) {
            let col = {};
            col.width = sheet.getColumnWidth(iCol);
            col.readOnly = sheet.getValue(setting.headRows + this.Rows.readOnly, iCol) || false;
            if (sheet.getValue(setting.headRows + this.Rows.showHint, iCol)) {
                col.showHint = sheet.getValue(setting.headRows + this.Rows.showHint, iCol) || false;
            }
            col.visible = sheet.getValue(setting.headRows + this.Rows.visible, iCol) || false;
            col.customize = sheet.getValue(setting.headRows + this.Rows.customize, iCol) || false;

            col.head = {};
            col.head.titleNames = [];
            col.head.spanCols = [];
            col.head.spanRows = [];
            col.head.vAlign = [];
            col.head.hAlign = [];
            col.head.font = [];
            for (let iRow = 0; iRow < setting.headRows; iRow++) {
                let cell = sheet.getCell(iRow, iCol);
                col.head.titleNames.push(cell.text());
                let span = sheet.getSpan(iRow, iCol);
                if (span) {
                    if (span.col === iCol && span.row === iRow) {
                        col.head.spanCols.push(span.colCount);
                        col.head.spanRows.push(span.rowCount);
                    } else {
                        col.head.spanCols.push(0);
                        col.head.spanRows.push(1);
                    }
                } else {
                    col.head.spanCols.push(1);
                    col.head.spanRows.push(1);
                }
                col.head.vAlign.push(cell.vAlign());
                col.head.hAlign.push(cell.hAlign());
                col.head.font.push(cell.font());
            }

            col.data = {};
            let cell = sheet.getCell(setting.headRows, iCol);
            col.data.field = cell.text();
            col.data.vAlign = cell.vAlign();
            col.data.hAlign = cell.hAlign();
            col.data.font = cell.font();
            // getText
            cell = sheet.getCell(setting.headRows + this.Rows.getText, iCol);
            if (cell.text() !== '') {
                col.data.getText = cell.text();
            }
            setting.cols.push(col);
            // wordWrap
            col.data.wordWrap = sheet.getValue(setting.headRows + this.Rows.wordWrap, iCol) || false;
        }
        return setting;
    }
};

$('#set-column').on('shown.bs.modal', function () {
    if (!colEditSpread) {
        ColSettingObj.colSetting = JSON.parse(mainTreeCol);
        ColSettingObj.initColSetting(ColSettingObj.colSetting);
    }
});
$('#col-count').change(function () {
    ColSettingObj.setColCount(parseInt($(this).val()));
});
$('#header-row-count').change(function () {
    ColSettingObj.setHeaderRowCount(parseInt($(this).val()));
    ColSettingObj.colSetting.headRows = parseInt($(this).val());
});
$('#is-tree').click(function () {
    if (this.checked) {
        $('#tree-col-div').removeClass('hidden');
    } else {
        $('#tree-col-div').addClass('hidden');
    }
});
$('#merge').click(function () {
    let sheet = colEditSpread.getActiveSheet();
    let sel = sheet.getSelections();

    if (sel.length > 0) {
        sel = ColSettingObj.getActualCellRange(sel[sel.length - 1], sheet.getRowCount(), sheet.getColumnCount());
        if (sel.row + sel.rowCount > ColSettingObj.colSetting.headRows) {
            alert('仅HeaderTitle部分可以合并单元格');
        } else {
            sheet.addSpan(sel.row, sel.col, sel.rowCount, sel.colCount);
            //sheet.setTag(sel.row, sel.col, 1);
        }
    }
});
$('#unmerge').click(function () {
    let sheet = colEditSpread.getActiveSheet();
    let sel = sheet.getSelections();

    if (sel.length > 0) {
        sel = ColSettingObj.getActualCellRange(sel[sel.length - 1], sheet.getRowCount(), sheet.getColumnCount());
        sheet.suspendPaint();
        for (var i = 0; i < sel.rowCount; i++) {
            for (var j = 0; j < sel.colCount; j++) {
                sheet.removeSpan(i + sel.row, j + sel.col);
            }
        }
        sheet.resumePaint();
    }
});
$('#save-col-setting').click(function () {
    ColSettingObj.colSetting = ColSettingObj.generateColSetting();
    mainTreeCol = JSON.stringify(ColSettingObj.colSetting);
    $('input:hidden[name="main_tree_col"]').val(mainTreeCol);
    $('#set-column').modal('hide');

    let billsTemplateTree = idTree.createNew({id: 'ID', pid: 'ParentID', nid: 'NextSiblingID', rootId: -1});
    billsTemplateTree.loadDatas(JSON.parse(billsTemplateData));
    TREE_SHEET_HELPER.loadSheetHeader(ColSettingObj.colSetting, colSpread.getActiveSheet());
    TREE_SHEET_HELPER.showTreeData(ColSettingObj.colSetting, colSpread.getActiveSheet(), billsTemplateTree);
});

$('#h-left').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
    });
});
$('#h-center').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
    });
});

$('#h-right').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.hAlign(GC.Spread.Sheets.HorizontalAlign.right);
    });
});

$('#v-top').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.vAlign(GC.Spread.Sheets.VerticalAlign.top);
    });
});
$('#v-center').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.vAlign(GC.Spread.Sheets.VerticalAlign.center);
    });
});
$('#v-bottom').click(function () {
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
    });
});

$('#set-font').click(function () {
    var sFont = $('#font').val();
    ColSettingObj.controlSelectCells(colEditSpread, function (cell) {
        cell.font(sFont);
    });
});