paloma blog

NWエンジニアやってます。主に自宅環境のお遊びを書きます。Pythonもちょっと。タイトルは好きなカクテルから。

pythonで通信要件を一意に出力する

私はSIerのネットワークエンジニアとして働いているのですが、どこのシステムでも設計のために通信要件一覧というのがあると思います。
(Web系の業界は詳しくないので使っていないかも)

FWやルーティング設計のために、通信名, From, To, Portなど書かれることが多いと思います。

また、資料はExcelで作られているものが多いと思いますが、方眼紙とまではいかなくても使い勝手はよくありません。
正直私の現場では使いづらいです。

こんな感じ

サンプルなので適当に作りましたが、送信元、宛先、IPアドレス、ポートなどは最低限書いてあると思います。
(Ubuntuで検証したのでLibreOfficeです)

f:id:paloma69:20180629001724p:plain

ネットで調べてもこんな感じのが出てきます。

小規模システムなら良いですが、大規模で大量の種類の通信があるシステムもこの様に書かれると、
読むのもパラメータに落とすのも一苦労です。
書き方によっては重複の行がいくつか出てくることもあります。

対象のセルをコピーして他の資料に貼り付ければいいのですが、
セル内改行のものがあったりすると一気に使い勝手が悪くなります。

こういうのをいちいち転記するのも馬鹿らしいので、Configに落としていく際に簡単にできないかと思いpythonで書いてみました。

やりたいこと

  • 各Cellに書かれている値を抽出する
  • Cellに数行書かれている場合も1行ずつ出力する
  • Config作成用にIPアドレスとポートだけ出力する

Excelファイルを処理するのはopenpyxlモジュールでやります。

今回ファイルは同階層におきます。

(python3) masashi@PC-ubuntu:~/trafficsorting$ ls
traffic.py  通信要件.xlsx
  • IPとポートだけ出力できれば良いのでD,F,G,H列だけ処理します。
  • 処理が途中で止まらないように書き方が変なセルは行数をメモっときます。
    • まだハンドリングが不完全なので空白でも反応してしまいます。
  • 複数シートがある場合を想定して一応シート名も出力します。
  • セル内改行してあるものは改行コードで文字列を分割します。
  • 全パターン抽出するにはforをネストすることによってできるようです。
    (もっと綺麗な方法があるかも)
#!/usr/bin/env python3

import openpyxl

wb = openpyxl.load_workbook("通信要件.xlsx", data_only = True)

wsa = wb['通信一覧']

def Traffic(sheet, ret):
    try:
        # All pattern output for each cell value
        for src in sheet['D' + str(ret)].value.split():
            for dst in sheet['F' + str(ret)].value.split():
                    for ptc in sheet['G' + str(ret)].value.split():
                            for port in str(sheet['H' + str(ret)].value).split():
                                print(src, dst, ptc, port)
    except:
        print(str(ret) + ' row has Error!')
        print('Cell\'s value is "x.x.x.x/x" with numbers only.')
        pass

# Display sheet to last line
for a in range(3, wsa.max_row + 1):
    print(wsa)
    Traffic(wsa, a)

動かしてみる

(python3) masashi@PC-ubuntu:~/trafficsorting$ python traffic.py 
<Worksheet "通信一覧">
any 192.168.10.1/24 TCP 80
any 192.168.10.2/24 TCP 80
<Worksheet "通信一覧">
192.168.10.1/24 192.168.20.1/24 TCP 8009
192.168.10.1/24 192.168.20.2/24 TCP 8009
192.168.10.2/24 192.168.20.1/24 TCP 8009
192.168.10.2/24 192.168.20.2/24 TCP 8009
<Worksheet "通信一覧">
192.168.20.1/24 192.168.30.1/24 TCP 1521
192.168.20.2/24 192.168.30.1/24 TCP 1521
<Worksheet "通信一覧">
192.168.40.1/24 192.168.10.1/24 ICMP -
192.168.40.1/24 192.168.10.2/24 ICMP -
<Worksheet "通信一覧">
192.168.40.1/24 192.168.20.1/24 ICMP -
192.168.40.1/24 192.168.20.2/24 ICMP -
<Worksheet "通信一覧">
192.168.40.1/24 192.168.30.2/24 ICMP -
192.168.40.1/24 192.168.30.3/24 ICMP -

ちゃんと出ました!

全てのパターンに対し、一意な値で出力できました。
また、テキストで出力する事により検索等しやすくなりました。

シート名が都度出てしまうのがかっこ悪いのでもう少し綺麗にしたいですね。

改修は置いといて次回は出力結果をもう少し加工したいと思います。

その他課題

セルが結合してあると結合がある行がまるまるエラーとして出力されてしまったので、実はExcelを修正しました。
これはどう直すかな…