Python操作Excel

最新推荐文章于 2024-05-01 03:45:31 发布

九月845

最新推荐文章于 2024-05-01 03:45:31 发布

阅读量618

点赞数

文章标签： excel python 开发语言

本文链接：https://blog.csdn.net/weixin_75205337/article/details/128070806

版权

Python操作Excel: openpyxl、(xlwt、xlrd)

一、openpyxl读Excel文件

Excel相关概念

1)工作簿 – 一个Excel文件(后缀是.xls或者.xlsx的文件)就是一个工作簿；
2)工作表 – Excel文件中每个活动页就是一个工作表；
3)单元格 – 工作表中每一个格子就是一个单元格。

用 openpyxl模块打开Excel文档

import openpyxl

# 1.打开Excel文件获取工作簿对象
wb = openpyxl.open('files/data.xlsx')

导入 openpyxl模块，从工作簿中取得工作表：

工作簿对象.sheetnames- 获取工作簿中所有的工作表名称
工作簿对象.active- 获取当前工作簿的活跃表(默认选中的表)
工作簿对象[表名]- 根据表名获取指定表对象
表对象.title- 获取表对象的表名
表对象.max_row- 获取表中最大行数
表对象.max_column- 获取表中最大列数
工作表对象.cell(行号, 列号)- 获取指定行中的指定列对应的单元格(获取指定位置的单元格)

# 2.获取工作表相关信息
# 1)获取工作簿中所有的工作表名称
all_sheet_name = wb.sheetnames
print(all_sheet_name)    # ['student', 'teacher']

# 2)获取指定的工作表对象
# a.工作簿对象.active   -   获取当前工作簿的活跃表(默认选中的表)
# b.工作簿对象[表名]
a_sheet = wb.active
print(a_sheet)         # <Worksheet "teacher">

student_s = wb['student']
print(student_s)       # <Worksheet "student">

teacher_s = wb['teacher']
print(teacher_s)       # <Worksheet "teacher">

# 3)获取单元格相关信息
# a.获取最大行数: 工作表对象.max_row       # 有数据部分的最大行数
# b.获取最大列数: 工作表对象.max_column
mr = student_s.max_row
mc = student_s.max_column
print(mr, mc)      # 8 4

# c.工作表对象.cell(行号, 列号)   -   获取指定行中的指定列对应的单元格(获取指定位置的单元格)
cell1 = student_s.cell(1, 2)
cell2 = student_s.cell(3, 1)

从表中取得单元格

表对象['列号行号'] - 获取指定列指定行对应的单元格对象（单元格对象是 Cell 类的对象，列号是从A开始，行号是从1开始）
表对象.iter_rows() - 一行一行的获取数据
表对象.iter_cols() - 一列一列的获取数据
单元格对象.value- 获取指定单元格中的内容
单元格对象.row - 获取行号（数字1开始）
单元格对象.column - 获取列号（数字1开始）
单元格对象.coordinate - 获取位置（包括行号和列号）

# d.单元格对象.value   -   获取指定单元格中的内容
print(cell1.value)    # 姓名
print(cell2.value)    # 002

# 练习1: 获取学生表中第五个学生的所有信息

stu = []
row = 5 + 1
for i in range(2,mc+1):
    stu.append(student_s.cell(row,i).value)
print(stu)

# 练习2: 获取所有学生的性别

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

# 练习3: 获取老师表中所有的数据
# 方法1
teacher_s = wb['teacher']
t_mr = teacher_s.max_row
t_mc = teacher_s.max_column
print(t_mr)
print(t_mc)
shuju = {}
for i in range(2, t_mr+1):
    for j in range(1, t_mc+1):
        shuju[teacher_s.cell(1, j).value] = teacher_s.cell(i, j).value
    print(shuju)

# {'姓名': '张老师', '电话': 110}
# {'姓名': '王老师', '电话': 120}
# {'姓名': '李老师', '电话': 130}

#方法2
teacher_s = wb['teacher']
t_mc = teacher_s.max_column
t_mr = teacher_s.max_row
for row in range(1, t_mr+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)

