1、以下表举例说明[1,1]
2、安装及导入
pip install openpyxl
import openpyxl
3、加载Excel文件,返回一个工作簿对象, openpyxl只能处理 .xlsx格式的表格
wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
4、选择表
table = wb['表2'] # <Worksheet "表2">
5、显示所有的表
list = wb.sheetnames # ['表1', '表2', '表3']
6、获取最大行数及列数
r = table.max_row # 10
c = table.max_column # 5
7、获取具体单元格的值
c = table.cell(row=1,column=1).value # 学号
8、写入数据,并保存文件
table.cell(row=10,column=1,value='python666')
wb.save(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
9、获取某列的所有值
for i in range(1,table.max_row+1):
print(table.cell(row=i, column=2).value)
10、获取某行的所有值
for i in range(1,table.max_column+1):
print(table.cell(row=2, column=i).value)
11、获取所有值(元组)
import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
table = wb['表2']
for i in table.values:
print(i)
执行结果:
('学号', '姓名', '年龄', '专业', '考试时间')
('B00001', '张1', 18, '语文', datetime.datetime(2019, 6, 18, 0, 0))
('B00002', '张2', 19, '数学', datetime.datetime(2019, 6, 19, 0, 0))
('B00003', '张3', 20, '英语', datetime.datetime(2019, 6, 20, 0, 0))
('B00004', '张4', 21, '物理', datetime.datetime(2019, 6, 21, 0, 0))
('B00005', '张5', 22, '化学', datetime.datetime(2019, 6, 22, 0, 0))
('B00006', '张6', 23, '生物', datetime.datetime(2019, 6, 23, 0, 0))
('B00007', '张7', 24, '历史', datetime.datetime(2019, 6, 24, 0, 0))
12、获取所有值(键值对)
import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
table = wb['表2']
r = []
L = []
for i in range(1,table.max_column+1):
# print(table.cell(row=2, column=i).value)
r.append(table.cell(row=1, column=i).value)
for i in range(2, table.max_row+1):
d = {}
for j in range(1, table.max_column+1):
d[r[j-1]] = table.cell(row=i,column=j).value
# print(d)
L.append(d)
print(L)
执行结果
[
{'学号': 'B00001', '姓名': '张1', '年龄': 18, '专业': '语文', '考试时间': datetime.datetime(2019, 6, 18, 0, 0)},
{'学号': 'B00002', '姓名': '张2', '年龄': 19, '专业': '数学', '考试时间': datetime.datetime(2019, 6, 19, 0, 0)},
{'学号': 'B00003', '姓名': '张3', '年龄': 20, '专业': '英语', '考试时间': datetime.datetime(2019, 6, 20, 0, 0)},
{'学号': 'B00004', '姓名': '张4', '年龄': 21, '专业': '物理', '考试时间': datetime.datetime(2019, 6, 21, 0, 0)},
{'学号': 'B00005', '姓名': '张5', '年龄': 22, '专业': '化学', '考试时间': datetime.datetime(2019, 6, 22, 0, 0)},
{'学号': 'B00006', '姓名': '张6', '年龄': 23, '专业': '生物', '考试时间': datetime.datetime(2019, 6, 23, 0, 0)},
{'学号': 'B00007', '姓名': '张7', '年龄': 24, '专业': '历史', '考试时间': datetime.datetime(2019, 6, 24, 0, 0)}
]
13、获取所有值(列表)
import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
table = wb['表2']
L=[]
for i in range(2, table.max_row+1):
d = []
for j in range(1, table.max_column+1):
ss = table.cell(row=i,column=j).value
d.append(ss)
L.append(d)
print(L)
执行结果:
[
['B00001', '张1', 18, '语文', datetime.datetime(2019, 6, 18, 0, 0)],
['B00002', '张2', 19, '数学', datetime.datetime(2019, 6, 19, 0, 0)],
['B00003', '张3', 20, '英语', datetime.datetime(2019, 6, 20, 0, 0)],
['B00004', '张4', 21, '物理', datetime.datetime(2019, 6, 21, 0, 0)],
['B00005', '张5', 22, '化学', datetime.datetime(2019, 6, 22, 0, 0)],
['B00006', '张6', 23, '生物', datetime.datetime(2019, 6, 23, 0, 0)],
['B00007', '张7', 24, '历史', datetime.datetime(2019, 6, 24, 0, 0)]
]
14、批量写入数据
import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
table = wb['表2']
for i in range(9,12):
for j in range(1,6):
if j == 1:
table.cell(row=i,column=j,value= '企业文化{0}'.format(j))
if j == 2:
table.cell(row=i,column=j,value= '规章制度{0}'.format(j))
if j == 3:
table.cell(row=i,column=j,value= '企业愿景{0}'.format(j))
if j == 4:
table.cell(row=i,column=j,value= '个人追求{0}'.format(j))
if j == 5:
table.cell(row=i,column=j,value= '生活理想{0}'.format(j))
wb.save(r'C:\Users\Administrator\Desktop\测试一下.xlsx')
执行结果: