python-openpyxl读写表格

于 2023-12-30 07:42:18 发布
阅读量1.1k 收藏 16
点赞数 23
分类专栏： python常用库学习 Python GUI 文章标签： python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_40597070/article/details/135301674
版权
本文详细介绍了如何使用openpyxl库在Python中读写.xlsx文件，包括创建和加载工作簿、操作工作表、选择和赋值单元格、处理行列、合并单元格、设置样式以及保存工作簿等关键步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

文章目录

前言

openpyxl只能处理 .xlsx 格适的表格

引入库

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

指定工作表

ws = wb['000']

已存在的全部工作簿

wb.sheetnames
# [‘000’, ‘111’]

选择单个单元格

ws['A1']
ws.cell(1,1)    # 先行后列，都是索引下标
# <Cell ‘000’.A1>

单元格属性

cell = ws['A1']#单元格属性
cell.col_idx# 单元格列索引
cell.column#单元格行索引
cell.column_letter#单元格列名
cell.coordinate#单元格的坐标-->‘A1’
cell.data_type # 单元格字符类型，默认是n 数值；s 字符串；d 日期时间
cell.encoding# 单元格编码格式，默认 utf-8
cell.has_style   # 默认样式是 Normal，如果是默认样式，返回False
cell.style# 单元格样式
cell.style_id#单元格样式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.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.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['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 = 30


ws.cell(2,2).value#‘陈桂荣’

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

  • 增加一行

    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)
"""

按行

  • ws.iter_rows() 将一行单元格作为元组单元组成的生成器，与ws.values的区别是，rows返回的是由单元格组成的元组，values是由值组成的
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’.A5>, <Cell ‘000’.B5>, <Cell ‘000’.C5>)
(<Cell ‘000’.A6>, <Cell ‘000’.B6>, <Cell ‘000’.C6>)
(<Cell ‘000’.A7>, <Cell ‘000’.B7>, <Cell ‘000’.C7>)
(<Cell ‘000’.A8>, <Cell ‘000’.B8>, <Cell ‘000’.C8>)
(<Cell ‘000’.A9>, <Cell ‘000’.B9>, <Cell ‘000’.C9>)
(<Cell ‘000’.A10>, <Cell ‘000’.B10>, <Cell ‘000’.C10>)
"""

  • ws.rows 全部单元格

    rows 和 iter_row()的区别在于，iter_row()可以指定区域，rows是全部单元格

import random
for i in ws.rows:
    for j in i:
        print(j,j.value)

按列

  • ws.columns 所有列

    for i in ws.columns:
    print(i)

    
"""
(<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’.A12>)
(<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>, <Cell ‘000’.B12>)
(<Cell ‘000’.C1>, <Cell ‘000’.C2>, <Cell ‘000’.C3>, <Cell ‘000’.C4>, <Cell ‘000’.C5>, <Cell ‘000’.C6>, <Cell ‘000’.C7>, <Cell ‘000’.C8>, <Cell ‘000’.C9>, <Cell ‘000’.C10>, <Cell ‘000’.C11>, <Cell ‘000’.C12>)
(<Cell ‘000’.D1>, <Cell ‘000’.D2>, <Cell ‘000’.D3>, <Cell ‘000’.D4>, <Cell ‘000’.D5>, <Cell ‘000’.D6>, <Cell ‘000’.D7>, <Cell ‘000’.D8>, <Cell ‘000’.D9>, <Cell ‘000’.D10>, <Cell ‘000’.D11>, <Cell ‘000’.D12>)
"""

  • ws.iter_cols(min_col=1,max_col=3,min_row=1,max_row=10) 可指定列

最大行、最大列

ws.max_column
ws.max_row

删除行或者列

注意，删除行或者列后，后面的行或者列会自动往前填充，也就是说，删除第一列，原来的第二列就会变成第一列

ws.delete_rows(3)
ws.delete_cols(1)

合并单元格

合并后的单元格，只会保留最上角的值，其他单元格的值全部为空(None)

  • 合并单元格
    ws.merge_cells("A1:B1")
ws.merge_cells(start_column=3,end_column=6,start_row=2,end_row=3)

  • 已存在的合并单元格

    ws.merged_cells

  • 已存在的合并单元格列表

    ws.merged_cell_ranges

过滤和排序

openpyxl可以添加过滤和排序，但是并不会起作用

# 过滤区域
ws.auto_filter.ref = "A:B"
# 给指定列添加过滤条件
ws.auto_filter.add_filter_column(0, ['ASC','DWS'])
ws.auto_filter.add_sort_condition("B2:B15")

样式设置

颜色

Color(index=0) # 根据索引进行填充
# 
Color(rgb='00000000') # 根据rgb值进行填充
# index 
COLOR_INDEX = (
    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4
    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9
    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14
    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19
    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24
    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29
    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34
    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39
    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44
    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49
    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54
    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59
    '00993300', '00993366', '00333399', '00333333',  #60-63
)
BLACK = COLOR_INDEX[0]
WHITE = COLOR_INDEX[1]
RED = COLOR_INDEX[2]
DARKRED = COLOR_INDEX[8]
BLUE = COLOR_INDEX[4]
DARKBLUE = COLOR_INDEX[12]
GREEN = COLOR_INDEX[3]
DARKGREEN = COLOR_INDEX[9]
YELLOW = COLOR_INDEX[5]
DARKYELLOW = COLOR_INDEX[19]

