Python操作Excel (openpyxl模块)

openpyxl：不需要 Excel 软件，仅支持 .xlsx 格式文件并进行增删改查

安装
openpyxl是一个非标准库，因此需要自行安装，安装过程并不困难，Windows/Mac用户均可以在命令行(CMD)/终端(Terminal)中使用pip安装。

pip install openpyxl

前置知识

Excel表格结构介绍

读取Excel

1.载入Excel

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
print(workbook.sheetnames)

备注：load_workbook只能打开已经存在的Excel，不能创建新的工作簿

2.根据名称获取工作表（sheet）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']

如果只有一张工作表也可以用：sheet = workbook.active

3.获取表格内容所在的范围

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
print(sheet.dimensions)

4.获取某个单元格的具体内容

方法一：指定坐标

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
cell = sheet['B2']
print(cell.value)

方法二：指定行列数

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
cell = sheet.cell(row=2, column=2)    # 获取2行2列内容
print(cell.value)

5.获取某个单元格的行、列、坐标

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
# cell = sheet.cell(row=2, column=2)
cell = sheet['B2']
print(cell.row, cell.column, cell.coordinate)

6.获取多个格子的值

指定坐标范围的值

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
alls = sheet['A1:B3']
for all in alls:
    for cell in all:
        print(cell.value)

指定列的值

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
columns = sheet['A:C']
for column in columns:
    for cell in column:
        print(cell.value)

指定行的值

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
rows = sheet['2:4']
for row in rows:
    for cell in row:
        print(cell.value)

指定范围的值

方法一：按行获取

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
for row in sheet.iter_rows(min_row=2,max_row=5,min_col=1,max_col=3):
    for cell in row:
        print(cell.value)

方法二：按列获取

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
for row in sheet.iter_cols(min_row=2,max_row=5,min_col=1,max_col=3):
    for cell in row:
        print(cell.value)

7.读取所有的行

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
for cell in sheet.rows:
    print(cell)

Excel写入

1.保存Excel

workbook.save(filename='test.xlsx')

• 如果读取和写入Excel的路径相同则为对原文件进行修改，
• 如果读取和写入Excel的路径不同则为保存成新的文件

2.写入单元格

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
cell = sheet['F1']
cell.value = 'hobby'
workbook.save(filename='test.xlsx')

3.追加一行或多行数据

在已有数据后面追加sheet.append(列表)

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
data1 = ['大强', 33, '深圳']
sheet.append(data1)
data2 = ['二强', 32, '广州']
sheet.append(data2)
workbook.save(filename='test.xlsx')

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
data = [['大强', 33, '深圳'],
         ['二强', 32, '广州'],
         ['三强', 31, '潮汕']]
for i in data:
    sheet.append(i)
workbook.save(filename='test.xlsx')

4.将公式写入单元格并保存

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet['B12'] = '=AVERAGE(B2:B11)'      # 求平均数
workbook.save(filename='test.xlsx')

5.插入空白行

插入一行（在idx行上面插入一行）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.insert_rows(idx=2)
workbook.save(filename='test.xlsx')

插入多行（在idx行上面插入多行）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.insert_rows(idx=2,amount=3)    # 插入三行
workbook.save(filename='test.xlsx')

6.插入空白列

插入一列（在idx列左列插入一列）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.insert_cols(idx=2)
workbook.save(filename='test.xlsx')

插入多列（在idx列左列插入多列）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.insert_cols(idx=2,amount=2)
workbook.save(filename='test.xlsx')

7.删除多行

从idx行开始删除多行（包括idx行以及下面的行）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.delete_rows(idx=2,amount=3)
workbook.save(filename='test.xlsx')

8.删除多列

从idx列开始删除多列（包括idx列以及右边的列）

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.delete_cols(idx=4,amount=2)
workbook.save(filename='test.xlsx')

9. 移动范围数据

from openpyxl import load_workbook
workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']
sheet.move_range('D1:F2', rows=5, cols=-3)   # 移到5+1行，向左移三列 （数字为正为向下或者向右，数字为负为向上或者向右）
workbook.save(filename='test.xlsx')

10.创建新的Execl表格

from openpyxl import Workbook
workbook = Workbook()
ws = workbook.active
ws.title = u'新表格title'
workbook.save('新表格.xlsx')

Excel样式调整

1. 设置字体样式

Font(name字体名称, size大小, bold粗体, italic斜体, color颜色)

from openpyxl import load_workbook
from openpyxl.styles import Font

workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']

cell = sheet['A1']
font = Font(name="微软雅黑", size=12, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save(filename='这是一个新表格.xlsx')

2. 设置对齐样式

Alignment(horizontal水平对齐, vertical垂直对齐, text_rotation字体倾斜度, wrap_text自动换行)

from openpyxl import load_workbook
from openpyxl.styles import Alignment

workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']

cell = sheet['A2']
alignment = Alignment(horizontal='center', vertical='center', text_rotation=45, wrap_text=True)
cell.alignment = alignment
workbook.save(filename='这是一个新表格.xlsx')

• 水平对齐参数：distributed, justify, center, left, fill, centerContinuous, right, general
• 垂直对齐参数：bottom, distributed, justify, center, top

3. 设置边框样式

Side(style边线样式， color边线颜色)
Border(左 右 上 下 边线)

from openpyxl import load_workbook
from openpyxl.styles import Side, Border

workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']

cell = sheet['A2']
side = Side(style='thin', color='FF0000')
border = Border(left=side, right=side, top=side, bottom=side)
cell.border = border
workbook.save(filename='这是一个新表格.xlsx')

边线样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

4. 设置单元格填充样式

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, GradientFill

workbook = load_workbook(filename='test.xlsx')
sheet = workbook['openpyxl测试']

cell1 = sheet['A2']
pattern_fill = PatternFill(fill_type='solid', fgColor='99CCFF')
cell1.fill = pattern_fill

cell2 = sheet['A3']
gradient_fill = GradientFill(stop=('FFFFFF', '99CCFF', '000000'))
cell2.fill = gradient_fill

workbook.save(filename='这是一个新表格.xlsx')

5. 设置行高和列宽

sheet.row_dimensions[1].height = 50 
sheet.column_dimensions['C'].width = 20 

6. 单元格合并与取消

合并

sheet.merge_cells('A1:B2')

sheet.merge_cells(start_row=1, start_column=3,
                  end_row=2, end_column=4)

取消合并

sheet.unmerge_cells('A1:B2')

sheet.unmerge_cells(start_row=1, start_column=3, 
                    end_row=2, end_column=4)