Calculation/app/public/report/js/rpt_jsexcel.js

/**
 * Created by Tony on 2022/2/21.
 * 导出excel从后端移到前端处理
 */

// const JV = require('../rpt_component/jpc_value_define'); // JV对象默认已有
// const JSZip = require('jszip'); // 已引用
// const strUtil = require('../public/stringUtil'); // stringUtil对象也有
// STAGE_AUDIT, STAGE_LIST 已存在
const DPI = 96;
const dftHeadXml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
const STD_COMP_STAMP_SIZE_WIDTH = Math.round(5 * 96 / 2.54); // 公章标准尺寸(宽4.2厘米)转成像素
const STD_COMP_STAMP_SIZE_HEIGHT = STD_COMP_STAMP_SIZE_WIDTH; // 公章标准尺寸(高4.2厘米)转成像素


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');
    const rst = [`<?xml version="1.0"encoding="UTF-8"standalone="yes"?><a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"name="Office 主题"><a:themeElements><a:clrScheme name="Office"><a:dk1><a:sysClr val="windowText"lastClr="000000"/></a:dk1><a:lt1><a:sysClr val="window"lastClr="FFFFFF"/></a:lt1><a:dk2><a:srgbClr val="1F497D"/></a:dk2><a:lt2><a:srgbClr val="EEECE1"/></a:lt2><a:accent1><a:srgbClr val="4F81BD"/></a:accent1><a:accent2><a:srgbClr val="C0504D"/></a:accent2><a:accent3><a:srgbClr val="9BBB59"/></a:accent3><a:accent4><a:srgbClr val="8064A2"/></a:accent4><a:accent5><a:srgbClr val="4BACC6"/></a:accent5><a:accent6><a:srgbClr val="F79646"/></a:accent6><a:hlink><a:srgbClr val="0000FF"/></a:hlink><a:folHlink><a:srgbClr val="800080"/></a:folHlink></a:clrScheme><a:fontScheme name="Office"><a:majorFont><a:latin typeface="Cambria"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan"typeface="ＭＳ Ｐゴシック"/><a:font script="Hang"typeface="맑은 고딕"/><a:font script="Hans"typeface="宋体"/><a:font script="Hant"typeface="新細明體"/><a:font script="Arab"typeface="Times New Roman"/><a:font script="Hebr"typeface="Times New Roman"/><a:font script="Thai"typeface="Tahoma"/><a:font script="Ethi"typeface="Nyala"/><a:font script="Beng"typeface="Vrinda"/><a:font script="Gujr"typeface="Shruti"/><a:font script="Khmr"typeface="MoolBoran"/><a:font script="Knda"typeface="Tunga"/><a:font script="Guru"typeface="Raavi"/><a:font script="Cans"typeface="Euphemia"/><a:font script="Cher"typeface="Plantagenet Cherokee"/><a:font script="Yiii"typeface="Microsoft Yi Baiti"/><a:font script="Tibt"typeface="Microsoft Himalaya"/><a:font script="Thaa"typeface="MV Boli"/><a:font script="Deva"typeface="Mangal"/><a:font script="Telu"typeface="Gautami"/><a:font script="Taml"typeface="Latha"/><a:font script="Syrc"typeface="Estrangelo Edessa"/><a:font script="Orya"typeface="Kalinga"/><a:font script="Mlym"typeface="Kartika"/><a:font script="Laoo"typeface="DokChampa"/><a:font script="Sinh"typeface="Iskoola Pota"/><a:font script="Mong"typeface="Mongolian Baiti"/><a:font script="Viet"typeface="Times New Roman"/><a:font script="Uigh"typeface="Microsoft Uighur"/></a:majorFont><a:minorFont><a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/><a:font script="Jpan"typeface="ＭＳ Ｐゴシック"/><a:font script="Hang"typeface="맑은 고딕"/><a:font script="Hans"typeface="宋体"/><a:font script="Hant"typeface="新細明體"/><a:font script="Arab"typeface="Arial"/><a:font script="Hebr"typeface="Arial"/><a:font script="Thai"typeface="Tahoma"/><a:font script="Ethi"typeface="Nyala"/><a:font script="Beng"typeface="Vrinda"/><a:font script="Gujr"typeface="Shruti"/><a:font script="Khmr"typeface="DaunPenh"/><a:font script="Knda"typeface="Tunga"/><a:font script="Guru"typeface="Raavi"/><a:font script="Cans"typeface="Euphemia"/><a:font script="Cher"typeface="Plantagenet Cherokee"/><a:font script="Yiii"typeface="Microsoft Yi Baiti"/><a:font script="Tibt"typeface="Microsoft Himalaya"/><a:font script="Thaa"typeface="MV Boli"/><a:font script="Deva"typeface="Mangal"/><a:font script="Telu"typeface="Gautami"/><a:font script="Taml"typeface="Latha"/><a:font script="Syrc"typeface="Estrangelo Edessa"/><a:font script="Orya"typeface="Kalinga"/><a:font script="Mlym"typeface="Kartika"/><a:font script="Laoo"typeface="DokChampa"/><a:font script="Sinh"typeface="Iskoola Pota"/><a:font script="Mong"typeface="Mongolian Baiti"/><a:font script="Viet"typeface="Arial"/><a:font script="Uigh"typeface="Microsoft Uighur"/></a:minorFont></a:fontScheme><a:fmtScheme name="Office"><a:fillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="50000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="35000"><a:schemeClr val="phClr"><a:tint val="37000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="15000"/><a:satMod val="350000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000"scaled="1"/></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:shade val="51000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="80000"><a:schemeClr val="phClr"><a:shade val="93000"/><a:satMod val="130000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="94000"/><a:satMod val="135000"/></a:schemeClr></a:gs></a:gsLst><a:lin ang="16200000"scaled="0"/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w="9525"cap="flat"cmpd="sng"algn="ctr"><a:solidFill><a:schemeClr val="phClr"><a:shade val="95000"/><a:satMod val="105000"/></a:schemeClr></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="25400"cap="flat"cmpd="sng"algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln><a:ln w="38100"cap="flat"cmpd="sng"algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000"dist="20000"dir="5400000"rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="38000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000"dist="23000"dir="5400000"rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad="40000"dist="23000"dir="5400000"rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst><a:scene3d><a:camera prst="orthographicFront"><a:rot lat="0"lon="0"rev="0"/></a:camera><a:lightRig rig="threePt"dir="t"><a:rot lat="0"lon="0"rev="1200000"/></a:lightRig></a:scene3d><a:sp3d><a:bevelT w="63500"h="25400"/></a:sp3d></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="40000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="40000"><a:schemeClr val="phClr"><a:tint val="45000"/><a:shade val="99000"/><a:satMod val="350000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="20000"/><a:satMod val="255000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000"t="-80000"r="50000"b="180000"/></a:path></a:gradFill><a:gradFill rotWithShape="1"><a:gsLst><a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="80000"/><a:satMod val="300000"/></a:schemeClr></a:gs><a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="30000"/><a:satMod val="200000"/></a:schemeClr></a:gs></a:gsLst><a:path path="circle"><a:fillToRect l="50000"t="50000"r="50000"b="50000"/></a:path></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/></a:theme>`];
    // 前端没有这个文件，需要想想怎么给(说白了，也就是一个一个xml文件而已)
    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 (stringUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]])) {
            rst.push('<b/>');
        }
        if (stringUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]])) {
            rst.push('<i/>');
        }
        if (stringUtil.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]] && !stringUtil.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 (stringUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[0]])) {
            alignStr += ' shrinkToFit="1"';
        }
        if (stringUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[4]]) && !stringUtil.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 _setupPgBrks(pageData) {
    pageData.pageBreaks = [];
    let pgBrkIdx = 0;
    const pgBrk = [];
    function _insertMaxBottom(pCells, targetBottomArr, chkTop) {
        if (pCells) {
            for (const cell of pCells) {
                let cb = parseFloat(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
                if (targetBottomArr.indexOf(cb) < 0) {
                    targetBottomArr.push(cb);
                }
                if (chkTop || pCells.isStamp) {
                    cb = parseFloat(cell[JV.PROP_AREA][JV.PROP_TOP]);
                    if (targetBottomArr.indexOf(cb) < 0) {
                        targetBottomArr.push(cb);
                    }
                }
            }
        }
    }
    // for (const page of pageData.items) {
    // for (let i = 0; i < pageData.items.length - 1; i++) {
    for (let i = 0; i < pageData.items.length; i++) {
        const page = pageData.items[i];
        const maxBottomArr = [];
        _insertMaxBottom(page[JV.PROP_CELLS], maxBottomArr);
        _insertMaxBottom(page[JV.PROP_INTERACT_CELLS], maxBottomArr);
        _insertMaxBottom(page[JV.PROP_SIGNATURE_CELLS], maxBottomArr, true);
        _insertMaxBottom(page[JV.PROP_SIGNATURE_DATE_CELLS], maxBottomArr);
        _insertMaxBottom(page[JV.PROP_SIGNATURE_AUDIT_CELLS], maxBottomArr);
        _insertMaxBottom(page[JV.PROP_WATERMARK_CELLS], maxBottomArr, true);
        pgBrkIdx += maxBottomArr.length;
        pgBrk.push(pgBrkIdx);
    }
    pageData.pageBreaks.push(pgBrk);
}

function writeSheets(pageData, paperSize, sharedStrList, stylesObj, isSinglePage, custSheetMergeBands, hasSignature, signSheetIdxArr) {
    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, signSheetIdxArr, 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, hasSignature, sheetIdx, signSheetIdxArr, i === 0, i));
            sheetIdx++;
        }
    }
    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_INTERACT_CELLS].length; i++) {
            cell = theShtData[JV.PROP_INTERACT_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, 0, 0, JV);
            if (cell.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
                area[JV.IDX_LEFT] = cell[JV.PROP_AREA][JV.PROP_LEFT];
                area[JV.IDX_TOP] = cell[JV.PROP_AREA][JV.PROP_TOP];
                area[JV.IDX_RIGHT] = cell[JV.PROP_AREA][JV.PROP_RIGHT];
                area[JV.IDX_BOTTOM] = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
            }
            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);
        }
        for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS].length; i++) {
            cell = theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS][i];
            private_set_cell_pos(cell, theXPos, theYPos);
        }
        for (let i = 0; i < theShtData[JV.PROP_WATERMARK_CELLS].length; i++) {
            cell = theShtData[JV.PROP_WATERMARK_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, 0, 0, JV);
            if (cell.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
                area[JV.IDX_LEFT] = cell[JV.PROP_AREA][JV.PROP_LEFT];
                area[JV.IDX_TOP] = cell[JV.PROP_AREA][JV.PROP_TOP];
                area[JV.IDX_RIGHT] = cell[JV.PROP_AREA][JV.PROP_RIGHT];
                area[JV.IDX_BOTTOM] = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
            }
            private_set_cell_pos2(area, 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, signSheetIdxArr, isFirstSheet, pgBrkIdx) {
    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 (mergeBorder && mergeBorder[borderStr] && 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(cells, theYPos, offsetY) {
            for (let i = 0; i < cells.length; i++) {
                const cell = cells[i];
                if (!cell.isStamp) {
                    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);
            self_setMergedCells(sheetData[JV.PROP_CELLS], yPos, 0);
            self_setMergedCells(sheetData[JV.PROP_INTERACT_CELLS], yPos, 0);
            if (sheetData[JV.PROP_SIGNATURE_DATE_CELLS]) self_setMergedCells(sheetData[JV.PROP_SIGNATURE_DATE_CELLS], yPos, 0);
            if (sheetData[JV.PROP_SIGNATURE_AUDIT_CELLS]) self_setMergedCells(sheetData[JV.PROP_SIGNATURE_AUDIT_CELLS], 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);
                self_setMergedCells(shtItemData[JV.PROP_CELLS], tmpPos, osY);
                self_setMergedCells(shtItemData[JV.PROP_INTERACT_CELLS], tmpPos, osY);
                if (shtItemData[JV.PROP_SIGNATURE_DATE_CELLS]) self_setMergedCells(shtItemData[JV.PROP_SIGNATURE_DATE_CELLS], tmpPos, osY);
                if (shtItemData[JV.PROP_SIGNATURE_AUDIT_CELLS]) self_setMergedCells(shtItemData[JV.PROP_SIGNATURE_AUDIT_CELLS], 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_create_cell = function(cells, rows, theYPos) {
            let rowIdx1 = 0;
            let colIdx1 = 0;
            let rowIdx2 = 0;
            let colIdx2 = 0;
            for (let cIdx = 0; cIdx < cells.length; cIdx++) {
                const styleIdx = private_getStyleId(cells[cIdx]);
                rowIdx1 = theYPos.indexOf(cells[cIdx][JV.PROP_AREA][JV.PROP_TOP]);
                colIdx1 = xPos.indexOf(cells[cIdx][JV.PROP_AREA][JV.PROP_LEFT]);
                if (rowIdx1 <= rows.length) {
                    let cellObj = rows[rowIdx1 - 1].items[colIdx1 - 1];
                    cellObj.s = styleIdx;
                    cellObj.isBlank = false;
                    if (!(stringUtil.isEmptyString(cells[cIdx][JV.PROP_VALUE]))) {
                        const valIdx = private_getSharedStrIdx(cells[cIdx][JV.PROP_VALUE]);
                        cellObj.v = valIdx;
                    }

                    rowIdx2 = theYPos.indexOf(cells[cIdx][JV.PROP_AREA][JV.PROP_BOTTOM]);
                    colIdx2 = xPos.indexOf(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;
                            }
                        }
                    }
                }
            }
        };
        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
            self_create_cell(theShtData[JV.PROP_CELLS], rows, theYPos);
            self_create_cell(theShtData[JV.PROP_INTERACT_CELLS], rows, theYPos);
            self_create_cell(theShtData[JV.PROP_SIGNATURE_DATE_CELLS], rows, theYPos);
            self_create_cell(theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS], rows, theYPos);
            // fsUtil.writeObjToFile(newPageData, 'D:/GitHome/ConstructionOperation/tmp/combinedHeader.js');
            // console.log('theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS]: ');
            // console.log(theShtData[JV.PROP_SIGNATURE_AUDIT_CELLS]);

            // 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];
            if (hasSignature && sheetData[JV.PROP_SIGNATURE_CELLS] && sheetData[JV.PROP_SIGNATURE_CELLS].length > 0) {
                // 有签名情况下，还是有必要创建一个dummy cell的(画框用)
                _createDummyCell(sheetData[JV.PROP_SIGNATURE_CELLS], sheetData);
            }

            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];
                // console.log(yMultiPos[i]);
                cellIdx = 0;
                if (hasSignature && shtItemData[JV.PROP_SIGNATURE_CELLS] && shtItemData[JV.PROP_SIGNATURE_CELLS].length > 0) {
                    // 有签名情况下，还是有必要创建一个dummy cell的(画框用)
                    _createDummyCell(shtItemData[JV.PROP_SIGNATURE_CELLS], shtItemData);
                }
                self_setDataEx(shtItemData, tmpPos, rowOffset);
                rowOffset += tmpPos.length - 2;
            }
        }
        rst.push('</sheetData>');
    };

    preAnalyzePos(pageData, sheetData, xPos, yPos, 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 + '"/>');
    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"/>');
    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 (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>');
        }
    }
    // 插入分页符结束---------------------------
    // 图形要放在最后，否则excel会报错(但WPS不会)
    if (hasSignature && (sheetData === null || signSheetIdxArr[sheetIdx])) {
        rst.push('<drawing r:id="rId1"/>');
    }
    rst.push('</worksheet>');
    return rst;
}
function writeWorkSheetRels(signSheetIdx) {
    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="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing' + signSheetIdx + '.xml"/>');
    rst.push('</Relationships>');
    return rst;
}

