openpyxl

引入库

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']

<Cell ‘000’.A1>

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

<Cell ‘000’.A1>

单元格属性

cell = ws['A1']

1 .单元格列索引

cell.col_idx

cell.column

单元格行索引

cell.row

单元格列名

cell.column_letter

‘A’

单元格的坐标

cell.coordinate
1

‘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]

多行

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
# 会报错
12

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

增加一行

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

单元格遍历

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

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

ws.values

<generator object values at 0x00000297EAB07F68>

for i in ws.values:
    print(i)
12

(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)
12

(<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.iter_rows()
1

<generator object Worksheet._cells_by_row at 0x00000297EAB623B8>

ws.rows
1

<generator object Worksheet._cells_by_row at 0x00000297EAB62518>

是将一行单元格作为元组单元组成的生成器，与ws.values的区别是，rows返回的是由单元格组成的元组，values是由值组成的

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

<Cell ‘000’.A1> 20
 <Cell ‘000’.B1> NAME
 <Cell ‘000’.C1> DATE_TIME
 <Cell ‘000’.D1> PAY
 <Cell ‘000’.A2> 0
 <Cell ‘000’.B2> 30
 <Cell ‘000’.C2> 1972-02-23 03:10:02
 <Cell ‘000’.D2> 8803
 <Cell ‘000’.A3> 1
 <Cell ‘000’.B3> 黄瑞
 <Cell ‘000’.C3> 1977-11-29 04:49:16
 <Cell ‘000’.D3> 5951
 <Cell ‘000’.A4> 2
 <Cell ‘000’.B4> 李阳
 <Cell ‘000’.C4> 1982-08-30 18:12:46
 <Cell ‘000’.D4> 7418
 <Cell ‘000’.A5> 3
 <Cell ‘000’.B5> 石淑英
 <Cell ‘000’.C5> 2016-04-18 11:24:17
 <Cell ‘000’.D5> 737
 <Cell ‘000’.A6> 4
 <Cell ‘000’.B6> 陈红霞
 <Cell ‘000’.C6> 2011-12-12 03:12:47.000001
 <Cell ‘000’.D6> 3555
 <Cell ‘000’.A7> 5
 <Cell ‘000’.B7> 廖健
 <Cell ‘000’.C7> 1989-09-25 20:09:45.000001
 <Cell ‘000’.D7> 2649
 <Cell ‘000’.A8> 6
 <Cell ‘000’.B8> 韩雪梅
 <Cell ‘000’.C8> 2002-01-02 08:00:51
 <Cell ‘000’.D8> 7344
 <Cell ‘000’.A9> 7
 <Cell ‘000’.B9> 赵丽丽
 <Cell ‘000’.C9> 2018-07-01 19:35:24
 <Cell ‘000’.D9> 8735
 <Cell ‘000’.A10> 8
 <Cell ‘000’.B10> 侯建华
 <Cell ‘000’.C10> 1971-08-01 16:59:01
 <Cell ‘000’.D10> 6148
 <Cell ‘000’.A11> 9
 <Cell ‘000’.B11> 谭桂花
 <Cell ‘000’.C11> 2000-04-07 05:02:38
 <Cell ‘000’.D11> 8900
 <Cell ‘000’.A12> 1
 <Cell ‘000’.B12> 2
 <Cell ‘000’.C12> 3
 <Cell ‘000’.D12> None

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

columns 、iter_col() 是按列

ws.columns
1

<generator object Worksheet._cells_by_col at 0x00000297EAB627D8>

for i in ws.columns:
    print(i)
12

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

<generator object Worksheet._cells_by_col at 0x00000297EAB62A40>

最大行、最大列

ws.max_column

ws.max_row

删除行或者列

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

ws.cell(1,2).value
1

‘NAME’

ws.delete_cols(1)
1

ws.cell(1,1).value
1

‘NAME’

ws.delete_rows(3)
1

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

<Cell ‘000’.A1> NAME
 <Cell ‘000’.B1> DATE_TIME
 <Cell ‘000’.C1> PAY
 <Cell ‘000’.A2> 30
 <Cell ‘000’.B2> 1972-02-23 03:10:02
 <Cell ‘000’.C2> 8803
 <Cell ‘000’.A3> 李阳
 <Cell ‘000’.B3> 1982-08-30 18:12:46
 <Cell ‘000’.C3> 7418
 <Cell ‘000’.A4> 石淑英
 <Cell ‘000’.B4> 2016-04-18 11:24:17
 <Cell ‘000’.C4> 737
 <Cell ‘000’.A5> 陈红霞
 <Cell ‘000’.B5> 2011-12-12 03:12:47.000001
 <Cell ‘000’.C5> 3555
 <Cell ‘000’.A6> 廖健
 <Cell ‘000’.B6> 1989-09-25 20:09:45.000001
 <Cell ‘000’.C6> 2649
 <Cell ‘000’.A7> 韩雪梅
 <Cell ‘000’.B7> 2002-01-02 08:00:51
 <Cell ‘000’.C7> 7344
 <Cell ‘000’.A8> 赵丽丽
 <Cell ‘000’.B8> 2018-07-01 19:35:24
 <Cell ‘000’.C8> 8735
 <Cell ‘000’.A9> 侯建华
 <Cell ‘000’.B9> 1971-08-01 16:59:01
 <Cell ‘000’.C9> 6148
 <Cell ‘000’.A10> 谭桂花
 <Cell ‘000’.B10> 2000-04-07 05:02:38
 <Cell ‘000’.C10> 8900
 <Cell ‘000’.A11> 2
 <Cell ‘000’.B11> 3
 <Cell ‘000’.C11> None

转pandas

import pandas as pd
df = pd.DataFrame(ws.values)

df
1

pandas 转ws

for i in df.values:
    ws.append(i.tolist())
12

for i in ws.rows:
    for j in i:
        print(j,j.value,end=',')
    print('')
1234

<Cell ‘000’.A1> NAME,<Cell ‘000’.B1> DATE_TIME,<Cell ‘000’.C1> PAY,
 <Cell ‘000’.A2> 30,<Cell ‘000’.B2> 1972-02-23 03:10:02,<Cell ‘000’.C2> 8803,
 <Cell ‘000’.A3> 李阳,<Cell ‘000’.B3> 1982-08-30 18:12:46,<Cell ‘000’.C3> 7418,
 <Cell ‘000’.A4> 石淑英,<Cell ‘000’.B4> 2016-04-18 11:24:17,<Cell ‘000’.C4> 737,
 <Cell ‘000’.A5> 陈红霞,<Cell ‘000’.B5> 2011-12-12 03:12:47.000001,<Cell ‘000’.C5> 3555,
 <Cell ‘000’.A6> 廖健,<Cell ‘000’.B6> 1989-09-25 20:09:45.000001,<Cell ‘000’.C6> 2649,
 <Cell ‘000’.A7> 韩雪梅,<Cell ‘000’.B7> 2002-01-02 08:00:51,<Cell ‘000’.C7> 7344,
 <Cell ‘000’.A8> 赵丽丽,<Cell ‘000’.B8> 2018-07-01 19:35:24,<Cell ‘000’.C8> 8735,
 <Cell ‘000’.A9> 侯建华,<Cell ‘000’.B9> 1971-08-01 16:59:01,<Cell ‘000’.C9> 6148,
 <Cell ‘000’.A10> 谭桂花,<Cell ‘000’.B10> 2000-04-07 05:02:38,<Cell ‘000’.C10> 8900,
 <Cell ‘000’.A11> 2,<Cell ‘000’.B11> 3,<Cell ‘000’.C11> None,
 <Cell ‘000’.A12> NAME,<Cell ‘000’.B12> DATE_TIME,<Cell ‘000’.C12> PAY,
 <Cell ‘000’.A13> 30,<Cell ‘000’.B13> 1972-02-23 03:10:02,<Cell ‘000’.C13> 8803,
 <Cell ‘000’.A14> 李阳,<Cell ‘000’.B14> 1982-08-30 18:12:46,<Cell ‘000’.C14> 7418,
 <Cell ‘000’.A15> 石淑英,<Cell ‘000’.B15> 2016-04-18 11:24:17,<Cell ‘000’.C15> 737,
 <Cell ‘000’.A16> 陈红霞,<Cell ‘000’.B16> 2011-12-12 03:12:47.000001,<Cell ‘000’.C16> 3555,
 <Cell ‘000’.A17> 廖健,<Cell ‘000’.B17> 1989-09-25 20:09:45.000001,<Cell ‘000’.C17> 2649,
 <Cell ‘000’.A18> 韩雪梅,<Cell ‘000’.B18> 2002-01-02 08:00:51,<Cell ‘000’.C18> 7344,
 <Cell ‘000’.A19> 赵丽丽,<Cell ‘000’.B19> 2018-07-01 19:35:24,<Cell ‘000’.C19> 8735,
 <Cell ‘000’.A20> 侯建华,<Cell ‘000’.B20> 1971-08-01 16:59:01,<Cell ‘000’.C20> 6148,
 <Cell ‘000’.A21> 谭桂花,<Cell ‘000’.B21> 2000-04-07 05:02:38,<Cell ‘000’.C21> 8900,
 <Cell ‘000’.A22> 2,<Cell ‘000’.B22> 3,<Cell ‘000’.C22> None,

合并单元格

ws.merge_cells("A1:B1")

ws.merge_cells(start_column=3,end_column=6,start_row=2,end_row=3)

已存在的合并单元格

ws.merged_cells

<MultiCellRange [A1:B1 C2:F3]>

已存在的合并单元格列表

ws.merged_cell_ranges

[< CellRange A1:B1>, < CellRange C2:F3>]

ws['A1'].value 1

‘NAME’

ws['B1'].value

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

过滤和排序

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

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

样式设置

颜色

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]
1234567891011121314151617181920212223242526272829

