/** * 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 = ''; 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(''); rst.push(''); rst.push(''); if (hasSignature) { rst.push(''); } rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); if (isSinglePage) { rst.push(''); if (hasSignature) { rst.push(''); } } else { for (let i = 0; i < sheets.length; i++) { rst.push(''); if (hasSignature) { rst.push(''); } } } rst.push(''); rst.push(''); return rst; } function writeRootRels() { const rst = []; rst.push(dftHeadXml + '\r\n'); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); return rst; } function writeApp(sheets, isSinglePage) { const rst = []; rst.push(dftHeadXml + '\r\n'); rst.push(''); rst.push('Microsoft Excel'); rst.push('0'); rst.push('false'); rst.push(''); rst.push(''); rst.push('工作表'); if (isSinglePage) rst.push('1'); else rst.push('' + sheets.length + ''); rst.push(''); rst.push(''); rst.push(''); if (isSinglePage) { rst.push(''); rst.push('' + sheets[0].sheetName + ''); } else { rst.push(''); for (let i = 0; i < sheets.length; i++) { rst.push('' + sheets[i].sheetName + ''); } } rst.push(''); rst.push(''); rst.push('SmartCost'); rst.push('false'); rst.push('false'); rst.push('false'); rst.push('12.0000'); rst.push(''); 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(''); rst.push('SmartCost'); rst.push('SmartCost'); 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('' + dtStr + ''); rst.push('' + dtStr + ''); rst.push(''); return rst; } function writeXlWorkBook(sheets, isSinglePage) { const rst = []; rst.push(dftHeadXml + '\r\n'); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); if (isSinglePage) { rst.push(''); } else { for (let i = 0; i < sheets.length; i++) { rst.push(''); } } rst.push(''); rst.push(''); rst.push(''); return rst; } function writeXlRels(sheets, isSinglePage) { const rst = []; let idx = 1; rst.push(dftHeadXml + '\r\n'); rst.push(''); if (isSinglePage) { rst.push(''); idx++; } else { for (let i = 0; i < sheets.length; i++) { rst.push(''); idx++; } } rst.push(''); idx++; rst.push(''); idx++; rst.push(''); rst.push(''); return rst; } function writeTheme() { // const rst = fs.readFileSync(__dirname + '/excel_base_files/theme1.xml', 'utf8', 'r'); const rst = [``]; // 前端没有这个文件,需要想想怎么给(说白了,也就是一个一个xml文件而已) return rst; } function writeStyles(stylesObj, hasSignature) { const rst = []; rst.push(dftHeadXml + '\r\n'); rst.push(''); // 1. push fonts rst.push(''); for (let i = 0; i < stylesObj.fonts.length; i++) { const font = stylesObj.fonts[i]; rst.push(''); if (stringUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_BOLD]])) { rst.push(''); } if (stringUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_ITALIC]])) { rst.push(''); } if (stringUtil.convertStrToBoolean(font[JV.FONT_PROPS[JV.FONT_PROP_IDX_UNDERLINE]])) { rst.push(''); } rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); } rst.push(''); // 2. push default fills rst.push(''); // 3. push borders rst.push(''); 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 + '">'); } }; for (let i = 0; i < stylesObj.borders.length; i++) { const border = stylesObj.borders[i]; rst.push(''); 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(''); rst.push(''); } rst.push(''); // 4. push cellStyleXfs rst.push(''); // 5. push cellXfs rst.push(''); for (let i = 0; i < stylesObj.cellXfs.length; i++) { const excelStyle = stylesObj.cellXfs[i]; rst.push(''); // pageData[JV.NODE_FONT_COLLECTION] excelStyle.fontId let alignStr = ' 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(''); } rst.push(''); // 6. others (xfl style / dxfs / tableStyles) rst.push(''); rst.push(''); rst.push(''); if (hasSignature) { rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); } rst.push(''); 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('' + cellVal + ''); } else { rst.push('' + cellVal + ''); } } }; if (sharedStrList && sharedStrList.length > 0) { rst.push(dftHeadXml + '\r\n'); rst.push(''); const regExp = new RegExp('<', 'gm'); for (let i = 0; i < sharedStrList.length; i++) { if (typeof sharedStrList[i] === 'string') { // 转换特殊字符,如 < , 则需要转义一下 sharedStrList[i] = sharedStrList[i].replace(regExp, '<'); if (sharedStrList[i].indexOf('|') >= 0) { // rst.push('' + sharedStrList[i].split('|').join('\r\n') + ''); // rst.push('' + sharedStrList[i].split('|').join('\n') + ''); pri_func_write(sharedStrList[i].split('|').join('\n')); } else { // rst.push('' + sharedStrList[i] + ''); pri_func_write(sharedStrList[i]); } // rst.push('' + sharedStrList[i].replace('|','\r\n') + ''); } else { // rst.push('' + sharedStrList[i] + ''); pri_func_write(sharedStrList[i]); } } rst.push(''); } 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 getAndSetProperSignatureArea(cell, cellControl) { const area = getProperSignatureArea(cell, cellControl, 0, 0, JV); if (!!cell.isStamp) { 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]; } return area; } 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 = getAndSetProperSignatureArea(cell, cellControl); private_set_cell_pos2(area, theXPos, theYPos); } for (let i = 0; i < theShtData[JV.PROP_SIGNATURE_DATE_CELLS].length; i++) { cell = theShtData[JV.PROP_SIGNATURE_DATE_CELLS][i]; private_set_cell_pos(cell, theXPos, theYPos); } 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 = getAndSetProperSignatureArea(cell, cellControl); 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(''); 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(''); } rst.push(''); rst.push(''); }; const private_setMergedCells = function() { let cnt = 0; rst.push(''); 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(''); 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] = ''; rst.push(''); }; const private_setSheetData = function() { // remark: 1 excel unit height = 0.3612 mm rst.push(''); 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(''); for (let j = 0; j < rows[i].items.length; j++) { const cellObj = rows[i].items[j]; if (cellObj.v === undefined) { rst.push(''); } else { rst.push(''); rst.push('' + cellObj.v + ''); rst.push(''); } } rst.push(''); } // 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(''); }; preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos); rst.push(dftHeadXml + '\r\n'); rst.push(''); const colStr = private_getCellIdxStr(xPos.length - 3); rst.push(''); if (isFirstSheet) { rst.push(''); } else { rst.push(''); } // rst.push(''); rst.push(''); rst.push(''); rst.push(''); private_setCols(); private_setSheetData(); private_setMergedCells(); rst.push(''); // rst.push(''); rst.push(''); let actPageSize = paperSize; if (sheetData && sheetData[JV.PROP_PAGE_SIZE]) { actPageSize = sheetData[JV.PROP_PAGE_SIZE]; } else { actPageSize = (pageData[JV.NODE_PAGE_INFO][JV.PROP_PAGE_SIZE] !== 'A3') ? paperSize : pageData[JV.NODE_PAGE_INFO][JV.PROP_PAGE_SIZE]; } // 这里的逻辑是:如果初始设置为A3的模板,表示为固化显示(未来可能会有其他的标记来处理,目前先这样) const paperSizeIdx = JV.PAGES_SIZE_STR.indexOf(actPageSize); 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(''); rst.push(''); // 插入分页符--------------------------- if (pageData.pageBreaks) { let pgBrks = []; if (sheetData !== null) { pgBrks = pageData.pageBreaks[pgBrkIdx]; } else { pgBrks = pageData.pageBreaks[0]; } if (pgBrks.length > 1) { rst.push(''); for (let pbi = 0; pbi < pgBrks.length; pbi++) { rst.push(''); } rst.push(''); } } // 插入分页符结束--------------------------- // 图形要放在最后,否则excel会报错(但WPS不会) if (hasSignature && (sheetData === null || signSheetIdxArr[sheetIdx])) { rst.push(''); } rst.push(''); return rst; } function writeWorkSheetRels(signSheetIdx) { const rst = []; rst.push(''); rst.push(''); rst.push(''); rst.push(''); 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 = getAndSetProperSignatureArea(signCell, cellControl); // 1. from // let colIdx = xPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_LEFT]); // let rowIdx = theYPos.indexOf(signCell[JV.PROP_AREA][JV.PROP_TOP]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出) let colIdx = xPos.indexOf(area[0]); let rowIdx = theYPos.indexOf(area[1]) + offsetRow; // 这里要考虑Row的偏移量(多页在同同一个sheet输出) // 因为 xPos, theYPos的首位是0,所以还需要减1 colIdx--; rowIdx--; rst.push(''); rst.push(''); rst.push('' + colIdx + ''); rst.push('0'); rst.push('' + rowIdx + ''); rst.push('0'); rst.push(''); // 2. to colIdx = xPos.indexOf(area[2]); rowIdx = theYPos.indexOf(area[3]) + offsetRow; // 因为 xPos, theYPos的首位是0,所以还需要减1 colIdx--; rowIdx--; rst.push(''); rst.push('' + colIdx + ''); rst.push('0'); rst.push('' + rowIdx + ''); rst.push('0'); rst.push(''); // 3. pic rst.push(''); // 3.1 rst.push(''); rst.push(''); rst.push(''); rst.push(''); // 3.2 rst.push(''); // 重点!!! const picIdx = subSignKeyArr.indexOf(signCell.signature_name) + 1; // 3.2.1 rst.push(''); rst.push(''); rst.push(''); // 参考excel本身,都是固定的uri rst.push(''); rst.push(''); rst.push(''); rst.push(''); // 3.2.2 rst.push(''); // 3.2.3 rst.push(''); rst.push(''); // 3.3 rst.push(''); // 3.3.1 rst.push(''); // 以下经过Excel及WPS的测试,这数据好像没什么意义,都设为0 rst.push(''); rst.push(''); rst.push(''); // 3.3.2 rst.push(''); // 3.3.3 rst.push(''); // 3.3.4 rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); rst.push(''); // 收尾 rst.push(''); rst.push(''); // 4. client Data rst.push(''); rst.push(''); }; preAnalyzePos(pageData, sheetData, xPos, yPos, yMultiPos); rst.push(''); rst.push(''); // 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(''); return rst; } function writeDrawingsRels(amt, startIdx) { const rst = []; rst.push(''); rst.push(''); for (let idx = 0; idx < amt; idx++) { rst.push(''); } rst.push(''); return rst; } function mergeProperties(orgObj, newObj) { const orgPropArr = []; const newPropArr = []; for (const p in orgObj) { orgPropArr.push(p); } for (const p in newObj) { newPropArr.push(p); } for (let i = 0; i < newPropArr.length; i++) { if (orgPropArr.indexOf(newPropArr[i]) < 0) { orgObj[newPropArr[i]] = newObj[newPropArr[i]]; } } } function _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; if (cell.orgArea) { cell.orgArea[JV.PROP_BOTTOM] += offsetY; cell.orgArea[JV.PROP_TOP] += offsetY; } if (cell.maxRect) { cell.maxRect[JV.IDX_TOP] += offsetY; cell.maxRect[JV.IDX_BOTTOM] += 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]; pageItem[JV.PROP_PAGE_SIZE] = pageDataArray[i][JV.NODE_PAGE_INFO][JV.PROP_PAGE_SIZE]; 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' }); }