Python —— 新版openpyxl简记

官方帮助地址
 
 
 
 

使用的基本流程

1、导入openpyxl
2、实例化wb对象
3、实例化sheet对象
4、获取或修改sheetName
5、获取或修改cellValue
6、生成图表
7、保存或另存excel

 
 
 
 

实例化excel表格及sheet对象

workbook对象可以进行遍历，会遍历每个sheet表

 
 
 
 

读取excel表格

命令：

openpyxl.load_workbook()

返回值：workbook对象

 
示例：

 
 
 
 

获取当前excel中有哪些sheet及其名字

命令：
注意：wb.get_sheet_names()已经被弃用，尽量避免使用这个方式去获取sheet名

wb.sheetnames --- 属性
wb.get_sheet_names() --- 方法 （已弃用）

返回值：字符串形式的表名

 

示例：

 
 
 
 

通过sheet名实例化sheet对象

命令：

sheet = wb[sheetName]

返回值：sheet对象

 
示例：

 
 
 
 

获取当前活动的sheet

命令：

sheetAc = wb.active

返回值：sheet对象

 
示例：

 
 
 
 
 
 

修改excel表格数据

注意： 如果不是非常确定知道自己在做什么，建议每次修改excel并需要保存修改的时候，使用另存的形式修改， 或者在git跟踪状态下进行修改。

 
 

修改sheet名（可读写属性）

命令：

# 获取sheet名
sheetObject.title
# 修改sheet名
sheetObject.title = "mySheet"

返回值：字符串

 
示例：

 
 
 
 

从sheet中获取cell对象

注意：sheet中的cellName为"A1"、"B2"之类的，列从A开始，行从1开始而非0
命令：

sheetObject[cellName]

返回值：cell对象

 
示例：

 
 

修改单元格的值

wb = openpyxl.workbook()
sht = wb.active
# 方法一
sht["A1"] = 1
# 方法二
sht.cell(1, 1).value = 1

 
 

获取单元格的值 (可读写属性)

命令：

cellObject.value
或
sheetObject.cell(row=rowNum, culumn=colNum)

返回值：单元格中的值

 
示例：



 
 

获取单元格的坐标

命令：

# 获取行号
cellObject.row
# 获取列号（以数字1、2、3形式而非A、B、C）
cellObject.column
# 获取cell的坐标名
cellObject.coordinate

返回值：略

 
示例：

 
 
 
 

获取最大、最小行列数

命令：

sheetAc.max_column
sheetAc.max_row
sheetAc.min_column
sheetAc.min_column

返回值：数值

 
示例：

 
 
 
 

对表进行切片

命令：

# 切片的结果包含A1-B3的所有单元格
sheetAc["A1":"B3"]

返回值：tuple

 
示例：

 
 
 
 

遍历所有的行列

命令：

sheetObject.columns
sheetObject.rows

返回值：

 
示例：

 
 
 
 
 
 

保存excel数据

命令：

wb.save()

 
 
 
 
 
 

创建和删除工作表

 
 

创建excel表

命令：

wb = openpyxl.Workbook()

示例：

 
 
 
 

创建sheet表

命令：

wb.create_sheet(index=1, title='test')

示例：

 
 
 
 

删除sheet表

命令：

wb.remove(sheetObject)

示例：

 
 
 
 
 
 

修改单元格字体

流程：
1、实例化font对象（openpyxl.styles.Font(para)）
para：

• name —— 字体名称（str——Calibri、Times New Roman）
• size —— 字体大小（int）
• bold —— 字体粗体（boolean）
• italic —— 字体斜体（boolean）
• color —— 字体颜色（如"ff0000"）

命令:

fontObject= 	openpyxl.styles.Font()	
cellObject.font = fontObject

示例：

import openpyxl

# 数据路径
xlsx = r"C:\Users\dell\Desktop\temp1.xlsx"

wb = openpyxl.load_workbook(xlsx)
sht = wb.active

font = openpyxl.styles.Font(name="Time New Romans", bold=True, italic=True)

for eachCol in sht.columns:
    for eachCell in eachCol:
        eachCell.font = font

wb.save("D:/q.xlsx")

 
 
 
 
 
 

添加公式

将单元格的值改为具体的公式即可。如：

sht["F16"] = "=SUM(F2:F15)"

注意在读取excel的时候，若单元格为公式则默认读取公式，若要读取结果那么设置如下即可openpyxl.load_workbook(data_only=True)

 
 
 
 
 
 

设置行高、列宽

命令：

# 设置行高、列宽
sht.row_dimensions[1].height = 30
sht.column_dimensions["B"].width = 20

 
 
 
 
 
 

应用实例

 
 

基础数据

 
 
 
 

 
 

实例1、给表中所有SALES部门的人涨薪 500

代码：

# 给所有销售人员涨薪500
import openpyxl
# 数据路径
xlsx = r"C:\Users\dell\Desktop\temp1.xlsx"

# 读取excel表格
wb = openpyxl.load_workbook(xlsx)

# 读取sheet
sht = wb.active

dnameCellRow = None
dnameCellColumn = None
for col in range(sht.max_column):
    # 假定不知道DNAME在哪一列
    col += 1
    cell = sht.cell(row=1, column=col)
    if str(cell.value).upper() == "DNAME":
        dnameCellRow = cell.row
        dnameCellColumn = cell.column

# 获取薪水所在的位置
salCellRow = None
salCellColumn = None
for col in range(sht.max_column):
    # 假定不知道DNAME在哪一列
    col += 1
    cell = sht.cell(row=1, column=col)
    if str(cell.value).upper() == "SAL":
        salCellRow = cell.row
        salCellColumn = cell.column


# 存在DNAME列
if dnameCellColumn and dnameCellRow:
    for row in range(sht.max_row):
        row += 2
        if str(sht.cell(row=row, column=dnameCellColumn).value).upper() == "SALES":
            sht.cell(row=row, column=salCellColumn).value += 500
#             print(sht.cell(row=row, column=salCellColumn))

wb.save(r"C:\Users\dell\Desktop\temp1_copy.xlsx")

print(dnameCellRow, dnameCellColumn)

结果：
1、原始数据：

2、结果数据

demo:

 
 
 
 

命令：

返回值：

 
示例：