rpt_excel_util.js 46 KB

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