# ['姓名', '电话']
# ['张老师', 110]
# ['王老师', 120]
# ['李老师', 130]

二、openpyxl对Excel写进行操作

创建并保存Excel文档

openpyxl.Workbook() - 创建空的 Excel 文件对应的工作簿对象
工作簿对象.save(文件路径) - 保存文件

import openpyxl
# 创建空的Workbook对象(新建一个Excel文件对应的工作簿)
wb = openpyxl.Workbook()

# 但是在操作中得判断该工作簿是否存在，若存在只需打开即可
# 1.新建工作簿(新建Excel文件)  -  openpyxl.Workbook()
if os.path.exists('files/data2.xlsx'):
    wb1 = openpyxl.open('files/data2.xlsx')
else:
    wb1 = openpyxl.Workbook()

    
    
# 保存工作簿(保存文件)   -   所有的写操作只有在保存之后有效
wb1.save('files/data2.xlsx')

创建和删除工作表
- 工作表对象.create_sheet(title, index)- 创建名字为指定值的工作表，并且返回工作表对象
- 工作簿对象.remove(工作表)- 删除工作簿中的指定表
标准库 os的使用
- os.path.exists(文件或者文件夹路径)- 判断指定文件或者文件夹是否存在

将数据写入表格，修改单元格内容

student.cell(row, col).value = '指定内容'- 修改单元格内容
表对象[位置] = 值 - 在表中指定位置对应的单元格中写入指定的值，位置是字符串：‘A1’（第1列的第一行）、‘B1’（第二列的第一行）

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

# 2.新建工作表
# 工作表对象.create_sheet(表名)   -   创建名字为指定值的工作表，并且返回工作表对象
# print(wb1.sheetnames)
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.修改单元格内容
student.cell(1,3).value = '李四'
student.cell(1,1).value = '老王'
student.cell(1,2).value = None

# 保存工作簿(保存文件)   -   所有的写操作只有在保存之后有效
wb1.save('files/data2.xlsx')

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QbhK3jQW-1669557733579)(D:\pictures\data2.png)]

练习

# 新建Excel文件(信息.xlsx),然后再新建'学生表',
# 将students中所有学生信息写入到新建的'学生表'中(并且需要自己添加表头)
# 姓名 年龄 性别 电话 联系人姓名 联系电话

import openpyxl
import os

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

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


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'}}
    ]

# 添加数据
# 添加表头
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


wb2.save('files/信息.xlsx')

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xvvw1vFW-1669557733581)(D:\pictures\学生表.png)]

三、openpyxl给Excel设置样式

导入数据

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

打开文件

wb = openpyxl.open('files/data.xlsx')
sheet = wb['teacher']

设置样式

# 1. 设置宽度和高度
# 设置指定列的宽度
sheet.column_dimensions['A'].width = 50
sheet.column_dimensions['B'].width = 20
# 设置指定行的高度
sheet.row_dimensions[1].height = 55
for i in range(3,5):
    sheet.row_dimensions[i].height =30

# 2.设置单元格字体样式
# 1)创建字体对象
font1 = Font(
    name='宋体',        # 字体名称
    strike=True,       # 是否添加删除线
    color='ff8937',    # 字体颜色
    size = 15,         # 设置字体大小
    bold = True,       # 是否加粗
    italic=True,       # 是否倾斜
    # 添加下划线: 'singleAccounting', 'double', 'single', 'doubleAccounting
    underline='double'
)
# 2) 设置单元格的字体
# sheet.cell(4, 1).font = font1
for row in range(2, 5):
    sheet.cell(row, 1).font = font1

# 3. 设置填充样式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', '
# lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown',
# 'mediumGray',  'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp'
# 1) 创建填充对象
fill1 = PatternFill(
    # 设置填充样式
    fill_type='darkUp',
    # 起始颜色: fill.type为solid的时候只有起始颜色有效
    start_color='015632',
    # 结束颜色
    end_color='897423'
)
# 2) 设置单元格填充样式
sheet.cell(1, 1).fill = fill1
# 设置字体颜色  -  计算机识别十六进制
# r(0~255)、g(0~255)、b(0~255)   -   RGB  : (255, 0, 0)  (0, 255, 0)  (0, 0, 255)


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

