ExcelVBAマクロ exce.liveで全自動百葉箱を作ってみた

プログラミング

exce.liveとRaspberry Piを使って温湿度を自動で計測する全自動百葉箱を作ってみたのでその情報を公開します。

夏休みの自由研究にいかがでしょうか?

システム概要

システム全体のイメージは以下の通りです。

  1. Raspberry PiのGPIOを使い温湿度センサー(DHT11)で計測
  2. exce.liveを使いExcelへ情報を送信

見た目は単純ですね。

仕様

概要が決まったので次は仕様を決めていきます。

  • 1時間毎の温度と湿度を計測する
  • 計測結果はExcelへ出力する
  • 温度と湿度は折れ線グラフで表示する

こんなところですかね。

細かいところは抜きにしてこの仕様に合わせたシステムを作っていきます。

実装方法検討

仕様が決まったのでその仕様を満たすためにどう実装していくか考えていきます。

言語

Excel側はVBAが準備されているのでわざわざ他を選択する必要は無いですね。

Raspberry Pi側は特にどれでも良いのですが初期から入っていてトレンドになっているPythonを使う事にします。

※個人的にはFusion360でPythonスクリプトをちょこちょこいじっているのでとっつきやすさもありました。

計測

温湿度センサー(DHT11)を使って温度と湿度を計測する方法を検討します。

このセンサーは有名で検索をかければ多くのページがヒットします。

それらを見てみると大体はDHT11用ライブラリが使われているので今回の実装も同様にこのライブラリを使う事にします。

GitHub - szazo/DHT11_Python: Pure Python library for reading DHT11 sensor on Raspberry Pi
Pure Python library for reading DHT11 sensor on Raspberry Pi - szazo/DHT11_Python

送信

送信はexce.liveを使います。

exce.liveで情報を送信するためにはURLを組み立ててリクエストするだけで実現します。

URLの送信が必要なため、これもURLライブラリを使います。

URLライブラリは標準ライブラリに含まれるためインストール不要です。

グラフ表示

exce.liveにてExcelへ追加された情報はExcelの機能をフルに使えるためグラフもExcelで準備されているグラフを使います。

検討する物はこれで全てです。

次は実際に実装していきます。

実装

実装していく手順は以下の通りです。

  1. Raspberry PiにOSをインストールする
  2. 計測用プログラムを作成する
  3. 温湿度センサーを取り付ける
  4. 送信用プログラムを作成する
  5. 情報を保持するプログラムを作成する
  6. グラフを作成する
  7. cronを設定する

手順は多いですが1つ1つは小さいので恐れずやっていきましょう。

Raspberry PiにOSをインストールする

これは検索すればいくらでも情報が出てきます。

