Axlxsを使って新規ファイルを作って、RubyXLで読み出す話

どんどんtipsがニッチになっていきますね。

Axlxsで作ったファイルがRubyXLで読み取れませんでした。
一度エクセルで開いて保存すると読み取れるようになる。

RubyXLでAxlxsで作成したファイルを読み込むと数値とかは辛うじて所々取れていたので文字コードかと思って色々やってみましたが、このページで解決

Strings outputted not seen by rubyXL ? Issue #349 ? randym/axlsx ? GitHub

pkg = Package.create
したら
pkg.use shared strings = true
する必要があるみたいでした。

これで無事に読みだすことができました。

AxlsxでExcelファイルをcell単位でprotectionをした話

Axlsxでエクセルと戯れる日々です。
2シート目とかにマスタデータを保持しておいて、1シート目でプルダウンでマスタデータを選ばせたりしています。
変更できないように2シート目はシート全体を保護しております。

今日はAxlsxでセルの保護をしたお話です。

これググっても全然出てきませんでした。

結論から言うとこれでできます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

require 'axlsx'
require "securerandom"

package = Axlsx::Package.new
sheet = package.workbook.add_worksheet(name: 'lists')
sheet.sheet_protection.password = SecureRandom.uuid
locked = package.workbook.styles.add_style :locked => true
unlocked = package.workbook.styles.add_style :locked => false

sheet.add_row(['品名', '単価', '数量', '計'], style: unlocked )
sheet.add_row(['にんじん',    80, 1,      '=B2*C2'], style: unlocked)

sheet.rows[0].cells[0].style = locked # A1をロック(lock cell => A1)

package.serialize('test.xlsx')

この答えの出し方が最終的にRubyのことはRubyに聞くという方式で解決したのですが、
この解決の仕方がRubyエンジニアっぽいなと思ったのでどうやってこの結論に辿り着いたかダラダラ書こうかと思います。

まずセルの保護をしたいという要件がありました。
コピペエンジニアの端くれとしてまずはググります。

行単位のロックのお話は見つけましたが、セルの保護ではいい結果がありません。
ruby on rails 3 - How do I protect header rows but allow to enter new rows using AXLSX? - Stack Overflow

テストは下手なドキュメントよりも役にたつと誰かが言っていたので(実際の使い方のサンプルが見れるので)Axlsxのテストを見てみます。
bundle open axlsxでaxlsxのインストールされたディレクトリを開いて、grep protectとかやってみます。
いくつかヒットしたのでソースを見てみますがなんのこっちゃわかりません感じです。

とりあえずgrepでひっかかった
Axlsx::CellProtection.new
をキーワードにグーグルで検索してみますが有用な情報は皆無です。

一旦途方にくれます。コーヒーとか飲みます。

おもむろに再度irbを立ち上げます。
とりあえず怪しいものがないかコンソールから確認していってみることにします

1
2
3
4
5

require 'axlsx'
package = Axlsx::Package.new
sheet = package.workbook.add_worksheet(name: 'lists')

sheet.add_row(['品名', '単価', '数量', '計'])

と打ちます。

cellに保護をかけたいのだからcellオブジェクトにプロパティがあるんじゃないかとあたりをつけます。

sheet.cellとうちます。そんなんねーよと返ってきます。
sheet.cells と打ちます。なんかコンソールに文字がたくさんでます。

A1のセルを取得してみます。
sheet.cells[0,0]とうちます。空の配列が返ってきます。
sheet.cells[0]とうちます。なんか一杯でてきます。
sheet.cells[0].classとうちます。 # = > Axlsx::Cell
お、なんかいいの返ってきました。
sheet.cells[0].methods.grep(/protect/)とかうってみます。 [:protected _methods]しか返ってきません。
一回煮詰まります。 sheet.cells[0].valueとかここで色々他のことをやってみますが、なかなかうまくいきません。

そもそもセル単位で値とか設定できるんかと思いつきます。
ググります。見つけます。

ruby on rails - Modify specific cell value using Axlsx gem given the column number and row numer - Stack Overflow

特定のセルを取得するのは以下の方法でした。
sheet.rows[0].cells[0]

sheet.rows[0].cells[0].methodsでメソッド一回全部のメソッドを出してみます。

sheet.rows[0].cells[0].pos # = > [0,0]
とか
sheet.rows[0].cells[0].reference # = > $A $1
とか面白そうなメソッドがあります。

その中にstyle=というメソッドを見つけました。
これってひょっとして行単位のロックであったロックの書式を設定できるのではないかと推測します。

そして出きたのが冒頭のコードです。

sheet.rows[0].cells[0].style = locked
だけでは不十分で
sheet.sheet _protection.password
でパスワードを設定しないといけないとか、保護をかけて必要な所をlockしたりunlockしたりしないといけないとか色々とはまりながらもなんとか答えを出せました。
今回は運も味方してくれたかなぁという気もします。

困ったときはインタラクティブにどんなメソッドがあるかどんな値が設定されているか確認しながら
試しながらやることで問題が解決できるというのは素晴らしいと思いました。

特定のキーを除いたhashを返す

