linux,  python

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)

2条评论

留言

您的电子邮箱地址不会被公开。 必填项已用*标注