# 5.设置边框样式
side1 = Side(
    # 'dashDot', 'dotted', 'thin', 'double', 'medium', 'thick', 'dashed',
    # 'mediumDashDot', 'slantDashDot', 'mediumDashed', 'mediumDashDotDot',
    # 'hair', 'dashDotDot'
    border_style='thin',
    color='214324'
)

side2 = Side(
    border_style='thin',
    color='ff7688'
)

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

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

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

四、案例：使用openpyxl将数据写入到创建的Excel文件中，对新文件设置相关样式

导入Python的第三方库openpyxl和标准库os

import openpyxl
import os
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment    # 字体，  填充  
# 边框   边框样式  对齐方式

创建与原始数据对应的工作簿

wb1 = openpyxl.open('files/作业数据.xlsx')

if os.path.exists('files/suhju.xlsx'):
    wb2 = openpyxl.open('files/shuju.xlsx')
else:
    wb2 = openpyxl.Workbook()

取和创建原数据对应的表和保存数据的表

shuju_s = wb1['原始数据']

if 'scores' in wb2.sheetnames:
    print('获取成绩表')
    sco = wb2['scores']
else:
    print('创建成绩表')
    sco = wb2.create_sheet('scores')

添加需要样式对象

# 数据对应的字体
font1 = Font(size=14)
# 第一行内容对应的字体
font2 = Font(size=20, bold=True)
# 名字对应的字体
font3 = Font(color='4B0082', size=14)
# 补考的字体
font4 = Font(color='ff0000', size=14)
# 对齐方式
ag =Alignment(vertical= 'center', horizontal='center')
# 填充
fill = PatternFill('solid', start_color='FFB6C1')
fill2 = PatternFill('solid', start_color='FFD700')
# 边框
side = Side('medium','000000')
broder = Border(left=side, right=side, top=side, bottom=side)

将原始数据读出，写入结果表中

# shuju_s = wb1.active
mr = shuju_s.max_row
mc = shuju_s.max_column
for row in range(1, mr+1):
    for col in range(1,mc+1):
        # 获取原始数据表中指定位置对应的数据
        data = shuju_s.cell(row, col).value
        # 获取对应位置的格子
        cell = sco.cell(row, col)
        # 单元格样式的设置
        cell.font = font1
        cell.border = broder
        cell.alignment = ag
        # 直接将获取到的数据写入到结果表中相同的位置
        if data !=0:
            cell.value = data
            if row >= 2 and col >= 2 and data < 60:
                cell.fill = fill
        else:
            cell.value = '补考'
            cell.font = font4

设置单元格的高度和宽度

sco.row_dimensions[1].height = 35
for x in range(2, mr+1):
    sco.row_dimensions[x].height = 25
# 方法1
# for x in ['A', 'B', 'C', 'D', 'E']:
#     sheet2.column_dimensions[x].width = 25
# 方法2
key = 'A'
for x in range(mc):
    key = chr(ord(key)+1)
    sco.column_dimensions[key].width = 25

单独设置字体（达到预期效果）

# 单独设置第一行的字体
for col in range(1, mc+1):
    sco.cell(1, col).font = font2
    sco.cell(1, col).fill = fill2

# 单独设置名字的字体
for row in range(2, mr+1):
    sco.cell(row, 1).font = font3

保存文件内容
```
wb2.save('files/shuju.xlsx')
```

五、xlrd获取Excel文件内容

安装

pip install xlrd
使用xlrd获取Excel文件内容（以下操作以data1数据为例）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SpO32V80-1669557733583)(D:\pictures\data1.png)]

xlrd对Excel内容的操作与直接在Excel里对内容操作效果一致

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

import xlrd

# 1. 打开Excel文件获取工作簿对象
wb = xlrd.open_workbook('files/data1.xls')
print(wb)     
# <xlrd.book.Book object at 0x0000016B7D0D8640>

获取工作表

