HTAでExcel内テキスト置換ツールを作るよ
※これの焼き直し
以下に対応してるよ
- セル内テキスト置換
- オートシェイプ内テキスト置換
- 正規表現対応(高速化の犠牲となった) - 処理フォルダ指定で、複数ファイル一気に置換
仕様
- ロックや非表示シートは無視して処理する
## 本体（Excel内テキスト置換ツール.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>置換条件入力<span style="text-decoration: line-through;">（正規表現対応）</span></li>
<li>対象文字列: <input type="text" id="targetStr" /></li>
<li>置換文字列: <input type="text" id="replaceStr" /></li>
<br/>
<li><input type="checkbox" id="autoShapes" /><label for="autoShapes">オートシェイプ内も置換する</label></li>
<br/>
<li id="wrapper"><input type="button" onClick="execute()" value="実行"/></li>
<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 = 350;
var baseHeight = 400;
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(
"SUBSTITUTE(" + rng.address + "&\"\"," + target + "," + rep + ")"
);
rng.Value = newData;
},
replaceShapeValue : function ( sheet, target, replaceStr ) {
$.mapX( sheet.Shapes , function( shape ) {
if ( shape.TextFrame2.HasText ) {
var textRange = shape.TextFrame2.TextRange;
var text = textRange.Text;
if ( text && new RegExp(target).test( text ) ) {
textRange.Text = text.replace( target, 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 = "REP";
// target folder.
var targetFolderPath = _("#folderPath").value;
if ( !targetFolderPath ) {
return;
}
var currentPath = $.Fso.GetAbsolutePathName(".");
_("#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 replaceInfo = {};
replaceInfo.targetStr = _("#targetStr").value;
replaceInfo.replaceStr = _("#replaceStr").value;
replaceInfo.autoShapes = _("#autoShapes").value;
// 各ファイルのループ処理
var done = 0;
var target = replaceInfo.targetStr;
var rep = replaceInfo.replaceStr;
$.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;
if ( replaceInfo.autoShapes ){
$.Xlsx.replaceShapeValue( sheet, target, rep );
}
$.Xlsx.replaceCellValue( ole, getTrueDataRange(sheet), target, rep );
});
// 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 getTrueDataRange(sheet) {
var xlValues = -4163;
var xlPart = 2;
var xlByRows = 1;
var xlByColumns = 2;
var xlPrevious = 2;
var lastRow, lastCol;
var usedRange = sheet.UsedRange;
var usedRangePlus = usedRange.Resize(usedRange.Rows.Count +1, usedRange.Columns.Count +1);
var lastCellInUsed = usedRangePlus.Cells(usedRangePlus.Cells.Count);
var foundRow = usedRangePlus.Find("*", lastCellInUsed, xlValues, xlPart, xlByRows, xlPrevious);
lastRow = (foundRow != null) ? foundRow.Row : 1;
var foundCol = usedRangePlus.Find("*", lastCellInUsed, xlValues, xlPart, xlByColumns, xlPrevious);
lastCol = (foundCol != null) ? foundCol.Column : 1;
return sheet.Range(sheet.Cells(1, 1), sheet.Cells(lastRow, lastCol));
}
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);
}
</script>
</body>
</html>
総括・ハマりポイント
- オートシェイプ内テキストの置換でテキスト持ってないシェイプがあるのに、はまった。
- AIが役に立った。
宿題
・fileタグでフォルダ指定して実行ボタン押下は実行のみにする
・検索時は再帰的に5階層くらい掘り下げて実行できるようにする
・getTrueDataRange はusedRange内から探すようにする
→実装した