day8-Python操作Excel

day8-Python操作Excel

1.openpyxl读Excel文件

1）工作簿：一个Excel文件（后缀是.xls或.xlsx的文件）就是一个工作薄

2）工作表：Excel文件中每个活动页就是一个工作表

3）单元格：工作表中每一个格子就是一个单元格

import openpyxl

1.打开Excel文件获取工作簿对象

wb = openpyxl.open('files/data.xlsx')

2.获取工作表相关信息

1)获取工作簿中所有的工作表名称: 工作簿对象.sheetnames

names = wb.sheetnames
print(names)			# ['student','teacher']

2)获取指定的工作表对象

工作簿对象.active - 获取当前工作簿的活跃表(默认选中的表)

工作簿对象[表名] - 获取工作簿中指定名字对应的工作表

a_sheet = wb.active
print(a_sheet)

student_s = wb['student']
print(student_s)

teacher_s = wb['teacher']
print(teacher_s)

3)获取单元格相关信息

工作表对象.max_row - 最大行数

工作表对象.max_column - 最大列数

mr = student_s.max_row
mc = student_s.max_column
print(mr,mc)

工作表对象.cell(行号，列号) - 获取指定行中指定列对应的单元格（获取指定位置对应的单元格

cell1 = student_s.cell(1,2)
cell2 = student_s.cell(3,1)

单元格对象.value - 获取指定单元格中的内容

print(cell1.value)
print(cell2.value)

• 练习1：获取学生表中第5个学生的所有信息

stu = []
row = 6
for col in range(1,mc+1):
    stu.append(student_s.cell(row,col).value)
promt(stu)

• 练习2：获取所有学生的性别

genders = []
col = 4
for row in range(2,mr+1):
    value = student_s.cell(row,col).value
    genders.append(value)
print(genders)

• 练习3：获取老师表中所有的数据

teacher_s = wb['teacher']
t_mc = teacher_s.max_column
t_mr = teacher_s.max_row
for row in range(1,t_mc+1):
    row_data = []
    for col in range(1,t_mc+1):
        value = teacher_s.cell(row,col).value
        row_data.append(value)
	print(row_data)

2.Excel写操作（openpyxl）

import …

补充：os.path.exists(文件或者文件夹路径) - 判断指定文件或者文件夹是否存在

1.新建工作簿

• 新建Excel文件：openpyxl.Workbook()

if os.path.exists('files/data2.xlsx'):
    wb1 = openpyxl.open('files/data2.xlsx')

else:
    wb1 = openpyxl.Workbook()

2.新建工作表

工作簿对象.create_sheet(表名) - 创建名字为指定值的工作表，并且返回工作表对象

if 'student' in wb1.sheetnames:
    print('获取学生表')
    student = wb1['student']
else:
    print('创建学生表')
    student = wb1.create_sheet('student')

3.删除工作表

工作簿对象.remove(工作表)

if 'Sheet' in wb1.sheetnames:
    wb1.remove(wb1['Sheet'])

4.修改单元格内容

单元格.value = 数据

student.cell(1,3).value = '李四'
student.cell(1,1).value = '老王'
student.cell(1,2).value = None

保存工作簿（保存文件） - 所有的写操作只有在保存之后有效

wb1.save('files/data2.xlsx')

3.写操作练习

​ 新建Excel文件(信息.xlsx),然后再新建’学生表’, 将students中所有学生信息写入到新建的’学生表’中(并且需要自己添加表头)

​ 学生表需要的表头：姓名、年龄、性别、电话、联系人姓名、联系人电话

students = [
        {'name': 'stu1', 'age': 20, 'gender': '男', 'tel': '11987223', 'linkman': {'name': '张三', 'tel': '81923'}},
        {'name': 'stu2', 'age': 22, 'gender': '女', 'tel': '8293212', 'linkman': {'name': '小明', 'tel': '6666'}},
        {'name': 'stu3', 'age': 25, 'gender': '女', 'tel': '727332', 'linkman': {'name': '李四', 'tel': '829333'}},
        {'name': 'stu4', 'age': 19, 'gender': '男', 'tel': '563743', 'linkman': {'name': '老王', 'tel': '778822'}},
        {'name': 'stu5', 'age': 23, 'gender': '男', 'tel': '0928322', 'linkman': {'name': '赵六', 'tel': '829101'}}
]

