'use strict'; /** * * * @author Zhong * @date 2019/2/21 * @version */ const TransferExcel = (function () { let curTransferType = 'gl'; const transferType = { // 公路 gl: 'gl', // 内蒙养护 neimengyh: 'neimengyh', // 建筑 jz: 'jz' }; const $transferModal = $('#transfer'); const $file = $('#transfer-file'); const $transfer = $('#transferConfirm'); const $exportSpread = $('#exportSpread'); let exportSpread = null; let transferData = []; //--以防需要可配置的情况 let fixedCount = 4; //固定列数（顺序号、项目、单位、代号） const beginCol = 0;//起始的列 //固定的这几列的列号映射是动态的，因为有一些子表，这些列进行了合并列的处理 let colMapping = { serialNo: 0, name: 1, unit: 2, code: 3, consumeAmt: 4, //消耗量开始的列 }; //--- function isUnDef(v) { return typeof v === 'undefined' || v === null; } //取消所有空格 function trimAll(v) { return typeof v === 'string' ? v.replace(/\s/g, '') : v; } //单元格是否有数据 function cellHasData(cell) { return cell && cell.value; } //行是否有数据 //@param {Object}rowData（行数据） {Array}deduct(排除的列) function rowHasData(rowData, deduct = null) { for (let col in rowData) { if (deduct && deduct.includes(col)) { continue; } let cell = rowData[col]; if (cell && cell.value) { return true; } } return false; } //行为列头行 //第一列为数值，且后面其他列有数据（不单纯认为是第二列，因为怕有合并序号列） function headerRow(rowData) { return rowData[0] && rowData[0].value && /^\d+$/.test(rowData[0].value) && rowHasData(rowData, ['0']); } //去除定额子表数组中的空行(有的表格是在 单位：xx 下面还跟着一些垃圾数据，导致subRation的range范围判定变大了，但是四列后面的数据是空的) function simplifyRationTable(arr) { let target = []; for (let subArr of arr) { let emptyRow = subArr.every(function (ele) { return ele === null; }); if (emptyRow) { continue; } target.push(subArr); } return target; } //将二维数组转换成每个元素都有值的二维数据(去没值元素，因为前四列有可能合并列) function simplifyGljTable(arr) { let target = []; for (let subArr of arr) { let subTarget = []; for (let ele of subArr) { if (ele !== null) { subTarget.push(ele); } } target.push(subTarget); } return target; } //获取表头定额名称数据(最后一行为定额末位编码) //合并的单元格处理：为了更好的让定额获得对应的定额名称，将合并单元格做填值处理, eg: [a]['合并'] = [a][a] //@param {Object}dataTable {Object}range(表头的范围，row, col, rowCount, colCount) {Array}spans(spread解析Excel后的合并数组) //@return {Array} function getSubRationTable(dataTable, range, spans) { let subTable = []; for (let i = 0; i < range.rowCount; i++) { subTable.push(Array(range.colCount).fill(null)); } //获取合并的单元格填充数据 let fillArr = []; for (let i = 0; i < range.rowCount; i++) { let row = range.row + i; if (!dataTable[row]) { continue; } for (let j = 0; j < range.colCount; j++) { let col = range.col + j; let cell = dataTable[row][col]; //只有有值的单元格，判断合并才有意义，有值且合并列的单元格，每列的值认为都是一样的 if (cellHasData(cell)) { //是否合并了单元格 let span = spans.find(function (data) { return data.row === row && data.col === col; }); //这个fillData给subData填值用 let fillData = {value: trimAll(cell.value), range: {row: i, col: j}}; if (span) { fillData.range.rowCount = span.rowCount; fillData.range.colCount = span.colCount; } else { fillData.range.rowCount = 1; fillData.range.colCount = 1; } fillArr.push(fillData); } } } //将数据填充到subData中 for (let fillData of fillArr) { //合并行不需要向下填值(否则会重复) let row = fillData.range.row; //合并列需要向右填值 for (let j = 0; j < fillData.range.colCount; j++) { let col = fillData.range.col + j; subTable[row][col] = trimAll(fillData.value); } } return simplifyRationTable(subTable); } //获取工料机子表数据(序号至最末消耗量) //@param {Object}dataTable {Object}range(工料机子表的范围，row, col, rowCount, colCount) //@return {Array} function getSubGljTable(dataTable, range) { let gljTable = []; for (let i = 0; i < range.rowCount; i++) { gljTable.push(Array(range.colCount).fill(null)); } // 消耗量列 let consumeAmtCol = colMapping.consumeAmt; for (let i = 0; i < range.rowCount; i++) { let row = range.row + i; if (!dataTable[row]) { continue; } for (let j = 0; j < range.colCount; j++) { let col = range.col + j; let cell = dataTable[row][col]; if (cellHasData(cell)) { gljTable[i][j] = cell.value; } else if (col >= consumeAmtCol) { // 消耗量单位格，没值则认为是0 gljTable[i][j] = 0; } } } return gljTable; } /* * 从原本的excel中提取数据 * 需要的数据结构：eg: [{code: '1-1-1', name: '伐树', unit: '表列单位', subTable: [{subGlj: [], subRation: []}]}] * subTable为定额数据对应的子表数据，其中有定额名称子表、工料机子表 * */ function extractDataFromExcel(sheetData) { // 识别定额行的表达式 //[\-,—,一] 这里是因为pdf转出来的excel（需要转换的excel）会吧"-"显示成各种奇怪的横杆 //第一数值限制在3位以内，防止有: 2019-05-01等日期干扰 // x-x-x xx let rationCodeReg = /^(\d{1,3}[\-,_,—,一]{1}\d+[\-,_,—,一]{1}\d+)(\w{0,}[\u4e00-\u9fa5]{1,})/; let dataTable = sheetData.data.dataTable, spans = sheetData.spans; //行数据是定额行 eg: 1-1-1 人工挖土方 //@param {Number}row //@return {Object || Null} eg: {code: '1-1-1', name: '人工挖土方'} function rationRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); let match = rationCodeReg.exec(v); if (match && match.length === 3 && match[0] && match[1] && match[2]) { return {code: match[1].replace(/[_,—,一]/g, '-'), name: match[2]}; } return null; } //单位数据行 eg: 单位：表列单位 //@return {String || Null} eg: '表列单位' function unitRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); let reg = /单位[\:, ：]([\w+, \u4e00-\u9fa5]{0,})/; let match = reg.exec(v); if (match && match.length === 2 && match[0] && match[1]) { return match[1]; } return null; } //行数据是人材机数据行 //表头后，某行第一列为数值，第二列有值，则该行为紧接着表头的人材机数据行 //@return {Boolean} function rowIsGlj(rowData) { let numberCell = rowData[colMapping.serialNo], valueCell = rowData[colMapping.name]; return numberCell && numberCell.value && /^\d+$/.test(numberCell.value) && valueCell && Boolean(valueCell.value); } //连续有数据的列数(每个子表格中工料机的数据列数) //由于序号、项目、单位、代号可能存在合并列，因此从消耗量列(消耗量列不会合并列，消耗量对应的列号已在getColMapping获取)开始统计 function getDataCount(row) { let consumeAmtCol = colMapping.consumeAmt; for (let col = consumeAmtCol; col < sheetData.columnCount; col++) { let cell = dataTable[row][col]; if (!cell || !cell.value) { return col; } } return sheetData.columnCount; } //获取前固定四列的动态列映射（解决这几列可能有合并列的问题） function getColMapping(rowData, colCount) { let needFields = ['serialNo', 'name', 'unit', 'code', 'consumeAmt']; function getUndefinedField(obj) { for (let field of needFields) { if (!obj.hasOwnProperty(field)) { return field; } } return null; } //假定序号列为第一列 let mapping = {serialNo: 0}; for (let i = 1; i < colCount; i++) { let col = beginCol + i, cell = rowData[col]; //还没设置的字段（必须要有的是serialNo, name, unit, code, consumeAmt） if (cell && cell.value) { let field = getUndefinedField(mapping); if (field) { mapping[field] = col; } if (typeof mapping.consumeAmt !== 'undefined') { return mapping; } } } return null; } //获取表格的子表范围（定额名称表头范围、工料机数据范围） //遇到单位:xx行rowA后，获取紧跟其后的表格，该表格最少要有一行工料机数据行rowB，表头范围则为rowA至rowB //@param {Number}beginRow function getTableRange(beginRow) { let hasTHead = false, hasTable = false, hasMapping = false; //是否获取过固定列映射 let range = { subRation: {}, subGlj: {}, }; for (let row = beginRow; row < sheetData.rowCount; row++) { if (!dataTable[row]) { continue; } //第一个有数据的行，为表头第一行 if (rowHasData(dataTable[row]) && !hasTHead) { hasTHead = true; range.subRation.row = row; } //获取当前子表的固定列映射 if (hasTHead && !hasTable && !hasMapping && headerRow(dataTable[row])) { hasMapping = true; colMapping = getColMapping(dataTable[row], sheetData.columnCount); if (!colMapping) { return null; } } //第一条工料机数据行 if (hasTHead && !hasTable && colMapping && rowIsGlj(dataTable[row])) { hasTable = true; range.subGlj.row = row; range.subGlj.col = 0; range.subRation.col = colMapping.consumeAmt; range.subRation.rowCount = range.subGlj.row - range.subRation.row; range.subGlj.colCount = getDataCount(row); range.subRation.colCount = range.subGlj.colCount - colMapping.consumeAmt; } if (hasTable && !rowIsGlj(dataTable[row])) { range.subGlj.rowCount = row - range.subGlj.row; return range; } if (hasTable && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) { range.subGlj.rowCount = row - range.subGlj.row + 1; return range; } } return null; } //分析整个表 let extractData = []; //定额行后必须要跟着单位行 let hasUnit = false; for (let row = 0; row < sheetData.rowCount; row++) { if (!dataTable[row] || !rowHasData(dataTable[row])) { continue; } let rationData = rationRow(row); if (rationData) { if (!hasUnit) { extractData.pop(); } hasUnit = false; //转换数据数组的每个元素，为定额基本数据：不完整的code、不完整的name、unit、和子表门构成 //subTable: [{subRation: [], subGlj: []}]，subRation为每个子表表头数据（排除掉了顺序号至代号）， //subGlj为每个子表工料机数据 let basicData = { code: rationData.code, name: rationData.name, unit: null, subTable: [] }; extractData.push(basicData); } let unitData = unitRow(row); if (unitData) { hasUnit = true; let thisBasicData = extractData[extractData.length - 1]; if (thisBasicData) { if (!thisBasicData.unit) { thisBasicData.unit = unitData; } //获取表格数据 let range = getTableRange(row + 1); if (range) { let subRationTable = getSubRationTable(dataTable, range.subRation, spans), subGljTable = getSubGljTable(dataTable, range.subGlj); thisBasicData.subTable.push({subRation: subRationTable, subGlj: subGljTable}); //跳过其中的行 row = range.subGlj.row + range.subGlj.rowCount - 1; } } } } return extractData; } /* * 转换数据，将提取出来的数据转换成另外一种数据结构，便于转化Excel的结构 * 需要的数据结构: eg: [{code: '1-1-1-1', name: '伐树xxx', unit: '表列单位', gljList: [{code,name,unit,comsumeAmt}]}] * */ function transferDataFromExtract(extractData) { console.log(extractData); let existCodes = []; //从一个提取的数据(1定额数据及其子表数据)中获取一份转换数据 function transfer(source) { //以完整定额编码为属性，因为1个定额可能会有多张子表，且完整定额编码相同，子工料机不同，方便直接添加后续的工料机 let temp = {}, target = []; let basicCode = source.code, basicName = source.name, basicUnit = source.unit; if (existCodes.includes(basicCode)) { return []; } existCodes.push(basicCode); //处理消耗量，可能有(3.5) 和 - 的情况, 处理：(3.5) => 3.5 - => 0 function handleConsumeAmt(consumeAmt) { if (typeof consumeAmt === 'string') { consumeAmt = trimAll(consumeAmt); consumeAmt = consumeAmt.replace(/[\-,_,—,一,\(,\),（,）]/g, ''); if (!consumeAmt) { return 0; } } return consumeAmt; } //从工料机子表中获取工料机数据, index为定额编码对应的下标索引 function getGljList(gljTable, index) { let gljList = []; //获取的工料机对应列 let gljColMapping = { name: 1, unit: 2, code: 3, consumeAmtCol: fixedCount + index }; for (let rowData of gljTable) { //工料机数据必须要有名称、单位、编码 if (!rowData[gljColMapping.name] || !rowData[gljColMapping.unit] || !rowData[gljColMapping.code]) { continue; } let consumeAmt = isUnDef(rowData[gljColMapping.consumeAmtCol]) ? 0 : handleConsumeAmt(rowData[gljColMapping.consumeAmtCol]); gljList.push({ name: rowData[gljColMapping.name], unit: rowData[gljColMapping.unit], code: rowData[gljColMapping.code], consumeAmt: consumeAmt, }); } return gljList; } //拼接定额工料机数据 for (let table of source.subTable) { let rationTable = table.subRation; if (!rationTable || rationTable.length === 0) { continue; } let lastRationCodes = rationTable.pop(); //定额子表，最后一行是末位定额编码 for (let i = 0; i < lastRationCodes.length; i++) { let lastCode = lastRationCodes[i]; //拼接定额编码 let compleCode = `${basicCode}-${lastCode}`, gljList = getGljList(table.subGlj, i); if (!temp[compleCode]) { //该定额不存在 temp[compleCode] = { code: compleCode, unit: basicUnit, name: basicName, gljList: gljList }; } else { //该定额已存在，则追加工料机 temp[compleCode].gljList = temp[compleCode].gljList.concat(gljList); } //拼接定额名称 for (let rationNameRow of rationTable) { if (rationNameRow[i]) { temp[compleCode].name += ` ${rationNameRow[i]}`; } } } } //将temp对象转换为数据 for (let code in temp) { target.push(temp[code]); } return target; } let transferData = []; for (let data of extractData) { let unitTargetData = transfer(data); transferData = transferData.concat(unitTargetData); } console.log(transferData); return transferData; } // 提取内蒙养护数据 function extractDataFromNMYH(sheetData) { // 识别定额行的表达式 //固定列数（顺序号、项目、单位、代号、单价） fixedCount = 5; // x-x xx let rationCodeReg = /^(\d{1,3}[\-,_,—,一]{1}\d+)(\w{0,}[\u4e00-\u9fa5]{1,})/; let dataTable = sheetData.data.dataTable, spans = sheetData.spans; //行数据是定额行 eg: 1-1-1 人工挖土方 //@param {Number}row //@return {Object || Null} eg: {code: '1-1-1', name: '人工挖土方'} function rationRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); let match = rationCodeReg.exec(v); if (match && match.length === 3 && match[0] && match[1] && match[2]) { return {code: match[1].replace(/[_,—,一]/g, '-'), name: match[2]}; } return null; } //单位数据行 eg: 单位：表列单位 //@return {String || Null} eg: '表列单位' function unitRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); let reg = /单位[\:, ：]([\w+, \u4e00-\u9fa5]{0,})/; let match = reg.exec(v); if (match && match.length === 2 && match[0] && match[1]) { return match[1]; } return null; } //行数据是人材机数据行 //表头后，某行第一列为数值，第二列有值，则该行为紧接着表头的人材机数据行 //@return {Boolean} function rowIsGlj(rowData) { let numberCell = rowData[colMapping.serialNo], valueCell = rowData[colMapping.name]; return numberCell && numberCell.value && /^\d+$/.test(numberCell.value) && valueCell && Boolean(valueCell.value); } //获取前固定四列的动态列映射（解决这几列可能有合并列的问题） function getColMapping(rowData, colCount) { let needFields = ['serialNo', 'name', 'unit', 'code']; function getUndefinedField(obj) { for (let field of needFields) { if (!obj.hasOwnProperty(field)) { return field; } } return null; } //假定序号列为第一列 let mapping = {serialNo: 0}; for (let i = 1; i < colCount; i++) { let col = beginCol + i, cell = rowData[col]; if (cell && cell.value) { let field = getUndefinedField(mapping); if (field) { mapping[field] = col; } if (typeof mapping.code !== 'undefined') { return mapping; } } } return null; } // 获取定额剩余编号的列号信息 function getRestCodeInfo(rowData) { if (!rowData) { return; } let numReg = /^\d+$/; let col, colCount = 0; Object.values(rowData).forEach((data, index) => { let v = data.value || ''; if (numReg.test(v)) { if (!colCount) { col = index; } colCount++; } }); return colCount ? {col, colCount} : null; } //获取表格的子表范围（定额名称表头范围、工料机数据范围） //遇到单位:xx行rowA后，获取紧跟其后的表格，该表格最少要有一行工料机数据行rowB，表头范围则为rowA至rowB //@param {Number}beginRow function getTableRange(beginRow) { let hasTHead = false, hasTable = false, hasMapping = false; //是否获取过固定列映射 let range = { subRation: {}, subGlj: {}, }; for (let row = beginRow; row < sheetData.rowCount; row++) { if (!dataTable[row]) { continue; } //第一个有数据的行，为表头第一行 if (rowHasData(dataTable[row]) && !hasTHead) { hasTHead = true; range.subRation.row = row; } //获取当前子表的固定列映射 if (hasTHead && !hasTable && !hasMapping && headerRow(dataTable[row])) { hasMapping = true; colMapping = getColMapping(dataTable[row], sheetData.columnCount); if (!colMapping) { return null; } // 获取定额剩余编码所处的col、和colCount let restCodeInfo = getRestCodeInfo(dataTable[row - 1]); if (!restCodeInfo) { return null; } colMapping.consumeAmt = restCodeInfo.col; range.subRation.col = restCodeInfo.col; range.subRation.colCount = restCodeInfo.colCount; range.subGlj.col = 0; range.subGlj.colCount = restCodeInfo.col + restCodeInfo.colCount; } //第一条工料机数据行 if (hasTHead && !hasTable && colMapping && rowIsGlj(dataTable[row])) { hasTable = true; range.subGlj.row = row; range.subRation.rowCount = range.subGlj.row - range.subRation.row; } if (hasTable && !rowIsGlj(dataTable[row])) { range.subGlj.rowCount = row - range.subGlj.row; return range; } if (hasTable && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) { range.subGlj.rowCount = row - range.subGlj.row + 1; return range; } } return null; } //分析整个表 let extractData = []; //定额行后必须要跟着单位行 let hasUnit = false; for (let row = 0; row < sheetData.rowCount; row++) { if (!dataTable[row] || !rowHasData(dataTable[row])) { continue; } let rationData = rationRow(row); if (rationData) { if (!hasUnit) { extractData.pop(); } hasUnit = false; //转换数据数组的每个元素，为定额基本数据：不完整的code、不完整的name、unit、和子表门构成 //subTable: [{subRation: [], subGlj: []}]，subRation为每个子表表头数据（排除掉了顺序号至代号）， //subGlj为每个子表工料机数据 let basicData = { code: rationData.code, name: rationData.name, unit: null, subTable: [] }; extractData.push(basicData); } let unitData = unitRow(row); if (unitData) { hasUnit = true; let thisBasicData = extractData[extractData.length - 1]; if (thisBasicData) { if (!thisBasicData.unit) { thisBasicData.unit = unitData; } //获取表格数据 let range = getTableRange(row + 1); if (range) { let subRationTable = getSubRationTable(dataTable, range.subRation, spans), subGljTable = getSubGljTable(dataTable, range.subGlj); thisBasicData.subTable.push({subRation: subRationTable, subGlj: subGljTable, colMapping: colMapping}); //跳过其中的行 row = range.subGlj.row + range.subGlj.rowCount - 1; } } } } return extractData; } // 转换内蒙养护数据 function transferDataFromNMYH(extractData) { console.log(extractData); let existCodes = []; //从一个提取的数据(1定额数据及其子表数据)中获取一份转换数据 function transfer(source) { //以完整定额编码为属性，因为1个定额可能会有多张子表，且完整定额编码相同，子工料机不同，方便直接添加后续的工料机 let temp = {}, target = []; let basicCode = source.code, basicName = source.name, basicUnit = source.unit; if (existCodes.includes(basicCode)) { return []; } existCodes.push(basicCode); //处理消耗量，可能有(3.5) 和 - 的情况, 处理：(3.5) => 3.5 - => 0 function handleConsumeAmt(consumeAmt) { if (typeof consumeAmt === 'string') { consumeAmt = trimAll(consumeAmt); consumeAmt = consumeAmt.replace(/[\-,_,—,一,\(,\),（,）]/g, ''); if (!consumeAmt) { return 0; } } return consumeAmt; } //从工料机子表中获取工料机数据, index为定额编码对应的下标索引 function getGljList(gljColMapping, gljTable, index) { let gljList = []; let consumeAmtCol = gljColMapping.consumeAmt + index; for (let rowData of gljTable) { //工料机数据必须要有名称、单位、编码 if (!rowData[gljColMapping.name] || !rowData[gljColMapping.unit] || !rowData[gljColMapping.code]) { continue; } let consumeAmt = isUnDef(rowData[consumeAmtCol]) ? 0 : handleConsumeAmt(rowData[consumeAmtCol]); gljList.push({ name: rowData[gljColMapping.name], unit: rowData[gljColMapping.unit], code: rowData[gljColMapping.code], consumeAmt: consumeAmt, }); } return gljList; } //拼接定额工料机数据 for (let table of source.subTable) { let rationTable = table.subRation; if (!rationTable || rationTable.length === 0) { continue; } let lastRationCodes = rationTable.pop(); //定额子表，最后一行是末位定额编码 for (let i = 0; i < lastRationCodes.length; i++) { let lastCode = lastRationCodes[i]; //拼接定额编码 let compleCode = `${basicCode}-${lastCode}`, gljList = getGljList(table.colMapping, table.subGlj, i); if (!temp[compleCode]) { //该定额不存在 temp[compleCode] = { code: compleCode, unit: basicUnit, name: basicName, gljList: gljList }; } else { //该定额已存在，则追加工料机 temp[compleCode].gljList = temp[compleCode].gljList.concat(gljList); } //拼接定额名称 for (let rationNameRow of rationTable) { if (rationNameRow[i]) { temp[compleCode].name += ` ${rationNameRow[i]}`; } } } } //将temp对象转换为数据 for (let code in temp) { target.push(temp[code]); } return target; } let transferData = []; for (let data of extractData) { let unitTargetData = transfer(data); transferData = transferData.concat(unitTargetData); } console.log(transferData); return transferData; } /* * 从原本的建筑Excel中提取数据 * */ function extractDataFromJZ(sheetData) { let dataTable = sheetData.data.dataTable, spans = sheetData.spans; //定额编号为定额行 function rationRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); return cell.value === '定额编号'; } function endRationRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); return cell.value.includes('基价'); } //表格中"分类"行为人材机子表起始行 function classRow(row) { let cell = dataTable[row][beginCol]; if (!cell || !cell.value || typeof cell.value !== 'string') { return false; } let v = trimAll(cell.value); return cell.value === '分类'; } //人材机行:编码、名称、单位不为空 function gljRow(rowData, colMapping) { let code = rowData[colMapping.code] ? rowData[colMapping.code].value : null, name = rowData[colMapping.name] ? rowData[colMapping.name].value : null, unit = rowData[colMapping.unit] ? rowData[colMapping.unit].value : null; return code && name && unit; } //获取"分类"行列对应：编码、名称、单位、单价的列 function getColMapping(rowData, colCount) { function validMapping(obj) { let needFields = ['code', 'name', 'unit', 'price']; for (let field of needFields) { if (!field in obj) { return false; } } return true; } let mapping = {}; for (let i = 0; i < colCount; i++) { let col = beginCol + i, cell = rowData[col]; if (cell && cell.value) { let cellValue = trimAll(cell.value); if (cellValue === '编码') { mapping.code = col; } else if (cellValue === '名称') { mapping.name = col; } else if (cellValue === '单位') { mapping.unit = col; } else if ( cellValue.includes('单价')) { mapping.price = col; return validMapping(mapping) ? mapping : null; } } } return null; } function getTableRange(beginRow) { let range = { subRation: {row: beginRow}, subManage: {},//管理费 subGlj: {}, }; //获取定额子表列数，从定额编号后面的有数据的列算起 let beginCol = null, //第一个有效数据列 lastCol = null; //最后一个有效数据列 for (let col = 0; col < sheetData.columnCount; col++) { let cell = dataTable[beginRow][col]; if (cell && cell.value && trimAll(cell.value) !== '定额编号') { if (!beginCol) { beginCol = col; } lastCol = col; } } if (!beginCol || !lastCol) { return null } range.subRation.col = beginCol; range.subRation.colCount = lastCol - beginCol + 1; let enterRation = false, enterGlj = false; for (let row = beginRow; row < sheetData.rowCount; row++) { if (!dataTable[row] || !rowHasData(dataTable[row])) { continue; } if (endRationRow(row)) { enterRation = true; range.subRation.rowCount = row - range.subRation.row; } if (enterRation && classRow(row)) { enterGlj = true; colMapping = getColMapping(dataTable[row], sheetData.columnCount); if (!colMapping) { return null; } //管理费（人材机）相关 range.subManage.row = row - 1; range.subManage.rowCount = 1; range.subManage.col = beginCol; range.subManage.colCount = range.subRation.colCount; //人材机相关 range.subGlj.row = row + 1; range.subGlj.col = colMapping.code; range.subGlj.colCount = lastCol - range.subGlj.col + 1; } if (enterGlj && !gljRow(dataTable[row], colMapping)) { range.subGlj.rowCount = row - range.subGlj.row; return range; } if (enterGlj && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) { range.subGlj.rowCount = row - range.subGlj.row + 1; return range; } } return null; } //管理费table并入工料机table function mergeManage(gljTable, manageTable) { for (let rowData of manageTable) { rowData.unshift(1); rowData.unshift('元'); rowData.unshift('管理费'); rowData.unshift('GLF'); } return gljTable.concat(manageTable); } //分析整个表 let extractData = []; for (let row = 0; row < sheetData.rowCount; row++) { if (!dataTable[row] || !rowHasData(dataTable[row])) { continue; } if (rationRow(row)) { //获取表格数据 let range = getTableRange(row); if (range) { let subRationTable = validateRationTable(getSubRationTable(dataTable, range.subRation, spans)), subGljTable = getSubGljTable(dataTable, range.subGlj), subManageTable = getSubGljTable(dataTable, range.subManage); subGljTable = mergeManage(subGljTable, subManageTable); //将管理费合并进人材机 extractData.push({subRation: subRationTable, subGlj: subGljTable}); //跳过其中的行 row = range.subGlj.row + range.subGlj.rowCount - 1; } } } function validateRationTable(source) { let target = []; let codeLine = source[0]; for (let subSource of source) { let subTarget = []; for (let i = 0; i < subSource.length; i++) { if (!codeLine[i]) { continue; } subTarget.push(subSource[i]); } target.push(subTarget); } return target; } return extractData; } /* * 转换建筑数据 * */ function transferDataFromJZ(extractData) { function handleNumber(v) { if (typeof v === 'string') { v = trimAll(v); v = v.replace(/[\-,_,—,一,\(,\),（,）,[,]/g, ''); if (!v) { return 0; } } return Math.abs(parseFloat(v)); } //从工料机子表中获取工料机数据, index为定额编码对应的下标索引 function getGljList(gljTable, index) { let gljList = []; //获取的工料机对应列 let gljColMapping = { code: 0, name: 1, unit: 2, price: 3, consumeAmtCol: fixedCount + index }; for (let rowData of gljTable) { //工料机数据必须要有名称、单位、编码、单价 if (!rowData[gljColMapping.name] || !rowData[gljColMapping.unit] || !rowData[gljColMapping.code] || !rowData[gljColMapping.price]) { continue; } let consumeAmt = isUnDef(rowData[gljColMapping.consumeAmtCol]) ? 0 : handleNumber(rowData[gljColMapping.consumeAmtCol]), price = isUnDef(rowData[gljColMapping.price]) ? 0 : handleNumber(rowData[gljColMapping.price]); gljList.push({ name: rowData[gljColMapping.name], unit: rowData[gljColMapping.unit], code: rowData[gljColMapping.code], price: price, consumeAmt: consumeAmt, }); } return gljList; } let temp = {}, target = []; for (let source of extractData) { let rationTable = source.subRation, gljTable = source.subGlj; if (!rationTable || rationTable.length === 0) { continue; } //拼接定额工料机数据 let rationCodes = rationTable.shift(); //定额编码 for (let i = 0; i < rationCodes.length; i++) { let code = rationCodes[i].replace(/[_,—,一]/g, '-'); let gljList = getGljList(gljTable, i); if (!temp[code]) { //该定额不存在 temp[code] = { code: code, unit: '', name: '', gljList: gljList }; } else { //该定额已存在，则追加工料机 temp[code].gljList = temp[code].gljList.concat(gljList); } //拼接定额名称 for (let rationNameRow of rationTable) { if (rationNameRow[i]) { temp[code].name += ` ${rationNameRow[i]}`; } } } } for (let code in temp) { target.push(temp[code]); } return target; } //导入Excel function exportToExcel(transferData, fileName) { $.bootstrapLoading.start(); setTimeout(function () { if (exportSpread) { exportSpread.destroy(); } exportSpread = new GC.Spread.Sheets.Workbook($exportSpread[0], {sheetCount: 1}); let sheet = exportSpread.getSheet(0); sheet.suspendPaint(); sheet.suspendEvent(); //往表格填值 let curRow = 1, fillCol = { code: 1, name: 2, unit: 3, consumeAmt: 4, price: 5 }; function getRowCount() { let count = 0; for (let data of transferData) { count += 1 + data.gljList.length; } return count; } sheet.setRowCount(getRowCount()+1); //test /* let temp = {}; for (let testData of transferData) { if (temp[testData.code]) { temp[testData.code]++; } else { temp[testData.code] = 1; } } let consoleData = []; for (let i in temp) { if (temp[i] > 1) { consoleData.push({code: i, count: temp[i]}); } } console.log(consoleData);*/ //test sheet.setValue(0, fillCol.code, '编码'); sheet.setValue(0, fillCol.name, '名称'); sheet.setValue(0, fillCol.unit, '单位'); sheet.setValue(0, fillCol.consumeAmt, '消耗量'); if (curTransferType === transferType.jz) { sheet.setValue(0, fillCol.price, '单价'); } for (let data of transferData) { sheet.setValue(curRow, 0, '定额'); sheet.setValue(curRow, fillCol.code, data.code); sheet.setValue(curRow, fillCol.name, data.name); sheet.setValue(curRow, fillCol.unit, data.unit); curRow++; for (let glj of data.gljList) { sheet.setValue(curRow, fillCol.code, glj.code); sheet.setValue(curRow, fillCol.name, glj.name); sheet.setValue(curRow, fillCol.unit, glj.unit); sheet.setValue(curRow, fillCol.consumeAmt, glj.consumeAmt); if (curTransferType === transferType.jz) { sheet.setValue(curRow, fillCol.price, glj.price); } curRow++; } } sheet.resumeEvent(); sheet.resumePaint(); let json = exportSpread.toJSON(); let excelIo = new GC.Spread.Excel.IO(); excelIo.save(json, function(blob) { saveAs(blob, fileName); $.bootstrapLoading.end(); $transferModal.modal('hide'); }, function(e) { $.bootstrapLoading.end(); $transferModal.modal('hide'); console.log(e); }); }, 200); } function eventListener() { $('.transfer-excel').click(function () { curTransferType = $(this).data('exceltype'); console.log(curTransferType); }); $transferModal.on('hidden.bs.modal', function () { $file.val(''); transferData = []; }); //导入excel,提取并转换定额数据 $file.change(function () { $transfer.addClass('disabled'); let file = $(this)[0]; $.bootstrapLoading.start(); $('#loadingPage').css('z-index', '2000'); setTimeout(function () { let excelFile = file.files[0]; if(excelFile) { let xlsReg = /xls$/g; if(excelFile.name && xlsReg.test(excelFile.name)){ alert('请选择xlsx文件'); $(this).val(''); $transfer.removeClass('disabled'); $.bootstrapLoading.end(); return; } //前端解析excel数据 let excelIo = new GC.Spread.Excel.IO(); let sDate = +new Date(); excelIo.open(excelFile, function (json) { console.log(json); if (curTransferType === transferType.gl) { let extractData = extractDataFromExcel(json.sheets.Sheet1); transferData = transferDataFromExtract(extractData); } else if (curTransferType === transferType.neimengyh) { let extractData = extractDataFromNMYH(json.sheets.Sheet1); transferData = transferDataFromNMYH(extractData); } else { let extractData = extractDataFromJZ(json.sheets.Sheet1); transferData = transferDataFromJZ(extractData); console.log(transferData); } console.log(`解析Excel文件时间：${+new Date() - sDate}`); $.bootstrapLoading.end(); $transfer.removeClass('disabled'); }, function (e) { $.bootstrapLoading.end(); $transfer.removeClass('disabled'); alert(e.errorMessage); }); } }, 200); }); //确认转换，导出转换的Excel $transfer.click(function () { if ($(this).hasClass('disabled')) { return; } if (!transferData || transferData.length === 0) { //没有转换数据 alert('没有转换数据'); return; } let fileName = '转换数据.xlsx'; if ($file[0].files && $file[0].files[0] && $file[0].files[0].name) { fileName = '转换数据' + $file[0].files[0].name; } exportToExcel(transferData, fileName); }); } return {eventListener} })(); $(document).ready(function () { TransferExcel.eventListener(); });