Excel对某一列的数据插入处理，域名得出IP

早期都是通过Excel做数据统计，对某一列的数据插入处理。

代码功能是从A列纯域名，将域名转换为IP，从域名A列得到IP写到B列。

代码

#!/usr/bin/python
#coding:utf-8import sys
import os
import socket
from xlutils.copy import copy
import xlrd
from urllib.parse import urlparsedef domain2ip(domain):
res = urlparse(domain)
try :
if res.netloc == "":
ip = socket.gethostbyname(res.path)
else:
ip = socket.gethostbyname(res.netloc)
except Exception as e:
ip = "error"
return ipdef read_excel(filename,
Row_num,
Col_num):
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_name('target')
return sheet.cell_value(Row_num,Col_num)def excel_insert(excel_filename,
Row_num, # 行
Col_num , # 列
Value # 内容
):
book = xlrd.open_workbook(excel_filename) # 打开excel
new_book = copy(book) # 复制excel
sheet = new_book.get_sheet(0) # 获取第一个表格的数据
sheet.write(Row_num, Col_num, Value) # 修改1行2列的数据为'Haha'
new_book.save('secondsheet.xlsx') # 保存新的excel
os.remove(excel_filename) # 删除旧的excel
os.rename('secondsheet.xlsx', excel_filename) # 将新excel重命名def target_domain(filename):
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_name('target')
rows = sheet.nrows #获取行数
cols = sheet.ncols #获取列数 for rows_num in range(1,rows):
domain = read_excel(filename, rows_num, 1) # 读取第N行第1列的数据
print(rows_num,domain)
ip = domain2ip(str(domain))
excel_insert(filename,rows_num,2,ip) # 写进第N行第2列数据if __name__ == '__main__':
#Mysql_insert() # Mysql写入 filename = "赏金列表.xlsx"
target_domain(filename) # 目标列表的域名转IP