SpreadJS單元格數據綁定教程

轉帖|使用教程|編輯：龔雪|2016-03-02 15:22:06.000|閱讀 2284 次

概述：SpreadJS是一個JavaScript的電子表格和網格功能控件。用于顯示和管理類似Excel的數據。SpreadJS使用Json作為加載數據的方式。本文主要介紹通過SpreadJS實現單元格數據綁定。

SpreadJS是一個JavaScript的電子表格和網格功能控件。用于顯示和管理類似Excel的數據。SpreadJS使用Json作為加載數據的方式。本文主要介紹通過初始化Spread、綁定按鈕、初始化Source、初始化CellLevel四個步驟來實現單元格數據綁定。

1.初始化Spread

     var spread = new GcSpread.Sheets.Spread($("#ss")[0], { sheetCount: 2 });
            var sheet = spread.getActiveSheet();
            var sourceFormRow = -1;
            var sourceFormCol = -1;
            var sourceFormSheet = spread.getSheet(0);
            spread.newTabVisible(false);
            spread.highlightInvalidData(true);
            //cell:0 row:1 column:2
            sheet.selectionUnit(1);
            sheet.allowEditorReservedLocations(true);
            initDataSourceBind(spread.getSheet(0));
            initCellLevelBind(spread.getSheet(1));
            sheet.getColumn(0).visible(false);
            sheet.getColumn(7).visible(false);
            sheet.setIsProtected(true);
            sheet.getColumn(sheet.getColumnCount() - 1).locked(false);
            sheet.getColumn(sheet.getColumnCount() - 2).locked(false);

2.綁定按鈕

 spread.bind(GcSpread.Sheets.Events.ButtonClicked, function (e, args)
            {
                var clickSheet = args.sheet;
                var clickRow = args.row;
                var clickCol = args.col;
                var cellType = clickSheet.getCellType(clickRow, clickCol);
                if (clickSheet.getName() == "SourceForm")
                {
                    if (cellType instanceof GcSpread.Sheets.ButtonCellType)
                    {
                        sourceFormRow = clickRow;
                        sourceFormCol = clickCol;
                        if (args.col === (clickSheet.getColumnCount() - 2))
                        {
                            var result = confirm("Are you sure you want to delete this record", "OK", "Cancel");
                            if (result)
                            {
                                clickSheet.deleteRows(clickSheet.getActiveRowIndex(), 1);
                                if (clickSheet.isEditing())
                                {
                                    clickSheet.endEdit();
                                }
                            }
                        }
                        if (args.col === clickSheet.getColumnCount() - 1)
                        {
                            var clickItem = clickSheet.getDataSource()[clickRow];
                            _dummySource.id = clickItem.id;
                            _dummySource.name = clickItem.name;
                            _dummySource.line = clickItem.line;
                            _dummySource.color = clickItem.color;
                            _dummySource.price = clickItem.price;
                            _dummySource.cost = clickItem.cost;
                            _dummySource.weight = clickItem.weight;
                            _dummySource.discontinued = clickItem.discontinued;
                            _dummySource.rating = clickItem.rating;
                            _dummySource.dataIndex = clickRow;
                            var detialFormSheet = args.sheet.parent.getSheet(1);
                            detialFormSheet.setDataSource(new GcSpread.Sheets.CellBindingSource(_dummySource));
                            args.sheet.parent.setActiveSheetIndex(1);
                        }
                    }
                }
                if (clickSheet.getName() == "DetialForm")
                {
                    if (cellType instanceof GcSpread.Sheets.ButtonCellType)
                    {
                        if (sourceFormRow == -1)
                        {
                            alert("Please Input Valid Data");
                            return -1;
                        }
                        else
                        {
                            var updateItem = sourceFormSheet.getDataSource()[sourceFormRow];
                            updateItem.id = _dummySource.id;
                            updateItem.name = _dummySource.name;
                            updateItem.line = _dummySource.line;
                            updateItem.color = _dummySource.color;
                            updateItem.price = _dummySource.price;
                            updateItem.cost = _dummySource.cost;
                            updateItem.weight = _dummySource.weight;
                            updateItem.discontinued = _dummySource.discontinued;
                            updateItem.rating = _dummySource.rating;
                            args.sheet.parent.setActiveSheetIndex(0);
                        }
                    }
                }
            });

