rubyでExcel用xmlssファイルを作成するためのライブラリ exlap_c

最終更新日： 2011/06/28

exlap_cは、Excelで扱えるxmlスプレッドシートファイル(xmlss)を作成するために設けたruby用のライブラリです。

最新のZIPアーカイブ：　exlap_c130.zip（ver 1.3）

ver 1.2.4 から 1.3 にかけて、かなりの修正を施しました。詳しくは exlap_c.txt を参照していただくとして、主な点は次のとおり。

• 構造体的Hashを採用した。これにより、cell.Font.Size のような記述が可能になった。
• ワークシート内の一定の領域を扱うための ExlRangeクラスを新たに設けた。また、それに合わせて ExlSheetクラスににExlRangeに関連するメソッドをいくつか設けた。
• ワークシートが空の時に、適切なxmlssを出力できなかったバグを修正。
• 既存のxmlssを取り込んだ時に、その文字コードがutf-8のままになっていたため(つまりベース文字コードと一致しなかったため)、文字化けになるケースがあったバグを修正。

特徴

• csv、webのtable部分、xmlssを読み込んで、rubyの２次元配列に変換する仕組みを含んでいます。また、その２次元配列をcsv、web、xmlssとして書き出すメソッドも用意されています。つまり csv, web, xmlss を相互に変換できます。
• 基本的にはrubyの標準添付ライブラリ nkf, csv, open-uri, rexmlを使っているだけなので、MS-Windowsに限らずいろいろなＯＳで使えます(Webを扱う時は htmlパーサとしてnokogiriかhpricotが必要)。
• いわゆる「001問題」(左側の0が消えてしまう)とか「セル結合」に対応しています。
• font(文字の大きさや色)、alignment(文字の配置)、borders(罫線)などの指定が可能です。
• 列の幅、行の高さを指定できます。
• Excelの計算式や関数をセルに埋め込むことができます。

(注)　ここでいうxmlssは、SpreadsheetMLの仕様に従って書かれたxmlです。
　Excel2002(Office XP版)以降で扱えます。Excel2003以降が動く環境であれば、xmlssのファイルをクリックするだけでExcelが起動します。

動作環境

ruby ver 1.8.x または 1.9.x で動作します。

rubyが使える環境であれば、ＯＳは問わないと思います。

動作確認は、rubyのバージョンごとに次の環境で行いました。

• ruby 1.8.7p302: Windows XP|VISTA
• ruby 1.8.7p248: FreeBSD 7.2
• ruby 1.9.1p429: Windows XP|VISTA

なお、webの解析には htmlパーサとして hpricot または nokogiri を用います。これらは、rubyのライブラリです。webのtableを扱うメソッドを利用するには、少なくともどちらか１つをインストールしておく必要があります。
hpricot ver 0.8.2 および nokogiri ver 1.4.3.1 で動作確認しました。

その他、詳しくは付属のドキュメント exlap_c.txt を参照して下さい。

このドキュメントをWeb上で閲覧したい方は「exlap_cの使い方」からどうぞ。

また、当ライブラリを利用したサンプルを「exlap_cのサンプル集」に掲載してあります。

それから、「クロス集計表をカイ二乗(χ2)検定するためのxchi」もexlap_cのサンプルになっていますので、よかったら参考にして下さい。

サーバサイドにあるxmlssをブラウザまたはExcelで表示する方法のヒントについては、例えば、次のサイトが参考になります。

• たのしいXML: XMLをOfficeで扱う(xsltを利用してxmlssをＩＥで表示できるようにする例)
• [OFF2003] ASP を使用してクライアント側で表示する XML スプレッドシートを作成する方法(Excelの「開く」でurlを指定する方法など)

バージョンアップ履歴

□ exlap_c ver 1.2.3 → 1.2.4

他のライブラリとメソッド名が衝突しないよう、クラスに属していないメソッドをmodule化しました。それ以外の変更はありません。機能的には v1.2.3 と同じです。

以下のメソッドをmodule Exl の下に収納しました。そのため、それらメソッドを使う時は予め include Exl するか、もしくは、Exl::eData(……) のようにメソッド名の前に「Exl::」を付けます。

• eData, eFont など、小文字の 'e' に続いて大文字がくるメソッド
• getData(２次元配列の要素のデータを得る)
• convert_for_csv(２次元配列をcsv用に変換)
• arrange_array(２次元配列の文字コードや形式を変換)

なお、上記以外のクラスに属さないメソッドは、module ExlPrivate の下に集約しました。

