openpyxl模块

openpyxl模块

第三方模块，可以对excle表格进行操作的模块

下载：pip3 install openpyxl

##写入数据
from openpyxl import Workbook
#获取Excel文件对象
wb_obj = Workbook()
wb1 = wb_obj.create_sheet('day15practice',1)
wb2 = wb_obj.create_sheet('day16practice',2)
#修改当前sheet标题：
print(wb1.title)
wb1.title = 'day17practice'
print(wb1.title)
#为第一张工作表添加值
#wb1['工作簿中表格的位置']
wb1['A10'] = 200
wb1['B10'] = 1000
wb1['C10'] = '=SUM(A10:B10)'

wb2['A10'] = 100

#生成excle表格
wb_obj.save('practice.xlsx')
print('excel表格生成成功')

#读取数据
from openpyxl import load_workbook
wb_obj = load_workbook('practice.xlsx')
print(wb_obj)
##wb_obj['表名']
wb1 = wb_obj['day16practice']
print(wb1['A10'].value)
#更改值
wb1['A10'] = 20
print(wb1['A10'].value)
wb_obj.save('practice.xlsx') #修改后需要保存


##批量写入100条数据
from openpyxl import Workbook
wb_obj = Workbook()
sheet1 = wb_obj.create_sheet('表一')
#wb1['表格位置'] = 对应的值
n = 1
for line in range(100):
    sheet1['A%s' %n] = line + 1
    n += 1
#wb_obj.save('dataaaa.xlsx')

##输入n条数据的字典
shee2 = wb_obj.create_sheet('表二')
dict1 = {
    'name':'lzn',
    'age':15,
    'gender':male
}
n = 1
for key,value in dict1.items():
    sheet2['A%s' %n] = key
    sheet2['B%s' %n] = value
    n += 1
wb_obj.save('ppppp.xlsx')