python如何向excel中写入某些内容?
1)修改表格中的内容
① 向某个格子中写入内容并保存
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet["A1"] = "哈喽" # 这句代码也可以改为cell = sheet["A1"] cell.value = "哈喽"
workbook.save(filename = "哈喽.xlsx")
"""
注意:我们将“A1”单元格的数据改为了“哈喽”,并另存为了“哈喽.xlsx”文件。
如果我们保存的时候,不修改表名,相当于直接修改源文件;
"""
1
2
3
4
5
6
7
8
9
结果如下:
② .append():向表格中插入行数据
.append()方式:会在表格已有的数据后面,增添这些数(按行插入);
这个操作很有用,爬虫得到的数据,可以使用该方式保存成Excel文件;
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
data = [
["唐僧","男","180cm"],
["孙悟空","男","188cm"],
["猪八戒","男","175cm"],
["沙僧","男","176cm"],
]
for row in data:
sheet.append(row)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
7
8
9
10
11
12
结果如下:
③ 在python中使用excel函数公式(很有用)
# 这是我们在excel中输入的公式
=IF(RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm")
# 那么,在python中怎么插入excel公式呢?
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet["D1"] = "标准身高"
for i in range(2,16):
sheet["D{}".format(i)] = '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
7
8
9
10
结果如下:
此时,你肯定会好奇,python究竟支持写哪些“excel函数公式”呢?我们可以使用如下操作查看一下。
import openpyxl
from openpyxl.utils import FORMULAE
print(FORMULAE)
1
2
3
结果如下:
④ .insert_cols()和.insert_rows():插入空行和空列
.insert_cols(idx=数字编号, amount=要插入的列数),插入的位置是在idx列数的左侧插入;
.insert_rows(idx=数字编号, amount=要插入的行数),插入的行数是在idx行数的下方插入;
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet.insert_cols(idx=4,amount=2)
sheet.insert_rows(idx=5,amount=4)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
结果如下:
⑤ .delete_rows()和.delete_cols():删除行和列
.delete_rows(idx=数字编号, amount=要删除的行数)
.delete_cols(idx=数字编号, amount=要删除的列数)
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
# 删除第一列,第一行
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
7
结果如下:
⑥ .move_range():移动格子
.move_range(“数据区域”,rows=,cols=):正整数为向下或向右、负整数为向左或向上;
# 向左移动两列,向下移动两行
sheet.move_range("C1:D4",rows=2,cols=-1)
1
2
演示效果如下:
⑦ .create_sheet():创建新的sheet表格
.create_sheet(“新的sheet名”):创建一个新的sheet表;
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
workbook.create_sheet("我是一个新的sheet")
print(workbook.sheetnames)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
结果如下:
⑧ .remove():删除某个sheet表
.remove(“sheet名”):删除某个sheet表;
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(workbook.sheetnames)
# 这个相当于激活的这个sheet表,激活状态下,才可以操作;
sheet = workbook['我是一个新的sheet']
print(sheet)
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
7
8
9
结果如下:
⑨ .copy_worksheet():复制一个sheet表到另外一张excel表
这个操作的实质,就是复制某个excel表中的sheet表,然后将文件存储到另外一张excel表中;
workbook = load_workbook(filename = "a.xlsx")
sheet = workbook.active
print("a.xlsx中有这几个sheet表",workbook.sheetnames)
sheet = workbook['姓名']
workbook.copy_worksheet(sheet)
workbook.save(filename = "test.xlsx")
1
2
3
4
5
6
结果如下:
⑩ sheet.title:修改sheet表的名称
.title = “新的sheet表名”
workbook = load_workbook(filename = "a.xlsx")
sheet = workbook.active
print(sheet)
sheet.title = "我是修改后的sheet名"
print(sheet)
1
2
3
4
5
结果如下:
⑪ 创建新的excel表格文件
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "表格1"
workbook.save(filename = "新建的excel表格")
1
2
3
4
5
6
结果如下:
⑫ sheet.freeze_panes:冻结窗口
.freeze_panes = “单元格”
workbook = load_workbook(filename = "花园.xlsx")
sheet = workbook.active
print(sheet)
sheet.freeze_panes = "C3"
workbook.save(filename = "花园.xlsx")
"""
冻结窗口以后,你可以打开源文件,进行检验;
"""
1
2
3
4
5
6
7
8
结果如下:
⑬ sheet.auto_filter.ref:给表格添加“筛选器”
.auto_filter.ref = sheet.dimension 给所有字段添加筛选器;
.auto_filter.ref = “A1” 给A1这个格子添加“筛选器”,就是给第一列添加“筛选器”;
workbook = load_workbook(filename = "花园.xlsx")
sheet = workbook.active
print(sheet)
sheet.auto_filter.ref = sheet["A1"]
workbook.save(filename = "花园.xlsx")
1
2
3
4
5
网站声明:如果转载,请联系本站管理员。否则一切后果自行承担。
加入交流群
请使用微信扫一扫!