__author__ = 'Administrator'
# _*_ coding: UTF-8 _*_
# @Create by gengyu
# @Create Time :2021/12/2
# @File_name : python_switch
import openpyxl
import os,re,time
import pathlib
# 获取当前目录
current_path = os.getcwd()

# 新建文件的名称
new_excel_path = current_path + r'\myexcel.xlsx'

# 新建sheet的名称
ws1_name = 'mysheet'
ws2_name = 'testsheet'

# 创建一个excel表格，默认表格内存在一个名为‘Sheet’的sheet，
# 表格创建在缓存区，通过save(‘file_name.xlsx’)函数保存到指定目录下
if not pathlib.Path('myexcel.xlsx').exists():
    wb = openpyxl.Workbook()
else:
    wb = openpyxl.load_workbook('myexcel.xlsx')
# 将默认创建的名为‘Sheet’的sheet重命名为ws1_name
if 'Sheet' in wb.sheetnames:
    wb['Sheet'].title = ws1_name

#create new sheet,sheet name = ws2_name
wb.create_sheet(ws2_name)

# 获取sheet
ws1 = wb[ws1_name]
ws2 = wb[ws2_name]

# sheet 中的表头
titles_ws1 = ['IP','TYPE','VERSION','PLMN','ESN','EFF_DATE']
titles_ws2 = ['可以ping通的ip','ping不通的ip','LOGIN_FAIL']

#表格行首命名
for i in range(1,7):
    ws1.cell(1,i).value = titles_ws1[i-1]
for i in range(1,4) :
    ws2.cell(1,i).value = titles_ws2[i-1]
'''
for i in range(1,7):
    ws1.cell(1,i).value = titles_ws1[i-1]
    if i < 4:
        ws2.cell(1,i).value = titles_ws2[i-1]
'''

#最大行数,在某一列追加数据,m+2是防止m = 1时,range()无法执行
m = ws1.max_row
for row in range(2,m+2):
    cell_value = ws1['C' + str(row)].value
    if cell_value is None:
        ws1['C' + str(row)] = 'gyz'
# 保存文件
wb.save(new_excel_path)