sheet_common.js 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371
  1. /**
  2. * Created by Tony on 2017/4/28.
  3. */
  4. var sheetCommonObj = {
  5. // createSpread、initSheet 在一个Spread多个Sheet分别调用时的情况下使用。
  6. // buildSheet 在一个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.allowCopyPasteExcelStyle = false;
  11. spreadBook.options.tabStripVisible = false;
  12. //spreadBook.options.showHorizontalScrollbar = false;
  13. spreadBook.options.allowUserDragDrop = false;
  14. spreadBook.options.scrollbarMaxAlign = true;
  15. return spreadBook;
  16. },
  17. initSheet: function(sheet, setting, rowCount) {
  18. var me = this;
  19. var spreadNS = GC.Spread.Sheets;
  20. sheet.suspendPaint();
  21. sheet.suspendEvent();
  22. sheet.setRowCount(1, spreadNS.SheetArea.colHeader);
  23. sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
  24. sheet.options.colHeaderAutoTextIndex = 1;
  25. sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
  26. sheet.options.protectionOptions = {
  27. allowResizeRows: true,
  28. allowResizeColumns: true
  29. };
  30. sheet.showRowOutline(false);
  31. me.buildHeader(sheet, setting);
  32. if (rowCount > 0) sheet.setRowCount(rowCount);
  33. sheet.resumeEvent();
  34. sheet.resumePaint();
  35. },
  36. buildSheet: function(container, setting, rowCount) {
  37. var me = this;
  38. var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: 1 });
  39. spreadBook.options.tabStripVisible = false;
  40. //spreadBook.options.showHorizontalScrollbar = false;
  41. spreadBook.options.scrollbarMaxAlign = true;
  42. spreadBook.options.allowCopyPasteExcelStyle = false;
  43. spreadBook.options.allowExtendPasteRange = true;
  44. spreadBook.options.allowUserDragDrop = false;
  45. var spreadNS = GC.Spread.Sheets;
  46. var sheet = spreadBook.getSheet(0);
  47. sheet.suspendPaint();
  48. sheet.suspendEvent();
  49. //Set rowHeader count and columnHeader count.
  50. sheet.setRowCount(1, spreadNS.SheetArea.colHeader);
  51. sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
  52. sheet.options.colHeaderAutoTextIndex = 1;
  53. sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
  54. sheet.options.protectionOptions = {
  55. allowResizeRows: true,
  56. allowResizeColumns: true
  57. };
  58. sheet.showRowOutline(false);
  59. //setup column header
  60. me.buildHeader(sheet, setting);
  61. //setup cells
  62. if (rowCount > 0) sheet.setRowCount(rowCount);
  63. sheet.resumeEvent();
  64. sheet.resumePaint();
  65. return spreadBook;
  66. },
  67. buildHeader: function(sheet, setting){
  68. var me = this, ch = GC.Spread.Sheets.SheetArea.colHeader;
  69. for (var i = 0; i < setting.header.length; i++) {
  70. sheet.setValue(0, i, setting.header[i].headerName, ch);
  71. sheet.setColumnWidth(i, setting.header[i].headerWidth?setting.header[i].headerWidth:100);
  72. }
  73. },
  74. cleanData: function (sheet, setting, rowCount) {
  75. sheet.suspendPaint();
  76. sheet.suspendEvent();
  77. sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
  78. if (rowCount > 0) sheet.setRowCount(rowCount);
  79. sheet.resumeEvent();
  80. sheet.resumePaint();
  81. },
  82. cleanSheet: function(sheet, setting, rowCount) {
  83. sheet.suspendPaint();
  84. sheet.suspendEvent();
  85. sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
  86. if (rowCount > 0) sheet.setRowCount(rowCount);
  87. sheet.clearSelection();
  88. sheet.resumeEvent();
  89. sheet.resumePaint();
  90. },
  91. setAreaAlign: function(area, hAlign, vAlign){
  92. if (!(hAlign) || hAlign === "left") {
  93. area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
  94. } else if (hAlign === "right") {
  95. area.hAlign(GC.Spread.Sheets.HorizontalAlign.right);
  96. } else if (hAlign === "center") {
  97. area.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
  98. } else {
  99. area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
  100. }
  101. if (!(vAlign) || vAlign === "center") {
  102. area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
  103. } else if (vAlign === "top") {
  104. area.vAlign(GC.Spread.Sheets.VerticalAlign.top);
  105. } else if (vAlign === "bottom") {
  106. area.vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
  107. } else {
  108. area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
  109. }
  110. },
  111. showData: function(sheet, setting, data, distTypeTree) {
  112. var me = this, ch = GC.Spread.Sheets.SheetArea.viewport;
  113. sheet.suspendPaint();
  114. sheet.suspendEvent();
  115. if(typeof setting.owner !== 'undefined' && setting.owner === 'gljComponent'){
  116. sheet.setRowCount(data.length + 5);
  117. }
  118. else{
  119. sheet.setRowCount(typeof repositoryGljObj !== 'undefined' && repositoryGljObj.currentOprParent === 1 ? data.length : data.length + 10);
  120. }
  121. if(data.length === 0){
  122. for(let i = 0; i < sheet.getRowCount(); i++){
  123. sheet.getCell(i, 4, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  124. }
  125. }
  126. for (var col = 0; col < setting.header.length; col++) {
  127. var hAlign = "left", vAlign = "center";
  128. if (setting.header[col].hAlign) {
  129. hAlign = setting.header[col].hAlign;
  130. } else if (setting.header[col].dataType !== "String"){
  131. hAlign = "right";
  132. }
  133. vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
  134. me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
  135. if (setting.header[col].formatter) {
  136. //var style = new GC.Spread.Sheets.Style();
  137. //style.formatter = setting.header[col].formatter;
  138. //sheet.setStyle(row,col,style,GC.Spread.Sheets.SheetArea.viewport);
  139. sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
  140. }
  141. for (var row = 0; row < data.length; row++) {
  142. //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport);
  143. if(setting.header[col].dataCode === 'gljType' && data[row].gljType){
  144. if(typeof repositoryGljObj !== 'undefined' && typeof repositoryGljObj.allowComponent !== 'undefined' && repositoryGljObj.allowComponent.indexOf(data[row].gljType) !== -1){
  145. sheet.getCell(row, 4, GC.Spread.Sheets.SheetArea.viewport).locked(true);
  146. }
  147. else if(typeof repositoryGljObj !== 'undefined' && typeof repositoryGljObj.allowComponent !== 'undefined' && repositoryGljObj.allowComponent.indexOf(data[row].gljType) === -1){
  148. sheet.getCell(row, 4, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  149. }
  150. let distTypeVal = distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName;
  151. sheet.setValue(row, col, distTypeVal, ch);
  152. }
  153. else {
  154. sheet.setValue(row, col, data[row][setting.header[col].dataCode], ch);
  155. sheet.setTag(row, 0, data[row].ID, ch);
  156. if(typeof setting.owner !== 'undefined' && setting.owner !== 'gljComponent'){
  157. sheet.getCell(row, 0, GC.Spread.Sheets.SheetArea.viewport).locked(true);
  158. }
  159. }
  160. }
  161. for(let i = data.length; i < sheet.getRowCount(); i++){
  162. sheet.getCell(i, 4, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  163. }
  164. }
  165. sheet.resumeEvent();
  166. sheet.resumePaint();
  167. //me.shieldAllCells(sheet);
  168. },
  169. analyzePasteData: function(setting, pastedInfo) {
  170. var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};
  171. for (var i = 0; i < pastedInfo.pasteData.text.length; i++) {
  172. if (pastedInfo.pasteData.text[i] === "\n") {
  173. propId = pastedInfo.cellRange.col;
  174. preStrIdx = i + 1;
  175. rst.push(itemObj);
  176. if (i < pastedInfo.pasteData.text.length - 1) {
  177. itemObj = {};
  178. }
  179. } else if (pastedInfo.pasteData.text[i] === "\t" || pastedInfo.pasteData.text[i] === "\r") {
  180. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx, i);
  181. propId++;
  182. preStrIdx = i + 1;
  183. //if the last copied-cell were empty, should check whether the end of text
  184. if (i == pastedInfo.pasteData.text.length - 1) {
  185. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
  186. rst.push(itemObj);
  187. }
  188. } else if (i == pastedInfo.pasteData.text.length - 1) {
  189. itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
  190. rst.push(itemObj);
  191. }
  192. }
  193. return rst;
  194. },
  195. combineRowData: function(sheet, setting, row, repositoryGljObj) {
  196. let me = this;
  197. var rst = {};
  198. let comboBoxCellType = sheet.getCellType(row, 5);
  199. let items = comboBoxCellType.items();
  200. for (var col = 0; col < setting.header.length; col++) {
  201. if(setting.header[col].dataCode === 'gljType'){
  202. items.forEach(function(item){
  203. if(sheet.getValue(row, col) === item.text){
  204. rst[setting.header[col].dataCode] = item.value;
  205. if(repositoryGljObj){
  206. rst.shortName = repositoryGljObj.distTypeTree.distTypes[repositoryGljObj.distTypeTree.prefix + item.value].data.shortName;
  207. }
  208. }
  209. });
  210. }
  211. else if (setting.header[col].dataCode === 'code'){
  212. if(repositoryGljObj){
  213. let gljList = repositoryGljObj.gljList,
  214. orgCode = repositoryGljObj.orgCode,
  215. isExist = false;
  216. for(let i=0; i< gljList.length; i++){
  217. if(gljList[i].code === sheet.getValue(row, col) && sheet.getValue(row, col)!== orgCode){
  218. me.lockAllCells(sheet);
  219. $('#alertText').text("输入的编号已存在,请重新输入!");
  220. $('#codeAlertBtn').click();
  221. $('#codAleConfBtn').click(function () {
  222. // me.reLockSomeCodes(sheet, 0, repositoryGljObj.currentCache.length);
  223. me.unLockAllCells(sheet);
  224. me.reLockSomeCodes(sheet, 0, repositoryGljObj.currentCache.length);
  225. sheet.setValue(row, 0, orgCode);
  226. sheet.getCell(row, 0).formatter("@");
  227. sheet.setActiveCell(row, 0);
  228. });
  229. $('#codAleClose').click(function () {
  230. //me.reLockSomeCodes(sheet, 0, repositoryGljObj.currentCache.length);
  231. me.unLockAllCells(sheet);
  232. me.reLockSomeCodes(sheet, 0, repositoryGljObj.currentCache.length);
  233. sheet.setValue(row, 0, orgCode);
  234. sheet.getCell(row, 0).formatter("@");
  235. sheet.setActiveCell(row, 0);
  236. });
  237. // sheet.setValue(row, col, orgCode);
  238. isExist = true
  239. }
  240. }
  241. if(!isExist){
  242. rst[setting.header[col].dataCode] = sheet.getValue(row, col);
  243. }
  244. }
  245. else{
  246. rst[setting.header[col].dataCode] = sheet.getValue(row, col);
  247. }
  248. }
  249. else{
  250. rst[setting.header[col].dataCode] = sheet.getValue(row, col);
  251. }
  252. }
  253. return rst;
  254. },
  255. combineRationRowData: function(sheet, setting, row) {
  256. var rst = {};
  257. for (var col = 0; col < setting.header.length; col++) {
  258. rst[setting.header[col].dataCode] = sheet.getValue(row, col);
  259. }
  260. return rst;
  261. },
  262. shieldAllCells: function(sheet) {
  263. sheet.options.isProtected = true;
  264. },
  265. unShieldAllCells: function(sheet) {
  266. sheet.options.isProtected = false;
  267. },
  268. reLockSomeCodes: function (sheet, beginRow, endRow) {
  269. sheet.suspendPaint();
  270. sheet.suspendEvent();
  271. let defaultStyle = new GC.Spread.Sheets.Style();
  272. defaultStyle.locked = false;
  273. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  274. let unLockStyle = new GC.Spread.Sheets.Style();
  275. unLockStyle.locked = false;
  276. let lockStyle = new GC.Spread.Sheets.Style();
  277. lockStyle.locked = true;
  278. for(let row = beginRow; row < endRow; row++){
  279. sheet.setStyle(row, 0, lockStyle);
  280. }
  281. for(let row = endRow; row < sheet.getRowCount(); row++){
  282. sheet.setStyle(row, 0, unLockStyle);
  283. }
  284. sheet.options.isProtected = true;
  285. sheet.resumePaint();
  286. sheet.resumeEvent();
  287. },
  288. unLockAllCells: function (sheet) {
  289. sheet.suspendPaint();
  290. sheet.suspendEvent();
  291. let defaultStyle = new GC.Spread.Sheets.Style();
  292. defaultStyle.locked = false;
  293. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  294. sheet.setStyle(-1, 0, defaultStyle);
  295. sheet.options.isProtected = false;
  296. sheet.resumePaint();
  297. sheet.resumeEvent();
  298. },
  299. lockAllCells: function (sheet) {
  300. sheet.suspendPaint();
  301. sheet.suspendEvent();
  302. let defaultStyle = new GC.Spread.Sheets.Style();
  303. defaultStyle.locked = true;
  304. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  305. for(let i = 0; i < sheet.getRowCount(); i++){
  306. sheet.setStyle(i, 0, defaultStyle);
  307. }
  308. sheet.options.isProtected = true;
  309. sheet.resumePaint();
  310. sheet.resumeEvent();
  311. },
  312. lockSomeCodes: function (sheet, beginRow, endRow) {
  313. let defaultStyle = new GC.Spread.Sheets.Style();
  314. defaultStyle.locked = false;
  315. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  316. let style = new GC.Spread.Sheets.Style();
  317. style.locked = true;
  318. sheet.suspendPaint();
  319. sheet.suspendEvent();
  320. for(let i = beginRow; i < endRow; i++){
  321. sheet.setStyle(i, 0, style);
  322. }
  323. sheet.options.isProtected = true;
  324. sheet.resumePaint();
  325. sheet.resumeEvent();
  326. },
  327. lockCodeCells: function (sheet, rowCount) {
  328. sheet.suspendPaint();
  329. sheet.suspendEvent();
  330. let sheetRowCount = sheet.getRowCount();
  331. let defaultStyle = new GC.Spread.Sheets.Style();
  332. defaultStyle.locked = false;
  333. sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
  334. let style = new GC.Spread.Sheets.Style();
  335. style.locked = true;
  336. sheet.setStyle(-1, 0, style);
  337. for(let i =rowCount; i<sheetRowCount; i++){
  338. sheet.setStyle(i, -1, style);
  339. }
  340. sheet.options.isProtected = true;
  341. sheet.resumePaint();
  342. sheet.resumeEvent();
  343. },
  344. lockCells: function(sheet, setting){
  345. sheet.suspendPaint();
  346. sheet.suspendEvent();
  347. if (setting && setting.view.lockColumns && setting.view.lockColumns.length > 0) {
  348. sheet.options.isProtected = true;
  349. sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
  350. for (var i = 0; i < setting.view.lockColumns.length; i++) {
  351. sheet.getRange(-1,setting.view.lockColumns[i] , -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
  352. }
  353. }
  354. sheet.resumePaint();
  355. sheet.resumeEvent();
  356. },
  357. chkIfEmpty: function(rObj, setting) {
  358. var rst = true;
  359. if (rObj) {
  360. for (var i = 0; i < setting.header.length; i++) {
  361. if (rObj[setting.header[i].dataCode]) {
  362. rst = false;
  363. break;
  364. }
  365. }
  366. }
  367. return rst;
  368. }
  369. }