openpyxl超详细笔记

最新推荐文章于 2024-08-22 20:39:39 发布

不正经Python学习

最新推荐文章于 2024-08-22 20:39:39 发布

阅读量10w+

收藏 2.3k

点赞数 385

分类专栏： Python&Go自动化办公文章标签： python

本文链接：https://blog.csdn.net/weixin_41546513/article/details/109555832

版权

本文详细介绍了如何使用openpyxl库进行Excel文件的读写操作，包括创建和加载工作簿、操作单元格、设置样式、数据转换、删除行列、合并单元格等功能，并提供了丰富的代码示例。

摘要由CSDN通过智能技术生成

文章目录

总结

前言

本文的内容都是用jupyter notebook执行的。

以下是本篇文章正文内容

引入库

from openpyxl import Workbook,load_workbook
from openpyxl.styles import *


import warnings
warnings.filterwarnings('ignore')

基本操作

创建新的工作薄

wb1 = Workbook()

加载已存在的工作簿

wb = load_workbook('./000.xlsx')
# openpyxl只能处理 .xlsx 合适的表格

创建新的工作表

ws1 = wb.create_sheet('111')

当前工作表

ws2 = wb.active
ws2.title

‘000’

指定工作表

ws = wb['000']

已存在的全部工作簿

wb.sheetnames

[‘000’, ‘111’]

选择单个单元格

ws['A1']

ws.cell(1,1)    # 先行后列，都是索引下标

单元格属性

cell = ws['A1']

1 .单元格列索引

cell.col_idx

cell.column

单元格行索引

cell.row

单元格列名

cell.column_letter

‘A’

单元格的坐标

cell.coordinate

‘A1’

单元格数字类型

默认是n 数值

s 字符串

d 日期时间

cell.data_type

‘n’

单元格编码格式，默认 utf-8

cell.encoding

‘utf-8’

是否有样式

cell.has_style   # 默认样式是 Normal，如果是默认样式，返回False

False

单元格样式

cell.style

‘Normal’

单元格样式id

cell.style_id

单元格的样式属性

属性样式会在后面设置中详细演示，此处只做查询

cell.font

<openpyxl.styles.fonts.Font object>
Parameters:
name=‘Calibri’, charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type=‘theme’, extend=None, sz=11.0, u=None, vertAlign=None, scheme=‘minor’

cell.alignment

<openpyxl.styles.alignment.Alignment object>
Parameters:
horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0, justifyLastLine=None, readingOrder=0.0

cell.border

<openpyxl.styles.borders.Border object>
Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, right=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, top=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, bottom=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, diagonal=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, vertical=None, horizontal=None

cell.fill

<openpyxl.styles.fills.PatternFill object>
Parameters:
patternType=None, fgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb=‘00000000’, indexed=None, auto=None, theme=None, tint=0.0, type=‘rgb’, bgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb=‘00000000’, indexed=None, auto=None, theme=None, tint=0.0, type=‘rgb’

cell.number_format

‘General’

cell.hyperlink

单元格的值

ws['A1'].value

选择单元格

选择一行或者一列

一列字符串

ws['A']

(<Cell ‘000’.A1>,
<Cell ‘000’.A2>,
<Cell ‘000’.A3>,
<Cell ‘000’.A4>,
<Cell ‘000’.A5>,
<Cell ‘000’.A6>,
<Cell ‘000’.A7>,
<Cell ‘000’.A8>,
<Cell ‘000’.A9>,
<Cell ‘000’.A10>,
<Cell ‘000’.A11>)

一行，数字

ws[1]

(<Cell ‘000’.A1>, <Cell ‘000’.B1>, <Cell ‘000’.C1>, <Cell ‘000’.D1>)

多行

ws['A:B']

((<Cell ‘000’.A1>,
<Cell ‘000’.A2>,
<Cell ‘000’.A3>,
<Cell ‘000’.A4>,
<Cell ‘000’.A5>,
<Cell ‘000’.A6>,
<Cell ‘000’.A7>,
<Cell ‘000’.A8>,
<Cell ‘000’.A9>,
<Cell ‘000’.A10>,
<Cell ‘000’.A11>),
(<Cell ‘000’.B1>,
<Cell ‘000’.B2>,
<Cell ‘000’.B3>,
<Cell ‘000’.B4>,
<Cell ‘000’.B5>,
<Cell ‘000’.B6>,
<Cell ‘000’.B7>,
<Cell ‘000’.B8>,
<Cell ‘000’.B9>,
<Cell ‘000’.B10>,
<Cell ‘000’.B11>))

