代码操作Excel

一、安装第三方库更加快捷

1.Python国内镜像地址：

cmd打开后输入

windows：pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple/

mac: pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple/

import openpyxl #检验是否安装成功

1.阿里云： https://mirrors.aliyun.com/pypi/simple/

2.豆瓣： https://pypi.douban.com/simple/

3.清华大学： https://pypi.tuna.tsinghua.edu.cn/simple/(推荐)

4.中国科学技术大学 ：https://pypi.mirrors.ustc.edu.cn/simple/

5.华中理工大学： https://pypi.hustunique.com/

6.山东理工大学： https://pypi.sdutlinux.org/

二.excel相关的基本概念

1.excel相关的基本概念

1）工作簿(workbook)：一个Excel文件就是一个工作簿

2）工作表(sheet)：工作表是工作簿的基本单位，每个工作簿至少要有一个工作表

3）单元格(cell)：工作表中每一个用来保存数据的格子就是单元格

4）行(row)号：每一行前面的数字（从1开始）

5）列(column)号：每一列上面的大写字母（也可以用数字1开头，表示列号）

2.excel相关读取操作

import openpyxl#导入库

1.打开Excel文件(加载Excel文件)

1）openpyxl.open(Excel文件地址) - 打开指定的Excel文件得到工作簿对象

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

2）openpyxl.load_workbook(Excel文件地址) - 打开指定的Excel文件得到工作簿对象

wb = openpyxl.load_workbook('files/example.xlsx')

2.获取工作簿所有的工作表的名字

方法：工作簿对象.sheetnames

names = wb.sheetnames
print(names)        # ['表1', '表2', 'Sheet1']

3.获取工作表

1)工作簿.active - 当前工作簿中的活跃表

sheet1 = wb.active
print(sheet1)

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

sheet2 = wb['表1']
print(sheet2)

4.获取最大行数和最大列数

1)工作表.max_row

2)sheet2.max_column（变量，自定义表。不是文件中的表名）

m_r = sheet2.max_row#（最大行）
m_c = sheet2.max_column#（最大列）
print(m_r, m_c)#13 14(工资表数据)

print(wb['Sheet1'].max_row, wb['Sheet1'].max_column)

5.获取单元格

方法：工作表.cell(行号,列号)

cell1 = sheet2.cell(2,1)
print(cell1)#<Cell '工资表'.A2>

6.获取单元格中的内容

方法：单元格.value

print(cell1.value)#姓名
sheet2.cell(2,1).value#姓名

3.练习

1.获取第2列所有内容，然后将数据保存到列表中：[Language, Python, Java, C,…,Go]

2.获取第二行所有内容，然后将数保存到字典中： {‘Rank’:1, ‘Language’: ‘Python’, ‘Score’: 100}

import openpyxl

wb = openpyxl.open('files/example.xlsx')
sheet = wb['表1']

m_r = sheet.max_row
m_c = sheet.max_column

# 获取第2列所有内容
data = []
for row in range(1, m_r+1):
    cell = sheet.cell(row, 2)
    data.append(cell.value)
print(data)

# 获取第2行所有内容
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)#{'Rank': 1, 'Language': 'Python', 'Score': 100}

# 获取整个文件所有的数据
result = []
for row in range(2, m_r+1):
    line = {}
    for col in range(1, m_c+1):
        key = sheet.cell(1, col).value
        value = sheet.cell(row, col).value
        line.setdefault(key, value)
    result.append(line)
print(result)

三、excel相关修改操作

注意：Excel文件相关任何写操作，只会在保存以后有效

1.新建Excel文件（新建工作簿）

wb1 = openpyxl.Workbook()

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

2.保存文件: 工作簿.save(文件地址)

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

# 案例：如果example3.xlsx文件不存在就创建，存在就打开
import os#导入

if os.path.exists('files/example3.xlsx'):
    wb2 = openpyxl.load_workbook('files/example3.xlsx')#打开文件，也可用open
else:
    wb2 = openpyxl.Workbook()#新建文件
    wb2.save('files/example3.xlsx')#保存文件

3.新建工作表

1)工作簿.create_sheet() #在最后加一个表

2)工作簿.create_sheet(表名)

3)工作簿.create_sheet(表名, 下标)

sheet1 = wb2.create_sheet()
sheet2 = wb2.create_sheet('student')
sheet3 = wb2.create_sheet('teacher', 0)#加在下标前

案例：如果teacher表不存在就创建这张表，存在就获取teacher表

if 'teacher' in wb2.sheetnames:
    sheet3 = wb2['teacher']
else:
    sheet3 = wb2.create_sheet('teacher', 0)

4.删除表-工作簿.remove(工作表)

#wb2.remove(wb2['Sheet1'])

if 'Sheet1' in wb2.sheetnames:
    wb2.remove(wb2['Sheet1'])

5.修改单元格内容 # 单元格.value = 数据

sheet3.cell(2, 1).value = '小明'
sheet3.cell(1, 3).value = 'Tel'
sheet3.cell(1, 2).value = None

wb2.save('files/example3.xlsx')#一系列操作完成后记得保存

6.练习题

# 1. 不断提示用户输入学生信息：
"""
请输入学生的姓名：xiaoming
请输入学生的年龄：18
请输入学生的性别：男
请输入学生的电话：110
是否继续(Y/N)：Y
...
请输入学生的姓名：xiaoming
请输入学生的年龄：18
请输入学生的性别：男
请输入学生的电话：110
是否继续(Y/N)：N
"""
# 2.将输入的每个学生的信息保存在excel文件
"""
姓名   年龄  性别  电话
xx     xx   xx    xx
xxx    xxx  xxx    xxx
....
"""

