JScriptでExcel操作（その１） #Excel

はじめに

JScriptで「Excel.Application」を解説しようと思ったんですが
いかんせん、プロパティとメソッドが多すぎでした

それから内容を突き詰めていくと、JScriptっていうよりVBAなんじゃないか
という疑念が発生しました

サンプルコードを作って、それを解説していきます

サンプル

以下のようなExcelファイルが複数あります

	A	B
1	店舗名	店舗A
2	りんご	100
3	みかん	100
4	ぶどう	100

これを以下のような形にしてExcelを作成します

	A	B	C	D	E
1	店舗名	店舗A	店舗B	店舗C	合計
2	りんご	10	100	1000	1110
3	みかん	20	200	2000	2220
4	ぶどう	30	300	3000	3330

サンプルコードは以下のようになります

function logic(out, files) {
    // メッセージ
    var msg = "";
    // Excelオブジェクト
    var excel = null;
    try {
        // Excelオブジェクト生成
        var excel = new ActiveXObject("Excel.Application");
        // デバッグ用（表示）
        //excel.Visible = true;
        // 出力Workbook
        var outBook = null;
        try {
            // 出力ファイル作成
            outBook = excel.Workbooks.Add();
            // 挿入列
            var col = 1;
            // 挿入シート取得
            var outSheet = excel.Sheets(1);
            // 初期値設定
            outSheet.Cells(1, col).Value = "店舗名";
            outSheet.Cells(2, col).Value = "りんご";
            outSheet.Cells(3, col).Value = "みかん";
            outSheet.Cells(4, col).Value = "ぶどう";
            outSheet.Name = "集計";
            // 挿入列インクリメント
            col++;
            // ファイルを開く
            var book = null;
            // ファイル数分ループ
            for (var i = 0; i < files.length; i++) {
                try {
                    // 読み取り専用で開く
                    book = excel.Workbooks.Open(files[i], 0, true);
                    // 1シート目を取得
                    var sheet = book.Sheets(1);
                    // データ最大行取得
                    var maxrow = sheet.Cells(1, 1).SpecialCells(11).Row;
                    // データ最大列取得
                    var maxcol = sheet.Cells(1, 1).SpecialCells(11).Column;
                    // シートサイズチェック
                    if (maxrow >= 4 && maxcol >= 2) {
                        // データ転記
                        outSheet.Cells(1, col).Value = sheet.Cells(1, 2);
                        outSheet.Cells(2, col).Value = sheet.Cells(2, 2);
                        outSheet.Cells(3, col).Value = sheet.Cells(3, 2);
                        outSheet.Cells(4, col).Value = sheet.Cells(4, 2);
                    }
                } catch (e) {
                    // エラーの場合
                    msg += "\"" + files[i] + "\" Error(" + (e.number & 0xFFFF) + "):" + e.message + "\n";
                } finally {
                    // Workbookオブジェクト存在チェック
                    if (book != null) {
                        // Workbookを閉じる
                        book.Close(false);
                        book = null;
                    }
                }
                // 挿入列インクリメント
                col++;
            }
            // 合計関数設定
            outSheet.Cells(1, col).Value = "合計";
            outSheet.Cells(2, col).Formula = "=SUM(B2:" + outSheet.Cells(2, col - 1).Address(false, false) + ")";
            outSheet.Cells(3, col).Formula = "=SUM(B3:" + outSheet.Cells(3, col - 1).Address(false, false) + ")";
            outSheet.Cells(4, col).Formula = "=SUM(B4:" + outSheet.Cells(4, col - 1).Address(false, false) + ")";
            // 罫線
            var range = outSheet.Range(outSheet.Cells(1, 1), outSheet.Cells(4, col));
            // 罫線用変数（左：7、上：8、下：9、右：10、水平線：11、垂直線：12）
            var lines = [7, 8, 9, 10, 11, 12];
            // 罫線を引く
            for (var i = 0; i < lines.length; i++) {
                var border = range.Borders(lines[i]);
                border.LineStyle = 1;
            }
            // 列の幅を揃える
            range.ColumnWidth = 20;
            //range.Columns.AutoFit(); // 自動でやる場合
            // 互換性チェックオフ
            outBook.CheckCompatibility = false;
            // 警告ダイアログオフ
            excel.DisplayAlerts = false;
            // 保存
            outBook.SaveAs(out);
        } catch (e) {
            // エラーの場合
            msg += "\"" + out + "\" Error(" + (e.number & 0xFFFF) + "):" + e.message + "\n";
        } finally {
            // Workbookオブジェクト存在チェック
            if (outBook != null) {
                // クリップボードは無視
                excel.CutCopyMode = false;
                // Workbookを閉じる
                outBook.Close(false);
                outBook = null;
            }
        }
    } catch (e) {
        // エラーの場合
        msg += "Error(" + (e.number & 0xFFFF) + "):" + e.message + "\n";
        // Excelオブジェクト存在チェック
        if (excel != null) {
            // 見えないと困るので、表示
            excel.Visible = true;
        }
    } finally {
        // Excelオブジェクト存在チェック
        if (excel != null) {
            // 警告ダイアログ表示
            excel.DisplayAlerts = true;
            // Excelを閉じる
            excel.Quit();
            excel = null;
        }
    }
    return msg;
}

引数outは、出力ファイルパスです
引数filesは、対称ファイルパス配列です

データの範囲

データが存在しているかをデータの範囲で判断しています。
その為、存在しているデータの範囲を予め取得しています。

sheet.Cells(1, 1).SpecialCells(11)の「11」は
「xlCellTypeLastCell」（使われたセル範囲内の最後のセル。）です。

アドレス

集計関数（SUM）を使うにあたり、A1参照形式でセルのアドレスが必要となる為
outSheet.Cells(2, col - 1).Address(false, false)
といった関数で取得しています。

罫線

罫線の設定で斜め線以外すべて取得しています。
var border = range.Borders(lines[i]);

名前	値	説明
xlDiagonalDown	5	斜め
xlDiagonalUp	6	斜め
xlEdgeBottom	9	下部
xlEdgeLeft	7	左部
xlEdgeRight	10	右部
xlEdgeTop	8	上部
xlInsideHorizontal	12	水平
xlInsideVertical	11	垂直

また、実線に設定しています。
border.LineStyle = 1;

名前	値	説明
xlContinuous	1	実線
xlDash	-4115	破線
xlDashDot	4	一点鎖線
xlDashDotDot	5	ニ点鎖線
xlDot	-4118	点線
xlDouble	-4119	2 本線
xlLineStyleNone	-4142	線なし
xlSlantDashDot	13	斜破線

保存

保存時に警告系のダイアログが出ると、スクリプトが停止してしまうので
一旦、警告系のダイアログを出ないようにします。

// 互換性チェックオフ
outBook.CheckCompatibility = false;
// 警告ダイアログオフ
excel.DisplayAlerts = false;

ただ、そのままだと次に開いても警告がでないままになってしまうので
Excelを閉じる時に解除します。
(互換性は、後に依存しない）

// 警告ダイアログ表示
excel.DisplayAlerts = true;