多行

ws[5:10]

((<Cell ‘000’.A5>, <Cell ‘000’.B5>, <Cell ‘000’.C5>, <Cell ‘000’.D5>),
(<Cell ‘000’.A6>, <Cell ‘000’.B6>, <Cell ‘000’.C6>, <Cell ‘000’.D6>),
(<Cell ‘000’.A7>, <Cell ‘000’.B7>, <Cell ‘000’.C7>, <Cell ‘000’.D7>),
(<Cell ‘000’.A8>, <Cell ‘000’.B8>, <Cell ‘000’.C8>, <Cell ‘000’.D8>),
(<Cell ‘000’.A9>, <Cell ‘000’.B9>, <Cell ‘000’.C9>, <Cell ‘000’.D9>),
(<Cell ‘000’.A10>, <Cell ‘000’.B10>, <Cell ‘000’.C10>, <Cell ‘000’.D10>))

指定区域单元格

ws['A3:B9']

((<Cell ‘000’.A3>, <Cell ‘000’.B3>),
(<Cell ‘000’.A4>, <Cell ‘000’.B4>),
(<Cell ‘000’.A5>, <Cell ‘000’.B5>),
(<Cell ‘000’.A6>, <Cell ‘000’.B6>),
(<Cell ‘000’.A7>, <Cell ‘000’.B7>),
(<Cell ‘000’.A8>, <Cell ‘000’.B8>),
(<Cell ‘000’.A9>, <Cell ‘000’.B9>))

单元格赋值

ws['A1'] = 20

ws.cell(2,2).value

‘陈桂荣’

当使用cell() 时，只能给value属性赋值

# ws.cell(2,2) = 20
# 会报错

ws.cell(2,2).value = 30

增加一行

ws.append([1,2,3])

单元格遍历

ws.values 返回的是生成器，是将一行数据作为一个元组单元组成的，是由值组成的

ws.values 获取的内容是从 “A1” 到 “最大行最大列”

ws.values

for i in ws.values:
    print(i)

(20, ‘NAME’, ‘DATE_TIME’, ‘PAY’)
(0, 30, datetime.datetime(1972, 2, 23, 3, 10, 2), 8803)
(1, ‘黄瑞’, datetime.datetime(1977, 11, 29, 4, 49, 16), 5951)
(2, ‘李阳’, datetime.datetime(1982, 8, 30, 18, 12, 46), 7418)
(3, ‘石淑英’, datetime.datetime(2016, 4, 18, 11, 24, 17), 737)
(4, ‘陈红霞’, datetime.datetime(2011, 12, 12, 3, 12, 47, 1), 3555)
(5, ‘廖健’, datetime.datetime(1989, 9, 25, 20, 9, 45, 1), 2649)
(6, ‘韩雪梅’, datetime.datetime(2002, 1, 2, 8, 0, 51), 7344)
(7, ‘赵丽丽’, datetime.datetime(2018, 7, 1, 19, 35, 24), 8735)
(8, ‘侯建华’, datetime.datetime(1971, 8, 1, 16, 59, 1), 6148)
(9, ‘谭桂花’, datetime.datetime(2000, 4, 7, 5, 2, 38), 8900)
(1, 2, 3, None)

for i in ws.iter_rows(min_col=1,max_col=3,min_row=1,max_row=10):
    print(i)

(<Cell ‘000’.A1>, <Cell ‘000’.B1>, <Cell ‘000’.C1>)
(<Cell ‘000’.A2>, <Cell ‘000’.B2>, <Cell ‘000’.C2>)
(<Cell ‘000’.A3>, <Cell ‘000’.B3>, <Cell ‘000’.C3>)
(<Cell ‘000’.A4>, <Cell ‘000’.B4>, <Cell ‘000’.C4>)
(<Cell ‘000’