• 创建工作簿

import openpyxl
if os.path.exists('files/信息.xlsx')：
	wb = openpyxl.open('files/信息.xlsx')
else:
    wb = openpyxl.Workbook()

• 创建工作表

if '学生表' in wb.sheetnames:
    sheet = wb['学生表']
else:
    sheet = wb.create_sheet('学生表')

• 添加数据

# 添加表头
headers = ['姓名','年龄','性别','联系人姓名','联系人电话']
for col in range(1,len(headers)+1):
    sheet.cell(1,col).value = headers[col-1]
    
# 添加学生信息
row = 2
for stu in students:
    sheet.cell(row,1).value = stu['name']
    sheet.cell(row,2).value = stu['age']
    sheet.cell(row,3).value = stu['gender']
    sheet.cell(row,4).value = stu['tel']
    sheet.cell(row,5).value = stu['linkman']['name']
    sheet.cell(row,6).value = stu['linkman']['tel']
    row += 1
    
wb.save('files/信息.xlsx')
    

4.设置样式（openpyxl）

# 建立文本
import ...
wb= openpyxl.open('files/data.xlsx')
# sheet = wb['teacher']
sheet = wb.active

1.设置宽度和高度

• 设置指定列的宽度

sheet.column_dimensions['A'].width = 25
sheet.column_dimensions['b'].width = 30

• 设置指定行的高度

sheet.row_dimension[1].height = 30
for x in range(2,5):
    sheet.row_dimensions[x].height = 25
    

2.设置单元格字体样式

• 创建字体对象

font1 = Font(
	# 字体名称
	name = '黑体'
    # 是否添加删除线
    skrike = True,
    # 文字颜色
    color = '643850',
    # 设置字体大小
    size = 15,
    # 是否加粗
    bold = True,
    # 是否倾斜
    italic = True,
    # 添加下划线：'singleAccounting','double','single',
    'doubleAccounting'
    underline='double'
)

• 设置单元格的字体

# sheet.cell(4,1).font = font1
for row in range(2,5):
    sheet.cell(row,1).font = font1

3.设置填充样式

• 创建填充对象

fill1 = PatternFill(
	# 设置填充样式：'lightGray', 'solid', 'darkUp', 'gray0625', 'lightDown', 'lightGrid', 'mediumGray', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkDown', 'lightHorizontal', 'darkTrellis', 'darkVertical', 'lightTrellis', 'lightVertical', 'lightUp', 'gray125
    fill_type = 'solid',
    # 起始颜色：fill_type为solid的时候只有起始颜色有效
    start_color = 'FAFAD2',
    # 结束颜色
    end_color = 'FF7F50'
)

4.设置单元格填充样式

ag1 = Alignment(
	# 垂直对齐方式：center、top、bottom
    vertical = 'center',
    # 水平对齐方式
    horizontal = 'center'
)
sheet.cell(1,2).alignment = ag1

5.设置边框样式

• 创建边对象

side1 = Side(
	# 'dashDot', 'dotted', 'thin', 'double', 'medium', 'thick', 'dashed', 'mediumDashDot', 'slantDashDot', 'mediumDashed', 'mediumDashDotDot', 'hair', 'dashDotDot'
    border_style = 'medium',
    color = '800000'
)
side2 = Side(
	bor_style = 'mediumDashed',
    color = 'ff0000'
)

• 创建边框(颜色)

border1 = Border(bottom = side1,top = side1,left = side2,right = side2)

• 设置单元格边框样式

sheet.cell(2,4).border = border1

wb.save('files/data.xlsx')