Axlsxでウィンドウ枠の固定

エクセルのウィンドウ枠の固定をAxlsxで行う方法

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

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

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

sheet.sheet_view.pane do |pane|
  pane.top_left_cell = "B2"
  pane.state = :frozen_split
  pane.y_split = 1
  pane.x_split = 1
  pane.active_pane = :bottom_right
end

package.serialize('test.xlsx')

Axlsxでエクセルファイル作成時に注意する点とかはまりどころとか

最近Railsを使う会社が増えてきて、2年前にRailsを扱う会社をエージェントに探してもらった時はなかなか見つからなかったんですが、今は楽に見つかりそうですね。 そもそも登録した転職サイトがダメだったのかもしれないと思いますが。 千葉でRuby、Railsメインでやってる会社ないかな。

さてさて、ここのところずっと触っているAxlsxです。

型に気をつける

エクセルのセルが数値か文字列かで挙動が変わったりします。 文字列型のカラムに"1000"とか入っていた場合、エクセルでは数値として認識されてしまってvloolupなどの関数が反応しないことがあります。 その場合は明示的にtypeを指定しましょう。

1
2
3
4
5
6
7
8
9
sheet.add_row(
  [
    "1000",
    1000,
  ], types: [:string, :float])
)

# もしくは
sheet.rows[0].cells[0].type = [:string]

typesに設定できるシンボル Method: Axlsx::Cell #type — Documentation for randym/axlsx (master) [:date, :time, :float, :integer, :string, :boolean]

エクセルの関数を設定してダウンロード

そのままアップロードすると関数による値の設定が行われません。 おそらく数式はエクセルを開いた時にエクセル側で計算されるので、エクセルを一回も開かずにアップロードすると値が設定されていないことになるのかと思います fomula _valueを設定しましょう

1
2
3
4
5
sheet.add_row [1, 2, '=A1+B1'], :formula_values => [nil, nil, 3]

#_もしくは
sheet.rows[0].cells[0].value         = "=A1+B1"
sheet.rows[0].cells[0].formula_value = "3"

trueとfalseにrefinmentsを使ってメソッド追加

Rubyでモデルの値がtrueの場合は○。falseの場合は空文字を表示するメソッドが欲しかったのでrefimentsを使って実装したかったけどなかなかうまくいかない。
trueはインスタンスだと思うんだけど何か普通のインスタンスと違うのだろうか。

試行錯誤は省いて結論としてはこうしたらできました.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
module BooleanEx
  refine TrueClass do
    def true.to_circle
      "○"
    end
  end

  refine FalseClass do
    def false.to_circle
      ""
    end
  end
end

using BooleanEx

true.to_circle # => "○"
false.to_circle # => ""

cacheのキーを削除

例えばmodelへの問い合わせ結果をキャッシュに保存していて、after _saveなどで消したい場合などは
Rails.cache.delete(‘キー名’)でできる。

1
2
3
4
5
6
users = Rails.cache.fetch("users", expires_in: 30.seconds) do
  User.all.to_a
end

# 消す
Rails.cache.delete('users')

関連を保存時に削除する

saveした時に削除したいときは対象のレコードに対して

1
accepts_nested_attributes_for :対象のアソシエーション, allow_destroy: true

で宣言してから、

1
2
parent.childs.first.mark_for_destruction # 削除したいレコードに削除のマークをつける
parent.childs.save # => childsのfirstのものか削除される。

で削除される。
ちなみにsave時にこれを削除してねってマークしたかは

1
parent.childs.first.marked_for_destruction? # => true

で確認できる。


カリー化のお話

Ruby のカリー化を日本語で説明してみる - Qiita
という記事を見たので自分も食べられない方のカリーを使ったものをなんか書いてみました。

irbで実行できます。

1
2
3
4
5
6
7
8
9
10
11
meeting = Proc.new do |teacher, student|
 p "#{teacher}#{student}くんとカレーについて話す"
end

fujii_meeting = meeting.curry.("藤井先生")

%w(田中 佐藤 斉藤 加藤).each(&fujii_meeting)
# => "藤井先生は田中くんとカレーについて話す"
# => "藤井先生は佐藤くんとカレーについて話す"
# => "藤井先生は斉藤くんとカレーについて話す"
# => "藤井先生は加藤くんとカレーについて話す"

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
ってコールバックが発生するんですね。知らなかった。