□ exlap_c ver 1.2.2 → 1.2.3

主な変更点は次の４つ。今回の眼目は、exlap_cライブラリが取り扱わない情報の取込み／書き出しを行えるようにしたこと。これにより、例えば、印刷レイアウト情報の付加、ドロップダウンリスト(選択入力欄)設定等が可能になる。

• ふりがなの表示／非表示の指定を行えるようにした。
• ハイパーリンクの指定を行えるようにした。
• 各々のセルのNamedCell情報を扱えるようにした(名前付き範囲の設定に必要)。
• exlap_cライブラリが取り扱わない情報の取込み／書き出しを行えるようにした。

ふりがなは、HashのPhoneticTextで指定しますが、次のようにすると、ふりがな表示を指定することになります。

    ary[0][0] = {'Data'=>"総理大臣",
        'PhoneticText'=>{'Text'=>"ソウリダイジン",
        'Visible'=>1}}

ちなみに、非表示のままでよければ次の記述で大丈夫です。

    ary[0][0] = {'Data'=>"総理大臣",
        'PhoneticText'=>"ソウリダイジン"}

簡易記述用メソッド ePhoneticText() を用いる場合は次のように書けます。

    eData("総理大臣", ePhoneticText("ソウリダイジン"))  # 非表示
    eData("総理大臣", ePhoneticText("ソウリダイジン", 1))  # 表示

ハイパーリンクは、HashのHRefで指定します(最初の２文字が大文字)。一例は次のとおり。

    ary[0][0] = {'Data'=>"ルビマ",
        'HRef'=>"http://jp.rubyist.net/magazine/"}

簡易記述用メソッド eHRef() を用いると次のように書けます。

    ary[0][0] = eData("ルビマ", eHRef("http://jp.rubyist.net/magazine/"))

NamedCellは、名前付き範囲の設定に関わるものです。例えば、A1欄からB2欄までの２×２の領域を名前付き範囲として設定する場合、この４つのセルにNamedCellで名前を付けます。A1欄に名前を付ける例は次のとおり。

    ary[0][0] = {'Data'=>"ルビマ", 'NamedCell'=>"ruby関連"}

簡易記述用メソッド eNamedCell() を用いると次のように書けます。

    ary[0][0] = eData("ルビマ", eNamedCell("ruby関連"))

これはA1欄１つのみを設定する例ですが、２×２の４つのセルに対して同じように設定します。

ただし、名前付き範囲を設定するためには、この他に xmlssソースにおいて

    <Names><NamedRange ……></Names>

名前付き範囲の設定は、例えば、選択入力欄(ドロップダウンリスト)を設定する時に必要になります。そのサンプルが付属の sample02.rb です。

exlap_cライブラリは、例えば、印刷用レイアウト情報の WorksheetOptions を直接扱う機能を持っていません。そこで、そうした情報をxmlssソースで取り込んだり書き出したりする仕組みを設けました。

ExlBookBaseクラスおよびExlSheetクラスにメンバー変数 @other を設けました。この変数はHashを記録するもので、初期値は {} です。

ExlBookBaseの@otherに記録されたものは、xmlss出力時にWorkbookエレメントの内側に挿入されます。ExlSheetクラスの@otherは、Worksheetエレメントの内側です。

例えば、WorksheetOptionsエレメントを記録するなら、次のようにします。

    ss = ExlSheet.new
    ss.other['WorksheetOptions'] = <<EOS
    <WorksheetOptions ……>
    <PageSetup>
    <Header ……>
    <Footer ……>
    ……
    </PageSetup>
    …………
    </WorksheetOptions>
    EOS

上のように@otherを設定した上で、output_xml() でxmlssを書き出すと、該当のワークシートに印刷用の情報が付加されます。

一方、xml_push() メソッドでxmlssを取り込む時に

    wb = ExlBook.new
    wb.xml_push("test.xml", true)

したがって、このようにしてtest.xmlを取り込んだ上で

    wb.output_xml "test02.xml"

xml_push()の第２引数は、正確には、trueでなくても「nilかfalse」以外であれば同じです。第２引数を指定しなければnilが指定されたものとみなされます。つまり @otherには何も記録されません。

xml_push()を複数回呼び出して、２つ以上のxmlssを取り込んだ場合、@otherには複数のxmlssの情報がごちゃごちゃになって記録されます(どんどん追加記録されていきます)。これはトラブルの原因になるので、@otherに情報を記録する形でxmlssを取り込む時は、１つのみに限るのが無難です。

なお、exlap_cライブラリが取り扱う情報は次のとおり。