字体

ws.cell(5,3).value='哈哈哈'
ws.cell(5,3).font = Font(name='仿宋',size=12,color=Color(index=0),b=True,i=True)

# size   sz  字体大小
# b bold  是否粗体
# i italic  是否斜体
# name family  字体样式

边框

Side(style='thin',color=Color(index=0))

# style可选项
style = ('dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin')
#  'medium' 中粗
#  'thin'  细
#  'thick'  粗
#  'dashed'  虚线
#  'dotted'  点线


Border(left=Side(),
      right=Side(),
      top=Side(),
      bottom=Side())


<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


ws.cell(3,3).border = Border()

填充

PatternFill(patternType='solid',fgColor=Color(), bgColor=Color())
# fgColor   前景色
# bgColor   后景色
# 参数可选项
patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 
               'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 
               'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 
               'lightHorizontal', 'darkTrellis', 'darkVertical'}


ws.cell(3,3).fill = PatternFill()

对齐

Alignment(horizontal='fill',vertical='center')

# 参数可选项
horizontal = {'fill', 'distributed', 'centerContinuous', 'right',
              'justify', 'center', 'left', 'general'}

vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}


ws.cell(3,3).alignment= Alignment()

数字显示样式

设置工作薄自动识别单元格样式

wb.guess_types = True

当设置为自动识别后，单元格赋值python类型即可，会自动识别为Excel的数字类型

ws['A11'] = '2020-09-22'
ws['A11'].value # ‘2020-09-22’
ws['A11'].data_type # ‘s’
ws['A11'].number_format # 'General'

内建样式

ws['B5'] = 50000
ws['B5'].number_format = '#,##0'
ws['B5'].data_type
# 'n'

自定义样式

ws['B6'].number_format = 'yyyy-mm-dd'
ws['B6'] = datetime.datetime.now()
ws['B6'].value
datetime.datetime(2020, 11, 8, 9, 25, 37, 722481)
ws['B6'].data_type
# 'd'

内建数字样式

BUILTIN_FORMATS = {
    0: 'General',    # 默认样式
    1: '0',
    2: '0.00',
    3: '#,##0',
    4: '#,##0.00',
    5: '"$"#,##0_);("$"#,##0)',
    6: '"$"#,##0_);[Red]("$"#,##0)',
    7: '"$"#,##0.00_);("$"#,##0.00)',
    8: '"$"#,##0.00_);[Red]("$"#,##0.00)',
    9: '0%',
    10: '0.00%',
    11: '0.00E+00',
    12: '# ?/?',
    13: '# ??/??',
    14: 'mm-dd-yy',
    15: 'd-mmm-yy',
    16: 'd-mmm',
    17: 'mmm-yy',
    18: 'h:mm AM/PM',
    19: 'h:mm:ss AM/PM',
    20: 'h:mm',
    21: 'h:mm:ss',
    22: 'm/d/yy h:mm',

    37: '#,##0_);(#,##0)',
    38: '#,##0_);[Red](#,##0)',
    39: '#,##0.00_);(#,##0.00)',
    40: '#,##0.00_);[Red](#,##0.00)',

    41: r'_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)',
    42: r'_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)',
    43: r'_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)',

    44: r'_("$"* #,##0.00_)_("$"* \(#,##0.00\)_("$"* "-"??_)_(@_)',
    45: 'mm:ss',
    46: '[h]:mm:ss',
    47: 'mmss.0',
    48: '##0.0E+0',
    49: '@', }

链接

  1. Excel的链接公式

    ws['C5'].value = '=HYPERLINK("#Sheet!B2","名称")'

  2. hyperlink参数

    from openpyxl.worksheet.hyperlink import Hyperlink
ws['C6'].hyperlink = Hyperlink(ref='',location='Sheet!H5',target='')
ws['C6'].value = '这是链接'

target : 目标文件

location ：目标单元格 工作表名 + ！ + 单元格名

如果需要显示蓝色字体和下划线，需要设置字体

link = NamedStyle(name='link',font=Font(color=colors.BLUE,underline='single'))
ws['C6'].style = link

多个样式设置整合

bbb = NamedStyle(name='bbb',
           font=Font(color=colors.BLUE),
           fill=PatternFill(fgColor=Color(index=0)),
           border=Border(left=Side(style='medium',color=Color(theme=6,tint=0.6)),
                         right=Side(style='medium',color='FFBB00'),
                         top=Side(),
                         bottom=Side()),
           alignment=Alignment(horizontal='center',vertical='top')
          )

可以将样式添加到工作薄，可以直接用字符串的形式赋值样式

wb.add_named_style(bbb)
ws['A5'].style=bbb
ws['A5'].value='自定义样式'
ws['A10'].style = 'bbb'
ws['A10'].style_id#3
ws['A10'].has_style#True

行高 列宽

row =ws.row_dimensions[1]
row.height = 15
col = ws.column_dimensions['E']
col.width = 10

保存工作薄

wb.save('./000.xlsx')
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

士别三日，当挖目相待

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值