82855 AI Apple Watch CentOS Eclipse H700 iCal iOS iPad iPhone iphone4 iTunes Java Javascript linux Mac MBP ML MySQL Oracle OS X Parallels Desktop RAID redmine Rocky Linux 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, 它会自行报错退出。