工作簿对象.sheets()- 获取所有的工作表
工作簿对象.sheet_by_index(下标)- 获取指定下标的工作表
工作簿对象.sheet_by_name(表名)- 获取指定名字对应的工作表

all_sheet = wb.sheets()
print(all_sheet)    
# [Sheet  0:<students>, Sheet  1:<teacher>]

print(wb.sheet_by_index(0))   # Sheet  0:<students>

students = wb.sheet_by_name('students')
print(students)      # Sheet  0:<students>

获取表中的相关信息

获取表中数据部分的最大行数和最大列数:

表对象.nrows- 最大行数

表对象.ncols- 最大列数
按行或者按列获取数据

print(students.nrows, students.ncols)   # 6 4

print(students.row_values(0))   
# ['学号', '姓名', '年龄', '性别']  -  按行   
print(students.col_values(2))   
# ['年龄', 18.0, 20.0, 30.0, 25.0, 22.0]   -  按列

print(students.col_values(0,start_rowx=1))   
# ['001', '002', '003', '004', '005']

print(students.col_values(0,start_rowx=1, end_rowx=4))   # ['001', '002', '003']

获取单元格对象
1. 工作表.cell(行下标, 列下标)- 获取指定位置对应的单元格对象
2. 单元格对象.value- 获取指定位置对应单元格的内容
3. 工作表.row(行下标)- 获取指定行中所有数据的单元格对象
4. 工作表.col(列下标)- 获取指定列中所有数据的单元格对象
5. ⼯作表对象.row_values(⾏下标)- 获取指定下标对应的⾏中所有的数据，结果以列表的形式返回(下标从0开始)
6. ⼯作表对象.col_values(列下标)- 获取指定下标对应的列中所有的数据，结果以列表的形式返回(下标从0开始)
```
print(students.cell(0, 0))   # text:'学号'
print(students.cell(0, 0).value)   # 学号

print(students.row(1))     # [text:'001', text:'小明', number:18.0, text:'男']
print(students.row(1)[-1])   # text:'男'

print(students.col(2))   
# [text:'年龄', number:18.0, number:20.0, number:30.0, number:25.0, number:22.0]
print(students.col(3)[-2])    # text:'男'
```

练习

# 练习:一行行获取整个表中所有的数据
for i in range(0, students.nrows):
    print(students.row_values(i))
# 练习:一列列获取整个表中所有的数据
for j in range(0,students.ncols):
    print(students.col_values(j, start_rowx=0, end_rowx=students.nrows))

结果：

['学号', '姓名', '年龄', '性别']
['年龄', 18.0, 20.0, 30.0, 25.0, 22.0]
['001', '002', '003', '004', '005']
['001', '002', '003']

六、xlwt对Excel进行写操作

注意:

1） xlwt操作单元格内容的时候,只能在单元格中添加数据，不能修改有数据的单元格内容

2）xlrd打开工作簿不能进行写操作;xlwt打开工作簿只能进行写操作

安装

pip install xlwt
使用xlrd对Excel⽂件进⾏写操作（以下操作以data2.数据为例,下图为结果展示）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tTjYLinQ-1669557733584)(D:\pictures\data2…png)]

