123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032 |
- 'use strict';
- /**
- * Created by Tony on 2017/4/1.
- */
- const JV = require('../rpt_component/jpc_value_define');
- const fs = require('fs');
- const JSZip = require('jszip');
- const strUtil = require('../public/stringUtil');
- const jpcCmnHelper = require('../rpt_component/helper/jpc_helper_common');
- const DPI = jpcCmnHelper.getScreenDPI()[0];
- const fsUtil = require('../public/fsUtil');
- const dftHeadXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
- const uuidV1 = require('uuid/v1');
- function writeContentTypes(sheets, isSinglePage) {
- const rst = [];
- rst.push(dftHeadXml + '\r\n');
- rst.push('<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">');
- rst.push('<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>');
- rst.push('<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>');
- rst.push('<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>');
- rst.push('<Default Extension="xml" ContentType="application/xml"/>');
- rst.push('<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>');
- rst.push('<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>');
- rst.push('<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>');
- if (isSinglePage) {
- rst.push('<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
- } else {
- for (let i = 0; i < sheets.length; i++) {
- rst.push('<Override PartName="/xl/worksheets/sheet' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>');
- }
- }
- rst.push('<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>');
- rst.push('</Types>');
- return rst;
- }
- function writeRootRels() {
- const rst = [];
- rst.push(dftHeadXml + '\r\n');
- rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
- rst.push('<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>');
- rst.push('<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>');
- rst.push('<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>');
- rst.push('</Relationships>');
- return rst;
- }
- function writeApp(sheets, isSinglePage) {
- const rst = [];
- rst.push(dftHeadXml + '\r\n');
- rst.push('<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">');
- rst.push('<Application>Microsoft Excel</Application>');
- rst.push('<DocSecurity>0</DocSecurity>');
- rst.push('<ScaleCrop>false</ScaleCrop>');
- rst.push('<HeadingPairs>');
- rst.push('<vt:vector size="2" baseType="variant">');
- rst.push('<vt:variant><vt:lpstr>工作表</vt:lpstr></vt:variant>');
- if (isSinglePage) rst.push('<vt:variant><vt:i4>1</vt:i4></vt:variant>');
- else rst.push('<vt:variant><vt:i4>' + sheets.length + '</vt:i4></vt:variant>');
- rst.push('</vt:vector>');
- rst.push('</HeadingPairs>');
- rst.push('<TitlesOfParts>');
- if (isSinglePage) {
- rst.push('<vt:vector size="1" baseType="lpstr">');
- rst.push('<vt:lpstr>' + sheets[0].sheetName + '</vt:lpstr>');
- } else {
- rst.push('<vt:vector size="' + sheets.length + '" baseType="lpstr">');
- for (let i = 0; i < sheets.length; i++) {
- rst.push('<vt:lpstr>' + sheets[i].sheetName + '</vt:lpstr>');
- }
- }
- rst.push('</vt:vector>');
- rst.push('</TitlesOfParts>');
- rst.push('<Company>SmartCost</Company>');
- rst.push('<LinksUpToDate>false</LinksUpToDate>');
- rst.push('<SharedDoc>false</SharedDoc>');
- rst.push('<HyperlinksChanged>false</HyperlinksChanged>');
- rst.push('<AppVersion>12.0000</AppVersion>');
- rst.push('</Properties>');
- return rst;
- }
- function writeCore() {
- const rst = [];
- const p_fillZero = function(val) {
- let rst = val;
- if (val < 10) {
- rst = '0' + val;
- }
- return rst;
- };
- rst.push(dftHeadXml + '\r\n');
- 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">');
- rst.push('<dc:creator>SmartCost</dc:creator>');
- rst.push('<cp:lastModifiedBy>SmartCost</cp:lastModifiedBy>');
- const dt = new Date();
- dt.setDate(dt.getDate() - 8 / 24); // it's GMT time, so please add the server offset time ( -8 hours )
- const dtStr = dt.getFullYear() + '-' + p_fillZero(dt.getMonth() + 1) + '-' + p_fillZero(dt.getDate()) + 'T' +
- p_fillZero(dt.getHours()) + ':' + p_fillZero(dt.getMinutes()) + ':' + p_fillZero(dt.getSeconds()) + 'Z';
- rst.push('<dcterms:created xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:created>');
- rst.push('<dcterms:modified xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:modified>');
- rst.push('</cp:coreProperties>');
- return rst;
- }
- function writeXlWorkBook(sheets, isSinglePage) {
- const rst = [];
- rst.push(dftHeadXml + '\r\n');
- rst.push('<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
- rst.push('<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>');
- rst.push('<workbookPr defaultThemeVersion="124226"/>');
- rst.push('<bookViews><workbookView xWindow="360" yWindow="345" windowWidth="14655" windowHeight="4305"/></bookViews>');
- rst.push('<sheets>');
- if (isSinglePage) {
- rst.push('<sheet name="' + sheets[0].sheetName + '" sheetId="1" r:id="rId1"/>');
- } else {
- for (let i = 0; i < sheets.length; i++) {
- rst.push('<sheet name="' + sheets[i].sheetName + '" sheetId="' + (i + 1) + '" r:id="rId' + (i + 1) + '"/>');
- }
- }
- rst.push('</sheets>');
- rst.push('<calcPr calcId="124519"/>');
- rst.push('</workbook>');
- return rst;
- }
- function writeXlRels(sheets, isSinglePage) {
- const rst = [];
- let idx = 1;
- rst.push(dftHeadXml + '\r\n');
- rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
- if (isSinglePage) {
- rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>');
- idx++;
- } else {
- for (let i = 0; i < sheets.length; i++) {
- rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' + (i + 1) + '.xml"/>');
- idx++;
- }
- }
- rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>');
- idx++;
- rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>');
- idx++;
- rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>');
- rst.push('</Relationships>');
- return rst;
- }
- function writeTheme() {
- const rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r');
- return rst;
- }
- function writeStyles(stylesObj) {
- const rst = [];
- rst.push(dftHeadXml + '\r\n');
- rst.push('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
- // 1. push fonts
- rst.push('<fonts count="' + stylesObj.fonts.length + '">');
- for (let i = 0; i < stylesObj.fonts.length; i++) {
- const font = stylesObj.fonts[i];
- rst.push('<font>');
- if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]])) {
- rst.push('<b/>');
- }
- if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]])) {
- rst.push('<i/>');
- }
- if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]])) {
- rst.push('<u/>');
- }
- rst.push('<sz val="' + font.size + '"/>');
- rst.push('<color indexed="' + font.colorIdx + '"/>');
- rst.push('<name val="' + font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] + '"/>');
- rst.push('<charset val="' + font.charset + '"/>');
- rst.push('</font>');
- }
- rst.push('</fonts>');
- // 2. push default fills
- rst.push('<fills count="2"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill></fills>');
- // 3. push borders
- rst.push('<borders count="' + stylesObj.borders.length + '">');
- const private_setBorder = function(border, borderDirection) {
- if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 0) {
- rst.push('<' + borderDirection.toLowerCase() + '/>');
- } else {
- let bW = 'thin';
- if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) === 2) bW = 'medium';
- if (parseInt(border[borderDirection][JV.PROP_LINE_WEIGHT]) > 2) bW = 'thick';
- rst.push('<' + borderDirection.toLowerCase() + ' style="' + bW + '"><color indexed="64"/></' + borderDirection.toLowerCase() + '>');
- }
- };
- for (let i = 0; i < stylesObj.borders.length; i++) {
- const border = stylesObj.borders[i];
- rst.push('<border>');
- private_setBorder(border, JV.PROP_LEFT);
- private_setBorder(border, JV.PROP_RIGHT);
- private_setBorder(border, JV.PROP_TOP);
- private_setBorder(border, JV.PROP_BOTTOM);
- rst.push('<diagonal />');
- rst.push('</border>');
- }
- rst.push('</borders>');
- // 4. push cellStyleXfs
- rst.push('<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"><alignment vertical="center"/></xf></cellStyleXfs>');
- // 5. push cellXfs
- rst.push('<cellXfs count="' + stylesObj.cellXfs.length + '">');
- for (let i = 0; i < stylesObj.cellXfs.length; i++) {
- const excelStyle = stylesObj.cellXfs[i];
- rst.push('<xf numFmtId="0" fontId="' + excelStyle.fontId + '" fillId="0" borderId="' + excelStyle.borderId + '" xfId="0">');
- // pageData[JV.NODE_FONT_COLLECTION] excelStyle.fontId
- let alignStr = '<alignment';
- let textRotation = 0;
- let newHorizontal = excelStyle[JV.CONTROL_PROPS[2]];
- let newVertical = excelStyle[JV.CONTROL_PROPS[3]];
- if (excelStyle[JV.CONTROL_PROPS[5]] && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_CLOSE_OUTPUT]])) {
- newVertical = excelStyle[JV.CONTROL_PROPS[5]];
- }
- if (parseInt(excelStyle.fontAngle) !== 0) {
- let tmpH = newHorizontal;
- let tmpV = newVertical;
- if (excelStyle.fontAngle > 0) {
- textRotation = 180;
- if (newHorizontal === 'left') {
- tmpV = 'top';
- } else if (newHorizontal === 'right') {
- tmpV = 'bottom';
- } else {
- tmpV = 'center';
- }
- if (newVertical === 'top') {
- tmpH = 'right';
- } else if (newVertical === 'bottom') {
- tmpH = 'left';
- } else if (newVertical === 'justify') {
- tmpH = 'justify';
- } else {
- tmpH = 'center';
- }
- } else {
- textRotation = 90;
- if (newHorizontal === 'left') {
- tmpV = 'bottom';
- } else if (newHorizontal === 'right') {
- tmpV = 'top';
- } else {
- tmpV = 'center';
- }
- if (newVertical === 'top') {
- tmpH = 'left';
- } else if (newVertical === 'bottom') {
- tmpH = 'right';
- } else {
- tmpH = 'center';
- }
- }
- newHorizontal = tmpH;
- newVertical = tmpV;
- }
- alignStr += ' horizontal="' + newHorizontal + '" vertical="' + newVertical + '"';
- if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[0]])) {
- alignStr += ' shrinkToFit="1"';
- }
- if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[4]]) && !strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[6]])) {
- alignStr += ' wrapText="1"';
- }
- if (textRotation !== 0) {
- alignStr += ' textRotation="' + textRotation + '"';
- }
- alignStr += '/>';
- rst.push(alignStr);
- rst.push('</xf>');
- }
- rst.push('</cellXfs>');
- // 6. others (xfl style / dxfs / tableStyles)
- rst.push('<cellStyles count="1"><cellStyle name="常规" xfId="0" builtinId="0"/></cellStyles>');
- rst.push('<dxfs count="0"/>');
- rst.push('<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>');
- rst.push('</styleSheet>');
- return rst;
- }
- function writeSharedString(sharedStrList) {
- const rst = [];
- const pri_func_write = function(cellVal) {
- if (cellVal !== null) {
- if ((typeof cellVal === 'string') && cellVal.indexOf(' ') === 0) {
- rst.push('<si><t xml:space="preserve">' + cellVal + '</t></si>');
- } else {
- rst.push('<si><t>' + cellVal + '</t></si>');
- }
- }
- };
- if (sharedStrList && sharedStrList.length > 0) {
- rst.push(dftHeadXml + '\r\n');
- rst.push('<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' + sharedStrList.length + '" uniqueCount="' + sharedStrList.length + '">');
- const regExp = new RegExp('<', 'gm');
- for (let i = 0; i < sharedStrList.length; i++) {
- if (typeof sharedStrList[i] === 'string') {
- // 转换特殊字符,如 < , 则需要转义一下
- sharedStrList[i] = sharedStrList[i].replace(regExp, '<');
- if (sharedStrList[i].indexOf('|') >= 0) {
- // rst.push('<si><t>' + sharedStrList[i].split('|').join('\r\n') + '</t></si>');
- // rst.push('<si><t>' + sharedStrList[i].split('|').join('\n') + '</t></si>');
- pri_func_write(sharedStrList[i].split('|').join('\n'));
- } else {
- // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
- pri_func_write(sharedStrList[i]);
- }
- // rst.push('<si><t>' + sharedStrList[i].replace('|','\r\n') + '</t></si>');
- } else {
- // rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
- pri_func_write(sharedStrList[i]);
- }
- }
- rst.push('</sst>');
- }
- return rst;
- }
- function writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands) {
- const rst = [];
- const private_pushDftFont = function() {
- const font = {};
- if (!(stylesObj.fonts)) {
- stylesObj.fonts = [];
- }
- font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = '宋体'; // font name
- font.size = 12;
- font.charset = 134;
- font.colorIdx = '8';
- stylesObj.fonts.push(font);
- };
- const private_buildFirstDftStyle = function() {
- stylesObj.cellXfs = [];
- stylesObj.borders = [];
- const fontId = 0;
- const borderId = 0;
- const border = {};
- border[JV.PROP_LEFT] = {};
- border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
- border[JV.PROP_RIGHT] = {};
- border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
- border[JV.PROP_TOP] = {};
- border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
- border[JV.PROP_BOTTOM] = {};
- border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
- stylesObj.borders.push(border);
- const cellControl = pageData[JV.NODE_CONTROL_COLLECTION].Default;
- const sheetControl = {};
- sheetControl.fontId = fontId;
- sheetControl.borderId = borderId;
- sheetControl.fontAngle = 0;
- for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
- sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
- }
- stylesObj.cellXfs.push(sheetControl);
- };
- private_pushDftFont();
- private_buildFirstDftStyle();
- if (isSinglePage) {
- rst.push(writeSheet(pageData, null, paperSize, sharedStrList, stylesObj, null));
- } else {
- for (let i = 0; i < pageData.items.length; i++) {
- let appointedMergeBand = null;
- if (custSheetMergeBands && custSheetMergeBands.length > i) {
- appointedMergeBand = custSheetMergeBands[i];
- }
- rst.push(writeSheet(pageData, pageData.items[i], paperSize, sharedStrList, stylesObj, appointedMergeBand));
- }
- }
- return rst;
- }
- function writeSheet(pageData, sheetData, paperSize, sharedStrList, stylesObj, appointedMergeBand) {
- const rst = [];
- const xPos = [];
- let yPos = [];
- const yMultiPos = [];
- let currentMergeBorder = null;
- const headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
- let currentPageMergePos = null; // 在 JV.PAGING_OPTION_INFINITY 场合应用
- const private_pre_analyze_pos = function() {
- let cell;
- let pos;
- const private_array_sort = function(i1, i2) {
- let rst = 0;
- if (i1 > i2) { rst = 1; } else
- if (i1 < i2) rst = -1;
- return rst;
- };
- const self_analyze_sheet_pos = function(theShtData, theXPos, theYPos) {
- for (let i = 0; i < theShtData.cells.length; i++) {
- cell = theShtData.cells[i];
- pos = cell[JV.PROP_AREA][JV.PROP_LEFT];
- if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
- pos = cell[JV.PROP_AREA][JV.PROP_RIGHT];
- if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
- pos = cell[JV.PROP_AREA][JV.PROP_TOP];
- if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
- pos = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
- if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
- }
- };
- xPos.push(0);
- if (sheetData) {
- // current sheet data
- yPos.push(0);
- self_analyze_sheet_pos(sheetData, xPos, yPos);
- xPos.sort(private_array_sort);
- yPos.sort(private_array_sort);
- } else {
- // total data in one sheet
- 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);
- for (const shtItemData of pageData.items) {
- const tmpPos = [];
- tmpPos.push(0);
- self_analyze_sheet_pos(shtItemData, xPos, tmpPos);
- tmpPos.sort(private_array_sort);
- if (marginBottomPos - tmpPos[tmpPos.length - 1] > 10) {
- // 此逻辑是为了防止打印跨页(假设有些报表模板高度设置离底部margin还好远,导出excel后预览时会发现跨页现象(即下一页的某几行数据会挪到前一页来预览))
- tmpPos.push(marginBottomPos - 10);
- }
- yMultiPos.push(tmpPos);
- }
- xPos.sort(private_array_sort);
- yPos = yMultiPos[0];
- }
- };
- const private_getCellIdxStr = function(idx) {
- let rst = 'A';
- if (idx < 26) {
- rst = headerStr[idx];
- } else if (idx < 26 * 26 + 26) {
- const ti = Math.floor(idx / 26);
- const tj = idx % 26;
- rst = headerStr[ti - 1] + headerStr[tj];
- } else if (idx < 26 * 26 * 26 + 26) {
- const ti = Math.floor(idx / (26 * 26));
- const tj = Math.floor((idx - ti * 26 * 26) / 26);
- const tk = idx % 26;
- rst = headerStr[ti - 1] + headerStr[tj - 1] + headerStr[tk];
- }
- return rst;
- };
- const private_getSharedStrIdx = function(val) {
- let strVal = val;
- if (val === null || val === undefined || (typeof val === 'number' && isNaN(val))) {
- strVal = '';
- }
- let rst = sharedStrList.indexOf(strVal);
- if (rst < 0) {
- sharedStrList.push(strVal);
- rst = sharedStrList.length - 1;
- }
- return rst;
- };
- const private_getFontId = function(cell) {
- let rst = 0;
- let hasFont = false;
- if (!(stylesObj.fonts)) {
- stylesObj.fonts = [];
- // for (let i = 0; i < sheetData.font_collection)
- }
- let sheetFont = null;
- if (typeof cell[JV.PROP_FONT] === 'string') {
- sheetFont = pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]];
- } else {
- sheetFont = cell[JV.PROP_FONT];
- }
- for (let i = 0; i < stylesObj.fonts.length; i++) {
- const font = stylesObj.fonts[i];
- if (sheetFont) {
- 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)
- && 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]]
- && font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] === sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]) {
- hasFont = true;
- rst = i;
- break;
- }
- } else {
- break;
- }
- }
- if (!hasFont) {
- const font = {};
- font[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_NAME]]; // font name
- // font.size = Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
- font.size = Math.floor(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
- font.charset = 134;
- font.colorIdx = '8';
- font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]]; // font bold
- font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]]; // font italic
- font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]] = sheetFont[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]]; // font underline
- stylesObj.fonts.push(font);
- rst = stylesObj.fonts.length - 1;
- }
- return rst;
- };
- let private_checkBorder = function(cell, border, sheetBorder) {
- let rst = true, borderLineWidths = [], sheetBorderLineWidths = [];
- borderLineWidths.push(border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]);
- borderLineWidths.push(border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]);
- borderLineWidths.push(border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]);
- borderLineWidths.push(border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]);
- if (sheetBorder[JV.PROP_LEFT] && sheetBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]) {
- sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true));
- } else {
- sheetBorderLineWidths.push(0);
- }
- if (sheetBorder[JV.PROP_RIGHT] && sheetBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]) {
- sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true));
- } else {
- sheetBorderLineWidths.push(0);
- }
- if (sheetBorder[JV.PROP_TOP] && sheetBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]) {
- sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false));
- } else {
- sheetBorderLineWidths.push(0);
- }
- if (sheetBorder[JV.PROP_BOTTOM] && sheetBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]) {
- sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false));
- } else {
- sheetBorderLineWidths.push(0);
- }
- for (let i = 0; i < 4; i++) {
- if (borderLineWidths[i] != sheetBorderLineWidths[i]) {
- rst = false;
- break;
- }
- }
- return rst;
- };
- let private_chkAndGetMergeLine = function(cell, sheetBorder, borderStr, needFurtherChk) {
- let rst = 0,
- // mergeBorder = (sheetData)?sheetData[JV.PROP_PAGE_MERGE_BORDER]:pageData[JV.BAND_PROP_MERGE_BAND],
- mergeBorder = currentMergeBorder,
- mergeBand = pageData[JV.BAND_PROP_MERGE_BAND]
- ;
- if (appointedMergeBand !== null) {
- mergeBand = appointedMergeBand;
- }
- if (sheetBorder[borderStr] && sheetBorder[borderStr][JV.PROP_LINE_WEIGHT] !== undefined) {
- rst = sheetBorder[borderStr][JV.PROP_LINE_WEIGHT];
- }
- if (currentPageMergePos) {
- let side = currentPageMergePos[borderStr];
- if (side.indexOf(cell[JV.PROP_AREA][borderStr]) >= 0) {
- if (needFurtherChk) {
- let topSide = currentPageMergePos[JV.PROP_TOP];
- let bottomSide = currentPageMergePos[JV.PROP_BOTTOM];
- for (let i = 0; i < topSide.length; i++) {
- if (cell[JV.PROP_AREA][JV.PROP_TOP] >= topSide[i]) {
- if (cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= bottomSide[i]) {
- let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
- rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
- break;
- }
- } else {
- break;
- }
- }
- } else {
- let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
- rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
- }
- }
- } else {
- if (cell[JV.PROP_AREA][borderStr] === mergeBorder[borderStr]) {
- let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
- if (needFurtherChk) {
- if (cell[JV.PROP_AREA][JV.PROP_TOP] >= mergeBorder[JV.PROP_TOP] &&
- cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= mergeBorder[JV.PROP_BOTTOM]) {
- rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
- }
- } else {
- rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
- }
- }
- }
- return parseInt(rst);
- };
- let private_getIniBorder = function() {
- let rst = {};
- rst[JV.PROP_LEFT] = {};
- rst[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
- rst[JV.PROP_RIGHT] = {};
- rst[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
- rst[JV.PROP_TOP] = {};
- rst[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
- rst[JV.PROP_BOTTOM] = {};
- rst[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
- return rst;
- };
- let private_getBorderId = function(cell) {
- let rst = 0, hasBorder = false;
- if (!(stylesObj.borders)) {
- stylesObj.borders = [];
- }
- let sheetBorder = pageData[JV.NODE_STYLE_COLLECTION][cell.style];
- let mergedBorder = private_getIniBorder();
- mergedBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT, true);
- mergedBorder[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT, true);
- mergedBorder[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP, false);
- mergedBorder[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM, false);
- for (let i = 0; i < stylesObj.borders.length; i++) {
- let border = stylesObj.borders[i];
- if (private_checkBorder(cell, border, mergedBorder)) {
- hasBorder = true;
- rst = i;
- break;
- }
- }
- if (!hasBorder) {
- stylesObj.borders.push(mergedBorder);
- rst = stylesObj.borders.length - 1;
- }
- return rst;
- };
- let private_checkControl = function(cellControl, sheetControl) {
- let rst = true;
- for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
- if (cellControl[JV.CONTROL_PROPS[i]] != sheetControl[JV.CONTROL_PROPS[i]]) {
- rst = false;
- break;
- }
- }
- return rst;
- };
- let private_getStyleId = function(cell) {
- let rst = 1, hasStyle = false;
- if (!(stylesObj.cellXfs)) stylesObj.cellXfs = [];
- let fontId = private_getFontId(cell);
- let fontAngle = 0;
- if (typeof cell[JV.PROP_FONT] === "string") {
- fontAngle = parseInt(pageData[JV.NODE_FONT_COLLECTION][cell[JV.PROP_FONT]].FontAngle);
- } else {
- fontAngle = parseInt(cell[JV.PROP_FONT].FontAngle);
- }
- let borderId = private_getBorderId(cell);
- let cellControl = null;
- if (typeof cell[JV.PROP_CONTROL] === "string") {
- cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
- } else {
- cellControl = cell[JV.PROP_CONTROL];
- }
- for (let i = 0; i < stylesObj.cellXfs.length; i++) {
- let sheetControl = stylesObj.cellXfs[i];
- if (sheetControl.fontId === fontId && sheetControl.borderId === borderId) {
- if (private_checkControl(cellControl, sheetControl)) {
- rst = i;
- hasStyle = true;
- break;
- }
- }
- }
- if (!hasStyle) {
- let sheetControl = {};
- sheetControl.fontId = fontId;
- sheetControl.borderId = borderId;
- sheetControl.fontAngle = fontAngle;
- for (let i = 0; i < JV.CONTROL_PROPS.length; i++) {
- sheetControl[JV.CONTROL_PROPS[i]] = cellControl[JV.CONTROL_PROPS[i]];
- }
- stylesObj.cellXfs.push(sheetControl);
- rst = stylesObj.cellXfs.length - 1;
- }
- return rst;
- };
- let private_setCols = function(){
- //remark: 1 excel unit width = 2.117 mm
- rst.push('<cols>');
- let w = 0;
- for (let i = 1; i < xPos.length - 1; i++) {
- w = 1.0 * (xPos[i + 1] - xPos[i]) / DPI * 25.4 / 2.117;
- w = Math.round(w * 1000) / 1000;
- rst.push('<col min="' + i +'" max="' + i +'" width="' + w + '" customWidth="1"/>');
- }
- rst.push('<col min="' + (xPos.length - 1) +'" max="' + (xPos.length - 1) +'" width="' + 10 + '" customWidth="1"/>');
- rst.push('</cols>');
- };
- let private_setMergedCells = function() {
- let cell, idxR, idxL, idxT, idxB, cnt = 0;
- rst.push('<mergeCells count="?">');
- let startIdx = rst.length - 1;
- let self_setMergedCells = function (theData, theYPos, offsetY) {
- for (let i = 0; i < theData.cells.length; i++) {
- cell = theData.cells[i];
- idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
- idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
- idxB = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
- idxT = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_TOP]);
- if (idxR - idxL > 1 || idxB - idxT > 1) {
- rst.push('<mergeCell ref="' + private_getCellIdxStr(idxL - 1) + (idxT + offsetY) + ':' + private_getCellIdxStr(idxR - 2) + (idxB - 1 + offsetY) + '"/>');
- cnt++;
- }
- }
- }
- if (sheetData) {
- self_setMergedCells(sheetData, yPos, 0);
- } else {
- let osY = 0;
- for (let i = 0; i < pageData.items.length; i++) {
- let shtItemData = pageData.items[i];
- let tmpPos = yMultiPos[i];
- self_setMergedCells(shtItemData, tmpPos, osY);
- osY += tmpPos.length - 2;
- }
- }
- rst[startIdx] = '<mergeCells count="' + cnt + '">';
- rst.push('</mergeCells>');
- };
- let private_setSheetData = function(){
- //remark: 1 excel unit height = 0.3612 mm
- rst.push('<sheetData>');
- let spanX = xPos.length - 2, cellIdx = 0, h = 0
- ;
- let self_setDataEx = function (theShtData, theYPos, offsetY) {
- let rows = [];
- //1. build full set of blank rows/cells
- for (let i = 1; i < theYPos.length - 1; i++) {
- let rowObj = {};
- h = (theYPos[i+1] - theYPos[i]) / DPI * 25.4 / 0.3612;
- h = Math.round(h * 1000) / 1000;
- rowObj.height = h;
- rowObj.r = i + offsetY;
- rowObj.items = [];
- rows.push(rowObj);
- for (let j = 1; j < xPos.length - 1; j++) {
- let colIdxStr = private_getCellIdxStr(j - 1);
- let cellObj = {};
- cellObj.r = colIdxStr + (i + offsetY);
- cellObj.s = 0;
- cellObj.isBlank = true;
- rows[i - 1].items.push(cellObj);
- }
- }
- //2. then fill up the cell style-ids and values
- let rowIdx1 = 0, colIdx1 = 0, rowIdx2 = 0, colIdx2 = 0, colIdxStr = '';
- for (let cIdx = 0; cIdx < theShtData.cells.length; cIdx++) {
- let styleIdx = private_getStyleId(theShtData.cells[cIdx]);
- //colIdxStr = private_getCellIdxStr(j - 1);
- rowIdx1 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_TOP]);
- colIdx1 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_LEFT]);
- let cellObj = rows[rowIdx1 - 1].items[colIdx1 - 1];
- cellObj.s = styleIdx;
- cellObj.isBlank = false;
- if (!(strUtil.isEmptyString(theShtData.cells[cIdx][JV.PROP_VALUE]))) {
- let valIdx = private_getSharedStrIdx(theShtData.cells[cIdx][JV.PROP_VALUE]);
- cellObj.v = valIdx;
- }
- rowIdx2 = theYPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_BOTTOM]);
- colIdx2 = xPos.indexOf(theShtData.cells[cIdx][JV.PROP_AREA][JV.PROP_RIGHT]);
- if ((rowIdx2 - rowIdx1 > 1) || (colIdx2 - colIdx1 > 1)) {
- for (let i = 0; i < rowIdx2 - rowIdx1; i++) {
- for (let j = 0; j < colIdx2 - colIdx1; j++) {
- if (i === 0 && j === 0) continue;
- cellObj = rows[rowIdx1 - 1 + i].items[colIdx1 - 1 + j];
- cellObj.s = styleIdx;
- cellObj.isBlank = true;
- }
- }
- }
- }
- //3. then fill up rst
- for (let i = 0; i < rows.length; i++) {
- rst.push('<row r="' + (i + 1 + offsetY) + '" spans="1:' + spanX + '" ht="' + rows[i].height + '" customHeight="1">');
- for (let j = 0; j < rows[i].items.length; j++) {
- let cellObj = rows[i].items[j];
- if (cellObj.v === undefined) {
- rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '"/>');
- } else {
- rst.push('<c r="' + cellObj.r + '" s="' + cellObj.s + '" t="s">');
- rst.push('<v>' + cellObj.v + '</v>');
- rst.push('</c>');
- }
- }
- rst.push('</row>');
- }
- //4. maybe need to dispose the memory
- //...
- };
- if (sheetData) {
- //current sheet data
- currentPageMergePos = sheetData[JV.PAGE_SPECIAL_MERGE_POS];
- currentMergeBorder = sheetData[JV.PROP_PAGE_MERGE_BORDER];
- self_setDataEx(sheetData, yPos, 0);
- } else {
- //total data in one sheet
- let cnt = 0;
- for (let i = 0; i < pageData.items.length; i++) {
- let shtItemData = pageData.items[i];
- currentPageMergePos = shtItemData[JV.PAGE_SPECIAL_MERGE_POS];
- currentMergeBorder = shtItemData[JV.PROP_PAGE_MERGE_BORDER];
- let tmpPos = yMultiPos[i];
- cellIdx = 0;
- self_setDataEx(shtItemData, tmpPos, cnt);
- cnt += tmpPos.length - 2;
- }
- }
- rst.push('</sheetData>');
- };
- private_pre_analyze_pos();
- rst.push(dftHeadXml + '\r\n');
- rst.push('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
- let colStr = private_getCellIdxStr(xPos.length - 3);
- rst.push('<dimension ref="A1:' + colStr + '' + yPos.length + '"/>');
- rst.push('<sheetViews><sheetView tabSelected="1" workbookViewId="0">');
- //rst.push('<selection sqref="A1:' + colStr + '1"/>');
- rst.push('<selection sqref="A1:A1"/>');
- rst.push('</sheetView></sheetViews>');
- rst.push('<sheetFormatPr defaultRowHeight="13.5"/>');
- private_setCols();
- private_setSheetData();
- private_setMergedCells();
- rst.push('<phoneticPr fontId="1" type="noConversion"/>');
- // rst.push('<pageMargins left="0.315" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
- rst.push('<pageMargins left="' + (parseFloat(pageData[JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_LEFT]) * 0.39375) +
- '" right="0.215" top="0.315" bottom="0.315" header="0" footer="0"/>');
- let paperSizeIdx = JV.PAGES_SIZE_STR.indexOf(paperSize);
- let pStr = '';
- if (paperSizeIdx >= 0) {
- pStr = 'paperSize="' + JV.PAGES_SIZE_IDX[paperSizeIdx] + '"';
- }
- let orientationStr = (pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][0] > pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1])?'landscape':'portrait';
- if (currentPageMergePos) {
- orientationStr = (currentPageMergePos[JV.NODE_PAGE_SIZE][0] > currentPageMergePos[JV.NODE_PAGE_SIZE][1])?'landscape':'portrait';
- }
- rst.push('<pageSetup ' + pStr + ' fitToWidth="0" fitToHeight="0" orientation="' + orientationStr + '" />');
- rst.push('<headerFooter alignWithMargins="0"/>');
- rst.push('</worksheet>');
- return rst;
- }
- function mergeProperties(orgObj, newObj) {
- let orgPropArr = [], newPropArr = [];
- for (let p in orgObj) {
- orgPropArr.push(p);
- }
- for (let p in newObj) {
- newPropArr.push(p);
- }
- for (let i = 0; i < newPropArr.length; i++) {
- if (orgPropArr.indexOf(newPropArr[i]) < 0) {
- orgObj[newPropArr[i]] = newObj[newPropArr[i]];
- }
- }
- }
- module.exports = {
- exportExcel: function (pageData, paperSize, fName, options, custSheetNames, custSheetMergeBands, callback) {
- let rptOptions = ({singlePage: false, fileName: 'report'});
- if (options === 'true' || options === true) {
- rptOptions.singlePage = true;
- }
- let isSinglePage = rptOptions.singlePage;
- let sheets = [];
- if (isSinglePage) {
- sheets.push({sheetName: '全部页'});
- } else {
- if (custSheetNames && custSheetNames.length === pageData.items.length) {
- for (let i = 0; i < pageData.items.length; i++) {
- sheets.push({sheetName: custSheetNames[i]});
- }
- } else {
- for (let i = 0; i < pageData.items.length; i++) {
- sheets.push({sheetName: '第' + (i + 1) + '页'});
- }
- }
- }
- //1.
- let file = '[Content_Types].xml';
- let data = writeContentTypes(sheets, isSinglePage);
- let zip = new JSZip();
- zip.file(file, data.join(''), {compression: 'DEFLATE'});
- //2.
- let zip_rels = zip.folder('_rels');
- file = '.rels';
- data = writeRootRels();
- zip_rels.file(file, data.join(''), {compression: 'DEFLATE'});
- //3.
- let zip_docProps = zip.folder('docProps');
- file = 'app.xml';
- data = writeApp(sheets, isSinglePage);
- zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
- file = 'core.xml';
- data = writeCore();
- zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
- //4.
- let zip_xl = zip.folder('xl');
- file = 'workbook.xml';
- data = writeXlWorkBook(sheets, isSinglePage);
- zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
- let zip_rels2 = zip_xl.folder('_rels');
- file = 'workbook.xml.rels';
- data = writeXlRels(sheets, isSinglePage);
- zip_rels2.file(file, data.join(''), {compression: 'DEFLATE'});
- //5.
- let zip_theme = zip_xl.folder('theme');
- file = 'theme1.xml';
- data = writeTheme();
- zip_theme.file(file, data, {compression: 'DEFLATE'});
- //6.
- let zip_worksheets = zip_xl.folder('worksheets');
- let sharedStrList = [], stylesObj = {};
- data = writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands);
- if (isSinglePage) {
- for (let i = 0; i < 1; i++) {
- file = 'sheet' + (i + 1) + '.xml';
- zip_worksheets.file(file, data[i].join(''), {compression: 'DEFLATE'});
- }
- } else {
- for (let i = 0; i < data.length; i++) {
- file = 'sheet' + (i + 1) + '.xml';
- zip_worksheets.file(file, data[i].join(''), {compression: 'DEFLATE'});
- }
- }
- file = 'sharedStrings.xml';
- data = writeSharedString(sharedStrList);
- zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
- file = 'styles.xml';
- data = writeStyles(stylesObj);
- zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
- if (fName) {
- // let newName = '' + (new Date()).valueOf();
- let newName = uuidV1();
- // console.log('current path: ' + __dirname);
- // const filePath = this.app.baseDir + '/app/public/download/';
- zip.generateNodeStream({type:'nodebuffer',streamFiles:true})
- .pipe(fs.createWriteStream(__dirname.slice(0, __dirname.length - 13) + '/public/download/' + newName + '.xlsx'))
- .on('finish', function () {
- // JSZip generates a readable stream with a "end" event,
- // but is piped here in a writable stream which emits a "finish" event.
- console.log(newName + ".xlsx was written.");
- if (callback) callback(null, newName);
- }
- );
- } else {
- //return zip.generateNodeStream({type:'nodebuffer',streamFiles:true});
- return zip;
- }
- },
- exportExcelInOneBook: function (pageDataArray, paperSize, fName, callback) {
- let me = this, newPageData = {};
- //1. 重新编排一下数据,把一份报表的pageData合并到一起作为一个Sheet输出(需要重新调整数据纵向坐标),多份报表数据就形成多个Sheet
- // -- 简单来说,就是重新包装数据
- try {
- // 1.1 newPageData外围属性
- let newContrl = {}, newFont = {}, newStyle = {}, custMergeBands = [];
- for (let i = 0; i < pageDataArray.length; i++) {
- mergeProperties(newContrl, pageDataArray[i][JV.NODE_CONTROL_COLLECTION]);
- mergeProperties(newFont, pageDataArray[i][JV.NODE_FONT_COLLECTION]);
- mergeProperties(newStyle, pageDataArray[i][JV.NODE_STYLE_COLLECTION]);
- }
- newPageData[JV.NODE_CONTROL_COLLECTION] = newContrl;
- newPageData[JV.NODE_FONT_COLLECTION] = newFont;
- newPageData[JV.NODE_STYLE_COLLECTION] = newStyle;
- newPageData[JV.NODE_PAGE_INFO] = pageDataArray[0][JV.NODE_PAGE_INFO];
- newPageData[JV.BAND_PROP_MERGE_BAND] = pageDataArray[0][JV.BAND_PROP_MERGE_BAND];
- // 1.2 重新设置pageDataArray的各个cell的Top/Bottom坐标
- let sheetNames = [], newPagePos = [];
- for (let i = 0; i < pageDataArray.length; i++) {
- let offsetY = 0;
- let mergeBand = {};
- custMergeBands.push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND]);
- //备注:不同的报表有可能有不同的边框,如封面表就是无边框的
- mergeBand[JV.PROP_LEFT] = [];
- mergeBand[JV.PROP_RIGHT] = [];
- mergeBand[JV.PROP_TOP] = [];
- mergeBand[JV.PROP_BOTTOM] = [];
- newPagePos.push(mergeBand);
- mergeBand[JV.PROP_LEFT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_LEFT]);
- mergeBand[JV.PROP_RIGHT].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_RIGHT]);
- sheetNames.push(pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_MAIN_INFO_RPT_NAME]);
- for (let j = 0; j < pageDataArray[i].items.length; j++) {
- let maxY = 0, minY = 100000;
- if (pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS]) {
- let pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_TOP][0] + offsetY;
- mergeBand[JV.PROP_TOP].push(pos);
- pos = pageDataArray[i].items[j][JV.PAGE_SPECIAL_MERGE_POS][JV.PROP_BOTTOM][0] + offsetY;
- mergeBand[JV.PROP_BOTTOM].push(pos);
- } else if (pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER]) {
- let pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_TOP] + offsetY;
- mergeBand[JV.PROP_TOP].push(pos);
- pos = pageDataArray[i].items[j][JV.PROP_PAGE_MERGE_BORDER][JV.PROP_BOTTOM] + offsetY;
- mergeBand[JV.PROP_BOTTOM].push(pos);
- } else {
- mergeBand[JV.PROP_TOP].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_TOP] + offsetY);
- mergeBand[JV.PROP_BOTTOM].push(pageDataArray[i][JV.BAND_PROP_MERGE_BAND][JV.PROP_BOTTOM] + offsetY);
- }
- for (let k = 0; k < pageDataArray[i].items[j].cells.length; k++) {
- if (maxY < pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM]) {
- maxY = pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM];
- }
- if (minY > pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP]) {
- minY = pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP];
- }
- pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
- pageDataArray[i].items[j].cells[k][JV.PROP_AREA][JV.PROP_TOP] += offsetY;
- }
- 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;
- offsetY += (maxY - minY);
- if (bottomGap > 10) {
- offsetY += (bottomGap - 10);
- }
- }
- }
- //2. newPageData的items属性
- newPageData.items = [];
- for (let i = 0; i < pageDataArray.length; i++) {
- let pageItem = {};
- pageItem[JV.PROP_PAGE_SEQ] = i + 1;
- pageItem[JV.PROP_CELLS] = [];
- for (let j = 0; j < pageDataArray[i].items.length; j++) {
- for (let k = 0; k < pageDataArray[i].items[j].cells.length; k++) {
- pageItem[JV.PROP_CELLS].push(pageDataArray[i].items[j].cells[k]);
- }
- }
- newPagePos[i][JV.NODE_PAGE_SIZE] = pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE];
- pageItem[JV.PAGE_SPECIAL_MERGE_POS] = newPagePos[i];
- newPageData.items.push(pageItem);
- }
- //3. everything is ok, then call me
- me.exportExcel(newPageData, paperSize, fName, 'false', sheetNames, custMergeBands, callback);
- // fsUtil.writeObjToFile(newPageData, 'D:/GitHome/ConstructionOperation/tmp/combinedHeader.js');
- } catch (e) {
- console.log(e);
- }
- }
- }
|