15 Mar 2015, 02:38

R で Excel のデータを読み込む方法のメモ (Windows)

はじめに

Windows 環境に R をインストールします. また, Excel データを R から読み込んでみます.

[toc]

Environment

<div class="outline-text-3" id="text-1-1">
  <ul class="org-ul">
    <li>
      windows 8.1
    </li>
    <li>
      R version 3.1.3
    </li>
  </ul>
</div>

R のインストール

以下から最新版をインストール.

インストール後, パスを通す.

[sourcecode language=”text” title=””]
C:\Program Files\R\R-3.1.3\bin\x64
[/sourcecode]

コマンドプロンプトをを開いて R と打ち込んで R プログラムが立ち上がれば OK.

文字化けする場合はコンソールで chcp 65001 と入力で UTF-8 になる.

Excel 用のパッケージ取得

xlsx パッケージを利用することで, Excel ファイルを R から読みこむことができる.

CRAN からインストール.R コンソール上で,

[sourcecode language=”bash” title=””]
install.packages ("xlsx", dependencies = TRUE)
[/sourcecode]

使い方マニュアル.

読み込みテスト

<div class="outline-text-3" id="text-3-1">
  <p>
    以下のサイトからサンプルデータ習得して読み込んでみる.
  </p>

  <ul class="org-ul">
    <li>
      <a href="http://www.lanlan.co.jp/mtuinfo/exceldata.html">Excel データサンプル</a>
    </li>
  </ul>

  <p>
    [sourcecode language=&#8221;text&#8221; title=&#8221;&#8221;]<br /> require (xlsx)
  </p>

  <p>
    # read excelsample.xls sheet1<br /> data <- read.xlsx ("C:\\Users\\tsu-nera\\Downloads\\excelsample.xls", 2)<br /> head (data)<br /> [/sourcecode]
  </p>
</div>

<div id="outline-container-sec-3-1-1" class="outline-4">
  <h4 id="sec-3-1-1">
    result
  </h4>

  <div class="outline-text-4" id="text-3-1-1">
    [sourcecode language=&#8221;text&#8221; title=&#8221;&#8221;]<br /> 仮設工事 1 1 401350 320888 401350 79.953 100 401350 320888 401350 33.83 0<br /> 基礎工事及び土工事 1 1 7861394 6296746 7854794 80.098 99.917 7861394 6296746 7854794 0 0<br /> 屋外付帯工事 1 1 435800 348640 435800 80 100 435800 348640 435800 0 0<br /> 木工事 1 1 515720 412576 515720 80 100 515720 412576 515720 0 0<br /> 外壁工事 1 1 3239500 2575650 3207600 79.508 99.016 3239500 2575650 3207600 0 0<br /> 屋根及び板金工事 1 1 402400 321920 402400 80 100 402400 321920 402400 0 0<br /> [/sourcecode]
  </div>
</div>

その他

RExcel を利用すると R へ Excel データを読み込まずとも, Excel 上から R を呼び出すことも可能.

13 Apr 2014, 06:51

ExcelVBA統合BDD開発環境をrakeでまとめてみた

ExcelVBAで統合BDD開発環境を構築したメモです。

この記事は以下の記事の続きです。

機能

開発環境というところが指す意味は、 rakeをつかってVBA開発で役立つ機能をまとめたということです。

以下のような機能が実装済み。


rake clean    # Remove any temporary products.
rake clobber  # Remove any generated file.
rake export   # export all files to specified dir
rake hide     # Hide Excel
rake import   # import All Modules
rake open     # Open or Connect Excel File
rake release  # Make releasee excel file
rake save     # Save Excel File
rake show     # Show Excel
rake spec     # Run All Tests
rake step     # Count Steps in Project
rake vbe      # Open Visual Basic Editor for Application

rakeからExcelマクロを実行する

<div class="outline-text-3" id="text-1-1">
  <p>
    ruby の win32oleを利用して、Excelのマクロを実行するところがこのツールのキモ。
  </p>

  <pre><code>@book.run("ShowTotalCodeLinesInProject")

  <p>
    こんな感じで、runメソッドをつかって実行する。
  </p></p>
</div></p>

BDD実行エンジン

<div class="outline-text-3" id="text-1-2">
  <p>
    実は、VB Lite Unitが会社の環境では使えなかった。おそらく、Excelが64bit版なことが原因。 ということで、代替手段を探したところ、コンナツールを発見。
  </p>

  <ul class="org-ul">
    <li>
      <a href="https://github.com/timhall/Excel-TDD">timhall/Excel-TDD</a>
    </li>
  </ul>

  <p>
    記法がRSpecに似ていて、しかも純VBAで書かれている。これを採用。
  </p>

  <p>
    これと、VBAコードの import/export機能の組み合わせによって
  </p>

  <ol class="org-ol">
    <li>
      Emacsで編集
    </li>
    <li>
      rake spec -> Excelにコードがインポートされてテスト実行
    </li>
    <li>
      イミディエイトウィンドウで結果を確認。
    </li>
    <li>
      バグってたらデバッガでステップ実行とか
    </li>
    <li>
      テストが成功したら、rake exportしてgitにcommit
    </li>
  </ol>

  <p>
    というTDDサイクルを回すことができるようになった。
  </p>

  <p>
    Excelを起動していない状態で rake specを実行すると、 メモリ不足でテストが実行できないというバグがある。(というより解消方法がわからない) rake openしたあとに、一旦 rake saveをすることで回避できる。
  </p></p>
</div></p>

フォルダ構造

<div class="outline-text-3" id="text-1-3">
  <p>
    src/spec/helperの3つに分けた。
  </p>

  <p>
    生産物はsrcにいれる。テストコードは specフォルダに入れる。helperはお助けツール。
  </p>

  <p>
    specの命名規則はxxx_specとする。 ファイル名が_specかどうかでテストコードを判定しているので。 本当はもっと設定ファイルとかで設定させたいけど、そのうち。
  </p></p>
</div></p>

コード

Environment

<div class="outline-text-3" id="text-2-1">
  <ul class="org-ul">
    <li>
      Windows 7 64bit
    </li>
    <li>
      Excel 2010
    </li>
  </ul>

  <p>
    ツールバーから 参照設定を選択肢、以下を追加する必要あり。
  </p>

  <ul class="org-ul">
    <li>
      Microsoft Visual Basic for Application Extention
    </li>
  </ul>
</div></p>

12 Apr 2014, 17:07

ExcelVBAのステップ数をカウントする

Excel VBAのステップ数を数えるツールを探そうとおもったけれども、勉強のために自作してみる。

[toc]

ステップ数を数える方法について

ソースコードから以下の情報を得たい。

  • 全体のステップ数
  • 実行ステップ数
  • コメント行
  • 空白行

こんな出力結果を得たい。


--------------------------
  FileName    Exxecute
--------------------------
 hogehoge.cls       10
 hugahuga.bas       10
--------------------------
 Sum                20
--------------------------

サンプルを探してみた。以下のサイトのコードが良さそうだ。

テンプレートエンジンをつかってみる

テンプレートエンジンについて

<div class="outline-text-3" id="text-2-1">
  <p>
    テンプレートエンジンとは、データとテンプレートからドキュメントを作成するもの。
  </p>

  <ul class="org-ul">
    <li>
      <a href="http://ja.wikipedia.org/wiki/%E3%83%86%E3%83%B3%E3%83%97%E3%83%AC%E3%83%BC%E3%83%88%E3%82%A8%E3%83%B3%E3%82%B8%E3%83%B3">テンプレートエンジン &#8211; Wikipedia</a>
    </li>
  </ul>

  <p>
    VBAで利用できるテンプレートエンジンは、「Mini Templator」が唯一の選択肢のようだ。
  </p>
</div>

Mini Templatorについて

<div class="outline-text-3" id="text-2-2">
  <p>
    Mini TemplatorはHTML用のテンプレートエンジン。 VBAだけではなくて、Java, PHPでもつかえる模様。オープンソース。
  </p>

  <ul class="org-ul">
    <li>
      <a href="http://www.source-code.biz/MiniTemplator/">MiniTemplator &#8211; A compact template engine for HTML files</a>
    </li>
  </ul>

  <p>
    上記サイトからVBA用のファイル一式をダウンロード。 なかにあるMiniTemplator.clsをインポートする。
  </p>

  <ul class="org-ul">
    <li>
      MiniTemplator_vbasic.zip <ul class="org-ul">
        <li>
          MiniTemplator.cls
        </li>
      </ul>
    </li>
  </ul>

  <p>
    ドキュメントは以下。
  </p>

  <ul class="org-ul">
    <li>
      <a href="http://www.source-code.biz/MiniTemplator/vbasic/MiniTemplator.htm">MiniTemplator Class API Documentation</a>
    </li>
  </ul>
</div>

Mini Templatorのつかいかた

<div class="outline-text-3" id="text-2-3">
  <p>
    キホンは以下の4ステップ
  </p>

  <ol class="org-ol">
    <li>
      テンプレートエンジン生成
    </li>
  </ol>

  <pre><code>  Dim templator As New MiniTemplator

  <ol class="org-ol">
    <li>
      テンプレート読み込み
    </li>
  </ol>

  <pre><code> templator.ReadTemplateFromFile STEPCOUNTE_TEMPLATE_PATH

  <ol class="org-ol">
    <li>
      データ入力
    </li>
  </ol>

  <p>
    3.1 変数代入 3.2 ブロック代入
  </p>

  <pre><code>    templator.SetVariable "filename", vbcComp.Name
templator.SetVariable "execute", vbcLine
templator.SetBlock "file"</code></pre>

  <ol class="org-ol">
    <li>
      データを文字列 or ファイルに出力
    </li>
  </ol>

  <pre><code>  Debug.Print templator.GenerateOutputToString</code></pre>
</div>

テンプレートを用意

<div class="outline-text-3" id="text-2-4">
  <p>
    こんな感じでテンプレート用意。
  </p>

  <pre><code>-------------------------

FileName} Execute

${filename} ${execute}

————————-

Sum ${execute}

————————-

コード

<p>
</p>

<h3 id="sec-2-6">
  出力結果
</h3>

<div class="outline-text-3" id="text-2-6">
  <p>
    ずれずれ・・・もともとがHTMLようなので、フォーマット出力なんて考えてないようだ。
  </p>

  <p>
    テキスト出力はつらい。
  </p>

  <pre><code>-------------------------

FileName} Execute