• Workbookの内側：

  DocumentProperties(Author, LastAuthor, Company, Created, LastSaved)

  Styles(Font, Alignment, Borders, NumberFormat など)

  Worksheet(Tableのみ)

• Worksheetの内側：

  Table(これには Column, Row, Cellなどの子要素が含まれる。)

  (Table以外は対象としない。)

Workbookに属する他のエレメントとしては、OfficeDocumentSettings, ExcelWorkbook, Names などがあるようです。

Worksheetに属するものとしては、WorksheetOptions, DataValidation, Names などがあるようです。

名前付き範囲の Names は、場合によって Workbook に属したり Worksheet に属したりするようです。

DataValidationは、選択入力欄(ドロップダウンリスト)の設定に関わります。

xmlssの正確・詳細な仕様は、webでは見あたらないように思います。各エレメントの働きを知るには、Excelで書き出したxmlssから推測するしかないのでは？という気がします。

□ exlap_c ver 1.2.1 → 1.2.2

主な変更点は次の３つ。

• Formula式の出力を「有｜無」する変数$ExlapFormulaOutを設けた。
• Excelの「非表示」への対応。
• Excelの「ふりがな」への対応。
• その他、バグ修正。

　csvやwebとして出力する際に、Formulaの計算式や関数を出力するかどうかをグローバル変数$ExlapFormulaOutで制御できるようにしました。出力されると結構じゃまなので(あせ)。

　$ExlapFormulaOut の初期値は nil。

　これがnilかfalseだと、csv, web を出力する際に Formula式を出力しません。それ以外(trueなど)なら出力します。

　グローバル変数 $ExlapHiddenOut(初期値は nil)がtrueだと、csv, web を出力する際に、行や列に非表示属性(Hidden)が付いていても出力します。ちなみに、Excelによりcsvを書き出した時は非表示のものも出力されます。

　nil または false だと出力しませんが、非表示属性の付いた行や列を空欄にするだけで削除はしません。つまり、行や列の数は変化しません。

　:DELETE の場合は、空欄にするのではなく削除します。したがって、行や列の数が少なくなります。　この :DELETE の場合、セル結合のある２次元配列をweb出力する時は、意図したような出力結果にならない可能性があるので、:DELETE よりも nil の方が無難です。

　非表示の処理は、オリジナルの２次元配列に変更を加えるわけではなく、csv, web を出力する際、２次元配列のコピーを作り、それに非表示の処理を施して出力します。

　なお、非表示属性の指定は Hash で行います。例えば、ssがExlSheetクラスのオブジェクトのとき、
ss.col[0] = {'Hidden'=>1}
とすれば、第１列目が非表示となります。簡易記述用メソッド eHidden() を用いて
ss.col[0] = eHidden(1)
と書くこともできます。
ss.row[1] = eHidden(1)
とすれば、第２行目が非表示となります。

　ふりがなを扱えるようにしました。HashのPhoneticTextで指定します。

　例えば、２次元配列の１つの要素を
{'Data'=>"総理大臣", 'PhoneticText'=>"ソウリダイジン"}
のようにします。簡易記述用メソッド ePhoneticText() を設けたので、次のように書くこともできます。
eData("総理大臣", ePhoneticText("ソウリダイジン"))

　xml_push() でxmlssを取り込んだ時も、ふりがなデータが取り込まれます。

　変数$ExlapPhoneticTextOutがnilかfalseなら、csv, web出力時に、ふりがなを出力しません。それ以外(trueなど)なら出力します。デフォルトは nil です。

　ちなみに、Excelにはふりがなを取り出す関数 PHONETIC() があります。例えば、B1欄に「=PHONETIC(RC[-1])」と書き込んであると、左隣(A1欄)のふりがな情報がB1欄に表示されます。

　xml_push()でxmlssを取り込む際、Font, Alignment などのスタイル情報の取込みに失敗し、エラーが発生して中断してしまうケースがあったのを修正しました。

□　exlap_c ver 1.1 → 1.2.1

変更点は次の６つ。眼目は、xmlss, csv, html それぞれについて入力と出力の双方を行えるようにしたこと。

• xmlssを取り込めるようにした。
• htmlを出力する機能を付加。
• セル内の一部に色を付けるなどの部分的指定を可能にした。
• 表・列・行の各単位でfontやalignmentの設定ができるようにした。
• csv|html出力時に、NumberFormatに即してデータの書式を整えるようにした。
• Formula式の代替rubyスクリプトを定義できるようにした。