新建⼯作簿和⼯作表
1. xlwt.Workbook()- 新建⼀个⼯作簿对象并且返回
2. ⼯作簿对象.add_sheet(表名)- 在⼯作簿中新建⼀个⼯作表
3. ⼯作簿对象.save(⽂件路径)- 将⼯作簿对象对应的Excel⽂件保存到指定位置(⽂件必须保存成xls格式的)
```
import xlrd
import xlwt
import os

# 1. 创建工作簿对象(默认不会创建工作表)
wb = xlwt.Workbook()
# 2. 新建表
student = wb.add_sheet('学生表', cell_overwrite_ok=True)

wb.save('files/data2.xls')
```
写入数据到指定单元格
1. ⼯作表对象.write(⾏下标, 列下标, 内容)- 将指定内容写⼊到指定单元格(通过⾏下标和列下标可以确定⼀个唯⼀单元格)
```
student.write(0, 0, '姓名')      
#   默认有数据时再添加就会报错
# 但在已经有数据的单元格中再重新写入数据，创建表的时候加入cell_overwrite_ok=True就不会报错
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fHOWS2un-1669557733585)(D:\pictures\1.png)]

设置单元格样式

⼯作表对象.col(列下标).width = 宽度- 设置指定列的宽度（宽度在赋值的时候需要表示成：宽度值 * 256）
⼯作表对象.row(⾏下标).height_mismatch = True- 设置指定⾏的⾼度不适应内容⾼度（如果不设置后⾯设置⾼度⽆效）
⼯作表对象.row(⾏下标).height = ⾼度- 设置指定⾏的⾼度为指定⾼度（⾼度在赋值的时候需要表示成：⾼度值 * 60）

# a.设置指定行的高度
# 允许设置高度
student.row(0).height_mismatch = True
# 设置高度值
student.row(0).height = 10*60
# b.设置指定列的宽度
student.col(1).width = 25*256

设置字体

字体属性	字体属性值
name=‘⿊体’	字体名称
blod=True	是否加粗，True为加粗
height=20*20	字号⼤⼩
underline = True	⽆下划线：0、xlwt.Font.UNDERLINE_NONE 、 0x00；单下划线，⽂字下划线：True、xlwt.Font.UNDERLINE_SINGLE、 0x01；单下滑线，全单元格：xlwt.Font.UNDERLINE_SINGLE_ACC、0x21；双下划线，⽂字下划线：xlwt.Font.UNDERLINE_DOUBLE 、 0x02；双下滑线，全单元格：xlwt.Font.UNDERLINE_DOUBLE_ACC 、 0x22
italic = True	设置斜体
escapement=xlwt.Font.ESCAPEMENT_SUPERSCRIPT	设置字体悬空位于下⽅：xlwt.Font.ESCAPEMENT_SUBSCRIPT；设置字体悬空位于上⽅：xlwt.Font.ESCAPEMENT_SUPERSCRIPT；设置字体没有这个效果：xlwt.Font.ESCAPEMENT_NONE
colour_index = 12	设置⽂字颜⾊：颜⾊只可参考xlwt.Style.colour_map
struck_out = True	设置删除线

# 创建样式对象
style1 = xlwt.XFStyle()
# 创建字体对象
font = xlwt.Font()
# 将字体对象绑定到样式对象中
style1.font = font
# d.添加各种字体属性
font.name = '黑体'
font.bold = True
font.height = 15*15
font.colour_index = 10
font.underline = True
font.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT
font.struck_out = True

# e.将样式绑定到单元格中
student.write(0, 1, '年龄', style = style1)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4cG3GjKl-1669557733588)(D:\pictures\2.png)]

所有可用的颜色

print(xlwt.Style.colour_map)
{'aqua': 49, 'black': 8, 'blue': 12, 'blue_gray': 54, 'blue_grey': 54,
'bright_green': 11, 'brown': 60, 'coral': 29, 'cyan_ega': 15, 'dark_blue': 18,
'dark_blue_ega': 18, 'dark_green': 58, 'dark_green_ega': 17, 'dark_purple':
28, 'dark_red': 16, 'dark_red_ega': 16, 'dark_teal': 56, 'dark_yellow': 19,
'gold': 51, 'gray_ega': 23, 'grey_ega': 23, 'gray25': 22, 'grey25': 22,
'gray40': 55, 'grey40': 55, 'gray50': 23, 'grey50': 23, 'gray80': 63,
'grey80': 63, 'green': 17, 'ice_blue': 31, 'indigo': 62, 'ivory': 26,
'lavender': 46, 'light_blue': 48, 'light_green': 42, 'light_orange': 52,
'light_turquoise': 41, 'light_yellow': 43, 'lime': 50, 'magenta_ega': 14,
'ocean_blue': 30, 'olive_ega': 19, 'olive_green': 59, 'orange': 53,
'pale_blue': 44, 'periwinkle': 24, 'pink': 14, 'plum': 61, 'purple_ega': 20,
'red': 10, 'rose': 45, 'sea_green': 57, 'silver_ega': 22, 'sky_blue': 40,
'tan': 47, 'teal': 21, 'teal_ega': 21, 'turquoise': 15, 'violet': 20, 'white':
9, 'yellow': 13}

设置边框

边框属性	边框属性值
top	上边框样式：(边框样式：细实线:1，⼩粗实线:2，细虚线:3，中细虚线:4，⼤粗实线:5，双线:6，细点虚线:7，⼤粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13)
bottom	下边框样式
left	左边框样式
right	右边框样式
top_colour	上边框颜⾊
bottom_colour	下边框颜⾊
left_colour	左边框颜⾊
right_colour	右边框颜⾊

style2 = xlwt.XFStyle()
border = xlwt.Borders()
style2.borders = border
# 添加边框属性
# 边框样式: 细实线:1  ⼩粗实线:2  细虚线:3  中细虚线:4  ⼤粗实线:5  双线:6
# 细点虚线:7  ⼤粗虚线:8  细点划线:9  粗点划线:10  细双点划线:11
# 粗双点划线:12  斜点划线:13
border.bottom = 10    # 设置样式
border.bottom_colour = 14    # 设置颜色
border.top = 2
border.top_colour = 53
student.write(1, 3, 'abc', style = style2)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zU747zn9-1669557733589)(D:\pictures\3.png)]

设置填充样式

style3 = xlwt.XFStyle()
pa = xlwt.Pattern()
style3.pattern = pa
# 设置填充样式
pa.pattern = xlwt.Pattern.SOLID_PATTERN
pa.pattern_fore_colour = 14

student.write(1,5, 'hello', style = style3)

设置对齐方式

属性名	属性值
vert	⽔平上对⻬：xlwt.Alignment.VERT_TOP；⽔平居中：xlwt.Alignment.VERT_CENTER；⽔平下对⻬：xlwt.Alignment.VERT_BOTTOM
horz	垂直左对⻬：xlwt.Alignment.HORZ_LEFT；垂直居中：xlwt.Alignment.HORZ_CENTER；垂直右对⻬：xlwt.Alignment.HORZ_RIGHT
rota	旋转⽅向，值为数字，表示旋转的⻆度 # 用的很少
wrap	⾃动换⾏
shri	⾃动缩进

style4 = xlwt.XFStyle()
ag = xlwt.Alignment()
style4.alignment = ag
ag.vert = xlwt.Alignment.VERT_CENTER    # 垂直对齐
ag.horz = xlwt.Alignment.HORZ_CENTER    # 水平对齐
ag.rota = 90    # 旋转
ag.wrap = True   # 自动换行,内容超过单元格的宽度时，自动换行


student.write(0, 7, 'world---------------------', style = style4)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GRwsSNQP-1669557733589)(D:\pictures\4.png)]

合并单元格

⼯作表对象.write_merge(r1, r2, c1, c2, 写⼊内容, 样式)- r1,r2合并的起始⾏和终⽌⾏，c1,c2合并的起始列和终⽌列

student.write_merge(7, 9, 3, 4, 'student')

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-COfI9jnV-1669557733590)(D:\pictures\5.png)]

七、创建颜色对应表（练习）

练习: 新建一个Excel文件, 创建一个颜色对应表

import xlwt
# 1.创建工作簿和工作表
wb = xlwt.Workbook()
sheet = wb.add_sheet('颜色对照表', cell_overwrite_ok=True)


# 1)获取所有的颜色值
colour_map = xlwt.Style.colour_map

# 2)写入表头数据和设置基本样式
style1 = xlwt.XFStyle()

# 写入第一行数据
sheet.write(0, 0, '颜色', style=style1)
sheet.write(0, 1, '颜色单词', style=style1)
sheet.write(0, 2, '颜色值', style=style1)

# 3)将颜色信息写入到单元格中
row = 1
for i in colour_map:
    # 拿到颜色值
    color_value = colour_map[i]
    # 创建对应的填充对象
    style = xlwt.XFStyle()   # 样式
    fill = xlwt.Pattern()    # 填充
    fill.pattern = xlwt.Pattern.SOLID_PATTERN
    fill.pattern_fore_colour = color_value
    style.pattern = fill
    sheet.write(row, 0, '', style = style)
    sheet.write(row, 1, i)
    sheet.write(row, 2, color_value)
    row +=1
wb.save('files/颜色数据对应表.xls')

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iovGbeMk-1669557733590)(D:\pictures\01.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jlIIRftx-1669557733590)(D:\pictures\02.png)]

练习添加样式使得表格变得美观

import xlwt

# 1. 创建工作簿和工作表
wb = xlwt.Workbook()
sheet = wb.add_sheet('颜色对照表')

# 2. 添加颜色信息
# 1)获取所有的颜色值
color_map = xlwt.Style.colour_map

# 2)写入表头数据和设置基本样式
style1 = xlwt.XFStyle()

# 创建字体对象
font1 = xlwt.Font()
font1.height = 17 * 17
font1.bold = True
font1.name = '黑体'
style1.font = font1

# 创建对齐方式
ag = xlwt.Alignment()
ag.vert = xlwt.Alignment.VERT_CENTER
ag.horz = xlwt.Alignment.HORZ_CENTER
style1.alignment = ag

# 创建边框
border = xlwt.Borders()
border.top = 1
border.bottom = 1
border.left = 1
border.right = 1
style1.borders = border

# 写入第一行的数据
sheet.write(0, 0, '颜色', style=style1)
sheet.write(0, 1, '颜色单词', style=style1)
sheet.write(0, 2, '颜色值', style=style1)

# 设置第一行内容的高度
sheet.row(0).height_mismatch = True
sheet.row(0).height = 10*60
# 设置前三列的宽度
sheet.col(0).width = 20 * 256
sheet.col(1).width = 20 * 256
sheet.col(2).width = 20 * 256

# 3)将颜色信息写入到单元格中
row_index = 1
for x in color_map:
    # 拿到颜色值
    color_value = color_map[x]
    # 创建对应的填充对象
    style = xlwt.XFStyle()
    style.borders = border

    fill = xlwt.Pattern()
    fill.pattern = xlwt.Pattern.SOLID_PATTERN
    fill.pattern_fore_colour = color_value
    style.pattern = fill
    # 设置对应单元格的样式
    sheet.write(row_index, 0, '', style=style)

    # 设置行高
    sheet.row(row_index).height_mismatch = True
    sheet.row(row_index).height = 10 * 60

    # 写入其他数据
    style2 = xlwt.XFStyle()
    style2.alignment = ag
    style2.borders = border

    sheet.write(row_index, 1, x, style=style2)
    sheet.write(row_index, 2, color_value, style=style2)

    row_index += 1

wb.save('files/颜色值.xls')

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aDGMnJfY-1669557733591)(D:\pictures\03.png)]

八、xlutils对Excel进行写操作

安装

pip install xlutils– xlwt 只能对新建的Excel文件进行写操作；xlutils可以对已经存在的Excel文件进行写操作

导入相关库
```
import xlrd
from xlutils.copy import copy
# 将打开的工作簿进行复制，然后在指定的文件中操作
```
使用xlutils对Excel进行写操作

打开指定的Excel文件

r_wb = xlrd.open_workbook('files/data1.xls')

对打开的工作簿对象进行拷贝，得到一个可写的工作簿
```
w_wb = copy(r_wb)
```

在已经存在的工作表中写入数据(将students中的小明改成xiaoming)

sheet2_n = w_wb.add_sheet('students2', cell_overwrite_ok=True)
sheet2_o = r_wb.sheet_by_index(0)
for r in range(sheet2_o.nrows):
    r_data = sheet2_o.row_values(r)
    col = 0
    for x in r_data:
        sheet2_n.write(r, col, x)
        col +=1

sheet2_n.write(1, 0, 'xiaoming')

保存文件
```
w_wb.save('files/data1.xls')
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-klwV8HQh-1669557733591)(D:\pictures\11.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wSnMBxA1-1669557733592)(D:\pictures\12.png)]

