Excel自动化办公——Openpyxl的基本使用

news/2024/11/29 22:43:42/

Excel自动化办公——Openpyxl的基本使用

个人感觉，相比Pandas，openpyxl对Excel的操作更为细致，Pandas则更适用于统计计算；

  • 01 基本环境
  • 02 Excel数据读取操作
  • 03 案例
  • 04 向Excel写入数据
  • 05 表数据定向修改
  • 06 单元格样式制定
  • 07 单元格边框样式制定
  • 08 单元格对其方式
  • 09 筛选器
  • 10 公式操作
  • 11 设置行高、列宽
  • 12 单元格拆分合并
  • 13 冻结窗口
  • 14 添加绘制图表

01 基本环境

使用库：openpyxl（3.x），依赖 lxml、pillow；

一些基本定义：

  • 工作簿：一个Excel电子表格文档，保存到扩展名为.xlsx的文件中；
  • sheet表：工作簿包含的多个工作表；
  • 活动表：当前查看的表，或关闭前最后查看的表；
  • 单元格：列从A开始，行从1开始，特定行列的位置方格即为单元格；注意在Z之后，使用两个字母AA、AB、AC等；
import openpyxl
openpyxl.__version__

'3.0.9'

02 Excel数据读取操作

# 加载工作簿，返回工作簿对象
wb = openpyxl.load_workbook("./youtube_mp3_ds_test.xlsx")# 获取工作表名称列表
print("工作表名称列表 = ",wb.sheetnames)# 获取sheet，返回工作表对象
ws1 = wb["Sheet1"]# 获取活动表，返回工作表对象
ws2 = wb.active# 从表中取单元格，按行列名字访问，返回Cell对象
cell = ws2["A4"]# 属性：value：cell中存储的值
# 属性：row：行索引
# 属性：column：列索引
# 属性：coordinate：坐标
print("单元格诸属性 = ",cell.value, cell.row, cell.column, cell.coordinate)# 行索引可以使用数字，代替字母，从1开始，但需要变化下方式；
cell = ws2.cell(row=1, column=4)
print("单元格诸属性（索引方式） = ",cell.value, cell.row, cell.column, cell.coordinate)# 从工作表中 可以获取一个矩形区域
for cell_row in ws2["A2":"E6"]:for cell in cell_row:
#         print(cell.coordinate)pass# 也可以获取单独的一行或一列：使用worksheet对象的rows和columns属性，返回的是一个迭代器
for column in ws2.columns:for cell in column:print("每一列的第一行 = ",cell.coordinate)break# 获取第一列 的所有单元格        
list(ws2.columns)[0]# 获取工作表中的最大行数和最大列数
print("工作表中的最大行数和最大列数 = ",ws2.max_row,ws2.max_column)
工作表名称列表 =  ['Sheet1']
单元格诸属性 =  114584 4 1 A4
单元格诸属性（索引方式） =  a_item 1 4 D1
每一列的第一行 =  A1
每一列的第一行 =  B1
每一列的第一行 =  C1
每一列的第一行 =  D1
每一列的第一行 =  E1
每一列的第一行 =  F1
每一列的第一行 =  G1
每一列的第一行 =  H1
工作表中的最大行数和最大列数 =  12567 8

03 案例

# 四列：普查区编号A、州简称B、县名称C、普查区人口D；每个县可能存在多个普查区；# 代码示意
sheet = ...
countryData = {}
for row in range(2, sheet.max_row + 1):cell_1 = sheet["B" + str(row)]cell_2 = sheet["C" + str(row)]cell_3 = sheet["D" + str(row)]countryData.setdefault(cell_1.value, {})countryData[cell_1.value].setdefault(cell_2.value, {'v1':0, 'v2':0})countryData[cell_1.value][cell_2.value]['v1'] += 1countryData[cell_1.value][cell_2.value]['v2'] += int(cell_3.value)# 结果可以保存到json

04 向Excel写入数据


# 创建一个空工作薄
wb = openpyxl.Workbook()wb.sheetnames # 默认工作表
['Sheet']


ws = wb.active# 为工作表 设置名称
ws.title = "test_sheet"
ws.title
'test_sheet'

# 保存工作薄
wb.save("./wb_demo.xlsx")
# 创建工作表
wb = openpyxl.load_workbook("./wb_demo.xlsx")
wb.create_sheet(title="test_sheetA", index=0) # 指定sheet放的位置（该值从0开始）
wb.save("./wb_demo.xlsx")
wb.sheetnames
['test_sheetA', 'test_sheet']

# 删除工作表
del wb["test_sheetA"]
wb.sheetnames
['test_sheet']

# 指定单元格的值
sheet = wb["test_sheet"]
sheet["A1"] = "ColumnA"
sheet["B1"] = "ColumnB"
sheet.cell(row=2, column=1).value = "ValueA"
sheet.cell(row=2, column=2).value = "ValueB"
wb.save("./wb_demo.xlsx")

05 表数据定向修改

