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

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

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

代码

#!/usr/bin/python
#coding:utf-8

import sys
import os
import socket
from xlutils.copy import copy
import xlrd
from urllib.parse import urlparse

def 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 ip



def 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