WindowsのJScriptでExcelを読み書きするためのコードスニペット + JScript便利関数

# WindowsのJScriptでExcelを読み書きするためのコードスニペット

[WindowsでExcelをいじるコードを書く最適解](http://qiita.com/onegear0o/items/2363394e5ba23170d878)にて必要だった各種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);
}
```