ConstructionCost/modules/reports/util/rpt_excel_util.js

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

function writeContentTypes(sheets) {
    let rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">');
    //...
    rst.push('<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>');
    rst.push('<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>');
    rst.push('<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>');
    rst.push('<Default Extension="xml" ContentType="application/xml"/>');
    rst.push('<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>');
    rst.push('<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>');
    rst.push('<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>');
    for (let i = 0; i < sheets.length; i++) {
        rst.push('<Override PartName="/xl/worksheets/sheet' + (i + 1) + '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>')
    }
    rst.push('<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>');
    rst.push('</Types>');
    return rst;
}
function writeRootRels(){
    let rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
    rst.push('<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>');
    rst.push('<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>');
    rst.push('<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>');
    rst.push('</Relationships>');
    return rst;
}
function writeApp(sheets) {
    let rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">');
    rst.push('<Application>Microsoft Excel</Application>');
    rst.push('<DocSecurity>0</DocSecurity>');
    rst.push('<ScaleCrop>false</ScaleCrop>');
    rst.push('<HeadingPairs>');
    rst.push('<vt:vector size="2" baseType="variant">');
    rst.push('<vt:variant><vt:lpstr>工作表</vt:lpstr></vt:variant>');
    rst.push('<vt:variant><vt:i4>' + sheets.length + '</vt:i4></vt:variant>');
    rst.push('</vt:vector>');
    rst.push('</HeadingPairs>');
    rst.push('<TitlesOfParts>');
    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('');
    rst.push('</Properties>');
    return rst;
}
function writeCore() {
    let rst = [];
    p_fillZero = function(val){
        let rst = val;
        if (val < 10) {
            rst = '0' + val;
        }
        return rst;
    };
    rst.push(dftHeadXml + '\r\n');
    rst.push('<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');
    rst.push('<dc:creator>SmartCost</dc:creator>');
    rst.push('<cp:lastModifiedBy>SmartCost</cp:lastModifiedBy>');
    let dt = new Date(), dtStr = dt.getFullYear() + '-' + p_fillZero(dt.getMonth()+1) + '-' + p_fillZero(dt.getDate()) + 'T' +
        p_fillZero(dt.getHours()) + ':' + p_fillZero(dt.getMinutes()) + ':' + p_fillZero(dt.getSeconds()) + 'Z';
    rst.push('<dcterms:created xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:created>');
    rst.push('<dcterms:modified xsi:type="dcterms:W3CDTF">' + dtStr + '</dcterms:modified>');
    //rst.push('');
    rst.push('</cp:coreProperties>');
    return rst;
}
function writeXlWorkBook(sheets){
    let rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
    rst.push('<fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>');
    rst.push('<workbookPr defaultThemeVersion="124226"/>');
    rst.push('<bookViews><workbookView xWindow="360" yWindow="345" windowWidth="14655" windowHeight="4305"/></bookViews>');
    rst.push('<sheets>');
    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('');
    rst.push('</workbook>');
    return rst;
}
function writeXlRels(sheets){
    let rst = [], idx = 1;
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
    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('');
    rst.push('</Relationships>');
    return rst;
}
function writeTheme(){
    let rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r');
    return rst;
}
function writeStyles(stylesObj){
    let rst = [];
    rst.push(dftHeadXml + '\r\n');
    rst.push('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
    //1. push fonts
    rst.push('<fonts count="' + stylesObj.fonts.length + '">')
    for (let i = 0; i < stylesObj.fonts.length; i++) {
        let font = stylesObj.fonts[i];
        rst.push('<font>');
        if (strUtil.convertStrToBoolean(font[JV.FONT_PROPS[3]])) {
            rst.push('<b/>');
        }
        rst.push('<sz val="' + font.size + '"/>');
        rst.push('<color indexed="' + font.colorIdx + '"/>');
        rst.push('<name val="' + font[JV.FONT_PROPS[0]] + '"/>');
        rst.push('<charset val="' + font.charset + '"/>');
        rst.push('</font>');
    }
    rst.push('</fonts>');
    //2. push default fills
    rst.push('<fills count="2"><fill><patternFill patternType="none" /></fill><fill><patternFill patternType="gray125" /></fill></fills>');
    //3. push borders
    rst.push('<borders count="' + stylesObj.borders.length + '">')
    private_setBorder = function(border, borderDirection) {
        if (border[borderDirection][JV.PROP_LINE_WEIGHT] == 0) {
            rst.push('<' + borderDirection.toLowerCase() + '/>');
        } else {
            let bW = 'thin';
            if (border[borderDirection][JV.PROP_LINE_WEIGHT] == 2) bW = 'medium';
            if (border[borderDirection][JV.PROP_LINE_WEIGHT] > 2) bW = 'thick';
            rst.push('<' + borderDirection.toLowerCase() + ' style="' + bW + '">' + '<color indexed="64"/>' + '</' + borderDirection.toLowerCase() + '>');
        }
    };
    for (let i = 0; i < stylesObj.borders.length; i++) {
        let border = stylesObj.borders[i];
        rst.push('<border>');
        private_setBorder(border, JV.PROP_LEFT);
        private_setBorder(border, JV.PROP_RIGHT);
        private_setBorder(border, JV.PROP_TOP);
        private_setBorder(border, JV.PROP_BOTTOM);
        rst.push('<diagonal />');
        rst.push('</border>');
    }
    rst.push('</borders>');
    //4. push cellStyleXfs
    rst.push('<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"><alignment vertical="center"/></xf></cellStyleXfs>');
    //5. push cellXfs
    rst.push('<cellXfs count="' + stylesObj.cellXfs.length + '">');
    for (let i = 0; i < stylesObj.cellXfs.length; i++) {
        let excelStyle = stylesObj.cellXfs[i];
        rst.push('<xf numFmtId="0" fontId="' + excelStyle.fontId + '" fillId="0" borderId="' + excelStyle.borderId + '" xfId="0">');
        let alignStr = '<alignment horizontal="' + excelStyle[JV.CONTROL_PROPS[2]] + '" vertical="' + excelStyle[JV.CONTROL_PROPS[3]] + '"';
        if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[1]])) {
            alignStr = alignStr + ' shrinkToFit="1"';
        }
        if (strUtil.convertStrToBoolean(excelStyle[JV.CONTROL_PROPS[4]])) {
            alignStr = alignStr + ' wrapText="1"';
        }
        alignStr = alignStr + '/>';
        rst.push(alignStr);
        rst.push('</xf>');
    }
    rst.push('</cellXfs>');
    //6. others (xfl style / dxfs / tableStyles)
    rst.push('<cellStyles count="1"><cellStyle name="常规" xfId="0" builtinId="0"/></cellStyles>');
    rst.push('<dxfs count="0"/>');
    rst.push('<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>');
    rst.push('</styleSheet>');
    return rst;
}
function writeSharedString(sharedStrList){
    let rst = [];
    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 + '">');
        for (let i = 0; i < sharedStrList.length; i++) {
            //rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
            if (typeof sharedStrList[i] === 'string') {
                rst.push('<si><t>' + sharedStrList[i].replace('|','\r\n') + '</t></si>');
            } else {
                rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
            }
        }
        rst.push('</sst>');
    }
    return rst;
}
function writeSheets(pageData, sharedStrList, stylesObj){
    let rst = [];
    private_pushDftFont = function(){
        let font = {};
        if (!(stylesObj.fonts)) {
            stylesObj.fonts = [];
        }
        font[JV.FONT_PROPS[0]] = "宋体"; //font name
        font.size = 11;
        font.charset = 134;
        font.colorIdx = "8";
        stylesObj.fonts.push(font);
    };
    private_pushDftFont();
    for (let i = 0; i < pageData.items.length; i++) {
        rst.push(writeSheet(pageData, pageData.items[i], sharedStrList, stylesObj));
    }
    return rst;
}
function writeSheet(pageData, sheetData, sharedStrList, stylesObj){
    let rst = [], xPos = [], yPos = [], headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    let cacheBorderCell = {};
    xPos.push(0);
    yPos.push(0);
    private_pre_analyze_pos = function(){
        let cell, pos;
        sheetData.cells.sort(function(cell1, cell2) {
            let rst = 0;
            if (cell1[JV.PROP_AREA][JV.PROP_TOP] > cell2[JV.PROP_AREA][JV.PROP_TOP]) {
                rst = 1;
            } else if (cell1[JV.PROP_AREA][JV.PROP_TOP] < cell2[JV.PROP_AREA][JV.PROP_TOP]) {
                rst = -1;
            } else {
                if (cell1[JV.PROP_AREA][JV.PROP_LEFT] > cell2[JV.PROP_AREA][JV.PROP_LEFT]) {
                    rst = 1;
                } else if (cell1[JV.PROP_AREA][JV.PROP_LEFT] < cell2[JV.PROP_AREA][JV.PROP_LEFT]) {
                    rst = -1;
                }
            }
            return rst;
        });
        for (let i = 0; i < sheetData.cells.length; i++) {
            cell = sheetData.cells[i];
            pos = cell[JV.PROP_AREA][JV.PROP_LEFT];
            if (xPos.indexOf(pos) < 0) xPos.push(pos);
            pos = cell[JV.PROP_AREA][JV.PROP_RIGHT];
            if (xPos.indexOf(pos) < 0) xPos.push(pos);
            pos = cell[JV.PROP_AREA][JV.PROP_TOP];
            if (yPos.indexOf(pos) < 0) yPos.push(pos);
            pos = cell[JV.PROP_AREA][JV.PROP_BOTTOM];
            if (yPos.indexOf(pos) < 0) yPos.push(pos);
        }
        xPos.sort(private_array_sort);
        yPos.sort(private_array_sort);
    };
    private_array_sort = function(i1, i2){
        let rst = 0;
        if (i1 > i2) {rst = 1} else
        if (i1 < i2) rst = -1;
        return rst;
    };
    private_getCellIdxStr = function(idx){
        let rst = 'A';
        if (idx < 26) {
            rst = headerStr[idx];
        } else if (idx < 26*26+26) {
            let ti = Math.floor(idx / 26), tj = idx % 26;
            rst = headerStr[ti - 1] + headerStr[tj];
        } else if (idx < 26*26*26+26) {
            let ti = Math.floor(idx / (26*26)), tj = Math.floor((idx - ti * 26*26) / 26), tk = idx % 26;
            rst = headerStr[ti - 1] + headerStr[tj-1] + headerStr[tk];
        }
        return rst;
    };
    private_getSharedStrIdx = function(val) {
        let rst = sharedStrList.indexOf(val);
        if (rst < 0) {
            sharedStrList.push(val);
            rst = sharedStrList.length - 1;
        }
        return rst;
    };
    private_getFontId = function(cell) {
        let rst = 0, hasFont = false;
        if (!(stylesObj.fonts)) {
            stylesObj.fonts = [];
            //for (let i = 0; i < sheetData.font_collection)
        }
        let sheetFont = pageData.font_collection[cell.font];
        for (let i = 0; i < stylesObj.fonts.length; i++) {
            let font = stylesObj.fonts[i];
            if (sheetFont) {
                if (font[JV.FONT_PROPS[0]] === sheetFont[JV.FONT_PROPS[0]] && font.size === Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4) && font[JV.FONT_PROPS[3]] == sheetFont[JV.FONT_PROPS[3]]) {
                    hasFont = true;
                    rst = i;
                    break;
                }
            } else {
                break;
            }
        }
        if (!hasFont) {
            let font = {};
            font[JV.FONT_PROPS[0]] = sheetFont[JV.FONT_PROPS[0]]; //font name
            font.size = Math.round(sheetFont[JV.FONT_PROPS[1]] * 3 / 4);
            font.charset = 134;
            font.colorIdx = "8";
            font[JV.FONT_PROPS[3]] = sheetFont[JV.FONT_PROPS[3]]; //font bold
            stylesObj.fonts.push(font);
            rst = stylesObj.fonts.length - 1;
        }
        return rst;
    };
    private_checkBorder = function(cell, border, sheetBorder) {
        let rst = true, borderLineWidths = [], sheetBorderLineWidths = [];
        borderLineWidths.push(border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]);
        borderLineWidths.push(border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT]);
        borderLineWidths.push(border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT]);
        borderLineWidths.push(border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT]);
        if (sheetBorder[JV.PROP_LEFT] && sheetBorder[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT]) {
            sheetBorderLineWidths.push(private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT));
        } 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));
        } 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));
        } 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));
        } else {
            sheetBorderLineWidths.push(0);
        }
        for (let i = 0; i < 4; i++) {
            if (borderLineWidths[i] != sheetBorderLineWidths[i]) {
                rst = false;
                break;
            }
        }
        return rst;
    };
    private_chkAndGetMergeLine = function(cell, sheetBorder, borderStr) {
        let rst = sheetBorder[borderStr][JV.PROP_LINE_WEIGHT], mergeBorder = pageData[JV.BAND_PROP_MERGE_BAND];
        if (cell[JV.PROP_AREA][borderStr] == mergeBorder[borderStr]) {
            let destStyle = pageData[JV.NODE_STYLE_COLLECTION][mergeBorder[JV.PROP_STYLE][JV.PROP_ID]];
            rst = destStyle[borderStr][JV.PROP_LINE_WEIGHT];
        }
        return parseInt(rst);
    };
    private_getIniBorder = function() {
        let rst = {};
        rst[JV.PROP_LEFT] = {};
        rst[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = 0;
        rst[JV.PROP_RIGHT] = {};
        rst[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = 0;
        rst[JV.PROP_TOP] = {};
        rst[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = 0;
        rst[JV.PROP_BOTTOM] = {};
        rst[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = 0;
        return rst;
    };
    private_getBorderId = function(cell) {
        let rst = 0, hasBorder = false;
        if (!(stylesObj.borders)) {
            stylesObj.borders = [];
        }
        let sheetBorder = pageData[JV.NODE_STYLE_COLLECTION][cell.style];
        for (let i = 0; i < stylesObj.borders.length; i++) {
            let border = stylesObj.borders[i];
            if (private_checkBorder(cell, border, sheetBorder)) {
                hasBorder = true;
                rst = i;
                break;
            }
        }
        if (!hasBorder) {
            let border = private_getIniBorder();
            if (sheetBorder && sheetBorder[JV.PROP_LEFT]) {
                border[JV.PROP_LEFT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_LEFT);
            }
            if (sheetBorder && sheetBorder[JV.PROP_RIGHT]) {
                border[JV.PROP_RIGHT][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_RIGHT);
            }
            if (sheetBorder && sheetBorder[JV.PROP_TOP]) {
                border[JV.PROP_TOP][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_TOP);
            }
            if (sheetBorder && sheetBorder[JV.PROP_BOTTOM]) {
                border[JV.PROP_BOTTOM][JV.PROP_LINE_WEIGHT] = private_chkAndGetMergeLine(cell, sheetBorder, JV.PROP_BOTTOM);
            }
            stylesObj.borders.push(border);
            rst = stylesObj.borders.length - 1;
        }
        return rst;
    };
    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;
    };
    private_getStyleId = function(cell) {
        let rst = 1, hasStyle = false;
        if (!(stylesObj.cellXfs)) stylesObj.cellXfs = [];
        let fontId = private_getFontId(cell);
        let borderId = private_getBorderId(cell);
        let cellControl = pageData[JV.NODE_CONTROL_COLLECTION][cell[JV.PROP_CONTROL]];
        for (let i = 0; i < stylesObj.cellXfs.length; i++) {
            let sheetControl = stylesObj.cellXfs[i];
            if (sheetControl.fontId == fontId && sheetControl.borderId == borderId) {
                if (private_checkControl(cellControl, sheetControl)) {
                    rst = i;
                    hasStyle = true;
                    break;
                }
            }
        }
        if (!hasStyle) {
            let sheetControl = {};
            sheetControl.fontId = fontId;
            sheetControl.borderId = borderId;
            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;
    };
    private_setCols = function(){
        //remark: 1 excel width = 2.117 mm
        rst.push('<cols>');
        let w = 0;
        for (let i = 1; i < xPos.length - 1; i++) {
            w = 1.0 * (xPos[i + 1] - xPos[i]) / DPI * 25.4 / 2.117;
            w = Math.round(w * 1000) / 1000;
            rst.push('<col min="' + i +'" max="' + i +'" width="' + w + '" customWidth="1"/>');
        }
        rst.push('</cols>');
    };
    private_setMergedCells = function() {
        let cell, idxR, idxL, idxT, idxB, cnt = 0;
        rst.push('<mergeCells count="?">');
        let startIdx = rst.length - 1;
        for (let i = 0; i < sheetData.cells.length; i++) {
            cell = sheetData.cells[i];
            idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
            idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
            idxB = yPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
            idxT = yPos.indexOf(cell[JV.PROP_AREA][JV.PROP_TOP]);
            if (idxR - idxL > 1 || idxB - idxT > 1) {
                rst.push('<mergeCell ref="' + private_getCellIdxStr(idxL - 1) + idxT + ':' + private_getCellIdxStr(idxR - 2) + (idxB - 1) + '"/>');
                cnt++;
            }
        }
        rst[startIdx] = '<mergeCells count="' + cnt + '">';
        rst.push('</mergeCells>');
    };
    private_chkIfNeedCacheCell = function(cell){
        let rst = false;
        if (cell[JV.PROP_AREA][JV.PROP_LEFT] == pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_LEFT] ||
            cell[JV.PROP_AREA][JV.PROP_RIGHT] == pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_RIGHT] ||
            cell[JV.PROP_AREA][JV.PROP_TOP] == pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_TOP] ||
            cell[JV.PROP_AREA][JV.PROP_BOTTOM] == pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_BOTTOM]){
            if (cell[JV.PROP_AREA][JV.PROP_LEFT] >= pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_LEFT] &&
                cell[JV.PROP_AREA][JV.PROP_RIGHT] <= pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_RIGHT] &&
                cell[JV.PROP_AREA][JV.PROP_TOP] >= pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_TOP] &&
                cell[JV.PROP_AREA][JV.PROP_BOTTOM] <= pageData[JV.BAND_PROP_MERGE_BAND][JV.PROP_BOTTOM]) {
                rst = true;
            }
        }
        return rst;
    };
    private_cacheMergeBandBorderIdxs = function() {
        let cell, idxR, idxL, idxT, idxB;
        for (let i = 0; i < sheetData.cells.length; i++) {
            cell = sheetData.cells[i];
            idxR = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_RIGHT]);
            idxL = xPos.indexOf(cell[JV.PROP_AREA][JV.PROP_LEFT]);
            idxB = yPos.indexOf(cell[JV.PROP_AREA][JV.PROP_BOTTOM]);
            idxT = yPos.indexOf(cell[JV.PROP_AREA][JV.PROP_TOP]);
            if (idxR - idxL > 1 || idxB - idxT > 1) {
                if (private_chkIfNeedCacheCell(cell)) {
                    for (let xi = idxL; xi < idxR; xi++) {
                        for (let yj = idxT; yj < idxB; yj++) {
                            cacheBorderCell[private_getCellIdxStr(xi - 1) + yj] = cell;
                        }
                    }
                }
            }
        }
    };
    private_getMergedCellStyleId = function(preStyleId, colIdxStr) {
        let rst = preStyleId;
        if (cacheBorderCell[colIdxStr]) {
            rst = private_getStyleId(cacheBorderCell[colIdxStr]);
        }
        return rst;
    };
    private_setSheetData = function(){
        //remark: 1 excel height = 0.3612 mm
        rst.push('<sheetData>');
        let spanX = xPos.length - 2, cellIdx = 0, h = 0,
            hasMoreCols = true, nextColIdx = -1,
            nextRowIdx = yPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_TOP])
            ;
        private_cacheMergeBandBorderIdxs();
        for (let i = 1; i < yPos.length - 1; i++) {
            h = 1.0 * (yPos[i+1] - yPos[i]) / DPI * 25.4 / 0.3612;
            h = Math.round(h * 1000) / 1000;
            rst.push('<row r="' + i + '" spans="1:' + spanX + '" ht="' + h + '" customHeight="1">');
            //then put the cells of this row
            let colIdxStr = '';
            hasMoreCols = true;
            while (nextRowIdx < i) {
                if (cellIdx >= sheetData.cells.length || nextRowIdx > i) {
                    break;
                } else {
                    cellIdx++;
                    nextRowIdx = yPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_TOP]);
                }
            }
            if (nextRowIdx > i) {
                hasMoreCols = false;
            }
            nextColIdx = xPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_LEFT]);
            let preStyleIdx = 1;
            for (let j = 1; j < xPos.length - 1; j++) {
                colIdxStr = private_getCellIdxStr(j - 1);
                if (hasMoreCols) {
                    if (nextColIdx == j) {
                        let styleIdx = private_getStyleId(sheetData.cells[cellIdx]);
                        preStyleIdx = styleIdx;
                        if (strUtil.isEmptyString(sheetData.cells[cellIdx][JV.PROP_VALUE])) {
                            rst.push('<c r="' + colIdxStr + i + '" s="' + styleIdx + '"/>');
                            //should setup the right style instead!
                        } else {
                            let valIdx = private_getSharedStrIdx(sheetData.cells[cellIdx][JV.PROP_VALUE]);
                            rst.push('<c r="' + colIdxStr + i + '" s="' + styleIdx + '" t="s">');
                            rst.push('<v>' + valIdx + '</v>');
                            rst.push('</c>');
                        }
                        cellIdx++;
                        if (cellIdx < sheetData.cells.length) {
                            nextRowIdx = yPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_TOP]);
                            if (nextRowIdx > i) {
                                hasMoreCols = false;
                            } else {
                                nextColIdx = xPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_LEFT]);
                            }
                        } else {
                            hasMoreCols = false;
                        }
                    } else if (nextColIdx < 0) {
                        //impossible!
                        console.log('has abnormal case!');
                        hasMoreCols = false;
                    } else {
                        //rst.push('<c r="' + colIdxStr + i + '" s="' + preStyleIdx + '"/>');
                        rst.push('<c r="' + colIdxStr + i + '" s="' + private_getMergedCellStyleId(preStyleIdx, colIdxStr + i) + '"/>');
                    }
                } else {
                    //rst.push('<c r="' + colIdxStr + i + '" s="' + preStyleIdx + '"/>');
                    rst.push('<c r="' + colIdxStr + i + '" s="' + private_getMergedCellStyleId(preStyleIdx, colIdxStr + i) + '"/>');
                }
            }
            rst.push('</row>');
        }
        //sheetData.cells.length
        rst.push('</sheetData>');
    };
    private_pre_analyze_pos();
    rst.push(dftHeadXml + '\r\n');
    rst.push('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
    let colStr = private_getCellIdxStr(xPos.length - 3);
    rst.push('<dimension ref="A1:' + colStr + '' + yPos.length + '"/>');
    rst.push('<sheetViews><sheetView tabSelected="1" workbookViewId="0">');
    rst.push('<selection sqref="A1:' + colStr + '1"/>');
    rst.push('</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('<pageSetup paperSize="9" fitToWidth="0" fitToHeight="0" orientation="landscape" horizontalDpi="300" verticalDpi="300"/>');
    rst.push('<headerFooter alignWithMargins="0"/>');
    rst.push('</worksheet>');
    //rst.push('');
    return rst;
}

module.exports = {
    exportExcel: function (pageData, fName, options, callback) {
        let rptOptions = (options || {singlePage: false, fileName: 'report'});
        let sheets = [];
        for (let i = 0; i < pageData.items.length; i++) {
            sheets.push({sheetName: '第' + (i + 1) + '页'});
        }
        //1.
        let file = '[Content_Types].xml';
        let data = writeContentTypes(sheets);
        let zip = new JSZip();
        zip.file(file, data.join(''), {compression: 'DEFLATE'});
        //2.
        let zip_rels = zip.folder('_rels');
        file = '.rels';
        data = writeRootRels();
        zip_rels.file(file, data.join(''), {compression: 'DEFLATE'});
        //3.
        let zip_docProps = zip.folder('docProps');
        file = 'app.xml';
        data = writeApp(sheets);
        zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
        file = 'core.xml';
        data = writeCore();
        zip_docProps.file(file, data.join(''), {compression: 'DEFLATE'});
        //4.
        let zip_xl = zip.folder('xl');
        file = 'workbook.xml';
        data = writeXlWorkBook(sheets);
        zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
        let zip_rels2 = zip_xl.folder('_rels');
        file = 'workbook.xml.rels';
        data = writeXlRels(sheets);
        zip_rels2.file(file, data.join(''), {compression: 'DEFLATE'});
        //5.
        let zip_theme = zip_xl.folder('theme');
        file = 'theme1.xml';
        data = writeTheme();
        zip_theme.file(file, data, {compression: 'DEFLATE'});
        //6.
        let zip_worksheets = zip_xl.folder('worksheets');
        let sharedStrList = [], stylesObj = {};
        data = writeSheets(pageData, sharedStrList, stylesObj);
        for (let i = 0; i < data.length; i++) {
            file = 'sheet' + (i + 1) + '.xml';
            zip_worksheets.file(file, data[i].join(''), {compression: 'DEFLATE'});
        }
        file = 'sharedStrings.xml';
        data = writeSharedString(sharedStrList);
        zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});
        file = 'styles.xml';
        data = writeStyles(stylesObj);
        zip_xl.file(file, data.join(''), {compression: 'DEFLATE'});

        if (fName) {
            let newName = '' + (new Date()).valueOf();
            zip.generateNodeStream({type:'nodebuffer',streamFiles:true})
                .pipe(fs.createWriteStream(__dirname.slice(0, __dirname.length - 21) + '/tmp/' + newName + '.xlsx'))
                .on('finish', function () {
                    // JSZip generates a readable stream with a "end" event,
                    // but is piped here in a writable stream which emits a "finish" event.
                    console.log(newName + ".xlsx was written.");
                    if (callback) callback(newName);
                }
            );
        } else {
            //return zip.generateNodeStream({type:'nodebuffer',streamFiles:true});
            return zip;
        }
    }
}