57 读写Excel

import openpyxl 

# 写入Excel 

# 新建一个workbook 

wb = openpyxl.Workbook() 

# 获取当前的活动窗口

ws = wb.active 

# 创建一个新sheet

ws1 = wb.create_sheet("Mysheet")  

# 更改sheet名称

ws.title = "New Title" 

# 向sheet中输入值

ws['F6'] = 4 

cell_range = ws['A1':'C10'] 

for cell in cell_range:     

    for i in cell:         

        i.value = 9 

colC = ws['C'] 

wb.save('aa.xlsx') 

wb.close 

 

import openpyxl

wb = openpyxl.load_workbook(r"data/test.xlsx")

# 查看工作表名称

ws = wb['profits']

dict = {}

for row in ws.iter_rows(min_row=2, max_col=5, max_row=len(ws['D'])):   

    dict[row[3].value] = dict.get(row[3].value, 0) + row[4].value

#填充数据

a = len(ws['H'])

end = 'I' + str(a)

use_range = ws['H1':end]

for i in use_range:    

    if dict.get(i[0].value, 0) != 0:        

        i[1].value = dict[i[0].value]

wb.save('t11.xlsx')

wb.close