# 代码示意
wb = openpyxl.load_workbook("./xxx.xlsx")
sheet = wb["Sheet"]PRICE_UPDATE = {"A": 1.0,"B": 2.0,"C": 3.0
}
for rowNum in range(2, sheet.max_row + 1):cur_name = sheet.cell(row=rowNum, column=1).valueif cur_name in PRICE_UPDATE:sheet.cell(row=rowNum, column=2).value = PRICE_UPDATE[cur_name]# 保存更新 wb

06 单元格样式制定

使用openpyxl.stayles模块导入 Font 和 PatternFill 工具包；

字体

  • name=“楷体”，名称
  • color=“000000”，颜色
  • italic=Ture，斜体
  • size=12，字体大小
  • underline=“sigle”，单下划线
  • b=True，粗体
# 设置字体样式
import openpyxl
from openpyxl.styles import Fontwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]for row in sheet["A1":"B1"]:for col in row:col.font = Font(name="楷体")
wb.save("./wb_demo.xlsx")
# 设置字体颜色
wb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]for row in sheet["A1":"B1"]:for col in row:col.font = Font(name="楷体",color="668B8B")
wb.save("./wb_demo.xlsx")

填充色

# 设置单元格填充色
import openpyxl
from openpyxl.styles import PatternFillwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]for row in sheet["A2":"B2"]:for col in row:col.fill = PatternFill(patternType="solid",fgColor="8470FF")
wb.save("./wb_demo.xlsx")

在这里插入图片描述

07 单元格边框样式制定

# 设置边框（并不常用）
import openpyxl
from openpyxl.styles import Side, Borderwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]for row in sheet["A2":"B2"]:for col in row:side = Side(style='double', color='FF0000')col.border = Border(top=side,bottom=side, left=side, right=side)wb.save("./wb_demo.xlsx")

08 单元格对其方式

# 使用Alignment工具包
import openpyxl
from openpyxl.styles import Alignmentwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]for row in sheet["A2":"B2"]:for col in row:col.alignment = Alignment(horizontal="right",vertical='center')wb.save("./wb_demo.xlsx")

09 筛选器

import openpyxlwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]# 设置筛选器:筛选器对象需制定引用的区域
sheet.auto_filter.ref = 'A1:B3'# 设置筛选项:参数1指定对第几列应用筛选条件，参数2表示筛选条件的内容
sheet.auto_filter.add_filter_column(0, ["ValueA"]) # 但注意 数据展示样式 需要打开Excel并操作筛选后才会生效，相当于bug# 设置排序：指定排序区域，及升降序
sheet.auto_filter.add_sort_condition(ref="B2:B3", descending=True) # 依旧存在上面的bug：配置已经存在，样式却未生效wb.save("./wb_demo.xlsx")

10 公式操作

import openpyxlwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]
sheet['C4'] = '=SUM(C2:C3)'
wb.save("./wb_demo.xlsx")
# 读取使用公式的单元格的结果
wb = openpyxl.load_workbook("./wb_demo.xlsx",read_only=True)
sheet = wb["test_sheet"]
sheet['C4'].value # 注意 这是一个公式字符串，而非计算好的结果
'=SUM(C2:C3)'

11 设置行高、列宽

sheet对象有 row_dimensions 和 column_demensions 属性，控制行高和列宽；

  • 行高范围 0~409的整数或小数，表示点数，默认行高12.75，一点等于1/72英寸；
  • 列宽可设置0~255的整数或小数，默认8.43个字符；
  • 行高或列宽设为0，将使单元格隐藏；
import openpyxlwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]sheet.row_dimensions[2].height = 50 # 设置第2行 行高
sheet.column_dimensions['A'].width = 50 # 设置第A列 列宽wb.save("./wb_demo.xlsx")

12 单元格拆分合并

  • merge_cells() 将一个矩形区域的单元格合并为一个单元格
  • unmerge_cells() 用于拆分单元格
import openpyxlwb = openpyxl.load_workbook("./wb_demo.xlsx")
sheet = wb["test_sheet"]sheet.merge_cells('A2:B3')
sheet['A2'] = "合并后的内容"# sheet.unmerge_cells('A2:B3')wb.save("./wb_demo.xlsx")

13 冻结窗口

  • 悬停顶部几行或最左侧几列；
  • sheet对象的freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标字符串；
  • 单元格上边的所有行和左边的所有列都会被冻结，但单元格所在行和列不会冻结；
属性值受影响的行列
=‘A2’行1
=‘B1’列A
=‘C1’列A和列B
=‘C2’行1和列A和列B
=‘A1’ 或 =None解冻所有单元格

14 添加绘制图表

利用工作表中数据创建 条形图、折线图、散点图和饼图：

  • 创建一个Reference对象，表示作用于图表的数据区域；
  • 创建图表对象
  • 向图表对象中添加数据
  • 将图表添加到指定sheet中

python_openpyxl中的48种图表样式参考

Python办公自动化—用openpyxl制作Excel图表