ThisWorkbook 39 Sheet2 0 Calc 3 Calc_spec 11 FileManager_spec 47 InlineRunner 112 MiniTemplator 608 SpecDefinition 62 SpecExpectation 164 SpecRunner 4 SpecSuite 42 StepCounter 64

FileManager 129

Sum 1285

printf的なものはないかな

C言語でのprintfにあたる機能がVBAにないものか、と探したらあった。

このツールが良さそうだ。

上記からダウンロードして、PrintF.basをインポート。

使い方も、C言語のprintfと同じ。

コード

<p>
</p>

<h3 id="sec-3-2">
  出力結果
</h3>

<div class="outline-text-3" id="text-3-2">
  <p>
    完璧、素晴らしい。
  </p>

  <pre><code>-----------------------

FileName Execute

ThisWorkbook 39 Sheet2 0 Calc 3 Calc_spec 11 FileManager_spec 47 InlineRunner 112 MiniTemplator 608 mdlPrintF 282 SpecDefinition 62 SpecExpectation 164 SpecRunner 4 SpecSuite 42 StepCounter 51

FileManager 129

Sum 1554

Special Thanks

<div class="outline-text-3" id="text-3-3">
  <ul class="org-ul">
    <li>
      <a href="http://d.hatena.ne.jp/lam_bda/20081124/1227502941">会社でRuby使用禁止になったのなら、VBAでテンプレートエンジンを使えばいいじゃない &#8211; ランバダ</a>
    </li>
    <li>
      <a href="http://wasuke.shioya.jp.net/archives/=344">MiniTemplator VBAで使えるテンプレートエンジン &#8211; わすけのブログわすけのブログ</a>
    </li>
    <li>
      <a href="http://www.1stdegree.co.jp/blog/yokomaku/archives/=103">横のマーク &#8211; EXCELのVBAでsprintf</a>
    </li>
  </ul>