3.初始化Source

  function initDataSourceBind(sheet)
        {
            sheet.isPaintSuspended(true);
            sheet.setName("SourceForm");
            sheet.autoGenerateColumns = false;
            sheet.setDataSource(getProducts(100));
            var lineCellType = new GcSpread.Sheets.ComboBoxCellType();
            lineCellType.items(_lines);
            var colorCellType = new GcSpread.Sheets.ComboBoxCellType();
            colorCellType.items(_colors);
            var checkBoxCellType = new GcSpread.Sheets.CheckBoxCellType();
            var ratingCellType = new GcSpread.Sheets.ComboBoxCellType();
            ratingCellType.items(_ratings);
            var cis = [
                { name: "id", displayName: "ID", size: 25 },
                { name: "name", displayName: "Name", size: 100 },
                { name: "line", displayName: "Line", cellType: lineCellType, size: 80 },
                { name: "color", displayName: "Color", cellType: colorCellType },
                { name: "price", displayName: "Price", formatter: "0.00" },
                { name: "cost", displayName: "Cost", formatter: "0.00" },
                { name: "weight", displayName: "Weight", formatter: "0.00" },
                { name: "discontinued", displayName: "Discontinued", cellType: checkBoxCellType, size: 80 },
                { name: "rating", displayName: "Rating", cellType: ratingCellType }
            ];
            sheet.bindColumns(cis);
            sheet.getColumn(7).hAlign(GcSpread.Sheets.HorizontalAlign.center)
                .vAlign(GcSpread.Sheets.VerticalAlign.center);
            sheet.defaults.rowHeight = 23;
            var columnCount = sheet.getColumnCount();
            sheet.addColumns(columnCount, 2);
            var deleteCellType = new GcSpread.Sheets.ButtonCellType();
            deleteCellType.marginTop(4).marginRight(4).marginBottom(4).marginLeft(4);
            deleteCellType.text("Delete");
            sheet.getColumn(columnCount).cellType(deleteCellType).hAlign(GcSpread.Sheets.HorizontalAlign.center).vAlign(GcSpread.Sheets.VerticalAlign.center);
            sheet.setValue(0, columnCount, "", GcSpread.Sheets.SheetArea.colHeader);
            var showDetialCellType = new GcSpread.Sheets.ButtonCellType();
            showDetialCellType.marginTop(4).marginRight(4).marginBottom(4).marginLeft(4);
            showDetialCellType.text("Detial");
            sheet.getColumn(columnCount + 1).cellType(showDetialCellType).hAlign(GcSpread.Sheets.HorizontalAlign.center).vAlign(GcSpread.Sheets.VerticalAlign.center);
            sheet.setValue(0, columnCount + 1, "", GcSpread.Sheets.SheetArea.colHeader);
            //set SourceForm column's width and the header's height
            for (var i = 1; i < sheet.getColumnCount() - 2; i++)
            {
                sheet.setColumnWidth(i, 80);
            }
            sheet.setColumnWidth(1, 110);
            sheet.setColumnWidth(2, 90);
            sheet.setRowHeight(0, 40, GcSpread.Sheets.SheetArea.colHeader);
            sheet.isPaintSuspended(false);
        }

4.初始化CellLevel

