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