HTAで納品時用Excel整頓ツールを作るよ
※これの焼き直し
なぜ、WSHやHTAにこだわるか
→ 客先などでアプリを自由にインストールできない時の為。
以下に対応してるよ
- 全シートA1へ移動
- 全セルを指定フォントへ変更
- 全セルを指定フォントサイズへ変更
- ウィンドウ表示形式を指定形式へ変更
- ズーム値を指定値へ変更
- 印刷向きを指定形式へ変更
- 印刷領域を指定値へ変更
- 用紙サイズを指定値へ変更
- 拡大縮小を指定形式へ変更
- シート１を選択状態にする
- 処理フォルダ指定で、複数ファイル一気に整頓
## 本体（TidyExcel.hta）
ソース
utf-8で保存する
<!doctype html>
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta charset="utf-8">
<title>Excel納品用ツール</title>
<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;}--> progress {width: 90%;} #progressBar {width: 0%; height: 30px; background-color: green; text-align: center; line-height: 30px; color: white;} .status-text{ font-family: Arial, sans-serif;} #status { font-size: 12px; }</style></head>
<body>
<form><ul>
<li>対象フォルダ:
<input type="text" id="folderPath" style="width: 50%;" readonly>
<button onclick="selectFolder();return false;">...</button>
</li>
<br/>
<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="游ゴシック">游ゴシック</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="10.5">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="" 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=""/></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="execute()" value="実行"/></li></ul>
<li><div id="progressBar">0%</div></li>
<li><div id="status"></div></li>
</ul></form>
<script>
document.body.style.overflow="hidden";
var _ = document.querySelector.bind( document );
var files = [];
// window settings.
var baseWidth = 380;
var baseHeight = 540;
var zoomFactor = screen.deviceXDPI / 96;
var finalWidth = baseWidth * zoomFactor;
var finalHeight = baseHeight * zoomFactor;
window.resizeTo(finalWidth, finalHeight);
var x = (screen.width - finalWidth) / 2;
var y = (screen.height - finalHeight) / 2;
window.moveTo(x, y);
/////////////////////////////////////////////////// CommonUtil.js
(function() {
"use strict";
var root = this;
var $ = function( obj ) {
if ( obj instanceof $ ) return obj;
if ( !( this instanceof $ ) ) return new $( obj );
this._wrapped = obj;
}
root.$ = $;
$.Fso = new ActiveXObject( "Scripting.FileSystemObject" );
$.Shell = new ActiveXObject( "WScript.Shell" );
$.ShApp = new ActiveXObject("Shell.Application");
$.FileSeparator = "/";
$.AdoDbStream = {
obj : new ActiveXObject( "ADODB.Stream" ),
// StreamTypeEnum.
adTypeBinary : 1,
adTypeText : 2,
// SaveOptionsEnum.
adSaveCreateNotExist : 1,
adSaveCreateOverWrite : 2,
// StreamWriteEnum.
adWriteChar : 0,
adWriteLine : 1,
// StreamReadEnum.
adReadAll : -1,
adReadLine : -2,
LF : 10,
// read file with charset.
readFile : function( fullPath, charset, func ) {
if ( charset == null ) {
charset = "_autodetect_all";
}
var st = $.AdoDbStream.obj;
st.Type = $.AdoDbStream.adTypeText;
st.charset = charset;
st.LineSeparator = $.AdoDbStream.LF;
st.Open();
st.LoadFromFile( fullPath );
st.Position = 0;
while( ! st.EOS ){
func( st.ReadText( $.AdoDbStream.adReadLine ) );
}
st.Close();
},
// write file with charset.
writeFile : function( fullPath, text, charset ) {
if ( charset == null ) {
charset = "_autodetect_all";
}
var st = $.AdoDbStream.obj;
st.Type = $.AdoDbStream.adTypeText;
st.charset = charset;
st.Open();
st.WriteText( text, $.AdoDbStream.adWriteLine );
st.SaveToFile( fullPath, $.AdoDbStream.adSaveCreateOverWrite );
st.Close();
}
}
$.yesno = function( message, title ) {
return $.Shell.Popup( message, 0, title, 1 );
}
$.info = function( message, title ) {
return $.Shell.Popup( message, 0, title, 0 );
}
$.log = function( m ) {
alert( m );
}
// each with activeXObject
$.eachX = function( obj, func ) {
for( var _obj = new Enumerator( obj ); !_obj.atEnd(); _obj.moveNext() ){
var v = _obj.item();
func( v )
}
}
// map with activeXobject
$.mapX = function( obj, func ) {
var ret = [];
for( var _obj = new Enumerator( obj ); !_obj.atEnd(); _obj.moveNext() ){
var v = _obj.item();
var value = func( v );
if ( !( value === false ) ) ret[ret.length] = value
}
return ret;
}
$.assignObject = function( obj1, obj2 ) {
if ( !obj2 ) {
obj2 = {};
}
for ( var attrName in obj2 ) {
if ( obj2.hasOwnProperty( attrName ) ) {
obj1[attrName] = obj2[attrName];
}
}
return obj1;
}
$.objectkeys = function( obj ) {
var keys = [];
for ( var attrName in obj ) {
if ( obj.hasOwnProperty( attrName ) ) {
keys.push( attrName );
}
}
return keys;
}
$.getZeroPadding = function( number, digits ) {
return ( Array( digits +1 ).join( '0' ) + number ).slice( -digits );
}
$.getDateString = function( date, dateFormat ) {
var months = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
];
var dayOfWeekStr = [ "日", "月", "火", "水", "木", "金", "土" ];
var dayOfWeekEngStr = [ "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" ];
return dateFormat
.replace( /@YYYY@/g, date.getFullYear() )
.replace( /@YY@/g, ( "" + date.getFullYear() ).slice( -2 ) )
.replace( /@MMM@/g, months[ date.getMonth() ] )
.replace( /@MM@/g, $.getZeroPadding( date.getMonth() + 1, 2) )
.replace( /@M@/g, date.getMonth() + 1 )
.replace( /@DD@/g, $.getZeroPadding( date.getDate(), 2) )
.replace( /@D@/g, date.getDate() )
.replace( /@hh@/g, $.getZeroPadding( date.getHours(), 2) )
.replace( /@h@/g, date.getHours() )
.replace( /@mm@/g, $.getZeroPadding( date.getMinutes(), 2) )
.replace( /@m@/g, date.getMinutes() )
.replace( /@ss@/g, $.getZeroPadding( date.getSeconds(), 2) )
.replace( /@s@/g, date.getSeconds() )
.replace( /@EEE@/g, dayOfWeekStr[ date.getDay() ] )
.replace( /@eee@/g, dayOfWeekEngStr[ date.getDay() ] );
}
$.isDate = function( s ){
var d = new Date( s );
return d.toString() !== 'NaN' && d.toString() !== 'Invalid Date';
}
$.compareDateStr = function( dateStr1, dateStr2 ){
var date1 = new Date( dateStr1 );
var date2 = new Date( dateStr2 );
return date1.getTime() < date2.getTime() ? -1 : date1.getTime() > date2.getTime() ? 1 : 0;
}
Array.prototype.indexOf = function(obj, start){ for(var i = (start || 0), j = this.length; i < j; i++){ if(this[i] === obj){ return i }} return -1 };
}).call( this );
//////////////////////////////////////////////// XlsxUtil.js
(function () {
"use strict";
var root = this;
if ( typeof $ === "undefined" ) {
alert('"CommonUtil.js" must be loaded.');
return;
}
// For Excel.
$.Xlsx = {
// XlPageOrientation.
xlPortrait: 1,
xlLandscape: 2,
// XlWindowView.
xlNormalView: 1,
xlPageBreakPreview: 2,
xlPageLayoutView: 3,
// XlPaperSize.
xlPaperA3: 8,
xlPaperA4: 9,
xlPaperA5: 11,
xlPaperB4: 12,
xlPaperB5: 13,
withXlsx : function ( fileName , func ) {
var ole = new ActiveXObject( "Excel.Application" );
ole.Visible = false;
ole.DisplayAlerts = false;
var book = null;
var full_path = null;
try {
full_path = $.Fso.getAbsolutePathName( fileName );
book = ole.Workbooks.Open( full_path );
func( ole , book );
} catch( e ) {
alert( e.name + ":" + e.message );
} finally {
if( book != null){
book.Close();
}
ole.Quit();
ole = null;
}
},
range2hash : function ( range ) {
var ret = [];
ret = $.mapX( range.rows , function( row ) {
var _v = $.mapX( row.columns , function( cell ) {
var v = cell.value;
if ( typeof v == "undefined" ) v = "";
if ( typeof v == "unknown" ) v = "#REF"
return v;
})
return _v;
})
return ret;
},
range2AddressMap : function ( sheetName ,range ) {
var ret = {};
$.mapX( range.rows , function( row ) {
$.mapX( row.columns , function( cell ) {
var text = cell.Text;
if ( text ) ret[sheetName + "!" + cell.Address( false, false )] = text;
});
});
return ret;
},
replaceCellValue : function ( ole, rng, targetStr, replaceStr ) {
var data = rng.Value.toArray();
var rep = "\"" + replaceStr + "\"";
var target = "\"" + targetStr + "\"";
var newData = ole.Evaluate(
"IF(" + rng.address + "=" + target + "," + rep + "," + rng.address + "&\"\")"
);
rng.Value = newData;
},
replaceShapeValue : function ( sheet, reg, replaceStr ) {
$.mapX( sheet.Shapes , function( shape ) {
if ( shape.TextFrame2.HasText ) {
var textRange = shape.TextFrame2.TextRange;
var text = textRange.Text;
if ( text && reg.test( text ) ){
textRange.Text = text.replace( reg, replaceStr );
}
}
});
},
speak : function ( str ) {
var ole = new ActiveXObject( "Excel.Application" );
ole.Visible = false;
ole.DisplayAlerts = false;
try {
ole.Speech.Speak ( str );
} finally {
ole.Quit();
ole = null;
}
}
}
}).call( this );
function execute() {
// const.
var outputPrefix = "TIDY";
var currentPath = $.Fso.GetAbsolutePathName(".");
// target folder.
var targetFolderPath = _("#folderPath").value;
if ( !targetFolderPath ) {
return;
}
_("#status").innerText = "待機中...";
try {
var outputDir = currentPath + $.FileSeparator + outputPrefix + $.getDateString( new Date(), '@YYYY@@MM@@DD@@hh@@mm@@ss@' );
processFolder( targetFolderPath, outputDir );
if (files.length === 0) {
_("#status").innerText = "対象ファイルがありません。";
return;
}
// 進捗バー 初期化
_("#progressBar").style.width = '0%';
_("#progressBar").innerText = '0%';
// 入力値取得
var tidyInfo = {};
tidyInfo.font = _("#font").value;
tidyInfo.fontSize = _("#fontSize").value;
tidyInfo.xlWindowView = _("#xlWindowView").value;
tidyInfo.zoom = _("#zoom").value;
tidyInfo.xlPageOrientation = _("#xlPageOrientation").value;
tidyInfo.printArea = _("#printArea").value;
tidyInfo.xlPaperSize = _("#xlPaperSize").value;
tidyInfo.pageSetupZoom = _("#pageSetupZoom").value;
// 各ファイルのループ処理
var done = 0;
$.mapX( files, function( path ){
if (!/\.xlsx?$/i.test( path )) {
done++;
return;
}
$.Xlsx.withXlsx( path, function( ole, book ){
$.mapX( book.Worksheets, function( sheet ){
if ( ! sheet.Visible || sheet.ProtectContents ) return;
tidyWorkSheet( ole, sheet, tidyInfo );
});
// saveAs.
book.Saveas( outputDir + path.replace(targetFolderPath, '') );
});
var progress = Math.round((++done / files.length) * 100);
// HTAのUIを更新
_("#progressBar").style.width = progress + '%';
_("#progressBar").innerText = progress + '%';
var fileName = $.Fso.GetFileName( path );
_("#status").innerText = "処理中: " + fileName + " (" + done + "/" + files.length + ")";
repaintDom();
});
_("#status").innerText = "完了しました";
$.Xlsx.speak( "処理完了しました" );
_("#progressBar").style.width = '100%';
_("#progressBar").innerText = '100%';
repaintDom();
} catch (e) {
$.Xlsx.speak( "エラーが発生しました" );
alert("エラーが発生しました: " + e.message);
}
}
function selectFolder(){
//var desktopPath = $.Shell.ExpandEnvironmentStrings("%UserProfile%") + "\\Desktop";
var targetFolder = $.ShApp.BrowseForFolder(0, "処理対象フォルダを選択してください", 0);
if ( !targetFolder ) {
return;
}
_("#folderPath").value = targetFolder.Self.Path;
}
function processFolder( currentSrc, currentDest ) {
if (!$.Fso.FolderExists( currentDest )) {
$.Fso.CreateFolder( currentDest );
}
var folder = $.Fso.GetFolder( currentSrc );
var fileList = new Enumerator( folder.Files );
for (; !fileList.atEnd(); fileList.moveNext()) {
var file = fileList.item();
files.push( file.Path );
}
var subFolders = new Enumerator( folder.SubFolders );
for (; !subFolders.atEnd(); subFolders.moveNext()) {
var subFolder = subFolders.item();
var nextDest = $.Fso.BuildPath( currentDest, subFolder.Name );
processFolder( subFolder.Path, nextDest );
}
}
function repaintDom(){
// HTAはシングルスレッドなので、UI描画を強制的に更新させる為のHack
$.Shell.Run("powershell -command \"Start-Sleep -m 1 \", 0, true", 0, true);
}
function tidyWorkSheet( ole, sheet, tidyInfo ) {
// const.
var XlsxPrefix = "$.Xlsx.";
sheet.Select;
ole.Application.Goto( sheet.Range( "A1" ), true );
// # PageSetup.Duplex
var xlSimplex = 1; // 片面印刷（単票印刷）
var xlDuplexVertical = 2; // 両面印刷（長辺とじ）
var xlDuplexHorizontal = 3; // 両面印刷（短辺とじ）
//if (sheet.PageSetup) sheet.PageSetup.Duplex = xlSimplex;
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>
</body>
</html>
総括・ハマりポイント
- 進捗状況表示でDOMが更新されないのにはまった。
- AIが役に立った。がウソを頻繁につく