SpreadJS表格控件如何进行单元格数据绑定

作者：控件中国网出处：控件中国网 2015-12-15 15:06:14 阅读：12

SpreadJS是一个JavaScript的电子表格和网格功能控件。用于显示和管理类似Excel的数据。SpreadJS使用Json作为加载数据的方式。本文主要介绍通过SpreadJS单元格数据绑定。

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);

}

功能分类