可以下载的网站模板,深圳 网站策划,简单详细搭建网站教程视频教程,雅安建设网站Excel自动化办公——Openpyxl的基本使用
个人感觉#xff0c;相比Pandas#xff0c;openpyxl对Excel的操作更为细致#xff0c;Pandas则更适用于统计计算#xff1b;
01 基本环境02 Excel数据读取操作03 案例04 向Excel写入数据05 表数据定向修改06 单元格样式制定07 单元…Excel自动化办公——Openpyxl的基本使用
个人感觉相比Pandasopenpyxl对Excel的操作更为细致Pandas则更适用于统计计算
01 基本环境02 Excel数据读取操作03 案例04 向Excel写入数据05 表数据定向修改06 单元格样式制定07 单元格边框样式制定08 单元格对其方式09 筛选器10 公式操作11 设置行高、列宽12 单元格拆分合并13 冻结窗口14 添加绘制图表
01 基本环境
使用库openpyxl3.x依赖 lxml、pillow
一些基本定义
工作簿一个Excel电子表格文档保存到扩展名为.xlsx的文件中sheet表工作簿包含的多个工作表活动表当前查看的表或关闭前最后查看的表单元格列从A开始行从1开始特定行列的位置方格即为单元格注意在Z之后使用两个字母AA、AB、AC等
import openpyxl
openpyxl.__version__3.0.902 Excel数据读取操作
# 加载工作簿返回工作簿对象
wb openpyxl.load_workbook(./youtube_mp3_ds_test.xlsx)# 获取工作表名称列表
print(工作表名称列表 ,wb.sheetnames)# 获取sheet返回工作表对象
ws1 wb[Sheet1]# 获取活动表返回工作表对象
ws2 wb.active# 从表中取单元格按行列名字访问返回Cell对象
cell ws2[A4]# 属性valuecell中存储的值
# 属性row行索引
# 属性column列索引
# 属性coordinate坐标
print(单元格诸属性 ,cell.value, cell.row, cell.column, cell.coordinate)# 行索引可以使用数字代替字母从1开始但需要变化下方式
cell ws2.cell(row1, column4)
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 803 案例
# 四列普查区编号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(titletest_sheetA, index0) # 指定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(row2, column1).value ValueA
sheet.cell(row2, column2).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(rowrowNum, column1).valueif cur_name in PRICE_UPDATE:sheet.cell(rowrowNum, column2).value PRICE_UPDATE[cur_name]# 保存更新 wb 06 单元格样式制定
使用openpyxl.stayles模块导入 Font 和 PatternFill 工具包
字体
name“楷体”名称color“000000”颜色italicTure斜体size12字体大小underline“sigle”单下划线bTrue粗体
# 设置字体样式
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楷体,color668B8B)
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(patternTypesolid,fgColor8470FF)
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(styledouble, colorFF0000)col.border Border(topside,bottomside, leftside, rightside)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(horizontalright,verticalcenter)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(refB2:B3, descendingTrue) # 依旧存在上面的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_onlyTrue)
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_row2, min_col2, max_row5, max_col3)# 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(row1, columnindex).valueseriesObj openpyxl.chart.Series(list(values.cols)[index-2], titlename)chart.append(seriesObj)# 以第1列 作为x轴展示(需要add_data后设置)
x_label openpyxl.chart.Reference(sheet, min_row2, min_col1, max_row5, max_col1)
chart.set_categories(x_label)sheet.add_chart(chart, I1) # I1 表示放置位置的单元格
wb.save(./youtube_mp3_ds_test1.xlsx)