</div>

12 Apr 2014, 12:37

ExcelVBAのコードをimport/exportする

Excel VBAの最大の課題が、保守性。 Excelの中にコードが縛られている。このコードを外に出すと、

  • バージョン管理ができる
  • VimやEmacsなどのエディタで開発できる
  • テスト駆動開発できる

などなど。

レガシーなExcelVBAもバージョン管理、テスト、自動化という 現代ソフトウェア開発の三種の神器に従って開発ができる。

調べてみると、同じようなことを考えている人たちがいた。

これらの方法を利用してもいいのだが、 現在VBA絶賛勉強中のため、自分の学習も兼ねて、 VBAのコードをExcel内からimort/exportするコードを書いてみた。

苦労した点とか

Rubyの win32oleがつかえなかった

<div class="outline-text-3" id="text-1-1">
  <p>
    はじめはRubyでやっちゃえと思ったけど、どうもwin32oleではメソッドがみつからなかった。
  </p></p>
</div></p>

Excel Objectについて

<div class="outline-text-3" id="text-1-2">
  <p>
    標準モジュール、クラスモジュールはImpot/Exportができるのだが、 ThisWorkbookやSheetなどのExcel Objectはそれができない。 これらは、行の削除と挿入で対応した。
  </p>

  <pre><code>Private Sub InsertLines(myFile As String)

