ConstructionCost/modules/reports/util/rpt_excel_util.js

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

function writeContentTypes(sheets) {
    var 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 (var i = 0; i < sheets.length; i++) {
        rst.push('<Override PartName="/xl/worksheets/' + sheets[i].sheetName + '.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(){
    var 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) {
    var 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 (var 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() {
    var rst = [];
    p_fillZero = function(val){
        var 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>');
    var dt = new Date(), dtStr = dt.getFullYear() + '-' + p_fillZero(dt.getMonth()) + '-' + 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){
    var 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 (var i = 0; i < sheets.length; i++) {
        rst.push('<sheet name="' + sheets[i].sheetName + '" sheetId="' + (i + 1) + 'r:id="rId' + (i + 1) + '1"/>')
    }
    rst.push('</sheets>');
    rst.push('<calcPr calcId="124519"/>');
    //rst.push('');
    rst.push('</workbook>');
    return rst;
}
function writeXlRels(sheets){
    var rst = [], idx = 1;
    rst.push(dftHeadXml + '\r\n');
    rst.push('<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">');
    for (var i = 0; i < sheets.length; i++) {
        rst.push('<Relationship Id="rId' + idx + '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/' + sheets[i].sheetName + '.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(){
    var rst = fs.readFileSync('./excel_base_files/theme1.xml', 'utf8', 'r');
    return rst;
}
function writeStyles(styleList){
    //
}
function writeSharedString(sharedStrList){
    var 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 (var i = 0; i < sharedStrList.length; i++) {
            rst.push('<si><t>' + sharedStrList[i] + '</t></si>');
        }
        rst.push('</sst>');
    }
    return rst;
}
function writeSheets(pageData, sharedStrList, styleList){
    var rst = [];
    for (var i = 0; i < pageData.items.length; i++) {
        rst.push(writeSheet(pageData.items[i], sharedStrList, styleList));
    }
    return rst;
}
function writeSheet(sheetData, sharedStrList, styleList){
    var rst = [], xPos = [], yPos = [], headerStr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    xPos.push(0);
    yPos.push(0);
    private_pre_analyze_pos = function(){
        var cell, pos;
        sheetData.cells.sort(function(cell1, cell2) {
            var 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 (var 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){
        var rst = 0;
        if (i1 > i2) {rst = 1} else
        if (i1 < i2) rst = -1;
        return rst;
    };
    private_getCellIdxStr = function(idx){
        var rst = 'A';
        if (idx < 26) {
            rst = headerStr[idx];
        } else if (idx < 26*26+26) {
            var ti = Math.floor(idx / 26), tj = idx % 26;
            rst = headerStr[ti - 1] + headerStr[tj];
        } else if (idx < 26*26*26+26) {
            var 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) {
        var rst = sharedStrList.indexOf(val);
        if (rst < 0) {
            sharedStrList.push(val);
            rst = sharedStrList.length - 1;
        }
        return rst;
    };
    private_getStyleIdx = function(cell) {
        var rst = 1;
        //
        return rst;
    };
    private_setCols = function(){
        //remark: 1 excel width = 2.117 mm
        rst.push('<cols>');
        var w = 0;
        for (var i = 1; i < xPos.length; i++) {
            w = 1.0 * (xPos[i] - xPos[i - 1]) / 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() {
        var cell, idxR, idxL, idxT, idxB, cnt = 0;
        rst.push('<mergeCells count="?">');
        var startIdx = rst.length - 1;
        for (var 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_setSheetData = function(){
        //remark: 1 excel height = 0.3612 mm
        rst.push('<sheetData>');
        var spanX = xPos.length - 1, cellIdx = 0, h = 0,
            hasMoreCols = true, nextColIdx = -1,
            nextRowIdx = yPos.indexOf(sheetData.cells[cellIdx][JV.PROP_AREA][JV.PROP_TOP]);
        for (var i = 1; i < yPos.length - 1; i++) {
            if (i === 0) {
                h = 1.0 * (yPos[i]) / DPI * 25.4 / 0.3612;
            } else {
                h = 1.0 * (yPos[i] - yPos[i - 1]) / 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
            var 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]);
            for (var j = 0; j < xPos.length; j++) {
                colIdxStr = private_getCellIdxStr(j);
                if (hasMoreCols) {
                    if (nextColIdx == j) {
                        var styleIdx = private_getStyleIdx(sheetData.cells[cellIdx]);
                        if (strUtil.isEmptyString(sheetData.cells[cellIdx][JV.PROP_VALUE])) {
                            rst.push('<c r="' + colIdxStr + i + '" s="' + styleIdx + '"/>');
                            //should setup the right style instead!
                        } else {
                            var valIdx = private_getSharedStrIdx(sheetData.cells[cellIdx][JV.PROP_VALUE]);
                            rst.push('<c r="' + colIdxStr + i + '" s="' + styleIdx + '">');
                            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="1"/>');
                    }
                } else {
                    rst.push('<c r="' + colIdxStr + i + '" s="1"/>');
                }
            }
            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">');
    var colStr = private_getCellIdxStr(xPos.length - 2);
    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, options) {
        var rptOptions = (options || {singlePage: false, fileName: 'report'});
    }
    ,testWriteContentTypes: function(sheets) {
        return writeContentTypes(sheets);
    }
    ,testWriteRootRels: function() {
        return writeRootRels();
    }
    ,testWriteApp: function(sheets) {
        return writeApp(sheets);
    }
    ,testWriteCore: function() {
        return writeCore();
    }
    ,testWriteXlWorkBook: function(sheets) {
        return writeXlWorkBook(sheets);
    }
    ,testWriteXlRels: function(sheets) {
        return writeXlRels(sheets);
    }
    ,testWriteSheets: function(pageData, sharedStrList, styleList){
        return writeSheets(pageData, sharedStrList, styleList);
    }
    ,testWriteSharedString: function(sharedStrList){
        return writeSharedString(sharedStrList);
    }
}