python 操作excel-使用openpyxl

0x0 python 处理excel常用方法

python常见处理excel常用方法有
xlrd、xlwd、xuitls
xlwings
openpyxl
pandas

其中openpyxl，除了不支持早期的excel格式文件(xls外)，其他均可对excel进行读、写、更改等操作，处理速度中等。其他方式或多或少缺失部分能力。
其中xlwings功能也较为强大

0x1 openpyxl 简介及安装

官方地址 https://foss.heptapod.net/openpyxl/openpyxl

安装命令

pip install openpyxl

如果还需要处理一些图片相关的数据，需要额外安装pillow模块

pip install pillow

相关概念
workbook 就一个excel文件
worksheet 一个excel文件中的一张表

row 行
column 列
cell 单元格

示例excel

species	color	height
maple	red	500
oka	green	800
pine	green	1200

0x1 workbook操作

0x1.1 导入模块

import Workbook from openpyxl
或者直接
import openpyxl

import openpyxl

0x1.2 创建新excel文件、打开存在的excel文件、保存excel文件

创建新excel并保存
注意： 保存都会覆盖原有文件(相同文件名)，且不会有提示

import openpyxl
# import Workbook from openpyxl #只导入Workbook模块

new_workbook = openpyxl.Workbook()
new_workbook.save("./new_workbook.xlsx")

打开已有excel文件并保存

import openpyxl

test_workbook = openpyxl.load_workbook("./test.xlsx")
test_workbook.save()
#test_workbook.save("./test_new.xlsx") #保存到其他文件，原始文件不变，save必须要有文件名

0x2 worksheet工作表操作

打开一个工作簿(excel文件)

import openpyxl
test_workbook = openpyxl.load_workbook("./test.xlsx")

0x2.1 打开默认工作表worksheet

(一般都是第一个工作表，默认名字也是sheet1)

test_worksheet = test_workbook.active  #active就是默认工作表

0x2.2 创建新工作表worksheet

test_worksheet = test_workbook.create_sheet(title = "sheet_title",index=1)

title： 是工作表标题名
index： 是插入位置
这两个参数都是可选参数(也可由位置参数传入)，不传入，默认由系统生成。

0x2.3 打开原有工作表worksheet

test_worksheet = test_workbook["xxxx"]

0x2.4 获取所有的工作表worksheet

使用.sheetnames或者对workbook进行遍历

test_workbook.sheetnames #1、直接通过sheetnames属性获取工作表

for ws in test_workbook:  #2、直接对workbook进行遍历
	print(ws.title)

0x2.5 获取和更改工作表worksheet名字

使用.tittle

test_worksheet.title #为工作表的名字
test_worksheet.title 为重命名

0x2.6 复制工作表worksheet

使用copy_worksheet

source_worksheet  = test_workbook['xxx']
new_worksheet  = test_workbook.copy_worksheet(source_worksheet)

0x2.7 删除工作表worksheet

test_workbook.remove(test_worksheet) #可以把其当成一个列表进行删除
del test_worksheet #直接在内存中删除该数据

#注意保存

0x3 行、列、单元格操作

首先说明的是excel的行号都是用数字表示(从1开始)，列号都是字母表示(超过26个 则使用多个字母)。

打开一个工作簿(excel文件)

import openpyxl
test_workbook = openpyxl.load_workbook("./test.xlsx")
test_worksheet = test_worksheet["xxx_worksheet"]

0x3.1 行操作

指定某行或连续多行
row_xx = test_worksheet[10] #第十行
rows_x_x = test_worksheet[5:10] #第5到10行

row_2 = test_worksheet[2]
for cell in row_2:
	print(cell.value)

rows_1_3 = test_worksheet[1:3]
for row in rows_1_3:
	for cell in row:
		print(cell.value)

获取所有行
test_worksheet.rows

rows = test_worksheet.rows
for row in rows:
	for cell in row:
		print(cell.value)

插入行
test_worksheet.insert_rows(x) #在第x前插入一行

test_worksheet.insert_rows(1)
for cell in test_worksheet[1]:
	cell.value =1 

删除行
test_worksheet.delete_rows(x)
test_worksheet.delete_rows(x,y)

0x3.2 列操作

获取某列或连续多列
column_xx = test_worksheet[‘A’] #第1列
column_x_x = test_worksheet[‘A:D’] #第1列到第4列，注意是在字符串内

column_A = test_worksheet['A']
for cell in column_A:
	print(cell.value)

columns_A_C = test_worksheet['A:C']
for row in column_A_C:
	for cell in row:
		print(cell.value)

获取所有列
test_worksheet.columns

for column in test_worksheet.columns:
	for cell in column:
		print(cell.value)

插入列(指定位置)
test_worksheet.insert_cols(2) #注意cols是简写，columns
test_worksheet.insert_cols(2,3) # 第一个参数是插入位置（xx之前），后面是数量

test_worksheet.insert_cols(2)
for cell in test_worksheet['B']
	cell.value = 1

删除列(指定位置)-内容

test_worksheet.delete_cols(2)
test_worksheet.delete_cols(2,3)

0x3.3 单元格或区域操作

单元格
test_worksheet[‘A1’]
或者
test_worksheet.cell(row=1,column=1,value=22)

单元格的值
test_worksheet[‘A1’].value #可直接对其赋值，修改，获取

单元格的行和列
cell.row
cell.column

单元区域
cell_range = ws[‘A1’:‘C2’]
cell_range = ws.iter_rows(min_row=1, max_col=3, max_row=2) #返回行形式的数据
cell_range = ws.iter_cols(min_row=1, max_col=3, max_row=2) #返回以列形式的数据

0X4 边框，颜色，对齐，字体，格式

参考

https://openpyxl.readthedocs.io/en/stable/