自学做网站多久,王悦做网站,小型手机网站建设企业,我的世界做皮肤的网站本代码是我帮客户做的兼职的实例#xff0c;涉及用node读取excel文件#xff0c;遍历sheets#xff0c;给单元格设置颜色等操作#xff0c;希望对大家接活有所帮助。
gen.js let dirZ:\\武汉烟厂\\山东区域\\备档资料\\销区零售终端APP维护清单\\走访档案\\2024年6月…本代码是我帮客户做的兼职的实例涉及用node读取excel文件遍历sheets给单元格设置颜色等操作希望对大家接活有所帮助。
gen.js let dirZ:\\武汉烟厂\\山东区域\\备档资料\\销区零售终端APP维护清单\\走访档案\\2024年6月\\200户走访档案\\修改\\;
let dir2Z:\\liujuan\\new_gen4\\;const fs require(fs);
const path require(path);
const ExcelJS require(exceljs);// get text in cell
function gv(cell) {let vcell.value; if(!v) return ;v (v.richText? v.richText.map(({ text }) text).join(): v.toString());return v.trim().toLowerCase().replace(/[(]/g, ).replace(/[)]/g, ).replace(/\s/g, );
}async function make_console(filename, sheetname, list)
{const FilePath path.join(dir2, console_${filename}_${sheetname}.js); const file_content
let sheet_name${sheetname};
let list${list};
let diffs, diffj[]; let nl\\r\\n;
let noinputdocument.getElementById(licensenum);
function check_one(list, itemno)
{ if(itemnolist.length){ console.log(diffs); console.log(diffj); return; }console.log(checking itemno);let itemlist[itemno]; let { no }item; noinput.valueno; queryInfo(); setTimeout((){let diff{no, cols:[]}; let pushed_difffalse;let tbodydocument.getElementById(detailTbodyId); if(!tbody || !tbody.children || tbody.children.length1 || !tbody.children[0].children || tbody.children[0].children.length4){ let msg许可证不存在1: no; console.log(msg); diffs(msgnlnl); diffj.push(diff); check_one(list, itemno1); return; }let tdstbody.children[0].children; if(tds[0].innerText.toLowerCase().trim()!no) { let msg许可证不存在2: no; console.log(msg); diffs(msgnlnl); diffj.push(diff); check_one(list, itemno1); return; }if(tds[1].innerText.toLowerCase().trim().replace(/[(]/g, ).replace(/[)]/g, ).replace(/\\s/g, )!item.company) { if(!pushed_diff) { pushed_difftrue; diffj.push(diff); } diff.cols.push(company); diffs(no: nltds[1].innerTextnlitem.companynlnl);}if(tds[2].innerText.toLowerCase().trim().replace(/[(]/g, ).replace(/[)]/g, ).replace(/\\s/g, )!item.addr) { if(!pushed_diff) { pushed_difftrue; diffj.push(diff); } diff.cols.push(addr); diffs(no: nltds[2].innerTextnlitem.addrnlnl);}if(!tds[3].innerText.toLowerCase().includes(item.date)) { if(!pushed_diff) { pushed_difftrue; diffj.push(diff); } diff.cols.push(date); diffs(no: nltds[3].innerTextnlitem.datenlnl);}check_one(list, itemno1);}, 2000);
}
check_one(list, 0);
;fs.writeFileSync(FilePath, file_content);
}async function make_mark(filename, sheetname)
{const FilePath path.join(dir2, mark_${filename}_${sheetname}.js); const file_content
let diffs
;
let filename${filename};
let sheet_name${sheetname};
let dir${ dir.replace(/\\/g, \\\\) };
const ExcelJS require(exceljs);
const fs require(fs);let style_red{font:{color:{ argb: FFFF0000}}};
let style_black{font:{color:{ argb: FF000000}}};
let style_blue{font:{color:{ argb: FF0000FF}}};
function gv(cell) {let vcell.value; if(!v) return ;v (v.richText? v.richText.map(({ text }) text).join(): v.toString());return v.trim().toLowerCase().replace(/[(]/g, ).replace(/[)]/g, ).replace(/\\s/g, );
}async function ProcessSheet(sheet)
{let rowssheet.rowCount, colssheet.columnCount;let found_first_rowfalse, no_col0, addr_col0, date_col0, company_col0, result[];for(let row1; rowrows; row){console.log(row: , row);let rsheet.getRow(row);if(found_first_row){let no_cellr.getCell(no_col), nogv(no_cell); if(!no){ for(let col1; colcols; col) r.getCell(col).stylestyle_black; continue; }let diff; if(diffdiffs.find(dd.nono)){ let dcolsdiff.cols;if(dcols.length0) { for(let col1; colcols; col) r.getCell(col).stylestyle_black; no_cell.stylestyle_blue; continue;}for(let col1; colcols; col){let cellr.getCell(col); if((company_colcol dcols.includes(company))||(addr_colcol dcols.includes(addr))||(date_colcol dcols.includes(date))) { cell.stylestyle_red; }else cell.stylestyle_black;} }else{ for(let col1; colcols; col) r.getCell(col).stylestyle_black;}}else{if(gv(r.getCell(1))区域 gv(r.getCell(3))人员){ // console.log(成功找到区域和人员列);for(let col3; colcols; col){let cellr.getCell(col); let vgv(cell);if(v专卖许可证名称) company_colcol; else if(v地址) addr_colcol; else if(v许可证到期日期) date_colcol; else if(v编号) no_colcol;}if(company_col0 || addr_col0 || date_col0 || no_col0) { console.log(未找到指定的4列); return; }else { found_first_rowtrue; console.log(成功找到指定的4列); }}}}
}async function ProcessFile(ExcelFilePath)
{const workbook new ExcelJS.Workbook(); await workbook.xlsx.readFile(ExcelFilePath);let sheetsworkbook.worksheets, sheetsCountsheets.length;for(let sheet_index0; sheet_indexsheetsCount; sheet_index){let sheetsheets[sheet_index]; if(sheet.namesheet_name) { await ProcessSheet(sheet); break; }}await workbook.xlsx.writeFile(ExcelFilePath);
}
ProcessFile(dirfilename.xlsx);
;fs.writeFileSync(FilePath, file_content);
}async function traverseFolder(folderPath) {const items fs.readdirSync(folderPath);for(let item of items){console.log(item);if(item.toLowerCase().includes(.xlsx) item.indexOf(~)0){const filenameitem.substring(0, item.length-5); const ExcelFilePath path.join(folderPath, item); const workbook new ExcelJS.Workbook(); await workbook.xlsx.readFile(ExcelFilePath);let sheetsworkbook.worksheets, sheetsCountsheets.length;for(let sheet_index0; sheet_indexsheetsCount; sheet_index){let sheetsheets[sheet_index]; console.log(sheet ${sheet.id}: ${sheet.name}); //await ProcessSheet(sheet);let rowssheet.rowCount, colssheet.columnCount; console.log(${rows} rows, ${cols} cols);let found_first_rowfalse, no_col0, addr_col0, date_col0, company_col0, result[];for(let row1; rowrows; row){let rsheet.getRow(row);if(found_first_row){let nogv(r.getCell(no_col)); if(!no) continue;result.push({no, addr: gv(r.getCell(addr_col)), company: gv(r.getCell(company_col)), date: gv(r.getCell(date_col)), });}else{if(gv(r.getCell(1))区域 gv(r.getCell(3))人员){ //console.log(成功找到区域和人员列);for(let col3; colcols; col){let cellr.getCell(col); let vgv(cell);if(v专卖许可证名称) company_colcol; else if(v地址) addr_colcol; else if(v许可证到期日期) date_colcol; else if(v编号) no_colcol; // console.log( [${row}, ${col}] ${v});}if(company_col0 || addr_col0 || date_col0 || no_col0) { console.log(未找到指定的4列); return; }else { found_first_rowtrue; console.log(成功找到指定的4列); }}}}//console.log(result.length, result[0]);try {await make_console(filename, sheet.name, JSON.stringify(result)); await make_mark(filename, sheet.name);fs.writeFileSync(${dir2}${filename}_${sheet.name}_差异.txt, );} catch (err) { console.error(err); }} }}
}traverseFolder(dir);
