spreadjs使用

初始化表单API

const spreadNS = GC.Spread.Sheets;const SHEETS = new spreadNS.Workbook(this.refs['overseas']);// set sheet count// SHEETS.setSheetCount(1);const sheet = SHEETS.sheets[0];

设置默认属性

const defaultStyle = new GC.Spread.Sheets.Style();// 设置默认背景色// @method1 defaultStyle.backColor = "LemonChiffon";// @method2 SHEETS.options.backColor = "#ccc"; //defaultStyle.foreColor = "Red";//defaultStyle.formatter = "0.00";defaultStyle.font   = "bold normal 9px normal"defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;//defaultStyle.borderLeft = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);//defaultStyle.borderTop = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);//defaultStyle.borderRight = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);//defaultStyle.borderBottom = new GC.Spread.Sheets.LineBorder("Green",GC.Spread.Sheets.LineStyle.medium);sheet.setDefaultStyle(defaultStyle, spreadNS.SheetArea.viewport);

表格的四个部分

spreadNS.SheetArea.viewport// spreadNS.SheetArea include the following parts// colHeader: 1 ,//    corner: 0// rowHeader: 2//  viewport: 3// 改变表头的背景 Set the backcolor of second row header.sheet.getCell(1, 0, GC.Spread.Sheets.SheetArea.rowHeader).backColor("Yellow");

其他配置

// 表格下边的tab的颜色 sheet.options.sheetTabColor = "red";// 表格只读 sheet.options.isProtected = true// 允许cell内容移除activeSheet.options.allowCellOverflow = true;// 第一列不可见sheet.setColumnVisible(0, false)// 添加tips sheet.comments.add(4, 4, "不要修改!");// 禁止用户编辑公式 spread.options.allowUserEditFormula = false;// 不显示格子的边 sheet.options.gridline = {    color:"red",     showVerticalGridline: true,    showHorizontalGridline: false};// 不显示表头 activeSheet.options.colHeaderVisible = false;activeSheet.options.rowHeaderVisible = false;

减少重绘

spread.reset() // 清空数据 spread.suspendPaint();  //suspendPaint 暂停重绘  先这样 很多操作之后 resumePaintspread.addSheet(0);spread.fromJSON(json);  // json dataspread.resumePaint();   // 调用resumePaint 重新激活Spread重绘 

excel 运算符

引用运算符 含义（示例）:（冒号） 区域运算符，产生对包括在两个引用之间的所有单元格的引用 (B5:B15),（逗号） 联合运算符，将多个引用合并为一个引用 (SUM(B5:B15,D5:D15))（空格） 交叉运算符产生对两个引用共有的单元格的引用。(B7:D7 C6:C8)

设置单元格属性

      sheet.getCell(2, 1).text("huahua").             // 设置文字 backColor("rgba(1,1,1,.3)") //设置背景色 

公式

// 随机数  sheet.setFormula(1, 1, "RandBetween(45,85)");// 求和 sheet.setFormula(1, 1, "SUM(A1,B1)"); //A1和B1之和sheet.setFormula(1, 1, "SUM(A1:H1)"); //A1到H1之和 // 条件 sheet.setFormula(4, 1, "IF(A1>10, A1*2, A1*3)");

开启R1C1 引用

// open r1c1 referenceSHEETS.options.referenceStyle = 1;

基本操作

// 添加行/列 sheet.addRows(1, 1);    sheet.addColumns(1, 1);// 设置背景图片activeSheet.getCell(1, 1).backgroundImage("Image file path name");

设置row和column的背景色

/*** @name set the bgColor of column** @type1* sheet.getRange(-1, 1, -1, 1).backColor("lightYellow").width(330);** @name set the bgColor of row** @type1* sheet.getRange(0, -1, 1, -1).backColor("lightYellow").height(44)** @type2* const rowStyle1 = new spreadNS.Style();* rowStyle1.backColor = "#bdcde3";* sheet.setStyle(0, -1, rowStyle1, spreadNS.SheetArea.viewport);*/

events

// 获取点击的cell和所在的sheet SHEETS.bind(spreadNS.Events.EnterCell, function (event, data) {    console.log(data.col)    console.log(data.row)    console.log(data)    var activeSheet = data.sheet;    activeSheet.startEdit(true);});// 离开cell事件 sheet.bind(GC.Spread.Sheets.Events.LeaveCell, function (event, infos) {    //Reset the backcolor of cell before moving    infos.sheet.getCell(infos.row, infos.col).backColor(undefined);});