rpt_excel_util.js 76 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608
  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. const atob = require('atob');
  15. function writeContentTypes(sheets, isSinglePage, hasSignature) {
  16. const rst = [];
  17. rst.push(dftHeadXml + '\r\n');
  18. rst.push('<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">');
  19. rst.push('<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>');
  20. rst.push('<Default Extension="xml" ContentType="application/xml"/>');
  21. if (hasSignature) {
  22. rst.push('<Default Extension="png" ContentType="image/png"/>');
  23. }
  24. rst.push('<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>');
  25. rst.push('<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>');
  26. rst.push('<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>');
  27. rst.push('<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>');
  28. rst.push('<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>');
  29. if (isSinglePage) {
  30. rst.push('<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
  31. if (hasSignature) {
  32. rst.push('<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>');
  33. }
  34. } else {
  35. for (let i = 0; i < sheets.length; i++) {
  36. rst.push('<Override PartName="/xl/worksheets/sheet' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
  37. if (hasSignature) {
  38. rst.push('<Override PartName="/xl/drawings/drawing' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>');
  39. }
  40. }
  41. }
  42. rst.push('<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>');
  43. rst.push('</Types>');
  44. return rst;
  45. }
  46. function writeRootRels() {
  47. const rst = [];
  48. rst.push(dftHeadXml + '\r\n');
  49. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  50. rst.push('<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>');
  51. rst.push('<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>');
  52. rst.push('<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>');
  53. rst.push('</Relationships>');
  54. return rst;
  55. }
  56. function writeApp(sheets, isSinglePage) {
  57. const rst = [];
  58. rst.push(dftHeadXml + '\r\n');
  59. rst.push('<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">');
  60. rst.push('<Application>Microsoft Excel</Application>');
  61. rst.push('<DocSecurity>0</DocSecurity>');
  62. rst.push('<ScaleCrop>false</ScaleCrop>');
  63. rst.push('<HeadingPairs>');
  64. rst.push('<vt:vector size="2" baseType="variant">');
  65. rst.push('<vt:variant><vt:lpstr>工作表</vt:lpstr></vt:variant>');
  66. if (isSinglePage) rst.push('<vt:variant><vt:i4>1</vt:i4></vt:variant>');
  67. else rst.push('<vt:variant><vt:i4>' + sheets.length + '</vt:i4></vt:variant>');
  68. rst.push('</vt:vector>');
  69. rst.push('</HeadingPairs>');
  70. rst.push('<TitlesOfParts>');
  71. if (isSinglePage) {
  72. rst.push('<vt:vector size="1" baseType="lpstr">');
  73. rst.push('<vt:lpstr>' + sheets[0].sheetName + '</vt:lpstr>');
  74. } else {
  75. rst.push('<vt:vector size="' + sheets.length + '" baseType="lpstr">');
  76. for (let i = 0; i < sheets.length; i++) {
  77. rst.push('<vt:lpstr>' + sheets[i].sheetName + '</vt:lpstr>');
  78. }
  79. }
  80. rst.push('</vt:vector>');
  81. rst.push('</TitlesOfParts>');
  82. rst.push('<Company>SmartCost</Company>');
  83. rst.push('<LinksUpToDate>false</LinksUpToDate>');
  84. rst.push('<SharedDoc>false</SharedDoc>');
  85. rst.push('<HyperlinksChanged>false</HyperlinksChanged>');
  86. rst.push('<AppVersion>12.0000</AppVersion>');
  87. rst.push('</Properties>');
  88. return rst;
  89. }
  90. function writeCore() {
  91. const rst = [];
  92. const p_fillZero = function(val) {
  93. let rst = val;
  94. if (val < 10) {
  95. rst = '0' + val;
  96. }
  97. return rst;
  98. };
  99. rst.push(dftHeadXml + '\r\n');
  100. 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">');
  101. rst.push('<dc:creator>SmartCost</dc:creator>');
  102. rst.push('<cp:lastModifiedBy>SmartCost</cp:lastModifiedBy>');
  103. const dt = new Date();
  104. dt.setDate(dt.getDate() - 8 / 24); // it's GMT time, so please add the server offset time ( -8 hours )
  105. const dtStr = dt.getFullYear() + '-' + p_fillZero(dt.getMonth() + 1) + '-' + p_fillZero(dt.getDate()) + 'T' +
  106. p_fillZero(dt.getHours()) + ':' + p_fillZero(dt.getMinutes()) + ':' + p_fillZero(dt.getSeconds()) + 'Z';
  107. rst.push('<dcterms:created xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:created>');
  108. rst.push('<dcterms:modified xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:modified>');
  109. rst.push('</cp:coreProperties>');
  110. return rst;
  111. }
  112. function writeXlWorkBook(sheets, isSinglePage) {
  113. const rst = [];
  114. rst.push(dftHeadXml + '\r\n');
  115. rst.push('<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
  116. rst.push('<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>');
  117. rst.push('<workbookPr defaultThemeVersion="124226"/>');
  118. rst.push('<bookViews><workbookView xWindow="360" yWindow="345" windowWidth="14655" windowHeight="4305"/></bookViews>');
  119. rst.push('<sheets>');
  120. if (isSinglePage) {
  121. rst.push('<sheet name="' + sheets[0].sheetName + '" sheetId="1" r:id="rId1"/>');
  122. } else {
  123. for (let i = 0; i < sheets.length; i++) {
  124. rst.push('<sheet name="' + sheets[i].sheetName + '" sheetId="' + (i + 1) + '" r:id="rId' + (i + 1) + '"/>');
  125. }
  126. }
  127. rst.push('</sheets>');
  128. rst.push('<calcPr calcId="124519"/>');
  129. rst.push('</workbook>');
  130. return rst;
  131. }
  132. function writeXlRels(sheets, isSinglePage) {
  133. const rst = [];
  134. let idx = 1;
  135. rst.push(dftHeadXml + '\r\n');
  136. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  137. if (isSinglePage) {
  138. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>');
  139. idx++;
  140. } else {
  141. for (let i = 0; i < sheets.length; i++) {
  142. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' + (i + 1) + '.xml"/>');
  143. idx++;
  144. }
  145. }
  146. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>');
  147. idx++;
  148. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>');
  149. idx++;
  150. rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>');
  151. rst.push('</Relationships>');
  152. return rst;
  153. }
  154. function writeTheme() {
  155. const rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r');
  156. return rst;
  157. }
  158. function writeStyles(stylesObj, hasSignature) {
  159. const rst = [];
  160. rst.push(dftHeadXml + '\r\n');
  161. rst.push('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
  162. // 1. push fonts
  163. rst.push('<fonts count="' + stylesObj.fonts.length + '">');
  164. for (let i = 0; i < stylesObj.fonts.length; i++) {
  165. const font = stylesObj.fonts[i];
  166. rst.push('<font>');
  167. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]])) {
  168. rst.push('<b/>');
  169. }
  170. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]])) {
  171. rst.push('<i/>');
  172. }
  173. if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]])) {
  174. rst.push('<u/>');
  175. }
  176. rst.push('<sz val="' + font.size + '"/>');
  177. rst.push('<color indexed="' + font.colorIdx + '"/>');
  178. rst.push('<name val="' + font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] + '"/>');
  179. rst.push('<charset val="' + font.charset + '"/>');
  180. rst.push('</font>');
  181. }
  182. rst.push('</fonts>');
  183. // 2. push default fills
  184. rst.push('<fills count="2"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill></fills>');
  185. // 3. push borders
  186. rst.push('<borders count="' + stylesObj.borders.length + '">');
  187. const private_setBorder = function(border, borderDirection) {
  188. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 0) {
  189. rst.push('<' + borderDirection.toLowerCase() + '/>');
  190. } else {
  191. let bW = 'thin';
  192. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 2) bW = 'medium';
  193. if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) > 2) bW = 'thick';
  194. rst.push('<' + borderDirection.toLowerCase() + ' style="' + bW + '"><color indexed="64"/></' + borderDirection.toLowerCase() + '>');
  195. }
  196. };
  197. for (let i = 0; i < stylesObj.borders.length; i++) {
  198. const border = stylesObj.borders[i];
  199. rst.push('<border>');
  200. private_setBorder(border, JV.PROP_LEFT);
  201. private_setBorder(border, JV.PROP_RIGHT);
  202. private_setBorder(border, JV.PROP_TOP);
  203. private_setBorder(border, JV.PROP_BOTTOM);
  204. rst.push('<diagonal />');
  205. rst.push('</border>');
  206. }
  207. rst.push('</borders>');
  208. // 4. push cellStyleXfs
  209. rst.push('<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"><alignment vertical="center"/></xf></cellStyleXfs>');
  210. // 5. push cellXfs
  211. rst.push('<cellXfs count="' + stylesObj.cellXfs.length + '">');
  212. for (let i = 0; i < stylesObj.cellXfs.length; i++) {
  213. const excelStyle = stylesObj.cellXfs[i];
  214. rst.push('<xf numFmtId="0" fontId="' + excelStyle.fontId + '" fillId="0" borderId="' + excelStyle.borderId + '" xfId="0">');
  215. // pageData[JV.NODE_FONT_COLLECTION] excelStyle.fontId
  216. let alignStr = '<alignment';
  217. let textRotation = 0;
  218. let newHorizontal = excelStyle[JV.CONTROL_PROPS[2]];
  219. let newVertical = excelStyle[JV.CONTROL_PROPS[3]];
  220. if (excelStyle[JV.CONTROL_PROPS[5]] && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_CLOSE_OUTPUT]])) {
  221. newVertical = excelStyle[JV.CONTROL_PROPS[5]];
  222. }
  223. if (parseInt(excelStyle.fontAngle) !== 0) {
  224. let tmpH = newHorizontal;
  225. let tmpV = newVertical;
  226. if (excelStyle.fontAngle > 0) {
  227. textRotation = 180;
  228. if (newHorizontal === 'left') {
  229. tmpV = 'top';
  230. } else if (newHorizontal === 'right') {
  231. tmpV = 'bottom';
  232. } else {
  233. tmpV = 'center';
  234. }
  235. if (newVertical === 'top') {
  236. tmpH = 'right';
  237. } else if (newVertical === 'bottom') {
  238. tmpH = 'left';
  239. } else if (newVertical === 'justify') {
  240. tmpH = 'justify';
  241. } else {
  242. tmpH = 'center';
  243. }
  244. } else {
  245. textRotation = 90;
  246. if (newHorizontal === 'left') {
  247. tmpV = 'bottom';
  248. } else if (newHorizontal === 'right') {
  249. tmpV = 'top';
  250. } else {
  251. tmpV = 'center';
  252. }
  253. if (newVertical === 'top') {
  254. tmpH = 'left';
  255. } else if (newVertical === 'bottom') {
  256. tmpH = 'right';
  257. } else {
  258. tmpH = 'center';
  259. }
  260. }
  261. newHorizontal = tmpH;
  262. newVertical = tmpV;
  263. }
  264. alignStr += ' horizontal="' + newHorizontal + '" vertical="' + newVertical + '"';
  265. if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[0]])) {
  266. alignStr += ' shrinkToFit="1"';
  267. }
  268. if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[4]]) && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[6]])) {
  269. alignStr += ' wrapText="1"';
  270. }
  271. if (textRotation !== 0) {
  272. alignStr += ' textRotation="' + textRotation + '"';
  273. }
  274. alignStr += '/>';
  275. rst.push(alignStr);
  276. rst.push('</xf>');
  277. }
  278. rst.push('</cellXfs>');
  279. // 6. others (xfl style / dxfs / tableStyles)
  280. rst.push('<cellStyles count="1"><cellStyle name="常规" xfId="0" builtinId="0"/></cellStyles>');
  281. rst.push('<dxfs count="0"/>');
  282. rst.push('<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>');
  283. if (hasSignature) {
  284. rst.push('<extLst>');
  285. rst.push('<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">');
  286. rst.push('<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>');
  287. rst.push('</ext>');
  288. rst.push('<ext uri="{9260A510-F301-46a8-8635-F512D64BE5F5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">');
  289. rst.push('<x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1"/>');
  290. rst.push('</ext>');
  291. rst.push('</extLst>');
  292. }
  293. rst.push('</styleSheet>');
  294. return rst;
  295. }
  296. function writeSharedString(sharedStrList) {
  297. const rst = [];
  298. const pri_func_write = function(cellVal) {
  299. if (cellVal !== null) {
  300. if ((typeof cellVal === 'string') && cellVal.indexOf(' ') === 0) {
  301. rst.push('<si><t xml:space="preserve">' + cellVal + '</t></si>');
  302. } else {
  303. rst.push('<si><t>' + cellVal + '</t></si>');
  304. }
  305. }
  306. };
  307. if (sharedStrList && sharedStrList.length > 0) {
  308. rst.push(dftHeadXml + '\r\n');
  309. rst.push('<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' + sharedStrList.length + '" uniqueCount="' + sharedStrList.length + '">');
  310. const regExp = new RegExp('<', 'gm');
  311. for (let i = 0; i < sharedStrList.length; i++) {
  312. if (typeof sharedStrList[i] === 'string') {
  313. // 转换特殊字符,如 < , 则需要转义一下
  314. sharedStrList[i] = sharedStrList[i].replace(regExp, '&lt;');
  315. if (sharedStrList[i].indexOf('|') >= 0) {
  316. // rst.push('<si><t>' + sharedStrList[i].split('|').join('\r\n') + '</t></si>');
  317. // rst.push('<si><t>' + sharedStrList[i].split('|').join('\n') + '</t></si>');
  318. pri_func_write(sharedStrList[i].split('|').join('\n'));
  319. } else {
  320. // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
  321. pri_func_write(sharedStrList[i]);
  322. }
  323. // rst.push('<si><t>' + sharedStrList[i].replace('|','\r\n') + '</t></si>');
  324. } else {
  325. // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
  326. pri_func_write(sharedStrList[i]);
  327. }
  328. }
  329. rst.push('</sst>');
  330. }
  331. return rst;
  332. }
  333. function writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands, hasSignature, signSheetIdxArr) {
  334. const rst = [];
  335. const private_pushDftFont = function() {
  336. const font = {};
  337. if (!(stylesObj.fonts)) {
  338. stylesObj.fonts = [];
  339. }
  340. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = '宋体'; // font name
  341. font.size = 12;
  342. font.charset = 134;
  343. font.colorIdx = '8';
  344. stylesObj.fonts.push(font);
  345. };
  346. const private_buildFirstDftStyle = function() {
  347. stylesObj.cellXfs = [];
  348. stylesObj.borders = [];
  349. const fontId = 0;
  350. const borderId = 0;
  351. const border = {};
  352. border[JV.PROP_LEFT] = {};
  353. border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
  354. border[JV.PROP_RIGHT] = {};
  355. border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
  356. border[JV.PROP_TOP] = {};
  357. border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
  358. border[JV.PROP_BOTTOM] = {};
  359. border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
  360. stylesObj.borders.push(border);
  361. const cellControl = pageData[JV.NODE_CONTROL_COLLECTION].Default;
  362. const sheetControl = {};
  363. sheetControl.fontId = fontId;
  364. sheetControl.borderId = borderId;
  365. sheetControl.fontAngle = 0;
  366. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  367. sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
  368. }
  369. stylesObj.cellXfs.push(sheetControl);
  370. };
  371. private_pushDftFont();
  372. private_buildFirstDftStyle();
  373. let sheetIdx = 0;
  374. if (isSinglePage) {
  375. rst.push(writeSheet(pageData, null, paperSize, sharedStrList, stylesObj, null, hasSignature, sheetIdx, signSheetIdxArr, true));
  376. } else {
  377. for (let i = 0; i < pageData.items.length; i++) {
  378. let appointedMergeBand = null;
  379. if (custSheetMergeBands && custSheetMergeBands.length > i) {
  380. appointedMergeBand = custSheetMergeBands[i];
  381. }
  382. rst.push(writeSheet(pageData, pageData.items[i], paperSize, sharedStrList, stylesObj, appointedMergeBand, hasSignature, sheetIdx, signSheetIdxArr, i === 0));
  383. sheetIdx++;
  384. }
  385. }
  386. return rst;
  387. }
  388. function getProperSignatureArea(cell, control) {
  389. // 约定默认长宽比例是2:1,图片分辨率是600*300
  390. const rst = [0, 0, 0, 0]; // left, top, right, bottom
  391. if (cell && cell[JV.PROP_AREA]) {
  392. let width = cell[JV.PROP_AREA][JV.PROP_RIGHT] - cell[JV.PROP_AREA][JV.PROP_LEFT],
  393. height = cell[JV.PROP_AREA][JV.PROP_BOTTOM] - cell[JV.PROP_AREA][JV.PROP_TOP];
  394. if (width > height * 2) {
  395. width = height * 2;
  396. } else {
  397. height = width / 2;
  398. }
  399. switch (control[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_HORIZON]]) {
  400. case 'left':
  401. rst[0] = cell[JV.PROP_AREA][JV.PROP_LEFT];
  402. rst[1] = cell[JV.PROP_AREA][JV.PROP_TOP];
  403. rst[2] = rst[0] + width;
  404. rst[3] = rst[1] + height;
  405. break;
  406. case 'right':
  407. rst[2] = cell[JV.PROP_AREA][JV.PROP_RIGHT];
  408. rst[3] = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
  409. rst[0] = rst[2] - width;
  410. rst[1] = rst[3] - height;
  411. break;
  412. default:
  413. // center
  414. rst[0] = (cell[JV.PROP_AREA][JV.PROP_LEFT] + cell[JV.PROP_AREA][JV.PROP_RIGHT] - width) / 2;
  415. rst[1] = cell[JV.PROP_AREA][JV.PROP_TOP];
  416. rst[2] = rst[0] + width;
  417. rst[3] = rst[1] + height;
  418. break;
  419. }
  420. }
  421. // rst[0] = rst[0] + JpcCanvasOutput.offsetX;
  422. // rst[2] = rst[2] + JpcCanvasOutput.offsetX;
  423. // rst[1] = rst[1] + JpcCanvasOutput.offsetY;
  424. // rst[3] = rst[3] + JpcCanvasOutput.offsetY;
  425. return rst;
  426. }
  427. function preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos) {
  428. let cell;
  429. let pos;
  430. const private_array_sort = function(i1, i2) {
  431. return (i1 - i2);
  432. };
  433. const private_set_cell_pos = function(theCell, theXPos, theYPos) {
  434. pos = theCell[JV.PROP_AREA][JV.PROP_LEFT];
  435. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  436. pos = theCell[JV.PROP_AREA][JV.PROP_RIGHT];
  437. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  438. pos = theCell[JV.PROP_AREA][JV.PROP_TOP];
  439. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  440. pos = theCell[JV.PROP_AREA][JV.PROP_BOTTOM];
  441. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  442. };
  443. const private_set_cell_pos2 = function(area, theXPos, theYPos) {
  444. pos = area[0];
  445. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  446. pos = area[2];
  447. if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
  448. pos = area[1];
  449. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  450. pos = area[3];
  451. if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
  452. };
  453. const self_analyze_sheet_pos = function(theShtData, theXPos, theYPos) {
  454. for (let i = 0; i < theShtData[JV.PROP_CELLS].length; i++) {
  455. cell = theShtData[JV.PROP_CELLS][i];
  456. private_set_cell_pos(cell, theXPos, theYPos);
  457. }
  458. for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_CELLS].length; i++) {
  459. cell = theShtData[JV.PROP_SIGNATURE_CELLS][i];
  460. private_set_cell_pos(cell, theXPos, theYPos);
  461. let cellControl;
  462. if (typeof cell[JV.PROP_CONTROL] === 'string') {
  463. cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
  464. } else {
  465. cellControl = cell[JV.PROP_CONTROL];
  466. }
  467. const area = getProperSignatureArea(cell, cellControl);
  468. private_set_cell_pos2(area, theXPos, theYPos);
  469. }
  470. for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_DATE_CELLS].length; i++) {
  471. cell = theShtData[JV.PROP_SIGNATURE_DATE_CELLS][i];
  472. private_set_cell_pos(cell, theXPos, theYPos);
  473. }
  474. for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS].length; i++) {
  475. cell = theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS][i];
  476. private_set_cell_pos(cell, theXPos, theYPos);
  477. }
  478. };
  479. xPos.push(0);
  480. if (sheetData) {
  481. // current sheet data
  482. // console.log('preAnalyzePos not single');
  483. yPos.push(0);
  484. self_analyze_sheet_pos(sheetData, xPos, yPos);
  485. xPos.sort(private_array_sort);
  486. yPos.sort(private_array_sort);
  487. } else {
  488. // total data in one sheet
  489. 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);
  490. // console.log('preAnalyzePos isSingle start');
  491. for (const shtItemData of pageData.items) {
  492. const tmpPos = [];
  493. tmpPos.push(0);
  494. self_analyze_sheet_pos(shtItemData, xPos, tmpPos);
  495. tmpPos.sort(private_array_sort);
  496. if (marginBottomPos - tmpPos[tmpPos.length - 1] > 10) {
  497. // 此逻辑是为了防止打印跨页(假设有些报表模板高度设置离底部margin还好远,导出excel后预览时会发现跨页现象(即下一页的某几行数据会挪到前一页来预览))
  498. tmpPos.push(marginBottomPos - 10);
  499. }
  500. // console.log('preAnalyzePos push to yMultiPos');
  501. yMultiPos.push(tmpPos);
  502. }
  503. xPos.sort(private_array_sort);
  504. yPos.splice(0, yPos.length);
  505. for (const p of yMultiPos[0]) {
  506. yPos.push(p);
  507. }
  508. }
  509. }
  510. function writeSheet(pageData, sheetData, paperSize, sharedStrList, stylesObj, appointedMergeBand, hasSignature, sheetIdx, signSheetIdxArr, isFirstSheet) {
  511. const rst = [];
  512. const xPos = [];
  513. const yPos = [];
  514. const yMultiPos = [];
  515. let currentMergeBorder = null;
  516. const headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  517. let currentPageMergePos = null; // 在 JV.PAGING_OPTION_INFINITY 场合应用
  518. const private_getCellIdxStr = function(idx) {
  519. let rst = 'A';
  520. if (idx < 26) {
  521. rst = headerStr[idx];
  522. } else if (idx < 26 * 26 + 26) {
  523. const ti = Math.floor(idx / 26);
  524. const tj = idx % 26;
  525. rst = headerStr[ti - 1] + headerStr[tj];
  526. } else if (idx < 26 * 26 * 26 + 26) {
  527. const ti = Math.floor(idx / (26 * 26));
  528. const tj = Math.floor((idx - ti * 26 * 26) / 26);
  529. const tk = idx % 26;
  530. rst = headerStr[ti - 1] + headerStr[tj - 1] + headerStr[tk];
  531. }
  532. return rst;
  533. };
  534. const private_getSharedStrIdx = function(val) {
  535. let strVal = val;
  536. if (val === null || val === undefined || (typeof val === 'number' && isNaN(val))) {
  537. strVal = '';
  538. }
  539. let rst = sharedStrList.indexOf(strVal);
  540. if (rst < 0) {
  541. sharedStrList.push(strVal);
  542. rst = sharedStrList.length - 1;
  543. }
  544. return rst;
  545. };
  546. const private_getFontId = function(cell) {
  547. let rst = 0;
  548. let hasFont = false;
  549. if (!(stylesObj.fonts)) {
  550. stylesObj.fonts = [];
  551. // for (let i = 0; i < sheetData.font_collection)
  552. }
  553. let sheetFont = null;
  554. if (typeof cell[JV.PROP_FONT] === 'string') {
  555. sheetFont = pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]];
  556. } else {
  557. sheetFont = cell[JV.PROP_FONT];
  558. }
  559. for (let i = 0; i < stylesObj.fonts.length; i++) {
  560. const font = stylesObj.fonts[i];
  561. if (sheetFont) {
  562. 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)
  563. && 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]]
  564. && font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]) {
  565. hasFont = true;
  566. rst = i;
  567. break;
  568. }
  569. } else {
  570. break;
  571. }
  572. }
  573. if (!hasFont) {
  574. const font = {};
  575. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]]; // font name
  576. // font.size = Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
  577. font.size = Math.floor(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
  578. font.charset = 134;
  579. font.colorIdx = '8';
  580. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]]; // font bold
  581. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]]; // font italic
  582. font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]; // font underline
  583. stylesObj.fonts.push(font);
  584. rst = stylesObj.fonts.length - 1;
  585. }
  586. return rst;
  587. };
  588. const private_chkAndGetMergeLine = function(cell, sheetBorder, borderStr, needFurtherChk) {
  589. let rst = 0;
  590. const mergeBorder = currentMergeBorder;
  591. let mergeBand = pageData[JV.BAND_PROP_MERGE_BAND];
  592. if (appointedMergeBand !== null) {
  593. mergeBand = appointedMergeBand;
  594. }
  595. if (sheetBorder[borderStr] && sheetBorder[borderStr][JV.PROP_LINE_WEIGHT] !== undefined) {
  596. rst = sheetBorder[borderStr][JV.PROP_LINE_WEIGHT];
  597. }
  598. if (currentPageMergePos) {
  599. const side = currentPageMergePos[borderStr];
  600. if (side.indexOf(cell[JV.PROP_AREA][borderStr]) >= 0) {
  601. if (needFurtherChk) {
  602. const topSide = currentPageMergePos[JV.PROP_TOP];
  603. const bottomSide = currentPageMergePos[JV.PROP_BOTTOM];
  604. for (let i = 0; i < topSide.length; i++) {
  605. if (cell[JV.PROP_AREA][JV.PROP_TOP] >= topSide[i]) {
  606. if (cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= bottomSide[i]) {
  607. const destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  608. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  609. break;
  610. }
  611. } else {
  612. break;
  613. }
  614. }
  615. } else {
  616. const destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  617. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  618. }
  619. }
  620. } else {
  621. if (mergeBorder && mergeBorder[borderStr] && cell[JV.PROP_AREA][borderStr] === mergeBorder[borderStr]) {
  622. const destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
  623. if (needFurtherChk) {
  624. if (cell[JV.PROP_AREA][JV.PROP_TOP] >= mergeBorder[JV.PROP_TOP] &&
  625. cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= mergeBorder[JV.PROP_BOTTOM]) {
  626. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  627. }
  628. } else {
  629. rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
  630. }
  631. }
  632. }
  633. return parseInt(rst);
  634. };
  635. const private_checkBorder = function(cell, border, sheetBorder) {
  636. let rst = true;
  637. const borderLineWidths = [];
  638. const sheetBorderLineWidths = [];
  639. borderLineWidths.push(border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]);
  640. borderLineWidths.push(border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]);
  641. borderLineWidths.push(border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]);
  642. borderLineWidths.push(border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]);
  643. if (sheetBorder[JV.PROP_LEFT] && sheetBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]) {
  644. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true));
  645. } else {
  646. sheetBorderLineWidths.push(0);
  647. }
  648. if (sheetBorder[JV.PROP_RIGHT] && sheetBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]) {
  649. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true));
  650. } else {
  651. sheetBorderLineWidths.push(0);
  652. }
  653. if (sheetBorder[JV.PROP_TOP] && sheetBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]) {
  654. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false));
  655. } else {
  656. sheetBorderLineWidths.push(0);
  657. }
  658. if (sheetBorder[JV.PROP_BOTTOM] && sheetBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]) {
  659. sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false));
  660. } else {
  661. sheetBorderLineWidths.push(0);
  662. }
  663. for (let i = 0; i < 4; i++) {
  664. if (borderLineWidths[i] != sheetBorderLineWidths[i]) {
  665. rst = false;
  666. break;
  667. }
  668. }
  669. return rst;
  670. };
  671. const private_getIniBorder = function() {
  672. const rst = {};
  673. rst[JV.PROP_LEFT] = {};
  674. rst[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
  675. rst[JV.PROP_RIGHT] = {};
  676. rst[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
  677. rst[JV.PROP_TOP] = {};
  678. rst[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
  679. rst[JV.PROP_BOTTOM] = {};
  680. rst[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
  681. return rst;
  682. };
  683. const private_getBorderId = function(cell) {
  684. let rst = 0;
  685. let hasBorder = false;
  686. if (!(stylesObj.borders)) {
  687. stylesObj.borders = [];
  688. }
  689. const sheetBorder = pageData[JV.NODE_STYLE_COLLECTION][cell.style];
  690. const mergedBorder = private_getIniBorder();
  691. mergedBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true);
  692. mergedBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true);
  693. mergedBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false);
  694. mergedBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false);
  695. for (let i = 0; i < stylesObj.borders.length; i++) {
  696. const border = stylesObj.borders[i];
  697. if (private_checkBorder(cell, border, mergedBorder)) {
  698. hasBorder = true;
  699. rst = i;
  700. break;
  701. }
  702. }
  703. if (!hasBorder) {
  704. stylesObj.borders.push(mergedBorder);
  705. rst = stylesObj.borders.length - 1;
  706. }
  707. return rst;
  708. };
  709. const private_checkControl = function(cellControl, sheetControl) {
  710. let rst = true;
  711. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  712. if (cellControl[JV.CONTROL_PROPS[i]] !== sheetControl[JV.CONTROL_PROPS[i]]) {
  713. rst = false;
  714. break;
  715. }
  716. }
  717. return rst;
  718. };
  719. const private_getStyleId = function(cell) {
  720. let rst = 1;
  721. let hasStyle = false;
  722. if (!(stylesObj.cellXfs)) stylesObj.cellXfs = [];
  723. const fontId = private_getFontId(cell);
  724. let fontAngle = 0;
  725. if (typeof cell[JV.PROP_FONT] === 'string') {
  726. fontAngle = parseInt(pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]].FontAngle);
  727. } else {
  728. fontAngle = parseInt(cell[JV.PROP_FONT].FontAngle);
  729. }
  730. const borderId = private_getBorderId(cell);
  731. let cellControl = null;
  732. if (typeof cell[JV.PROP_CONTROL] === 'string') {
  733. cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
  734. } else {
  735. cellControl = cell[JV.PROP_CONTROL];
  736. }
  737. for (let i = 0; i < stylesObj.cellXfs.length; i++) {
  738. const sheetControl = stylesObj.cellXfs[i];
  739. if (sheetControl.fontId === fontId && sheetControl.borderId === borderId) {
  740. if (private_checkControl(cellControl, sheetControl)) {
  741. rst = i;
  742. hasStyle = true;
  743. break;
  744. }
  745. }
  746. }
  747. if (!hasStyle) {
  748. const sheetControl = {};
  749. sheetControl.fontId = fontId;
  750. sheetControl.borderId = borderId;
  751. sheetControl.fontAngle = fontAngle;
  752. for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
  753. sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
  754. }
  755. stylesObj.cellXfs.push(sheetControl);
  756. rst = stylesObj.cellXfs.length - 1;
  757. }
  758. return rst;
  759. };
  760. const private_setCols = function() {
  761. // remark: 1 excel unit width = 2.117 mm
  762. rst.push('<cols>');
  763. let w = 0;
  764. for (let i = 1; i < xPos.length - 1; i++) {
  765. w = (xPos[i + 1] - xPos[i]) / DPI * 25.4 / 2.117;
  766. w = Math.round(w * 1000) / 1000;
  767. rst.push('<col min="' + i + '" max="' + i + '" width="' + w + '" customWidth="1"/>');
  768. }
  769. rst.push('<col min="' + (xPos.length - 1) + '" max="' + (xPos.length - 1) + '" width="' + 10 + '" customWidth="1"/>');
  770. rst.push('</cols>');
  771. };
  772. const private_setMergedCells = function() {
  773. let cnt = 0;
  774. rst.push('<mergeCells count="?">');
  775. const startIdx = rst.length - 1;
  776. const self_setMergedCells = function(theData, theYPos, offsetY) {
  777. for (let i = 0; i < theData.cells.length; i++) {
  778. const cell = theData.cells[i];
  779. const idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
  780. const idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
  781. const idxB = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
  782. const idxT = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_TOP]);
  783. if (idxR - idxL > 1 || idxB - idxT > 1) {
  784. rst.push('<mergeCell ref="' + private_getCellIdxStr(idxL - 1) + (idxT + offsetY) + ':' + private_getCellIdxStr(idxR - 2) + (idxB - 1 + offsetY) + '"/>');
  785. cnt++;
  786. }
  787. }
  788. };
  789. if (sheetData) {
  790. self_setMergedCells(sheetData, yPos, 0);
  791. } else {
  792. let osY = 0;
  793. for (let i = 0; i < pageData.items.length; i++) {
  794. const shtItemData = pageData.items[i];
  795. const tmpPos = yMultiPos[i];
  796. self_setMergedCells(shtItemData, tmpPos, osY);
  797. osY += tmpPos.length - 2;
  798. }
  799. }
  800. rst[startIdx] = '<mergeCells count="' + cnt + '">';
  801. rst.push('</mergeCells>');
  802. };
  803. const private_setSheetData = function() {
  804. // remark: 1 excel unit height = 0.3612 mm
  805. rst.push('<sheetData>');
  806. const spanX = xPos.length - 2;
  807. let cellIdx = 0;
  808. let h = 0;
  809. const self_setDataEx = function(theShtData, theYPos, rowOffset) {
  810. const rows = [];
  811. // 1. build full set of blank rows/cells
  812. for (let i = 1; i < theYPos.length - 1; i++) {
  813. const rowObj = {};
  814. h = (theYPos[i + 1] - theYPos[i]) / DPI * 25.4 / 0.3612;
  815. h = Math.round(h * 1000) / 1000;
  816. rowObj.height = h;
  817. rowObj.r = i + rowOffset;
  818. rowObj.items = [];
  819. rows.push(rowObj);
  820. for (let j = 1; j < xPos.length - 1; j++) {
  821. const colIdxStr = private_getCellIdxStr(j - 1);
  822. const cellObj = {};
  823. cellObj.r = colIdxStr + (i + rowOffset);
  824. cellObj.s = 0;
  825. cellObj.isBlank = true;
  826. rows[i - 1].items.push(cellObj);
  827. }
  828. }
  829. // 2. then fill up the cell style-ids and values
  830. let rowIdx1 = 0;
  831. let colIdx1 = 0;
  832. let rowIdx2 = 0;
  833. let colIdx2 = 0;
  834. // let colIdxStr = '';
  835. for (let cIdx = 0; cIdx < theShtData.cells.length; cIdx++) {
  836. const styleIdx = private_getStyleId(theShtData.cells[cIdx]);
  837. rowIdx1 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_TOP]);
  838. colIdx1 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_LEFT]);
  839. let cellObj = rows[rowIdx1 - 1].items[colIdx1 - 1];
  840. cellObj.s = styleIdx;
  841. cellObj.isBlank = false;
  842. if (!(strUtil.isEmptyString(theShtData.cells[cIdx][JV.PROP_VALUE]))) {
  843. const valIdx = private_getSharedStrIdx(theShtData.cells[cIdx][JV.PROP_VALUE]);
  844. cellObj.v = valIdx;
  845. }
  846. rowIdx2 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_BOTTOM]);
  847. colIdx2 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_RIGHT]);
  848. if ((rowIdx2 - rowIdx1 > 1) || (colIdx2 - colIdx1 > 1)) {
  849. for (let i = 0; i < rowIdx2 - rowIdx1; i++) {
  850. for (let j = 0; j < colIdx2 - colIdx1; j++) {
  851. if (i === 0 && j === 0) continue;
  852. cellObj = rows[rowIdx1 - 1 + i].items[colIdx1 - 1 + j];
  853. cellObj.s = styleIdx;
  854. cellObj.isBlank = true;
  855. }
  856. }
  857. }
  858. }
  859. // 3. then fill up rst
  860. for (let i = 0; i < rows.length; i++) {
  861. rst.push('<row r="' + (i + 1 + rowOffset) + '" spans="1:' + spanX + '" ht="' + rows[i].height + '" customHeight="1">');
  862. for (let j = 0; j < rows[i].items.length; j++) {
  863. const cellObj = rows[i].items[j];
  864. if (cellObj.v === undefined) {
  865. rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '"/>');
  866. } else {
  867. rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '" t="s">');
  868. rst.push('<v>' + cellObj.v + '</v>');
  869. rst.push('</c>');
  870. }
  871. }
  872. rst.push('</row>');
  873. }
  874. // 4. maybe need to dispose the memory
  875. // ...
  876. };
  877. if (sheetData) {
  878. // current sheet data
  879. currentPageMergePos = sheetData[JV.PAGE_SPECIAL_MERGE_POS];
  880. currentMergeBorder = sheetData[JV.PROP_PAGE_MERGE_BORDER];
  881. self_setDataEx(sheetData, yPos, 0);
  882. } else {
  883. // total data in one sheet
  884. let rowOffset = 0;
  885. for (let i = 0; i < pageData.items.length; i++) {
  886. const shtItemData = pageData.items[i];
  887. currentPageMergePos = shtItemData[JV.PAGE_SPECIAL_MERGE_POS];
  888. currentMergeBorder = shtItemData[JV.PROP_PAGE_MERGE_BORDER];
  889. const tmpPos = yMultiPos[i];
  890. cellIdx = 0;
  891. self_setDataEx(shtItemData, tmpPos, rowOffset);
  892. rowOffset += tmpPos.length - 2;
  893. }
  894. }
  895. rst.push('</sheetData>');
  896. };
  897. preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos);
  898. // console.log('yMultiPos');
  899. // console.log(yMultiPos);
  900. rst.push(dftHeadXml + '\r\n');
  901. rst.push('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
  902. const colStr = private_getCellIdxStr(xPos.length - 3);
  903. rst.push('<dimension ref="A1:' + colStr + '' + yPos.length + '"/>');
  904. if (isFirstSheet) {
  905. rst.push('<sheetViews><sheetView showGridLines="1" tabSelected="1" workbookViewId="0">');
  906. } else {
  907. rst.push('<sheetViews><sheetView showGridLines="1" workbookViewId="0">');
  908. }
  909. // rst.push('<selection sqref="A1:' + colStr + '1"/>');
  910. rst.push('<selection sqref="A1:A1"/>');
  911. rst.push('</sheetView></sheetViews>');
  912. rst.push('<sheetFormatPr defaultRowHeight="13.5"/>');
  913. private_setCols();
  914. private_setSheetData();
  915. private_setMergedCells();
  916. rst.push('<phoneticPr fontId="1" type="noConversion"/>');
  917. // rst.push('<pageMargins left="0.315" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
  918. rst.push('<pageMargins left="' + (parseFloat(pageData[JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_LEFT]) * 0.39375) +
  919. '" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
  920. const paperSizeIdx = JV.PAGES_SIZE_STR.indexOf(paperSize);
  921. let pStr = '';
  922. if (paperSizeIdx >= 0) {
  923. pStr = 'paperSize="' + JV.PAGES_SIZE_IDX[paperSizeIdx] + '"';
  924. }
  925. let orientationStr = (pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][0] > pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1]) ? 'landscape' : 'portrait';
  926. if (currentPageMergePos) {
  927. orientationStr = (currentPageMergePos[JV.NODE_PAGE_SIZE][0] > currentPageMergePos[JV.NODE_PAGE_SIZE][1]) ? 'landscape' : 'portrait';
  928. }
  929. rst.push('<pageSetup ' + pStr + ' fitToWidth="0" fitToHeight="0" orientation="' + orientationStr + '" />');
  930. rst.push('<headerFooter alignWithMargins="0"/>');
  931. if (hasSignature && signSheetIdxArr[sheetIdx]) {
  932. // let rIdx = 1;
  933. // for (let ssIdx = 0; ssIdx < signSheetIdxArr.length; ssIdx++) {
  934. // if (signSheetIdxArr[ssIdx]) {
  935. // if (ssIdx < sheetIdx) rIdx++
  936. // else break;
  937. // }
  938. // }
  939. rst.push('<drawing r:id="rId1"/>');
  940. }
  941. rst.push('</worksheet>');
  942. return rst;
  943. }
  944. function writeWorkSheetRels(signSheetIdx) {
  945. const rst = [];
  946. rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
  947. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  948. rst.push('<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing' + signSheetIdx + '.xml"/>');
  949. rst.push('</Relationships>');
  950. return rst;
  951. }
  952. function writeImage(path, pic, baseDir) {
  953. let rst = null;
  954. if (pic) {
  955. // 这里pic的数据就是base64的,不能是其他
  956. rst = base64ToBuffer(pic);
  957. // fs.createReadStream
  958. } else if (path) {
  959. const filePath = baseDir + '/app' + path;
  960. // console.log(filePath);
  961. rst = fs.readFileSync(filePath);
  962. // console.log(rst);
  963. }
  964. return rst;
  965. }
  966. function writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage, signSheetIdxArr) {
  967. const rst = [];
  968. // console.log('isSinglePage: ' + isSinglePage);
  969. if (isSinglePage) {
  970. rst.push(writeDrawing(pageData, null, signKeyArr[0]));
  971. } else {
  972. for (let i = 0; i < pageData.items.length; i++) {
  973. if (signSheetIdxArr[i]) {
  974. rst.push(writeDrawing(pageData, pageData.items[i], signKeyArr[i]));
  975. }
  976. }
  977. }
  978. return rst;
  979. }
  980. function writeDrawing(pageData, sheetData, subSignKeyArr) {
  981. const rst = [];
  982. const xPos = [];
  983. const yPos = [];
  984. const yMultiPos = [];
  985. const private_setSheetDrawingCellData = function(signCell, theYPos, startPicIdx, offsetRow) {
  986. let cellControl;
  987. if (typeof signCell[JV.PROP_CONTROL] === 'string') {
  988. cellControl = pageData[JV.NODE_CONTROL_COLLECTION][signCell[JV.PROP_CONTROL]];
  989. } else {
  990. cellControl = signCell[JV.PROP_CONTROL];
  991. }
  992. const area = getProperSignatureArea(signCell, cellControl);
  993. // 1. from
  994. // let colIdx = xPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_LEFT]);
  995. // let rowIdx = theYPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_TOP]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出)
  996. let colIdx = xPos.indexOf(area[0]);
  997. let rowIdx = theYPos.indexOf(area[1]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出)
  998. // 因为 xPos, theYPos的首位是0,所以还需要减1
  999. colIdx--;
  1000. rowIdx--;
  1001. rst.push('<xdr:twoCellAnchor editAs="oneCell">');
  1002. rst.push('<xdr:from>');
  1003. rst.push('<xdr:col>' + colIdx + '</xdr:col>');
  1004. rst.push('<xdr:colOff>0</xdr:colOff>');
  1005. rst.push('<xdr:row>' + rowIdx + '</xdr:row>');
  1006. rst.push('<xdr:rowOff>0</xdr:rowOff>');
  1007. rst.push('</xdr:from>');
  1008. // 2. to
  1009. colIdx = xPos.indexOf(area[2]);
  1010. rowIdx = theYPos.indexOf(area[3]) + offsetRow;
  1011. // 因为 xPos, theYPos的首位是0,所以还需要减1
  1012. colIdx--;
  1013. rowIdx--;
  1014. rst.push('<xdr:to>');
  1015. rst.push('<xdr:col>' + colIdx + '</xdr:col>');
  1016. rst.push('<xdr:colOff>0</xdr:colOff>');
  1017. rst.push('<xdr:row>' + rowIdx + '</xdr:row>');
  1018. rst.push('<xdr:rowOff>0</xdr:rowOff>');
  1019. rst.push('</xdr:to>');
  1020. // 3. pic
  1021. rst.push('<xdr:pic>');
  1022. // 3.1
  1023. rst.push('<xdr:nvPicPr>');
  1024. rst.push('<xdr:cNvPr id="' + startPicIdx + '" name="图片 ' + startPicIdx + '"/>');
  1025. rst.push('<xdr:cNvPicPr><a:picLocks noChangeAspect="1" noChangeArrowheads="1"/></xdr:cNvPicPr>');
  1026. rst.push('</xdr:nvPicPr>');
  1027. // 3.2
  1028. rst.push('<xdr:blipFill>');
  1029. // 重点!!!
  1030. const picIdx = subSignKeyArr.indexOf(signCell.signature_name) + 1;
  1031. // 3.2.1
  1032. rst.push('<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId' + picIdx + '" cstate="print">');
  1033. rst.push('<a:extLst>');
  1034. rst.push('<a:ext uri="{28A0092B-C50C-407E-A947-70E740481C1C}">'); // 参考excel本身,都是固定的uri
  1035. rst.push('<a14:useLocalDpi xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main" val="0"/>');
  1036. rst.push('</a:ext>');
  1037. rst.push('</a:extLst>');
  1038. rst.push('</a:blip>');
  1039. // 3.2.2
  1040. rst.push('<a:srcRect/>');
  1041. // 3.2.3
  1042. rst.push('<a:stretch><a:fillRect/></a:stretch>');
  1043. rst.push('</xdr:blipFill>');
  1044. // 3.3
  1045. rst.push('<xdr:spPr bwMode="auto">');
  1046. // 3.3.1
  1047. rst.push('<a:xfrm>');
  1048. // 以下经过Excel及WPS的测试,这数据好像没什么意义,都设为0
  1049. rst.push('<a:off x="0" y="0"/>');
  1050. rst.push('<a:ext cx="0" cy="0"/>');
  1051. rst.push('</a:xfrm>');
  1052. // 3.3.2
  1053. rst.push('<a:prstGeom prst="rect"><a:avLst/></a:prstGeom>');
  1054. // 3.3.3
  1055. rst.push('<a:noFill/>');
  1056. // 3.3.4
  1057. rst.push('<a:extLst>');
  1058. rst.push('<a:ext uri="{909E8E84-426E-40DD-AFC4-6F175D3DCCD1}">');
  1059. rst.push('<a14:hiddenFill xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main">');
  1060. rst.push('<a:solidFill><a:srgbClr val="FFFFFF"/></a:solidFill>');
  1061. rst.push('</a14:hiddenFill>');
  1062. rst.push('</a:ext>');
  1063. rst.push('</a:extLst>');
  1064. // 收尾
  1065. rst.push('</xdr:spPr>');
  1066. rst.push('</xdr:pic>');
  1067. // 4. client Data
  1068. rst.push('<xdr:clientData/>');
  1069. rst.push('</xdr:twoCellAnchor>');
  1070. };
  1071. preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos);
  1072. rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
  1073. rst.push('<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">');
  1074. if (sheetData) {
  1075. let startPicIdx = 2;
  1076. for (const sCell of sheetData[JV.PROP_SIGNATURE_CELLS]) {
  1077. if (subSignKeyArr.indexOf(sCell.signature_name) >= 0) {
  1078. private_setSheetDrawingCellData(sCell, yPos, startPicIdx, 0);
  1079. startPicIdx++;
  1080. }
  1081. }
  1082. } else {
  1083. // total data in one sheet
  1084. let rowOffset = 0;
  1085. for (let i = 0; i < pageData.items.length; i++) {
  1086. const shtItemData = pageData.items[i];
  1087. const tmpPos = yMultiPos[i];
  1088. let startPicIdx = 2;
  1089. for (const sCell of shtItemData[JV.PROP_SIGNATURE_CELLS]) {
  1090. if (subSignKeyArr.indexOf(sCell.signature_name) >= 0) {
  1091. private_setSheetDrawingCellData(sCell, tmpPos, startPicIdx, rowOffset);
  1092. startPicIdx++;
  1093. }
  1094. }
  1095. rowOffset += tmpPos.length - 2;
  1096. }
  1097. }
  1098. rst.push('</xdr:wsDr>');
  1099. return rst;
  1100. }
  1101. function writeDrawingsRels(amt, startIdx) {
  1102. const rst = [];
  1103. rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
  1104. rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
  1105. for (let idx = 0; idx < amt; idx++) {
  1106. rst.push('<Relationship Id="rId' + (idx + 1) + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image' + (startIdx + idx + 1) + '.png"/>');
  1107. }
  1108. rst.push('</Relationships>');
  1109. return rst;
  1110. }
  1111. function mergeProperties(orgObj, newObj) {
  1112. const orgPropArr = [];
  1113. const newPropArr = [];
  1114. for (const p in orgObj) {
  1115. orgPropArr.push(p);
  1116. }
  1117. for (const p in newObj) {
  1118. newPropArr.push(p);
  1119. }
  1120. for (let i = 0; i < newPropArr.length; i++) {
  1121. if (orgPropArr.indexOf(newPropArr[i]) < 0) {
  1122. orgObj[newPropArr[i]] = newObj[newPropArr[i]];
  1123. }
  1124. }
  1125. }
  1126. function resetDummuySignature(pageData, roleRel) {
  1127. // 备注:计算草图等其他图形需要额外做些处理
  1128. let dummySignIdx = 0;
  1129. for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
  1130. const page = pageData.items[pageIdx];
  1131. if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
  1132. for (const signature of page[JV.PROP_SIGNATURE_CELLS]) {
  1133. if (signature.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
  1134. // 表示这是一个其他类型的非原生电子签名图,只是借用signature的处理机制,每个图都是唯一的,所以需要重新给个唯一的新signature_name
  1135. signature.signature_name = signature.signature_name + '_' + pageIdx + '_' + dummySignIdx;
  1136. dummySignIdx++;
  1137. const roleRelItem = { type: '用户', sign_path: signature.path, signature_name: signature.signature_name };
  1138. roleRel.push(roleRelItem);
  1139. }
  1140. }
  1141. }
  1142. }
  1143. }
  1144. function _createDummyCell(dummyOrgCells, parentPage) {
  1145. for (const signature of dummyOrgCells) {
  1146. const dummyCell = {
  1147. font: 'Content',
  1148. control: 'Default',
  1149. style: 'Default_None',
  1150. Value: '',
  1151. area: { Left: 0, Right: 0, Top: 0, Bottom: 0 }
  1152. };
  1153. if (signature.style) dummyCell.style = signature.style;
  1154. dummyCell[JV.PROP_AREA][JV.PROP_LEFT] = signature[JV.PROP_AREA][JV.PROP_LEFT];
  1155. dummyCell[JV.PROP_AREA][JV.PROP_RIGHT] = signature[JV.PROP_AREA][JV.PROP_RIGHT];
  1156. dummyCell[JV.PROP_AREA][JV.PROP_TOP] = signature[JV.PROP_AREA][JV.PROP_TOP];
  1157. dummyCell[JV.PROP_AREA][JV.PROP_BOTTOM] = signature[JV.PROP_AREA][JV.PROP_BOTTOM];
  1158. parentPage[JV.PROP_CELLS].push(dummyCell);
  1159. }
  1160. }
  1161. function createDummySignatureCell(pageData) {
  1162. for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
  1163. const page = pageData.items[pageIdx];
  1164. if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
  1165. _createDummyCell(page[JV.PROP_SIGNATURE_CELLS], page);
  1166. // for (const signature of page[JV.PROP_SIGNATURE_CELLS]) {
  1167. // const dummyCell = {
  1168. // font: 'Content',
  1169. // control: 'Default',
  1170. // style: 'Default_None',
  1171. // Value: '',
  1172. // area: { Left: 0, Right: 0, Top: 0, Bottom: 0 }
  1173. // };
  1174. // if (signature.style) dummyCell.style = signature.style;
  1175. // dummyCell[JV.PROP_AREA][JV.PROP_LEFT] = signature[JV.PROP_AREA][JV.PROP_LEFT];
  1176. // dummyCell[JV.PROP_AREA][JV.PROP_RIGHT] = signature[JV.PROP_AREA][JV.PROP_RIGHT];
  1177. // dummyCell[JV.PROP_AREA][JV.PROP_TOP] = signature[JV.PROP_AREA][JV.PROP_TOP];
  1178. // dummyCell[JV.PROP_AREA][JV.PROP_BOTTOM] = signature[JV.PROP_AREA][JV.PROP_BOTTOM];
  1179. // page[JV.PROP_CELLS].push(dummyCell);
  1180. // }
  1181. }
  1182. if (page[JV.PROP_SIGNATURE_DATE_CELLS] && page[JV.PROP_SIGNATURE_DATE_CELLS].length > 0) {
  1183. _createDummyCell(page[JV.PROP_SIGNATURE_DATE_CELLS], page);
  1184. }
  1185. if (page[JV.PROP_SIGNATURE_AUDIT_CELLS] && page[JV.PROP_SIGNATURE_AUDIT_CELLS].length > 0) {
  1186. _createDummyCell(page[JV.PROP_SIGNATURE_AUDIT_CELLS], page);
  1187. }
  1188. }
  1189. }
  1190. function checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, roleRel, signSheetIdxArr) {
  1191. // 备注:电子签名是以图形的方式处理,一页可以有多个签名,多页的签名基本是引用同样的图片,在这里先处理一下,后期统一引用。
  1192. // 另:以后的图片(在电子签名(signature_cells)以外的图片)会单独处理(如计算草图等)
  1193. let rst = false;
  1194. let chkRoles = [];
  1195. if (roleRel instanceof Array) {
  1196. chkRoles = roleRel;
  1197. } else {
  1198. if (roleRel) chkRoles.push(roleRel);
  1199. }
  1200. // console.log(chkRoles);
  1201. // console.log(signKeyArr);
  1202. // for (const page of pageData.items) {
  1203. for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
  1204. const page = pageData.items[pageIdx];
  1205. signKeyArr.push([]);
  1206. signPathArr.push([]);
  1207. signSheetIdxArr[pageIdx] = false;
  1208. // console.log('page index: ' + pageIdx);
  1209. // console.log(page[JV.PROP_SIGNATURE_CELLS]);
  1210. if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
  1211. // let dummySignIdx = 0;
  1212. for (const signature of page[JV.PROP_SIGNATURE_CELLS]) {
  1213. // if (signature.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
  1214. // // 表示这是一个其他类型的非原生电子签名图,只是借用signature的处理机制,每个图都是唯一的,所以需要重新给个唯一的新signature_name
  1215. // signature.signature_name = signature.signature_name + '_' + pageIdx + '_' + dummySignIdx;
  1216. // dummySignIdx++;
  1217. // rst = true;
  1218. // signSheetIdxArr[pageIdx] = true;
  1219. // }
  1220. if (signKeyArr[pageIdx].indexOf(signature.signature_name) < 0) {
  1221. if (signature.pic) {
  1222. const signPath = { path: null, pic: null };
  1223. signPathArr[pageIdx].push(signPath);
  1224. signPath.pic = signature.pic; // 历史报表
  1225. signKeyArr[pageIdx].push(signature.signature_name);
  1226. rst = true;
  1227. signSheetIdxArr[pageIdx] = true;
  1228. } else {
  1229. for (const role of chkRoles) {
  1230. if (signature.signature_name === role.signature_name) {
  1231. // console.log('signature.signature_name: ' + signature.signature_name);
  1232. if (role.sign_pic) {
  1233. const signPath = { path: null, pic: null };
  1234. signPathArr[pageIdx].push(signPath);
  1235. signPath.pic = role.sign_pic;
  1236. signKeyArr[pageIdx].push(signature.signature_name);
  1237. rst = true;
  1238. signSheetIdxArr[pageIdx] = true;
  1239. } else if (role.sign_path) {
  1240. const signPath = { path: null, pic: null };
  1241. signPathArr[pageIdx].push(signPath);
  1242. signPath.path = role.sign_path;
  1243. signKeyArr[pageIdx].push(signature.signature_name);
  1244. rst = true;
  1245. signSheetIdxArr[pageIdx] = true;
  1246. }
  1247. break;
  1248. }
  1249. }
  1250. }
  1251. }
  1252. }
  1253. }
  1254. }
  1255. return rst;
  1256. }
  1257. function base64ToBuffer(dataurl) {
  1258. // 将base64转换为buffer
  1259. const arr = dataurl.split(',');
  1260. // console.log(arr[1]);
  1261. const rst = new Buffer(arr[1], 'base64');
  1262. return rst;
  1263. }
  1264. module.exports = {
  1265. exportExcel: function(pageData, paperSize, fName, options, custSheetNames, custSheetMergeBands, baseDir, roleRel, callback) {
  1266. const rptOptions = ({ singlePage: false, fileName: 'report' });
  1267. if (options === 'true' || options === true) {
  1268. rptOptions.singlePage = true;
  1269. }
  1270. const isSinglePage = rptOptions.singlePage;
  1271. const sheets = [];
  1272. const signKeyArr = [];
  1273. const signPathArr = [];
  1274. const signSheetIdxArr = []; // 确定哪些sheet有签名(在多表导出的时候,有些表可能没有签名,但其他的表有签名)
  1275. // console.log('in exportExcel!');
  1276. // console.log(roleRel);
  1277. // const hasSignature = (roleRel !== null) ? checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, roleRel, signSheetIdxArr) : false;
  1278. let thisRoleRel = [];
  1279. if (roleRel !== null && roleRel !== undefined) {
  1280. thisRoleRel = roleRel;
  1281. }
  1282. resetDummuySignature(pageData, thisRoleRel); // 把草图转换一下roleRel
  1283. // console.log(thisRoleRel);
  1284. const hasSignature = false; // 暂时不支持电子签名、草图导出excel
  1285. // const hasSignature = checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, thisRoleRel, signSheetIdxArr); // 因草图的关系,thisRoleRel是否为null就不是充要的条件
  1286. // console.log('signKeyArr');
  1287. // console.log(signKeyArr);
  1288. // console.log('signPathArr');
  1289. // console.log(signPathArr);
  1290. // console.log('hasSignature: ' + hasSignature);
  1291. if (!hasSignature) {
  1292. createDummySignatureCell(pageData);
  1293. }
  1294. if (isSinglePage) {
  1295. sheets.push({ sheetName: '全部页' });
  1296. } else {
  1297. if (custSheetNames && custSheetNames.length === pageData.items.length) {
  1298. for (let i = 0; i < pageData.items.length; i++) {
  1299. sheets.push({ sheetName: custSheetNames[i] });
  1300. }
  1301. } else {
  1302. for (let i = 0; i < pageData.items.length; i++) {
  1303. sheets.push({ sheetName: '第' + (i + 1) + '页' });
  1304. }
  1305. }
  1306. }
  1307. // 1.
  1308. let file = '[Content_Types].xml';
  1309. let data = writeContentTypes(sheets, isSinglePage, hasSignature);
  1310. const zip = new JSZip();
  1311. zip.file(file, data.join(''), { compression: 'DEFLATE' });
  1312. // console.log('step 1');
  1313. // 2.
  1314. const zip_rels = zip.folder('_rels');
  1315. file = '.rels';
  1316. data = writeRootRels();
  1317. zip_rels.file(file, data.join(''), { compression: 'DEFLATE' });
  1318. // console.log('step 2');
  1319. // 3.
  1320. const zip_docProps = zip.folder('docProps');
  1321. file = 'app.xml';
  1322. data = writeApp(sheets, isSinglePage);
  1323. zip_docProps.file(file, data.join(''), { compression: 'DEFLATE' });
  1324. file = 'core.xml';
  1325. data = writeCore();
  1326. zip_docProps.file(file, data.join(''), { compression: 'DEFLATE' });
  1327. // console.log('step 3');
  1328. // 4.
  1329. const zip_xl = zip.folder('xl');
  1330. file = 'workbook.xml';
  1331. data = writeXlWorkBook(sheets, isSinglePage);
  1332. zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });
  1333. const zip_rels2 = zip_xl.folder('_rels');
  1334. file = 'workbook.xml.rels';
  1335. data = writeXlRels(sheets, isSinglePage);
  1336. zip_rels2.file(file, data.join(''), { compression: 'DEFLATE' });
  1337. // console.log('step 4');
  1338. // 5.
  1339. const zip_theme = zip_xl.folder('theme');
  1340. file = 'theme1.xml';
  1341. data = writeTheme();
  1342. zip_theme.file(file, data, { compression: 'DEFLATE' });
  1343. if (hasSignature) {
  1344. // 5.1 写所有的image
  1345. const zip_media = zip_xl.folder('media');
  1346. let picIdx = 0;
  1347. // console.log(signKeyArr);
  1348. // console.log(signPathArr);
  1349. for (let signIdx = 0; signIdx < signPathArr.length; signIdx++) {
  1350. if (signKeyArr[signIdx].length > 0) {
  1351. for (let pathIdx = 0; pathIdx < signPathArr[signIdx].length; pathIdx++) {
  1352. picIdx++;
  1353. data = writeImage(signPathArr[signIdx][pathIdx].path, signPathArr[signIdx][pathIdx].pic, baseDir);
  1354. file = 'image' + picIdx + '.png';
  1355. zip_media.file(file, data, { compression: 'DEFLATE' });
  1356. }
  1357. }
  1358. }
  1359. // 5.2
  1360. const zip_drawings = zip_xl.folder('drawings');
  1361. data = writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage, signSheetIdxArr);
  1362. // console.log('isSinglePage: ' + isSinglePage);
  1363. // console.log(data);
  1364. for (let psIdx = 0; psIdx < data.length; psIdx++) {
  1365. file = 'drawing' + (psIdx + 1) + '.xml';
  1366. // console.log('drawing1' + (psIdx + 1) + '.xml data');
  1367. // console.log(data);
  1368. zip_drawings.file(file, data[psIdx].join(''), { compression: 'DEFLATE' });
  1369. }
  1370. // 5.3
  1371. const zip_drawings_rels = zip_drawings.folder('_rels');
  1372. let relsIdx = 0;
  1373. let relsImgAmt = 0;
  1374. for (let ssIdx = 0; ssIdx < signKeyArr.length; ssIdx++) {
  1375. if (signKeyArr[ssIdx].length > 0) {
  1376. data = writeDrawingsRels(signKeyArr[ssIdx].length, relsImgAmt); // 一个drawingX.xml.rels文件与一个drawingX.xml对应
  1377. relsIdx++;
  1378. relsImgAmt += signKeyArr[ssIdx].length;
  1379. // console.log('drawing1.xml.rels data');
  1380. // console.log(data);
  1381. file = 'drawing' + relsIdx + '.xml.rels';
  1382. zip_drawings_rels.file(file, data.join(''), { compression: 'DEFLATE' });
  1383. }
  1384. }
  1385. }
  1386. // console.log('step 5');
  1387. // 6.
  1388. const zip_xl_worksheets = zip_xl.folder('worksheets');
  1389. const sharedStrList = [];
  1390. const stylesObj = {};
  1391. data = writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands, hasSignature, signSheetIdxArr);
  1392. if (isSinglePage) {
  1393. for (let i = 0; i < 1; i++) {
  1394. file = 'sheet' + (i + 1) + '.xml';
  1395. zip_xl_worksheets.file(file, data[i].join(''), { compression: 'DEFLATE' });
  1396. }
  1397. } else {
  1398. for (let i = 0; i < data.length; i++) {
  1399. file = 'sheet' + (i + 1) + '.xml';
  1400. zip_xl_worksheets.file(file, data[i].join(''), { compression: 'DEFLATE' });
  1401. }
  1402. }
  1403. // console.log('step 6');
  1404. // 7.
  1405. if (hasSignature) {
  1406. const zip_xl_worksheets_rels = zip_xl_worksheets.folder('_rels');
  1407. if (isSinglePage) {
  1408. data = writeWorkSheetRels(1);
  1409. file = 'sheet1.xml.rels';
  1410. zip_xl_worksheets_rels.file(file, data.join(''), { compression: 'DEFLATE' });
  1411. } else {
  1412. let ssIdx = 0;
  1413. for (let i = 0; i < data.length; i++) {
  1414. if (signSheetIdxArr[i]) {
  1415. ssIdx++;
  1416. const ssData = writeWorkSheetRels(ssIdx);
  1417. file = 'sheet' + (i + 1) + '.xml.rels';
  1418. zip_xl_worksheets_rels.file(file, ssData.join(''), { compression: 'DEFLATE' });
  1419. }
  1420. }
  1421. }
  1422. }
  1423. file = 'sharedStrings.xml';
  1424. data = writeSharedString(sharedStrList);
  1425. zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });
  1426. file = 'styles.xml';
  1427. data = writeStyles(stylesObj, hasSignature);
  1428. zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });
  1429. // console.log('step 7');
  1430. if (fName) {
  1431. // let newName = '' + (new Date()).valueOf();
  1432. const newName = uuidV1();
  1433. // console.log('current path: ' + __dirname);
  1434. // const filePath = this.app.baseDir + '/app/public/download/';
  1435. zip.generateNodeStream({ type: 'nodebuffer', streamFiles: true })
  1436. .pipe(fs.createWriteStream(__dirname.slice(0, __dirname.length - 13) + '/public/download/' + newName + '.xlsx'))
  1437. .on('finish', function() {
  1438. // JSZip generates a readable stream with a "end" event,
  1439. // but is piped here in a writable stream which emits a "finish" event.
  1440. console.log(newName + '.xlsx was written.');
  1441. if (callback) callback(null, newName);
  1442. });
  1443. } else {
  1444. // return zip.generateNodeStream({type:'nodebuffer',streamFiles:true});
  1445. return zip;
  1446. }
  1447. },
  1448. exportExcelInOneBook: function(pageDataArray, paperSize, fName, baseDir, roleRelArr, callback) {
  1449. const me = this;
  1450. const newPageData = {};
  1451. // 1. 重新编排一下数据,把一份报表的pageData合并到一起作为一个Sheet输出(需要重新调整数据纵向坐标),多份报表数据就形成多个Sheet
  1452. // -- 简单来说,就是重新包装数据
  1453. try {
  1454. // 1.1 newPageData外围属性
  1455. const newContrl = {};
  1456. const newFont = {};
  1457. const newStyle = {};
  1458. const custMergeBands = [];
  1459. for (let i = 0; i < pageDataArray.length; i++) {
  1460. mergeProperties(newContrl, pageDataArray[i][JV.NODE_CONTROL_COLLECTION]);
  1461. mergeProperties(newFont, pageDataArray[i][JV.NODE_FONT_COLLECTION]);
  1462. mergeProperties(newStyle, pageDataArray[i][JV.NODE_STYLE_COLLECTION]);
  1463. }
  1464. newPageData[JV.NODE_CONTROL_COLLECTION] = newContrl;
  1465. newPageData[JV.NODE_FONT_COLLECTION] = newFont;
  1466. newPageData[JV.NODE_STYLE_COLLECTION] = newStyle;
  1467. newPageData[JV.NODE_PAGE_INFO] = pageDataArray[0][JV.NODE_PAGE_INFO];
  1468. newPageData[JV.BAND_PROP_MERGE_BAND] = pageDataArray[0][JV.BAND_PROP_MERGE_BAND];
  1469. // 1.2 重新设置pageDataArray的各个cell的Top/Bottom坐标
  1470. const sheetNames = [];
  1471. const newPagePos = [];
  1472. for (let i = 0; i < pageDataArray.length; i++) {
  1473. let offsetY = 0;
  1474. const mergeBand = {};
  1475. custMergeBands.push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND]);
  1476. // 备注:不同的报表有可能有不同的边框,如封面表就是无边框的
  1477. mergeBand[JV.PROP_LEFT] = [];
  1478. mergeBand[JV.PROP_RIGHT] = [];
  1479. mergeBand[JV.PROP_TOP] = [];
  1480. mergeBand[JV.PROP_BOTTOM] = [];
  1481. newPagePos.push(mergeBand);
  1482. mergeBand[JV.PROP_LEFT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_LEFT]);
  1483. mergeBand[JV.PROP_RIGHT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_RIGHT]);
  1484. sheetNames.push(pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_MAIN_INFO_RPT_NAME]);
  1485. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1486. let maxY = 0;
  1487. let minY = 100000;
  1488. if (pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS]) {
  1489. let pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_TOP][0] + offsetY;
  1490. mergeBand[JV.PROP_TOP].push(pos);
  1491. pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_BOTTOM][0] + offsetY;
  1492. mergeBand[JV.PROP_BOTTOM].push(pos);
  1493. } else if (pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER]) {
  1494. let pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_TOP] + offsetY;
  1495. mergeBand[JV.PROP_TOP].push(pos);
  1496. pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_BOTTOM] + offsetY;
  1497. mergeBand[JV.PROP_BOTTOM].push(pos);
  1498. } else {
  1499. mergeBand[JV.PROP_TOP].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_TOP] + offsetY);
  1500. mergeBand[JV.PROP_BOTTOM].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_BOTTOM] + offsetY);
  1501. }
  1502. // 1.2.1 重新设置普通cells的Top Bottom坐标
  1503. for (const cell of pageDataArray[i].items[j].cells) {
  1504. // for (let k = 0; k < pageDataArray[i].items[j].cells.length; k++) {
  1505. if (maxY < cell[JV.PROP_AREA][JV.PROP_BOTTOM]) {
  1506. maxY = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
  1507. }
  1508. if (minY > cell[JV.PROP_AREA][JV.PROP_TOP]) {
  1509. minY = cell[JV.PROP_AREA][JV.PROP_TOP];
  1510. }
  1511. cell[JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
  1512. cell[JV.PROP_AREA][JV.PROP_TOP] += offsetY;
  1513. }
  1514. // 1.2.2 重新设置电子签名cells的Top Bottom坐标
  1515. for (const cell of pageDataArray[i].items[j].signature_cells) {
  1516. if (maxY < cell[JV.PROP_AREA][JV.PROP_BOTTOM]) {
  1517. maxY = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
  1518. }
  1519. if (minY > cell[JV.PROP_AREA][JV.PROP_TOP]) {
  1520. minY = cell[JV.PROP_AREA][JV.PROP_TOP];
  1521. }
  1522. // console.log('before');
  1523. // console.log(cell);
  1524. cell[JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
  1525. cell[JV.PROP_AREA][JV.PROP_TOP] += offsetY;
  1526. // console.log('after');
  1527. // console.log(cell);
  1528. }
  1529. // 1.2.3 重新设置电子签名日期cells的Top Bottom坐标
  1530. for (const cell of pageDataArray[i].items[j].signature_date_cells) {
  1531. if (maxY < cell[JV.PROP_AREA][JV.PROP_BOTTOM]) {
  1532. maxY = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
  1533. }
  1534. if (minY > cell[JV.PROP_AREA][JV.PROP_TOP]) {
  1535. minY = cell[JV.PROP_AREA][JV.PROP_TOP];
  1536. }
  1537. cell[JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
  1538. cell[JV.PROP_AREA][JV.PROP_TOP] += offsetY;
  1539. }
  1540. const 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;
  1541. offsetY += (maxY - minY);
  1542. if (bottomGap > 10) {
  1543. offsetY += (bottomGap - 10);
  1544. }
  1545. }
  1546. }
  1547. // 2. newPageData的items属性
  1548. newPageData.items = [];
  1549. for (let i = 0; i < pageDataArray.length; i++) {
  1550. const pageItem = {};
  1551. pageItem[JV.PROP_PAGE_SEQ] = i + 1;
  1552. pageItem[JV.PROP_CELLS] = [];
  1553. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1554. for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_CELLS].length; k++) {
  1555. pageItem[JV.PROP_CELLS].push(pageDataArray[i].items[j][JV.PROP_CELLS][k]);
  1556. }
  1557. }
  1558. pageItem[JV.PROP_SIGNATURE_CELLS] = [];
  1559. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1560. for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_CELLS].length; k++) {
  1561. pageItem[JV.PROP_SIGNATURE_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_CELLS][k]);
  1562. }
  1563. }
  1564. pageItem[JV.PROP_SIGNATURE_DATE_CELLS] = [];
  1565. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1566. for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_DATE_CELLS].length; k++) {
  1567. pageItem[JV.PROP_SIGNATURE_DATE_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_DATE_CELLS][k]);
  1568. }
  1569. }
  1570. pageItem[JV.PROP_SIGNATURE_AUDIT_CELLS] = [];
  1571. for (let j = 0; j < pageDataArray[i].items.length; j++) {
  1572. for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_AUDIT_CELLS].length; k++) {
  1573. pageItem[JV.PROP_SIGNATURE_AUDIT_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_AUDIT_CELLS][k]);
  1574. }
  1575. }
  1576. newPagePos[i][JV.NODE_PAGE_SIZE] = pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE];
  1577. pageItem[JV.PAGE_SPECIAL_MERGE_POS] = newPagePos[i];
  1578. newPageData.items.push(pageItem);
  1579. }
  1580. // 3. everything is ok, then call me
  1581. // let roleRel = null; // 未来调用的时候,这个属性要从外面给!!!
  1582. // roleRelArr
  1583. me.exportExcel(newPageData, paperSize, fName, 'false', sheetNames, custMergeBands, baseDir, roleRelArr, callback);
  1584. // fsUtil.writeObjToFile(newPageData, 'D:/GitHome/ConstructionOperation/tmp/combinedHeader.js');
  1585. } catch (e) {
  1586. console.log(e);
  1587. }
  1588. },
  1589. };