async function writeImage(path, pic) {
    let rst = null;
    if (pic) {
        rst = convertPicBase64ToBlob(pic);
    } else if (path) {
        // 前端方式处理不同，不用考虑哪里（水印也是），直接都一股脑地申请下载即可(重复的浏览器会找cache!)
        let picpath = path; // 前端的path已经是完整的了
        rst = await getBlob(picpath); //这里通过getBlob函数得到的结果已经是所需要的流数据，无需转换
    }
    return rst;
}
function writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage, signSheetIdxArr) {
    const rst = [];
    // 优化调整，不再每页都设置一个，重复的签名只需要一个即可(极端case: 13xx多页的签名文档，导出有1.3G大)
    if (isSinglePage) {
        rst.push(writeDrawing(pageData, null, signKeyArr, 0));
    } else {
        for (let i = 0; i < pageData.items.length; i++) {
            if (signSheetIdxArr[i]) {
                rst.push(writeDrawing(pageData, pageData.items[i], signKeyArr, i));
            }
        }
    }
    // */
    return rst;
}
function writeDrawing(pageData, sheetData, subSignKeyArr, 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, 0, 0, JV);
        if (signCell.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
            area[JV.IDX_LEFT] = signCell[JV.PROP_AREA][JV.PROP_LEFT];
            area[JV.IDX_TOP] = signCell[JV.PROP_AREA][JV.PROP_TOP];
            area[JV.IDX_RIGHT] = signCell[JV.PROP_AREA][JV.PROP_RIGHT];
            area[JV.IDX_BOTTOM] = signCell[JV.PROP_AREA][JV.PROP_BOTTOM];
        }

        // 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 = subSignKeyArr.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">');
    // console.log('subSignKeyArr');
    // console.log(subSignKeyArr);
    if (sheetData) {
        // console.log('sheetData');
        let startPicIdx = 2 + sheetIdx * 1000;
        for (const sCell of sheetData[JV.PROP_SIGNATURE_CELLS]) {
            if (subSignKeyArr.indexOf(sCell.signature_name) >= 0) {
                private_setSheetDrawingCellData(sCell, yPos, startPicIdx, 0);
                startPicIdx++;
            }
        }
        for (const sCell of sheetData[JV.PROP_WATERMARK_CELLS]) {
            if (subSignKeyArr.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 (subSignKeyArr.indexOf(sCell.signature_name) >= 0) {
                    private_setSheetDrawingCellData(sCell, tmpPos, startPicIdx, rowOffset);
                    startPicIdx++;
                }
            }
            for (const sCell of shtItemData[JV.PROP_WATERMARK_CELLS]) {
                if (subSignKeyArr.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, startIdx) {
    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' + (startIdx + 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]];
        }
    }
}

async function resetDummySignature(pageData, roleRel) {
    // 备注：计算草图等其他图形需要额外做些处理
    let dummySignIdx = 0;
    const stampPicKeys = [], stampPicFeatures = [];
    const _resetStampArea = function(stampCell, roleRelItem) {
        const ctrl = pageData[JV.NODE_CONTROL_COLLECTION][stampCell[JV.PROP_CONTROL]];
        const rst = { Left: stampCell.area.Left, Right: stampCell.area.Right, Top: stampCell.area.Top, Bottom: stampCell.area.Bottom };
        let pLeft = stampCell.orgArea.Left,
            pTop = stampCell.orgArea.Top;
        let std_stamp_size_width = STD_COMP_STAMP_SIZE_WIDTH, std_stamp_size_height = STD_COMP_STAMP_SIZE_HEIGHT;
        if (roleRelItem.stampFeature) {
            std_stamp_size_width = parseFloat(roleRelItem.stampFeature.ImageWidth.value);
            std_stamp_size_height = parseFloat(roleRelItem.stampFeature.ImageHeight.value);
        }
        switch (ctrl[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_HORIZON]]) {
            case JV.OUTPUT_ALIGN.H[JV.H_ALIGN_IDX_LEFT]:
                pLeft = stampCell.orgArea.Left;
                break;
            case JV.OUTPUT_ALIGN.H[JV.H_ALIGN_IDX_CENTER]:
                pLeft = (stampCell.orgArea.Left + stampCell.orgArea.Right - std_stamp_size_width) / 2;
                break;
            case JV.OUTPUT_ALIGN.H[JV.H_ALIGN_IDX_RIGHT]:
                pLeft = stampCell.orgArea.Right - std_stamp_size_width;
                break;
            default:break;
        }
        switch (ctrl[JV.CONTROL_PROPS[JV.CONTROL_PROP_IDX_VERTICAL]]) {
            case JV.OUTPUT_ALIGN.H[JV.V_ALIGN_IDX_TOP]:
                pTop = stampCell.orgArea.Top;
                break;
            case JV.OUTPUT_ALIGN.H[JV.V_ALIGN_IDX_CENTER]:
                pTop = (stampCell.orgArea.Top + stampCell.orgArea.Bottom - std_stamp_size_height) / 2;
                break;
            case JV.OUTPUT_ALIGN.H[JV.V_ALIGN_IDX_BOTTOM]:
                pTop = stampCell.orgArea.Bottom - std_stamp_size_height;
                break;
            default:break;
        }
        stampCell.area.Left = pLeft;
        stampCell.area.Top = pTop;
        stampCell.area.Right = pLeft + std_stamp_size_width;
        stampCell.area.Bottom = pTop + std_stamp_size_height;
        // 最后一步，如超过报表范围，则要调整坐标
        // const maxRect = stampCell.maxRect;
        // if (rst.Left < maxRect[0]) {
        //     const width = maxRect[0] - rst.Left;
        //     rst.Left += width;
        //     rst.Right += width;
        // }
        // if (rst.Top < maxRect[1]) {
        //     const height = maxRect[1] - rst.Top;
        //     rst.Top += height;
        //     rst.Bottom += height;
        // }
        // if (rst.Right > maxRect[2]) {
        //     const width = maxRect[2] - rst.Right; // 负
        //     rst.Left += width;
        //     rst.Right += width;
        // }
        // if (rst.Bottom > maxRect[3]) {
        //     const height = maxRect[3] - rst.Bottom;
        //     rst.Top += height;
        //     rst.Bottom += height;
        // }
        return rst;
    };
    for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
        const page = pageData.items[pageIdx];
        if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
            for (const signatureCell of page[JV.PROP_SIGNATURE_CELLS]) {
                if (signatureCell.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0) {
                    // 表示这是一个其他类型的非原生电子签名图，只是借用signature的处理机制，每个图都是唯一的，所以需要重新给个唯一的新signature_name
                    // signatureCell.signature_name = signatureCell.signature_name + '_' + pageIdx + '_' + dummySignIdx;
                    signatureCell.signature_name = signatureCell.signature_name + '_' + page.page_seq + '_' + dummySignIdx; // page_seq在分页后都不会变动
                    dummySignIdx++;
                    const roleRelItem = { type: '用户', sign_path: signatureCell.path, signature_name: signatureCell.signature_name };
                    if (signatureCell.isStamp) {
                        let stmpIdx = stampPicKeys.indexOf(signatureCell.path);
                        if (stampPicKeys.indexOf(signatureCell.path) < 0) {
                            stampPicKeys.push(signatureCell.path);
                            stmpIdx = stampPicKeys.length - 1;
                            const picRes = await getHttpBlobText(signatureCell.path + '?x-oss-process=image/info');
                            stampPicFeatures.push(picRes);
                        }
                        // { "FileSize": {"value": "2514"}, "Format": {"value": "png"}, "ImageHeight": {"value": "94"}, "ImageWidth": {"value": "94"} }
                        roleRelItem.stampFeature = stampPicFeatures[stmpIdx];
                        // 重新处理cell坐标
                        _resetStampArea(signatureCell, roleRelItem);
                    }
                    roleRel.push(roleRelItem);
                }
            }
        }
    }
}

