热点！Python 使用openpyxl处理Excel文件详情
目录
前言1. Excel窗口2. 读取Excel文件3. 写入Excel文件4. 复制Excel文件5. 创建工作表6. 设置单元格字体及颜色7. 数学公式的使用8. 设置单元格宽高9. 设置单元格对齐方式10. 合并与取消单元格合并11. 创建图表11.1 柱状图11.2 饼图前言
安装openpyxl模块：
pip install openpyxl
导入模块：
import openpyxl
官方文档：
【资料图】
1. Excel窗口
工作簿（workbook）：Excel的文件工作表（worksheet）：一个工作簿由多个工作表组成列（column）：工作表的列名为A、B、C等的大写字母行（row）：工作表的行名称为1、2、3等的数字单元格（cell）：工作表中的每个格子称为单元格，用(列名,行名)表示2. 读取Excel文件
# author:mlnt
# createdate:2022/8/16
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
# 1.打开文件
# 使用openpyxl.load_workbook()方法打开Excel文件
filename = "data.xlsx"
work_book = openpyxl.load_workbook(filename=filename) # 加载Excel文件
# 2.获取工作表名称
"""
- Excel文件对象.sheetnames：获取工作簿文件的所有工作表，以列表数据类型返回
- Excel文件对象.active：获取当前工作表的名称
"""
# 获取所有工作表的名称
work_sheets = work_book.sheetnames
print(f"工作表列表：{work_sheets}")
# 工作表列表：["Sheet1", "Sheet2", "Sheet3"]
# 获取当前工作表的名称
current_sheet = work_book.active
print(f"当前工作表：{current_sheet}")
# 当前工作表：
# 获取当前工作表的内容
title = current_sheet.title
print(f"当前工作表标题：{title}")
# 当前工作表标题：Sheet1
# 3.切换工作表
work_sheet = work_book["Sheet2"] # 返回名称相应的工作表
print(f"当前工作表：{work_sheet.title}")
# 当前工作表：Sheet2
work_sheet = work_book["Sheet1"] # 返回名称相应的工作表
print(f"当前工作表：{work_sheet.title}")
# 当前工作表：Sheet1
# 4.获取工作表的内容
print(f"单元格A1: {work_sheet["A1"].value}")
print(f"单元格B1: {work_sheet["B1"].value}")
print(f"单元格C1: {work_sheet["C1"].value}")
print(f"单元格D1: {work_sheet["D1"].value}")
print(f"单元格E1: {work_sheet["E1"].value}")
print(f"单元格F1: {work_sheet["F1"].value}")
# 单元格A1: 姓名
# 单元格B1: 字
# 单元格C1: 号
# 单元格D1: 所处时代
# 单元格E1: 别称
# 单元格F1: 代表作
# 获取单元格相对位置信息
# column：列，row：行，coordinate：坐标
print(f"单元格A1: {work_sheet["A1"].column}, {work_sheet["A1"].row}, {work_sheet["A1"].coordinate}")
print(f"单元格B1: {work_sheet["B1"].column}, {work_sheet["B1"].row}, {work_sheet["B1"].coordinate}")
print(f"单元格C1: {work_sheet["C1"].column}, {work_sheet["C1"].row}, {work_sheet["C1"].coordinate}")
print(f"单元格D1: {work_sheet["D1"].column}, {work_sheet["D1"].row}, {work_sheet["D1"].coordinate}")
print(f"单元格E1: {work_sheet["E1"].column}, {work_sheet["E1"].row}, {work_sheet["E1"].coordinate}")
print(f"单元格F1: {work_sheet["F1"].column}, {work_sheet["F1"].row}, {work_sheet["F1"].coordinate}")
# 单元格A1: 1, 1, A1
# 单元格B1: 2, 1, B1
# 单元格C1: 3, 1, C1
# 单元格D1: 4, 1, D1
# 单元格E1: 5, 1, E1
# 单元格F1: 6, 1, F1
# 5.获取工作表内容的列数和行数
print(f"工作表列数：{work_sheet.max_column}")
print(f"工作表行数：{work_sheet.max_row}")
# 工作表列数：6
# 工作表行数：20
# 6.获取单元格内容
# cell(column=n, row=m)
for j in range(1, work_sheet.max_row + 1):
for i in range(1, work_sheet.max_column + 1):
print(work_sheet.cell(column=i, row=j).value, end=" ")
print()
# 7.工作表对象的rows和columns
"""
创建工作表对象成功后，会自动产生数据产生器（generators）：
rows: 工作表数据产生器以行方式包裹，每一行用一个Tuple包裹；
columns：工作表数据产生器以列方式包裹，每一列用一个Tuple包裹。
"""
print(type(work_sheet.rows)) #
print(type(work_sheet.columns)) #
for cell in list(work_sheet.columns)[0]:
print(cell.value)
for cell in list(work_sheet.rows)[1]:
print(cell.value, end=" ")
# 逐行遍历
print("逐行遍历开始...")
for row in work_sheet.rows:
for cell in row:
print(cell.value, end=" ")
print()
print("逐行遍历结束...")
# 逐列遍历
print("逐列遍历开始...")
for column in work_sheet.columns:
for cell in column:
print(cell.value, end=" ")
print()
print("逐列遍历结束...")
# 8.用整数取代域名
"""
get_column_letter(数值)：将数值转成字母
column_index_from_string(字母)：将字母转成数值
"""
print(f"列数：{get_column_letter(work_sheet.max_column)}")
print(f"3 --> {get_column_letter(3)}")
print(f"26 --> {get_column_letter(26)}")
print(f"39 --> {get_column_letter(39)}")
print(f"46 --> {get_column_letter(46)}")
print(f"120 --> {get_column_letter(120)}")
# 列数：F
# 3 --> C
# 26 --> Z
# 39 --> AM
# 46 --> AT
# 120 --> DP
print(f"A --> {column_index_from_string("A")}")
print(f"F --> {column_index_from_string("F")}")
print(f"AB --> {column_index_from_string("AB")}")
print(f"BBC --> {column_index_from_string("BBC")}")
print(f"CNN --> {column_index_from_string("CNN")}")
# A --> 1
# F --> 6
# AB --> 28
# BBC --> 1407
# CNN --> 2406
# 9.切片
# 使用切片的概念读取某区间数据
# 逐行读取
for row in work_sheet["A3":"F4"]:
for cell in row:
print(cell.value, end=" ")
print()
# 白居易 乐天 香山居士 唐朝 诗魔、诗王 《长恨歌》、《卖炭翁》、《琵琶行》
# 杜甫 子美 少陵野老 唐朝 诗圣 《春望》、《茅屋为秋风所破歌》、《登高》、《望岳》 data.xlsx：
3. 写入Excel文件
import openpyxl
# 1.创建空白工作簿
work_book = openpyxl.Workbook()
# 2.保存Excel文件
work_book.save("new_workbook.xlsx")4. 复制Excel文件
import openpyxl
filename = "data.xlsx"
work_book = openpyxl.load_workbook(filename=filename) # 开启工作簿
backup_name = filename[:filename.find(".xlsx")] + "-backup.xlsx"
work_book.save(backup_name)效果：
5. 创建工作表
# author:mlnt
# createdate:2022/8/16
import openpyxl
# 1.创建空白工作簿
work_book = openpyxl.Workbook()
print(f"工作表列表：{work_book.sheetnames}") # 工作表列表：["Sheet"]
# 2.创建新的工作表
work_book.create_sheet()
print(f"工作表列表：{work_book.sheetnames}") # 工作表列表：["Sheet", "Sheet1"]
work_sheet = work_book.active # 获取当前工作表
print(f"当前工作表：{work_sheet.title}") # 当前工作表：Sheet
"""
在创建工作表时，预设的工作表名称为”SheetN“，N为数字编号，以递增方式显示；
新建的工作表放在工作表列的最右边。
可以通过在create_sheet()中添加参数title和index设置新工作表的名称及位置（工作表位置从0开始）
"""
work_book.create_sheet(index=0, title="工作表1")
work_book.create_sheet(index=2, title="工作表3")
print(f"工作表列表：{work_book.sheetnames}")
# 工作表列表：["工作表1", "Sheet", "工作表3", "Sheet1"]
# 3.删除工作表
# 删除”工作表3“
work_book.remove(work_book["工作表3"])
print(f"工作表列表：{work_book.sheetnames}") # 工作表列表：["工作表1", "Sheet", "Sheet1"]
# 删除”Sheet“
del work_book["Sheet"]
print(f"工作表列表：{work_book.sheetnames}") # 工作表列表：["工作表1", "Sheet1"]
# 4.写入单元格
work_sheet = work_book.active # 获取当前工作表
print(f"当前工作表：{work_sheet.title}") # 当前工作表：Sheet
rows = [
["姓名", "年龄", "联系方式", "学历"],
["张三", "18", "18888886666", "大专"],
["王二狗", "28", "18888888888", "研究生"],
["苟恭芝", "38", "18888889999", "博士"],
["李华", "20", "18888887777", "本科"],
["曹亠强", "18", "18888883333", "大专"]
]
for row in rows:
work_sheet.append(row)
# 保存Excel文件
work_book.save("my_workbook.xlsx")
6. 设置单元格字体及颜色
# author:mlnt
# createdate:2022/8/16
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
"""
bold：加粗，值为True时表示粗体
italic：斜体，值为True时设置斜体
strike：删除线，值为True时设置删除线
name：字体名称，如：Arial
size：字号
color：字体颜色，color="FFFFFF"
"""
fontTitle1 = Font(name="微软雅黑", size=24)
ws["A1"].font = fontTitle1
ws["A1"] = "勿谓言之不预"
fontTitle2 = Font(name="楷体", size=18, bold=True)
ws["A2"].font = fontTitle2
ws["A2"] = "山不在高，有仙则名"
# 设置字体及颜色
# RGB颜色对照表：https://www.917118.com/tool/color_3.html
fontTitle3 = Font(name="Arial", size=20, italic=True, color="00FF7F")
ws["A3"].font = fontTitle3
ws["A3"] = "The early bird catches the worm."
# 保存Excel文件
wb.save("设置单元格字体.xlsx")效果：
7. 数学公式的使用
常用的数学公式：
SUM()：总和，如：SUM(A1:A3)AVERAGE()：平均值，如：AVERAGE(A1:A3)MAX()：最大值，如：MAX(A1:A3)MIN()：最小值，如：MIN(A1:A3)import openpyxl
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
rows = [
["学号", "姓名", "语文", "数学", "英语", "物理", "化学", "生物", "总分"],
["1001", "张三", 90, 98, 106, 80, 85, 78, "=SUM(C2:H2)"],
["1002", "Tom", 93, 100, 96, 84, 75, 68, "=SUM(C3:H3)"],
["1003", "Jack", 89, 80, 108, 70, 65, 88, "=SUM(C4:H4)"],
["1004", "Mary", 110, 88, 88, 68, 68, 64, "=SUM(C5:H5)"],
["1005", "Jane", 98, 78, 86, 56, 95, 72, "=SUM(C6:H6)"]
]
for row in rows:
# 将数据添加到工作表
ws.append(row)
ws["B7"] = "总分"
ws["C7"] = "=SUM(C2:C6)"
ws["D7"] = "=SUM(D2:D6)"
ws["E7"] = "=SUM(E2:E6)"
ws["F7"] = "=SUM(F2:F6)"
ws["G7"] = "=SUM(G2:G6)"
ws["H7"] = "=SUM(H2:H6)"
ws["B8"] = "平均分"
ws["C8"] = "=AVERAGE(C2:C6)"
ws["D8"] = "=AVERAGE(D2:D6)"
ws["E8"] = "=AVERAGE(E2:E6)"
ws["F8"] = "=AVERAGE(F2:F6)"
ws["G8"] = "=AVERAGE(G2:G6)"
ws["H8"] = "=AVERAGE(H2:H6)"
ws["B9"] = "最高分"
ws["C9"] = "=MAX(C2:C6)"
ws["D9"] = "=MAX(D2:D6)"
ws["E9"] = "=MAX(E2:E6)"
ws["F9"] = "=MAX(F2:F6)"
ws["G9"] = "=MAX(G2:G6)"
ws["H9"] = "=MAX(H2:H6)"
ws["B10"] = "最低分"
ws["C10"] = "=MIN(C2:C6)"
ws["D10"] = "=MIN(D2:D6)"
ws["E10"] = "=MIN(E2:E6)"
ws["F10"] = "=MIN(F2:F6)"
ws["G10"] = "=MIN(G2:G6)"
ws["H10"] = "=MIN(H2:H6)"
wb.save("数学公式的使用.xlsx")效果：
8. 设置单元格宽高
单元格预设的高度为12.75pt，72pt等于1英寸，使用column_dimensions属性可以设置行高；单元格默认宽度为8.43个英文字符宽度，可使用row_dimensions设置单元格的宽度如果将宽度或高度设置为0，则具有隐藏单元格的效果。
import openpyxl
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
ws["A1"] = "海内存知己"
ws["A2"] = "天涯若比邻"
ws["B2"] = "Hello world"
ws.row_dimensions[1].height = 30 # 设置高度为30pt
ws.column_dimensions["B"].width = 30 # 设置宽度为30个英文字符宽
wb.save("设置单元格宽高.xlsx")效果：
9. 设置单元格对齐方式
使用Alignment()方法，需设置2个参数：
horizontal（水平方向）:
left：靠左right: 靠右center: 居中vertical（垂直方向）:
top：靠上center：居中bottom：靠下import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
ws["A1"] = "测试1"
ws["B1"] = "测试2"
ws["C1"] = "测试3"
ws.row_dimensions[1].height = 30 # 设置高度为40pt
ws.column_dimensions["B"].width = 20 # 设置宽度为20个字符宽
ws["A1"].alignment = Alignment(horizontal="left", vertical="top") # 居左靠上
ws["B1"].alignment = Alignment(horizontal="center", vertical="center") # 水平居中，垂直居中
ws["C1"].alignment = Alignment(horizontal="right", vertical="bottom") # 靠右居下
# 保存excel文件
wb.save("设置单元格对齐方式.xlsx")效果：
10. 合并与取消单元格合并
合并单元格：
使用merge_cells()合并单元格，可以合并同一行（row）、同一列（column）或一个区域的单元格
取消合并单元格：
unmerge_cells()
# author:mlnt
# createdate:2022/8/16
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
"""
1.合并单元格
使用merge_cells()合并单元格，可以合并同一行（row）、同一列（column）或一个区域的单元格
"""
ws["A1"] = "早起的鸟儿有虫吃"
ws["A2"] = "The early bird catches the worm."
ws["A3"] = "人生如戏"
ws["C4"] = "Where there is a will there is a way."
ws.merge_cells("A1:D1") # 合并A1:D1单元格
ws.merge_cells("A3:A8") # 合并A3:A8单元格
ws.merge_cells("C4:G6") # 合并C4:G6单元格
ws["A1"].alignment = Alignment(horizontal="center")
ws["A3"].alignment = Alignment(vertical="center")
ws["C3"].alignment = Alignment(horizontal="center", vertical="center")
# 2.取消合并单元格
# unmerge_cells()
ws.unmerge_cells("A3:A8") # 取消合并A3:A8单元格
wb.save("合并与取消单元格合并.xlsx")效果：
11. 创建图表
11.1 柱状图
# author:mlnt
# createdate:2022/8/16
"""
BarChart：柱状图
BarChart3D：3D柱状图
PieChart：饼图
PieChart：3D饼图
BubleChart：泡泡图
AreaChart：分区图
AreaChart3D：3D分区图
LineChart：折线图
LineChart3D：3D折线图
RedarChart：雷达图
StockChart：股票图
"""
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
rows = [
["学号", "姓名", "语文", "数学", "英语", "物理", "化学", "生物"],
["1001", "张三", 90, 98, 106, 80, 85, 78],
["1002", "Tom", 93, 100, 96, 84, 75, 68],
["1003", "Jack", 89, 80, 108, 70, 65, 88],
["1004", "Mary", 110, 88, 88, 68, 68, 64],
["1005", "Jane", 98, 78, 86, 56, 95, 72]
]
for row in rows:
# 将数据添加到工作表
ws.append(row)
chart = BarChart() # 直方图
chart.title = "2022某班某小组学生成绩表" # 图表标题
chart.y_axis.title = "分数" # y轴标题
chart.x_axis.title = "学员" # x轴标题
data = Reference(ws, min_col=3, max_col=8, min_row=1, max_row=6) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
x_title = Reference(ws, min_col=2, min_row=2, max_row=6) # x轴标记名称
chart.set_categories(x_title) # 设置x轴标记名称
ws.add_chart(chart, "J1") # 放置图标位置
wb.save("柱状图.xlsx")效果：
11.2 饼图
# author:mlnt
# createdate:2022/8/16
import openpyxl
from openpyxl.chart import PieChart, Reference
wb = openpyxl.Workbook() # 创建空白工作簿
ws = wb.active # 获得当前工作表
rows = [
["科目", "分数"],
["语文", 90],
["数学", 98],
["英语", 106],
["物理", 80],
["化学", 85],
["生物", 78]
]
for row in rows:
ws.append(row)
chart = PieChart() # 饼图
chart.title = "某学员成绩分析表"
data = Reference(ws, min_col=2, min_row=1, max_row=7) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
labels = Reference(ws, min_col=1, min_row=2, max_row=7) # 标签名称
chart.set_categories(labels) # 设置标签名称
ws.add_chart(chart, "D1")
wb.save("饼图.xlsx")效果：
到此这篇关于Python 使用openpyxl处理Excel文件详情的文章就介绍到这了,更多相关Python 处理Excel文件 内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
X 关闭
X 关闭
- 1亚马逊开始大规模推广掌纹支付技术 顾客可使用“挥手付”结账
- 2现代和起亚上半年出口20万辆新能源汽车同比增长30.6%
- 3如何让居民5分钟使用到各种设施？沙特“线性城市”来了
- 4AMD实现连续8个季度的增长 季度营收首次突破60亿美元利润更是翻倍
- 5转转集团发布2022年二季度手机行情报告：二手市场“飘香”
- 6充电宝100Wh等于多少毫安？铁路旅客禁止、限制携带和托运物品目录
- 7好消息！京东与腾讯续签三年战略合作协议 加强技术创新与供应链服务
- 8名创优品拟通过香港IPO全球发售4100万股 全球发售所得款项有什么用处？
- 9亚马逊云科技成立量子网络中心致力解决量子计算领域的挑战
- 10京东绿色建材线上平台上线 新增用户70%来自下沉市场