#修改表格内容
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
print(sheet['A1'].value) #ID
sheet['A1']='标识'
print(sheet['A1'].value) #标识
workbook.save(filename='F:\\pythonProject\\test.xlsx')
#按行追加数据到excel表格中
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
data=[['1005','黎明',12,67],['1006','徕卡',12,84],['1007','武鸣',11,45]]
for i in data:
sheet.append(i)
workbook.save(filename="F:\\pythonProject\\test.xlsx")
#在Python中使用excel公式,
公式为：=IF(RIGHT(E2,2)="cm",E2,SUBSTITUTE(E2,"m","")&"cm")
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
sheet['F1']='标准身高'#增加列名
for i in range(2,9):
sheet['F{}'.format(i)]='=IF(RIGHT(E{},2)="cm",E{},SUBSTITUTE(E{},"m","")&"cm")'.format(i,i,i)#每一列更新单位
workbook.save('test.xlsx')
#增加空白行和空白列
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
sheet.insert_rows(idx=4,amount=2) #在第四行前，插入2行空白行
sheet.insert_cols(idx=2,amount=1) #在第二列前，插入一列空白列
workbook.save(filename='F:\\pythonProject\\test.xlsx')
#删除行删除列
from openpyxl import load_workbook
workbook=load_workbook(filename='F:\\pythonProject\\test.xlsx')
sheet=workbook.active
sheet.delete_cols(idx=2,amount=1)#从第二列开始删除，删除一列
sheet.delete_rows(idx=3,amount=2)#从第三行开始删除，删除2行
workbook.save(filename='F:\\pythonProject\\test.xlsx')