82855 AI Apple Watch CentOS Eclipse H700 iCal iOS iPad iPhone iphone4 iTunes Java Javascript linux Mac MBP ML MySQL Oracle Parallels Desktop RAID redmine Rocky Linux RockyLinux screen Snow Leopard SQL SVN Thinkpad VMware VR Windows Windows 7 Windows 11 Word X40 东航 字体 导航 数据库 朗逸 签证 达美 闹钟

用 Javascript 批量查找 Excel 中的文字


比 Excel 的查找有下述优点:

  • 批量查找整个目录
  • 可以找到图形和文本框中的文字
  • 采用正则表达式查找,可以实现复杂的查找条件

缺点是比较慢。

// 设定值:分别是要查找的目录和查找条件
var searchPath = "D:/temp";
var searchPattern = /事業/i;

// 正则表达式参考:
//http://msdn.microsoft.com/en-us/library/1400241x(VS.85).aspx
//http://msdn.microsoft.com/zh-cn/library/28hw3sce(VS.80).aspx

var msoAutoShape = 1
var msoGroup = 6

var xlApp = WScript.CreateObject("Excel.Application");
xlApp.Visible = true;
xlApp.Interactive = true;
var logBook = xlApp.Workbooks.Add(); // For Output
var logSheet = logBook.Sheets(1);
var logLine = 2;
var outSheet = logBook.Sheets(2);
var outLine = 2;

var allFiles = new Array();
var fso = new ActiveXObject("Scripting.FileSystemObject");

GetFileList(searchPath, allFiles);

var i;
for (i = 0; i < allFiles.length; i++) {
  logSheet.Cells(logLine + i + 1, "B").Value = allFiles[i].name;
  logSheet.Cells(logLine + i + 1, "B").NoteText (allFiles[i].path);
}

for (i = 0; i < allFiles.length; i++) {
  xlApp.StatusBar = (i + 1) + '/' + allFiles.length + ': ' + allFiles[i].name;
  ProcessFile(allFiles[i], i);
}

xlApp.StatusBar = 'done.';

function GetFileList(folderspec, arr) {
  var f = fso.GetFolder(folderspec);
  var fc = new Enumerator(f.SubFolders);
  for (; !fc.atEnd(); fc.moveNext())
    GetFileList(fc.item(), arr);
  fc = new Enumerator(f.Files);
  for (; !fc.atEnd(); fc.moveNext()) {
    var fo = fc.item();
    if (fo.Name.match(/.xls$/i))
      arr.push(fo);
  }
}

function ProcessFile(fo, lineNo) {
  crtLogLine = logLine + lineNo;
  logSheet.Cells(crtLogLine, "A").Value = "WORKING...";

  xlBook = xlApp.Workbooks.Open(fo.path, false, true);

  outSheet.Cells(outLine, "A").Value = fo.Name;
  var j;
  for (j = 1; j <= xlBook.Sheets.Count; j++) {
    outSheet.Cells(outLine, "B").Value = xlBook.Sheets(j).Name;
    SearchCells(xlBook.Sheets(j));
    SearchShapes(new Enumerator(xlBook.Sheets(j).Shapes));

  }
  xlBook.Close(false);

  logSheet.Cells(crtLogLine, "A").Value = "DONE";

}

function SearchCells(sht) {
  var i, j, ur;
  ur = sht.UsedRange;
  sht.Activate();
  for (j = 1; j <= ur.Rows.Count; j++) {
    sht.Cells(j, 1).Select();
    for (i = 1; i <= ur.Columns.Count; i++)  {
      if (sht.Cells(j, i).Value)
        if ((sht.Cells(j, i).Value + "").match(searchPattern)) {
          outSheet.Cells(outLine, "C").Value = sht.Cells(j, i).Address;
          outSheet.Cells(outLine, "D").Value = sht.Cells(j, i).Value;
          outLine++;
      }
    }
  }
} 


function SearchShapes(en) {
  for (; !en.atEnd(); en.moveNext()) {
    var shp = en.item();
    switch(shp.Type) {
    case msoGroup:
      SearchShapes(new Enumerator(shp.GroupItems));
      break;
    case msoAutoShape:
      if (shp.TextFrame.Characters().Text) 
        if (shp.TextFrame.Characters().Text.match(searchPattern)) {
          outSheet.Cells(outLine, "C").Value = shp.Name;
          outSheet.Cells(outLine, "D").Value = shp.TextFrame.Characters().Text;
          outLine++;
      }
      break;
    default:
      break;
    }
  }
  
}
// 对付控件工具栏上的 TextBox:
// sheet.Shapes("TextBox1").DrawingObject.object.Text

用法:把这个文件存为.js文件,修改好参数之后直接双击即可。
查找的结果保存在新建的 Excel 文件的 Sheet2 里。
等的不耐烦的时候可以直接关掉 Excel, 它会自行报错退出。