python 使用openpyxl加密excel python对excel文件加密

一、加密模块

1、有解密的加密方式（base64）

#base64加密
import base64
str_encrypt = input("输入要加密的字符串:\n")
base64_encrypt = base64.b64encode(str_encrypt.encode('utf-8'))
print("BASE64加密串:\n"+str(base64_encrypt,'utf-8'))


# 解密
str_decrypt = input("输入要解密的字符串:\n")
base64_decrypt = base64.b64decode(str_decrypt.encode('utf8'))
print("BASE64解密串:\n"+str(base64_decrypt,'utf-8'))

2、无解密的加密方式hashlib（碰撞检查）

1、不同数据加密后的结果一定不一致

        2、相同数据的加密结果一定是一致的

import hashlib

user_map = {}
# user_map = {'abc': '202cb962ac59075b964b07152d234b70'}
def lock(msg):
    b_msg = msg.encode('utf-8')
    # 将数据进行加密
    cipher = hashlib.md5(b_msg)
    # 拿到加密后的字符串
    return cipher.hexdigest()

def register():
    print('注册...')
    user = input('user: ')
    pwd = input('pwd: ')
    lock_pwd = lock(pwd)
    # print(user)
    # print(lock_pwd)
    user_map[user] = lock_pwd
    print(user_map)

register()

def loging():
    print('登录...')
    user = input('user: ')
    pwd = input('pwd: ')
    if user in user_map:
        lock_pwd = lock(pwd)
        if user_map[user] == lock_pwd:
            print('登录成功')
        else:
            print('登录失败')
    else:
        print('账号不存在')
loging()

三种方式：

2.1、直接传参

# part1
cipher = hashlib.md5('要被加密的字符串'.encode('utf-8'))
print(cipher.hexdigest())

cipher = hashlib.md5('要被加密的字符串'.encode('utf-8'))
print(cipher.hexdigest())
# 整体被加密的数据一样，加密后的结果就一样
cipher = hashlib.md5('要被加密'.encode('utf-8'))
cipher.update('的字'.encode('utf-8'))
cipher.update('符串'.encode('utf-8'))
print(cipher.hexdigest())

2.2、加盐

# part2：加盐 - add salt
cipher = hashlib.md5()
msg = input('msg：')
# 加前盐:最好与要加密的数据类型一致
cipher.update('前盐'.encode('utf-8'))
# 要被加密的数据
cipher.update(msg.encode('utf-8'))
# 加后盐
cipher.update('后盐'.encode('utf-8'))
print(cipher.hexdigest())

print(hashlib.md5('前盐123后盐'.encode('utf-8')).hexdigest())

2.3、加密长度不一样

# part3:加密长度不一样
cipher = hashlib.sha3_512('abc123呵呵'.encode('utf-8'))
print(cipher.hexdigest())

3、hmac模块

 和md5()区别：
 hashlib.md5():
      -- 可以有初始参数，可以没有初始参数
      -- 可以通过update再添加新内容
 hmac.new():
      -- 必须有初始参数
      -- 可以通过update再添加新内容

import hmac

cipher = hmac.new('盐'.encode('utf-8'))
cipher.update('数据'.encode('utf-8'))
print(cipher.hexdigest())

二、ini配置文件操作模块

# my.ini文件
[server]
name = mysql
version = 20000

[client]
name = owen
adress = 192.168.11.174

 操作文件的代码

from configparser import ConfigParser
# 初始化配置文件的操作对象
parser = ConfigParser()
# 读
parser.read('my.ini', encoding='utf-8')
# 大分类：section
print(parser.sections())
# 某分类下的keys：option
print(parser.options('server'))
print(parser.options('client'))
for section in parser.sections():
    print(parser.options(section))
# 获取某section下某option的具体值
res = parser.get('server', 'version')
# res = parser.getfloat('server', 'version')
print(res, type(res))

# 写
parser.set('server', 'version', '20000')  # 写到内存
parser.write(open('my.ini', 'wt'))

三、shell指令操作模块

import subprocess

# subprocess.run('dir', shell=True)

order = subprocess.Popen('dir1',
                         shell=True,
                         # 存放指令执行成功的信息管道
                         stdout=subprocess.PIPE,
                         # 存放指令执行失败的信息管道
                         stderr=subprocess.PIPE
                         )
print(order.stdout)
success_msg = order.stdout.read().decode('GBK')
print(success_msg)
error_msg = order.stderr.read().decode('GBK')
print(error_msg)
# 实际项目中，会接着对success_msg加以分析处理


order = subprocess.run('dir',
                       shell=True,
                       # 存放指令执行成功的信息管道
                       stdout=subprocess.PIPE,
                       # 存放指令执行失败的信息管道
                       stderr=subprocess.PIPE
                       )
print(order.stdout)
success_msg = order.stdout.decode('GBK')
print(success_msg)
error_msg = order.stderr.decode('GBK')
print(error_msg)

四、excel操作模块

