/** * Created by Tony on 2017/4/28. */ var sheetCommonObj = { // CSL.2017.06.05 // createSpread、initSheet 在一个Spread多个Sheet分别调用时的情况下使用。 createSpread: function (container, SheetCount) { var me = this; var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: SheetCount }); spreadBook.options.tabStripVisible = false; spreadBook.options.showHorizontalScrollbar = true; spreadBook.options.showVerticalScrollbar = true; spreadBook.options.allowCopyPasteExcelStyle = false; spreadBook.options.allowUserDragDrop = true; spreadBook.options.allowContextMenu = false; spreadBook.options.allowUserEditFormula = false; spreadBook.options.showDragFillSmartTag = false; return spreadBook; }, initSheet: function (sheet, setting, rowCount) { var me = this; var spreadNS = GC.Spread.Sheets; sheet.suspendPaint(); sheet.suspendEvent(); if (setting.frozenCols) sheet.frozenColumnCount(setting.frozenCols);//冻结列 sheet.setRowCount(1, spreadNS.SheetArea.colHeader); sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport); if (setting && setting.view && setting.view.colHeaderHeight) { sheet.setRowHeight(0, setting.view.colHeaderHeight, spreadNS.SheetArea.colHeader); }; if (setting && setting.view && setting.view.rowHeaderWidth) { sheet.setColumnWidth(0, setting.view.rowHeaderWidth, spreadNS.SheetArea.rowHeader); }; if (setting.emptyRowHeader) { sheet.setColumnWidth(0, 1, GC.Spread.Sheets.SheetArea.rowHeader); } sheet.options.colHeaderAutoTextIndex = 1; sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers; sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values; sheet.options.protectionOptions = { allowResizeColumns: true }; sheet.showRowOutline(false); sheet.options.allowCellOverflow = false; me.buildHeader(sheet, setting); if (rowCount > 0) sheet.setRowCount(rowCount); else sheet.setRowCount(1); sheet.resumeEvent(); sheet.resumePaint(); }, // buildSheet 在一个Spread、一个Sheet的情况下使用。 buildSheet: function (container, setting, rowCount) { var me = this; var spreadBook = me.createSpread(container, { sheetCount: 1 }); var sheet = spreadBook.getSheet(0); me.initSheet(sheet, setting, rowCount); return spreadBook; }, buildHeader: function (sheet, setting) { var me = this, ch = GC.Spread.Sheets.SheetArea.colHeader; for (var i = 0; i < setting.header.length; i++) { sheet.setValue(0, i, setting.header[i].headerName, ch); sheet.getCell(0, i, ch).wordWrap(true); sheet.setColumnWidth(i, setting.header[i].headerWidth ? setting.header[i].headerWidth : 100); sheet.setColumnVisible(i, setting.header[i].visible === false ? false : true); } }, cleanSheet: function (sheet, setting, rowCount) { sheet.suspendPaint(); sheet.suspendEvent(); sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data); if (rowCount > 0) sheet.setRowCount(rowCount); sheet.clearSelection(); sheet.resumeEvent(); sheet.resumePaint(); }, cleanData: function (sheet, setting, rowCount) { sheet.suspendPaint(); sheet.suspendEvent(); sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data); if (rowCount > 0) sheet.setRowCount(rowCount); sheet.resumeEvent(); sheet.resumePaint(); }, setAreaAlign: function (area, hAlign, vAlign) { if (!(hAlign) || hAlign === "left") { area.hAlign(GC.Spread.Sheets.HorizontalAlign.left); } else if (hAlign === "right") { area.hAlign(GC.Spread.Sheets.HorizontalAlign.right); } else if (hAlign === "center") { area.hAlign(GC.Spread.Sheets.HorizontalAlign.center); } else { area.hAlign(GC.Spread.Sheets.HorizontalAlign.left); } if (!(vAlign) || vAlign === "center") { area.vAlign(GC.Spread.Sheets.VerticalAlign.center); } else if (vAlign === "top") { area.vAlign(GC.Spread.Sheets.VerticalAlign.top); } else if (vAlign === "bottom") { area.vAlign(GC.Spread.Sheets.VerticalAlign.bottom); } else { area.vAlign(GC.Spread.Sheets.VerticalAlign.center); } }, showData: function (sheet, setting, data, distTypeTree, callback) {//这个callback是为了在showdata后还做了引起重画表格的操作，在callback里调用能提高效率 var me = this, ch = GC.Spread.Sheets.SheetArea.viewport; sheet.suspendPaint(); sheet.suspendEvent(); //sheet.addRows(row, 1); sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data); if (sheet.getRowCount() < data.length) { data.length < 30 ? sheet.setRowCount(30) : sheet.setRowCount(data.length); } else if (sheet.getRowCount() == 0) { sheet.setRowCount(30); } for (var col = 0; col < setting.header.length; col++) { var hAlign = "left", vAlign = "center"; if (setting.header[col].hAlign) { hAlign = setting.header[col].hAlign; } else if (setting.header[col].dataType !== "String") { hAlign = "right"; } vAlign = setting.header[col].vAlign ? setting.header[col].vAlign : vAlign; me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign); if (setting.header[col].formatter) { sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport); } /* 直接在showrowdata时当val为null时返回一个text类型的单元格 if (setting.header[col].cellType === "checkBox" || setting.header[col].cellType === "button") {//clear and reset var me = this, header = GC.Spread.Sheets.SheetArea.colHeader; sheet.deleteColumns(col, 1); sheet.addColumns(col, 1); sheet.setValue(0, col, setting.header[col].headerName, header); sheet.setColumnWidth(col, setting.header[col].headerWidth ? setting.header[col].headerWidth : 100); } */ if (setting.header[col].visible !== null && setting.header[col].visible !== undefined) { sheet.setColumnVisible(col, setting.header[col].visible); } sheet.getCell(0, col, GC.Spread.Sheets.SheetArea.colHeader).wordWrap(true); } for (var row = 0; row < data.length; row++) { //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport); this.showRowData(sheet, setting, row, data, distTypeTree); } if (setting.emptyRowHeader) { let rowCount = sheet.getRowCount(); for (let row = 0; row < rowCount; row++) { sheet.setValue(row, 0, '', GC.Spread.Sheets.SheetArea.rowHeader); } } this.lockCells(sheet, setting); if (callback) callback(); sheet.resumeEvent(); sheet.resumePaint(); //me.shieldAllCells(sheet); }, showRowData: function (sheet, setting, row, data, distTypeTree = null) { let ch = GC.Spread.Sheets.SheetArea.viewport; for (var col = 0; col < setting.header.length; col++) { //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport); var val = _.get(data[row],setting.header[col].dataCode); if (val && setting.header[col].dataType === "Number") { if (setting.header[col].hasOwnProperty('tofix')) { val = scMathUtil.roundToString(val, setting.header[col].tofix); } else if (setting.header[col].hasOwnProperty('decimalField')) { var decimal = getDecimal(setting.header[col].decimalField); val = scMathUtil.roundToString(val, decimal); sheet.setFormatter(-1, col, getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport); } else { val = val + ''; } } if (setting.header[col].cellType === "checkBox") { this.setCheckBoxCell(row, col, sheet, val) } if (setting.header[col].cellType === "comboBox") { this.setComboBox(row, col, sheet, setting.header[col].options, setting.header[col].editorValueType, setting.header[col].editable, setting.header[col].maxDropDownItems); } if (setting.header[col].cellType === "selectButton") { this.setSelectButton(row, col, sheet, setting.header[col]); } if (setting.header[col].cellType === "replaceButton") { this.setReplaceButton(row, col, sheet, setting.header[col]); } if (setting.header[col].cellType === "cusButton") { this.setCusButton(row, col, sheet, setting); } if (setting.header[col].cellType === "tipsCell") { this.setTipsCell(row, col, sheet, setting.header[col]); } if (setting.owner === 'gljTree') { if (setting.header[col].cellType === "checkBox") { val= val == 1 ? val : 0; this.setCheckBoxCell(row, col, sheet, val); } if (setting.header[col].dataCode === 'gljType' && data[row].gljType) { let distTypeVal = distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName; val = distTypeVal; } } if (setting.header[col].getText) { val = setting.getText[setting.header[col].getText](data[row], val) } sheet.setValue(row, col, val, ch); } this.setRowStyle(row, sheet, data[row].bgColour); if (setting.autoFit == true) {//设置自动行高 if (setting.fitRow && setting.fitRow.length > 0) {//如果有设置特定的某些列才需要自动行高就按设置的来，没有设置就默认所有列 for (let dataCode of setting.fitRow) { let col = _.findIndex(setting.header, { dataCode: dataCode }); sheet.getCell(row, col).wordWrap(true); } } else { sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).wordWrap(true); } sheet.autoFitRow(row); } if (setting.getStyle && setting.getStyle(data[row], row, sheet.getActiveRowIndex())) { sheet.setStyle(row, -1, setting.getStyle(data[row])); } }, showTreeData:function (sheet,setting,data) { let ch = GC.Spread.Sheets.SheetArea.viewport; let parentMap=_.groupBy(data, 'ParentID'); let visibleMap = {}; let styleRow=[]; let treeCol = setting.treeCol?setting.treeCol:0; sheet.suspendPaint(); sheet.suspendEvent(); for (let col = 0; col < setting.header.length; col++) { let hAlign = "left", vAlign = "center"; if (setting.header[col].hAlign) { hAlign = setting.header[col].hAlign; } else if (setting.header[col].dataType !== "String"){ hAlign = "right"; } vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign; sheetCommonObj.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign); if (setting.header[col].formatter) { sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport); } if(setting.header[col].cellType === "comboBox"){ this.setComboBox(-1,col,sheet,setting.header[col].options,setting.header[col].editorValueType,setting.header[col].editable,setting.header[col].maxDropDownItems); } for (let row = 0; row < data.length; row++) { if(data[row].cellType === 'comboBox'){ let options = data[row].options ? data[row].options.split("@") : []; this.setComboBox(row,col,sheet,options); }else if(data[row].cellType === 'String'){//默认设置字符输入，避免出现输入10：01变成日期的情况 sheet.setFormatter(row, col,"@", GC.Spread.Sheets.SheetArea.viewport); } let val = data[row][setting.header[col].dataCode]; if(val&&setting.header[col].dataType === "Number"){ if(setting.header[col].hasOwnProperty('decimalField')){ let decimal = getDecimal(setting.header[col].decimalField); val =scMathUtil.roundToString(val,decimal); sheet.setFormatter(-1, col,getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport); }else { val = val + ''; } } if(val!=null && setting.header[col].cellType == "checkBox"){ this.setCheckBoxCell(row,col,sheet,val); } if(setting.header[col].getText) { val = setting.getText[setting.header[col].getText](data[row], val) } sheet.setValue(row, col, val, ch); if(col==treeCol){ let treeType = sheetCommonObj.getTreeNodeCellType(data,row,parentMap,treeCol); sheet.getCell(row, col).cellType(treeType); visibleMap[data[row].ID] = treeType.collapsed; this.setRowVisible(data,row,visibleMap,sheet); } if(data[row].bgColour) styleRow.push(row) } } for(let r of styleRow){ this.setRowStyle(r,sheet,data[r].bgColour); } this.lockCells(sheet,setting); sheet.resumeEvent(); sheet.resumePaint(); }, setRowVisible:function (data,row,visibleMap,sheet) { sheet.getRange(row , -1, 1, -1).visible(getVisible(data[row].ParentID));//显示或隐藏 function getVisible(ParentID) { if(visibleMap[ParentID]) return false //如果父节点是缩起的，那就隐藏本身。 if(visibleMap[ParentID] == false){//如果父节点不是缩起的，要再往父节点找看 let pnode = _.find(data,{'ID':ParentID}); if(pnode) return getVisible(pnode.ParentID);//如果有父节点，递归调用 return true;//没有，返回显示 } } }, checkData: function (col, setting, value) { let result = true; let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null; if (validator === null) { return result; } switch (validator) { case 'number': let regular = /^\d+(\.\d+)?$/; result = regular.test(value); break; case 'boolean': let booleanValue = [true, false]; result = booleanValue.indexOf(value) >= 0; break; } return result; }, analyzePasteData: function (setting, pastedInfo) { var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong for (var i = 0; i < pastedInfo.pasteData.text.length; i++) { if (pastedInfo.pasteData.text[i] === "\n") { propId = pastedInfo.cellRange.col;//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong preStrIdx = i + 1; rst.push(itemObj); if (i < pastedInfo.pasteData.text.length - 1) { itemObj = {}; } } else if (pastedInfo.pasteData.text[i] === "\t" || pastedInfo.pasteData.text[i] === "\r") { if (setting.header[propId]) { itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx, i); } propId++; preStrIdx = i + 1; //if the last copied-cell were empty, should check whether the end of text if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) { itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx); rst.push(itemObj); } } else if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) { itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx); rst.push(itemObj); } } return rst; }, combineRowData: function (sheet, setting, row) { var rst = {}; for (var col = 0; col < setting.header.length; col++) { rst[setting.header[col].dataCode] = sheet.getValue(row, col); } return rst; }, shieldAllCells: function (sheet) { sheet.options.isProtected = true; }, unShieldAllCells: function (sheet) { sheet.options.isProtected = false; }, lockCells: function (sheet, setting) { if (setting && setting.view.lockColumns && setting.view.lockColumns.length > 0) { sheet.options.isProtected = true; sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false); for (var i = 0; i < setting.view.lockColumns.length; i++) { let col = setting.view.lockColumns[i]; if (_.isString(col)) {//如果是dataCode 进行转换 col = _.findIndex(setting.header, { dataCode: col }) } sheet.getRange(-1, col, -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true); } } }, setCheckBoxCell(row, col, sheet, val) { var c = val==null?new GC.Spread.Sheets.CellTypes.Text():new GC.Spread.Sheets.CellTypes.CheckBox(); if(val != null) c.isThreeState(false); sheet.setCellType(row, col, c, GC.Spread.Sheets.SheetArea.viewport); sheet.getCell(row, col).value(val); sheet.getCell(row, col).hAlign(GC.Spread.Sheets.HorizontalAlign.center); }, setComboBox(row, col, sheet, options, editorValueType, editable, maxDropDownItems) { //let combo = new GC.Spread.Sheets.CellTypes.ComboBox(); let dynamicCombo = sheetCommonObj.getDynamicCombo(true); if (options) { dynamicCombo.items(options); if (editorValueType == true) { dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value); } if (editable) dynamicCombo.editable(true); if (maxDropDownItems) dynamicCombo.maxDropDownItems(maxDropDownItems); } sheet.setCellType(row, col, dynamicCombo, GC.Spread.Sheets.SheetArea.viewport); }, setRowStyle(row, sheet, bgColour) { if (bgColour) { let style = new GC.Spread.Sheets.Style(); style.backColor = bgColour; style.borderLeft = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin); style.borderTop = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin); style.borderRight = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin); style.borderBottom = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin); sheet.setStyle(row, -1, style); } }, getCustomerCoeCellType: function (htmlGenerator, setEditorValue, updateCallback) { let me = this; function CustomerCoeCellType() { this.isEscKey = false; this.displayText = ''; } CustomerCoeCellType.prototype = new GC.Spread.Sheets.CellTypes.Base(); CustomerCoeCellType.prototype.createEditorElement = function (context) { console.log("create editor") let element = document.createElement("div");//这里创建的，会自动销毁 return element }; CustomerCoeCellType.prototype.activateEditor = function (editorContext, cellStyle, cellRect, context) { if (editorContext) { $editor = $(editorContext); $editor.css("position", "fixed"); $editor.css("background", "white"); //$editor.css("width", cellRect.width); 2018-11-15 改成固定列宽 $editor.css("width", 160); $editor.attr("gcUIElement", "gcEditingInput"); if (htmlGenerator) htmlGenerator(context, cellRect, $editor); } } CustomerCoeCellType.prototype.deactivateEditor = function (editorContext, context) { }; CustomerCoeCellType.prototype.setEditorValue = function (editor, value, context) { console.log("set editor value"); this.displayText = value; }; CustomerCoeCellType.prototype.getEditorValue = function (editor, context) { console.log("get value"); if (this.isEscKey != true && updateCallback) { updateCallback(); } this.isEscKey = false; return this.displayText; }; CustomerCoeCellType.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) { console.log(" update editor"); if (setEditorValue) {//不是esc时才更新 setEditorValue(context); } }; CustomerCoeCellType.prototype.isReservedKey = function (e, context) { //cell type handle tab key by itself this.isEscKey = e.keyCode === GC.Spread.Commands.Key.esc; return false; }; return new CustomerCoeCellType(); }, scrollSheetForOption: function (sheet, cxt, cellRect, row, options) { let topRow = sheet.getViewportTopRow(1); if (row == topRow) return;//已经是最顶行了 let length = options && options.length > 0 ? options.length : 1; let height = cxt.canvas.height; let startY = cellRect.y + cellRect.height;//下拉框的起始显示位置 let endY = startY + length * cellRect.height;//下拉框的结束显示位置 if (endY <= height) return; //如果没有超出显示位置，直接返回 let overRow = Math.ceil((endY - height) / cellRect.height);//超出的行数 let showRow = topRow + overRow > row ? row : topRow + overRow; sheet.showRow(showRow, GC.Spread.Sheets.VerticalPosition.top); }, setSelectButton(row, col, sheet, header) { sheet.setCellType(row, col, this.getSelectButton(header.headerWidth), GC.Spread.Sheets.SheetArea.viewport); }, getSelectButton(cellWidth = 100) { function moreButton() { } moreButton.prototype = new GC.Spread.Sheets.CellTypes.Button(); moreButton.prototype.paint = function (ctx, value, x, y, w, h, style, options) { GC.Spread.Sheets.CellTypes.Button.prototype.paint.call(this, ctx, value, x, y, w, h, style, options); ctx.font = '14px Calibri'; let buttonW = cellWidth / 5; let endX = x + w - 2; if (value) { let textWidth = ctx.measureText(value).width; let spaceWidth = cellWidth - buttonW; let textEndX = x + textWidth + 2; if (spaceWidth < textWidth) { for (let i = value.length - 1; i > 1; i--) { let newValue = value.substr(0, i); let newTestWidth = ctx.measureText(newValue).width; if (spaceWidth > newTestWidth) { value = newValue; textEndX = x + newTestWidth + 2; break; } } } ctx.fillText(value, textEndX, y + h - 6); } //画三个点 ctx.save(); ctx.beginPath(); ctx.arc(endX - buttonW / 2, y + h / 2, 1, 0, 360, false); ctx.arc(endX - buttonW / 2 - 4, y + h / 2, 1, 0, 360, false); ctx.arc(endX - buttonW / 2 + 4, y + h / 2, 1, 0, 360, false); ctx.fillStyle = "black";//填充颜色,默认是黑色 ctx.fill();//画实心圆 ctx.closePath(); ctx.restore(); }; moreButton.prototype.processMouseLeave = function (hitinfo) { let newCell = new selectButton(); hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, newCell, GC.Spread.Sheets.SheetArea.viewport); hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(false); }; function selectButton() { } selectButton.prototype = new GC.Spread.Sheets.CellTypes.Text(); selectButton.prototype.paint = function (ctx, value, x, y, w, h, style, options) { GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments); }; selectButton.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) { return { x: x, y: y, row: context.row, col: context.col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: context.sheetArea }; }; selectButton.prototype.processMouseDown = function (hitinfo) { if (hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked() != true) { let b1 = new moreButton(); b1.marginLeft(cellWidth * 4 / 5); hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, b1, GC.Spread.Sheets.SheetArea.viewport); hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(true); } }; return new selectButton(); }, setCusButton: function (row, col, sheet, setting) { let functionName = setting.header[col].callback; let readOnly = setting.disable[setting.header[col].disable]; if (typeof (readOnly) == 'function') { readOnly = readOnly(row, col); } if (functionName) { sheet.setCellType(row, col, this.getCusButtonCellType(setting.callback[functionName], readOnly)); } //sheet.setCellType(row, col,this.getSelectButton(header.headerWidth),GC.Spread.Sheets.SheetArea.viewport); }, getCusButtonCellType: function (callback, readOnly = false) { var ns = GC.Spread.Sheets; function CusButtonCellType() { } CusButtonCellType.prototype = new ns.CellTypes.Text(); CusButtonCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) { if (!readOnly) { if (options.sheet.getActiveRowIndex() == options.row && options.sheet.getActiveColumnIndex() == options.col) { var image = document.getElementById('f_btn'), imageMagin = 3; var imageHeight = 15; var imageWidth = 25; var imageX = x + w - imageWidth - imageMagin, imageY = y + h / 2 - imageHeight / 2; ctx.save(); if (style.backColor) { ctx.fillStyle = style.backColor; ctx.fillRect(x, y, w, h); } ctx.drawImage(image, imageX, imageY, imageWidth, imageHeight); ctx.beginPath(); ctx.arc(imageX + imageWidth / 2, imageY + imageHeight / 2, 1, 0, 360, false); ctx.arc(imageX + imageWidth / 2 - 4, imageY + imageHeight / 2, 1, 0, 360, false); ctx.arc(imageX + imageWidth / 2 + 4, imageY + imageHeight / 2, 1, 0, 360, false); ctx.fillStyle = "black";//填充颜色,默认是黑色 ctx.fill();//画实心圆 ctx.closePath(); ctx.restore(); w = w - imageWidth - imageMagin; //这里的左对齐的，当显示的字长度超过空白地方时，要改成右对齐 if (style.hAlign == 0) { if (value) { let textWidth = ctx.measureText(value).width; let spaceWidth = w; if (spaceWidth < textWidth) { style.hAlign = 2; } } } } } GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments); }; CusButtonCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) { return { x: x, y: y, row: context.row, col: context.col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: context.sheetArea }; }; CusButtonCellType.prototype.processMouseDown = function (hitinfo) { if (hitinfo.sheet.getActiveRowIndex() == hitinfo.row && hitinfo.sheet.getActiveColumnIndex() == hitinfo.col) { var offset = hitinfo.cellRect.x + hitinfo.cellRect.width - 6; var imageWidth = 25; if (hitinfo.x < offset && hitinfo.x > offset - imageWidth) { if (!readOnly) { if (callback) callback(hitinfo) } } } }; return new CusButtonCellType(); }, setReplaceButton(row, col, sheet) { let replaceButton = function () { }; replaceButton.prototype = new GC.Spread.Sheets.CellTypes.Button(); replaceButton.prototype.paint = function (ctx, value, x, y, w, h, style, options) { GC.Spread.Sheets.CellTypes.Button.prototype.paint.apply(this, arguments); if (value) { ctx.save(); ctx.fillStyle = "white"; let fh = options.fontInfo && options.fontInfo.fontSize ? options.fontInfo.fontSize : h - 6; ctx.fillText(value, x + (w + ctx.measureText(value).width) / 2, y + (h + fh) / 2 - 2); ctx.restore(); } }; let cellType = new replaceButton(); cellType.buttonBackColor("#07A0FF"); sheet.setCellType(row, col, cellType, GC.Spread.Sheets.SheetArea.viewport); }, setTipsCell(row, col, sheet, header) { let TipCellType = function () { }; TipCellType.prototype = new GC.Spread.Sheets.CellTypes.Text(); TipCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) { return { x: x, y: y, row: context.row, col: context.col, cellStyle: cellStyle, cellRect: cellRect, sheet: context.sheet, sheetArea: context.sheetArea }; }; TipCellType.prototype.processMouseEnter = function (hitinfo) { let text = hitinfo.sheet.getText(hitinfo.row, hitinfo.col); let value = hitinfo.sheet.getValue(hitinfo.row, hitinfo.col); let tag = hitinfo.sheet.getTag(hitinfo.row, hitinfo.col); let acStyle = hitinfo.sheet.getActualStyle(hitinfo.row, hitinfo.col), zoom = hitinfo.sheet.zoom(); let textLength = this.getAutoFitWidth(value, text, acStyle, zoom, { sheet: hitinfo.sheet, row: hitinfo.row, col: hitinfo.col, sheetArea: GC.Spread.Sheets.SheetArea.viewport }); let cellWidth = hitinfo.sheet.getCell(-1, hitinfo.col).width(); let setting = {}; if (textLength <= cellWidth) { return; } if (sheet && sheet.getParent().qo) { setting.pos = SheetDataHelper.getObjPos(sheet.getParent().qo); } TREE_SHEET_HELPER.showTipsDiv(text, setting, hitinfo); }; TipCellType.prototype.processMouseLeave = function (hitinfo) { TREE_SHEET_HELPER.tipDiv = 'hide'; if (TREE_SHEET_HELPER._toolTipElement) { $(TREE_SHEET_HELPER._toolTipElement).hide(); TREE_SHEET_HELPER._toolTipElement = null; }; TREE_SHEET_HELPER.tipDivCheck();//延时检查：当tips正在show的时候，就调用了hide方法，会导致tips一直存在，所以设置一个超时处理 }; sheet.setCellType(row, col, new TipCellType(), GC.Spread.Sheets.SheetArea.viewport); }, chkIfEmpty: function (rObj, setting) { var rst = true; if (rObj) { for (var i = 0; i < setting.header.length; i++) { if (rObj[setting.header[i].dataCode]) { rst = false; break; } } } return rst; }, //add by zhong 2017-10-10 //动态下拉框,配合EnterCell, args.sheet.repaint(); getDynamicCombo: function (forLocked) { let ComboCellForActiveCell = function () { }; ComboCellForActiveCell.prototype = new GC.Spread.Sheets.CellTypes.ComboBox(); ComboCellForActiveCell.prototype.paintValue = function (ctx, value, x, y, w, h, style, options) { let sheet = options.sheet; if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) { GC.Spread.Sheets.CellTypes.ComboBox.prototype.paintValue.apply(this, arguments); } else { GC.Spread.Sheets.CellTypes.Base.prototype.paintValue.apply(this, arguments); } }; ComboCellForActiveCell.prototype.getHitInfo = function (x, y, cellStyle, cellRect, options) { let sheet = options.sheet; if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) { return GC.Spread.Sheets.CellTypes.ComboBox.prototype.getHitInfo.apply(this, arguments); } else { return { x: x, y: y, row: options.row, col: options.col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: options.sheetArea };//GC.Spread.Sheets.CellTypes.Text.prototype.getHitInfo.apply(this, arguments); } }; return new ComboCellForActiveCell(); }, getTipsCombo: function (forLocked, tips, setting, node) { let getTipsCombo = function () { this.clickCom = false; }; getTipsCombo.prototype = sheetCommonObj.getDynamicCombo(forLocked); if (tips && tips != "") { getTipsCombo.prototype.processMouseEnter = function (hitinfo) { if (this.clickCom == true) { //点击了下拉框的三角形，则不用再显示悬浮框了 this.clickCom = false; return; } let text = typeof tips == 'function' ? tips(node) : tips; TREE_SHEET_HELPER.delayShowTips(hitinfo, setting, text); }; getTipsCombo.prototype.processMouseLeave = function (hitinfo) { TREE_SHEET_HELPER.hideTipsDiv(); }; getTipsCombo.prototype.processMouseDown = function (hitinfo) { if (hitinfo.isReservedLocation == true) {//这里是点击了下拉框的三角形才会有这个属性 TREE_SHEET_HELPER.hideTipsDiv(); this.clickCom = true; } GC.Spread.Sheets.CellTypes.ComboBox.prototype.processMouseDown.apply(this, arguments); }; getTipsCombo.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) { TREE_SHEET_HELPER.hideTipsDiv(); GC.Spread.Sheets.CellTypes.ComboBox.prototype.updateEditor.apply(this, arguments); }; } return new getTipsCombo(); }, getTreeNodeCellType: function (datas, row, parentMap,treeCol) {// 2018-09-26 不用spreadjs默认的树结构，自定义控件 var ns = GC.Spread.Sheets; let rectW = 10; let rectH = 10; let margin = 3; function TreeNodeCellType() { this.collapsed = gljUtil.isDef(datas[row].collapsed) ? datas[row].collapsed : true; //默认是折叠的 this.treeNodeType = true; this.rectInfo = {}; } TreeNodeCellType.prototype = new ns.CellTypes.Text(); TreeNodeCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) { let offset = 0; let step = 7; let level = getTreeLevel(datas[row], datas);//从0开始，取当前节点是第几级的 let tem = offset + margin + rectW / 2 + step;//两条线之间的间隔 let t_offset = offset; let temParentID = datas[row].ParentID; if (this.treeNodeType == true) { for (let i = level; i > 0; i--) {//这里是画子节点前面的竖线，从第二级开始 let temParent = getParent(temParentID, datas); if (temParent) {//父节点有下一个兄弟节点才需要画 if (hasNextBrother(parentMap, temParent)) sheetCommonObj.drawLine(ctx, x + t_offset + tem * i, y, x + t_offset + tem * i, y + h); temParentID = temParent.ParentID; } offset += tem; } } offset += step; //这个没法移动,所以要两个判断 if (this.treeNodeType == true) { if (hasChildern(datas[row].ID, datas)) {//如果是有子节点 //第一条 或者没有父节点(如费率子表综合里程项)不用画方框头上那条竖线其它都要 if (row != 0 && gljUtil.isDef(datas[row].ParentID)) sheetCommonObj.drawLine(ctx, x + offset + rectW / 2 + margin, y, x + offset + rectW / 2 + margin, y + Math.round(h / 2) - rectH / 2); //画方框下面的那条竖线，如果没有下一个兄弟节点，则不用画 if (hasNextBrother(parentMap, datas[row])) sheetCommonObj.drawLine(ctx, x + offset + rectW / 2 + margin, y + Math.round(h / 2) + rectH / 2, x + offset + rectW / 2 + margin, y + h); sheetCommonObj.drowRect(ctx, x + offset, y, w, h, rectW, rectH, margin); sheetCommonObj.drowSymbol(ctx, x + offset, y, w, h, rectW, rectH, margin, this.collapsed); this.rectInfo = { x: x + offset + margin, rectW: rectW }//计录一下可点击位置 } else { let hasNext = datas[row + 1] ? datas[row + 1].ParentID == datas[row].ParentID : false; sheetCommonObj.drowSubItem(ctx, x, y, w, h, offset, hasNext, margin + rectW / 2); } } offset += step; offset += rectW; x = x + offset;//设置偏移 w = w - offset; GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments); }; // override getHitInfo to allow cell type get mouse messages TreeNodeCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) { return { x: x, y: y, row: context.row, col: context.col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: context.sheetArea }; } TreeNodeCellType.prototype.processMouseDown = function (hitinfo) { ////方框外1像素内都有效 if (!_.isEmpty(this.rectInfo) && Math.floor(hitinfo.x) <= this.rectInfo.x + this.rectInfo.rectW + 2 && Math.floor(hitinfo.x) >= this.rectInfo.x - 2) { this.collapsed = !this.collapsed; datas[row].collapsed = this.collapsed; this.refreshChildrenVisible(hitinfo.sheet); hitinfo.sheet.invalidateLayout(); hitinfo.sheet.repaint(); } }; TreeNodeCellType.prototype.refreshChildrenVisible = function (sheet) { sheet.suspendPaint(); sheet.suspendEvent(); refreshVisible(datas[row]); sheet.resumeEvent(); sheet.resumePaint(); function refreshVisible(item) { if (parentMap[item.ID]) { for (let sub of parentMap[item.ID]) { refreshVisible(sub) } } let visible = getVisible(item); let trow = datas.indexOf(item); sheet.getRange(trow, -1, 1, -1).visible(visible); } function getVisible(item) { if (item.ParentID) { let parent = getParent(item.ParentID, datas); if (!parent) return true; let p_row = datas.indexOf(parent); treeCol = treeCol?treeCol:0; let visible = !sheet.getCellType(p_row, treeCol).collapsed; if (visible == true) { //如果是显示的，则要再往父节点的父节点检查，只要有一个节点是隐藏的，则都是隐藏 return getVisible(parent); } else { return visible } } else {//如果parentID 为空则是最根节点 return true; } } }; return new TreeNodeCellType() function getTreeLevel(item, data) { if (item.ParentID && item.ParentID!=-1) { let pitem = _.find(data, { 'ID': item.ParentID }); return getTreeLevel(pitem, data) + 1; } else { return 0 } } function hasChildern(ID, data) {//返回是否有子项 let p = _.find(data, { 'ParentID': ID }); if (p) return true; return false } function getParent(ParentID, data) { let p = _.find(data, { 'ID': ParentID }); return p; } function hasNextBrother(parentMap, item) { let children = parentMap[item.ParentID]; if (!gljUtil.isDef(children) || children.indexOf(item) == children.length - 1) return false; return true } }, setDynamicCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) { let me = this; sheet.suspendPaint(); let combo = me.getDynamicCombo(); for (let i = 0, len = rowCount; i < len; i++) { if (itemsHeight) { combo.itemHeight(itemsHeight); combo._maxDropDownItems = itemsHeight + 5; } if (itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value); else if (itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text); else combo.items(items); sheet.getCell(beginRow + i, col).cellType(combo); } sheet.resumePaint(); }, setStaticCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) { sheet.suspendPaint(); let combo = new GC.Spread.Sheets.CellTypes.ComboBox(); for (let i = 0, len = rowCount; i < len; i++) { if (itemsHeight) combo.itemHeight(itemsHeight); if (itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value); else if (itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text); else combo.items(items); sheet.getCell(beginRow + i, col).cellType(combo); } sheet.resumePaint(); }, //设置系统粘贴板数据,需要用户触发事件，直接调用会失败 copyTextToClipboard: function (text) { let textArea = document.createElement("textarea"); textArea.style.position = 'fixed'; textArea.style.top = 0; textArea.style.left = 0; textArea.style.width = '2em'; textArea.style.height = '2em'; textArea.style.padding = 0; textArea.style.border = 'none'; textArea.style.outline = 'none'; textArea.style.boxShadow = 'none'; textArea.style.background = 'transparent'; textArea.value = text; document.body.appendChild(textArea); textArea.select(); try { let successful = document.execCommand('copy'); let msg = successful ? 'successful' : 'unsuccessful'; console.log('Copying text command was ' + msg); } catch (err) { console.log('Oops, unable to copy'); } document.body.removeChild(textArea); }, //获取选中区域的表格类型数据（可粘贴到excel） getTableData: function (sheet, colSettings = null) { let rst = ''; let sel = sheet.getSelections()[0]; let pasteText = []; for (let row = sel.row; row < sel.row + sel.rowCount; row++) { if (!sheet.getCell(row, -1).visible()) continue; let rowText = []; for (let j = 0; j < sel.colCount; j++) { let col = sel.col + j; if (!sheet.getCell(-1, col).visible()) continue; if (colSettings && (colSettings[col]['data']['field'] === 'itemCharacterText' || colSettings[col]['data']['field'] === 'jobContentText')) rowText.push(sheet.getText(row, col) ? `"${sheet.getText(row, col)}"` : ''); else rowText.push(sheet.getText(row, col) ? sheet.getText(row, col) : ''); } pasteText.push(rowText.join('\t')); } return pasteText.join('\n'); }, transferToTreeSetting: function (setting, treeSetting, treeCol) { for (let h of setting.header) { treeSetting.cols.push(getSettingCol(h)) } for (let l of setting.view.lockColumns) { treeSetting.cols[l].readOnly = true; } return treeSetting; function getSettingCol(header) { let aMap = { left: 0, center: 1, right: 2 }; let hAlign = header.hAlign ? aMap[header.hAlign] : 0; let col = { "width": header.headerWidth ? header.headerWidth : 100, "head": { "titleNames": Array.isArray(header.headerName) ? header.headerName : [header.headerName], "spanCols": header.spanCols ? header.spanCols : [1], "spanRows": header.spanRows ? header.spanRows : [1], "vAlign": [1], "hAlign": [1], "font": ["Arial"] }, "data": { "field": header.dataCode, "vAlign": 1, "hAlign": hAlign, "font": "Arial" } }; if (header.showHint == true) { col.showHint = true; } if (header.cellType) { col.data.cellType = getCellType(header); } if (header.decimalField) {//设置formatter let decimal = getDecimal(header.decimalField); col.formatter = getFormatter(decimal); } if (header.getText && treeCol) { col.data.getText = treeCol.getEvent(header.getText); } /*col.readOnly = function (node) { if(node.data.ParentID == -1 || node.data.id == 'GJ'){//三材类别项不能编辑) return true; } return false; };*/ return col; } function getCellType(header) { return function () { if (header.cellType === "checkBox") { return new GC.Spread.Sheets.CellTypes.CheckBox(); } if (header.cellType === "comboBox") { let dynamicCombo = sheetCommonObj.getDynamicCombo(true); if (header.options) { dynamicCombo.itemHeight(header.options.length).items(header.options); if (header.editorValueType == true) { dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value); } } return dynamicCombo } } } }, //注册自定义回车键事件 bindEnterKey: function (workBook, operation) { workBook.commandManager().register('myEnter', operation); workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.enter, false, false, false, false); workBook.commandManager().setShortcutKey('myEnter', GC.Spread.Commands.Key.enter, false, false, false, false); }, //解决esc后触发了编辑结束的保存事件，显示与实际数据不同问题 bindEscKey: function (workBook, sheets) { function isDef(v) { return typeof v !== 'undefined' && v !== null; } workBook.commandManager().register('myEsc', function () { let activeSheet = workBook.getActiveSheet(); let hasTheSheet = false; for (let sheetObj of sheets) { let sheet = sheetObj.sheet; if (sheet === activeSheet) { hasTheSheet = true; let editStarting = sheetObj.editStarting; let editEnded = sheetObj.editEnded; if (editStarting) { sheet.unbind(GC.Spread.Sheets.Events.EditStarting); } if (editEnded) { sheet.unbind(GC.Spread.Sheets.Events.EditEnded); } let row = sheet.getActiveRowIndex(); let col = sheet.getActiveColumnIndex(); let orgV = sheet.getValue(row, col); let orgText = sheet.getText(row, col); if (!isDef(orgV)) { orgV = ''; } if (sheet.isEditing()) { sheet.endEdit(); sheet.setValue(row, col, orgV); } if (editStarting) { sheet.bind(GC.Spread.Sheets.Events.EditStarting, editStarting); } if (editEnded) { sheet.bind(GC.Spread.Sheets.Events.EditEnded, editEnded); } } } //容错处理，以防没把所有工作簿的表格信息传入参数 if (!hasTheSheet) { if (activeSheet.isEditing()) { activeSheet.endEdit(); } } }); workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.esc, false, false, false, false); workBook.commandManager().setShortcutKey('myEsc', GC.Spread.Commands.Key.esc, false, false, false, false); }, //设置默认样式 spreadDefaultStyle: function (workBook) { let defaultStyle = new GC.Spread.Sheets.Style(); defaultStyle.font = '14px Calibri'; let sheetCount = workBook.getSheetCount(); for (let i = 0; i < sheetCount; i++) { let sheet = workBook.getSheet(i); sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport); sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.colHeader); sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.rowHeader); } }, //动态根据工作簿宽度和各列宽度比例设置宽度 setColumnWidthByRate: function (workBookWidth, workBook, headers) { if (workBook) { const sheet = workBook.getActiveSheet(); sheet.suspendEvent(); sheet.suspendPaint(); for (let col = 0; col < headers.length; col++) { if (headers[col]['rateWidth'] !== undefined && headers[col]['rateWidth'] !== null && headers[col]['rateWidth'] !== '') { sheet.setColumnWidth(col, workBookWidth * headers[col]['rateWidth'], GC.Spread.Sheets.SheetArea.colHeader) } else { if (headers[col]['headerWidth'] !== undefined && headers[col]['headerWidth'] !== null && headers[col]['headerWidth'] !== '') { sheet.setColumnWidth(col, headers[col]['headerWidth'], GC.Spread.Sheets.SheetArea.colHeader) } } } sheet.resumeEvent(); sheet.resumePaint(); } }, drowRect: function (ctx, x, y, w, h, rectW, rectH, margin) { ctx.save(); ctx.strokeStyle = "gray"; ctx.translate(0.5, 0.5); ctx.beginPath(); let rectX = x + margin; let rectY = y + Math.round(h / 2) - rectH / 2; ctx.moveTo(rectX, rectY); ctx.lineTo(rectX, rectY + rectH); ctx.lineTo(rectX + rectW, rectY + rectH); ctx.lineTo(rectX + rectW, rectY); ctx.lineTo(rectX, rectY); ctx.moveTo(rectX + rectW, y + Math.round(h / 2)); ctx.lineTo(rectX + rectW + 5, y + Math.round(h / 2)); ctx.stroke(); ctx.restore(); }, drawLine: function (ctx, x1, y1, x2, y2, color) { let l_color = color ? color : "gray"; ctx.save(); ctx.translate(0.5, 0.5); ctx.beginPath(); ctx.moveTo(x1, y1); ctx.lineTo(x2, y2); ctx.strokeStyle = l_color; ctx.stroke(); ctx.restore(); }, drowSymbol: function (ctx, x, y, w, h, rectW, rectH, margin, collapsed) { ctx.save(); ctx.strokeStyle = "#000000"; ctx.translate(0.5, 0.5); ctx.beginPath(); ctx.moveTo(x + margin + 2, y + Math.round(h / 2)); ctx.lineTo(x + margin + 8, y + Math.round(h / 2)); let rectY = y + Math.round(h / 2) - rectH / 2; if (collapsed) { ctx.moveTo(x + margin + rectW / 2, rectY + 2); ctx.lineTo(x + margin + rectW / 2, rectY + 2 + 6); } ctx.stroke(); ctx.restore(); }, drowTriangle: function (ctx, x, y) {//画向下三角形 ctx.save(); ctx.fillStyle = "black"; ctx.beginPath(); ctx.moveTo(x, y); ctx.lineTo(x - 3, y - 6); ctx.lineTo(x + 3, y - 6); ctx.fill(); ctx.restore(); }, drowSubItem: function (ctx, x, y, w, h, offset, hasNext, step) { let t_step = step ? step : 6; offset += t_step; ctx.save(); ctx.strokeStyle = "gray"; ctx.translate(0.5, 0.5); ctx.beginPath(); ctx.moveTo(x + offset, y); ctx.lineTo(x + offset, y + Math.round(h / 2)); offset += 9; ctx.lineTo(x + offset, y + Math.round(h / 2)); if (hasNext) { ctx.moveTo(x + offset - 9, y + Math.round(h / 2)); ctx.lineTo(x + offset - 9, y + h); } ctx.stroke(); ctx.restore(); return offset; }, checkData: function (col, setting, value) { let result = true; let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null; if (validator === null) { return result; } switch (validator) { case 'number': let regular = /^\d+(\.\d+)?$/; result = regular.test(value); break; case 'boolean': let booleanValue = [true, false]; result = booleanValue.indexOf(value) >= 0; break; } return result; }, getSelectedRecode: function (sheet, datas) { let sel = sheet.getSelections()[0]; let srow = sel.row == -1 || sel.row == "" ? 0 : sel.row; if (gljUtil.isDef(srow) && datas.length > srow) { return datas[srow]; } return null; }, // 延迟一段时间刷新表格，因为有的弹窗里面有表格，马上刷新可能会造成，弹窗界面还未完全显示完就完成了表格刷新，导致表格显示不完整 refreshWorkbookDelDefer(workbook, time) { if (workbook) { setTimeout(() => workbook.refresh(), time); } }, setRowsAutoFit(sheet, rows, col, wordWrap) { rows.forEach(row => { sheet.getCell(row, col).wordWrap(wordWrap); sheet.autoFitRow(row); }); } }