![6c78c0ec8d16332f2c2368e4441362f0.png](https://i-blog.csdnimg.cn/blog_migrate/a3f0b550114878ce30132107df6d63a1.png)
||每日一句||
“不开心就去超市听好消息”
![9bd5db13dbb2884ab7f48919afec4888.gif](https://i-blog.csdnimg.cn/blog_migrate/2ef363c45a8550e05f9a0443a8c94e2a.gif)
文章目录
- 向某个格子中写入内容并保存
- .append():向表格中插入行数据
- 简单的计算应用
- .insert_cols()和.insert_rows():插入空行和空列
- .delete_rows()和.delete_cols():删除行和列
- .create_sheet():创建新的sheet表格
向某个格子中写入内容并保存
from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
# 将“A2”单元格的数据改为了“刘备”
sheet["A2"] = "刘备"
# 另存为了“2.xlsx”文件
MyExcel.save(filename = "2.xlsx")
结果如下:
![6ee30c5b58e8b7ea3e6f0ffb7e7e6db0.png](https://i-blog.csdnimg.cn/blog_migrate/fb5887e583d0c87bb9cc848495c03130.png)
.append():向表格中插入行数据
from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
data = [
["孙悟空","1655"]
]
for row in data:
sheet.append(row)
MyExcel.save(filename = "1.xlsx")
结果如下:
![87dd505344f68390363305c7a020fe3c.png](https://i-blog.csdnimg.cn/blog_migrate/2b4289f7b5de45ef16bb63b1850e632b.png)
简单的计算应用
另外,python还支持多种的excel的数学函数,记忆相关有用的函数即可。from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
sheet["G1"] = "结果"
for i in range(2,7):
sheet["G{}".format(i)] ='=E{}+F{}'.format(i,i)
MyExcel.save(filename = "1.xlsx")
结果如下:
![1b1d96be41d02960e37d7141ce26078e.png](https://i-blog.csdnimg.cn/blog_migrate/9d79023fb782c25bb5fb9209a163960c.png)
.insert_cols()和.insert_rows():插入空行和空列
- .insert_cols(idx=数字编号, amount=要插入的列数),插入的位置是在idx列数的左侧插入;
- .insert_rows(idx=数字编号, amount=要插入的行数),插入的行数是在idx行数的下方插入;
from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
sheet.insert_cols(idx=4,amount=1)
sheet.insert_rows(idx=3,amount=2)
MyExcel.save(filename = "1.xlsx")
结果如下:
![0552822e32db08487a22d1f8863ddde5.png](https://i-blog.csdnimg.cn/blog_migrate/ab6712ad67df0af6e590e7909debd613.png)
.delete_rows()和.delete_cols():删除行和列
- .delete_rows(idx=数字编号, amount=要删除的行数)
- .delete_cols(idx=数字编号, amount=要删除的列数)
from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
# 删除第一列,第二行
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=2)
MyExcel.save(filename = "1.xlsx")
结果如下:
![f7d8d4188e7aeb9cd3e42759c47b76cb.png](https://i-blog.csdnimg.cn/blog_migrate/0f89c4d0f2aeca0eb95f6a5718a54cd2.png)
.create_sheet():创建新的sheet表格
from openpyxl import load_workbook
MyExcel = load_workbook(filename="1.xlsx")
sheet = MyExcel["表1"]
MyExcel.create_sheet("我是一个新的sheet")
MyExcel.save(filename = "test.xlsx")
结果如下:
![918058f6e406aef5f2467cc18352f2ee.png](https://i-blog.csdnimg.cn/blog_migrate/0867a2187c607e05d24111dcb6596c53.png)
.remove():删除某个sheet表 .copy_worksheet():复制一个sheet表到另外一张excel表 sheet.title:修改sheet表的名称-END- ▼ 推荐阅读
![b27d67a42081d5ba36b4ff1a2705f26f.png](https://i-blog.csdnimg.cn/blog_migrate/feafb330ec835235f5be10f4432ea112.png)
![0d4631164cbe7762f82915234a0d4945.png](https://i-blog.csdnimg.cn/blog_migrate/0d79eff3bb5f705871ae8015623a0ef0.png)
![4eb8a4affa6dac4616448ac26098d8e3.png](https://i-blog.csdnimg.cn/blog_migrate/6dbf009abac1dcf192a3b37bcc320509.png)