import xlrd
# 将excel文件读成对象
workbook = xlrd.open_workbook('寂寞.xlsx')
# 获取所有所有表格名称
# print(workbook.sheet_names())
# 选取一个表
sheet = workbook.sheet_by_index(0)
# print(sheet.name, sheet.nrows, sheet.ncols)
# 整行整列信息
print(sheet.row(1))
print(sheet.col(0))
# 单元格
print(sheet.cell(13, 4))
print(sheet.cell(13, 4).ctype)
print(sheet.cell(13, 4).value)
print(sheet.row(13)[4])

# 时间
info = sheet.cell(2, 0).value
print(info)
info = xlrd.xldate_as_datetime(info, 0)
print(info)


import xlwt
# 创建工作簿
work = xlwt.Workbook()
# 创建一个表
sheet = work.add_sheet("求败")

# 创建一个字体对象
font = xlwt.Font()
font.name = "Times New Roman"  # 字体名称
font.bold = True  # 加粗
font.italic = True  # 斜体
font.underline = True  # 下划线
# 创建一个样式对象
style = xlwt.XFStyle()
style.font = font

keys = ['Owen', 'Zero', 'Egon', 'Liuxx', 'Yhh']
# # 写入标题
for k in keys:
    # sheet.write(0, keys.index(k), k, style)
    sheet.write(keys.index(k) + 2, 1, k, style)
# # 写入数据
# sheet.write(1, 0, 'cool', style)


# 保存至文件
work.save("孤独.xlsx")

excel写表头示例：

import xlwt

class XLS:
    def __init__(self):
        self.sheet_name = '1'

    def set_style(self, name, height, bold=False):
        style = xlwt.XFStyle()  # 初始化样式

        font = xlwt.Font()  # 为样式创建字体
        font.name = name  # 'Times New Roman'
        font.bold = bold
        font.color_index = 4
        font.height = height
        style.font = font
        return style

    def write_row(self, sheet1, row0):
        for i in range(0, len(row0)):
            sheet1.write(0, i, row0[i], self.set_style('Times New Roman', 220, True))

    def excel_write_none(self, output=''):
        wbk = xlwt.Workbook(encoding='utf-8', style_compression=2)
        sheet1 = wbk.add_sheet(self.sheet_name)
        row0 = ["服务", "协议", "IP地址", "端口", "一级分类", "二级分类", "二级分类中文", "厂商", "厂商中文", "型号", "版本", "城市", "省份", "更新时间",
                "是否有漏洞", "漏洞详情", "漏洞评分", '漏洞名称', '漏洞类型', '漏洞等级', '漏洞描述', "漏洞地址"]
        self.write_row(sheet1, row0)
        wbk.save(output)
XLS().excel_write_none('test.xls')

五、xml模块

xml文件：

<data>
    <country name="Liechtenstein">
        <rank updated="yes">2</rank>
        <year>2018</year>
        <gdppc>141100</gdppc>
        <neighbor direction="E" name="Austria" />
        <neighbor direction="W" name="Switzerland" />
    </country>
    <country name="Singapore">
        <rank updated="yes">5</rank>
        <year>2011</year>
        <gdppc>59900</gdppc>
        <neighbor direction="N" name="Malaysia" />
    </country>
    <country name="Panama">
        <rank updated="yes">69</rank>
        <year>2011</year>
        <gdppc>13600</gdppc>
        <neighbor direction="W" name="Costa Rica" />
        <neighbor direction="E" name="Colombia" />
    </country>
</data>

python操作

import xml.etree.ElementTree as ET

tree = ET.parse('my.xml')
# 根节点
root_ele = tree.getroot()
print(root_ele.tag)

# 遍历根节点
for ele in root_ele:
    # print(ele)
    # print(ele.attrib)
    # print(ele.attrib['name'])
    if ele.attrib['name'] == 'Liechtenstein':
        for e in ele:
            print(e.text)

print(list(root_ele.iter('year'))[0].text)
print(root_ele.find('country'))
print(root_ele.findall('country'))

# 修改
list(root_ele.iter('year'))[0].text = '2018'
tree.write('my.xml')

# 增
my_ele = ET.Element('MyEle')
my_ele.text = 'new_ele'
my_ele.attrib = {'name': 'my_ele'}
root_ele.append(my_ele)
tree.write('my.xml')

# 删
my_ele = root_ele.find('MyEle')
# print(my_ele)
root_ele.remove(my_ele)
tree.write('my.xml')

六、数据格式分类

# xml | json

import json
# json.dumps() json.dump()
# json.loads() json.loads()

json：json就是一种特殊格式的字符串
# 格式要求：
1.只能由{}和[]嵌套形成
2.只能有一个根: 最外层要么是{}（推荐），要么是[]
3.所以key都是字符串类型，且json字符串中所有字符串类型必须用""包裹
4.json字符串中value类型可以为: number | boolean | null | 字符串 | dic | list
dic = {'': [1, 3.14, True, None, "字符串"]}
print(json.dumps(dic))

j_str = """{"key": [1, 3.14, true, null, "字符串"]}"""
print(json.loads(j_str))

'''
{
    "info": [1, 2, 3, 4, 5]
}
'''

# xml格式数据：
1.xml文件中都是由自定义标签嵌套形成，区分大小写
2.只能有一个根标签：其他内容或标签都被该标签包裹
3.标签名为key，标签中的值为value
4.与json不同的是，xml标签可以额外添加属性来标识key的区分度