function _createDummyCell(dummyOrgCells, parentPage) {
    for (const signature of dummyOrgCells) {
        if (!signature.isStamp) {
            const dummyCell = {
                font: 'Content',
                control: 'Default',
                style: 'Default_None',
                Value: '',
                area: { Left: 0, Right: 0, Top: 0, Bottom: 0 },
            };
            if (signature.style) dummyCell.style = signature.style;
            dummyCell[JV.PROP_AREA][JV.PROP_LEFT] = signature[JV.PROP_AREA][JV.PROP_LEFT];
            dummyCell[JV.PROP_AREA][JV.PROP_RIGHT] = signature[JV.PROP_AREA][JV.PROP_RIGHT];
            dummyCell[JV.PROP_AREA][JV.PROP_TOP] = signature[JV.PROP_AREA][JV.PROP_TOP];
            dummyCell[JV.PROP_AREA][JV.PROP_BOTTOM] = signature[JV.PROP_AREA][JV.PROP_BOTTOM];
            parentPage[JV.PROP_CELLS].push(dummyCell);
        }
    }
}

function createDummySignatureCell(pageData) {
    for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
        const page = pageData.items[pageIdx];
        if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
            _createDummyCell(page[JV.PROP_SIGNATURE_CELLS], page);
        }
        if (page[JV.PROP_SIGNATURE_DATE_CELLS] && page[JV.PROP_SIGNATURE_DATE_CELLS].length > 0) {
            _createDummyCell(page[JV.PROP_SIGNATURE_DATE_CELLS], page);
        }
        if (page[JV.PROP_SIGNATURE_AUDIT_CELLS] && page[JV.PROP_SIGNATURE_AUDIT_CELLS].length > 0) {
            _createDummyCell(page[JV.PROP_SIGNATURE_AUDIT_CELLS], page);
        }
    }
}

