I decided to put a DHCP server on Linux, but there was a small problem, we did not have a current DHCP server (all ips were static), a network map, a list of ip and mac addresses. I scanned the network, got a list of mac and ip, printed it out, and my colleague and I went to write down the names of those who have these mac addresses. Then I entered all this into an excel table (table 1).
Table 1
|
Cabinet | PC Name | Full name | IP
| MAC |
one | 1-1 | Ivanov
Ivan
Ivanovich | 192.168.0.10 | 00:00:00:00:00:00 |
Six months later, I finally decided to start installing a DHCP server. To reserve ip, it was necessary to register all this in config. But due to the fact that the table had about 75 lines with mac, ip, last names, it was somehow too lazy to write it all down manually and I decided to create a python script that would do it all.
Example dhcp.conf file:
host Lastname {
hardware ethernet 00:00:00:00:00:00;
fixed-address 192.168.0.1;
}
last name – Last name of the user.
hardware ethernet – MAC address.
fixed address – ip address for reservation.
The current table did not fit due to the fact that the full name is full in it. I created a new table (table 2) and rewrote the last names in Latin.
table 2 |
name | mac | ip |
Ivanov | 00:00:00:00:00:00 | 192.168.0.10 |
Next, I started writing a script in python, I got 3 script options.
Libraries xlrd and pandas 1 line in the excel table is not included in the list, considering it as the table of contents of the columns.
Option 1
I wanted to use the pandas library, but it did not start and decided to use lists. In order to enclose all the lines in brackets, I used the excel tool. With formula extension.
="'"&A1&"'," - для столбца name
="'"&B1&"'," - для столбца mac
="'"&C1&"'," - для столбца ip
It turned out like this (table 3).
Table 3 |
name | mac | ip |
‘Ivanov’ | ’00:00:00:00:00:00′, | ‘192.168.0.10’ |
Then he added it all to the lists and just ran through the cycle.
mac=['00:00:00:00:00:00', ...]
ip=['192.168.0.10', ...]
name=['Ivanov', ...]
def write():
with open("dhcp.conf", 'w') as f:
for i,j,k in zip(Config.name, Config.mac, Config.ip):
f.write(f"host {i}" + " {\n\thardware ethernet" + f" {j};" + "\n\tfixed-address " + f"{k};" + "\n}\n\n")
if __name__ == '__main__':
write()
We create 3 lists:
mac – for mac addresses
ip -for ip addresses
name – for usernames
mac=['00:00:00:00:00:00', ...]
ip=['192.168.0.10', ...]
name=['Ivanov', ...]
We create a function:
def write():
Open the dhcp.conf file in write mode:
with open("dhcp.conf", 'w') as f:
Looping through lists. zip – the function takes several lists as input and creates a list (tuple) from them:
for i,j,k in zip(Config.name, Config.mac, Config.ip):
We write to the file:
f.write(f"host {i}" + " {\n\thardware ethernet" + f" {j};" + "\n\tfixed-address " + f"{k};" + "\n}\n\n")
We call the function:
if __name__ == '__main__':
write()
Option 2
Used table 2 and xlrd library.
The library does not support xlsx format, so I had to save the file in xls format.
import xlrd
def write():
loc = (r"C:\path\to\file\filename.xls")
sheet = xlrd.open_workbook(loc).sheet_by_index(0)
with open("dhcp.conf", 'w') as f:
for i in range(sheet.nrows):
f.write(f"host {sheet.cell_value(i, 0)}" + " {\n\thardware ethernet" + f" {sheet.cell_value(i,1)};" + "\n\tfixed-address " + f"{sheet.cell_value(i,2)};" + "\n}\n\n")
if __name__ == '__main__':
write()
Import the library to be able to use it:
import xlrd
We declare a function:
def write():
Open excel and sheet with index 0:
loc = (r"C:\path\to\file\filename.xls")
sheet = xlrd.open_workbook(loc).sheet_by_index(0)
This snippet can be written like this:
sheet = xlrd.open_workbook(r"C:\path\to\file\filename.xls").sheet_by_index(0)
Open the file for writing:
with open("dhcp.conf", 'w') as f:
Loop through the rows. Where ‘sheet.nrows’ shows the number of lines:
for i in range(sheet.nrows):
We write to a file. sheet.cell_value(i, 0) – read the value from the field where i – line, 0 – column:
f.write(f"host {sheet.cell_value(i, 0)}" + " {\n\thardware ethernet" + f" {sheet.cell_value(i,1)};" + "\n\tfixed-address " + f"{sheet.cell_value(i,2)};" + "\n}\n\
We call the function:
if __name__ == '__main__':
write()
Option 3
Used table 2 and pandas library.
import pandas as pd
def write():
excel = pd.read_excel(r'C:\path\to\file\filename.xlsx')
with open("dhcp.conf", 'w') as f:
for i, j, k in zip(excel["Name"].tolist(), excel["MAC"].tolist(), excel["IP"].tolist()):
f.write(f"host {i}" + " {\n\thardware ethernet" + f" {j};" + "\n\tfixed-address " + f"{k};" + "\n}\n\n")
if __name__ == '__main__':
write()
We connect the library. as – creating an alias:
import pandas as pd
We create a function:
def write():
Reading data from excel:
excel = pd.read_excel(r'C:\path\to\file\filename.xlsx')
Open file for reading:
with open("dhcp.conf", 'w') as f:
Looping through the lists:
for i, j, k in zip(excel["Name"].tolist(), excel["MAC"].tolist(), excel["IP"].tolist()):
Select the columns with the name ‘Name’, ‘mac’, ‘ip’. Let’s create a list of them:
excel["Name"].tolist(), excel["MAC"].tolist(), excel["IP"].tolist
To see how pandas sees the table of contents in excel, use the following line:
print (excel.columns.ravel())
We write to the file:
f.write(f"host {i}" + " {\n\thardware ethernet" + f" {j};" + "\n\tfixed-address " + f"{k};" + "\n}\n\n
We call the function:
if __name__ == '__main__':
write()
Output of all script options in dhcp.conf:
host Ivanov {
hardware ethernet 00:00:00:00:00:00;
fixed-address 192.168.0.10;
}
I hope this will be useful to someone.
PS: this is my first article
