WindowsのJScriptでExcelを読み書きするためのコードスニペット
WindowsでExcelをいじるコードを書く最適解にて必要だった各種JScriptのコードスニペットです。
Rubyの影響を色濃く受けているかもしれないコード内容になっているかも。
コピペで使えることを意識してはいますが、適宜変更していただければと思います。
準備
ライブラリのロード
まず、必要なライブラリをロードしましょう。
これがないと始まりません。
たくさん作ってもあれなんで、グローバルに一発ロードすればいいと思います。
fso = new ActiveXObject("Scripting.FileSystemObject");
sh = new ActiveXObject( "WScript.Shell" );
FileSystemObjectはファイルとディレクトリへのアクセスを行います。
WScriptはポップアップ画面などの便利関数に必要です。
また、他のプロセスでプログラムを動かすことができます。
JScriptの拡張
これは好き好きですが、僕は最低限必要なメソッドをArrayとObject(連想配列)のprototypeに追加しています。グローバルの汚染という考えもありますが、これらの関数なしには苦痛でしかたない自分には必須でした。
Object(連想配列)への追加
Object.prototype.each = function( callback ){
for( var e in this ){
if( e != "each" && e != "map" && e != "keys" && e != "exist"){
callback.call( this, e, this[e])
}
}
}
each
以下の構文で繰り返しがかけるようになります。
{ a: 1 , b: 2 }.each( function(key, value){ do something... } )
Array(配列)への追加
Array.prototype.each = function( callback ){
for( var i = 0, l = this.length; i < l ; ++i){
callback.call( this, this[i], i )
}
}
Array.prototype.map = function( callback ){
ret = []
for( var i = 0, l = this.length; i < l ; ++i){
ret[ret.length] = callback.call( this, this[i], i )
}
return ret
}
Array.prototype.first = function(){
return this[0];
}
Array.prototype.last = function(){
return this[this.length-1]
}
Array.prototype.select = function( callback ){
var ret = []
this.each(function(v){
if( callback.call(this, v) ){
ret[ret.length] = v;
}
})
return ret
}
each
- 要素それぞれに対して実行
[1,2,3].each(function(value, index){ ...do something... } )
map
- returnで返された要素で構成される新しい配列を返す
[1,2,3].map( function(value, index){ return value + 1 ) }
first/last
- 最初の要素、最後の要素を取得
[1,2,3].first()
[3,4,5].last()
select
- tureを返した値のみで構成された配列を取得
[1,2,3].select(function(value){ return value > 2 })
絶対パスを取得する
Excelを開くためには、絶対パスを取得する必要があります。
function absolute_path( path ){
return fso.getAbsolutePathName( path );
}
ログを出力する
プログラムの進行具合、そのほかもろもろのために、ログを出力する方法は必要です。
powershellなどから実行する場合、以下のコードで、ログが出せます
function log(m){
WScript.echo(m);
}
Excel操作
まず、開いて、そして閉じる部分
いよいよ、Excelを開いて作業をします。
Excelを開いたら、必ず閉じなければなりません。
もし開いたExcel.Applicationのプロセスを閉じないと、プロセス上でしか発見できないExcelのプロセスが残ってしまいます。
これはタスクバー右クリックから「タスクマネージャー」を呼び出し、Excelのプロセスを確認することで発見、終了可能です。
開いて、閉じる一連の作業を意識しなくても行えるようにします。
function with_excel( file_name , func ){
ole = new ActiveXObject('Excel.Application');
ole.Visible = false;
ole.DisplayAlerts = false;
var book = null;
var full_path = null;
try {
full_path = absolute_path( file_name )
book = ole.Workbooks.Open( full_path )
func( ole , book )
}catch(e){
log(e.name+":"+e.message)
}finally{
if( book != null){
book.Close();
}
ole.Quit();
}
}
catchでエラーが発生した場合、そのエラーをlogで出力するようにしてあります。
また、最終的には必ずquitを呼び出すようにしています。
Visible は false にすることで、エクセルを開く部分を見せません。
DisplayAlerts は false にすることで、エクセルからの警告(編集中だよ？とか)を無視して、途中で止まらないようにします。
以下の様に使います。
with_excel( "test.xls", function(ole, book){
// do something with book
})
この中でbookにアクセスすることで、Excelの中身をいじることができますが、、、
このオブジェクはforでのアクセスができません。
ブック、シートにアクセスする
ブック、シートの内容を取得するために、以下のように行います。
// each with activeXObject
function x_each( obj , func ){
for( var _obj = new Enumerator( obj ); !_obj.atEnd(); _obj.moveNext() ){
var v = _obj.item();
func(v)
}
}
ActiveXObjectに対して、eachを行います。
book.Worksheets が シート郡になります。これを渡すことで、シートをeachします。
x_each(book.Worksheets, function(sheet){
// do something with sheet
})
また、mapも行えます
// map with activeXobject
function x_map( 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;
}
しかしながら、大抵の場合必要なのは、名前のわかっているシートを取得すれば足ります。
// find with activeXobject
function x_find( obj, func ){
ret = null;
for( var _obj = new Enumerator( obj ); !_obj.atEnd(); _obj.moveNext() ){
var v = _obj.item();
if( func(v) ){
ret = v;
}
}
return ret;
}
シートからcellを取得する
シートまで取得はできました。
ここから、シートの中身を取り出します。
結論から言うと、シートの中身をすべて配列にして取得した方がいいです。
領域の取得は、
sheet.usedrange
もしくは
sheet.range("A1:A10")
のようにして、rangeとして取得できます。
そのrangeの、 range.rows で行を、そしてさらにその中の range.columns で cellが取り出せます。
rangeをhash(連想配列)に変換するコードは以下のようになります。
上記のx_mapを使用しています。
function range2hash(range){
var ret = []
ret = x_map( range.rows , function( row ){
var _v = x_map( 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;
}
cell.Valueで実際の値を取得します。
この際、ValueはExcelでのさまざまな値によって型が設定されて返されますが、注意すべき点は、 #REF のセルです。
Cellの型
「#REF」 とは、エクセルの場合、計算不可能だった場合に入る値です。
しかしJScriptで読み込むとき、この値の型が unknown になるのです。
よって普通に読み込むのは大丈夫ですが、出力しようとするとエラーになります。
よって上では、unknownは「#REF」の文字列に変換しています。
さらに日付もDateというもっともな感じの型で返ってくるのですが、これは実は
var today = new Date()
この「today」とは異なるので注意です。
typeof (new Date())
はobjectです。
Dateの関数(getFullYearなど)を用いるには、Excelの日付をさらにJScriptのDateオブジェクトに変換します。
var date_obj = new Date( {Cellから受け取った「Date」型の値} )
書き込む
ここまでで、中身を読み取れるようになりました。
読み取った中身の配列を自由に変更したら、次は書き込みます。
以下の様に、Cellの番地を直接指定して書き込むことができます。
function write( sheet, data, start_row, start_col ){
var row_n = start_row
data.each(function(row){
var col_n = start_col
row.each(function(value){
sheet.cells(row_n, col_n).value = value;
col_n++;
})
row_n++;
})
}
この関数を使うと、スタート地点から、dataの二次配列の内容ですべて上書きします。
これでExcelの操作できる様になりました。
そのほかの便利関数
ポップアップ
yes or no?
ユーザーに選択を迫るyes,noのポップアップを出します。
function yesno(message, title){
return sh.Popup(message,0,title,1);
}
yesの場合は[1]
noの場合は[2]
が返されます。
info
function info( message , title){
return sh.Popup(message,0,title,0);
}
普通の情報windowはこちらです。
日付情報を綺麗に表示
「yyyy/mm/dd h: m :s」
の形で綺麗に日付を表示します。
function date2str(today){
if( typeof today == "undefined") today = new Date()
var dd = today.getDate();
var mm = today.getMonth()+1; //January is 0!
var h = today.getHours();
var m = today.getMinutes();
var s = today.getSeconds();
var yyyy = today.getFullYear();
if(dd<10){
dd='0'+dd
}
if(mm<10){
mm='0'+mm
}
if(h<10){
h='0'+h
}
if(m<10){
m='0'+m
}
if(s<10){
s='0'+s
}
return yyyy+'/'+mm+'/'+ dd + " " + h + ":" + m + ":" + s;
}
require or import
これはExcel操作とは関連がありませんが、必要な処理でした。
別ファイルの内容をrequire、もしくはimportのようにもってくる処理です。
ぶっちゃけてしまうと、ファイルの内容を全部読み込んで、その内容をevalする、というものです。
注意しなくてはならないのは、グローバルスコープでeval()を直接書かないと、グローバル空間に関数等が読み込まれない、ということです。
出典は覚えておらず、今ざっと探して見つかりませんでしたが、いずこかにアップされていたソースを少し改造したものです。
このコードを末尾に書いたファイルから、他のファイルを読み込むことができます。
fso としてFileSystemObjectが必須です。
eval( load("utility.js") )
// --- 以下を末尾においておく ---
function load(scriptfile) {
var readStream = file2readStream(filepath2file(scriptfile));
var returnValue = '';
try {
returnValue = readStream.readAll();
} finally {
readStream.close();
}
return returnValue;
}
function filepath2file(filepath) {
var aFileSystemObject = fso
if (!aFileSystemObject.fileExists(filepath))
aFileSystemObject.createTextFile(filepath, true); // 2nd arg : overwrite
return aFileSystemObject.getFile(filepath);
}
function file2readStream(file) {
var ForReading = 1;
var TristateUseDefault = -2;
return file.openAsTextStream(ForReading, TristateUseDefault);
}