rpt_excel_util.js 49 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032
  1. 'use strict';
  2. /**
  3. * Created by Tony on 2017/4/1.
  4. */
  5. const JV = require('../rpt_component/jpc_value_define');
  6. const fs = require('fs');
  7. const JSZip = require('jszip');
  8. const strUtil = require('../public/stringUtil');
  9. const jpcCmnHelper = require('../rpt_component/helper/jpc_helper_common');
  10. const DPI = jpcCmnHelper.getScreenDPI()[0];
  11. const fsUtil = require('../public/fsUtil');
  12. const dftHeadXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
  13. const uuidV1 = require('uuid/v1');
  14. function writeContentTypes(sheets, isSinglePage) {
  15. const rst = [];
  16. rst.push(dftHeadXml + '\r\n');
  17. rst.push('<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">');
  18. rst.push('<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>');
  19. rst.push('<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>');
  20. rst.push('<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>');
  21. rst.push('<Default Extension="xml" ContentType="application/xml"/>');
  22. rst.push('<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>');
  23. rst.push('<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>');
  24. rst.push('<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>');
  25. if (isSinglePage) {
  26. rst.push('<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
  27. } else {
  28. for (let i = 0; i < sheets.length; i++) {
  29. rst.push('<Override PartName="/xl/worksheets/sheet' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
  30. }
  31. }
  32. rst.push('<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>');
  33. rst.push('</Types>');
  34. return rst;
  35. }
  36. function writeRootRels() {
  37. const rst = [];
  38. rst.push(dftHeadXml + '\r\n');
  39. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  40. rst.push('<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>');
  41. rst.push('<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>');
  42. rst.push('<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>');
  43. rst.push('</Relationships>');
  44. return rst;
  45. }
  46. function writeApp(sheets, isSinglePage) {
  47. const rst = [];
  48. rst.push(dftHeadXml + '\r\n');
  49. rst.push('<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">');
  50. rst.push('<Application>Microsoft Excel</Application>');
  51. rst.push('<DocSecurity>0</DocSecurity>');
  52. rst.push('<ScaleCrop>false</ScaleCrop>');
  53. rst.push('<HeadingPairs>');
  54. rst.push('<vt:vector size="2" baseType="variant">');
  55. rst.push('<vt:variant><vt:lpstr>工作表</vt:lpstr></vt:variant>');
  56. if (isSinglePage) rst.push('<vt:variant><vt:i4>1</vt:i4></vt:variant>');
  57. else rst.push('<vt:variant><vt:i4>' + sheets.length + '</vt:i4></vt:variant>');
  58. rst.push('</vt:vector>');
  59. rst.push('</HeadingPairs>');
  60. rst.push('<TitlesOfParts>');
  61. if (isSinglePage) {
  62. rst.push('<vt:vector size="1" baseType="lpstr">');
  63. rst.push('<vt:lpstr>' + sheets[0].sheetName + '</vt:lpstr>');
  64. } else {
  65. rst.push('<vt:vector size="' + sheets.length + '" baseType="lpstr">');
  66. for (let i = 0; i < sheets.length; i++) {
  67. rst.push('<vt:lpstr>' + sheets[i].sheetName + '</vt:lpstr>');
  68. }
  69. }
  70. rst.push('</vt:vector>');
  71. rst.push('</TitlesOfParts>');
  72. rst.push('<Company>SmartCost</Company>');
  73. rst.push('<LinksUpToDate>false</LinksUpToDate>');
  74. rst.push('<SharedDoc>false</SharedDoc>');
  75. rst.push('<HyperlinksChanged>false</HyperlinksChanged>');
  76. rst.push('<AppVersion>12.0000</AppVersion>');
  77. rst.push('</Properties>');
  78. return rst;
  79. }
  80. function writeCore() {
  81. const rst = [];
  82. const p_fillZero = function(val) {
  83. let rst = val;
  84. if (val < 10) {
  85. rst = '0' + val;
  86. }
  87. return rst;
  88. };
  89. rst.push(dftHeadXml + '\r\n');
  90. rst.push('<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');
  91. rst.push('<dc:creator>SmartCost</dc:creator>');
  92. rst.push('<cp:lastModifiedBy>SmartCost</cp:lastModifiedBy>');
  93. const dt = new Date();
  94. dt.setDate(dt.getDate() - 8 / 24); // it's GMT time, so please add the server offset time ( -8 hours )
  95. const dtStr = dt.getFullYear() + '-' + p_fillZero(dt.getMonth() + 1) + '-' + p_fillZero(dt.getDate()) + 'T' +
  96. p_fillZero(dt.getHours()) + ':' + p_fillZero(dt.getMinutes()) + ':' + p_fillZero(dt.getSeconds()) + 'Z';
  97. rst.push('<dcterms:created xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:created>');
  98. rst.push('<dcterms:modified xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:modified>');
  99. rst.push('</cp:coreProperties>');
  100. return rst;
  101. }
  102. function writeXlWorkBook(sheets, isSinglePage) {
  103. const rst = [];
  104. rst.push(dftHeadXml + '\r\n');
  105. rst.push('<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
  106. rst.push('<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>');
  107. rst.push('<workbookPr defaultThemeVersion="124226"/>');
  108. rst.push('<bookViews><workbookView xWindow="360" yWindow="345" windowWidth="14655" windowHeight="4305"/></bookViews>');
  109. rst.push('<sheets>');
  110. if (isSinglePage) {
  111. rst.push('<sheet name="' + sheets[0].sheetName + '" sheetId="1" r:id="rId1"/>');
  112. } else {
  113. for (let i = 0; i < sheets.length; i++) {
  114. rst.push('<sheet name="' + sheets[i].sheetName + '" sheetId="' + (i + 1) + '" r:id="rId' + (i + 1) + '"/>');
  115. }
  116. }
  117. rst.push('</sheets>');
  118. rst.push('<calcPr calcId="124519"/>');
  119. rst.push('</workbook>');
  120. return rst;
  121. }
  122. function writeXlRels(sheets, isSinglePage) {
  123. const rst = [];
  124. let idx = 1;
  125. rst.push(dftHeadXml + '\r\n');
  126. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  127. if (isSinglePage) {
  128. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>');
  129. idx++;
  130. } else {
  131. for (let i = 0; i < sheets.length; i++) {
  132. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' + (i + 1) + '.xml"/>');
  133. idx++;
  134. }
  135. }
  136. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>');
  137. idx++;
  138. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>');
  139. idx++;
  140. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>');
  141. rst.push('</Relationships>');
  142. return rst;
  143. }
  144. function writeTheme() {
  145. const rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r');
  146. return rst;
  147. }
  148. function writeStyles(stylesObj) {
  149. const rst = [];
  150. rst.push(dftHeadXml + '\r\n');
  151. rst.push('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
  152. // 1. push fonts
  153. rst.push('<fonts count="' + stylesObj.fonts.length + '">');
  154. for (let i = 0; i < stylesObj.fonts.length; i++) {
  155. const font = stylesObj.fonts[i];
  156. rst.push('<font>');
  157. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]])) {
  158. rst.push('<b/>');
  159. }
  160. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]])) {
  161. rst.push('<i/>');
  162. }
  163. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]])) {
  164. rst.push('<u/>');
  165. }
  166. rst.push('<sz val="' + font.size + '"/>');
  167. rst.push('<color indexed="' + font.colorIdx + '"/>');
  168. rst.push('<name val="' + font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] + '"/>');
  169. rst.push('<charset val="' + font.charset + '"/>');
  170. rst.push('</font>');
  171. }
  172. rst.push('</fonts>');
  173. // 2. push default fills
  174. rst.push('<fills count="2"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill></fills>');
  175. // 3. push borders
  176. rst.push('<borders count="' + stylesObj.borders.length + '">');
  177. const private_setBorder = function(border, borderDirection) {
  178. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 0) {
  179. rst.push('<' + borderDirection.toLowerCase() + '/>');
  180. } else {
  181. let bW = 'thin';
  182. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 2) bW = 'medium';
  183. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) > 2) bW = 'thick';
  184. rst.push('<' + borderDirection.toLowerCase() + ' style="' + bW + '"><color indexed="64"/></' + borderDirection.toLowerCase() + '>');
  185. }
  186. };
  187. for (let i = 0; i < stylesObj.borders.length; i++) {
  188. const border = stylesObj.borders[i];
  189. rst.push('<border>');
  190. private_setBorder(border, JV.PROP_LEFT);
  191. private_setBorder(border, JV.PROP_RIGHT);
  192. private_setBorder(border, JV.PROP_TOP);
  193. private_setBorder(border, JV.PROP_BOTTOM);
  194. rst.push('<diagonal />');
  195. rst.push('</border>');
  196. }
  197. rst.push('</borders>');
  198. // 4. push cellStyleXfs
  199. rst.push('<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"><alignment vertical="center"/></xf></cellStyleXfs>');
  200. // 5. push cellXfs
  201. rst.push('<cellXfs count="' + stylesObj.cellXfs.length + '">');
  202. for (let i = 0; i < stylesObj.cellXfs.length; i++) {
  203. const excelStyle = stylesObj.cellXfs[i];
  204. rst.push('<xf numFmtId="0" fontId="' + excelStyle.fontId + '" fillId="0" borderId="' + excelStyle.borderId + '" xfId="0">');
  205. // pageData[JV.NODE_FONT_COLLECTION] excelStyle.fontId
  206. let alignStr = '<alignment';
  207. let textRotation = 0;
  208. let newHorizontal = excelStyle[JV.CONTROL_PROPS[2]];
  209. let newVertical = excelStyle[JV.CONTROL_PROPS[3]];
  210. if (excelStyle[JV.CONTROL_PROPS[5]] && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_CLOSE_OUTPUT]])) {
  211. newVertical = excelStyle[JV.CONTROL_PROPS[5]];
  212. }
  213. if (parseInt(excelStyle.fontAngle) !== 0) {
  214. let tmpH = newHorizontal;
  215. let tmpV = newVertical;
  216. if (excelStyle.fontAngle > 0) {
  217. textRotation = 180;
  218. if (newHorizontal === 'left') {
  219. tmpV = 'top';
  220. } else if (newHorizontal === 'right') {
  221. tmpV = 'bottom';
  222. } else {
  223. tmpV = 'center';
  224. }
  225. if (newVertical === 'top') {
  226. tmpH = 'right';
  227. } else if (newVertical === 'bottom') {
  228. tmpH = 'left';
  229. } else if (newVertical === 'justify') {
  230. tmpH = 'justify';
  231. } else {
  232. tmpH = 'center';
  233. }
  234. } else {
  235. textRotation = 90;
  236. if (newHorizontal === 'left') {
  237. tmpV = 'bottom';
  238. } else if (newHorizontal === 'right') {
  239. tmpV = 'top';
  240. } else {
  241. tmpV = 'center';
  242. }
  243. if (newVertical === 'top') {
  244. tmpH = 'left';
  245. } else if (newVertical === 'bottom') {
  246. tmpH = 'right';
  247. } else {
  248. tmpH = 'center';
  249. }
  250. }
  251. newHorizontal = tmpH;
  252. newVertical = tmpV;
  253. }
  254. alignStr += ' horizontal="' + newHorizontal + '" vertical="' + newVertical + '"';
  255. if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[0]])) {
  256. alignStr += ' shrinkToFit="1"';
  257. }
  258. if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[4]]) && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[6]])) {
  259. alignStr += ' wrapText="1"';
  260. }
  261. if (textRotation !== 0) {
  262. alignStr += ' textRotation="' + textRotation + '"';
  263. }
  264. alignStr += '/>';
  265. rst.push(alignStr);
  266. rst.push('</xf>');
  267. }
  268. rst.push('</cellXfs>');
  269. // 6. others (xfl style / dxfs / tableStyles)
  270. rst.push('<cellStyles count="1"><cellStyle name="常规" xfId="0" builtinId="0"/></cellStyles>');
  271. rst.push('<dxfs count="0"/>');
  272. rst.push('<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>');
  273. rst.push('</styleSheet>');
  274. return rst;
  275. }
  276. function writeSharedString(sharedStrList) {
  277. const rst = [];
  278. const pri_func_write = function(cellVal) {
  279. if (cellVal !== null) {
  280. if ((typeof cellVal === 'string') && cellVal.indexOf(' ') === 0) {
  281. rst.push('<si><t xml:space="preserve">' + cellVal + '</t></si>');
  282. } else {
  283. rst.push('<si><t>' + cellVal + '</t></si>');
  284. }
  285. }
  286. };
  287. if (sharedStrList && sharedStrList.length > 0) {
  288. rst.push(dftHeadXml + '\r\n');
  289. rst.push('<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' + sharedStrList.length + '" uniqueCount="' + sharedStrList.length + '">');
  290. const regExp = new RegExp('<', 'gm');
  291. for (let i = 0; i < sharedStrList.length; i++) {
  292. if (typeof sharedStrList[i] === 'string') {
  293. // 转换特殊字符,如 < , 则需要转义一下
  294. sharedStrList[i] = sharedStrList[i].replace(regExp, '&lt;');
  295. if (sharedStrList[i].indexOf('|') >= 0) {
  296. // rst.push('<si><t>' + sharedStrList[i].split('|').join('\r\n') + '</t></si>');
  297. // rst.push('<si><t>' + sharedStrList[i].split('|').join('\n') + '</t></si>');
  298. pri_func_write(sharedStrList[i].split('|').join('\n'));
  299. } else {
  300. // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
  301. pri_func_write(sharedStrList[i]);
  302. }
  303. // rst.push('<si><t>' + sharedStrList[i].replace('|','\r\n') + '</t></si>');
  304. } else {
  305. // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
  306. pri_func_write(sharedStrList[i]);
  307. }
  308. }
  309. rst.push('</sst>');
  310. }
  311. return rst;
  312. }
  313. function writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands) {
  314. const rst = [];
  315. const private_pushDftFont = function() {
  316. const font = {};
  317. if (!(stylesObj.fonts)) {
  318. stylesObj.fonts = [];
  319. }
  320. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = '宋体'; // font name
  321. font.size = 12;
  322. font.charset = 134;
  323. font.colorIdx = '8';
  324. stylesObj.fonts.push(font);
  325. };
  326. const private_buildFirstDftStyle = function() {
  327. stylesObj.cellXfs = [];
  328. stylesObj.borders = [];
  329. const fontId = 0;
  330. const borderId = 0;
  331. const border = {};
  332. border[JV.PROP_LEFT] = {};
  333. border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
  334. border[JV.PROP_RIGHT] = {};
  335. border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
  336. border[JV.PROP_TOP] = {};
  337. border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
  338. border[JV.PROP_BOTTOM] = {};
  339. border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
  340. stylesObj.borders.push(border);
  341. const cellControl = pageData[JV.NODE_CONTROL_COLLECTION].Default;
  342. const sheetControl = {};
  343. sheetControl.fontId = fontId;
  344. sheetControl.borderId = borderId;
  345. sheetControl.fontAngle = 0;
  346. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  347. sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
  348. }
  349. stylesObj.cellXfs.push(sheetControl);
  350. };
  351. private_pushDftFont();
  352. private_buildFirstDftStyle();
  353. if (isSinglePage) {
  354. rst.push(writeSheet(pageData, null, paperSize, sharedStrList, stylesObj, null));
  355. } else {
  356. for (let i = 0; i < pageData.items.length; i++) {
  357. let appointedMergeBand = null;
  358. if (custSheetMergeBands && custSheetMergeBands.length > i) {
  359. appointedMergeBand = custSheetMergeBands[i];
  360. }
  361. rst.push(writeSheet(pageData, pageData.items[i], paperSize, sharedStrList, stylesObj, appointedMergeBand));
  362. }
  363. }
  364. return rst;
  365. }
  366. function writeSheet(pageData, sheetData, paperSize, sharedStrList, stylesObj, appointedMergeBand) {
  367. const rst = [];
  368. const xPos = [];
  369. let yPos = [];
  370. const yMultiPos = [];
  371. let currentMergeBorder = null;
  372. const headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  373. let currentPageMergePos = null; // 在 JV.PAGING_OPTION_INFINITY 场合应用
  374. const private_pre_analyze_pos = function() {
  375. let cell;
  376. let pos;
  377. const private_array_sort = function(i1, i2) {
  378. let rst = 0;
  379. if (i1 > i2) { rst = 1; } else
  380. if (i1 < i2) rst = -1;
  381. return rst;
  382. };
  383. const self_analyze_sheet_pos = function(theShtData, theXPos, theYPos) {
  384. for (let i = 0; i < theShtData.cells.length; i++) {
  385. cell = theShtData.cells[i];
  386. pos = cell[JV.PROP_AREA][JV.PROP_LEFT];
  387. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  388. pos = cell[JV.PROP_AREA][JV.PROP_RIGHT];
  389. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  390. pos = cell[JV.PROP_AREA][JV.PROP_TOP];
  391. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  392. pos = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
  393. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  394. }
  395. };
  396. xPos.push(0);
  397. if (sheetData) {
  398. // current sheet data
  399. yPos.push(0);
  400. self_analyze_sheet_pos(sheetData, xPos, yPos);
  401. xPos.sort(private_array_sort);
  402. yPos.sort(private_array_sort);
  403. } else {
  404. // total data in one sheet
  405. const marginBottomPos = Math.round((pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1] - parseFloat(pageData[JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_BOTTOM]) / 2.54) * DPI);
  406. for (const shtItemData of pageData.items) {
  407. const tmpPos = [];
  408. tmpPos.push(0);
  409. self_analyze_sheet_pos(shtItemData, xPos, tmpPos);
  410. tmpPos.sort(private_array_sort);
  411. if (marginBottomPos - tmpPos[tmpPos.length - 1] > 10) {
  412. // 此逻辑是为了防止打印跨页(假设有些报表模板高度设置离底部margin还好远,导出excel后预览时会发现跨页现象(即下一页的某几行数据会挪到前一页来预览))
  413. tmpPos.push(marginBottomPos - 10);
  414. }
  415. yMultiPos.push(tmpPos);
  416. }
  417. xPos.sort(private_array_sort);
  418. yPos = yMultiPos[0];
  419. }
  420. };
  421. const private_getCellIdxStr = function(idx) {
  422. let rst = 'A';
  423. if (idx < 26) {
  424. rst = headerStr[idx];
  425. } else if (idx < 26 * 26 + 26) {
  426. const ti = Math.floor(idx / 26);
  427. const tj = idx % 26;
  428. rst = headerStr[ti - 1] + headerStr[tj];
  429. } else if (idx < 26 * 26 * 26 + 26) {
  430. const ti = Math.floor(idx / (26 * 26));
  431. const tj = Math.floor((idx - ti * 26 * 26) / 26);
  432. const tk = idx % 26;
  433. rst = headerStr[ti - 1] + headerStr[tj - 1] + headerStr[tk];
  434. }
  435. return rst;
  436. };
  437. const private_getSharedStrIdx = function(val) {
  438. let strVal = val;
  439. if (val === null || val === undefined || (typeof val === 'number' && isNaN(val))) {
  440. strVal = '';
  441. }
  442. let rst = sharedStrList.indexOf(strVal);
  443. if (rst < 0) {
  444. sharedStrList.push(strVal);
  445. rst = sharedStrList.length - 1;
  446. }
  447. return rst;
  448. };
  449. const private_getFontId = function(cell) {
  450. let rst = 0;
  451. let hasFont = false;
  452. if (!(stylesObj.fonts)) {
  453. stylesObj.fonts = [];
  454. // for (let i = 0; i < sheetData.font_collection)
  455. }
  456. let sheetFont = null;
  457. if (typeof cell[JV.PROP_FONT] === 'string') {
  458. sheetFont = pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]];
  459. } else {
  460. sheetFont = cell[JV.PROP_FONT];
  461. }
  462. for (let i = 0; i < stylesObj.fonts.length; i++) {
  463. const font = stylesObj.fonts[i];
  464. if (sheetFont) {
  465. if (font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] && font.size === Math.floor(sheetFont[JV.FONT_PROPS[1]] * 3 / 4)
  466. && font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]] && font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]]
  467. && font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]) {
  468. hasFont = true;
  469. rst = i;
  470. break;
  471. }
  472. } else {
  473. break;
  474. }
  475. }
  476. if (!hasFont) {
  477. const font = {};
  478. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]]; // font name
  479. // font.size = Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
  480. font.size = Math.floor(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
  481. font.charset = 134;
  482. font.colorIdx = '8';
  483. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]]; // font bold
  484. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]]; // font italic
  485. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]; // font underline
  486. stylesObj.fonts.push(font);
  487. rst = stylesObj.fonts.length - 1;
  488. }
  489. return rst;
  490. };
  491. let private_checkBorder = function(cell, border, sheetBorder) {
  492. let rst = true, borderLineWidths = [], sheetBorderLineWidths = [];
  493. borderLineWidths.push(border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]);
  494. borderLineWidths.push(border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]);
  495. borderLineWidths.push(border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]);
  496. borderLineWidths.push(border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]);
  497. if (sheetBorder[JV.PROP_LEFT] && sheetBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]) {
  498. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true));
  499. } else {
  500. sheetBorderLineWidths.push(0);
  501. }
  502. if (sheetBorder[JV.PROP_RIGHT] && sheetBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]) {
  503. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true));
  504. } else {
  505. sheetBorderLineWidths.push(0);
  506. }
  507. if (sheetBorder[JV.PROP_TOP] && sheetBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]) {
  508. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false));
  509. } else {
  510. sheetBorderLineWidths.push(0);
  511. }
  512. if (sheetBorder[JV.PROP_BOTTOM] && sheetBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]) {
  513. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false));
  514. } else {
  515. sheetBorderLineWidths.push(0);
  516. }
  517. for (let i = 0; i < 4; i++) {
  518. if (borderLineWidths[i] != sheetBorderLineWidths[i]) {
  519. rst = false;
  520. break;
  521. }
  522. }
  523. return rst;
  524. };
  525. let private_chkAndGetMergeLine = function(cell, sheetBorder, borderStr, needFurtherChk) {
  526. let rst = 0,
  527. // mergeBorder = (sheetData)?sheetData[JV.PROP_PAGE_MERGE_BORDER]:pageData[JV.BAND_PROP_MERGE_BAND],
  528. mergeBorder = currentMergeBorder,
  529. mergeBand = pageData[JV.BAND_PROP_MERGE_BAND]
  530. ;
  531. if (appointedMergeBand !== null) {
  532. mergeBand = appointedMergeBand;
  533. }
  534. if (sheetBorder[borderStr] && sheetBorder[borderStr][JV.PROP_LINE_WEIGHT] !== undefined) {
  535. rst = sheetBorder[borderStr][JV.PROP_LINE_WEIGHT];
  536. }
  537. if (currentPageMergePos) {
  538. let side = currentPageMergePos[borderStr];
  539. if (side.indexOf(cell[JV.PROP_AREA][borderStr]) >= 0) {
  540. if (needFurtherChk) {
  541. let topSide = currentPageMergePos[JV.PROP_TOP];
  542. let bottomSide = currentPageMergePos[JV.PROP_BOTTOM];
  543. for (let i = 0; i < topSide.length; i++) {
  544. if (cell[JV.PROP_AREA][JV.PROP_TOP] >= topSide[i]) {
  545. if (cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= bottomSide[i]) {
  546. let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  547. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  548. break;
  549. }
  550. } else {
  551. break;
  552. }
  553. }
  554. } else {
  555. let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  556. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  557. }
  558. }
  559. } else {
  560. if (cell[JV.PROP_AREA][borderStr] === mergeBorder[borderStr]) {
  561. let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  562. if (needFurtherChk) {
  563. if (cell[JV.PROP_AREA][JV.PROP_TOP] >= mergeBorder[JV.PROP_TOP] &&
  564. cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= mergeBorder[JV.PROP_BOTTOM]) {
  565. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  566. }
  567. } else {
  568. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  569. }
  570. }
  571. }
  572. return parseInt(rst);
  573. };
  574. let private_getIniBorder = function() {
  575. let rst = {};
  576. rst[JV.PROP_LEFT] = {};
  577. rst[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
  578. rst[JV.PROP_RIGHT] = {};
  579. rst[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
  580. rst[JV.PROP_TOP] = {};
  581. rst[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
  582. rst[JV.PROP_BOTTOM] = {};
  583. rst[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
  584. return rst;
  585. };
  586. let private_getBorderId = function(cell) {
  587. let rst = 0, hasBorder = false;
  588. if (!(stylesObj.borders)) {
  589. stylesObj.borders = [];
  590. }
  591. let sheetBorder = pageData[JV.NODE_STYLE_COLLECTION][cell.style];
  592. let mergedBorder = private_getIniBorder();
  593. mergedBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true);
  594. mergedBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true);
  595. mergedBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false);
  596. mergedBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false);
  597. for (let i = 0; i < stylesObj.borders.length; i++) {
  598. let border = stylesObj.borders[i];
  599. if (private_checkBorder(cell, border, mergedBorder)) {
  600. hasBorder = true;
  601. rst = i;
  602. break;
  603. }
  604. }
  605. if (!hasBorder) {
  606. stylesObj.borders.push(mergedBorder);
  607. rst = stylesObj.borders.length - 1;
  608. }
  609. return rst;
  610. };
  611. let private_checkControl = function(cellControl, sheetControl) {
  612. let rst = true;
  613. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  614. if (cellControl[JV.CONTROL_PROPS[i]] != sheetControl[JV.CONTROL_PROPS[i]]) {
  615. rst = false;
  616. break;
  617. }
  618. }
  619. return rst;
  620. };
  621. let private_getStyleId = function(cell) {
  622. let rst = 1, hasStyle = false;
  623. if (!(stylesObj.cellXfs)) stylesObj.cellXfs = [];
  624. let fontId = private_getFontId(cell);
  625. let fontAngle = 0;
  626. if (typeof cell[JV.PROP_FONT] === "string") {
  627. fontAngle = parseInt(pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]].FontAngle);
  628. } else {
  629. fontAngle = parseInt(cell[JV.PROP_FONT].FontAngle);
  630. }
  631. let borderId = private_getBorderId(cell);
  632. let cellControl = null;
  633. if (typeof cell[JV.PROP_CONTROL] === "string") {
  634. cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
  635. } else {
  636. cellControl = cell[JV.PROP_CONTROL];
  637. }
  638. for (let i = 0; i < stylesObj.cellXfs.length; i++) {
  639. let sheetControl = stylesObj.cellXfs[i];
  640. if (sheetControl.fontId === fontId && sheetControl.borderId === borderId) {
  641. if (private_checkControl(cellControl, sheetControl)) {
  642. rst = i;
  643. hasStyle = true;
  644. break;
  645. }
  646. }
  647. }
  648. if (!hasStyle) {
  649. let sheetControl = {};
  650. sheetControl.fontId = fontId;
  651. sheetControl.borderId = borderId;
  652. sheetControl.fontAngle = fontAngle;
  653. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  654. sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
  655. }
  656. stylesObj.cellXfs.push(sheetControl);
  657. rst = stylesObj.cellXfs.length - 1;
  658. }
  659. return rst;
  660. };
  661. let private_setCols = function(){
  662. //remark: 1 excel unit width = 2.117 mm
  663. rst.push('<cols>');
  664. let w = 0;
  665. for (let i = 1; i < xPos.length - 1; i++) {
  666. w = 1.0 * (xPos[i + 1] - xPos[i]) / DPI * 25.4 / 2.117;
  667. w = Math.round(w * 1000) / 1000;
  668. rst.push('<col min="' + i +'" max="' + i +'" width="' + w + '" customWidth="1"/>');
  669. }
  670. rst.push('<col min="' + (xPos.length - 1) +'" max="' + (xPos.length - 1) +'" width="' + 10 + '" customWidth="1"/>');
  671. rst.push('</cols>');
  672. };
  673. let private_setMergedCells = function() {
  674. let cell, idxR, idxL, idxT, idxB, cnt = 0;
  675. rst.push('<mergeCells count="?">');
  676. let startIdx = rst.length - 1;
  677. let self_setMergedCells = function (theData, theYPos, offsetY) {
  678. for (let i = 0; i < theData.cells.length; i++) {
  679. cell = theData.cells[i];
  680. idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
  681. idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
  682. idxB = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
  683. idxT = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_TOP]);
  684. if (idxR - idxL > 1 || idxB - idxT > 1) {
  685. rst.push('<mergeCell ref="' + private_getCellIdxStr(idxL - 1) + (idxT + offsetY) + ':' + private_getCellIdxStr(idxR - 2) + (idxB - 1 + offsetY) + '"/>');
  686. cnt++;
  687. }
  688. }
  689. }
  690. if (sheetData) {
  691. self_setMergedCells(sheetData, yPos, 0);
  692. } else {
  693. let osY = 0;
  694. for (let i = 0; i < pageData.items.length; i++) {
  695. let shtItemData = pageData.items[i];
  696. let tmpPos = yMultiPos[i];
  697. self_setMergedCells(shtItemData, tmpPos, osY);
  698. osY += tmpPos.length - 2;
  699. }
  700. }
  701. rst[startIdx] = '<mergeCells count="' + cnt + '">';
  702. rst.push('</mergeCells>');
  703. };
  704. let private_setSheetData = function(){
  705. //remark: 1 excel unit height = 0.3612 mm
  706. rst.push('<sheetData>');
  707. let spanX = xPos.length - 2, cellIdx = 0, h = 0
  708. ;
  709. let self_setDataEx = function (theShtData, theYPos, offsetY) {
  710. let rows = [];
  711. //1. build full set of blank rows/cells
  712. for (let i = 1; i < theYPos.length - 1; i++) {
  713. let rowObj = {};
  714. h = (theYPos[i+1] - theYPos[i]) / DPI * 25.4 / 0.3612;
  715. h = Math.round(h * 1000) / 1000;
  716. rowObj.height = h;
  717. rowObj.r = i + offsetY;
  718. rowObj.items = [];
  719. rows.push(rowObj);
  720. for (let j = 1; j < xPos.length - 1; j++) {
  721. let colIdxStr = private_getCellIdxStr(j - 1);
  722. let cellObj = {};
  723. cellObj.r = colIdxStr + (i + offsetY);
  724. cellObj.s = 0;
  725. cellObj.isBlank = true;
  726. rows[i - 1].items.push(cellObj);
  727. }
  728. }
  729. //2. then fill up the cell style-ids and values
  730. let rowIdx1 = 0, colIdx1 = 0, rowIdx2 = 0, colIdx2 = 0, colIdxStr = '';
  731. for (let cIdx = 0; cIdx < theShtData.cells.length; cIdx++) {
  732. let styleIdx = private_getStyleId(theShtData.cells[cIdx]);
  733. //colIdxStr = private_getCellIdxStr(j - 1);
  734. rowIdx1 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_TOP]);
  735. colIdx1 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_LEFT]);
  736. let cellObj = rows[rowIdx1 - 1].items[colIdx1 - 1];
  737. cellObj.s = styleIdx;
  738. cellObj.isBlank = false;
  739. if (!(strUtil.isEmptyString(theShtData.cells[cIdx][JV.PROP_VALUE]))) {
  740. let valIdx = private_getSharedStrIdx(theShtData.cells[cIdx][JV.PROP_VALUE]);
  741. cellObj.v = valIdx;
  742. }
  743. rowIdx2 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_BOTTOM]);
  744. colIdx2 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_RIGHT]);
  745. if ((rowIdx2 - rowIdx1 > 1) || (colIdx2 - colIdx1 > 1)) {
  746. for (let i = 0; i < rowIdx2 - rowIdx1; i++) {
  747. for (let j = 0; j < colIdx2 - colIdx1; j++) {
  748. if (i === 0 && j === 0) continue;
  749. cellObj = rows[rowIdx1 - 1 + i].items[colIdx1 - 1 + j];
  750. cellObj.s = styleIdx;
  751. cellObj.isBlank = true;
  752. }
  753. }
  754. }
  755. }
  756. //3. then fill up rst
  757. for (let i = 0; i < rows.length; i++) {
  758. rst.push('<row r="' + (i + 1 + offsetY) + '" spans="1:' + spanX + '" ht="' + rows[i].height + '" customHeight="1">');
  759. for (let j = 0; j < rows[i].items.length; j++) {
  760. let cellObj = rows[i].items[j];
  761. if (cellObj.v === undefined) {
  762. rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '"/>');
  763. } else {
  764. rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '" t="s">');
  765. rst.push('<v>' + cellObj.v + '</v>');
  766. rst.push('</c>');
  767. }
  768. }
  769. rst.push('</row>');
  770. }
  771. //4. maybe need to dispose the memory
  772. //...
  773. };
  774. if (sheetData) {
  775. //current sheet data
  776. currentPageMergePos = sheetData[JV.PAGE_SPECIAL_MERGE_POS];
  777. currentMergeBorder = sheetData[JV.PROP_PAGE_MERGE_BORDER];
  778. self_setDataEx(sheetData, yPos, 0);
  779. } else {
  780. //total data in one sheet
  781. let cnt = 0;
  782. for (let i = 0; i < pageData.items.length; i++) {
  783. let shtItemData = pageData.items[i];
  784. currentPageMergePos = shtItemData[JV.PAGE_SPECIAL_MERGE_POS];
  785. currentMergeBorder = shtItemData[JV.PROP_PAGE_MERGE_BORDER];
  786. let tmpPos = yMultiPos[i];
  787. cellIdx = 0;
  788. self_setDataEx(shtItemData, tmpPos, cnt);
  789. cnt += tmpPos.length - 2;
  790. }
  791. }
  792. rst.push('</sheetData>');
  793. };
  794. private_pre_analyze_pos();
  795. rst.push(dftHeadXml + '\r\n');
  796. rst.push('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
  797. let colStr = private_getCellIdxStr(xPos.length - 3);
  798. rst.push('<dimension ref="A1:' + colStr + '' + yPos.length + '"/>');
  799. rst.push('<sheetViews><sheetView tabSelected="1" workbookViewId="0">');
  800. //rst.push('<selection sqref="A1:' + colStr + '1"/>');
  801. rst.push('<selection sqref="A1:A1"/>');
  802. rst.push('</sheetView></sheetViews>');
  803. rst.push('<sheetFormatPr defaultRowHeight="13.5"/>');
  804. private_setCols();
  805. private_setSheetData();
  806. private_setMergedCells();
  807. rst.push('<phoneticPr fontId="1" type="noConversion"/>');
  808. // rst.push('<pageMargins left="0.315" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
  809. rst.push('<pageMargins left="' + (parseFloat(pageData[JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_LEFT]) * 0.39375) +
  810. '" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
  811. let paperSizeIdx = JV.PAGES_SIZE_STR.indexOf(paperSize);
  812. let pStr = '';
  813. if (paperSizeIdx >= 0) {
  814. pStr = 'paperSize="' + JV.PAGES_SIZE_IDX[paperSizeIdx] + '"';
  815. }
  816. let orientationStr = (pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][0] > pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1])?'landscape':'portrait';
  817. if (currentPageMergePos) {
  818. orientationStr = (currentPageMergePos[JV.NODE_PAGE_SIZE][0] > currentPageMergePos[JV.NODE_PAGE_SIZE][1])?'landscape':'portrait';
  819. }
  820. rst.push('<pageSetup ' + pStr + ' fitToWidth="0" fitToHeight="0" orientation="' + orientationStr + '" />');
  821. rst.push('<headerFooter alignWithMargins="0"/>');
  822. rst.push('</worksheet>');
  823. return rst;
  824. }
  825. function mergeProperties(orgObj, newObj) {
  826. let orgPropArr = [], newPropArr = [];
  827. for (let p in orgObj) {
  828. orgPropArr.push(p);
  829. }
  830. for (let p in newObj) {
  831. newPropArr.push(p);
  832. }
  833. for (let i = 0; i < newPropArr.length; i++) {
  834. if (orgPropArr.indexOf(newPropArr[i]) < 0) {
  835. orgObj[newPropArr[i]] = newObj[newPropArr[i]];
  836. }
  837. }
  838. }
  839. module.exports = {
  840. exportExcel: function (pageData, paperSize, fName, options, custSheetNames, custSheetMergeBands, callback) {
  841. let rptOptions = ({singlePage: false, fileName: 'report'});
  842. if (options === 'true' || options === true) {
  843. rptOptions.singlePage = true;
  844. }
  845. let isSinglePage = rptOptions.singlePage;
  846. let sheets = [];
  847. if (isSinglePage) {
  848. sheets.push({sheetName: '全部页'});
  849. } else {
  850. if (custSheetNames && custSheetNames.length === pageData.items.length) {
  851. for (let i = 0; i < pageData.items.length; i++) {
  852. sheets.push({sheetName: custSheetNames[i]});
  853. }
  854. } else {
  855. for (let i = 0; i < pageData.items.length; i++) {
  856. sheets.push({sheetName: '第' + (i + 1) + '页'});
  857. }
  858. }
  859. }
  860. //1.
  861. let file = '[Content_Types].xml';
  862. let data = writeContentTypes(sheets, isSinglePage);
  863. let zip = new JSZip();
  864. zip.file(file, data.join(''), {compression: 'DEFLATE'});
  865. //2.
  866. let zip_rels = zip.folder('_rels');
  867. file = '.rels';
  868. data = writeRootRels();
  869. zip_rels.file(file, data.join(''), {compression: 'DEFLATE'});
  870. //3.
  871. let zip_docProps = zip.folder('docProps');
  872. file = 'app.xml';
  873. data = writeApp(sheets, isSinglePage);
  874. zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
  875. file = 'core.xml';
  876. data = writeCore();
  877. zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
  878. //4.
  879. let zip_xl = zip.folder('xl');
  880. file = 'workbook.xml';
  881. data = writeXlWorkBook(sheets, isSinglePage);
  882. zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
  883. let zip_rels2 = zip_xl.folder('_rels');
  884. file = 'workbook.xml.rels';
  885. data = writeXlRels(sheets, isSinglePage);
  886. zip_rels2.file(file, data.join(''), {compression: 'DEFLATE'});
  887. //5.
  888. let zip_theme = zip_xl.folder('theme');
  889. file = 'theme1.xml';
  890. data = writeTheme();
  891. zip_theme.file(file, data, {compression: 'DEFLATE'});
  892. //6.
  893. let zip_worksheets = zip_xl.folder('worksheets');
  894. let sharedStrList = [], stylesObj = {};
  895. data = writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands);
  896. if (isSinglePage) {
  897. for (let i = 0; i < 1; i++) {
  898. file = 'sheet' + (i + 1) + '.xml';
  899. zip_worksheets.file(file, data[i].join(''), {compression: 'DEFLATE'});
  900. }
  901. } else {
  902. for (let i = 0; i < data.length; i++) {
  903. file = 'sheet' + (i + 1) + '.xml';
  904. zip_worksheets.file(file, data[i].join(''), {compression: 'DEFLATE'});
  905. }
  906. }
  907. file = 'sharedStrings.xml';
  908. data = writeSharedString(sharedStrList);
  909. zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
  910. file = 'styles.xml';
  911. data = writeStyles(stylesObj);
  912. zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
  913. if (fName) {
  914. // let newName = '' + (new Date()).valueOf();
  915. let newName = uuidV1();
  916. // console.log('current path: ' + __dirname);
  917. // const filePath = this.app.baseDir + '/app/public/download/';
  918. zip.generateNodeStream({type:'nodebuffer',streamFiles:true})
  919. .pipe(fs.createWriteStream(__dirname.slice(0, __dirname.length - 13) + '/public/download/' + newName + '.xlsx'))
  920. .on('finish', function () {
  921. // JSZip generates a readable stream with a "end" event,
  922. // but is piped here in a writable stream which emits a "finish" event.
  923. console.log(newName + ".xlsx was written.");
  924. if (callback) callback(null, newName);
  925. }
  926. );
  927. } else {
  928. //return zip.generateNodeStream({type:'nodebuffer',streamFiles:true});
  929. return zip;
  930. }
  931. },
  932. exportExcelInOneBook: function (pageDataArray, paperSize, fName, callback) {
  933. let me = this, newPageData = {};
  934. //1. 重新编排一下数据,把一份报表的pageData合并到一起作为一个Sheet输出(需要重新调整数据纵向坐标),多份报表数据就形成多个Sheet
  935. // -- 简单来说,就是重新包装数据
  936. try {
  937. // 1.1 newPageData外围属性
  938. let newContrl = {}, newFont = {}, newStyle = {}, custMergeBands = [];
  939. for (let i = 0; i < pageDataArray.length; i++) {
  940. mergeProperties(newContrl, pageDataArray[i][JV.NODE_CONTROL_COLLECTION]);
  941. mergeProperties(newFont, pageDataArray[i][JV.NODE_FONT_COLLECTION]);
  942. mergeProperties(newStyle, pageDataArray[i][JV.NODE_STYLE_COLLECTION]);
  943. }
  944. newPageData[JV.NODE_CONTROL_COLLECTION] = newContrl;
  945. newPageData[JV.NODE_FONT_COLLECTION] = newFont;
  946. newPageData[JV.NODE_STYLE_COLLECTION] = newStyle;
  947. newPageData[JV.NODE_PAGE_INFO] = pageDataArray[0][JV.NODE_PAGE_INFO];
  948. newPageData[JV.BAND_PROP_MERGE_BAND] = pageDataArray[0][JV.BAND_PROP_MERGE_BAND];
  949. // 1.2 重新设置pageDataArray的各个cell的Top/Bottom坐标
  950. let sheetNames = [], newPagePos = [];
  951. for (let i = 0; i < pageDataArray.length; i++) {
  952. let offsetY = 0;
  953. let mergeBand = {};
  954. custMergeBands.push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND]);
  955. //备注:不同的报表有可能有不同的边框,如封面表就是无边框的
  956. mergeBand[JV.PROP_LEFT] = [];
  957. mergeBand[JV.PROP_RIGHT] = [];
  958. mergeBand[JV.PROP_TOP] = [];
  959. mergeBand[JV.PROP_BOTTOM] = [];
  960. newPagePos.push(mergeBand);
  961. mergeBand[JV.PROP_LEFT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_LEFT]);
  962. mergeBand[JV.PROP_RIGHT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_RIGHT]);
  963. sheetNames.push(pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_MAIN_INFO_RPT_NAME]);
  964. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  965. let maxY = 0, minY = 100000;
  966. if (pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS]) {
  967. let pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_TOP][0] + offsetY;
  968. mergeBand[JV.PROP_TOP].push(pos);
  969. pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_BOTTOM][0] + offsetY;
  970. mergeBand[JV.PROP_BOTTOM].push(pos);
  971. } else if (pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER]) {
  972. let pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_TOP] + offsetY;
  973. mergeBand[JV.PROP_TOP].push(pos);
  974. pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_BOTTOM] + offsetY;
  975. mergeBand[JV.PROP_BOTTOM].push(pos);
  976. } else {
  977. mergeBand[JV.PROP_TOP].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_TOP] + offsetY);
  978. mergeBand[JV.PROP_BOTTOM].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_BOTTOM] + offsetY);
  979. }
  980. for (let k = 0; k < pageDataArray[i].items[j].cells.length; k++) {
  981. if (maxY < pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM]) {
  982. maxY = pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM];
  983. }
  984. if (minY > pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP]) {
  985. minY = pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP];
  986. }
  987. pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
  988. pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP] += offsetY;
  989. }
  990. let bottomGap = Math.round( (pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1] - parseFloat(pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_BOTTOM]) / 2.54 ) * DPI) - maxY;
  991. offsetY += (maxY - minY);
  992. if (bottomGap > 10) {
  993. offsetY += (bottomGap - 10);
  994. }
  995. }
  996. }
  997. //2. newPageData的items属性
  998. newPageData.items = [];
  999. for (let i = 0; i < pageDataArray.length; i++) {
  1000. let pageItem = {};
  1001. pageItem[JV.PROP_PAGE_SEQ] = i + 1;
  1002. pageItem[JV.PROP_CELLS] = [];
  1003. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1004. for (let k = 0; k < pageDataArray[i].items[j].cells.length; k++) {
  1005. pageItem[JV.PROP_CELLS].push(pageDataArray[i].items[j].cells[k]);
  1006. }
  1007. }
  1008. newPagePos[i][JV.NODE_PAGE_SIZE] = pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE];
  1009. pageItem[JV.PAGE_SPECIAL_MERGE_POS] = newPagePos[i];
  1010. newPageData.items.push(pageItem);
  1011. }
  1012. //3. everything is ok, then call me
  1013. me.exportExcel(newPageData, paperSize, fName, 'false', sheetNames, custMergeBands, callback);
  1014. // fsUtil.writeObjToFile(newPageData, 'D:/GitHome/ConstructionOperation/tmp/combinedHeader.js');
  1015. } catch (e) {
  1016. console.log(e);
  1017. }
  1018. }
  1019. }