xlsxのデータをcronに載せたくていろいろしたあれこれ

概要

この記事では、

• xlsxを保存時に一部シートをCSVとして保存する話
• そのCSVの日付データを元にrubyスクリプトで通知する話
• なんでそんなことをしなければいけなかったかの話

を書きたいと思います。

環境

PC環境

• windows 7
• EXCEL 2013
• エクセルはLinux(Samba)のファイルサーバにおかれています

サーバ環境

• ruby 2.0.0p481
• CentOS release 5.11

※もろもろ目をつぶってください。

状況

もともとはSSL証明書の有効期限の日付管理等をエクセルで行っていました。複数個ある証明書の有効期限を毎日エクセルを開いて確認するというのはなかなか酷な話なので、windows oleを使用したスクリプトを書いていました。

スクリプト自体は

• FQDNと有効期限をセットにして取得する
• 有効期限と今日の日付を比較して有効期限が近いものを集める
• メールでその内容を通知する

というものです。

このスクリプトはwindwos上のものなので、スタートアップに仕掛けて起動しておいてLinux(samba)の上にある管理エクセルを読ませて実行していました。そのキックだけがenter一発だけとはいえ手動でした。パソコンを起動しないと実行できないという。

解決策

「linuxのrubyでoleが面倒ならcsv出しちゃえば？」という先輩の一言がきっかけでややこしいんですが、該当のエクセルから使いたいデータをCSVにする方法を思いつきました。VBAではまりながらなんとか実行した記録です。

VBAでCSVを吐く

調べたところ、ThisWorkbookの中でエクセルを保存時にフックできるものがあることがわかりました。それを活用して以下のVBAを書きました。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  Call makeNewGeneralCSV
End Sub

' CSVを保存するために別bookを作成
Sub makeNewGeneralCSV()
  ' 変数の宣言
  Dim newBook As Workbook
  Dim masterBook As Workbook
  Dim filename1 As String
  Dim dirPath As String
  Dim format1 As String

  filename1 = "filename.csv"
  format1 = "xlCSV"

  ' 新しいBOOKの作成 （間に確認）
  Set masterBook = Workbooks("managefile.xlsm")
  dirPath = masterBook.Path & ""

  ' general csvが存在するか確認
  If Dir(dirPath & "filename.csv") = "" Then
    MsgBox ("ファイルは保存されましたが、通知管理用CSVの保存処理に失敗しました。同ディレクトリに「filename.csv」があるか確認してください")
    Exit Sub
  End If
  Set newBook = Workbooks.Open(dirPath & "filename.csv")

' アラートを消す
  Application.DisplayAlerts = False

  ' シートにデータコピー とその他のシート削除
  newBook.Sheets("datasheet").name = "datasheet_bak"
  masterBook.Sheets("DATASHEET").Copy After:=newBook.Worksheets("datasheet_bak")
  newBook.Sheets("datasheet_bak").Delete

  ' newBookの保存
  newBook.save

  ' newBookを閉じる
  newBook.Close

  ' アラートを戻す
  Application.DisplayAlerts = True
  MsgBox ("通知用CSVを最新にしました")
End Sub

なぜかxlCSVで吐き出すのにえらく苦労しました。
内容としては、それ用の別のブックを用意してCSV用のデータをコピーしてCSV保存しているというぐらいです。
ポイントはやはり「Private Sub Workbook_AfterSave(ByVal Success As Boolean)」の部分で、
こいつの中に書いておくことで、保存後に挟む処理をかくことができます。

CSVを読み込んでrubyスクリプトでメール通知

こっちもなんてことないんですが、やったことは

• CSVを頑張って切ったり貼ったりする
• 曜日によって差し出しにを変える必要があったのでそれやる
• erbを使ってメールテンプレートに変数をまぜる
• メールで通知する

というシンプルな感じです。
なお、mail gemを使った時に文字コードの警告がでていたのは、以下のblogを見て解決しました。
komagataさんありがとうございます(偶然一番上に！)

http://docs.komagata.org/4879

#!/usr/local/ruby200/bin/ruby

require 'csv'
require 'pp'
require 'date'
require 'mail'
require 'erb'

Encoding.default_external = 'utf-8'
Encoding.default_internal = 'utf-8'

class SslCheck
  def initialize
    @week     = Date.today.wday
    @csv_dir  = '/your/dir/filename.csv'
    @csv_path = File.join(@csv_dir, "filename.csv")
    @template = "/your/dir/template.erb"
  end

def read
    @csv = File.open(@csv_path, "rb:Shift_JIS:UTF-8") do |io| # xlsxからデータCSVなので文字コードがshift_JIS
      CSV.new(io).read
    end
    @csv.slice!(0, 2)
    @csv
  end

def parse
    @dataset = {}
    @csv.each do |array|
      if not array[1] == nil
        @dataset[array[1]] = array[5]
      else
        next
      end
    end
    caliculate()
    collect()
    make_text()
    bind()
  end

def send
    week_check()
    to   = @to
    mailbody = @mailbody
    @mail = Mail.new do
      from    'notification@example.com'
      to      to
      subject "#{Date.today} - SSL証明書有効期限通知"
      body    mailbody
    end

@mail.delivery_method :sendmail
    @mail.charset = 'utf-8'
    @mail.deliver!
  end

private
  def week_check
    case @week
    when 1
      @to = 'notification1@example.com'
    when 0,6
      exit 0
    else
      @to = 'notification2@example.com'
    end
  end

def collect
    @limit_time_fqdns = {:a_week => {}, :two_week => {}, :a_month => {}, :two_month => {}, :limit_over => {}}
    @fqdns.each do |fqdn|
      case fqdn[1]
      when -30..0
        @limit_time_fqdns[:limit_over][fqdn[0]] = fqdn[1]
      when 1..7
        @limit_time_fqdns[:a_week][fqdn[0]] = fqdn[1]
      when 8..14
        @limit_time_fqdns[:two_week][fqdn[0]] = fqdn[1]
      when 15..30
        @limit_time_fqdns[:a_month][fqdn[0]] = fqdn[1]
      when 31..60
        @limit_time_fqdns[:two_month][fqdn[0]] = fqdn[1]
      end
    end
  end

def caliculate
    @fqdns = @dataset.collect do |fqdn, expire_date_string|
      today = Date.today
      expire_date_array = expire_date_string.split('/')
      expire_date = Date.parse("#{expire_date_array[2]}-#{expire_date_array[0]}-#{expire_date_array[1]}")
      remain = (expire_date - today) - 1
      [fqdn, remain.to_i]
    end
  end

def make_text
    @text = {}
    @limit_time_fqdns.each do |timelimit, fqdns|
      @text[timelimit] = fqdns.collect do |fqdn, deadline|
        "#{fqdn}t 残り #{deadline} 日"
      end
    end
  end

def bind
    mailbody = ERB.new(File.open(@template).read, nil, "-")
    @mailbody  = mailbody.result(binding).encode("UTF-8", :invalid => :replace)
  end
end

checker = SslCheck.new
checker.read
checker.parse
checker.send

exit 0

これをcronにかけてOKな感じです。

最後に

不覚にも偉い遠回りしながらになりますが、xlsx自体はすごく好きなんですけど、こういうときになかなか痺れる感じを味わいました。
保存先のエクセルがたまたまLinuxのSambaだったことが幸いしてやってみちゃった備忘録ですが、
もともとのデータはやっぱり何かに依存しない形で管理したいものです。などと思いました。