NOTO

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だったことが幸いしてやってみちゃった備忘録ですが、 もともとのデータはやっぱり何かに依存しない形で管理したいものです。などと思いました。