Calculation/app/reports/util/rpt_excel_util.js

'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, hasSignature) {
    const rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">');
    rst.push('<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>');
    rst.push('<Default Extension="xml" ContentType="application/xml"/>');
    if (hasSignature) {
        rst.push('<Default Extension="png" ContentType="image/png"/>');
    }
    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('<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"/>');
        if (hasSignature) {
            rst.push('<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+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"/>');
            if (hasSignature) {
                rst.push('<Override PartName="/xl/drawings/drawing' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+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, hasSignature) {
    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"/>');
    if (hasSignature) {
        rst.push('<extLst>');
        rst.push('<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">');
        rst.push('<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>');
        rst.push('</ext>');
        rst.push('<ext uri="{9260A510-F301-46a8-8635-F512D64BE5F5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">');
        rst.push('<x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1"/>');
        rst.push('</ext>');
        rst.push('</extLst>');
    }
    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, '&lt;');
                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, hasSignature) {
    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();
    let sheetIdx = 0;
    if (isSinglePage) {
        rst.push(writeSheet(pageData, null, paperSize, sharedStrList, stylesObj, null, hasSignature, sheetIdx));
    } 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, hasSignature, sheetIdx));
            sheetIdx++;
        }
    }
    return rst;
}
function getProperSignatureArea(cell, control) {
    // 约定默认长宽比例是2:1，图片分辨率是600*300
    const rst = [0, 0, 0, 0]; // left, top, right, bottom
    if (cell && cell[JV.PROP_AREA]) {
        let width = cell[JV.PROP_AREA][JV.PROP_RIGHT] - cell[JV.PROP_AREA][JV.PROP_LEFT],
            height = cell[JV.PROP_AREA][JV.PROP_BOTTOM] - cell[JV.PROP_AREA][JV.PROP_TOP];
        if (width > height * 2) {
            width = height * 2;
        } else {
            height = width / 2;
        }
        switch (control[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_HORIZON]]) {
            case 'left':
                rst[0] = cell[JV.PROP_AREA][JV.PROP_LEFT];
                rst[1] = cell[JV.PROP_AREA][JV.PROP_TOP];
                rst[2] = rst[0] + width;
                rst[3] = rst[1] + height;
                break;
            case 'right':
                rst[2] = cell[JV.PROP_AREA][JV.PROP_RIGHT];
                rst[3] = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
                rst[0] = rst[2] - width;
                rst[1] = rst[3] - height;
                break;
            default:
                // center
                rst[0] = (cell[JV.PROP_AREA][JV.PROP_LEFT] + cell[JV.PROP_AREA][JV.PROP_RIGHT] - width) / 2;
                rst[1] = cell[JV.PROP_AREA][JV.PROP_TOP];
                rst[2] = rst[0] + width;
                rst[3] = rst[1] + height;
                break;
        }
    }
    // rst[0] = rst[0] + JpcCanvasOutput.offsetX;
    // rst[2] = rst[2] + JpcCanvasOutput.offsetX;
    // rst[1] = rst[1] + JpcCanvasOutput.offsetY;
    // rst[3] = rst[3] + JpcCanvasOutput.offsetY;
    return rst;
}
function preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos) {
    let cell;
    let pos;
    const private_array_sort = function(i1, i2) {
        return (i1 - i2);
    };
    const private_set_cell_pos = function(theCell, theXPos, theYPos) {
        pos = theCell[JV.PROP_AREA][JV.PROP_LEFT];
        if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
        pos = theCell[JV.PROP_AREA][JV.PROP_RIGHT];
        if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
        pos = theCell[JV.PROP_AREA][JV.PROP_TOP];
        if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
        pos = theCell[JV.PROP_AREA][JV.PROP_BOTTOM];
        if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
    };
    const private_set_cell_pos2 = function(area, theXPos, theYPos) {
        pos = area[0];
        if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
        pos = area[2];
        if (theXPos.indexOf(pos) < 0) theXPos.push(pos);
        pos = area[1];
        if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
        pos = area[3];
        if (theYPos.indexOf(pos) < 0) theYPos.push(pos);
    };
    const self_analyze_sheet_pos = function(theShtData, theXPos, theYPos) {
        for (let i = 0; i < theShtData[JV.PROP_CELLS].length; i++) {
            cell = theShtData[JV.PROP_CELLS][i];
            private_set_cell_pos(cell, theXPos, theYPos);
        }
        for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_CELLS].length; i++) {
            cell = theShtData[JV.PROP_SIGNATURE_CELLS][i];
            private_set_cell_pos(cell, theXPos, theYPos);
            let cellControl;
            if (typeof cell[JV.PROP_CONTROL] === 'string') {
                cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
            } else {
                cellControl = cell[JV.PROP_CONTROL];
            }
            const area = getProperSignatureArea(cell, cellControl);
            private_set_cell_pos2(area, theXPos, theYPos);
        }
        for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_DATE_CELLS].length; i++) {
            cell = theShtData[JV.PROP_SIGNATURE_DATE_CELLS][i];
            private_set_cell_pos(cell, theXPos, theYPos);
        }
    };
    xPos.push(0);
    if (sheetData) {
        // current sheet data
        console.log('preAnalyzePos not single');
        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);
        // console.log('preAnalyzePos isSingle start');
        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);
            }
            // console.log('preAnalyzePos push to yMultiPos');
            yMultiPos.push(tmpPos);
        }
        xPos.sort(private_array_sort);
        yPos.splice(0, yPos.length);
        for (const p of yMultiPos[0]) {
            yPos.push(p);
        }
    }
}
function writeSheet(pageData, sheetData, paperSize, sharedStrList, stylesObj, appointedMergeBand, hasSignature, sheetIdx) {
    const rst = [];
    const xPos = [];
    const yPos = [];
    const yMultiPos = [];
    let currentMergeBorder = null;
    const headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    let currentPageMergePos = null; // 在 JV.PAGING_OPTION_INFINITY 场合应用
    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;
    };
    const private_chkAndGetMergeLine = function(cell, sheetBorder, borderStr, needFurtherChk) {
        let rst = 0;
        const mergeBorder = currentMergeBorder;
        let 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) {
            const side = currentPageMergePos[borderStr];
            if (side.indexOf(cell[JV.PROP_AREA][borderStr]) >= 0) {
                if (needFurtherChk) {
                    const topSide = currentPageMergePos[JV.PROP_TOP];
                    const 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]) {
                                const destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBand[JV.PROP_STYLE][JV.PROP_ID]];
                                rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
                                break;
                            }
                        } else {
                            break;
                        }
                    }
                } else {
                    const 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]) {
                const 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);
    };
    const private_checkBorder = function(cell, border, sheetBorder) {
        let rst = true;
        const borderLineWidths = [];
        const 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;
    };
    const private_getIniBorder = function() {
        const 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;
    };
    const private_getBorderId = function(cell) {
        let rst = 0;
        let hasBorder = false;
        if (!(stylesObj.borders)) {
            stylesObj.borders = [];
        }
        const sheetBorder = pageData[JV.NODE_STYLE_COLLECTION][cell.style];
        const 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++) {
            const 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;
    };
    const 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;
    };
    const private_getStyleId = function(cell) {
        let rst = 1;
        let hasStyle = false;
        if (!(stylesObj.cellXfs)) stylesObj.cellXfs = [];
        const 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);
        }

        const 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++) {
            const sheetControl = stylesObj.cellXfs[i];
            if (sheetControl.fontId === fontId && sheetControl.borderId === borderId) {
                if (private_checkControl(cellControl, sheetControl)) {
                    rst = i;
                    hasStyle = true;
                    break;
                }
            }
        }
        if (!hasStyle) {
            const 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;
    };
    const 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 = (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>');
    };
    const private_setMergedCells = function() {
        let cnt = 0;
        rst.push('<mergeCells count="?">');
        const startIdx = rst.length - 1;
        const self_setMergedCells = function(theData, theYPos, offsetY) {
            for (let i = 0; i < theData.cells.length; i++) {
                const cell = theData.cells[i];
                const idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
                const idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
                const idxB = theYPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
                const 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++) {
                const shtItemData = pageData.items[i];
                const tmpPos = yMultiPos[i];
                self_setMergedCells(shtItemData, tmpPos, osY);
                osY += tmpPos.length - 2;
            }
        }
        rst[startIdx] = '<mergeCells count="' + cnt + '">';
        rst.push('</mergeCells>');
    };
    const private_setSheetData = function() {
        // remark: 1 excel unit height = 0.3612 mm
        rst.push('<sheetData>');
        const spanX = xPos.length - 2;
        let cellIdx = 0;
        let h = 0;
        const self_setDataEx = function(theShtData, theYPos, rowOffset) {
            const rows = [];
            // 1. build full set of blank rows/cells
            for (let i = 1; i < theYPos.length - 1; i++) {
                const rowObj = {};
                h = (theYPos[i + 1] - theYPos[i]) / DPI * 25.4 / 0.3612;
                h = Math.round(h * 1000) / 1000;
                rowObj.height = h;
                rowObj.r = i + rowOffset;
                rowObj.items = [];
                rows.push(rowObj);
                for (let j = 1; j < xPos.length - 1; j++) {
                    const colIdxStr = private_getCellIdxStr(j - 1);
                    const cellObj = {};
                    cellObj.r = colIdxStr + (i + rowOffset);
                    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;
            let colIdx1 = 0;
            let rowIdx2 = 0;
            let colIdx2 = 0;
            // let colIdxStr = '';
            for (let cIdx = 0; cIdx < theShtData.cells.length; cIdx++) {
                const styleIdx = private_getStyleId(theShtData.cells[cIdx]);
                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]))) {
                    const 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 + rowOffset) + '" spans="1:' + spanX + '" ht="' + rows[i].height + '" customHeight="1">');
                for (let j = 0; j < rows[i].items.length; j++) {
                    const 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 rowOffset = 0;
            for (let i = 0; i < pageData.items.length; i++) {
                const shtItemData = pageData.items[i];
                currentPageMergePos = shtItemData[JV.PAGE_SPECIAL_MERGE_POS];
                currentMergeBorder = shtItemData[JV.PROP_PAGE_MERGE_BORDER];
                const tmpPos = yMultiPos[i];
                cellIdx = 0;
                self_setDataEx(shtItemData, tmpPos, rowOffset);
                rowOffset += tmpPos.length - 2;
            }
        }
        rst.push('</sheetData>');
    };

    preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos);
    // console.log('yMultiPos');
    // console.log(yMultiPos);
    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">');
    const 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"/>');
    const 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 (hasSignature) {
        rst.push('<drawing r:id="rId' + (sheetIdx + 1) + '"/>');
    }
    rst.push('</worksheet>');
    return rst;
}
function writeWorkSheetRels(sheetIdx) {
    const rst = [];
    rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
    rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
    rst.push('<Relationship Id="rId' + (sheetIdx + 1) + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing' + (sheetIdx + 1) + '.xml"/>');
    rst.push('</Relationships>');
    return rst;
}
function writeImage(path, pic, baseDir) {
    let rst = null;
    if (pic) {
        // 这里pic的数据就是base64的，不能是其他
        rst = base64ToStream(pic);
    } else if (path) {
        const filePath = baseDir + '/app' + path;
        rst = fs.readFileSync(filePath);
    }
    return rst;
}
function writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage) {
    const rst = [];
    console.log('isSinglePage: ' + isSinglePage);
    if (isSinglePage) {
        rst.push(writeDrawing(pageData, null, signKeyArr, signPathArr, 0));
    } else {
        for (let i = 0; i < pageData.items.length; i++) {
            rst.push(writeDrawing(pageData, pageData.items[i], signKeyArr, signPathArr, i));
        }
    }
    return rst;
}
function writeDrawing(pageData, sheetData, signKeyArr, signPathArr, sheetIdx) {
    const rst = [];
    const xPos = [];
    const yPos = [];
    const yMultiPos = [];

    const private_setSheetDrawingCellData = function(signCell, theYPos, startPicIdx, offsetRow) {
        let cellControl;
        if (typeof signCell[JV.PROP_CONTROL] === 'string') {
            cellControl = pageData[JV.NODE_CONTROL_COLLECTION][signCell[JV.PROP_CONTROL]];
        } else {
            cellControl = signCell[JV.PROP_CONTROL];
        }
        const area = getProperSignatureArea(signCell, cellControl);

        // 1. from
        // let colIdx = xPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_LEFT]);
        // let rowIdx = theYPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_TOP]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出)
        let colIdx = xPos.indexOf(area[0]);
        let rowIdx = theYPos.indexOf(area[1]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出)
        // 因为 xPos, theYPos的首位是0，所以还需要减1
        colIdx--;
        rowIdx--;
        rst.push('<xdr:twoCellAnchor editAs="oneCell">');
        rst.push('<xdr:from>');
        rst.push('<xdr:col>' + colIdx + '</xdr:col>');
        rst.push('<xdr:colOff>0</xdr:colOff>');
        rst.push('<xdr:row>' + rowIdx + '</xdr:row>');
        rst.push('<xdr:rowOff>0</xdr:rowOff>');
        rst.push('</xdr:from>');
        // 2. to
        colIdx = xPos.indexOf(area[2]);
        rowIdx = theYPos.indexOf(area[3]) + offsetRow;
        // 因为 xPos, theYPos的首位是0，所以还需要减1
        colIdx--;
        rowIdx--;
        rst.push('<xdr:to>');
        rst.push('<xdr:col>' + colIdx + '</xdr:col>');
        rst.push('<xdr:colOff>0</xdr:colOff>');
        rst.push('<xdr:row>' + rowIdx + '</xdr:row>');
        rst.push('<xdr:rowOff>0</xdr:rowOff>');
        rst.push('</xdr:to>');
        // 3. pic
        rst.push('<xdr:pic>');
        // 3.1
        rst.push('<xdr:nvPicPr>');
        rst.push('<xdr:cNvPr id="' + startPicIdx + '" name="图片 ' + startPicIdx + '"/>');
        rst.push('<xdr:cNvPicPr><a:picLocks noChangeAspect="1" noChangeArrowheads="1"/></xdr:cNvPicPr>');
        rst.push('</xdr:nvPicPr>');
        // 3.2
        rst.push('<xdr:blipFill>');
        // 重点！！！
        const picIdx = signKeyArr.indexOf(signCell.signature_name) + 1;
        // 3.2.1
        rst.push('<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId' + picIdx + '" cstate="print">');
        rst.push('<a:extLst>');
        rst.push('<a:ext uri="{28A0092B-C50C-407E-A947-70E740481C1C}">'); // 参考excel本身，都是固定的uri
        rst.push('<a14:useLocalDpi xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main" val="0"/>');
        rst.push('</a:ext>');
        rst.push('</a:extLst>');
        rst.push('</a:blip>');
        // 3.2.2
        rst.push('<a:srcRect/>');
        // 3.2.3
        rst.push('<a:stretch><a:fillRect/></a:stretch>');
        rst.push('</xdr:blipFill>');
        // 3.3
        rst.push('<xdr:spPr bwMode="auto">');
        // 3.3.1
        rst.push('<a:xfrm>');
        // 以下经过Excel及WPS的测试，这数据好像没什么意义，都设为0
        rst.push('<a:off x="0" y="0"/>');
        rst.push('<a:ext cx="0" cy="0"/>');
        rst.push('</a:xfrm>');
        // 3.3.2
        rst.push('<a:prstGeom prst="rect"><a:avLst/></a:prstGeom>')
        // 3.3.3
        rst.push('<a:noFill/>');
        // 3.3.4
        rst.push('<a:extLst>');
        rst.push('<a:ext uri="{909E8E84-426E-40DD-AFC4-6F175D3DCCD1}">');
        rst.push('<a14:hiddenFill xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main">');
        rst.push('<a:solidFill><a:srgbClr val="FFFFFF"/></a:solidFill>');
        rst.push('</a14:hiddenFill>');
        rst.push('</a:ext>');
        rst.push('</a:extLst>');
        // 收尾
        rst.push('</xdr:spPr>');
        rst.push('</xdr:pic>');
        // 4. client Data
        rst.push('<xdr:clientData/>');
        rst.push('</xdr:twoCellAnchor>');
    }
    preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos);
    rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
    rst.push('<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">');
    if (sheetData) {
        let startPicIdx = 2;
        for (const sCell of sheetData[JV.PROP_SIGNATURE_CELLS]) {
            if (signKeyArr.indexOf(sCell.signature_name) >= 0) {
                private_setSheetDrawingCellData(sCell, yPos, startPicIdx, 0);
                startPicIdx++;
            }
        }
    } else {
        // total data in one sheet
        let rowOffset = 0;
        for (let i = 0; i < pageData.items.length; i++) {
            const shtItemData = pageData.items[i];
            const tmpPos = yMultiPos[i];
            let startPicIdx = 2;
            for (const sCell of shtItemData[JV.PROP_SIGNATURE_CELLS]) {
                if (signKeyArr.indexOf(sCell.signature_name) >= 0) {
                    private_setSheetDrawingCellData(sCell, tmpPos, startPicIdx, rowOffset);
                    startPicIdx++;
                }
            }
            rowOffset += tmpPos.length - 2;
        }
    }
    rst.push('</xdr:wsDr>');
    return rst;
}
function writeDrawingsRels(amt) {
    const rst = [];
    rst.push('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
    rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
    for (let idx = 0; idx < amt; idx++) {
        rst.push('<Relationship Id="rId' + (idx + 1) + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image' + (idx + 1) + '.png"/>');
    }
    rst.push('</Relationships>');
    return rst;
}

function mergeProperties(orgObj, newObj) {
    const orgPropArr = [];
    const newPropArr = [];
    for (const p in orgObj) {
        orgPropArr.push(p);
    }
    for (const 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]];
        }
    }
}
function checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, roleRel) {
    // 备注：电子签名是以图形的方式处理，一页可以有多个签名，多页的签名基本是引用同样的图片，在这里先处理一下，后期统一引用。
    //      另：以后的图片(在电子签名（signature_cells）以外的图片)会单独处理(如计算图等)
    let rst = false;
    let chkRoles = [];
    if (roleRel instanceof Array) {
        chkRoles = roleRel;
    } else {
        chkRoles.push(roleRel);
    }
    // console.log(chkRoles);
    for (const page of pageData.items) {
        if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
            // console.log('has SignatureCells!');
            for (const signature of page[JV.PROP_SIGNATURE_CELLS]) {
                if (signKeyArr.indexOf(signature.signature_name) < 0) {
                    if (signature.pic) {
                        const signPath = { path: null, pic: null };
                        signPathArr.push(signPath);
                        signPath.pic = signature.pic; // 历史报表
                        signKeyArr.push(signature.signature_name);
                        rst = true;
                    } else {
                        for (const role of chkRoles) {
                            if (signature.signature_name === role.signature_name) {
                                // console.log('signature.signature_name: ' + signature.signature_name);
                                if (role.sign_pic) {
                                    const signPath = { path: null, pic: null };
                                    signPathArr.push(signPath);
                                    signPath.pic = role.sign_pic;
                                    signKeyArr.push(signature.signature_name);
                                    rst = true;
                                } else if (role.sign_path) {
                                    const signPath = { path: null, pic: null };
                                    signPathArr.push(signPath);
                                    signPath.path = role.sign_path;
                                    signKeyArr.push(signature.signature_name);
                                    rst = true;
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
    }
    return rst;
}
function base64ToStream(dataurl) {
    // 将base64转换为流
    const arr = dataurl.split(',');
    const bstr = atob(arr[1]);
    let n = bstr.length;
    const u8arr = new Uint8Array(n);
    while (n--) {
        u8arr[n] = bstr.charCodeAt(n);
    }
    return u8arr;
}


module.exports = {
    exportExcel: function(pageData, paperSize, fName, options, custSheetNames, custSheetMergeBands, baseDir, roleRel, callback) {
        const rptOptions = ({ singlePage: false, fileName: 'report' });
        if (options === 'true' || options === true) {
            rptOptions.singlePage = true;
        }
        const isSinglePage = rptOptions.singlePage;
        const sheets = [];
        const signKeyArr = [];
        const signPathArr = [];
        // console.log('in exportExcel!');
        // console.log(roleRel);
        const hasSignature = (roleRel !== null) ? checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, roleRel) : false;
        // const hasSignature = false;
        console.log('hasSignature: ' + hasSignature);
        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, hasSignature);
        const zip = new JSZip();
        zip.file(file, data.join(''), { compression: 'DEFLATE' });
        // 2.
        const zip_rels = zip.folder('_rels');
        file = '.rels';
        data = writeRootRels();
        zip_rels.file(file, data.join(''), { compression: 'DEFLATE' });
        // 3.
        const 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.
        const zip_xl = zip.folder('xl');
        file = 'workbook.xml';
        data = writeXlWorkBook(sheets, isSinglePage);
        zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });
        const zip_rels2 = zip_xl.folder('_rels');
        file = 'workbook.xml.rels';
        data = writeXlRels(sheets, isSinglePage);
        zip_rels2.file(file, data.join(''), { compression: 'DEFLATE' });
        // 5.
        const zip_theme = zip_xl.folder('theme');
        file = 'theme1.xml';
        data = writeTheme();
        zip_theme.file(file, data, { compression: 'DEFLATE' });
        if (hasSignature) {
            // 5.1
            const zip_media = zip_xl.folder('media');
            for (let signIdx = 0; signIdx < signPathArr.length; signIdx++) {
                data = writeImage(signPathArr[signIdx].path, signPathArr[signIdx].pic, baseDir);
                file = 'image' + (signIdx + 1) + '.png';
                zip_media.file(file, data, { compression: 'DEFLATE' });
            }
            // 5.2
            const zip_drawings = zip_xl.folder('drawings');
            data = writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage);
            // console.log('isSinglePage: ' + isSinglePage);
            // console.log(data);
            for (let psIdx = 0; psIdx < data.length; psIdx++) {
                file = 'drawing' + (psIdx + 1) + '.xml';
                // console.log('drawing1' + (psIdx + 1) + '.xml data');
                // console.log(data);
                zip_drawings.file(file, data[psIdx].join(''), { compression: 'DEFLATE' });
            }
            // 5.3
            const zip_drawings_rels = zip_drawings.folder('_rels');
            data = writeDrawingsRels(signPathArr.length); // 这个一个文件搞定算了，无需多个文件
            // console.log('drawing1.xml.rels data');
            // console.log(data);
            file = 'drawing1.xml.rels';
            zip_drawings_rels.file(file, data.join(''), { compression: 'DEFLATE' });
        }
        // 6.
        const zip_xl_worksheets = zip_xl.folder('worksheets');
        const sharedStrList = [];
        const stylesObj = {};
        data = writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands, hasSignature);
        if (isSinglePage) {
            for (let i = 0; i < 1; i++) {
                file = 'sheet' + (i + 1) + '.xml';
                zip_xl_worksheets.file(file, data[i].join(''), { compression: 'DEFLATE' });
            }
        } else {
            for (let i = 0; i < data.length; i++) {
                file = 'sheet' + (i + 1) + '.xml';
                zip_xl_worksheets.file(file, data[i].join(''), { compression: 'DEFLATE' });
            }
        }
        // 7.
        if (hasSignature) {
            const zip_xl_worksheets_rels = zip_xl_worksheets.folder('_rels');
            if (isSinglePage) {
                data = writeWorkSheetRels(0);
                file = 'sheet1.xml.rels';
                zip_xl_worksheets_rels.file(file, data.join(''), { compression: 'DEFLATE' });
            } else {
                for (let i = 0; i < data.length; i++) {
                    data = writeWorkSheetRels(i);
                    file = 'sheet' + (i + 1) + '.xml.rels';
                    zip_xl_worksheets_rels.file(file, data.join(''), { compression: 'DEFLATE' });
                }
            }
        }
        file = 'sharedStrings.xml';
        data = writeSharedString(sharedStrList);
        zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });
        file = 'styles.xml';
        data = writeStyles(stylesObj, hasSignature);
        zip_xl.file(file, data.join(''), { compression: 'DEFLATE' });

        if (fName) {
            // let newName = '' + (new Date()).valueOf();
            const 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, baseDir, roleRelArr, callback) {
        const me = this;
        const newPageData = {};
        // 1. 重新编排一下数据，把一份报表的pageData合并到一起作为一个Sheet输出（需要重新调整数据纵向坐标），多份报表数据就形成多个Sheet
        //    -- 简单来说，就是重新包装数据
        try {
            // 1.1 newPageData外围属性
            const newContrl = {};
            const newFont = {};
            const newStyle = {};
            const 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坐标
            const sheetNames = [];
            const newPagePos = [];
            for (let i = 0; i < pageDataArray.length; i++) {
                let offsetY = 0;
                const 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;
                    let 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;
                    }
                    const bottomGap = Math.round( (pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_PAGE_SIZE][1] - parseFloat(pageDataArray[i][JV.NODE_PAGE_INFO][JV.NODE_MARGINS][JV.PROP_BOTTOM]) / 2.54 ) * DPI) - maxY;
                    offsetY += (maxY - minY);
                    if (bottomGap > 10) {
                        offsetY += (bottomGap - 10);
                    }
                }
            }
            // 2. newPageData的items属性
            newPageData.items = [];
            for (let i = 0; i < pageDataArray.length; i++) {
                const 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
            // let roleRel = null; // 未来调用的时候，这个属性要从外面给！！！
            // roleRelArr
            me.exportExcel(newPageData, paperSize, fName, 'false', sheetNames, custMergeBands, baseDir, null, callback);
            // fsUtil.writeObjToFile(newPageData, 'D:/GitHome/ConstructionOperation/tmp/combinedHeader.js');
        } catch (e) {
            console.log(e);
        }
    },
};