function _checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, roleRel, signSheetIdxArr, isSinglePage, checkAudit = false) {
    // 备注：电子签名是以图形的方式处理，一页可以有多个签名，多页的签名基本是引用同样的图片，在这里先处理一下，后期统一引用。
    //      另：以后的图片(在电子签名（signature_cells）以外的图片)会单独处理(如计算草图、水印等)
    let rst = false;
    let chkRoles = [];
    if (roleRel instanceof Array) {
        chkRoles = roleRel;
    } else {
        if (roleRel) chkRoles.push(roleRel);
    }
    const _getDupPicPath = function(path) {
        let rst = null;
        for (const sp of signPathArr) {
            if (sp.path === path) {
                rst = sp;
                break;
            }
        }
        return rst;
    };
    for (let pageIdx = 0; pageIdx < pageData.items.length; pageIdx++) {
        const page = pageData.items[pageIdx];
        // 优化调整，不再每页都设置一个，重复的签名只需要一个即可(极端case: 13xx多页的签名文档，导出有1.3G大)
        const curSignIdx = signKeyArr.length - 1;
        signSheetIdxArr[pageIdx] = false;
        if (page[JV.PROP_SIGNATURE_CELLS] && page[JV.PROP_SIGNATURE_CELLS].length > 0) {
            for (const signatureCell of page[JV.PROP_SIGNATURE_CELLS]) {
                // 这里把图片的路径作为key值判断
                if (signKeyArr.indexOf(signatureCell.signature_name) < 0) {
                    // 还得判断用户是否有选择签名输出
                    for (const role of chkRoles) {
                        if (signatureCell.signature_name === role.signature_name) {
                            if (!(role.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) < 0 && role.sign_output.indexOf('normal_sign') < 0)) {
                                if (!checkAudit || role.signature_name.indexOf(JV.SIGNATURE_NAME_DUMMY) >= 0 || _chkIfAudit(role)) {
                                    // 如果签名但用户并没有选择显示签名，则不输出！
                                    if (signatureCell.isStamp) {
                                        //签章不能用role的图片！！！
                                        let sPObj = _getDupPicPath(signatureCell.path);
                                        if (sPObj !== null && isSinglePage) { // 只有在isSinglePage为true时，才需要优化签名
                                            signatureCell.signature_name = sPObj.signature_name; // 重点，如果有相同路径的，修改signature_name!
                                            rst = true;
                                        } else {
                                            const signPath = { signature_name: signatureCell.signature_name, path: null, pic: null };
                                            signPathArr.push(signPath);
                                            signPath.path = signatureCell.path;
                                            signKeyArr.push(signatureCell.signature_name);
                                            rst = true;
                                            signSheetIdxArr[pageIdx] = true;
                                        }
                                    } else if (role.sign_pic) {
                                        const signPath = { signature_name: signatureCell.signature_name, path: null, pic: null };
                                        signPathArr.push(signPath);
                                        signPath.pic = role.sign_pic;
                                        signKeyArr.push(signatureCell.signature_name);
                                        rst = true;
                                        signSheetIdxArr[pageIdx] = true;
                                    } else if (role.sign_path) {
                                        let sPObj = _getDupPicPath(role.sign_path);
                                        if (sPObj !== null && isSinglePage) { // 只有在isSinglePage为true时，才需要优化签名
                                            signatureCell.signature_name = sPObj.signature_name; // 重点，如果有相同路径的，修改signature_name!
                                            rst = true;
                                        } else {
                                            const signPath = { signature_name: signatureCell.signature_name, path: null, pic: null };
                                            signPathArr.push(signPath);
                                            signPath.path = role.sign_path;
                                            signKeyArr.push(signatureCell.signature_name);
                                            rst = true;
                                            signSheetIdxArr[pageIdx] = true;
                                        }
                                    }
                                }
                            }
                            break;
                        }
                    }
                } else {
                    // console.log(`duplicate signature: ${signatureCell.signature_name}`);
                    // signSheetIdxArr[pageIdx] = true;
                }
            }
        }
        if (page[JV.PROP_WATERMARK_CELLS] && page[JV.PROP_WATERMARK_CELLS].length > 0) {
            // 水印，单独处理
            const waterMarkCell = page[JV.PROP_WATERMARK_CELLS][0];
            if (signKeyArr.indexOf(waterMarkCell.signature_name) < 0) {
                const signPath = { path: null, pic: null };
                signPathArr.push(signPath);
                signPath.pic = waterMarkCell.pic;
                signPath.path = waterMarkCell.path;
                signKeyArr.push(waterMarkCell.signature_name);
                rst = true;
                signSheetIdxArr[pageIdx] = true;
            } else if (!isSinglePage) {
                signSheetIdxArr[pageIdx] = true;
            }
        }
    }
    return rst;
}