import openpyxl
from openpyxl.chart.legend import LegendEntrywb = openpyxl.load_workbook("./youtube_mp3_ds_test.xlsx")
sheet = wb.active# 仅划定数据区域
values = openpyxl.chart.Reference(sheet, min_row=2, min_col=2, max_row=5, max_col=3)# chart = openpyxl.chart.BarChart()
chart = openpyxl.chart.LineChart()
# chart = openpyxl.chart.ScatterChart()
# chart = openpyxl.chart.PieChart()
chart.title = 'Bar for cls'
chart.x_axis.title = 'cls'
chart.y_axis.title = 'num'
# 图例的位置可以通过设置其位置来控制：
# 右、左、上、下和右上分别为r、l、t、b和tr。默认值为r
# chart.legend.position = 'tr'## chart.add_data(values)## 设置图例（注意 通过以下方式设置数据 不可与”chart.add_data(values)“共用
## 否则会导致新图例与”chart.add_data(values)“生成的默认图例共存）
for index in range(2,4):name = sheet.cell(row=1, column=index).valueseriesObj = openpyxl.chart.Series(list(values.cols)[index-2], title=name)chart.append(seriesObj)# 以第1列 作为x轴展示(需要add_data后设置)
x_label = openpyxl.chart.Reference(sheet, min_row=2, min_col=1, max_row=5, max_col=1)
chart.set_categories(x_label)sheet.add_chart(chart, 'I1') # I1 表示放置位置的单元格
wb.save("./youtube_mp3_ds_test1.xlsx")

在这里插入图片描述


http://www.ppmy.cn/news/1051453.html

相关文章

VisualStudio创建项目模板教程

VisualStudio创建项目模板教程

简介 Visual Studio 项目模板是预先定义的项目结构和设置，可以帮助开发者快速启动新的项目。项目模板中可以包含预配置的文件、资源、设置和代码结构，这样开发者在创建新的项目时就不必从零开始。 以下是Visual Studio项目模板的一些核心点&#xff1a…
阅读更多...
【力扣】746. 使用最小花费爬楼梯 ＜动态规划＞

【力扣】746. 使用最小花费爬楼梯 ＜动态规划＞

【力扣】746. 使用最小花费爬楼梯 给你一个整数数组 cost ，其中 cost[i] 是从楼梯第 i 个台阶向上爬需要支付的费用。一旦你支付此费用，即可选择向上爬一个或者两个台阶。你可以选择从下标为 0 或下标为 1 的台阶开始爬楼梯。 请你计算并返回达到楼梯顶…
阅读更多...
ffmpeg简介

ffmpeg简介

1.什么是ffmpeg ffmpeg即使一款音视频编解码工具，同时也是一组音视频编解码开发套件，作为编解码开发套件，它为开发者提供了丰富的音视频处理的调用接口。 ffmpeg提供了多种媒体格式的封装和解封装，包括多种音视频编码、多种协议…
阅读更多...
Android 显示、隐藏状态栏和导航栏

Android 显示、隐藏状态栏和导航栏

控制状态栏显示&#xff0c;Activity的主题中配置全屏属性 <item name"android:windowFullscreen">true</item> 控制状态栏显示&#xff0c;在setContentView之前设置全屏的flag 复制代码 getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULL…
阅读更多...
mac苹果电脑怎么运行Windows软件？怎么安装Win虚拟机？

mac苹果电脑怎么运行Windows软件？怎么安装Win虚拟机？

近年来&#xff0c;苹果电脑的用户群体不断扩大&#xff0c;许多用户对于苹果电脑是否可以运行Windows软件产生了疑问。苹果电脑和Windows操作系统有着明显的区别&#xff0c;是否能够在苹果电脑上运行Windows软件。下面我们就来看苹果电脑可以运行Windows软件吗&#xff0c;苹…
阅读更多...
二级MySQL（四）——数据表的增删改查

二级MySQL（四）——数据表的增删改查

首先认识数据类型&#xff1a; VERCHAR&#xff08;n&#xff09;最长长度为n的&#xff0c;可变长度的&#xff0c;字符串类型 CHAR&#xff08;n&#xff09;固定长度的字符串类型 TIME&#xff1a;时间内类型 DTAE&#xff1a;日期类型 INT&#xff1a;普通大小的整数 …
阅读更多...
html流光按钮

html流光按钮

出处bilibili猫咪爱狗 <!DOCTYPE html> <html><head><style>body {/*内容居中&#xff0c;背景色*/height: 100vh;display: flex;justify-content: center; align-items: center;background-color: #000;}a { /*水平垂直居中*/position: re…
阅读更多...
EasyExcel 导出报空指针NullPointerException

EasyExcel 导出报空指针NullPointerException

java.lang.NullPointerException: null at sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1264) at sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:219) 这是jdk缺少字体库问题 在官网也给出解决答案&#xff1a; 1.安装少了字体库…
阅读更多...
最新文章

Copyright @ 2022~2023