import openpyxl
import os

if os.path.exists('files/student.xlsx'):
    wb = openpyxl.open('files/student.xlsx')
else:
    wb = openpyxl.Workbook()
    wb.save('files/student.xlsx')

student1 = wb['Sheet']
student1.cell(1, 1).value = '姓名'
student1.cell(1, 2).value = '年龄'
student1.cell(1, 3).value = '性别'
student1.cell(1, 4).value = '电话'

mr = student1.max_row
while True:
    student1.cell(mr+1, 1).value = input('请输入学生的姓名：')
    student1.cell(mr+1, 2).value  = input('请输入学生的年龄：')
    student1.cell(mr+1, 3).value  = input('请输入学生的性别：')
    student1.cell(mr+1, 4).value  = input('请输入学生的电话：')
    wb.save('files/student.xlsx')
    cmd = input('是否继续（Y/N）：')
    if cmd == 'Y':
        mr += 1
        pass
    else:
        break

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

四、excel文件样式修改

openpyxl除了可以操作Excel文件内容，还可以修改Excel文件中内容的样式，常见的字体样式、边框、填充、设置单元格宽度和高度等。

下面我们用代码来修改example.xlsx文件中表1中内容的样式，修改之前先看一下原来的效果：

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# 打开Excel文件创建工作簿
wb = openpyxl.open('files/example.xlsx')
# 获取工作表
sheet1 = wb['表1']

# 1.设置行高和列宽
# a. 工作表.row_dimensions[行号].height = 高度
# b. 工作表.column_dimensions[列号].width = 宽度

# 设置第一行的高度为40
sheet1.row_dimensions[1].height = 40
# 设置A列的宽度为20
sheet1.column_dimensions['A'].width = 20

# 2.设置单元格字体样式
# 1）创建字体对象
font1 = Font(
    # 字体名称
    name='黑体',
    # 是否添加删除线
    # strike=True,
    # 文字颜色
    color='643850',
    # 设置字体大小
    size=15,
    # 是否加粗
    # bold=True,
    # 是否倾斜
    # italic=True,
    # 添加下划线：  'singleAccounting', 'double', 'single', 'doubleAccounting'
    # underline='double'
)
# 2）设置单元格字体(设置第2行第2列的字体)
sheet1.cell(2, 2).font = font1

# 3.设置填充样式
# 1)创建填充对象
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'
)

# 2)设置单元格填充样式
sheet1.cell(1, 1).fill = fill1

# 4.设置单元格对齐方式
ag1 = Alignment(
    # 垂直对齐方式：center、top、bottom
    vertical='center',
    # 水平对齐方式：center、letf、right
    horizontal='center'
)
sheet1.cell(1, 2).alignment = ag1

# 5.设置边框样式
# 1）创建边对象
side1 = Side(
    # 'dashDot', 'dotted', 'thin', 'double', 'medium', 'thick', 'dashed', 'mediumDashDot', 'slantDashDot', 'mediumDashed', 'mediumDashDotDot', 'hair', 'dashDotDot'
    border_style='medium',
    color='800080'
)

side2 = Side(
    border_style='mediumDashed',
    color='ff0000'
)

# 2)创建边框
border1 = Border(bottom=side1, top=side1, left=side2, right=side2)

# 3)设置单元格边框样式
sheet1.cell(2, 4).border = border1

# 保存文件
wb.save('files/example.xlsx')

设置完样式之后的效果：

五、练习题

import openpyxl
wb = openpyxl.open('files/stu_ms.xlsx')
if 'student2' in wb.sheetnames:
    sheet1 = wb['student2']
else:
    sheet1 = wb.create_sheet('student2')
wb.save('files/stu_ms.xlsx')
sheet2 = wb['students']

r_max = sheet2.max_row
c_max = sheet2.max_column
for row in range(2,r_max+1):
    for col in range (1,c_max+1):
        sheet1.cell(1, col).value = sheet2.cell(1, col).value
        sheet1.cell(row,col).value = sheet2.cell(row,col).value
wb.save('files/stu_ms.xlsx')
sheet1.cell(1,5).value = '留级建议'
for row in range(2,r_max+1):
    if sheet1.cell(row,4).value < 60:
        sheet1.cell(row,5).value = '留级'
wb.save('files/stu_ms.xlsx')


sheet1.row_dimensions[1].height = 30
sheet1.column_dimensions['A'].width = 20
sheet1.column_dimensions['B'].width = 20
sheet1.column_dimensions['C'].width = 20
sheet1.column_dimensions['D'].width = 20
sheet1.column_dimensions['E'].width = 20


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

or row in range(2,r_max+1):
if sheet1.cell(row,4).value < 60:
sheet1.cell(row,5).value = ‘留级’
wb.save(‘files/stu_ms.xlsx’)

sheet1.row_dimensions[1].height = 30
sheet1.column_dimensions[‘A’].width = 20
sheet1.column_dimensions[‘B’].width = 20
sheet1.column_dimensions[‘C’].width = 20
sheet1.column_dimensions[‘D’].width = 20
sheet1.column_dimensions[‘E’].width = 20

wb.save(‘files/stu_ms.xlsx’)