九、案例：使用xlwt将数据写入到创建的Excel文件中，对新文件设置相关样式

将数据写入新创建的Excel文件中

导入库
```
import xlrd
import xlwt
```

打开原数据对应的Excel文件，获取原始数据表

wb1 = xlrd.open_workbook('files/作业数据2.xls')
sheet1 = wb1.sheet_by_name('原始数据')

创建新的Excel文件，并且添加新的表

wb2 = xlwt.Workbook()
sheet2 = wb2.add_sheet('效果')

读取数据，写入到新建的效果表中

mr = sheet1.nrows
mc = sheet1.ncols
for i in range(mr):
    for j in range(mc):
        data = sheet1.cell(i, j).value          # 获取原始数据中每一个单元格的内容
        if data == 0:
            sheet2.write(i, j, '补考')
        else:
            sheet2.write(i, j, data)

保存文件
```
wb2.save('files/结果.xls')
```

以下是对新文件进行美化

需注意样式和边框可以提前设置，以方便写入数据时就将其设置

import xlrd
import xlwt

# 1.打开原数据对应的Excel文件，获取原始数据表
wb1 = xlrd.open_workbook('files/作业数据2.xls')
sheet1 = wb1.sheet_by_name('原始数据')

# 2.创建新的Excel文件，并且添加新的表
wb2 = xlwt.Workbook()
sheet2 = wb2.add_sheet('效果')