今までdeleteでチクチク消したり、rejectで該当キーをひっかけてhash作り直しリしてました。

exceptなんてあったんですね。

1

{name: "藤井", age: 34, job: "developer"}.except(:age) # => { :name => "藤井", :job => "developer" }

論理削除指定のモデルをhas_manyで参照し、かつnested_attributes_forにallow_destroyを指定するも物理削除になる。

環境はこちら
activerecord(4.1.6)
rails4 acts as _paranoid (0.1.4)

has manyのリレーション先でacts as paranoid指定しているのですが、
nested attributes for リレーション先, allow destroy : true
で何故か物理削除になっていまったので調べてみました。

結論としては

lib/active record/associations/has many association.rb
の112行目で
records.each(&:destroy!)
とdestroy!で消していて、acts as _paranoidのdestroy!は物理削除になるためでした。

何故destroyに!がついているのか 上記が定義されているdelete recordsメソッドを遡ってみます。
delete recordsメソッドはlib/active record/associations/collection association.rb
のremove _recordsの中で呼ばれています(ソースでは492行目)

1
2
3
4
5
6
7
8
9

  # lib/active_record/associations/collection_association.rb
  def remove_records(existing_records, records, method)
    records.each { |record| callback(:before_remove, record) }

    delete_records(existing_records, method) if existing_records.any?
    records.each { |record| target.delete(record) }

    records.each { |record| callback(:after_remove, record) }
  end

んでこれはどこで呼ばれるかというとここで呼ばれています(ソースでは485行目)

1
2
3
4
5
6
7
8
9
10
11
12

  # lib/active_record/associations/collection_association.rb
  def delete_or_destroy(records, method)
    records = records.flatten
    records.each { |record| raise_on_type_mismatch!(record) }
    existing_records = records.reject { |r| r.new_record? }

    if existing_records.empty?
      remove_records(existing_records, records, method)
    else
      transaction { remove_records(existing_records, records, method) }
    end
  end

transactionで囲まれています。
ということでdestroyに失敗した時にロールバックするために!をつけているようです。

なのでparanoidのdestroy!をalias method chainで物理削除にしてしまうか。
上記のrecords.each(&:destroy!)をなんとか書き換えてあげると治りそうですね。

今回はこうやって直しました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

module ActiveRecord
  module Associations
    module HasManyAssociationPatch
      def delete_records(records, method)
        if method == :destroy
          records.each do |record|
            raise ActiveRecord::ActiveRecordError unless record.destroy
          end
          update_counter(-records.length) unless inverse_updates_counter_cache?
        else
          super
        end
      end
    end
  end
end

ActiveRecord::Associations::HasManyAssociation.send(:prepend, ActiveRecord::Associations::HasManyAssociationPatch)

今回ソース読んでいて知ったんですけど、has manyって削除する時に
before remove
after _remove
ってコールバックが発生するんですね。知らなかった。

Nokogiriを使って部分的にhtmlを編集する

Nokogiriを使ってhelperメソッドで特定のinputに属性(クラス名)を追加したくなったのでやってみました。

1
2
3
4
5
6
7
8
9
10
11
12
13

html = <<HTML
<div>
  <input id="hoge" class="field">

HTML

doc = Nokogiri::HTML.parse(html)

doc.css("input#hoge").each do |input|
  input['class'] += " has_error"
end

doc.to_html # => "<!DOCTYPE html PUBLIC   "-//W3C//DTD HTML 4.0 Transitional//EN  "   "http://www.w3.org/TR/REC-html40/loose.dtd  ">  n<html><body>  n<div>  n  <input class=  "field  ">  n  n</body></html>  n"

Nokogiri::HTML.parseだとDOCTYPEとか とか余計なものがついてくる。

1
2
3
4
5
6
7
8
9
10
11
12
13

html = <<HTML
<div>
  <input id="hoge" class="field">

HTML

doc = Nokogiri::HTML::DocumentFragment.parse(html)

doc.css("input#hoge").each do |input|
  input['class'] += " has_error"
end

doc.to_html # => "<div>  n  <input id=  "hoge  " class=  "field has_error  ">  n  n"

そういう場合はDocumentFragmentを使うと部分的にhtmlを使える。

handlerbarsで現在のループ回数を知る

@indexで取れます。

1
2
3
4
5


  : 
  isFirst?: 
  isLast?:

その他に@firstには最初の一回目のループの時にはtrueが
@lastには最後のループの時にはtrueが入るようです。
@indexは入力欄 _1とかの1とか表示する時に使えそうですね。

2次元配列とか多次元配列を回す時はこうですね。

1
2
3
4
5
6
7
8
9

多次元配列の回し方

ちなみにでコメントです。
という書き方もできます。

slimbarsを使ったらUnknown line indicator

勉強がてらRailsのプロジェクトでBackBone.jsでテンプレートにはhandlebarsを使って処理を書いてみました。
viewがslimなのでslimbarsで書いてます。

変数を出力しようとしたら以下のエラー
Unknown line indicator

viewファイルはこんな感じ

1
2
3

.hoge
  .fuga

ただしくはこう

1
2
3

.hoge
  .fuga
    |

