ConstructionCost/modules/reports/util/rpt_excel_util.js

/**
 * Created by Tony on 2017/4/1.
 */
let JV = require('../rpt_component/jpc_value_define');
let fs = require('fs');
let JSZip = require("jszip");
let strUtil = require('../../../public/stringUtil');
let jpcCmnHelper = require('../rpt_component/helper/jpc_helper_common');
let DPI = jpcCmnHelper.getScreenDPI()[0];
let fsUtil = require('../../../public/fsUtil');
const dftHeadXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
const uuidV1 = require('uuid/v1');

function writeContentTypes(sheets, isSinglePage) {
    let 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(){
    let 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) {
    let 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() {
    let rst = [];
    let 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>');
    let dt = new Date();
    dt.setDate(dt.getDate() - 8/24); //it's GMT time, so please add the server offset time ( -8 hours )
    let 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('');
    rst.push('</cp:coreProperties>');
    return rst;
}
function writeXlWorkBook(sheets, isSinglePage){
    let 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){
    let rst = [], 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(){
    let rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r');
    return rst;
}
function writeStyles(stylesObj){
    let 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++) {
        let font = stylesObj.fonts[i];
        rst.push('<font>');
        if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[3]])) {
            rst.push('<b/>');
        }
        if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[4]])) {
            rst.push('<i/>');
        }
        if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[5]])) {
            rst.push('<u/>');
        }
        rst.push('<sz val="' + font.size + '"/>');
        rst.push('<color indexed="' + font.colorIdx + '"/>');
        rst.push('<name val="' + font[JV.FONT_PROPS[0]] + '"/>');
        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 + '">');
    let 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++) {
        let 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++) {
        let 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, 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){
    let rst = [];
    let 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 + '">');
        let regExp = new RegExp("<", "gm");
        let regExp1 = new RegExp(">", "gm");
        let regExp2 = new RegExp("&", "gm");
        for (let i = 0; i < sharedStrList.length; i++) {
            if (typeof sharedStrList[i] === 'string') {
                //转换特殊字符，如 < , 则需要转义一下
                sharedStrList[i] = sharedStrList[i].replace(regExp, "&lt;").replace(regExp1, '&gt;').replace(regExp2, '&amp;');
                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 _setupPgBrks(pageData) {
    pageData.pageBreaks = [];
    let pgBrkIdx = 0;
    const pgBrk = [];
    for (const page of pageData.items) {
        const maxBottomArr = [];
        for (const cell of page.cells) {
            if (maxBottomArr.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]) < 0) {
                maxBottomArr.push(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
            }
        }
        pgBrkIdx += maxBottomArr.length;
        pgBrk.push(pgBrkIdx);
    }
    pageData.pageBreaks.push(pgBrk);
}

function writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands){
    let rst = [];
    let private_pushDftFont = function(){
        let font = {};
        if (!(stylesObj.fonts)) {
            stylesObj.fonts = [];
        }
        font[JV.FONT_PROPS[0]] = "宋体"; //font name
        font.size = 12;
        font.charset = 134;
        font.colorIdx = "8";
        stylesObj.fonts.push(font);
    };
    let private_buildFirstDftStyle = function () {
        stylesObj.cellXfs = [];
        stylesObj.borders = [];
        let fontId = 0;
        let borderId = 0;
        let 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);

        let cellControl = pageData[JV.NODE_CONTROL_COLLECTION].Default;
        let 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, true, -1));
    } 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, i === 0, i));
        }
    }
    return rst;
}
function writeSheet(pageData, sheetData, paperSize, sharedStrList, stylesObj, appointedMergeBand, isFirstSheet, pgBrkIdx){
    let rst = [], xPos = [], yPos = [], yMultiPos = [], currentMergeBorder = null,
        headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    let currentPageMergePos = null; //在 JV.PAGING_OPTION_INFINITY 场合应用
    let private_pre_analyze_pos = function(){
        let cell, pos;
        let self_analyze_sheet_pos = function (theShtData, theXPos, theYPos) {
            // theShtData.cells.sort(function(cell1, cell2) {
            //     let rst = 0;
            //     if (cell1[JV.PROP_AREA][JV.PROP_TOP] > cell2[JV.PROP_AREA][JV.PROP_TOP]) {
            //         rst = 1;
            //     } else if (cell1[JV.PROP_AREA][JV.PROP_TOP] < cell2[JV.PROP_AREA][JV.PROP_TOP]) {
            //         rst = -1;
            //     } else {
            //         if (cell1[JV.PROP_AREA][JV.PROP_LEFT] > cell2[JV.PROP_AREA][JV.PROP_LEFT]) {
            //             rst = 1;
            //         } else if (cell1[JV.PROP_AREA][JV.PROP_LEFT] < cell2[JV.PROP_AREA][JV.PROP_LEFT]) {
            //             rst = -1;
            //         }
            //     }
            //     return rst;
            // });
            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
            let marginBottomPos = Math.round( (pageData[JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1] - parseFloat(pageData[JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_BOTTOM]) / 2.54 ) * DPI);
            for (let shtItemData of pageData.items) {
                let 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];
        }
    };
    let private_array_sort = function(i1, i2){
        let rst = 0;
        if (i1 > i2) {rst = 1} else
        if (i1 < i2) rst = -1;
        return rst;
    };
    let private_getCellIdxStr = function(idx){
        let rst = 'A';
        if (idx < 26) {
            rst = headerStr[idx];
        } else if (idx < 26*26+26) {
            let ti = Math.floor(idx / 26), tj = idx % 26;
            rst = headerStr[ti - 1] + headerStr[tj];
        } else if (idx < 26*26*26+26) {
            let ti = Math.floor(idx / (26*26)), tj = Math.floor((idx - ti * 26*26) / 26), tk = idx % 26;
            rst = headerStr[ti - 1] + headerStr[tj-1] + headerStr[tk];
        }
        return rst;
    };
    let 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;
    };
    let private_getFontId = function(cell) {
        let rst = 0, 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++) {
            let font = stylesObj.fonts[i];
            if (sheetFont) {
                // if (font[JV.FONT_PROPS[0]] === sheetFont[JV.FONT_PROPS[0]] && font.size === Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4)
                if (font[JV.FONT_PROPS[0]] === sheetFont[JV.FONT_PROPS[0]] && font.size === Math.floor(sheetFont[JV.FONT_PROPS[1]] * 3 / 4)
                    && font[JV.FONT_PROPS[3]] === sheetFont[JV.FONT_PROPS[3]] && font[JV.FONT_PROPS[4]] === sheetFont[JV.FONT_PROPS[4]]
                    && font[JV.FONT_PROPS[5]] === sheetFont[JV.FONT_PROPS[5]] ) {
                    hasFont = true;
                    rst = i;
                    break;
                }
            } else {
                break;
            }
        }
        if (!hasFont) {
            let font = {};
            font[JV.FONT_PROPS[0]] = sheetFont[JV.FONT_PROPS[0]]; //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[3]] = sheetFont[JV.FONT_PROPS[3]]; //font bold
            font[JV.FONT_PROPS[4]] = sheetFont[JV.FONT_PROPS[4]]; //font italic
            font[JV.FONT_PROPS[5]] = sheetFont[JV.FONT_PROPS[5]]; //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 + '"/>');
    if (isFirstSheet) {
        rst.push('<sheetViews><sheetView showGridLines="1" tabSelected="1" workbookViewId="0">');
    } else {
        rst.push('<sheetViews><sheetView showGridLines="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"/>');
    //插入分页符---------------------------
    if (pageData.pageBreaks) {
        let pgBrks = [];
        if (sheetData !== null) {
            pgBrks = pageData.pageBreaks[pgBrkIdx];
        } else {
            pgBrks = pageData.pageBreaks[0];
        }
        if (pgBrks.length > 1) {
            rst.push('<rowBreaks count="' + pgBrks.length + '" manualBreakCount="' + pgBrks.length + '" >');
            for (let pbi = 0; pbi < pgBrks.length; pbi++) {
                rst.push('<brk id="' + pgBrks[pbi] + '" max="16383" man="1" />');
            }
            rst.push('</rowBreaks>');
        }
    }
    //插入分页符结束---------------------------
    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) {
            //加分页符(分页符要在外部处理好)-------------------
            _setupPgBrks(pageData);
            //加分页符结束-------------------
            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();

            zip.generateNodeStream({type:'nodebuffer',streamFiles:true})
                .pipe(fs.createWriteStream(__dirname.slice(0, __dirname.length - 21) + '/tmp/' + 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(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]);

                //加分页符(分页符要在外部处理好)-------------------
                _setupPgBrks(pageDataArray[i]);
                //加分页符结束-------------------

                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 = [];
            newPageData.pageBreaks = [];
            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);
                //重新分配分页符()------------------
                if (pageDataArray[i].pageBreaks.length === 1) {
                    newPageData.pageBreaks.push(pageDataArray[i].pageBreaks[0]);
                } else {
                    newPageData.pageBreaks.push([]);
                }
                //重新分配分页符结束------------------
            }
            //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);
        }
    }
}