sheet_common.js 65 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454
  1. /**
  2. * Created by Tony on 2017/4/28.
  3. */
  4. var sheetCommonObj = {
  5. // CSL.2017.06.05
  6. // createSpread、initSheet 在一个Spread多个Sheet分别调用时的情况下使用。
  7. createSpread: function(container, SheetCount){
  8. var me = this;
  9. var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: SheetCount });
  10. spreadBook.options.tabStripVisible = false;
  11. spreadBook.options.showHorizontalScrollbar = true;
  12. spreadBook.options.showVerticalScrollbar = true;
  13. spreadBook.options.allowCopyPasteExcelStyle = false;
  14. spreadBook.options.allowUserDragDrop = true;
  15. spreadBook.options.allowContextMenu = false;
  16. spreadBook.options.allowUserEditFormula = false;
  17. spreadBook.options.showDragFillSmartTag = false;
  18. return spreadBook;
  19. },
  20. initSheet: function(sheet, setting, rowCount) {
  21. var me = this;
  22. var spreadNS = GC.Spread.Sheets;
  23. sheet.suspendPaint();
  24. sheet.suspendEvent();
  25. if(setting.frozenCols) sheet.frozenColumnCount(setting.frozenCols);//冻结列s
  26. sheet.setRowCount(setting.headRows ? setting.headRows : 1, spreadNS.SheetArea.colHeader);
  27. sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
  28. if (setting && setting.view && setting.view.colHeaderHeight) {
  29. sheet.setRowHeight(0, setting.view.colHeaderHeight, spreadNS.SheetArea.colHeader);
  30. };
  31. if (setting && setting.view && setting.view.rowHeaderWidth) {
  32. sheet.setColumnWidth(0, setting.view.rowHeaderWidth, spreadNS.SheetArea.rowHeader);
  33. };
  34. if (setting.emptyRowHeader) {
  35. sheet.setColumnWidth(0, 1, GC.Spread.Sheets.SheetArea.rowHeader);
  36. }
  37. sheet.options.colHeaderAutoTextIndex = 1;
  38. sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
  39. sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;
  40. sheet.options.protectionOptions = {
  41. allowResizeColumns: true
  42. };
  43. sheet.showRowOutline(false);
  44. sheet.options.allowCellOverflow = false;
  45. me.buildHeader(sheet, setting);
  46. if (rowCount > 0)
  47. sheet.setRowCount(rowCount);
  48. else
  49. sheet.setRowCount(1);
  50. sheet.resumeEvent();
  51. sheet.resumePaint();
  52. },
  53. // buildSheet 在一个Spread、一个Sheet的情况下使用。
  54. buildSheet: function(container, setting, rowCount) {
  55. var me = this;
  56. var spreadBook = me.createSpread(container, { sheetCount: 1 });
  57. var sheet = spreadBook.getSheet(0);
  58. me.initSheet(sheet, setting, rowCount);
  59. return spreadBook;
  60. },
  61. buildHeader: function(sheet, setting){
  62. var me = this, ch = GC.Spread.Sheets.SheetArea.colHeader;
  63. for (var i = 0; i < setting.header.length; i++) {
  64. sheet.setValue(0, i, setting.header[i].headerName, ch);
  65. sheet.getCell(0, i, ch).wordWrap(true);
  66. sheet.setColumnWidth(i, setting.header[i].headerWidth?setting.header[i].headerWidth:100);
  67. sheet.setColumnVisible(i,setting.header[i].visible === false ? false:true);
  68. }
  69. },
  70. getHeadersFromTreeSetting: function (treeSetting) {
  71. const vAlignMap = ['top', 'center', 'bottom'];
  72. const hAlignMap = ['left', 'center', 'right'];
  73. return treeSetting.cols.map(item => {
  74. const vAlign = vAlignMap[item.data.vAlign];
  75. const hAlign = hAlignMap[item.data.hAlign];
  76. return {
  77. name: item.head.titleNames[0],
  78. dataCode: item.data.field,
  79. width: item.width,
  80. vAlign,
  81. hAlign,
  82. };
  83. });
  84. },
  85. setHeader: function(sheet, headers) {
  86. sheet.suspendPaint();
  87. sheet.suspendEvent();
  88. sheet.setColumnCount(headers.length);
  89. sheet.setRowHeight(0, 30, GC.Spread.Sheets.SheetArea.colHeader);
  90. headers.forEach((header, index) => {
  91. sheet.setValue(0, index, header.name, GC.Spread.Sheets.SheetArea.colHeader);
  92. sheet.setColumnWidth(index, header.width, GC.Spread.Sheets.SheetArea.colHeader);
  93. if (header.formatter) {
  94. sheet.setFormatter(-1, index, header.formatter);
  95. }
  96. sheet.getRange(-1, index, -1, 1).hAlign(GC.Spread.Sheets.HorizontalAlign[header.hAlign]);
  97. sheet.getRange(-1, index, -1, 1).vAlign(GC.Spread.Sheets.VerticalAlign[header.vAlign]);
  98. });
  99. sheet.resumeEvent();
  100. sheet.resumePaint();
  101. },
  102. cleanSheet: function(sheet, setting, rowCount) {
  103. sheet.suspendPaint();
  104. sheet.suspendEvent();
  105. sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
  106. if (rowCount > 0) sheet.setRowCount(rowCount);
  107. sheet.clearSelection();
  108. sheet.resumeEvent();
  109. sheet.resumePaint();
  110. },
  111. cleanData: function (sheet, setting, rowCount) {
  112. sheet.suspendPaint();
  113. sheet.suspendEvent();
  114. sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
  115. if (rowCount > 0) sheet.setRowCount(rowCount);
  116. sheet.resumeEvent();
  117. sheet.resumePaint();
  118. },
  119. setAreaAlign: function(area, hAlign, vAlign){
  120. if (!(hAlign) || hAlign === "left") {
  121. area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
  122. } else if (hAlign === "right") {
  123. area.hAlign(GC.Spread.Sheets.HorizontalAlign.right);
  124. } else if (hAlign === "center") {
  125. area.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
  126. } else {
  127. area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
  128. }
  129. if (!(vAlign) || vAlign === "center") {
  130. area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
  131. } else if (vAlign === "top") {
  132. area.vAlign(GC.Spread.Sheets.VerticalAlign.top);
  133. } else if (vAlign === "bottom") {
  134. area.vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
  135. } else {
  136. area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
  137. }
  138. },
  139. showTreeData:function (sheet,setting,data) {
  140. let ch = GC.Spread.Sheets.SheetArea.viewport;
  141. let parentMap=_.groupBy(data, 'ParentID');
  142. let visibleMap = {};
  143. let styleRow=[];
  144. sheet.suspendPaint();
  145. sheet.suspendEvent();
  146. for (let col = 0; col < setting.header.length; col++) {
  147. let hAlign = "left", vAlign = "center";
  148. if (setting.header[col].hAlign) {
  149. hAlign = setting.header[col].hAlign;
  150. } else if (setting.header[col].dataType !== "String"){
  151. hAlign = "right";
  152. }
  153. vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
  154. sheetCommonObj.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
  155. if (setting.header[col].formatter) {
  156. sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
  157. }
  158. if(setting.header[col].cellType === "comboBox"){
  159. this.setComboBox(-1,col,sheet,setting.header[col].options,setting.header[col].editorValueType,setting.header[col].editable,setting.header[col].maxDropDownItems);
  160. }
  161. for (let row = 0; row < data.length; row++) {
  162. if(data[row].cellType === 'comboBox'){
  163. let options = data[row].options ? data[row].options.split("@") : [];
  164. this.setComboBox(row,col,sheet,options);
  165. }else if(data[row].cellType === 'String'){//默认设置字符输入,避免出现输入10:01变成日期的情况
  166. sheet.setFormatter(row, col,"@", GC.Spread.Sheets.SheetArea.viewport);
  167. }
  168. let val = data[row][setting.header[col].dataCode];
  169. if(val&&setting.header[col].dataType === "Number"){
  170. if(setting.header[col].hasOwnProperty('decimalField')){
  171. let decimal = getDecimal(setting.header[col].decimalField);
  172. val =scMathUtil.roundToString(val,decimal);
  173. sheet.setFormatter(row, col,getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport);
  174. }else {
  175. val =scMathUtil.roundToString(val,2);
  176. }
  177. }
  178. sheet.setValue(row, col, val, ch);
  179. if(col==0){
  180. let treeType = sheetCommonObj.getTreeNodeCellType(data,row,parentMap);
  181. sheet.getCell(row, 0).cellType(treeType);
  182. visibleMap[data[row].ID] = treeType.collapsed;
  183. this.setRowVisible(data,row,visibleMap,sheet);
  184. }
  185. if(data[row].bgColour) styleRow.push(row)
  186. }
  187. }
  188. for(let r of styleRow){
  189. this.setRowStyle(r,sheet,data[r].bgColour);
  190. }
  191. this.lockCells(sheet,setting);
  192. sheet.resumeEvent();
  193. sheet.resumePaint();
  194. },
  195. setRowVisible:function (data,row,visibleMap,sheet) {
  196. sheet.getRange(row , -1, 1, -1).visible(getVisible(data[row].ParentID));//显示或隐藏
  197. function getVisible(ParentID) {
  198. if(visibleMap[ParentID]) return false //如果父节点是缩起的,那就隐藏本身。
  199. if(visibleMap[ParentID] == false){//如果父节点不是缩起的,要再往父节点找看
  200. let pnode = _.find(data,{'ID':ParentID});
  201. if(pnode) return getVisible(pnode.ParentID);//如果有父节点,递归调用
  202. return true;//没有,返回显示
  203. }
  204. }
  205. },
  206. setSheetBySetting: function (sheet, setting) {
  207. function setCellType(range, cellType) {
  208. let type;
  209. if (cellType === 'checkBox') {
  210. type = new GC.Spread.Sheets.CellTypes.CheckBox();
  211. }
  212. range.cellType(type);
  213. }
  214. setting.header.forEach((item, index) => {
  215. const range = sheet.getRange(-1, index, -1, 1);
  216. this.setAreaAlign(range, item.hAlign, item.vAlign);
  217. if (item.cellType) {
  218. console.log(item);
  219. setCellType(range, item.cellType);
  220. }
  221. if (item.formatter) {
  222. range.formatter(item.formatter)
  223. }
  224. });
  225. },
  226. appendData: function (sheet, index, rowIndex, setting, data) {
  227. /*const viewport = GC.Spread.Sheets.SheetArea.viewport;
  228. const rowHeader = GC.Spread.Sheets.SheetArea.rowHeader;*/
  229. if (!index) {
  230. sheet.setRowCount(0);
  231. }
  232. sheet.suspendPaint();
  233. sheet.suspendEvent();
  234. // 追加空行
  235. sheet.addRows(index, data.length);
  236. // 显示数据
  237. data.forEach((item, rIdx) => {
  238. const row = index + rIdx;
  239. setting.header.forEach((hItem, cIdx) => {
  240. const dataCode = hItem.dataCode;
  241. const val = gljUtil.isDef(item[dataCode]) ? item[dataCode] : '';
  242. sheet.setValue(row, cIdx, val);
  243. });
  244. });
  245. sheet.resumeEvent();
  246. sheet.resumePaint();
  247. },
  248. showData: function(sheet, setting, data,distTypeTree,callback) {
  249. var me = this, ch = GC.Spread.Sheets.SheetArea.viewport;
  250. sheet.suspendPaint();
  251. sheet.suspendEvent();
  252. //sheet.addRows(row, 1);
  253. sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
  254. if(sheet.getRowCount()<data.length){
  255. data.length<30? sheet.setRowCount(30):sheet.setRowCount(data.length);
  256. }else if(sheet.getRowCount()==0){
  257. sheet.setRowCount(30);
  258. }
  259. for (var col = 0; col < setting.header.length; col++) {
  260. var hAlign = "left", vAlign = "center";
  261. if (setting.header[col].hAlign) {
  262. hAlign = setting.header[col].hAlign;
  263. } else if (setting.header[col].dataType !== "String"){
  264. hAlign = "right";
  265. }
  266. vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
  267. me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
  268. if (setting.header[col].formatter) {
  269. sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
  270. }
  271. if(setting.header[col].cellType === "checkBox"||setting.header[col].cellType === "button"){//clear and reset
  272. var me = this, header = GC.Spread.Sheets.SheetArea.colHeader;
  273. sheet.deleteColumns(col,1);
  274. sheet.addColumns(col, 1);
  275. sheet.setValue(0, col, setting.header[col].headerName, header);
  276. sheet.setColumnWidth(col, setting.header[col].headerWidth?setting.header[col].headerWidth:100);
  277. }
  278. if(setting.header[col].visible!==null&&setting.header[col].visible!==undefined){
  279. sheet.setColumnVisible(col,setting.header[col].visible);
  280. }
  281. sheet.getCell(0, col, GC.Spread.Sheets.SheetArea.colHeader).wordWrap(true);
  282. }
  283. for (var row = 0; row < data.length; row++) {
  284. //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport);
  285. this.showRowData(sheet,setting,row,data,distTypeTree);
  286. if(setting.getStyle && setting.getStyle(data[row])){
  287. sheet.setStyle(row, -1, setting.getStyle(data[row]));
  288. }
  289. }
  290. if(setting.emptyRowHeader){
  291. let rowCount = sheet.getRowCount();
  292. for (let row = 0; row < rowCount; row++) {
  293. sheet.setValue(row, 0, '', GC.Spread.Sheets.SheetArea.rowHeader);
  294. }
  295. }
  296. this.lockCells(sheet,setting);
  297. if(callback) callback();
  298. sheet.resumeEvent();
  299. sheet.resumePaint();
  300. //me.shieldAllCells(sheet);
  301. },
  302. showRowData:function (sheet,setting,row,data,distTypeTree=null) {
  303. let ch = GC.Spread.Sheets.SheetArea.viewport;
  304. if(data[row].cellType == "date"){//日期格式比较特殊,要判断数据里的属性,为data类型,同时读取列
  305. this.setDatePickerCellType(row,data[row].dateCol,sheet)
  306. }
  307. if(data[row].cellType === 'comboBox'){//对于选项在数据里下拉框类型,同时读取列
  308. let options = data[row].options ? data[row].options.split("@") : [];
  309. this.setComboBox(row,data[row].dateCol,sheet,options);
  310. }
  311. for (var col = 0; col < setting.header.length; col++) {
  312. //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport);
  313. var val = data[row][setting.header[col].dataCode];
  314. if(val&&setting.header[col].dataType === "Number"){
  315. if(setting.header[col].hasOwnProperty('tofix')){
  316. val =scMathUtil.roundToString(val,setting.header[col].tofix);
  317. }
  318. else if(setting.header[col].hasOwnProperty('decimalField')){
  319. var decimal = getDecimal(setting.header[col].decimalField);
  320. val =scMathUtil.roundToString(val,decimal);
  321. sheet.setFormatter(-1, col,getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport);
  322. }else {
  323. val =val+'';
  324. }
  325. }
  326. if(val!=null&&setting.header[col].cellType === "checkBox"){
  327. this.setCheckBoxCell(row,col,sheet,val)
  328. }
  329. if(setting.header[col].cellType === "comboBox"){
  330. this.setComboBox(row,col,sheet,setting.header[col].options,setting.header[col].editorValueType,setting.header[col].editable,setting.header[col].maxDropDownItems);
  331. }
  332. if(setting.header[col].cellType === "selectButton"){
  333. this.setSelectButton(row,col,sheet,setting.header[col]);
  334. }
  335. if(setting.header[col].cellType === "replaceButton"){
  336. this.setReplaceButton(row,col,sheet,setting.header[col]);
  337. }
  338. if(setting.header[col].cellType === "cusButton"){
  339. this.setCusButton(row,col,sheet,setting);
  340. }
  341. if(setting.header[col].cellType === "tipsCell"){
  342. this.setTipsCell(row,col,sheet,setting.header[col]);
  343. }
  344. if(setting.owner==='gljTree'){
  345. if(setting.header[col].cellType === "checkBox"){
  346. val==1?val:0;
  347. this.setCheckBoxCell(row,col,sheet,val);
  348. }
  349. if(setting.header[col].dataCode === 'gljType' && data[row].gljType){
  350. let distTypeVal = distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName;
  351. val=distTypeVal;
  352. }
  353. }
  354. if(setting.header[col].getText){
  355. val = setting.getText[setting.header[col].getText](data[row],val)
  356. }
  357. sheet.setValue(row, col, val, ch);
  358. }
  359. this.setRowStyle(row,sheet,data[row].bgColour);
  360. if(setting.autoFit==true){//设置自动行高
  361. if(setting.fitRow && setting.fitRow.length > 0){//如果有设置特定的某些列才需要自动行高就按设置的来,没有设置就默认所有列
  362. for(let dataCode of setting.fitRow){
  363. let col = _.findIndex(setting.header,{dataCode:dataCode})
  364. sheet.getCell(row,col).wordWrap(true);
  365. }
  366. }else {
  367. sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).wordWrap(true);
  368. }
  369. sheet.autoFitRow(row);
  370. }
  371. },
  372. checkData : function(col,setting, value) {
  373. let result = true;
  374. let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null;
  375. if (validator === null) {
  376. return result;
  377. }
  378. switch (validator) {
  379. case 'number':
  380. let regular = /^\d+(\.\d+)?$/;
  381. result = regular.test(value);
  382. break;
  383. case 'boolean':
  384. let booleanValue = [true, false];
  385. result = booleanValue.indexOf(value) >= 0;
  386. break;
  387. }
  388. return result;
  389. },
  390. //todo
  391. analyzePasteData: function(setting, pastedInfo) {
  392. var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong
  393. for (var i = 0; i < pastedInfo.pasteData.text.length; i++) {
  394. if (pastedInfo.pasteData.text[i] === "\n") {
  395. propId = pastedInfo.cellRange.col;//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong
  396. preStrIdx = i + 1;
  397. rst.push(itemObj);
  398. if (i < pastedInfo.pasteData.text.length - 1) {
  399. itemObj = {};
  400. }
  401. } else if (pastedInfo.pasteData.text[i] === "\t" || pastedInfo.pasteData.text[i] === "\r") {
  402. if (setting.header[propId]) {
  403. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx, i);
  404. }
  405. propId++;
  406. preStrIdx = i + 1;
  407. //if the last copied-cell were empty, should check whether the end of text
  408. if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) {
  409. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
  410. rst.push(itemObj);
  411. }
  412. } else if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) {
  413. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
  414. rst.push(itemObj);
  415. }
  416. }
  417. return rst;
  418. },
  419. combineRowData: function(sheet, setting, row) {
  420. var rst = {};
  421. for (var col = 0; col < setting.header.length; col++) {
  422. rst[setting.header[col].dataCode] = sheet.getValue(row, col);
  423. }
  424. return rst;
  425. },
  426. shieldAllCells: function(sheet) {
  427. sheet.options.isProtected = true;
  428. },
  429. unShieldAllCells: function(sheet) {
  430. sheet.options.isProtected = false;
  431. },
  432. lockCells: function(sheet, setting){
  433. if (setting && setting.view.lockColumns && setting.view.lockColumns.length > 0) {
  434. sheet.options.isProtected = true;
  435. sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  436. for (var i = 0; i < setting.view.lockColumns.length; i++) {
  437. let col = setting.view.lockColumns[i];
  438. if(_.isString(col)){//如果是dataCode 进行转换
  439. col = _.findIndex(setting.header,{dataCode:col})
  440. }
  441. sheet.getRange(-1,col, -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
  442. }
  443. }
  444. if (setting && Array.isArray(setting.view.lockRows) && setting.view.lockRows.length) {
  445. if (!setting.view.lockColumns || !setting.view.lockColumns.length) {
  446. sheet.options.isProtected = true;
  447. sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  448. }
  449. setting.view.lockRows.forEach(row => {
  450. sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
  451. });
  452. }
  453. },
  454. setCheckBoxCell(row,col,sheet,val){
  455. var c = this.getCheckBox();
  456. sheet.setCellType(row, col,c,GC.Spread.Sheets.SheetArea.viewport);
  457. sheet.getCell(row, col).value(val);
  458. sheet.getCell(row, col).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
  459. },
  460. getCheckBox(threeState = false){
  461. var c = new GC.Spread.Sheets.CellTypes.CheckBox();
  462. c.isThreeState(threeState);
  463. return c
  464. },
  465. // 无法勾选的复选框
  466. getReadOnlyCheckBox () {
  467. function ReadOnlyCheckBox() {}
  468. ReadOnlyCheckBox.prototype = new GC.Spread.Sheets.CellTypes.CheckBox();
  469. ReadOnlyCheckBox.prototype.processMouseUp = function () {
  470. return;
  471. };
  472. return new ReadOnlyCheckBox();
  473. },
  474. setComboBox(row,col,sheet,options,editorValueType,editable,maxDropDownItems){
  475. //let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
  476. let dynamicCombo = sheetCommonObj.getDynamicCombo(true);
  477. if(options){
  478. dynamicCombo.items(options);
  479. if(maxDropDownItems) dynamicCombo.maxDropDownItems(maxDropDownItems);
  480. if(editable == true) dynamicCombo.editable(true);//可编辑
  481. if(editorValueType==true){
  482. dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
  483. }
  484. }
  485. sheet.setCellType(row, col,dynamicCombo,GC.Spread.Sheets.SheetArea.viewport);
  486. },
  487. setRowStyle(row,sheet,bgColour) {
  488. if(bgColour){
  489. let style = new GC.Spread.Sheets.Style();
  490. style.backColor = bgColour;
  491. style.borderLeft = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
  492. style.borderTop = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
  493. style.borderRight = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
  494. style.borderBottom = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
  495. sheet.setStyle(row, -1, style);
  496. }
  497. },
  498. getCustomerCoeCellType: function (htmlGenerator,setEditorValue,updateCallback) {
  499. let me = this;
  500. function CustomerCoeCellType() {
  501. this.isEscKey=false;
  502. this.displayText='';
  503. }
  504. CustomerCoeCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();
  505. CustomerCoeCellType.prototype.createEditorElement = function (context) {
  506. console.log("create editor")
  507. let element = document.createElement("div");//这里创建的,会自动销毁
  508. return element
  509. };
  510. CustomerCoeCellType.prototype.activateEditor = function (editorContext, cellStyle, cellRect, context) {
  511. if (editorContext) {
  512. $editor = $(editorContext);
  513. $editor.css("position", "fixed");
  514. $editor.css("background", "white");
  515. //$editor.css("width", cellRect.width); 2018-11-15 改成固定列宽
  516. $editor.css("width", 160);
  517. $editor.attr("gcUIElement", "gcEditingInput");
  518. if(htmlGenerator) htmlGenerator(context,cellRect,$editor);
  519. }
  520. }
  521. CustomerCoeCellType.prototype.deactivateEditor = function (editorContext, context) {
  522. };
  523. CustomerCoeCellType.prototype.setEditorValue = function (editor, value, context) {
  524. console.log("set editor value");
  525. this.displayText = value;
  526. };
  527. CustomerCoeCellType.prototype.getEditorValue = function (editor, context) {
  528. console.log("get value");
  529. if(this.isEscKey !=true&& updateCallback){
  530. updateCallback();
  531. }
  532. this.isEscKey = false;
  533. return this.displayText;
  534. };
  535. CustomerCoeCellType.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) {
  536. console.log(" update editor");
  537. if( setEditorValue){//不是esc时才更新
  538. setEditorValue(context);
  539. }
  540. };
  541. CustomerCoeCellType.prototype.isReservedKey = function (e, context) {
  542. //cell type handle tab key by itself
  543. this.isEscKey = e.keyCode === GC.Spread.Commands.Key.esc;
  544. return false;
  545. };
  546. return new CustomerCoeCellType();
  547. },
  548. scrollSheetForOption:function (sheet,cxt,cellRect,row,options){
  549. let topRow = sheet.getViewportTopRow(1);
  550. if(row == topRow) return;//已经是最顶行了
  551. let length = options&&options.length>0?options.length:1;
  552. let height = cxt.canvas.height;
  553. let startY = cellRect.y+cellRect.height;//下拉框的起始显示位置
  554. let endY = startY + length * cellRect.height;//下拉框的结束显示位置
  555. if(endY <= height) return; //如果没有超出显示位置,直接返回
  556. let overRow = Math.ceil((endY - height)/cellRect.height);//超出的行数
  557. let showRow = topRow + overRow > row?row:topRow + overRow;
  558. sheet.showRow(showRow, GC.Spread.Sheets.VerticalPosition.top);
  559. },
  560. setSelectButton(row,col,sheet,header){
  561. /* let getSelectButton = function (cellWidth=100) {
  562. function moreButton() {
  563. }
  564. moreButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
  565. moreButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
  566. GC.Spread.Sheets.CellTypes.Button.prototype.paint.call(this, ctx, value, x, y, w, h, style, options);
  567. let buttonW = cellWidth/5;
  568. let endX = x+w-2;
  569. if(value){
  570. let textWidth = ctx.measureText(value).width;
  571. let spaceWidth = cellWidth - buttonW;
  572. let textEndX = x+2+textWidth;
  573. if(spaceWidth<textWidth){
  574. for(let i = value.length-1;i>1;i--){
  575. let newValue = value.substr(0,i);
  576. let newTestWidth = ctx.measureText(newValue).width;
  577. if(spaceWidth>newTestWidth){
  578. value = newValue;
  579. textEndX = x+2+newTestWidth;
  580. break;
  581. }
  582. }
  583. }
  584. ctx.fillText(value,textEndX,y+h-5);
  585. }
  586. //画三个点
  587. ctx.save();
  588. ctx.beginPath();
  589. ctx.arc(endX-buttonW/2,y+h/2,1,0,360,false);
  590. ctx.arc(endX-buttonW/2-4,y+h/2,1,0,360,false);
  591. ctx.arc(endX-buttonW/2+4,y+h/2,1,0,360,false);
  592. ctx.fillStyle="black";//填充颜色,默认是黑色
  593. ctx.fill();//画实心圆
  594. ctx.closePath();
  595. ctx.restore();
  596. };
  597. moreButton.prototype.processMouseLeave= function (hitinfo) {
  598. let newCell = new selectButton();
  599. hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, newCell, GC.Spread.Sheets.SheetArea.viewport);
  600. hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(false);
  601. };
  602. function selectButton() {
  603. }
  604. selectButton.prototype = new GC.Spread.Sheets.CellTypes.Text();
  605. selectButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
  606. GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this,arguments);
  607. };
  608. selectButton.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
  609. return {
  610. x: x,
  611. y: y,
  612. row: context.row,
  613. col: context.col,
  614. cellStyle: cellStyle,
  615. cellRect: cellRect,
  616. sheetArea: context.sheetArea
  617. };
  618. };
  619. selectButton.prototype.processMouseDown = function (hitinfo){
  620. if(hitinfo.sheet.getCell(hitinfo.row,hitinfo.col).locked()!=true){
  621. let b1 = new moreButton();
  622. b1.marginLeft(cellWidth*4/5);
  623. hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, b1, GC.Spread.Sheets.SheetArea.viewport);
  624. hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(true);
  625. }
  626. };
  627. return new selectButton();
  628. };*/
  629. sheet.setCellType(row, col,this.getSelectButton(header.headerWidth),GC.Spread.Sheets.SheetArea.viewport);
  630. },
  631. setCusButton:function (row,col,sheet,setting) {
  632. let functionName = setting.header[col].callback;
  633. let readOnly = setting.disable[setting.header[col].disable];
  634. if(typeof(readOnly) == 'function' ){
  635. readOnly = readOnly(row,col);
  636. }
  637. if(functionName){
  638. sheet.setCellType(row, col,this.getCusButtonCellType(setting.callback[functionName],readOnly));
  639. }
  640. //sheet.setCellType(row, col,this.getSelectButton(header.headerWidth),GC.Spread.Sheets.SheetArea.viewport);
  641. },
  642. getCusButtonCellType:function (callback,readOnly=false) {
  643. var ns = GC.Spread.Sheets;
  644. function CusButtonCellType() {
  645. }
  646. CusButtonCellType.prototype = new ns.CellTypes.Text();
  647. CusButtonCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) {
  648. if(!readOnly){
  649. if(options.sheet.getActiveRowIndex()==options.row&&options.sheet.getActiveColumnIndex()==options.col){
  650. var image = document.getElementById('f_btn'),imageMagin = 3;
  651. var imageHeight = 15;
  652. var imageWidth = 25;
  653. var imageX = x + w - imageWidth- imageMagin, imageY = y + h / 2 - imageHeight / 2;
  654. ctx.save();
  655. if(style.backColor){
  656. ctx.fillStyle = style.backColor;
  657. ctx.fillRect(x,y,w,h);
  658. }
  659. ctx.drawImage(image, imageX, imageY,imageWidth,imageHeight);
  660. ctx.beginPath();
  661. ctx.arc(imageX+imageWidth/2,imageY+imageHeight/2,1,0,360,false);
  662. ctx.arc(imageX+imageWidth/2-4,imageY+imageHeight/2,1,0,360,false);
  663. ctx.arc(imageX+imageWidth/2+4,imageY+imageHeight/2,1,0,360,false);
  664. ctx.fillStyle="black";//填充颜色,默认是黑色
  665. ctx.fill();//画实心圆
  666. ctx.closePath();
  667. ctx.restore();
  668. w = w - imageWidth - imageMagin;
  669. //这里的左对齐的,当显示的字长度超过空白地方时,要改成右对齐
  670. if(style.hAlign == 0){
  671. if(value){
  672. let textWidth = ctx.measureText(value).width;
  673. let spaceWidth = w;
  674. if(spaceWidth<textWidth){
  675. style.hAlign = 2;
  676. }
  677. }
  678. }
  679. }
  680. }
  681. GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments);
  682. };
  683. CusButtonCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
  684. return {
  685. x: x,
  686. y: y,
  687. row: context.row,
  688. col: context.col,
  689. cellStyle: cellStyle,
  690. cellRect: cellRect,
  691. sheetArea: context.sheetArea
  692. };
  693. };
  694. CusButtonCellType.prototype.processMouseDown = function (hitinfo) {
  695. if(hitinfo.sheet.getActiveRowIndex()==hitinfo.row&&hitinfo.sheet.getActiveColumnIndex()==hitinfo.col){
  696. var offset=hitinfo.cellRect.x+hitinfo.cellRect.width-6;
  697. var imageWidth = 25;
  698. if(hitinfo.x<offset&&hitinfo.x>offset-imageWidth){
  699. if(!readOnly){
  700. if(callback) callback(hitinfo)
  701. }
  702. }
  703. }
  704. };
  705. return new CusButtonCellType();
  706. },
  707. getSelectButton(cellWidth=100){
  708. function moreButton() {
  709. }
  710. moreButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
  711. moreButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
  712. GC.Spread.Sheets.CellTypes.Button.prototype.paint.call(this, ctx, value, x, y, w, h, style, options);
  713. ctx.font = '14px Calibri';
  714. let buttonW = cellWidth/5;
  715. let endX = x+w-2;
  716. if(value){
  717. let textWidth = ctx.measureText(value).width;
  718. let spaceWidth = cellWidth - buttonW;
  719. let textEndX = x+textWidth+2;
  720. if(spaceWidth<textWidth){
  721. for(let i = value.length-1;i>1;i--){
  722. let newValue = value.substr(0,i);
  723. let newTestWidth = ctx.measureText(newValue).width;
  724. if(spaceWidth>newTestWidth){
  725. value = newValue;
  726. textEndX = x+newTestWidth+2;
  727. break;
  728. }
  729. }
  730. }
  731. ctx.fillText(value,textEndX,y+h-6);
  732. }
  733. //画三个点
  734. ctx.save();
  735. ctx.beginPath();
  736. ctx.arc(endX-buttonW/2,y+h/2,1,0,360,false);
  737. ctx.arc(endX-buttonW/2-4,y+h/2,1,0,360,false);
  738. ctx.arc(endX-buttonW/2+4,y+h/2,1,0,360,false);
  739. ctx.fillStyle="black";//填充颜色,默认是黑色
  740. ctx.fill();//画实心圆
  741. ctx.closePath();
  742. ctx.restore();
  743. };
  744. moreButton.prototype.processMouseLeave= function (hitinfo) {
  745. let newCell = new selectButton();
  746. hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, newCell, GC.Spread.Sheets.SheetArea.viewport);
  747. hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(false);
  748. };
  749. function selectButton() {
  750. }
  751. selectButton.prototype = new GC.Spread.Sheets.CellTypes.Text();
  752. selectButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
  753. GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this,arguments);
  754. };
  755. selectButton.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
  756. return {
  757. x: x,
  758. y: y,
  759. row: context.row,
  760. col: context.col,
  761. cellStyle: cellStyle,
  762. cellRect: cellRect,
  763. sheetArea: context.sheetArea
  764. };
  765. };
  766. selectButton.prototype.processMouseDown = function (hitinfo){
  767. if(hitinfo.sheet.getCell(hitinfo.row,hitinfo.col).locked()!=true){
  768. let b1 = new moreButton();
  769. b1.marginLeft(cellWidth*4/5);
  770. hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, b1, GC.Spread.Sheets.SheetArea.viewport);
  771. hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(true);
  772. }
  773. };
  774. return new selectButton();
  775. },
  776. setReplaceButton(row,col,sheet){
  777. let replaceButton = function(){
  778. };
  779. replaceButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
  780. replaceButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
  781. GC.Spread.Sheets.CellTypes.Button.prototype.paint.apply(this,arguments);
  782. if(value){
  783. ctx.save();
  784. ctx.fillStyle = "white";
  785. let fh = options.fontInfo&&options.fontInfo.fontSize?options.fontInfo.fontSize:h-6;
  786. ctx.fillText(value,x+(w+ctx.measureText(value).width)/2,y+(h+fh)/2-2);
  787. ctx.restore();
  788. }
  789. };
  790. let cellType = new replaceButton();
  791. cellType.buttonBackColor("#07A0FF");
  792. sheet.setCellType(row, col,cellType,GC.Spread.Sheets.SheetArea.viewport);
  793. },
  794. setDatePickerCellType(row,col,sheet){
  795. let ns = GC.Spread.Sheets;
  796. function DatePickerCellType() {
  797. }
  798. DatePickerCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();
  799. DatePickerCellType.prototype.createEditorElement = function (context) {
  800. //Create input presenter.
  801. return document.createElement("input");
  802. };
  803. DatePickerCellType.prototype.activateEditor = function (editorContext, cellStyle, cellRect, context) {
  804. //Initialize input editor.
  805. if (editorContext) {
  806. $editor = $(editorContext);
  807. //DatePickerCellType.prototype.activateEditor.apply(this, arguments);
  808. $editor.datepicker({dateFormat: 'yy-mm-dd'});
  809. $editor.css("position", "absolute");
  810. $editor.attr("gcUIElement", "gcEditingInput");
  811. $(".ui-datepicker").attr("gcUIElement", "gcEditingInput");
  812. }
  813. }
  814. DatePickerCellType.prototype.deactivateEditor = function (editorContext, context) {
  815. //Remove input editor when end editor status.
  816. if (editorContext) {
  817. var element = editorContext;
  818. $(element).datepicker("hide");
  819. $(element).datepicker("destroy");
  820. }
  821. // DatePickerCellType.prototype.deactivateEditor.apply(this, arguments)
  822. };
  823. DatePickerCellType.prototype.setEditorValue = function (editor, value, context) {
  824. //Sync value from Cell value to editor value.
  825. $(editor).datepicker("setDate", value);
  826. };
  827. DatePickerCellType.prototype.getEditorValue = function (editor, context) {
  828. //Sync value from editor value to cell value.
  829. return $(editor).datepicker("getDate");
  830. };
  831. DatePickerCellType.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) {
  832. if (editorContext) {
  833. $editor = $(editorContext);
  834. $editor.css("width", cellRect.width - 1);
  835. $editor.css("height", cellRect.height - 3);
  836. }
  837. };
  838. let picker = new DatePickerCellType();
  839. sheet.getCell(row, col).cellType(picker).width(100).formatter('yyyy-mm-dd');
  840. },
  841. setTipsCell(row,col,sheet,header){
  842. let TipCellType = function () {};
  843. TipCellType.prototype = new GC.Spread.Sheets.CellTypes.Text();
  844. TipCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
  845. return {
  846. x: x,
  847. y: y,
  848. row: context.row,
  849. col: context.col,
  850. cellStyle: cellStyle,
  851. cellRect: cellRect,
  852. sheet: context.sheet,
  853. sheetArea: context.sheetArea
  854. };
  855. };
  856. TipCellType.prototype.processMouseEnter = function (hitinfo) {
  857. let text = hitinfo.sheet.getText(hitinfo.row, hitinfo.col);
  858. let value = hitinfo.sheet.getValue(hitinfo.row, hitinfo.col);
  859. let tag = hitinfo.sheet.getTag(hitinfo.row, hitinfo.col);
  860. let acStyle = hitinfo.sheet.getActualStyle(hitinfo.row, hitinfo.col),
  861. zoom = hitinfo.sheet.zoom();
  862. let textLength = this.getAutoFitWidth(value, text, acStyle, zoom, {sheet: hitinfo.sheet, row: hitinfo.row, col: hitinfo.col, sheetArea: GC.Spread.Sheets.SheetArea.viewport});
  863. let cellWidth = hitinfo.sheet.getCell(-1, hitinfo.col).width();
  864. let setting = {};
  865. if(textLength <= cellWidth){
  866. return;
  867. }
  868. if(sheet && sheet.getParent().qo){
  869. setting.pos = SheetDataHelper.getObjPos(sheet.getParent().qo);
  870. }
  871. TREE_SHEET_HELPER.showTipsDiv(text,setting,hitinfo);
  872. };
  873. TipCellType.prototype.processMouseLeave = function (hitinfo) {
  874. TREE_SHEET_HELPER.tipDiv = 'hide';
  875. if (TREE_SHEET_HELPER._toolTipElement) {
  876. $(TREE_SHEET_HELPER._toolTipElement).hide();
  877. TREE_SHEET_HELPER._toolTipElement = null;
  878. };
  879. TREE_SHEET_HELPER.tipDivCheck();//延时检查:当tips正在show的时候,就调用了hide方法,会导致tips一直存在,所以设置一个超时处理
  880. };
  881. sheet.setCellType(row, col,new TipCellType(),GC.Spread.Sheets.SheetArea.viewport);
  882. },
  883. chkIfEmpty: function(rObj, setting) {
  884. var rst = true;
  885. if (rObj) {
  886. for (var i = 0; i < setting.header.length; i++) {
  887. if (rObj[setting.header[i].dataCode]) {
  888. rst = false;
  889. break;
  890. }
  891. }
  892. }
  893. return rst;
  894. },
  895. //add by zhong 2017-10-10
  896. //动态下拉框,配合EnterCell, args.sheet.repaint();
  897. getDynamicCombo: function (forLocked) {
  898. let ComboCellForActiveCell = function () { };
  899. ComboCellForActiveCell.prototype = new GC.Spread.Sheets.CellTypes.ComboBox();
  900. ComboCellForActiveCell.prototype.paintValue = function (ctx, value, x, y, w, h, style, options) {
  901. let sheet = options.sheet;
  902. if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) {
  903. GC.Spread.Sheets.CellTypes.ComboBox.prototype.paintValue.apply(this, arguments);
  904. } else {
  905. GC.Spread.Sheets.CellTypes.Base.prototype.paintValue.apply(this, arguments);
  906. }
  907. };
  908. ComboCellForActiveCell.prototype.getHitInfo = function (x, y, cellStyle, cellRect, options) {
  909. let sheet = options.sheet;
  910. if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) {
  911. return GC.Spread.Sheets.CellTypes.ComboBox.prototype.getHitInfo.apply(this, arguments);
  912. } else {
  913. return {
  914. x: x,
  915. y: y,
  916. row: options.row,
  917. col: options.col,
  918. cellStyle: cellStyle,
  919. cellRect: cellRect,
  920. sheetArea: options.sheetArea
  921. };//GC.Spread.Sheets.CellTypes.Text.prototype.getHitInfo.apply(this, arguments);
  922. }
  923. };
  924. return new ComboCellForActiveCell();
  925. },
  926. getTipsCombo:function (forLocked,tips,setting,node) {
  927. let getTipsCombo = function () {
  928. this.clickCom=false;
  929. };
  930. getTipsCombo.prototype = sheetCommonObj.getDynamicCombo(forLocked);
  931. if(tips && tips !=""){
  932. getTipsCombo.prototype.processMouseEnter = function(hitinfo){
  933. if(this.clickCom == true){ //点击了下拉框的三角形,则不用再显示悬浮框了
  934. this.clickCom = false;
  935. return;
  936. }
  937. let text = typeof tips == 'function'?tips(node):tips;
  938. TREE_SHEET_HELPER.delayShowTips(hitinfo,setting,text);
  939. };
  940. getTipsCombo.prototype.processMouseLeave = function (hitinfo) {
  941. TREE_SHEET_HELPER.hideTipsDiv();
  942. };
  943. getTipsCombo.prototype.processMouseDown = function (hitinfo){
  944. if(hitinfo.isReservedLocation == true){//这里是点击了下拉框的三角形才会有这个属性
  945. TREE_SHEET_HELPER.hideTipsDiv();
  946. this.clickCom = true;
  947. }
  948. GC.Spread.Sheets.CellTypes.ComboBox.prototype.processMouseDown.apply(this, arguments);
  949. };
  950. getTipsCombo.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context){
  951. TREE_SHEET_HELPER.hideTipsDiv();
  952. GC.Spread.Sheets.CellTypes.ComboBox.prototype.updateEditor.apply(this, arguments);
  953. };
  954. }
  955. return new getTipsCombo();
  956. },
  957. getTreeNodeCellType:function (datas,row,parentMap) {// 2018-09-26 不用spreadjs默认的树结构,自定义控件
  958. var ns = GC.Spread.Sheets;
  959. let rectW = 10;
  960. let rectH = 10;
  961. let margin = 3;
  962. function TreeNodeCellType() {
  963. this.collapsed = gljUtil.isDef(datas[row].collapsed)?datas[row].collapsed: true; //默认是折叠的
  964. this.treeNodeType = true;
  965. this.rectInfo = {};
  966. }
  967. TreeNodeCellType.prototype = new ns.CellTypes.Text();
  968. TreeNodeCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) {
  969. let offset = 0;
  970. let step = 7;
  971. let level = getTreeLevel(datas[row],datas);//从0开始,取当前节点是第几级的
  972. let tem = offset+margin+ rectW/2+step;//两条线之间的间隔
  973. let t_offset = offset;
  974. let temParentID = datas[row].ParentID;
  975. if(this.treeNodeType == true){
  976. for(let i = level;i>0;i--){//这里是画子节点前面的竖线,从第二级开始
  977. let temParent = getParent(temParentID,datas);
  978. if(temParent){//父节点有下一个兄弟节点才需要画
  979. if(hasNextBrother(parentMap,temParent)) sheetCommonObj.drawLine(ctx,x+t_offset+tem*i,y,x+t_offset+tem*i,y+h);
  980. temParentID = temParent.ParentID;
  981. }
  982. offset +=tem;
  983. }
  984. }
  985. offset+=step; //这个没法移动,所以要两个判断
  986. if(this.treeNodeType == true){
  987. if(hasChildern(datas[row].ID,datas)){//如果是有子节点
  988. //第一条 或者没有父节点(如费率子表综合里程项)不用画方框头上那条竖线其它都要
  989. 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);
  990. //画方框下面的那条竖线,如果没有下一个兄弟节点,则不用画
  991. 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);
  992. sheetCommonObj.drowRect(ctx, x+offset, y, w, h,rectW,rectH,margin);
  993. sheetCommonObj.drowSymbol(ctx, x+offset, y, w, h,rectW,rectH,margin, this.collapsed);
  994. this.rectInfo = {x:x+offset+margin,rectW:rectW}//计录一下可点击位置
  995. }else {
  996. let hasNext = datas[row+1]?datas[row+1].ParentID == datas[row].ParentID:false;
  997. sheetCommonObj.drowSubItem(ctx, x, y, w, h, offset,hasNext,margin+ rectW/2);
  998. }
  999. }
  1000. offset += step;
  1001. offset += rectW;
  1002. x = x + offset;//设置偏移
  1003. w = w - offset;
  1004. if(datas[row].foreColor) style.foreColor = datas[row].foreColor;
  1005. GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments);
  1006. };
  1007. // override getHitInfo to allow cell type get mouse messages
  1008. TreeNodeCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
  1009. return {
  1010. x: x,
  1011. y: y,
  1012. row: context.row,
  1013. col: context.col,
  1014. cellStyle: cellStyle,
  1015. cellRect: cellRect,
  1016. sheetArea: context.sheetArea
  1017. };
  1018. }
  1019. TreeNodeCellType.prototype.processMouseDown = function (hitinfo) {
  1020. ////方框外1像素内都有效
  1021. if (!_.isEmpty(this.rectInfo)&&Math.floor(hitinfo.x) <= this.rectInfo.x+this.rectInfo.rectW+2 && Math.floor(hitinfo.x) >= this.rectInfo.x-2) {
  1022. this.collapsed = !this.collapsed;
  1023. datas[row].collapsed = this.collapsed;
  1024. this.refreshChildrenVisible(hitinfo.sheet);
  1025. hitinfo.sheet.invalidateLayout();
  1026. hitinfo.sheet.repaint();
  1027. }
  1028. };
  1029. TreeNodeCellType.prototype.refreshChildrenVisible = function (sheet) {
  1030. sheet.suspendPaint();
  1031. sheet.suspendEvent();
  1032. refreshVisible(datas[row]);
  1033. sheet.resumeEvent();
  1034. sheet.resumePaint();
  1035. function refreshVisible(item){
  1036. if(parentMap[item.ID]){
  1037. for(let sub of parentMap[item.ID]){
  1038. refreshVisible(sub)
  1039. }
  1040. }
  1041. let visible = getVisible(item);
  1042. let trow = datas.indexOf(item);
  1043. sheet.getRange(trow , -1, 1, -1).visible(visible);
  1044. }
  1045. function getVisible(item) {
  1046. if(item.ParentID){
  1047. let parent = getParent(item.ParentID,datas);
  1048. if(!parent) return true;
  1049. let p_row= datas.indexOf(parent);
  1050. let visible = !sheet.getCellType(p_row,0).collapsed;
  1051. if(visible == true){ //如果是显示的,则要再往父节点的父节点检查,只要有一个节点是隐藏的,则都是隐藏
  1052. return getVisible(parent);
  1053. }else {
  1054. return visible
  1055. }
  1056. }else {//如果parentID 为空则是最根节点
  1057. return true;
  1058. }
  1059. }
  1060. };
  1061. return new TreeNodeCellType()
  1062. function getTreeLevel(item,data) {
  1063. if(item.ParentID && item.ParentID!=-1){
  1064. let pitem = _.find(data,{'ID':item.ParentID});
  1065. return getTreeLevel(pitem,data) + 1;
  1066. }else {
  1067. return 0
  1068. }
  1069. }
  1070. function hasChildern(ID,data) {//返回是否有子项
  1071. let p = _.find(data,{'ParentID':ID});
  1072. if(p) return true;
  1073. return false
  1074. }
  1075. function getParent(ParentID,data) {
  1076. let p = _.find(data,{'ID':ParentID});
  1077. return p;
  1078. }
  1079. function hasNextBrother(parentMap,item){
  1080. let children =parentMap[item.ParentID];
  1081. if(!gljUtil.isDef(children)|| children.indexOf(item) == children.length -1) return false;
  1082. return true
  1083. }
  1084. },
  1085. setDynamicCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) {
  1086. let me = this;
  1087. sheet.suspendPaint();
  1088. let combo = me.getDynamicCombo();
  1089. for(let i = 0, len = rowCount; i < len; i++){
  1090. if(itemsHeight) {
  1091. combo.itemHeight(itemsHeight);
  1092. combo._maxDropDownItems = itemsHeight + 5;
  1093. }
  1094. if(itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
  1095. else if(itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
  1096. else combo.items(items);
  1097. sheet.getCell(beginRow + i, col).cellType(combo);
  1098. }
  1099. sheet.resumePaint();
  1100. },
  1101. setStaticCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) {
  1102. sheet.suspendPaint();
  1103. let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
  1104. for(let i = 0, len = rowCount; i < len; i++){
  1105. if(itemsHeight) combo.itemHeight(itemsHeight);
  1106. if(itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
  1107. else if(itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
  1108. else combo.items(items);
  1109. sheet.getCell(beginRow + i, col).cellType(combo);
  1110. }
  1111. sheet.resumePaint();
  1112. },
  1113. //设置系统粘贴板数据,需要用户触发事件,直接调用会失败
  1114. copyTextToClipboard: function(text) {
  1115. let textArea = document.createElement("textarea");
  1116. textArea.style.position = 'fixed';
  1117. textArea.style.top = 0;
  1118. textArea.style.left = 0;
  1119. textArea.style.width = '2em';
  1120. textArea.style.height = '2em';
  1121. textArea.style.padding = 0;
  1122. textArea.style.border = 'none';
  1123. textArea.style.outline = 'none';
  1124. textArea.style.boxShadow = 'none';
  1125. textArea.style.background = 'transparent';
  1126. textArea.value = text;
  1127. document.body.appendChild(textArea);
  1128. textArea.select();
  1129. try {
  1130. let successful = document.execCommand('copy');
  1131. let msg = successful ? 'successful' : 'unsuccessful';
  1132. console.log('Copying text command was ' + msg);
  1133. } catch (err) {
  1134. console.log('Oops, unable to copy');
  1135. }
  1136. document.body.removeChild(textArea);
  1137. },
  1138. //获取选中区域的表格类型数据(可粘贴到excel)
  1139. getTableData: function (sheet, colSettings = null) {
  1140. let rst = '';
  1141. let sel = sheet.getSelections()[0];
  1142. let pasteText = [];
  1143. for(let row = sel.row; row < sel.row + sel.rowCount; row++){
  1144. if(!sheet.getCell(row, -1).visible())
  1145. continue;
  1146. let rowText = [];
  1147. for(let j = 0; j < sel.colCount; j++){
  1148. let col = sel.col + j;
  1149. if(!sheet.getCell(-1, col).visible())
  1150. continue;
  1151. if(colSettings && (colSettings[col]['data']['field'] === 'itemCharacterText' || colSettings[col]['data']['field'] === 'jobContentText'))
  1152. rowText.push(sheet.getText(row, col) ? `"${sheet.getText(row, col)}"` : '');
  1153. else
  1154. rowText.push(sheet.getText(row, col) ? sheet.getText(row, col): '');
  1155. }
  1156. pasteText.push(rowText.join('\t'));
  1157. }
  1158. return pasteText.join('\n');
  1159. },
  1160. transferToTreeSetting:function(setting,treeSetting,treeCol){
  1161. for(let h of setting.header){
  1162. treeSetting.cols.push(getSettingCol(h))
  1163. }
  1164. for(let l of setting.view.lockColumns){
  1165. treeSetting.cols[l].readOnly = true;
  1166. }
  1167. return treeSetting;
  1168. function getSettingCol(header) {
  1169. let aMap ={left:0,center:1,right:2};
  1170. let hAlign = header.hAlign?aMap[header.hAlign]:0;
  1171. let col = {
  1172. "width":header.headerWidth?header.headerWidth:100,
  1173. "head":{
  1174. "titleNames":Array.isArray(header.headerName)?header.headerName:[header.headerName],
  1175. "spanCols":header.spanCols?header.spanCols:[1],
  1176. "spanRows":header.spanRows?header.spanRows:[1],
  1177. "vAlign":[1],
  1178. "hAlign":[1],
  1179. "font":["Arial"]
  1180. },
  1181. "data": {
  1182. "field": header.dataCode,
  1183. "vAlign": 1,
  1184. "hAlign": hAlign,
  1185. "font": "Arial"
  1186. }
  1187. };
  1188. if(header.showHint == true){
  1189. col.showHint = true;
  1190. }
  1191. if(header.cellType){
  1192. col.data.cellType = getCellType(header);
  1193. }
  1194. if(header.decimalField){//设置formatter
  1195. let decimal = getDecimal(header.decimalField);
  1196. col.formatter = getFormatter(decimal);
  1197. }
  1198. if(header.getText && treeCol){
  1199. col.data.getText = treeCol.getEvent(header.getText);
  1200. }
  1201. /*col.readOnly = function (node) {
  1202. if(node.data.ParentID == -1 || node.data.id == 'GJ'){//三材类别项不能编辑)
  1203. return true;
  1204. }
  1205. return false;
  1206. };*/
  1207. return col;
  1208. }
  1209. function getCellType(header) {
  1210. return function () {
  1211. if(header.cellType === "checkBox"){
  1212. return new GC.Spread.Sheets.CellTypes.CheckBox();
  1213. }
  1214. if(header.cellType === "comboBox"){
  1215. let dynamicCombo = sheetCommonObj.getDynamicCombo(true);
  1216. if(header.options){
  1217. dynamicCombo.itemHeight(header.options.length).items(header.options);
  1218. if(header.editorValueType==true){
  1219. dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
  1220. }
  1221. }
  1222. return dynamicCombo
  1223. }
  1224. }
  1225. }
  1226. },
  1227. //注册自定义回车键事件
  1228. bindEnterKey: function (workBook, operation) {
  1229. workBook.commandManager().register('myEnter', operation);
  1230. workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.enter, false, false, false, false);
  1231. workBook.commandManager().setShortcutKey('myEnter', GC.Spread.Commands.Key.enter, false, false, false, false);
  1232. },
  1233. //解决esc后触发了编辑结束的保存事件,显示与实际数据不同问题
  1234. bindEscKey: function (workBook, sheets) {
  1235. function isDef(v){
  1236. return typeof v !== 'undefined' && v !== null;
  1237. }
  1238. workBook.commandManager().register('myEsc', function () {
  1239. let activeSheet = workBook.getActiveSheet();
  1240. let hasTheSheet = false;
  1241. for(let sheetObj of sheets){
  1242. let sheet = sheetObj.sheet;
  1243. if(sheet === activeSheet){
  1244. hasTheSheet = true;
  1245. let editStarting = sheetObj.editStarting;
  1246. let editEnded = sheetObj.editEnded;
  1247. if(editStarting){
  1248. sheet.unbind(GC.Spread.Sheets.Events.EditStarting);
  1249. }
  1250. if(editEnded){
  1251. sheet.unbind(GC.Spread.Sheets.Events.EditEnded);
  1252. }
  1253. let row = sheet.getActiveRowIndex();
  1254. let col = sheet.getActiveColumnIndex();
  1255. let orgV = sheet.getValue(row, col);
  1256. let orgText = sheet.getText(row, col);
  1257. if(!isDef(orgV)){
  1258. orgV = '';
  1259. }
  1260. if(sheet.isEditing()){
  1261. sheet.endEdit();
  1262. sheet.setValue(row, col, orgV);
  1263. }
  1264. if(editStarting){
  1265. sheet.bind(GC.Spread.Sheets.Events.EditStarting, editStarting);
  1266. }
  1267. if(editEnded){
  1268. sheet.bind(GC.Spread.Sheets.Events.EditEnded, editEnded);
  1269. }
  1270. }
  1271. }
  1272. //容错处理,以防没把所有工作簿的表格信息传入参数
  1273. if(!hasTheSheet){
  1274. if(activeSheet.isEditing()){
  1275. activeSheet.endEdit();
  1276. }
  1277. }
  1278. });
  1279. workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.esc, false, false, false, false);
  1280. workBook.commandManager().setShortcutKey('myEsc', GC.Spread.Commands.Key.esc, false, false, false, false);
  1281. },
  1282. //设置默认样式
  1283. spreadDefaultStyle: function (workBook) {
  1284. let defaultStyle = new GC.Spread.Sheets.Style();
  1285. defaultStyle.font = '14px Calibri';
  1286. let sheetCount = workBook.getSheetCount();
  1287. for(let i = 0; i < sheetCount; i++){
  1288. let sheet = workBook.getSheet(i);
  1289. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  1290. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.colHeader);
  1291. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.rowHeader);
  1292. }
  1293. },
  1294. //动态根据工作簿宽度和各列宽度比例设置宽度
  1295. setColumnWidthByRate: function (workBookWidth, workBook, headers){
  1296. if(workBook){
  1297. const sheet = workBook.getActiveSheet();
  1298. sheet.suspendEvent();
  1299. sheet.suspendPaint();
  1300. for(let col = 0; col < headers.length; col++){
  1301. if(headers[col]['rateWidth'] !== undefined && headers[col]['rateWidth'] !== null && headers[col]['rateWidth'] !== ''){
  1302. sheet.setColumnWidth(col, workBookWidth * headers[col]['rateWidth'], GC.Spread.Sheets.SheetArea.colHeader)
  1303. }
  1304. else {
  1305. if(headers[col]['headerWidth'] !== undefined && headers[col]['headerWidth'] !== null && headers[col]['headerWidth'] !== ''){
  1306. sheet.setColumnWidth(col, headers[col]['headerWidth'], GC.Spread.Sheets.SheetArea.colHeader)
  1307. }
  1308. }
  1309. }
  1310. sheet.resumeEvent();
  1311. sheet.resumePaint();
  1312. }
  1313. },
  1314. drowRect:function (ctx, x, y, w, h,rectW,rectH,margin) {
  1315. ctx.save();
  1316. ctx.strokeStyle = "gray";
  1317. ctx.translate(0.5, 0.5);
  1318. ctx.beginPath();
  1319. let rectX = x + margin;
  1320. let rectY = y + Math.round(h / 2) - rectH / 2;
  1321. ctx.moveTo(rectX, rectY);
  1322. ctx.lineTo(rectX, rectY + rectH);
  1323. ctx.lineTo(rectX + rectW, rectY + rectH);
  1324. ctx.lineTo(rectX + rectW, rectY);
  1325. ctx.lineTo(rectX, rectY);
  1326. ctx.moveTo(rectX + rectW, y + Math.round(h / 2));
  1327. ctx.lineTo(rectX + rectW + 5, y + Math.round(h / 2));
  1328. ctx.stroke();
  1329. ctx.restore();
  1330. },
  1331. drawLine : function (ctx, x1, y1, x2, y2, color) {
  1332. let l_color = color?color:"gray";
  1333. ctx.save();
  1334. ctx.translate(0.5, 0.5);
  1335. ctx.beginPath();
  1336. ctx.moveTo(x1, y1);
  1337. ctx.lineTo(x2, y2);
  1338. ctx.strokeStyle = l_color;
  1339. ctx.stroke();
  1340. ctx.restore();
  1341. },
  1342. drowSymbol :function (ctx, x, y, w, h,rectW,rectH,margin,collapsed) {
  1343. ctx.save();
  1344. ctx.strokeStyle = "#000000";
  1345. ctx.translate(0.5, 0.5);
  1346. ctx.beginPath();
  1347. ctx.moveTo(x + margin + 2, y + Math.round(h / 2));
  1348. ctx.lineTo(x + margin + 8, y + Math.round(h / 2));
  1349. let rectY = y + Math.round(h / 2) - rectH / 2;
  1350. if (collapsed) {
  1351. ctx.moveTo(x + margin + rectW / 2, rectY + 2);
  1352. ctx.lineTo(x + margin + rectW / 2, rectY + 2 + 6);
  1353. }
  1354. ctx.stroke();
  1355. ctx.restore();
  1356. },
  1357. drowTriangle:function(ctx,x,y){//画向下三角形
  1358. ctx.save();
  1359. ctx.fillStyle = "black";
  1360. ctx.beginPath();
  1361. ctx.moveTo(x, y);
  1362. ctx.lineTo(x-3, y -6);
  1363. ctx.lineTo(x+3, y -6);
  1364. ctx.fill();
  1365. ctx.restore();
  1366. },
  1367. drowSubItem: function (ctx, x, y, w, h, offset, hasNext,step) {
  1368. let t_step = step?step:6;
  1369. offset += t_step;
  1370. ctx.save();
  1371. ctx.strokeStyle = "gray";
  1372. ctx.translate(0.5, 0.5);
  1373. ctx.beginPath();
  1374. ctx.moveTo(x + offset, y);
  1375. ctx.lineTo(x + offset, y + Math.round(h / 2));
  1376. offset += 9;
  1377. ctx.lineTo(x + offset, y + Math.round(h / 2));
  1378. if (hasNext) {
  1379. ctx.moveTo(x + offset - 9, y + Math.round(h / 2));
  1380. ctx.lineTo(x + offset - 9, y + h);
  1381. }
  1382. ctx.stroke();
  1383. ctx.restore();
  1384. return offset;
  1385. },
  1386. checkData:function(col,setting, value) {
  1387. let result = true;
  1388. let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null;
  1389. if (validator === null) {
  1390. return result;
  1391. }
  1392. switch (validator) {
  1393. case 'number':
  1394. let regular = /^\d+(\.\d+)?$/;
  1395. result = regular.test(value);
  1396. break;
  1397. case 'boolean':
  1398. let booleanValue = [true, false];
  1399. result = booleanValue.indexOf(value) >= 0;
  1400. break;
  1401. }
  1402. return result;
  1403. }
  1404. }