比 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, 它会自行报错退出。