如何利用Python程式來處理IP與學校的對應(防火牆LOG)

        縣網中心人員給我兩個Excel 檔案，分別是學校名稱與IP對應表與受攻擊IP對應表。希望我能夠利用這兩個Excel檔案，匯出受攻擊IP 與學校名稱。

壹、問題描述：

一、學校名稱與IP對應表，其格式內容：

二、受攻擊IP對應表，其格式內容：

三、最後的結果呈現，其格式內容：

貳、問題解決歷程：

一、學校名稱與IP對應表，其格式內容：

        要變更為下圖：

                                       

       其中學校名稱與IP對應表，會列出學校名稱、IP最小值與IP最大值。即：測試001國民小學，172.23.80.128~172.23.80.191。那我需要把這項資料調整為：測試001國民小學，172.23.80.128.191。

        但是問題來，同樣的校名，測試001國民小學也有另一組IP：192.168.4.1~192.168.4.4。同樣地，我也需要把這項資料調整為：測試001國民小學，192.168.4.1.4。

        如果要把這兩項資料變成字典的話，那就會比較麻煩。因為要把[測試001國民小學]當成字典的Index，後面的資料會蓋掉前面的資料。所以我必須在[測試001國民小學]前面加些字元，讓這兩筆資料視為不同資料。最後呈現結果如下：

參、最後完成程式：

下載檔案。解壓密碼：demo1234

教學影片：

使用教學說明：

        1.解壓縮後，ListOfSchoolBadIP資料夾內會有input 資料夾。input 資料夾內的檔案名稱為01SchoolExceptionBlacklist.xlsx，其內容格式為

        注意事項：上圖紅色圈選處，會被程式取得資料。若您要更新資料，可直接用新IP資料覆蓋該圈選處即可。或是您有新資料格式跟01SchoolExceptionBlacklist.xlsx一樣，則可以同樣檔名取代原先的檔案。

        2.input 資料夾內的另外一個檔案名稱為02ChcAllSchool2IP.xlsx，其內容格式為

        注意事項：上圖為虛擬資料。您有新資料格式跟02ChcAllSchool2IP.xlsx一樣，則可以同樣檔名取代原先的檔案。
        3.解壓縮後，ListOfSchoolBadIP資料夾內會有PandasOpenpyxlSetup.bat。若您還未安裝pythoon套件Pandas 與 Openpyxl，請對PandasOpenpyxlSetup.bat點兩下。

        4.當一切就緒，您已完成安裝python 3.9.0 與套件Pandas 與 Openpyxl，就可以對ListOfSchoolBadIP.py點兩下，即可出現最後結果檔案ListOfSchoolBadIP.csv。

一、各程式內容：

01.程式目的：安裝套件Pandas 與 Openpyxl

程式名稱：PandasOpenpyxlSetup.bat

程式內容：

@echo off

if not exist %HomeDrive%%HomePath%\AppData\Local\Programs\Python\Python39 (

echo "Please install Python 3.9.0"

start https://skjhcreator.blogspot.com/2022/05/batpython-390.html

) else (

echo "Python 3.9.0 OK"

python -m pip install pandas openpyxl

)

pause

exit

02.程式目的：匯出受攻擊IP、學校名稱對應表CSV

程式名稱：ListOfSchoolBadIP.py

程式內容：

import os

import csv

import openpyxl

# 取得 input資料夾內的兩個Excel檔名

FileName = []

DirPath=str(os.path.abspath(os.getcwd()))+'\\Input\\'

FileName=os.listdir(DirPath)

StrFileName0=DirPath+str(FileName[0])

StrFileName1=DirPath+str(FileName[1])

# 取得第一個Excel內所有的IP並存入list

workbook0 = openpyxl.load_workbook(StrFileName0)

sheet0 = workbook0.worksheets[0]

df0_list = list()

for i in range(2,sheet0.max_row):

     df0_list.append(str(sheet0.cell(row=i,column=2).value).lstrip())

#print(df0_list)

# 取得第二個Excel內所有的年班座號->Email對應表並存入dict

workbook1 = openpyxl.load_workbook(StrFileName1)

sheet1 = workbook1.worksheets[0]

df1_dict = dict()

for i in range(1,sheet1.max_row+1):

     df1_dict[str(sheet1.cell(row=i,column=1).value)]=str(sheet1.cell(row=i,column=2).value)

#print(df1_dict)

# 比較後並寫入CSV

itemplist = list()

jtemplist = list()

OutputFileName = "ListOfSchoolBadIP.csv"

with open(OutputFileName,'w',newline="") as csvfile:

     FieldNames = ['IP','School Name']

     writer = csv.DictWriter(csvfile,fieldnames=FieldNames)

     writer.writeheader()

     for i in df0_list:

          itemplist = i.split('.')

          #print(itemplist[0],itemplist[1],itemplist[2],itemplist[3])

          for j in df1_dict:

               jtemplist = df1_dict[j].split('.')

               #print(jtemplist[0],jtemplist[1],jtemplist[2],jtemplist[3],jtemplist[4])

               if (itemplist[0]==jtemplist[0] and itemplist[1]==jtemplist[1] and itemplist[2]==jtemplist[2] and itemplist[3]>=jtemplist[3]and itemplist[3]<=jtemplist[4]):

                    #print(i,j)

                    writer.writerow({FieldNames[0]:i,FieldNames[1]:j+";"})

資料來源：
1.Python split 字串分割用法與範例
https://shengyu7697.github.io/python-split/
2.使用函數將文字分割成不同的欄
https://support.microsoft.com/zh-tw/office/%E4%BD%BF%E7%94%A8%E5%87%BD%E6%95%B8%E5%B0%87%E6%96%87%E5%AD%97%E5%88%86%E5%89%B2%E6%88%90%E4%B8%8D%E5%90%8C%E7%9A%84%E6%AC%84-49ec57f9-3d5a-44b2-82da-50dded6e4a68