Dim myFSO As New FileSystemObject Dim myBaseName As String: myBaseName = myFSO.GetBaseName(myFile)

With ThisWorkbook.VBProject.VBComponents(myBaseName).CodeModule .DeleteLines StartLine:=1, count:=.CountOfLines .AddFromFile myFile

' Delete header lines
.DeleteLines StartLine:=1, count:=4

End With

Set myFSO = Nothing End Sub

自分で自分をリロードできない

<div class="outline-text-3" id="text-1-3">
  <p>
    VBAで書くと、自分自身のコードをimport/exportすることができない。
  </p>

  <p>
    なので、ThisWorkbookとFileManagerという二種類のファイルに処理を分けて 2段階でインポートすることにした。
  </p></p>
</div></p>

Environment

<div class="outline-text-3" id="text-1-4">
  <ul class="org-ul">
    <li>
      Windows 7 64bit
    </li>
    <li>
      Excel 2010
    </li>
  </ul>

  <p>
    </div>
  </p>
</div></p>

最後に

<div class="outline-text-2" id="text-2">
  <p>
    このベージの解説が素晴らしい。本当に、Special Thanks.
  </p>

  <ul class="org-ul">
    <li>
      <a href="http://rsh.csh.sh/text-scripting-vba/">vi で書こう VBA — Text Scripting on VBA</a>
    </li>
  </ul>
</div></p>

22 Mar 2014, 05:56

RubyからExcelを操作する方法について

RubyからExcel操作をする手順について調べてみた。

RubyからExcelを操作するライブラリ

RubyからExcelやスプレットシートを操作するライブラリはいくつかあるようだ。

RubyでExcelファイルを扱うライブラリの比較 – Qiita

標準ライブラリに含まれているものは、Win32OLEというものなので、これを試す。

環境

<div id="text-1-1" class="outline-text-3">
  <ul class="org-ul">
    <li>
      Windows 7 64bit
    </li>
    <li>
      cygwin
    </li>
    <li>
      Ruby 2.0
    </li>
  </ul>
</div>

Win32OLE

Win32OLE は、COM とか ActiveX などと呼ばれたりする技術を扱うためのライブラリ。

COM オブジェクトは WIN32OLE::new(ProgID)という形式で呼び出す。 Excelの場合は、ProgIDは’Excel.Application’となる。

利用できるメソッドは以下のコマンドで表示できる。

$ ruby -r win32ole -e "excel = WIN32OLE.new('Excel.Application');puts excel.ole_methods;"

Excelオブジェクトの整理

  • Application ・・・ Excelオブジェクト本体
  • WookBook ・・・ Excelワークブック
  • WookSheet ・・・ Excelワークシート
  • Range ・・・ Excelのセルやセルの集合。
  • Excelのクラスの説明

やることリスト

  • xlsx拡張子の既存ファイルを開く
  • 既存ファイルから情報を読みこむ
  • 既存ファイルから情報を書き込む
  • ファイルを保存する