字体

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  字体样式
1234567

边框

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'  点线
1234567891011

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

<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
12345678910111213

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

填充

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'}
12345678

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

对齐

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

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

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

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

数字显示样式

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

wb.guess_types = True
1

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

ws['A11'] = '2018-09-22'
1

ws['A11'].value

‘2018-09-22’

ws['A11'].data_type

‘s’

ws['A11'].number_format

'General'

import datetime

ws['B11'] = datetime.datetime.now()
123

ws['B11'].value

datetime.datetime(2018, 11, 8, 9, 25, 37, 657654)

ws['B11'].number_format

'yyyy-mm-dd h:mm:ss'

ws['B11'].data_type

'd'

也可以使用内建样式

ws['B5'] = 50000
1

ws['B5'].number_format = '#,##0'
1

ws['B5'].data_type
1

'n'
1

也可以自定义样式

ws['B6'].number_format = 'yyyy-mm-dd'
1

ws['B6'] = datetime.datetime.now()
1

ws['B6'].value
1

datetime.datetime(2018, 11, 8, 9, 25, 37, 722481)
1

ws['B6'].data_type
1

'd'
1

内建数字样式

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: '@', }
12345678910111213141516171819202122232425262728293031323334353637383940

链接

Excel的链接公式

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

hyperlink参数

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

行高列宽

row =ws.row_dimensions[1]

row.height = 15

col = ws.column_dimensions['E']

col.width = 10

保存工作薄

wb.save('./000.xlsx')

openpyxl

# openpyxl

# 引入库

# 基本操作

# 创建新的工作薄

# 加载已存在的工作簿

# 创建新的工作表

# 当前工作表

# 已存在的全部工作簿

# 选择单个单元格

# 单元格属性

# 单元格的样式属性

# 单元格的值

# 选择单元格

# 单元格赋值

# 单元格遍历

# 删除行或者列

# 转pandas

# 合并单元格

# 过滤和排序

# 样式设置

# 颜色

# 字体

# 边框

# 填充

# 对齐

# 数字显示样式

# 链接

# 保存工作薄

openpyxl

引入库

基本操作

创建新的工作薄

加载已存在的工作簿

创建新的工作表

当前工作表

已存在的全部工作簿

选择单个单元格

单元格属性

单元格的样式属性

单元格的值

选择单元格

单元格赋值

单元格遍历

删除行或者列

转pandas

合并单元格

过滤和排序

样式设置

颜色

字体

边框

填充

对齐

数字显示样式

链接

保存工作薄