WSHで横断的Excel検索ツールを作るよ
以下に対応してるよ
- Drag & Dropで、複数フォルダ/ファイル内を一括検索
- 検索結果は、tsvファイル出力するので、Excelに貼ったり可
- 検索キーワードは正規表現可
- オートシェイプ内も可
## 本体（XlsxSearch.wsf）
ソース
// Save UTF-8(with BOM).
<job>
<script language="JavaScript">
(function(){"use strict";var e=function(t){return t instanceof e?t:this instanceof e?void(this._wrapped=t):new e(t)};this.$=e,e.Fso=new ActiveXObject("Scripting.FileSystemObject"),e.Shell=new ActiveXObject("WScript.Shell"),e.FileSeparator="/",e.AdoDbStream={obj:new ActiveXObject("ADODB.Stream"),adTypeBinary:1,adTypeText:2,adSaveCreateNotExist:1,adSaveCreateOverWrite:2,adWriteChar:0,adWriteLine:1,adReadAll:-1,adReadLine:-2,LF:10,readFile:function(t,a,n){null==a&&(a="_autodetect_all");var r=e.AdoDbStream.obj;for(r.Type=e.AdoDbStream.adTypeText,r.charset=a,r.LineSeparator=e.AdoDbStream.LF,r.Open(),r.LoadFromFile(t),r.Position=0;!r.EOS;)n(r.ReadText(e.AdoDbStream.adReadLine));r.Close()},writeFile:function(t,a,n){null==n&&(n="_autodetect_all");var r=e.AdoDbStream.obj;r.Type=e.AdoDbStream.adTypeText,r.charset=n,r.Open(),r.WriteText(a,e.AdoDbStream.adWriteLine),r.SaveToFile(t,e.AdoDbStream.adSaveCreateOverWrite),r.Close()}},e.yesno=function(t,a){return e.Shell.Popup(t,0,a,1)},e.info=function(t,a){return e.Shell.Popup(t,0,a,0)},e.log=function(e){WScript.echo(e)},e.eachX=function(e,t){for(var a=new Enumerator(e);!a.atEnd();a.moveNext()){t(a.item())}},e.mapX=function(e,t){for(var a=[],n=new Enumerator(e);!n.atEnd();n.moveNext()){var r=t(n.item());!1!==r&&(a[a.length]=r)}return a},e.assignObject=function(e,t){for(var a in t||(t={}),t)t.hasOwnProperty(a)&&(e[a]=t[a]);return e},e.objectkeys=function(e){var t=[];for(var a in e)e.hasOwnProperty(a)&&t.push(a);return t},e.getZeroPadding=function(e,t){return(Array(t+1).join("0")+e).slice(-t)},e.getDateString=function(t,a){return a.replace(/@YYYY@/g,t.getFullYear()).replace(/@YY@/g,(""+t.getFullYear()).slice(-2)).replace(/@MMM@/g,["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"][t.getMonth()]).replace(/@MM@/g,e.getZeroPadding(t.getMonth()+1,2)).replace(/@M@/g,t.getMonth()+1).replace(/@DD@/g,e.getZeroPadding(t.getDate(),2)).replace(/@D@/g,t.getDate()).replace(/@hh@/g,e.getZeroPadding(t.getHours(),2)).replace(/@h@/g,t.getHours()).replace(/@mm@/g,e.getZeroPadding(t.getMinutes(),2)).replace(/@m@/g,t.getMinutes()).replace(/@ss@/g,e.getZeroPadding(t.getSeconds(),2)).replace(/@s@/g,t.getSeconds()).replace(/@EEE@/g,["日","月","火","水","木","金","土"][t.getDay()]).replace(/@eee@/g,["Sun","Mon","Tue","Wed","Thu","Fri","Sat"][t.getDay()])},e.isDate=function(e){var t=new Date(e);return"NaN"!==t.toString()&&"Invalid Date"!==t.toString()},e.compareDateStr=function(e,t){var a=new Date(e),n=new Date(t);return a.getTime()<n.getTime()?-1:a.getTime()>n.getTime()?1:0},e.Xlsx={xlPortrait:1,xlLandscape:2,xlNormalView:1,xlPageBreakPreview:2,xlPageLayoutView:3,xlPaperA3:8,xlPaperA4:9,xlPaperA5:11,xlPaperB4:12,xlPaperB5:13,withXlsx:function(t,a){var n=new ActiveXObject("Excel.Application");n.Visible=!1,n.DisplayAlerts=!1;var r=null,i=null;try{i=e.Fso.getAbsolutePathName(t),a(n,r=n.Workbooks.Open(i))}catch(t){e.log(t.name+":"+t.message)}finally{null!=r&&r.Close(),n.Quit()}},range2hash:function(t){return e.mapX(t.rows,function(t){return e.mapX(t.columns,function(e){var t=e.value;return void 0===t&&(t=""),"unknown"==typeof t&&(t="#REF"),t})})},range2AddressMap:function(t,a){var n={};return e.mapX(a.rows,function(a){e.mapX(a.columns,function(e){var a=e.Text;a&&(n[t+"!"+e.Address(!1,!1)]=a)})}),n},replaceCellValue:function(t,a,n){e.mapX(t.rows,function(t){e.mapX(t.columns,function(e){var t=e.Text;t&&(e.Value=t.replace(a,n))})})},replaceShapeValue:function(t,a,n){e.mapX(t.Shapes,function(e){var t=null;try{e.TextFrame2.HasText&&(t=e.TextFrame.Characters().Text)}catch(e){return}t&&(e.TextFrame.Characters().Text=t.replace(a,n))})}},this.IE_OnQuit=function(){e.IE.onQuitWrapper()},e.IE={READYSTATE_UNINITIALIZED:0,READYSTATE_LOADING:1,READYSTATE_LOADED:2,READYSTATE_INTERACTIVE:3,READYSTATE_COMPLETE:4,ObjectIE:WScript.CreateObject("InternetExplorer.Application","IE_"),SettingInfo:{},Clicked:!1,onQuitWrapper:function(){var t=this.ObjectIE;"true"===t.document.getElementById("CLICKED").value&&(e.mapX(t.document.getElementsByTagName("input"),function(t){e.IE.setTagVal(t,e.IE.SettingInfo)}),e.mapX(t.document.getElementsByTagName("select"),function(t){e.IE.setTagVal(t,e.IE.SettingInfo)})),this.Clicked=!0},getDisplaySize:function(e){var t=WScript.CreateObject("InternetExplorer.Application");this.Clicked=!1,this.SettingInfo={},t.Visible=!1,t.Navigate("about:blank"),this.waitIE(t);var a=t.document.parentWindow.screen.width,n=t.document.parentWindow.screen.height;return t.Quit(),{width:a,height:n}},displayCustomWindow:function(t,a,n,r,i,o){var c=this.ObjectIE;for(this.Clicked=!1,this.SettingInfo={},c.Left=(t-n)/2,c.Top=(a-r)/2,c.Width=n,c.Height=r,c.ToolBar=!1,c.StatusBar=!1,c.Resizable=!0,c.Visible=!0,c.Navigate("about:blank"),this.waitIE(c),c.document.write(i),c.document.title=o;!this.Clicked;)WScript.Sleep(500);return e.objectkeys(e.IE.SettingInfo).length||WScript.quit(),e.IE.SettingInfo},waitIE:function(e){for(;e.Busy||e.readystate!=this.READYSTATE_COMPLETE;)WScript.Sleep(100)},setTagVal:function(e,t){switch(e.type.toLowerCase()){case"text":t[e.id]=e.value;break;case"checkbox":t[e.id]=e.checked;break;case"radio":t[e.name]=e.checked?e.value:t[e.name];break;case"select-one":t[e.id]=e.value}return t}}}).call(this);
</script>
<resource id="inputHtml">
<![CDATA[<html><head><meta charset="utf-8"/><style type="text/css">body{font-family:'メイリオ',Meiryo}ul{padding-left:0px;list-style-type:none}li[id="wrapper"]{text-align:center}input[type="button"]{font-size:1.4em;font-weight:bold;padding:10px 30px;-moz-border-radius:5px;-webkit-border-radius:5px;border-radius:5px}</style></head><body><form> <input type="hidden" id="CLICKED" value="false"/><ul><li>検索条件入力（正規表現対応）</li><br/><li><label for="targetStr">対象文字列: </label><input type="text" id="targetStr"/><input type="text" id="dummy" style="display:none;"/></li> <br/><li><input type="checkbox" id="autoShapes" /><label for="autoShapes">オートシェイプ内も検索する</label></li><br/><li id="wrapper"><input type="button" onClick="quit()" value="実行"/></li></ul></form> <script>document.body.style.overflow="hidden";function quit(){document.getElementById("CLICKED").value="true";window.open('','_self').close();}</script> </body></html>]]>
</resource>
<script language="JavaScript">
// check arguments.
if ( WScript.Arguments.length === 0 ){
$.info( "ファイル/フォルダをD&Dしてください.", "エラー" );
WScript.Quit();
}
// # const.
var XlsxPrefix = "$.Xlsx.", OUTPUT_PREFIX = "RESULT_", TAB = "\t", CRLF = "\r\n", EXTENTION = ".tsv",
vbTrue = -1, msoGroup = 6;
// # input conditions.
var displaySize = $.IE.getDisplaySize();
var searchInfo = $.IE.displayCustomWindow(
displaySize.width, displaySize.height, 400, 350, getResource( "inputHtml" ), "検索条件入力"
);
var getFilePath = function( paths, strTargetDir ){
if ($.Fso.FileExists( strTargetDir )) {
paths.push( strTargetDir );
} else {
var folder = $.Fso.GetFolder( strTargetDir );
$.mapX( folder.SubFolders, function( subfolder ) {
paths.concat( getFilePath( paths, subfolder.path ) );
});
$.mapX( folder.Files, function( file ) {
paths.push( file.path );
});
}
return paths;
}
// # execute.
var result = [], fileCount = 0;
// header
result.push( "SEARCH KEYWORD: " + searchInfo.targetStr );
result.push( "No." + TAB + "BookName" + TAB + "SheetName" + TAB + "Address" + TAB + "Link" + TAB + "Target");
var reg = new RegExp( searchInfo.targetStr, 'g' );
$.mapX( WScript.Arguments, function( arg ){
$.mapX( getFilePath( [], arg ), function( path ){
fileCount++;
$.Xlsx.withXlsx( path, function( ole, book ){
$.mapX( book.Worksheets, function( sheet ){
// autoshapes.
if ( searchInfo.autoShapes ) {
$.mapX( sheet.Shapes , function( shape ) {
switch ( shape.type ) {
case msoGroup:
$.mapX( shape.groupItems, function( it ) {
switch ( it.TextFrame2.HasText ) {
case vbTrue:
var text = it.TextFrame2.TextRange.Text;
if ( text && reg.test(text) ) {
result.push(
// No.
"=row()-2" + TAB +
// BookName
book.name + TAB +
// SheetName
sheet.name + TAB +
// Address
it.TopLeftCell.address(true, true) + TAB +
// Link
"=HYPERLINK(\"[" + book.fullname + "]" + sheet.name + "!" + it.TopLeftCell.address(true, true) + "\", \"リンク\")" + TAB +
// Text
text
);
}
break;
}
});
break;
default:
switch ( shape.TextFrame2.HasText ) {
case vbTrue:
var text = shape.TextFrame2.TextRange.Text;
if ( text && reg.test(text) ) {
result.push(
// No.
"=row()-2" + TAB +
// BookName
book.name + TAB +
// SheetName
sheet.name + TAB +
// Address
shape.TopLeftCell.address(true, true) + TAB +
// Link
"=HYPERLINK(\"[" + book.fullname + "]" + sheet.name + "!" + shape.TopLeftCell.address(true, true) + "\", \"リンク\")" + TAB +
// Text
text
);
}
}
}
});
}
var range = sheet.usedrange;
$.mapX( range.rows, function( row ) {
$.mapX( row.columns, function( cell ) {
var text = cell.Text;
if ( text && reg.test(text) ) {
result.push(
// No.
"=row()-2" + TAB +
// BookName
book.name + TAB +
// SheetName
sheet.name + TAB +
// Address
cell.Address(true, true) + TAB +
// Link
"=HYPERLINK(\"[" + book.fullname + "]" + sheet.name + "!" + cell.Address(true, true) + "\", \"リンク\")" + TAB +
// Text
text
);
}
});
});
});
});
});
});
// # make results.
result.push( "NUMBER OF SEARCH FILE: " + fileCount );
var fileName = $.getDateString( new Date(), OUTPUT_PREFIX + '@YYYY@@MM@@DD@@hh@@mm@@ss@' ) + EXTENTION;
$.AdoDbStream.writeFile(
$.Fso.getParentFolderName( WScript.ScriptFullName ) + $.FileSeparator + fileName,
result.join( CRLF ),
"utf-8"
);
// # end.
$.info( 'done.', 'end' );
WScript.Quit();
</script>
</job>
総括・ハマりポイント
- 2022年6月にIEのサポート終了でも動くんだろうか？
- 今までの総括的なScriptとなった