ファイルオープン

<div id="text-2-1" class="outline-text-3">
  <p>
    Win32OLEを利用するには、wiin32oleをrequireする。
  </p>

  <div class="org-src-container">
    <pre class="src src-ruby">require <span style="color: #e6db74;">'win32ole'</span>

<div id="outline-container-sec-2-1-1" class="outline-4">
  <h4 id="sec-2-1-1">
    Applicationオブジェクト
  </h4>

  <div id="text-2-1-1" class="outline-text-4">
    <p>
      excelオブジェクト
    </p>

    <div class="org-src-container">
      <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">Excelオブジェクト生成</span>

excel = WIN32OLE.new(‘Excel.Application’) # FileSystemObject生成 fso = WIN32OLE.new(‘Scripting.FileSystemObject’) # デバッグ用表示 excel.visible = DEBUG_SHOW

<div id="outline-container-sec-2-1-2" class="outline-4">
  <h4 id="sec-2-1-2">
    WorkBookオブジェクト
  </h4>

  <div id="text-2-1-2" class="outline-text-4">
    <p>
      bookオブジェクトを作成してファイルを開く。
    </p>

    <div class="org-src-container">
      <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">指定したファイルを開く</span>

book = excel.Workbooks.Open(fso.GetAbsolutePathName(FILE_PATH))

<div id="outline-container-sec-2-1-3" class="outline-4">
  <h4 id="sec-2-1-3">
    WorkSheetオブジェクト
  </h4>

  <div id="text-2-1-3" class="outline-text-4">
    <p>
      シートの取得方法は以下。コレクションに従った記法もできるところが嬉しい。
    </p>

    <div class="org-src-container">
      <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">一番左のシートを取得</span>

sheet = book.Worksheets(1)

# シート名が “Sheet1” のシートを取得 sheet = book.Worksheets(“Sheet1”)

# bookに属するそれぞれのSheetについて操作 book.Worksheets.each {|sheet| puts sheet.Name }

    <ul>
      <li>
        <a href="http://d.hatena.ne.jp/maluboh/20070708#p1">2007-07-08 &#8211; まるぼ~の日記</a>
      </li>
    </ul>
  </div>
</div>

<div id="outline-container-sec-2-1-4" class="outline-4">
  <h4 id="sec-2-1-4">
    Rangeオブジェクト
  </h4>

  <div id="text-2-1-4" class="outline-text-4">
    <p>
      セルにアクセスする方法は以下。
    </p>

    <div class="org-src-container">
      <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">D5にアクセス</span>

cell = sheet.Cells.Item(4, 5) cell = sheet.Cells.Item(“5”, “D”) cell = sheet.Range(“D5”)

    <p>
      eachを利用してうまく処理するのがRubyをつかういいところ。
    </p>

    <div class="org-src-container">
      <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">列ごとに処理</span>

sheet.UsedRange.Rows.each do |row| # セルごとに処理 row.Columns.each do |cell| end end

# A1 ~ A5 を取得する方法 cells = sheet.Range(“A1:A5”) cells.each do |cell| end

指定したセルの読み書き

<div id="text-2-2" class="outline-text-3">
  <p>
    Valueメソッドで値を書き換える
  </p>

  <div class="org-src-container">
    <pre class="src src-ruby"><span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">値の読み込み</span>

puts cell.Value

# 値の書き込み cell.Value = “HogeHoge”

ファイルを保存する

<div id="text-2-3" class="outline-text-3">
  <p>
    saveメソッドで上書き保存ができる。
  </p>

  <div class="org-src-container">
    <pre class="src src-ruby">book.save

  <p>
    Excel操作中に異常終了してしまうと、プロセスが残ってしまう。 Rubyの例外処理の仕組みをつかうのがTips
  </p>

  <div class="org-src-container">
    <pre class="src src-ruby">  <span style="color: #8f8f8f;"># </span><span style="color: #8f8f8f;">始め処理</span>

begin # 行いたい処理 sheet = book.Worksheets(1) sheet.Cells.Item(2, “C”).Value = “HogeHoge” ensure # 終わり処理 book.Close excel.Quit end

結果

Links