nodejs操作excel文件实例，读取sheets, 设置cell颜色

本代码是我帮客户做的兼职的实例，涉及用node读取excel文件，遍历sheets，给单元格设置颜色等操作，希望对大家接活有所帮助。

gen.js

 
let dir="Z:\\武汉烟厂\\山东区域\\备档资料\\销区零售终端APP维护清单\\走访档案\\2024年6月\\200户走访档案\\修改\\";
let dir2="Z:\\liujuan\\new_gen4\\";
 
 
const fs = require('fs');
const path = require('path');
const ExcelJS = require('exceljs');
 
 
// get text in cell
function gv(cell) {
  let v=cell.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 noinput=document.getElementById('licensenum');
function check_one(list, itemno) 
{ 
  if(itemno>=list.length){ console.log(diffs); console.log(diffj); return; }
  console.log('checking '+itemno);
  let item=list[itemno]; let { no }=item; noinput.value=no; queryInfo(); 
  setTimeout(()=>{
    let diff={no, cols:[]}; let pushed_diff=false;
    let tbody=document.getElementById('detailTbodyId'); 
    if(!tbody || !tbody.children || tbody.children.length<1 || !tbody.children[0].children || tbody.children[0].children.length<4)
    { let msg='许可证不存在1: '+no; console.log(msg); diffs+=(msg+nl+nl); diffj.push(diff); check_one(list, itemno+1); return; }
    let tds=tbody.children[0].children; 
    if(tds[0].innerText.toLowerCase().trim()!=no) 
    { let msg='许可证不存在2: '+no; console.log(msg); diffs+=(msg+nl+nl); diffj.push(diff); check_one(list, itemno+1); return; }
    
    if(tds[1].innerText.toLowerCase().trim().replace(/[(]/g, '（').replace(/[)]/g, '）').replace(/\\s/g, '')!=item.company) 
    { if(!pushed_diff) { pushed_diff=true; diffj.push(diff); } diff.cols.push('company'); diffs+=(no+': '+nl+tds[1].innerText+nl+item.company+nl+nl);}
    if(tds[2].innerText.toLowerCase().trim().replace(/[(]/g, '（').replace(/[)]/g, '）').replace(/\\s/g, '')!=item.addr) 
    { if(!pushed_diff) { pushed_diff=true; diffj.push(diff); } diff.cols.push('addr'); diffs+=(no+': '+nl+tds[2].innerText+nl+item.addr+nl+nl);}
    if(!tds[3].innerText.toLowerCase().includes(item.date)) 
    { if(!pushed_diff) { pushed_diff=true; diffj.push(diff); } diff.cols.push('date');  diffs+=(no+': '+nl+tds[3].innerText+nl+item.date+nl+nl);}
    check_one(list, itemno+1);
  }, 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 v=cell.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 rows=sheet.rowCount, cols=sheet.columnCount;
  let found_first_row=false, no_col=0, addr_col=0, date_col=0, company_col=0, result=[];
  for(let row=1; row<=rows; row++)
  {
    console.log('row: ', row);
    let r=sheet.getRow(row);
    if(found_first_row)
    {
      let no_cell=r.getCell(no_col), no=gv(no_cell); if(!no){ for(let col=1; col<=cols; col++) r.getCell(col).style=style_black; continue; }
      let diff; if(diff=diffs.find(d=>d.no==no))
      {  
        let dcols=diff.cols;
        if(dcols.length==0) { for(let col=1; col<=cols; col++) r.getCell(col).style=style_black; no_cell.style=style_blue; continue;}
        for(let col=1; col<=cols; col++)
        {
          let cell=r.getCell(col); 
          if((company_col==col && dcols.includes('company'))||(addr_col==col && dcols.includes('addr'))||(date_col==col && dcols.includes('date'))) 
          { cell.style=style_red; }
          else cell.style=style_black;
        } 
      }
      else
      {  
        for(let col=1; col<=cols; col++) r.getCell(col).style=style_black;
      }
    }
    else
    {
      if(gv(r.getCell(1))=='区域' && gv(r.getCell(3))=='人员')
      { 
        // console.log('成功找到区域和人员列');
        for(let col=3; col<=cols; col++)
        {
          let cell=r.getCell(col); let v=gv(cell);
          if(v=='专卖许可证名称') company_col=col;     
          else if(v=='地址') addr_col=col;     
          else if(v=='许可证到期日期') date_col=col;     
          else if(v=='编号') no_col=col;
        }
        if(company_col==0 || addr_col==0 || date_col==0 || no_col==0) {  console.log('未找到指定的4列'); return;  }
        else { found_first_row=true; console.log('成功找到指定的4列'); }
      }
    }
  } 
}
 
 
async function ProcessFile(ExcelFilePath)
{
  const workbook = new ExcelJS.Workbook(); await workbook.xlsx.readFile(ExcelFilePath);
  let sheets=workbook.worksheets, sheetsCount=sheets.length;
  for(let sheet_index=0; sheet_index<sheetsCount; sheet_index++)
  {
    let sheet=sheets[sheet_index]; if(sheet.name==sheet_name) { await ProcessSheet(sheet); break; }
  }
  await workbook.xlsx.writeFile(ExcelFilePath);
} 
ProcessFile(dir+filename+'.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 filename=item.substring(0, item.length-5); const ExcelFilePath = path.join(folderPath, item);      
      const workbook = new ExcelJS.Workbook(); await workbook.xlsx.readFile(ExcelFilePath);
      let sheets=workbook.worksheets, sheetsCount=sheets.length;
      for(let sheet_index=0; sheet_index<sheetsCount; sheet_index++)
      {
        let sheet=sheets[sheet_index]; console.log(`sheet ${sheet.id}: ${sheet.name}`); //await ProcessSheet(sheet);
        let rows=sheet.rowCount, cols=sheet.columnCount; console.log(`${rows} rows, ${cols} cols`);
        let found_first_row=false, no_col=0, addr_col=0, date_col=0, company_col=0, result=[];
        for(let row=1; row<=rows; row++)
        {
          let r=sheet.getRow(row);
          if(found_first_row)
          {
            let no=gv(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 col=3; col<=cols; col++)
              {
                let cell=r.getCell(col); let v=gv(cell);
                if(v=='专卖许可证名称') company_col=col;     
                else if(v=='地址') addr_col=col;     
                else if(v=='许可证到期日期') date_col=col;     
                else if(v=='编号') no_col=col;               
                // console.log(`  [${row}, ${col}] ${v}`);
              }
              if(company_col==0 || addr_col==0 || date_col==0 || no_col==0) {  console.log('未找到指定的4列'); return;  }
              else { found_first_row=true; 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);