Python+Excel+数据——一键通关的快乐ing
人生苦短,学点Python
梳理工作和项目流程,在对接报告编制工具之前,对有效数据进行收集和筛查便变得尤为重要,特别是在文件管理没有一定概念的初期,标准的输入和输出需要形成一整套规范的模板,这无疑是一件令人头疼的事情。人需要解决的只有面对人的那一部分问题,而剩余步骤的工作则应该让工具的进步协助人脑,这才是解决问题的根本。
需求是第一生产动力
1.根据调研表的资产信息以及系统安全等级SAG数值的不同,制定不同的测评表;
2.对标准进行建模,用不同的数据模型进行存储,考虑简单易行的方式,这里就不上数据库了,而是将标准中的数据和实际需要的增量数据进行合并,并存储在SxAxGx.xlsx文件中,当做是数据源来维护.
善工之事以器利之
1.安装Python,Python官网:https://www.python.org/
tips:在安装过程中需要自行勾选“环境变量”,这样可以省去后期配置环境变量的步骤。
2.安装pycharm,pycharm官网:https://www.jetbrains.com/
找到上图这个英文版的页面,软件版本选择免费的社区版,不要相信什么破解的任何版本,第一,高级功能你暂时还用不上,第二破解失败居多还有各种bug,比如破解之后,第二次打开又需要license…诸如此类。也不建议用VScode,我试过了,import就失败了,虽然pycharm也有失败的时候…有些依赖的组件和包还是pycharm比较优秀,可以直接浪红让你去下载,别的IDE的扩展也可以自行根据喜好下载安装。
3.配置pycharm的汉化和字体格式,去除工作区的vim模式(这样可以用复制粘贴的快捷键),设置工作区的自动换行。
4.可以去网上搜课来看,如果代码摸鱼多年的——视频链接:https://www.bilibili.com/video/BV1m4411K7Tc/,这款视频教你直接对Excel进行读取、遍历、筛选、格式化以及输出等简单操作(很有成就感那种)。
5.有关于库模块在windows环境下的安装问题,不仅仅是openpyxl模块,后面还有制表的prettytable等模块,网上的搜的时候要针对Python2.x和Python3.x进行区分,并且不同模块用到的函数语句也不尽相同。
好记性不如PCPad
调查表——机房篇
上图为“调查表.xlsx”物理机房sheet的部分截图,逻辑是:将有效的已知关键信息列B和列C的数据取出来,判断数据的有效性,如“重要程度”不是一般,则并保存在jfAsset.py文件中。
源代码如下:
# main:对‘调查表.xlsx’进行整理
# 赋值:打开‘调查表.xlsx’
wbData=openpyxl.load_workbook('assess-require/调查表.xlsx')
# func:这里应该对wbData进行遍历,将所有sheet的名称方进行列表中,需要完善!!!
# 赋值:对机房的sheet进行整理
jfSH=wbData['表2-物理机房']
# 定义:字典,机房数据
jfData={}
# 遍历:将数据放进字典里,完整所有行需要多加个1
for row in range(2,jfSH.max_row+1):
# 确认sheet’机房名称‘的坐标并赋值
jfName=jfSH['B'+str(row)].value
# 确认sheet‘重要程度’的坐标并赋值
major=jfSH['D'+str(row)].value
# 判断:’重要程度‘是否是’关键‘或’重要‘
if (major=='关键')or(major=='重要'):
# 调用:初始化,格式化
jfData.setdefault(jfName,major)
# func:打开一个文本进行数据的保存,可以存为py文件
# 赋值:打开文件
outFile=open('dataCool/asset/jfAsset.py','w',encoding='utf-8')
# 调用:用pprint格式化数据,并写入文件中
outFile.write('allData='+pprint.pformat(jfData))
上图为jfAsset.py数据文件中的数据。
测评表导出工具
开始先从数据源着手,因为是从读到格式化输出的一个过程,所以。。。。。。手动写了Excel源表“S2A2G2.xlsx”.
其他“SxAxGx.xlsx”类似,当然格式内容什么的都可以自己调。原本是想用Python+PDF的方式从GB标准里将数据读出来再写算法格式化数据,但限于算法的代码量和我的能力,暂时搁置了,具体代码和输出文件内容如下:
数据源处理好了,咱们开始飞代码吧~~~︿( ̄︶ ̄)︿~~~
由于写的时候没考虑功能函数,所以代码中用func标识了,日后好将函数分出去。
下面是咱们用到的import:
# coding:utf-8
# 导入系统模块
import sys
# 导入系统模块
import os
# 导入Excel文件处理模块
import openpyxl
# 导入制表模块
import prettytable
# 导入re模块,对字符串进行校验
import re
# 导入打印模块
import pprint
# 导入openpyxl模块中的列-数字-字母转换功能
from openpyxl.utils import get_column_letter
# 导入 collection模块字典初始化功能,可用作一键多值
from collections import defaultdict
# 到入制表的功能
from prettytable import PrettyTable
# 导入机房数据包内的功能或者变量,因为包是自己设置的,必须将程序目录前一层的目录设置为根源目录,并且包下的文件如果没有需要提前生成!!!
# tips:目前allData里只有机房的资产信息
from dataCool.asset.jfAsset import allData
这里是main()主函数体,留作以后分函数,分文件用:
# main:根据输入的系统安全等级、制表信息和引用的关联资产信息,输出相应的测评表
# 定义:字典,tips:可以做成输入字符串切割判断,或者用数据文件导入引用
sagDict = {'G1': ['S1A1'], 'G2': ['S1A2', 'S2A2', 'S2A1'], 'G3': ['S1A3', 'S2A3', 'S3A3', 'S3A2', 'S3A1'],
'G4': ['S1A4', 'S2A4', 'S3A4', 'S4A4', 'S4A3', 'S4A2', 'S4A1', ],
'G5': ['S1A5', 'S2A5', 'S3A5', 'S4A5', 'S5A5', 'S5A4', 'S5A3', 'S5A2', 'S5A1']}
# 打印:提示信息
print('\n\n请输入是几级系统(1~5):')
# 定义:字符串,G-表示系统等级
gLevel = ''
# 定义:字符串,SA-表示业务和服务等级
saLevel = ''
# 定义:整数,循环锁控制
flag = 0
flagOut = 0
功能函数:func:确认输入的系统等级(G)
# func:确认输入的系统等级(G)
# 循环:输入-判断
while flag == flagOut:
# 赋值:接收input函数接收的系统等级字符串
level = input()
# 赋值:接收re.search函数返回的简单的值,None表示没有特殊字符
# W:表示非单词字符
reStr01 = re.search(r"\W", level)
# w:表示非数字
reStr02 = re.search(r"\D", level)
# 判断:输入为‘exit’
if level == 'exit':
# 调用:退出,并打印
sys.exit('已退出。。。。。。')
# 判断:输入为空,输入含特殊字符,输入含有非数字的字符,输入不在1~5之间,range最后区间要加 1
elif level == '' or reStr01 is not None or reStr02 is not None or int(level) not in range(1, 5 + 1):
# 打印:提示信息
print('输入有误,请重新输入!')
# 赋值:循环锁自增
flag += 1
# 赋值:循环锁自增
flagOut += 1
# 赋值:内部变量值赋给外部变量
gLevel = level
# 打印:业务和服务等级情况
print('等级组合有:{}'.format(sagDict['G' + level]))
# 打印:提示信息
print('\n请输入S和A的等级情况(连续数字即可):')
上图:目前只制定S2A2G2。
功能函数:func:确认输入的业务和服务等级(SA)
# func:确认输入的业务和服务等级(SA)
# 赋值:循环锁复位
flag = 0
flagOut = 0
# 循环:输入-判断
while flag == flagOut:
# 输入系统等级
level = input()
# 赋值:接收re.search函数返回的简单的值,None表示没有特殊字符
# W:表示非单词字符
reStr01 = re.search(r"\W", level)
# w:表示非数字
reStr02 = re.search(r"\D", level)
# 判断:输入为‘exit’
if level == 'exit':
# 调用:退出,并打印
sys.exit('已退出。。。。。。')
# 输入不为空,长度为2,不为数字字符,输入数的范围分别在1~系统等级值之间
elif level == '' or len(level) != 2 or reStr01 is not None or reStr02 is not None or int(level[0]) not in range(1, int(gLevel) + 1) or int(
level[1]) not in range(1, int(gLevel) + 1):
# 打印提示
print('输入有误,请重新输入!')
# 循环变量自增
flag += 1
# 输入数至少有和系统等级值相等的
elif level[0] != gLevel and level[1] != gLevel:
# 打印提示
print('请重新输入至少和系统等级相同的数字!')
# 循环变量自增
flag += 1
# 循环变量自增
flagOut += 1
# 业务和服务等级赋值
saLevel = level
# 打印:系统、业务和服务的等级
print('目前系统等级为:S{}A{}G{}\n'.format(saLevel[0], saLevel[1], gLevel))
上图:目前只制定了S2A2G2。
定义相关变量
# tips:可以将所有文件读取存储成完整的数据文件,或不用每次打开所有文件进行遍历
# 目前系统仅用到二级、三级、四级:S2A2G2、S3A3G3、S4A4G4
# 定义:字典,sag文件的路径,sag文件是底层数据文件,为所有数据汇总的源
pathDict = {'1': 'dataCool/base/S2A2G2.xlsx', '2': 'dataCool/base/S2A2G2.xlsx', '3': 'dataCool/base/S3A3G3.xlsx',
'4': 'dataCool/base/S4A4G4.xlsx', '5': 'dataCool/base/S5A5G5.xlsx'}
# 定义:字典,‘目录’sheet内部的索引
indDict = {}
# 定义:字典,数据的存储格式为:{‘sheet’:{’A1‘:[A1,A2,A3...]...}...}
sag_Data_dict = {}
# 定义:字符串,接收文件描述符,即文件入口的地址
wbSag01 = ''
# 定义:字符串,接收文件描述符,即文件入口的地址
wbSag02 = ''
# 定义:列表,存放打开的一个Excel文件中所有sheet名称
shList01 = []
# 定义:列表,存放打开的一个Excel文件中所有sheet名称
shList02 = []
功能函数:func:根据输入的内容和数据文件进行测评表的编制
# func:目前只对SAG等级系统进行操作,输出report测评表,目前只有S2A2G2,需要完善!!!
# 判断:S和A相同
if int(saLevel[0]) == int(saLevel[1]):
# 调用:异常处理
try:
# 赋值:SAG表的工作簿的文件描述符
wbSag01 = openpyxl.load_workbook(pathDict[saLevel[0]])
# 抛出:错误类型
except IOError:
# 打印:提示信息
print('读取文件或权限获取失败!!!')
# 调用:退出,并打印错误信息
sys.exit('已退出处理。。。')
# func:遍历SAG表里的数据,并对sag_Data_dict字典进行格式化存放数据
# 遍历:SAG表里的所有sheet
for sheet in wbSag01:
# 调用:将sheet名追加到shList01列表中
shList01.append(sheet.title)
# 遍历:列表的每行
for row in range(1, sheet.max_row + 1):
# 遍历:列表的每列
for col in range(1, sheet.max_column + 1):
# 赋值:调用get_column_letter,须提前导入该函数,功能:对列进行数字-字母的切换
colStr = get_column_letter(col)
# 调用:字典初始化
sag_Data_dict.setdefault(sheet.title, {})
# 调用:字典初始化,并追加数据
sag_Data_dict[sheet.title].setdefault(sheet[colStr + '1'].value, []).append(
sheet[colStr + str(row)].value)
# 遍历:’目录‘sheet
for i in range(1, wbSag01['目录'].max_row + 1):
# 赋值:更新,indDict字典:key是’名称‘,value是’序号‘
indDict[wbSag01['目录']['B' + str(i)].value] = wbSag01['目录']['A' + str(i)].value
# func:输入制表的名字,并判断,目前后续的功能只对安全物理环境进行展开。。。需要完善!!!
# 打印:提示信息
print('下面是所有层面的内容:')
# 定义:整数,控制打印数
ptVar = 0
# 遍历:'目录'sheet所含的信息
for str in sag_Data_dict['目录']['名称']:
# 判断:控制打印数是3
if ptVar == 3:
# 打印:并换行
print(str)
# 赋值:控制打印数复位
ptVar = 0
# 打印:以制表的方式
print(str, end='\t')
# 赋值:控制打印数自增
ptVar += 1
# 打印:提示信息
print('\n\n请输入你需要制的表名称(用逗号分隔):')
# 定义:列表,存放制表的名称
planeList = []
# 赋值:循环锁复位
flag = 0
flagOut = 0
# 循环:输入-判断
while flag == flagOut:
# 赋值:输入制表名字
sheetInfo = input()
# 赋值:对输入内容进行切割,按照空格的方式保存到planeList列表中
planeList = sheetInfo.split(',')
# 遍历:planeList列表
for str in planeList:
# 判断:输入的制表名字在sag_Data_dict字典相应的列表里
if str not in sag_Data_dict['目录']['名称'] or str == sag_Data_dict['目录']['名称'][0]:
# 打印:提示信息
print('输入有误,请重新输入!')
# 赋值:循环锁自增
flag += 1
# 判断:输入为‘exit’
elif str == 'exit':
# 调用:退出,并打印
sys.exit('已退出。。。。。。')
# 赋值:循环锁自增
flagOut += 1
# func:制表样式显示,目前不对安全要求汇总、扩展测评等sheet进行后续操作,这里的功能需要完善!!!
# 定义:列表,表头
row_Head_list = []
# 定义:列表,正文第一行信息
row_Body_list = []
# 遍历’目录‘sheet中的名称
for str in planeList:
# 判断:输入含有’扩展‘相关内容
if re.search(r'扩展', str) is not None:
# 调用:退出,并打印错误信息
sys.exit('扩展功能未开启')
# 判断:输入含有’名称‘相关内容
elif re.search(r'名称', str) is not None:
# 调用:退出,并打印错误信息
sys.exit('此表无有效信息')
# 判断:输入含有‘汇总’相关内容
elif re.search(r'汇总', str) is not None:
# 调用:退出,并打印错误信息
sys.exit('汇总功能暂未开启')
# 遍历:输入的制表名称所对应的sheet,用indDict字典将名称和sheet实际的名称进行置换,从wbSag01工作簿中遍历出来
for i in range(1, wbSag01[indDict[str]].max_column + 1):
# 调用:表头列表内容更新
row_Head_list.append(wbSag01[indDict[str]].cell(row=1, column=i).value)
# 调用:正文内容列表更新
row_Body_list.append(wbSag01[indDict[str]].cell(row=2, column=i).value)
# 赋值:将PrettyTable制表函数返回的句柄(参数)传给table
table = PrettyTable('')
# 调用:以列的格式进行填充
table.add_column('内容:', row_Head_list)
table.add_column('样式:', row_Body_list)
# 打印:table,完成制表显示,暂无发现对字体大小的样式说明,输出内容较多,先凑合着兑付吧!!!
print(table)
# 这里默认为'通用测评表',所以循环一次结束后用break直接跳出外层for循环,不再对其他sheet进行表头和正文的内容进行追加,避免重复制表内容
break
# func:确认输入的制表内容,并和制表名字一起打印
# 定义:列表,存放制表sheet的行首标题
plane_Data_list = []
# 赋值:循环锁复位
flag = 0
flagOut = 0
# 循环:输入-判断
while flag == flagOut:
# 打印:提示信息
print('请输入你想建表的内容(用逗号分隔)')
# 赋值:输入制表的内容,即表头
sheetInfo = input()
# 赋值:将输入内容切割放进plane_Data_list列表里,以逗号的方式,
plane_Data_list = sheetInfo.split(',')
# 遍历:切割内容 plane_Data_list列表
for str in plane_Data_list:
# 判断:输入的字符串有在 row_Head_list列表里
# tips:这个 row_Head_list 可以是前一个功能函数的变量,在这里因为是写在一个函数体中,可以直接调用,如果后期对函数进行模块分割,这里的 row_Head_list 要从原函数中进行转换
if str not in row_Head_list:
# 打印:提示信息
print('输入有误,请重新输入!')
# 循环锁自增
flag += 1
# 循环锁自增
flagOut += 1
# 打印:提示信息
print('\n\n建表名称为:')
# 遍历:制表名称 planeList列表
for i in range(len(planeList)):
# 打印:制表的名字,已制表的格式打印
print(planeList[i], end='\t')
# 打印:提示信息
print('\n建表内容为:')
# 遍历:建表内容 plane_Data_list列表
for i in range(len(plane_Data_list)):
# 打印:制表的内容,已制表的格式打印
print(plane_Data_list[i], end='\t')
# function:制定不同安全层面的测评表
# 定义:变量
jfStr = '安全物理环境'
txStr = '安全通信网络'
# func:制定安全物理环境测评表
# 判断:机房在列表中
if jfStr in planeList:
# 定义:列表
jfList = []
# 打印:提示信息
print('\n建表机房对象为:')
# 遍历机房的调研数据,并打印key值
for key, _ in allData.items():
print(key, end='\t')
jfList.append(key)
# 执行:外部jf.py文件,jf.py:获取’调查表.xlsx‘文件中物理机房的有效信息
os.system('jf.py')
# func:数据写入测评表report
# 定义:变量
a = 0
# 遍历:根据plane_Data_list列表元素,取得sag_Data_dict字典对应其他列表
for i in range(0, len(plane_Data_list)):
# 判断:a 大于 sag_Data_dict字典对应其他列表的长度
if a > len(sag_Data_dict[indDict[jfStr]][plane_Data_list[i]]):
# 赋值:不变,tips:此处得写什么。。。不然还报错
a = a
else:
# 赋值:将sag_Data_dict字典对应其他列表的长度 赋值给 a
a = len(sag_Data_dict[indDict[jfStr]][plane_Data_list[i]])
# 赋值:打开的空文件的描述符
wSh = openpyxl.Workbook()
# 遍历:步数为 allData字典的长度
for i in range(len(allData)):
# 赋值:将新创建的sheet的句柄丢给 sh
sh = wSh.create_sheet(index=i, title=jfList[i])
# 遍历:步数为a+1,根据sag_Data_dict字典对应其他列表中的元素存储的方式为一整列,所以a为数据的最大行数,再加上表头,所以取 +1
for row in range(1, a + 1):
# 遍历:步数为plane_Data_list列表的长度+1,plane_Data_list 中存储的是用户输入的建表内容
for col in range(1, len(plane_Data_list) + 1):
# 调用:cell为Excel的最小单元——格,row 锁定横坐标,column锁定列坐标,value进行取值
sh.cell(row=row, column=col, value=sag_Data_dict[indDict[jfStr]][plane_Data_list[col - 1]][row - 1])
# 调用:异常处理
try:
# 调用:remove用作对Excel列表的删除(Python3.0以上)
wSh.remove(wSh['Sheet'])
# 抛出:异常类型
except IOError:
# 调用:exit用作退出程序,并返回错误描述(可用作其他程序的判断)
sys.exit('不存在那个文件,文件操作失败!')
else:
# 调用:保存路径和文件名
wSh.save('assess-report/安全物理环境.xlsx')
# 判断:通信网络在列表中,后面判断类似,需要完善!!!
elif txStr in planeList:
sys.exit('现在只有机房,正常退出')
sys.exit('无论如何也得退出。。。。。。')
# S和A不同
elif int(saLevel[0]) != int(saLevel[1]):
sys.exit('暂不支持SA不同')
# 打开SAG表的工作簿
wbSag01 = openpyxl.load_workbook(pathDict[saLevel[0]])
wbSag02 = openpyxl.load_workbook(pathDict[saLevel[1]])
sys.exit('千山万水总是情,多待一会儿行不行。。。。。。')
文章结语
1.在使用pycharm编写Python项目的时候,如果遇到导入自己写的函数包的时候,需要将工程文件设置为根源:
2.由于不能像数据库一样将数据制表并进行关联,所有后期数据源的数据模型会根据实际需求增加。
3.涉及到后期的工程项目管理,或将部分功能函数块分出主函数,形成可调用的函数文件。
4.根据目标需求,完善代码。