# 3.准备写入数据的时候需要的样式

# 边框
border = xlwt.Borders()
border.top = 1
border.bottom = 1
border.left = 1
border.right = 1

# 1)表头样式
style1 = xlwt.XFStyle()
style1.borders = border

# 字体
font1 = xlwt.Font()
font1.name = '黑体'
font1.bold = True
font1.height = 20*20
style1.font1 = font1

# 填充
fill1 = xlwt.Pattern()
fill1.pattern = xlwt.Pattern.SOLID_PATTERN
fill1.pattern_fore_colour = 29
style1.pattern = fill1

# 对齐
ag = xlwt.Alignment()
ag.horz = xlwt.Alignment.HORZ_CENTER
ag.vert = xlwt.Alignment.VERT_CENTER
style1.alignment = ag

# 2)名字内容对应的样式
style2 = xlwt.XFStyle()
style2.borders = border

font2 = xlwt.Font()
font2.colour_index = 20
font2.height = 15*15
style2.font = font2
style2.alignment = ag

# 3)普通分数的样式
style3 = xlwt.XFStyle()
style3.borders = border

font3 = xlwt.Font()
font3.height = 15*15
style3.font = font3
style3.alignment = ag

# 4)补考样式
style4 = xlwt.XFStyle()
style4.borders = border