function initCellLevelBind(sheet)
        {
            sheet.isPaintSuspended(true);
            sheet.setName("DetialForm");
            sheet.setColumnWidth(1, 90);
            sheet.setColumnWidth(2, 100);
            sheet.setColumnWidth(3, 90);
            sheet.setColumnWidth(4, 100);
            sheet.setColumnWidth(5, 100);
            sheet.setColumnWidth(6, 100);
            sheet.setRowHeight(6, 40);
            sheet.setRowHeight(14, 30);
            sheet.addSpan(6, 1, 1, 2);
            sheet.addSpan(6, 3, 1, 2);
            sheet.addSpan(6, 5, 1, 2);
            sheet.setGridlineOptions({ showVerticalGridline: false, showHorizontalGridline: false });
            sheet.canUserDragDrop(false);
            sheet.canUserDragFill(false);
            //set border of the area which can be edited
            for (var i = 0; i < 5; i += 2)
            {
                for (var j = 0; j < 4; j++)
                {
                    var range_1 = new GcSpread.Sheets.Range(7 + j, 2 + i, 1, 1);
                    if (i + 2 == 6)
                    {
                        sheet.setBorder(range_1, new GcSpread.Sheets.LineBorder("rgb(216,216,216)", GcSpread.Sheets.LineStyle.thin), { right: true, bottom: true }, 3);
                    }
                    sheet.setBorder(range_1, new GcSpread.Sheets.LineBorder("rgb(216,216,216)", GcSpread.Sheets.LineStyle.thin), { bottom: true }, 3);
                }
            }
            sheet.getCell(6, 1).value("Product Info").font("bold 15pt Courier New").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center).foreColor("white").backColor("rgb(182,134,218)");
            sheet.getCell(6, 3).value("Market Info").font("bold 15pt Courier New").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center).foreColor("white").backColor("rgb(182,134,218)");
            sheet.getCell(6, 5).value("Status").font("bold 15pt Courier New").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center).foreColor("white").backColor("rgb(182,134,218)");
            sheet.getCells(7, 1, 10, 1).font("bold 10pt Courier New").foreColor("rgb(64,64,64)").backColor("rgb(216,216,216)").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center);
            sheet.getCells(7, 3, 10, 3).font("bold 10pt Courier New").foreColor("rgb(64,64,64)").backColor("rgb(216,216,216)").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center);
            sheet.getCells(7, 5, 10, 5).font("bold 10pt Courier New").foreColor("rgb(64,64,64)").backColor("rgb(216,216,216)").vAlign(GcSpread.Sheets.VerticalAlign.center).hAlign(GcSpread.Sheets.HorizontalAlign.center);
            sheet.getCells(11, 1, 11, 6).backColor("rgb(182,134,218)");
            sheet.setValue(7, 1, "Name", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(8, 1, "Line", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(9, 1, "Color", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(7, 3, "Price", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(8, 3, "Cost", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(9, 3, "Weight", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(7, 5, "Discontinued", GcSpread.Sheets.SheetArea.viewport);
            sheet.setValue(8, 5, "Rating", GcSpread.Sheets.SheetArea.viewport);
            var dv1 = new GcSpread.Sheets.DefaultDataValidator.createNumberValidator(GcSpread.Sheets.ComparisonOperator.GreaterThan, 0);
            sheet.setDataValidator(7, 4, dv1);
            sheet.setDataValidator(8, 4, dv1);
            sheet.setDataValidator(9, 4, dv1);
            var lineCellType = new GcSpread.Sheets.ComboBoxCellType();
            lineCellType.items(_lines);
            var colorCellType = new GcSpread.Sheets.ComboBoxCellType();
            colorCellType.items(_colors);
            var checkBoxCellType = new GcSpread.Sheets.CheckBoxCellType();
            var ratingCellType = new GcSpread.Sheets.ComboBoxCellType();
            ratingCellType.items(_ratings);
            sheet.getCell(7, 2).bindingPath("name");
            sheet.getCell(8, 2).bindingPath("line").cellType(lineCellType);
            sheet.getCell(9, 2).bindingPath("color").cellType(colorCellType);
            sheet.getCell(7, 4).bindingPath("price");
            sheet.getCell(8, 4).bindingPath("cost");
            sheet.getCell(9, 4).bindingPath("weight");
            sheet.getCell(7, 6).bindingPath("discontinued").cellType(checkBoxCellType);
            sheet.getCell(8, 6).bindingPath("rating").cellType(ratingCellType);
            var updateCellType = new GcSpread.Sheets.ButtonCellType();
            updateCellType.text("Update");
            sheet.getCell(14, 6).cellType(updateCellType).hAlign(GcSpread.Sheets.HorizontalAlign.center).vAlign(GcSpread.Sheets.VerticalAlign.center);
            sheet.isPaintSuspended(false);
        }