function _chkIfAudit(role_rel) {
    // STAGE_AUDIT, STAGE_LIST 已在index.ejs定义
    let rst = false;
    for (const stg_audit of STAGE_AUDIT) {
        if (role_rel.acc_id === stg_audit.aid) {
            if (stg_audit.status === 3) {
                rst = true;
            } else {
                rst = false;
            }
            // break; //因为实际业务中会有反复，所以就不break了，一直判断，以最后一个为准
        }
    }
    if (!rst) {
        // 还有原报需要检测
        for (const stg_ort_rpt of STAGE_LIST) {
            if (role_rel.acc_id === stg_ort_rpt.user_id) {
                if (stg_ort_rpt.status !== 1 && stg_ort_rpt.status !== 4) {
                    // 只要不是待上报(1)、退回审批(4) 状态,都需要显示
                    rst = true;
                } else {
                    rst = false;
                }
                // break; //因为实际业务中会有反复，所以就不break了，一直判断，以最后一个为准
            }
        }
    }
    // rst = true; //测试，未来将屏蔽
    return rst;
}

const excelExportUtil = {
    exportExcel: async function(pageData, paperSize, fName, options, custSheetNames, custSheetMergeBands, roleRel, callback, checkAudit = false) {
        const rptOptions = ({ singlePage: false, fileName: 'report' });
        if (options === 'true' || options === true) {
            rptOptions.singlePage = true;
        }
        const isSinglePage = rptOptions.singlePage;
        const sheets = [];
        const signKeyArr = [];
        const signPathArr = [];
        const signSheetIdxArr = []; // 确定哪些sheet有签名(在多表导出的时候，有些表可能没有签名，但其他的表有签名)
        let thisRoleRel = [];
        if (roleRel !== null && roleRel !== undefined) {
            thisRoleRel = JSON.parse(JSON.stringify(roleRel));
        }
        await rptSignatureHelper.resetDummySignature(pageData, thisRoleRel, true); // 把草图转换一下roleRel

        // const hasSignature = false; // 暂时不支持电子签名、草图导出excel
        const hasSignature = _checkAndSetSignatureCache(pageData, signKeyArr, signPathArr, thisRoleRel, signSheetIdxArr, isSinglePage, checkAudit); // 因草图的关系，thisRoleRel是否为null就不是充要的条件
        if (!hasSignature) {
            createDummySignatureCell(pageData);
        }
        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, 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 写所有的image
            const zip_media = zip_xl.folder('media');
            let picIdx = 0;
            // 优化调整，不再每页都设置一个，重复的签名只需要一个即可(极端case: 13xx多页的签名文档，导出有1.3G大)
            for (let pathIdx = 0; pathIdx < signPathArr.length; pathIdx++) {
                picIdx++;
                data = await writeImage(signPathArr[pathIdx].path, signPathArr[pathIdx].pic);
                file = 'image' + picIdx + '.png';
                zip_media.file(file, data, { compression: 'DEFLATE' });
            }
            // 5.2
            const zip_drawings = zip_xl.folder('drawings');
            data = writeDrawings(pageData, signKeyArr, signPathArr, isSinglePage, signSheetIdxArr);
            // data = writeDrawings(pageData, signKeyArr, signPathArr, false, signSheetIdxArr); // 备注：这里根本不需要考虑是否single page
            for (let psIdx = 0; psIdx < data.length; psIdx++) {
                file = 'drawing' + (psIdx + 1) + '.xml';
                zip_drawings.file(file, data[psIdx].join(''), { compression: 'DEFLATE' });
            }
            // 5.3
            const zip_drawings_rels = zip_drawings.folder('_rels');
            let relsIdx = 0;
            let relsImgAmt = 0;
            for (let ssIdx = 0; ssIdx < signKeyArr.length; ssIdx++) {
                data = writeDrawingsRels(signKeyArr.length, relsImgAmt); // 一个drawingX.xml.rels文件与一个drawingX.xml对应
                relsIdx++;
                if (isSinglePage) {
                    relsImgAmt += signKeyArr.length;
                }
                file = 'drawing' + relsIdx + '.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, signSheetIdxArr);
        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(1);
                file = 'sheet1.xml.rels';
                zip_xl_worksheets_rels.file(file, data.join(''), { compression: 'DEFLATE' });
            } else {
                let ssIdx = 0;
                for (let i = 0; i < data.length; i++) {
                    if (signSheetIdxArr[i]) {
                        ssIdx++;
                        const ssData = writeWorkSheetRels(ssIdx);
                        file = 'sheet' + (i + 1) + '.xml.rels';
                        zip_xl_worksheets_rels.file(file, ssData.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' });

        zip.generateAsync({type: 'blob'}).then(function(content){
            saveAs(content, `${fName}.xlsx`); // 页面需要引用 FileSaver.js 才有这个方法
        });
    },
    exportExcelInOneBook: async function(pageDataArray, paperSize, fName, roleRelArr, callback, checkAudit = false) {
        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]);

                // 加分页符(分页符要在外部处理好)-------------------
                _setupPgBrks(pageDataArray[i]);
                // 加分页符结束-------------------
                let maxY = 0;
                let minY = 100000;
                const checkAndResetCellYPos = function(cells) {
                    if (cells && cells.length > 0) {
                        for (const cell of cells) {
                            if (maxY < cell[JV.PROP_AREA][JV.PROP_BOTTOM]) {
                                maxY = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
                            }
                            if (minY > cell[JV.PROP_AREA][JV.PROP_TOP]) {
                                minY = cell[JV.PROP_AREA][JV.PROP_TOP];
                            }
                            cell[JV.PROP_AREA][JV.PROP_BOTTOM] += offsetY;
                            cell[JV.PROP_AREA][JV.PROP_TOP] += offsetY;
                        }
                    }
                };
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    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);
                    }
                    // 1.2.1 重新设置普通cells的Top Bottom坐标
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_CELLS]);
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_INTERACT_CELLS]);
                    // 1.2.2 重新设置电子签名cells的Top Bottom坐标
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_SIGNATURE_CELLS]);
                    // 1.2.3 重新设置电子签名日期cells的Top Bottom坐标
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_SIGNATURE_DATE_CELLS]);
                    // 1.2.4 重置电子签名审核意见cells的Top Bottom坐标
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_SIGNATURE_AUDIT_CELLS]);
                    // 1.2.5 重置水印的Top Bottom坐标
                    checkAndResetCellYPos(pageDataArray[i].items[j][JV.PROP_WATERMARK_CELLS]);

                    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 = [];
            newPageData.pageBreaks = [];
            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][JV.PROP_CELLS].length; k++) {
                        pageItem[JV.PROP_CELLS].push(pageDataArray[i].items[j][JV.PROP_CELLS][k]);
                    }
                }
                pageItem[JV.PROP_INTERACT_CELLS] = [];
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_INTERACT_CELLS].length; k++) {
                        pageItem[JV.PROP_INTERACT_CELLS].push(pageDataArray[i].items[j][JV.PROP_INTERACT_CELLS][k]);
                    }
                }
                pageItem[JV.PROP_SIGNATURE_CELLS] = [];
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_CELLS].length; k++) {
                        pageItem[JV.PROP_SIGNATURE_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_CELLS][k]);
                    }
                }
                pageItem[JV.PROP_SIGNATURE_DATE_CELLS] = [];
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_DATE_CELLS].length; k++) {
                        pageItem[JV.PROP_SIGNATURE_DATE_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_DATE_CELLS][k]);
                    }
                }
                pageItem[JV.PROP_SIGNATURE_AUDIT_CELLS] = [];
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_SIGNATURE_AUDIT_CELLS].length; k++) {
                        pageItem[JV.PROP_SIGNATURE_AUDIT_CELLS].push(pageDataArray[i].items[j][JV.PROP_SIGNATURE_AUDIT_CELLS][k]);
                    }
                }
                pageItem[JV.PROP_WATERMARK_CELLS] = [];
                for (let j = 0; j < pageDataArray[i].items.length; j++) {
                    for (let k = 0; k < pageDataArray[i].items[j][JV.PROP_WATERMARK_CELLS].length; k++) {
                        pageItem[JV.PROP_WATERMARK_CELLS].push(pageDataArray[i].items[j][JV.PROP_WATERMARK_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
            await me.exportExcel(newPageData, paperSize, fName, 'false', sheetNames, custMergeBands, roleRelArr, callback, checkAudit);
        } catch (e) {
            console.log(e);
        }
    }
};

function convertPicBase64ToBlob(picdata) {
    //去掉头（如：'data:image/png;base64,'），并转换为byte
    let splitPic = picdata.split(',');
    let tmpPicData = '';
    if (splitPic.length === 2) {
        tmpPicData = splitPic[1];
    } else {
        tmpPicData = splitPic[0];
    }
    var bytes = window.atob(tmpPicData);
    //处理异常,将ascii码小于0的转换为大于0
    var ab = new Uint8Array(bytes.length);
    for (var i = 0; i < bytes.length; i++) {
        ab[i] = bytes.charCodeAt(i);
    }
    return new Blob([ab], { type: 'application/pdf' });
}