font4 = xlwt.Font()
font4.colour_index = 10
font4.height = 15*15
style4.font = font4
style4.alignment = ag

# 5)不及格分数样式
style5 = xlwt.XFStyle()
style5.borders = border

fill2 = xlwt.Pattern()
fill2.pattern = xlwt.Pattern.SOLID_PATTERN
fill2.pattern_fore_colour = 10
style5.pattern = fill2
style5.font = font3

style5.alignment = ag


# 4.读取数据，写入到新建的效果表中
mr = sheet1.nrows
mc = sheet1.ncols
for i in range(mr):
    for j in range(mc):
        data = sheet1.cell(i, j).value          # 获取原始数据中每一个单元格的内容
        if data == 0:
            sheet2.write(i, j, '补考', style = style4)
        else:
            if i == 0:
                # 第一行表头样式
                sheet2.write(i, j, data, style = style1)
            elif j == 0:
                # 设置名字样式
                sheet2.write(i, j, data, style = style2)
            elif data < 60:
                # 不及格样式
                sheet2.write(i, j, data, style = style5)
            else:
                # 普通分数样式
                sheet2.write(i, j, data, style = style3)

# 设置宽度和高度
for i in range(mc):
    sheet2.col(i).width = 25*256     # 列宽

# 设置第一行的高度
sheet2.row(0).height_mismatch = True
sheet2.row(0).height = 10*60

wb2.save('files/结果.xls')