WSHで納品時用Excel整頓ツールを作るよ
以下に対応してるよ
- 全シートA1へ移動
- 全セルを指定フォントへ変更
- 全セルを指定フォントサイズへ変更
- ウィンドウ表示形式を指定形式へ変更
- ズーム値を指定値へ変更
- 印刷向きを指定形式へ変更
- 印刷領域を指定値へ変更
- 用紙サイズを指定値へ変更
- 拡大縮小を指定形式へ変更
- シート１を選択状態にする
- Drag & Dropで、複数ファイル一気に整頓
本体（TidyExcel.wsf）
ソース
// UTF-16LEで保存すること.
<job>
<script language="JavaScript" src="CommonUtil.js"></script>
<script language="JavaScript" src="XlsxUtil.js"></script>
<script language="JavaScript" src="IeUtil.js"></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; }
span.box-left { display: inline-block; width: 35%; margin-right: 2%; vertical-align: middle; }
span.box-right { display: inline-block; width: 40%; vertical-align: middle; }
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>
<span class="box-left">フォント</span>
<span class="box-right">
<select id="font">
<option value="" selected>変更しない</option>
<option value="MS Pゴシック">MS Pゴシック</option>
<option value="游ゴシック">游ゴシック</option>
<option value="HGPｺﾞｼｯｸM">HGPｺﾞｼｯｸM</option>
<option value="HGP明朝B">HGP明朝B</option>
<option value="HGP明朝E">HGP明朝E</option>
<option value="HGP行書体">HGP行書体</option>
<option value="HGP教科書体">HGP教科書体</option>
<option value="HGP創英角ｺﾞｼｯｸUB">HGP創英角ｺﾞｼｯｸUB</option>
<option value="HGP創英角ﾎﾟｯﾌﾟ体">HGP創英角ﾎﾟｯﾌﾟ体</option>
<option value="HGP創英ﾌﾟﾚｾﾞﾝｽEB">HGP創英ﾌﾟﾚｾﾞﾝｽEB</option>
<option value="HG丸ｺﾞｼｯｸM-Pro">HG丸ｺﾞｼｯｸM-Pro</option>
<option value="HG正楷書体PRO">HG正楷書体PRO</option>
<option value="メイリオ">メイリオ</option>
<option value="Meiryo UI">Meiryo UI</option>
<option value="Arial Unicode MS">Arial Unicode MS</option>
<option value="Broadway">Broadway</option>
<option value="Calibri">Calibri</option>
<option value="Cambria & Cambria Math">Cambria & Cambria Math</option>
<option value="Consolas">Consolas</option>
</select>
</span>
</li>
<li>
<span class="box-left">フォントサイズ</span>
<span class="box-right">
<select id="fontSize">
<option value="" selected>変更しない</option>
<option value="6">6</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="14">14</option>
<option value="16">16</option>
<option value="18">18</option>
<option value="20">20</option>
<option value="22">22</option>
<option value="24">24</option>
<option value="26">26</option>
<option value="28">28</option>
</select>
</span>
</li>
<li>
<span class="box-left">ウィンドウ表示</span>
<span class="box-right">
<select id="xlWindowView">
<option value="" selected>変更しない</option>
<option value="xlNormalView">標準</option>
<option value="xlPageBreakPreview">改ページ プレビュー</option>
<option value="xlPageLayoutView">ページレイアウト</option>
</select>
</span>
</li>
<li>
<span class="box-left">ズーム</span>
<span class="box-right">
<input type="text" id="zoom" value="85" />
</span>
</li>
<br/>
<li>
<span class="box-left">用紙方向</span>
<span class="box-right">
<select id="xlPageOrientation">
<option value="" selected>変更しない</option>
<option value="xlPortrait">縦方向</option>
<option value="xlLandscape">横方向</option>
</select>
</span>
</li>
<li>
<span class="box-left">印刷範囲</span>
<span class="box-right">
<input type="text" id="printArea" value="$A:$AA" />
</span>
</li>
<li>
<span class="box-left">用紙サイズ</span>
<span class="box-right">
<select id="xlPaperSize">
<option value="" selected>変更しない</option>
<option value="xlPaperA3">A3</option>
<option value="xlPaperA4">A4</option>
<option value="xlPaperA5">A5</option>
<option value="xlPaperB4">B4</option>
<option value="xlPaperB5">B5</option>
</select>
</span>
</li>
<li>
<span class="box-left">拡大縮小</span>
<span class="box-right">
<select id="pageSetupZoom">
<option value="" selected>変更しない</option>
<option value="zoomOff">拡大縮小なし</option>
<option value="Sheet1page">シートを1ページに印刷</option>
<option value="column1page">全ての列を1ページに印刷</option>
<option value="row1page">全ての行を1ページに印刷</option>
</select>
</span>
</li>
<br/>
<li id="wrapper">
<input type="button" onClick="quit()" value="実行" />
</li>
</ul>
</form>
<script> document.body.style.overflow = "hidden"; </script>
<script>
function quit(){
document.getElementById( "CLICKED" ).value="true";
window.open('','_self').close();
}
</script>
</body>
</html>
]]>
</resource>
<script language="JavaScript">
// 引数チェック.
if ( WScript.Arguments.length === 0 ){
$.info( "ファイルをD&Dしてください.", "エラー" );
WScript.Quit();
}
// const.
var NewBookPrefix = "new_";
// 入力画面描画、入力値取得
var displaySize = $.IE.getDisplaySize();
var tidyInfo = $.IE.displayCustomWindow(
displaySize.width, displaySize.height, 420, 420, getResource( "inputHtml" ), "整頓条件入力"
);
// 実行.
$.mapX( WScript.Arguments, function( path ){
$.Xlsx.withXlsx( path, function( ole, book ){
$.mapX( book.Worksheets, function( sheet ){
tidyWorkSheet( ole, sheet, tidyInfo );
});
book.Worksheets(1).Select;
// saveAs.
book.Saveas( book.Path + $.FileSeparator + NewBookPrefix + book.Name );
});
});
// 終了.
$.info( "done.", "end" );
WScript.Quit();
function tidyWorkSheet ( ole, sheet, tidyInfo ) {
if ( ! sheet.Visible ) return;
sheet.Select;
ole.Application.Goto( sheet.Range( "A1" ), true );
if ( tidyInfo.font ) sheet.Cells.Font.Name = tidyInfo.font;
if ( tidyInfo.fontSize ) sheet.Cells.Font.Size = tidyInfo.fontSize;
if ( tidyInfo.xlWindowView )
ole.Application.ActiveWindow.View = $.Xlsx[ tidyInfo.xlWindowView ];
if ( tidyInfo.zoom ) ole.Application.ActiveWindow.Zoom = tidyInfo.zoom;
if ( tidyInfo.xlPageOrientation )
sheet.PageSetup.Orientation = $.Xlsx[ tidyInfo.xlPageOrientation ];
if ( tidyInfo.printArea ) sheet.PageSetup.PrintArea = tidyInfo.printArea;
if ( tidyInfo.xlPaperSize )
sheet.PageSetup.PaperSize = $.Xlsx[ tidyInfo.xlPaperSize ];
switch ( tidyInfo.pageSetupZoom ) {
case "zoomOff":
sheet.PageSetup.Zoom = 100;
sheet.PageSetup.FitToPagesTall = false;
sheet.PageSetup.FitToPagesWide = false;
break;
case "Sheet1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = 1;
break;
case "column1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = false;
sheet.PageSetup.FitToPagesWide = 1;
break;
case "row1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = false;
break;
default:
break;
}
if ( tidyInfo.pageSetupZoom ) sheet.ResetAllPageBreaks();
}
</script>
</job>
共通FW（CommonUtil.js）
Excel用FWっぽいの（XlsxUtil.js）
IE制御用FWっぽいの（IeUtil.js）
統合版（TidyExcel.wsf）
ソース
// UTF-8(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;}span.box-left{display: inline-block; width: 35%; margin-right: 2%; vertical-align: middle;}span.box-right{display: inline-block; width: 40%; vertical-align: middle;}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><span class="box-left">フォント</span><span class="box-right"><select id="font"><option value="">変更しない</option><option value="MS Pゴシック">MS Pゴシック</option><option value="ＭＳ ゴシック" selected>ＭＳゴシック</option><option value="游ゴシック">游ゴシック</option><option value="HGPｺﾞｼｯｸM">HGPｺﾞｼｯｸM</option><option value="HGP明朝B">HGP明朝B</option><option value="HGP明朝E">HGP明朝E</option><option value="HGP行書体">HGP行書体</option><option value="HGP教科書体">HGP教科書体</option><option value="HGP創英角ｺﾞｼｯｸUB">HGP創英角ｺﾞｼｯｸUB</option><option value="HGP創英角ﾎﾟｯﾌﾟ体">HGP創英角ﾎﾟｯﾌﾟ体</option><option value="HGP創英ﾌﾟﾚｾﾞﾝｽEB">HGP創英ﾌﾟﾚｾﾞﾝｽEB</option><option value="HG丸ｺﾞｼｯｸM-Pro">HG丸ｺﾞｼｯｸM-Pro</option><option value="HG正楷書体PRO">HG正楷書体PRO</option><option value="メイリオ">メイリオ</option><option value="Meiryo UI">Meiryo UI</option><option value="Arial Unicode MS">Arial Unicode MS</option><option value="Broadway">Broadway</option><option value="Calibri">Calibri</option><option value="Cambria & Cambria Math">Cambria & Cambria Math</option><option value="Consolas">Consolas</option></select></span></li><li><span class="box-left">フォントサイズ</span><span class="box-right"><select id="fontSize"><option value="">変更しない</option><option value="6">6</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="10.5" selected>10.5</option><option value="11">11</option><option value="12">12</option><option value="14">14</option><option value="16">16</option><option value="18">18</option><option value="20">20</option><option value="22">22</option><option value="24">24</option><option value="26">26</option><option value="28">28</option></select></span></li><li><span class="box-left">ウィンドウ表示</span><span class="box-right"><select id="xlWindowView"><option value="" selected>変更しない</option><option value="xlNormalView">標準</option><option value="xlPageBreakPreview">改ページ プレビュー</option><option value="xlPageLayoutView">ページレイアウト</option></select></span></li><li><span class="box-left">ズーム</span><span class="box-right"><input type="text" id="zoom" value="100"/></span></li><br/><li><span class="box-left">用紙方向</span><span class="box-right"><select id="xlPageOrientation"><option value="">変更しない</option><option value="xlPortrait">縦方向</option><option value="xlLandscape" selected>横方向</option></select></span></li><li><span class="box-left">印刷範囲</span><span class="box-right"><input type="text" id="printArea" value=""/></span></li><li><span class="box-left">用紙サイズ</span><span class="box-right"><select id="xlPaperSize"><option value="" selected>変更しない</option><option value="xlPaperA3">A3</option><option value="xlPaperA4">A4</option><option value="xlPaperA5">A5</option><option value="xlPaperB4">B4</option><option value="xlPaperB5">B5</option></select></span></li><li><span class="box-left">拡大縮小</span><span class="box-right"><select id="pageSetupZoom"><option value="">変更しない</option><option value="zoomOff">拡大縮小なし</option><option value="Sheet1page">シートを1ページに印刷</option><option value="column1page" selected>全ての列を1ページに印刷</option><option value="row1page">全ての行を1ページに印刷</option></select></span></li><br/><li id="wrapper"><input type="button" onClick="quit()" value="実行"/></li></ul></form><script>document.body.style.overflow="hidden";</script><script>function quit(){document.getElementById( "CLICKED" ).value="true"; window.open('','_self').close();}</script></body></html>]]>
</resource>
<script language="JavaScript">
// 引数チェック.
if ( WScript.Arguments.length === 0 ){
$.info( "ファイルをD&Dしてください.", "エラー" );
WScript.Quit();
}
// const.
var XlsxPrefix = "$.Xlsx.";
var OutputPrefix = "TIDY";
// 入力画面描画、入力値取得
var displaySize = $.IE.getDisplaySize();
var tidyInfo = $.IE.displayCustomWindow(
displaySize.width, displaySize.height, 500, 530, getResource( "inputHtml" ), "整頓条件入力"
);
// create folder.
var dir = $.Fso.GetFile( WScript.Arguments.Item(0) ).ParentFolder + $.FileSeparator +
OutputPrefix + $.getDateString( new Date(), '@YYYY@@MM@@DD@@hh@@mm@@ss@' );
if ( !$.Fso.FolderExists( dir ) ){
$.Fso.CreateFolder( dir );
}
// 実行.
$.mapX( WScript.Arguments, function( path ){
$.Xlsx.withXlsx( path, function( ole, book ){
$.mapX( book.Worksheets, function( sheet ){
tidyWorkSheet( ole, sheet, tidyInfo );
});
book.Worksheets(1).Select;
// saveAs.
book.Saveas( dir + $.FileSeparator + book.Name );
});
});
// 終了.
$.info( "done.", "end" );
WScript.Quit();
function tidyWorkSheet ( ole, sheet, tidyInfo ) {
if ( ! sheet.Visible ) return;
sheet.Select;
ole.Application.Goto( sheet.Range( "A1" ), true );
if ( tidyInfo.font ) sheet.Cells.Font.Name = tidyInfo.font;
if ( tidyInfo.fontSize ) sheet.Cells.Font.Size = tidyInfo.fontSize;
if ( tidyInfo.xlWindowView )
ole.Application.ActiveWindow.View = eval( XlsxPrefix + tidyInfo.xlWindowView );
if ( tidyInfo.zoom ) ole.Application.ActiveWindow.Zoom = tidyInfo.zoom;
if ( tidyInfo.xlPageOrientation )
sheet.PageSetup.Orientation = eval( XlsxPrefix + tidyInfo.xlPageOrientation );
if ( tidyInfo.printArea ) sheet.PageSetup.PrintArea = tidyInfo.printArea;
if ( tidyInfo.xlPaperSize )
sheet.PageSetup.PaperSize = eval( XlsxPrefix + tidyInfo.xlPaperSize );
switch ( tidyInfo.pageSetupZoom ) {
case "zoomOff":
sheet.PageSetup.Zoom = 100;
sheet.PageSetup.FitToPagesTall = false;
sheet.PageSetup.FitToPagesWide = false;
break;
case "Sheet1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = 1;
break;
case "column1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = false;
sheet.PageSetup.FitToPagesWide = 1;
break;
case "row1page":
sheet.PageSetup.Zoom = false;
sheet.PageSetup.FitToPagesTall = 1;
sheet.PageSetup.FitToPagesWide = false;
break;
default:
break;
}
if ( tidyInfo.pageSetupZoom ) sheet.ResetAllPageBreaks();
}
</script>
</job>
総括・ハマりポイント
- 今までと同様で大したことはしていない。
- IEのDOMがらみと思われるオブジェクト解放でハマった。