そちらを参考にインストールしてみてください。(丸投げ(;^_^A)

※初手からすいませんm(_ _)m

計測用プログラムを作成する

Raspberry PiのOSをインストールするとPythonも付いてくるのでPythonのインストールは不要です。

ただし、温湿度センサー「DHT11」を扱うライブラリは標準ライブラリに含まれていないのでこのライブラリはインストールが必要です。

pipを使ってインストールをします。

ターミナルを起動して「pip install dht11」と入力し、実行してください。

インストールが完了するとカレントディレクトリにディレクトリ「DHT11_Python」が存在します。

これで準備が出来たので実際にPythonで測定用プログラムを作成します。

#全自動百葉箱(計測)
#
#温湿度センサー(DHT11)を使い温湿度を計測する
#計測した温湿度はファイル「TempHum.txt」へ出力する
#出力はカンマ区切りで日時、温度、湿度の順番
#
#Raspbery PiのGPIO接続図
# VDD  -- 3.3V
# DATA -- GPIO14
# NC   -- [未使用]
# GND  -- GND
#
#DHT11の制御は以下のライブラリで行う
#https://github.com/szazo/DHT11_Python.git

import RPi.GPIO as GPIO
import dht11
import time
import datetime

#設定
sTHPath = "/home/<ユーザー名>/Desktop/TempHum.txt"
iPin = 14

#温湿度センサーの値を取得する
GPIO.setwarnings(True)
GPIO.setmode(GPIO.BCM)
instance = dht11.DHT11(pin=iPin)
try:
    result = instance.read()
    if result.is_valid():
        sDateTime = str(datetime.datetime.now())
        fTemp = result.temperature
        fHum = result.humidity
        print("Last valid input: " + sDateTime)
        print("Temperature: %-3.1f C" % fTemp)
        print("Humidity: %-3.1f %%" % fHum)
except KeybordInterrupt:
    print("Cleanup")
    GPIO.cleanup()

#取得した温湿度をCSV形式でファイルに出力する
s = sDateTime.replace(" ", "_") + "," + str(fTemp) + "," + str(fHum)
with open(sTHPath, mode='w') as f:
    f.write(s)

※プログラム中の「<ユーザー名>」はご自身の環境に合わせて変更してください。

プログラム内のコメント通り、測定してその結果をテキストファイルに出力しています。

送信用プログラムはこのテキストファイルの情報を送信するだけで良いです。

一応プログラムもダウンロードできるように置いておきます。

温湿度センサーを取り付ける

Raspberry Piに温湿度センサー(DHT11)を取り付けます。

取付イメージは以下の通りです。

抵抗は10kΩを使いましたがプルアップ抵抗として使うようなので正確に10kΩである必要は無いようです。

取り付けできたら実際に温湿度を測定してみましょう。

計測用プログラムを実行して出力用ファイル(変更していなければ「TempHum.txt」)を開いて中身を確認してみましょう。

計測日時、温度、湿度がカンマ区切りで出力されていれば成功です。

これで計測側は完成です。

送信用プログラムを作成する

送信用プログラムは標準ライブラリだけで実装可能なため、別途インストールなどは不要です。

ただし、情報の送信にexce.liveを使っているため、送信用トークンが必要になります。

事前にexce.liveへ登録して送信用トークンを取得しておいてください。

exce.live - エクセリブ クラウド
全国最大規模の中小企業新開発ソフトウェアコンテスト 「第32回 中小企業優秀新技術・新製品賞」(主催:りそな中

exce.liveについては過去に当ブログでも紹介しています。

それでは早速送信プログラムを組んでいきます。

#全自動百葉箱(送信)
#
#温湿度は計測側で出力したファイルの値を使う
#温湿度はファイル「TempHum.txt」へ出力される
#ファイルから温湿度を取得してexce.liveにて情報を送信する
#処理の内容はログファイル「logfile.log」へ出力する

import logging
import time
import datetime
import urllib.request

#設定
sTHPath = "/home/raspi/Desktop/TempHum.txt"
sLogPath = "/home/raspi/Desktop/logfile.log"
token = "<送信トークン>"
sheet = "newsheet"

Log_Format = "%(levelname)s %(asctime)s - %(message)s"
logging.basicConfig(
    filename = sLogPath,
    filemode = "a",
    format = Log_Format,
    level = logging.INFO
    )
logger = logging.getLogger()

#処理の開始
logger.info("==========================================")
logger.info("Start THSend.py")

#温湿度はファイルから取得する
f = open(sTHPath)
f.close()
with open(sTHPath) as f:
    print(type(f))
    s = f.read()
    l = s.split(',')
    sDateTime = l[0]
    sTemp = l[1]
    sHum = l[2]
    print(s)
    logger.info("Read info(" + s + ")")

#送信情報を組み立てる
msg = sDateTime + "," + sTemp + "," + sHum
url = "https://api.exce.live/sendmsg"
param = "?token=<<token>>&sheet=<<sheet>>&msg=<<msg>>"
url = url + param
url = url.replace("<<token>>", token)
url = url.replace("<<sheet>>", sheet)
url = url.replace("<<msg>>", msg)
print(url)
logger.info("Send message(" + url + ")")

#exce.liveで情報を送信する
with urllib.request.urlopen(url) as res:
    html = res.read()
    print(html.decode())
    logger.info("Res message(" + html.decode() + ")")

※プログラム中の「<送信トークン>」はexce.liveにて取得したご自身の送信トークンに合わせて変更してください。
※プログラム中の「<ユーザー名>」はご自身の環境に合わせて変更してください。

一応プログラムもダウンロードできるように置いておきます。

送信用プログラムを実行して送信できたか確認してみましょう。

「正常に送信完了しました!」のレスポンスがあれば正しく送信が出来る事が確認できました。

これで送信側も完成です。

情報を保持するプログラムを作成する

温湿度の計測と送信はRaspberry Piで行いましたが受信以降はExcel側の作業になります。

まずはexce.liveで送信されたデータを受信できるように準備が必要です。

準備はexce.live公式のチュートリアルを参照して行ってください。

exce.live チュートリアル - エクセリブ クラウド
exce.live チュートリアル このチュートリアルでは、実際にexce.liveクラウドを使って、どのよう

あとはexce.liveに接続して受信すると「_newsheet」へ受信したデータで更新されます。

送信メッセージはカンマ区切りで送る事で受信側では自動で分解してくれるのでおすすめです。

「_newsheet」のチェンジイベントにプログラムを仕掛ける事で受信タイミングで動作するプログラムを作る事ができ、exce.liveの大きな強みになっています。

それではこの受信したデータを溜めておけるようにシートを作成します。

データの並びは「_newsheet」と同じです。

空いている行に受信データを追加するようなイメージです。

プログラムは「_newsheet」シートと標準モジュールに作成します。

「_newsheet」シートではexce.liveの受信タイミングで「全データ」シートへ情報を追加するためワークシートのチェンジイベントを使います。

Option Explicit

'exce.liveから受信したタイミングで「全データ」シートへ追加する
Private Sub Worksheet_Change(ByVal Target As Range)
    Call mMain.addLog
End Sub

ここではデータ追加用のプロシージャを呼んでいるだけで本体は標準モジュール「mMain」に記述します。

Option Explicit

'「_newsheet」の情報を「全データ」シートに追加する
Public Sub addLog()
    Dim lRow As Long
    lRow = getLastRow() + 1
    '「全データ」シートに変更があるとグラフが更新される
    wsLog.Cells(lRow, 1).Value = Sheet5.Cells(1, 1).Value
    wsLog.Cells(lRow, 2).Value = Sheet5.Cells(1, 2).Value
    wsLog.Cells(lRow, 3).Value = Sheet5.Cells(1, 3).Value
    wsLog.Cells(lRow, 4).Value = Sheet5.Cells(1, 4).Value
    'データ保持のため保存する
    Call Excel.Application.ThisWorkbook.Save
End Sub

'「全データ」シートの最終行を取得する
Private Function getLastRow() As Long
    Dim lRow As Long
    lRow = 1
    Do Until wsLog.Cells(lRow, 1).Value = ""
        lRow = lRow + 1
    Loop
    getLastRow = lRow - 1
End Function

これでexce.liveで受信した情報はその都度「全データ」シートへ追記していくプログラムが完成しました。

グラフを作成する

「全データ」シートでは各時間での温度と湿度を確認する事が出来ますがどんな推移を辿っているかは分かりづらいです。

そこでグラフを作成して推移を見やすくします。

グラフのイメージは以下の通りです。

温湿度は1時間毎にexce.liveで送信され、「全データ」シートへ追記されていきます。

このグラフもそのタイミングで更新をさせていきたいです。

このグラフの元データは「全データ」シートなので実行のタイミングは「全データ」シートが変更された時としたいため、「全データ」シートのチェンジイベントに記述します。

Option Explicit

'変更があったタイミングでグラフを更新する
Private Sub Worksheet_Change(ByVal Target As Range)
    Call mMain.updateGraph
End Sub

exce.liveの受信時と同様シートモジュールは呼び出しだけを行いメインの処理は標準モジュールに書きます。

「全データ」シートへの追記処理の下にグラフの更新処理を追記します。

Option Explicit

'「_newsheet」の情報を「全データ」シートに追加する
Public Sub addLog()
    Dim lRow As Long
    lRow = getLastRow() + 1
    '「全データ」シートに変更があるとグラフが更新される
    wsLog.Cells(lRow, 1).Value = Sheet5.Cells(1, 1).Value
    wsLog.Cells(lRow, 2).Value = Sheet5.Cells(1, 2).Value
    wsLog.Cells(lRow, 3).Value = Sheet5.Cells(1, 3).Value
    wsLog.Cells(lRow, 4).Value = Sheet5.Cells(1, 4).Value
    'データ保持のため保存する
    Call Excel.Application.ThisWorkbook.Save
End Sub

'「全データ」シートの情報に合わせてグラフを更新する
'※グラフの作りに合わせて改修する必要がある
Public Sub updateGraph()
    Dim coGraph As ChartObject
    Set coGraph = wsGraph.ChartObjects("グラフ 1")
    Dim lLastRow As Long
    lLastRow = getLastRow()
    With coGraph.Chart
        .FullSeriesCollection(1).Values = "=全データ!$C$2:$C$" & CStr(lLastRow)
        .FullSeriesCollection(2).Values = "=全データ!$D$2:$D$" & CStr(lLastRow)
        .FullSeriesCollection(2).XValues = "=全データ!$A$2:$A$" & CStr(lLastRow)
    End With
End Sub

'「全データ」シートの最終行を取得する
Private Function getLastRow() As Long
    Dim lRow As Long
    lRow = 1
    Do Until wsLog.Cells(lRow, 1).Value = ""
        lRow = lRow + 1
    Loop
    getLastRow = lRow - 1
End Function

これで「全データ」シートが変更した時にグラフも更新するように出来ました。

Excel側のプログラムはこれで全てです。

ダウンロードできるようにExcelファイルも準備しました。

cronを設定する

Excel側のプログラムも完成したのであとはRaspberry Pi側のプログラムを自動で実行するように設定が必要です。

それぞれ測定用プログラムは1分毎、送信用プログラムは1時間毎に設定していきます。

プログラムを定期的に実行させるにはcronを使います。

Windowsで言う「タスク」のような物です。

cronの設定でそれぞれのプログラム実行の設定を行います。

これでシステム全体の実装が完成しました。

システムの動作確認

そのままexce.liveへ接続したまま放置しておけば毎時の測定結果が「全データ」シートへ追記されていきます。

それに合わせてグラフも更新されます。

お疲れ様でした。

まとめ

かなり駆け足状態で紹介しました。

Raspberry Piでセンサー使ったやり取りをした経験が無いと今回の内容はなかなか理解できないところが多かったかもしれません。

コメントなりで改善提案があれば対応しようと思います。

何かのきっかけ作りになれれば幸いです。

コメント

タイトルとURLをコピーしました