hamlbarsではこう書けてしまうので、slimで書く場合は注意が必要だなと思いました。

1
2
3

.hoge
  .fuga

小ネタも小ネタでした。

しかしeachの中ではslimの記法使えないのかな？
こうやらないと出力されない・・・

1
2
3
4

select
  | 
    <option value=""></option>
  |

railsで任意のタイミングで処理を開始

おかあさんといっしょを娘が見るので、毎朝のテレビチャンネルはEテレです。

さてrailsでapp/model/concerns以下のモジュールを勝手にapp/modelにincludeしたいと考えました。
命名規則でファイルを拾って、includeすることにします。
例えば以下のファイル名をつけた場合に

app/model/user.rb
app/model/user auto load _concern.rb

userクラスに自動でuser auto load _concern.rbの内容がincludeされる感じです。

config/initializerに置きます。

1
2
3
4
5
6
7
8
9
10
11
12

  %w(models).each do |elem|
    Pathname.glob(Rails.root.join("app", elem, "concerns", "*")).each do |file|
      if file.file?
        file.basename.to_s.scan(/(.*)(_auto_load_concern.rb)$/) do |class_name|
          if class_name.present?
            model = class_name[0].classify.constantize
            model.class_eval {  include file.basename(".rb").to_s.classify.constantize }
          end
        end
      end
    end
  end

でもこのプロジェクトにはmodelクラスを拡張するプログラムがlib下にあります。
それを読み込まない限りはmodelを参照するとエラーになるようになっています(上記class _name[0].classify.constantizeでエラーとなる)

そこでlib以下のそのプログラム群を読み込んだ後に読み込むようにしたいと思います。

まず先ほどのコードを以下のようにActiveSupport.on _loadで囲みます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

ActiveSupport.on_load(:hoge) do
  %w(models).each do |elem|
    Pathname.glob(Rails.root.join("app", elem, "concerns", "*")).each do |file|
      if file.file?
        file.basename.to_s.scan(/(.*)(_auto_load_concern.rb)$/) do |class_name|
          if class_name.present?
            model = class_name[0].classify.constantize
            model.class_eval {  include file.basename(".rb").to_s.classify.constantize }
          end
        end
      end
    end
  end
end

lib下のプログラム群で処理が終わった後に以下を差し込みます。

1

  ActiveSupport.run_load_hooks :hoge

そうするとrun load hooksが呼ばれたタイミングでhogeがついていてるActiveSupport.on _loadのブロック処理が開始されます。

コンソールでローディングを表示

rubyXLで100以上シートのあるエクセルファイルを読み出すスクリプトを書いていて、
現在の処理中のシート/シートの総数でプログレスバーを表示することはしたけど、
ファイル読み込み中(5～7秒ぐらいかかる)はなんにもコンソールに表示されない状態が物足りなくて、
ファイルの読み込み中はローディングチックなのを出すようにした。

読み込みと別でThread生成して、そのThread内で1秒毎に >と > >と > > >を出している。
読み込みが完了したらThreadをkillしてる。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

def display_with_arrow(&block)
  loading_thread = Thread.start{
                              arrow_progress = %w(> >> >>>).cycle
                              loop{
                                print "  r file reading #{sprintf("%-  s3s",arrow_progress.next)}"
                                sleep 1
                              }
                            }

  rtn = block.call
  Thread.kill(loading_thread)
  rtn
end

# 読み込み
workbook = display_with_arrow do
             RubyXL::Parser.parse("エクセルファイルへのパス")
           end

するとこうなる。
f:id:gogo _sakura:20141106201955g:image

Axlsを使ってExcelでリスト（ドロップダウンするやつ）を作る

業務でエクセルを使うことになったので調べてました。

↓Qiitaの素晴らしいまとめ
RailsでExcelを扱うGemまとめ
http://qiita.com/Kta-M/items/02a2c41c5624f75498aa

↓情報はこの辺を見ました
http://stackoverflow.com/questions/17320645/axlsx-building-dynamic-columns

あまりエクセルのリストを作成する情報がなかったので書いておきます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

require 'axlsx'
package = Axlsx::Package.new
sheet = package.workbook.add_worksheet(name: 'lists')

sheet.add_row(['品名', '単価', '数量', '計'])
sheet.add_row(['にんじん',    80, 1,      '=B2*C2'])
sheet.add_row(['たまねぎ',    50, 2,      '=B3*C3'])
sheet.add_row(['じゃがいも',  40, 2,      '=B4*C4'])
sheet.add_row(['牛肉',       200, 1,      '=B5*C5'])
sheet.add_row(['カレー粉',   150, 1,      '=B6*C6'])
sheet.add_row(['',            '', '総計', '=SUM(D2:D6)'])

sheet.add_data_validation("A10", {
  :type => :list,
  :formula1 => 'A2:A6',
  :showDropDown => false,
  :showErrorMessage => true,
  :errorTitle => '',
  :error => 'リストから選んで下さい',
  :errorStyle => :stop,
  :showInputMessage => true,
  :promptTitle => 'カレー具材',
  :prompt => 'カレーの具材を選んで下さい。'})

package.serialize('test.xlsx')