Archive for the ‘Excel’ Category

用 Javascript 批量查找 Excel 中的文字

星期五, 六月 5th, 2009

比 Excel 的查找有下述优点：

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

缺点是比较慢。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

// 设定值：分别是要查找的目录和查找条件
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， 它会自行报错退出。

Tags:,
Posted in Excel | No Comments »