day7openpyxl读取操作练习
1.excel文件读取操作练习
1.获取第2列所有内容,然后将数据保存到列表中:[Language,Python,Java,C,...,Go]
import openpyxl
wb = openpyxl.load_workbook('files/example.xlsx')
sheet = wb['表1']
m_r = sheet.max_row
m_c = sheet.max_column
data = []
for row in range(1,m_r+1):
cell = sheet.cell(row,2)
data.append(cell.value)
print(data)
2.获取第2行所有内容,然后将数保存到列表中:{'Rank':1, 'Language': 'Python', 'Score': 100}
data = {}
for col in range(1,m_c + 1):
cell1 =sheet.cell(1,col)
cell2 = sheet.cell(2,col)
data[cell1.value] = cell2.value
print(data)
3.获取整个文件所有的数据
result = []
for row in range(2,m_r+1):
line = {}
for col in range(1,m_c+1):
key = sheet.cell(row,col).value
value = sheet.cell(row,col).value
line.setdefault(sheet.cell(1,col))
result.append(line)
print(result)
2.excel文件写操作练习
1.不断提示用户输入学生信息
请输入学生的姓名:xiaoming
请输入学生的年龄:18
请输入学生的性别:男
请输入学生的电话:110
是否继续(Y/N):Y
请输入学生的姓名:xiaoming
请输入学生的年龄:18
请输入学生的性别:男
请输入学生的电话:110
是否继续(Y/N):Y
…
请输入学生的姓名:xiaoming
请输入学生的年龄:18
请输入学生的性别:男
请输入学生的电话:110
是否继续(Y/N):N
import openpyxl
import os
wb2 = openpyxl.Workbook()
if os.path.exists('files/example4.xlsx'):
wb2 = openpyxl.load_workbook('files/example4.xlsx')
else:
wb2 = openpyxl.Workbook()
wb2.save('files/example4.xlsx')
if 'student' in wb2.sheetnames:
sheet = wb2['student']
else:
sheet = wb2.create_sheet('student',0)
wb2.save('files/example4.xlsx')
sheet.cell(1, 1).value = '姓名'
sheet.cell(1, 2).value = '年龄'
sheet.cell(1, 3).value = '性别'
sheet.cell(1, 4).value = '电话'
while True:
m_r = sheet.max_row
sheet.cell(m_r+1,1).value = input('请输入学生的姓名:')
sheet.cell(m_r+1,2).value = input('请输入学生的年龄:')
sheet.cell(m_r+1,3).value = input('请输入学生的性别:')
sheet.cell(m_r+1,4).value = input('请输入学生的电话:')
str1 = input('是否继续(Y/N):')
if str1 == 'Y':
continue
else:
break
wb2.save('files/example4.xlsx')
3.homework
import openpyxl
1.获取‘student’表中所有的数据,放到新建的工作表中
1)获取工作簿
wb = openpyxl.open('files/学生信息.xlsx')
2)获取原始数据对应的表
student_sheet = wb['students']
3)新建操作数据对应的表
if 'newStudent' not in wb.sheetnames:
new_student_sheet = wb.create_sheet('student')
else:
new_student_sheet = wb['newStudent']
m_r = student_sheet.max_row
m_c = student_sheet.max_column
for row in range(1,m_r+1):
for col in range(1,m_c+1):
# 复制数据
new_student_sheet.cell(row,col).value = student_sheet.cell(row,col).value
# 添加留级列
if row == 1:
new_student_sheet.cell(1,m_c+1).value = '留级建议'
elif student_sheet.cell(row,m_c).value < 60:
new_student_sheet.cell(row,m_c + 1).value = '留级'
2.修改新建的工作表数据所在的单元格的样式
from openpyxl.styles import Side,Border,Font,Alignment,PatternFill
s1 = Side(
border_style='medium',
color='bcd18f'
)
b1 = Border(
left=s1,
right=s1,
top=s1,
bottom=s1
)
# 2)字体
f1 = Font(size=18,color='157bbc')
f2 = Font(color='ff0000')
# 3)对齐方式
a1 = Alignment(vertical='center',horizontal='center')
# 4)填充
fill1 = PatternFill(
fill_type='solid',
start_color='ced79b'
)
m_r2 = new_student_sheet.max_row
m_c2 = new_student_sheet.max_column
for row in range(1,m_r2+1):
for col in range(1,m_c2+1):
cell = new_student_sheet.cell(row,col)
cell.border = b1
cell.alignment = a1
if row == 1:
cell.font = f1
elif col == m_c2:
cell.font = f2
elif col == m_c2-1 and cell.value < 60:
cell.font = f2
new_student_sheet.row_dimensions[1].height = 40
# new_student_sheet.column_dimensions['A']
补充:获取数字列号对应的字母值:get_column_letter(数字列号)
from openpyxl.utils import get_column_letter
for col in range(1,m_c2+1):
col_letter = get_column_letter(col)
new_student_sheet.column_dimensions[col_letter].width = 20
wb.save('files/学生信息.xlsx')