ExlBookクラスに、xml_push() メソッドを設け、これによりxmlssを取り込めるようにしました。

このメソッドに渡す引数は、xmlssのファイル名、url、文字列のどれかです。

引数は１つのみ渡します。カンマで区切って複数指定することはできません。ただし、メソッドを複数回呼び出すて、複数のxmlssを取り込むことは可能です。

    wb = ExlBook.new
    wb.xml_push 'test.xml'
    wb.xml_push 'http://www.hoge.com/test.xml'
    wb.xml_push '<?xml version="1.0" …… </Workbook>'

xmlssの作成者(author)、最終作成者(last_author)、所属(company)、作成日時(created)が未定義の場合、取り込んだxmlssの値がセットされますが、先に取り込んだものが優先されます。

ワークシート名が重複する場合は、適当に調整されます。

取り込んだxmlssのワークシートは、内部的に２次元配列として記録しますが、いずれの要素もHashとなります。

exlap_cライブラリで扱っていない情報は、取り込まずに捨てます。例えば、印刷用レイアウト情報等を含む WorksheetOptions は取り込みません。

取り込んだ各種データ(２次元配列、csv、html、xmlss)をhtml形式の文字列に変換する web_string(title) メソッドを設けました。ExlBookBaseクラスのメソッドです。なので、ExlBook, ExlWeb, ExlCsv の各クラスにおいて利用できます。

引数 title を与えると、それがwebページのタイトルとなります。省略すると 'no title' となります。

webに変換する際、セル結合以外の情報(font, alignment, bordersなど)は捨てます。色や文字の大きさなどは、webに反映されません。

各々のワークシートが <table> …… </table> として出力されます。

ワークシート名は、<table>の直前に <h2>……</h2> の形で出力されます。

また、各ワークシートを別々のhtmlソースとして得たい時は、web_array() を用います。このメソッドに引数はありません。このメソッドを呼び出すと、１つのワークシートが「<h2>シート名</h2> <table>シート本体</table>」の形式に変換され、それが複数記録された配列を返します。第１のシートを取り出すなら、wb.web_array[0] のようにします。

それから、webに変換したものをファイルに書き出しためのメソッド output_web(filename, title) も設けてあります。

    wb = ExlBook.new
    wb.output_web 'test.htm', 'test page'

例えば「あいうえおかきくけこ」のうち「あいうえお」だけを赤色にしたい場合、２次元配列の１つの要素を次のようにセットします。

    ary[0][0] = eData(
        '<Font html:Color="Red">あいうえお</Font><Font>かきくけこ</Font>',
        eType('String@html'))

データの型として 'String@html' のように '@html' を付加すると、そのデータはhtml形式で記述されているものとして処理されます。

html形式といっても、一般的なhtml記述のルールとは異なるように思いますが、正確な仕様は分かりません。

<I>……</I>でイタリック、Bなら太字、Uなら下線、といったタグが使えるようです。

誤って記述すると、読み込みのできないxmlssになってしまうので注意して下さい。

通常のデータは、xmlssにおいて <Data ss:Type="String">……</Data> などのようになりますが、Typeに 'String@html' を指定した場合は、<ss:Data ss:Type="String">……</ss:Data> に変換されます。

html形式の場合、当然ながら、'<' を '&lt;' に変換するといった処理は施しません。通常のデータについては、exlap_cライブラリがその種の変換を施します。

ExlSheetクラスの @row, @col は、それぞれ行、列に関する情報(行の高さ、列の幅)を記録するためのものですが、これらにFontやAlignmentなどのスタイル情報を記録できるようにしました。１列目は総て青、２列目は総て赤といった設定が可能です。

また、表全体についてのスタイル情報を記録できるように、メンバー変数 @tbl も設けました。

    ss = ExlSheet.new
    ss.tbl = eFont("Green")

色を緑に設定して、かつ、総てのセルを罫線で囲みたい時は

    ss.tbl = eFont("Green").update(eBorder())

第１列目を総て赤にしたい時は

    ss.col[0] = eFont("Red")

これまでは、NumberFormatを参照せず、単純に文字列に変換するだけでした。

例えば、データが0.5、NumberFormatが '0%' なら、Excelでは「50%」と表示されます。しかし、exlap_cライブラリでcsv出力した時は、「0.5」のままでした。これを「50%」になるように改めました。html出力時も同様です。

ExcelのNumberFormat処理に完全に対応するのは難しいのでやっていませんが、001問題、小数点以下の桁数調整、パーセント表示、数字のカンマ区切り、日付の表示(Short Date, Long Date, 典型的元号)、時刻の表示(Short Time, h:mm:ss)には対応しています。

それから、セルに計算式や関数が埋め込まれている場合、xmlssに書き込まれているデータ(不正確な可能性あり)に加えて、Formulaの式を出力するようにしました(ただし、そのFormulaの式に対応するrubyスクリプトが定義されている時は出力しません。次項参照)。

例えば、関数が埋め込まれているセルをcsvに出力した場合、「123 |=SUM(RC[-3]:RC[-1])」のように出力されます。

123は、xmlssに書き込まれていた値です(不正確かもしれません)。それに続いて半角スペースと'|'、そして '=SUM……' の関数記述が続きます。

クラスに属さないメソッド arrange_array() で２次元配列を変換した場合も、第３引数に :TO_STRING を指定すると、上記のNumberFormat, Formula を参照する形で各要素(セル)の変換が行われます。

セルにFormulaの式が埋め込まれていると、Excelで開いた時はちゃんと計算結果が表示されますが、exlap_cライブラリでcsvやwebに変換した場合は、先述したように、とりあえずの値と式を出力します。

ただし、その式の代替となるrubyスクリプトが定義されている時は、それに従って計算結果を出力します。式そのものの出力はしなくなります。

この定義は、グローバル変数 $formula(Hash)で行います。$formulaは、exlap_cライブラリをrequireした時に空のHashとして定義されます(既にHashとして定義されていれば空にしない)。

$formulaのkeyにはExcel用の式、valueにはrubyスクリプトを記録します。

例えば、左隣のセルのアルファベット大文字を小文字に変換する例は次のとおり。

    key = "=LOWER(RC[-1])"
    val = "data = getData(aa[rn][cn-1]).to_s.tr('A-Z', 'a-z')"
    $formula[key] = val

代替のrubyスクリプトにおいて、次の変数は特別の意味を持ちます。

• aa: ２次元配列
• rn: 現在の行番号(0以上)
• cn: 現在の列番号(0以上)
• data: セルに代入する値をこの変数に入れる。
• fr_scriptなど: 「fr_」で始まる変数名は、用いないで下さい。

上記以外のローカル変数は、自由に名前を付けて用いることができます。

代替のrubyスクリプトにおける漢字コードは、２次元配列のコードと同じになるよう調整されます。スクリプトを書く時に漢字コードを気にする必要はありません。

その他、getData(param)は、paramがHashならparam['Data']を返し、そうでないならparamそのものを返します。このメソッドは、exlap_cライブラリをrequireした後であればどこででも使えます。クラスには属していません。

• ２次元配列変換用メソッド arrange_array() の第３引数に :TO_HASH を指定すると、各要素がHash形式に変換されるようにしました。{'Data'=>……} の形になります。もともとHashだった時は変換されずそのままです。
• arrange_array() が２次元配列の複製を返すように改めました。これまでは浅い複製になっていたため、意図しないところで元の配列が変更されてしまう危険性がありました。

□　exlap_c ver 1.0 → 1.1 の変更点

変更点は次の３つです。(ライブラリの使い方に特に変更はありません。)

• rexmlを用いず、ダイレクトにxmlssを生成・出力するよう改めた。
• 日本語対応部分を明確化した。→他の言語向けの修正がしやすくなった？
• xmlssの省略可能箇所をなるべく除き、xmlssのサイズを抑制した。

　これにともない、ExlBookBaseクラスの script_string() メソッドを廃止しました。

　グローバル変数 $ExlapLang を設け、これにシンボル :JAPANESE を代入すると、日本語対応になります。これがデフォルトです。

　一応、$ExlapLang に :ENGLISH を代入すれば英語対応になると思いますが、動作確認はしていません。

　他の言語に対応させる場合、ライブラリスクリプトの $ExlapLang が出てくる辺りをいじれば、対応可能になるのではないかと思います。

　nkfによる漢字処理を別にすれば、言語に依存するのは主に次の２点です。

• FontName: 日本語のデフォルトは「ＭＳ Ｐゴシック」、英語は「Arial」
• 日付型の表示形式: 日本語と英語の Long Date, Short Date に対応させたつもり。

　例えば、<Row> や <Cell> には、それぞれ何行目、何列目かを明記するための Index属性がありますが、単純に連続している時は省略できます。v1.0では逐一出力していましたが、省略できる時は取り除くようにしました。

　また、空欄であっても <Data ……></Data> が出力されてしまう箇所がありましたが、出力しないよう改めました。

以上。

トップページへ