openpyxl库中文文档-1

openpyxl表格读取操作EXcel_

1.打开一个已存在的表格，获取表格文件内所有sheet名称

import openpyxl
from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格
filename = "xxx.xlsx"
work_book = load_workbook(filename=filename)
print(work_book.sheetnames)  #打印表格中所有的sheet名称

2.打开一个sheet表并获取sheet表大小

sheet = work_book['(sample)sam_tianchi_mum_baby_tr']   # 方式一  指定sheet名称
sheet = work_book.active                               # 方式二  使用active属性，表示当前活跃的或者唯一的sheet
print(sheet.dimensions)  # 打印sheet尺寸大小  A1:G1002  从A1到G1，共1002行 

3.获取指定格子的内容

cell = sheet['A1']                  # 方式一 通过cell坐标，A1表示格子的坐标
cell = sheet.cell(row=1,column=1)   # 方式二 通过指定行和列
print(cell.value, cell.row, cell.column, cell.coordinate) # 打印格子 内容 行数 列数 坐标

4. 获取多个格子的内容

cells = sheet['A1:A5'] 
print(cells)
for cell in cells:
        print(cell[0].value)
        print(cell[0].row)
        print(cell[0].column)

5、获取整列、整行、多列、多行格子

cells = sheet['A']      # 获取指定列所有格子，如A列所有格子 
cells = sheet['A:C']    # 获取指A列到G列所有格子
cells = sheet[1]        # 获取第一行所有格子
cells = sheet[1:5]     # 获取第一行到第五行所有格子

6.指定行和列的范围，按行获取

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)
for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):
    print(row) # A2,B2  A3,B3

7.指定行和列的范围，按列获取

# 起始行数 2 结束行数 3 起始列 1(A) 结束列 2(B)
for col in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):
    print(col) # A2,A3  B2,B3

8.获取所有的行

for row in sheet.rows:
    print(row)

8.1获取最大行数

import openpyxl
from openpyxl import load_workbook  #load_workbook 只能打开已经存在的表格，不能创建新表格
sPath = 'D:\\代码仓库\\日盛\\上海rpa-派\\客户共享盘模拟\\客户共享盘模拟\\6工作簿1-初加工表\\初加工表.xlsx'
filename = sPath
work_book = load_workbook(filename=filename)
sheet = work_book.active
from openpyxl.utils import FORMULAE
# print(FORMULAE)  # 打印所有支持直接赋值的公式
rows = sheet.max_row#3346 获取最大行数
print(rows)

9.获取所有的列

for col in sheet.columns:
    print(col)　

10、获取工作表中最大行，最小行，最大列 最小列

工作表最大行：worksheet.max_row，获取存在数据的最大一行索引
工作表最小行：worksheet.min_row，获取存在数据的最小一行索引
工作表最大列：worksheet.max_column，获取存在数据的最大一列索引
工作表最小列：worksheet.min_column，获取存在数据的最小一列索引

11、复制，移动，删除表

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.create_sheet("my_sheet1", 1)
ws2 = wb.create_sheet("my_sheet2", 2)

ws1.title = "my_sheet111"
print(ws1.title)
print(wb.sheetnames)  # 输出：['Sheet', 'my_sheet111', 'my_sheet2']

# 移动位置
wb.move_sheet("my_sheet111", -1)   # 向前移动一个位置，正数往后，负数往前
print(wb.sheetnames)  # 输出：['my_sheet111', 'Sheet', 'my_sheet2']

# 删除工作表
# wb.remove_sheet(ws1)  # 方法1
del wb["my_sheet111"]  # 方法2
print(wb.sheetnames)  # 输出：['Sheet', 'my_sheet2']

# 复制工作表
cp_sheet = wb.copy_worksheet(ws1)
print(cp_sheet.title)  # 输出：my_sheet111 Copy

12、创建一个新的sheet

# 创建一个新的sheet
ws1 = wb.create_sheet("Sheet_1")  # 在最后插入一个sheet(默认)
ws2 = wb.create_sheet("Sheet_2", 0)  # 在第一个位置插入一个sheet
ws3 = wb.create_sheet("Sheet_3", -1)  # 在倒数第二个位置插入一个sheet
ws4 = wb.create_sheet()  # 默认

13、向某个格子(cell)写入内容并保存

# 通过sheet实例写入
sheet['A1'] = '这是测试写入'
work_book.save(filename=filename)  #也可以是其他表格
# 通过cell实例写入
cell = sheet['A1']
cell.value = '这是测试写入'
work_book.save(filename=filename)

14、写入一维数组, 二维数组

import openpyxl
d=['a','b','c']
wb=openpyxl.Workbook()
ws = wb.create_sheet(index=0,title='my')
ws.append(['品名'])#首先写入标题栏
row=len(d)
col=1
for i in range(1,row+1):
    for j in range(col):
       #ws.append([d[i-1]])
       ws.cell(i+1,1,value=d[i-1])#因为要有标题栏，故要从第2行开始写
wb.save(filename='x2.xlsx',)


#向某个表格中写入一维数组，即只写入一列数据，可使用上面方法


dd=[['a','b','c','d'],['x','y','z','j'],['q','w','e','t']]
wb2=openpyxl.Workbook()
ws2=wb2.create_sheet(index=0,title='test')
ws2.append(['列名1','列名2','列名3','列名4'])
row=len(dd)
col=len(dd[0])
for i in range(1,row+1):
    for j in range(1,col+1):
        ws2.cell(i+1,j,value=dd[i-1][j-1])

wb2.save(filename='x3.xlsx')
#写入二维数组数据时，注意cell传递的坐标参数和本身数组坐标

15、横向写入一维数组

import openpyxl
arrExcel=['a','b','c']
path_excel='x2.xlsx'
sheetName="test"
rowNUm=4
colNum=7
def wExcel(path_excel,sheetName,rowNUm,colNum,arrExcel):
    '''
    功能:一维数组横向写入Excel,
    :param path_excel: 表格路径
    :param sheetName: Excel sheet名字
    :param rowNUm: 第几行开始
    :param colNum: 第几列开始
    :param arrExcel: 一维数组
    :return: ..考试 想办法改,方法不能完全一模一样哈;
    '''
    wb=openpyxl.Workbook()
    ws = wb.create_sheet(index=0,title=sheetName)

    for i ,j in enumerate(range(colNum,colNum+len(arrExcel))):
       ws.cell(rowNUm,j,value=arrExcel[i])#因为要有标题栏，故要从第2行开始写
    wb.save(filename=path_excel,)

16、追加写入一维数组，再表格含有数据的最后一行追加写入数据；

arrrest=["aaa","bbb","ccc"]
filename = r'''D:\RPA文件\xxx记录.xlsx'''#创建文Excle名字；
work_book = load_workbook(filename=filename)
sheet = work_book['汇总']#向sheet为汇总的里面写入
for data in arrrest:
    print(data)
    sheet.append(data)#追加写入表格 
    work_book.save(filename=filename)  # 保存到本地文件系统中

17、写入当前

def wExcel(path_excel,sheetName,rowNUm,colNum,arrExcel):
    '''
    功能:一维数组横向写入Excel,
    :param path_excel: 表格路径
    :param sheetName: Excel sheet名字
    :param rowNUm: 第几行开始
    :param colNum: 第几列开始
    :param arrExcel: 一维数组
    :return:
    '''
    wb = load_workbook(filename=path_excel)
    sheet = wb.active
    print(sheet)
    # ws = wb.create_sheet(index=0,title="test")#会重新新建一个

    for i ,j in enumerate(range(colNum,colNum+len(arrExcel))):
       sheet.cell(rowNUm,j,value=arrExcel[i])#因为要有标题栏，故要从第2行开始写
    wb.save(filename=path_excel,)

18、使用python列表批量插入数据

data = [
        ['a',1],
        ['b',2],
        ['c',3],
        ['d',4]
]

for row in data:
        sheet.append(row)
work_book.save(filename=filename)

19、直接赋值公式字符串

from openpyxl.utils import FORMULAE
print(FORMULAE)  # 打印所有支持直接赋值的公式

sheet['F1002'] = '=AVERAGE(F2:F1001)' #对F列进行评价计算
work_book.save(filename=filename)

20、openpyxl.styles.numbers 的源代码,设置表格内的各种格式

# Copyright (c) 2010-2023 openpyxl

import re

from openpyxl.descriptors import (
    String,
    Sequence,
    Integer,
)
from openpyxl.descriptors.serialisable import Serialisable


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

BUILTIN_FORMATS_MAX_SIZE = 164
BUILTIN_FORMATS_REVERSE = dict(
        [(value, key) for key, value in BUILTIN_FORMATS.items()])

FORMAT_GENERAL = BUILTIN_FORMATS[0]
FORMAT_TEXT = BUILTIN_FORMATS[49]
FORMAT_NUMBER = BUILTIN_FORMATS[1]
FORMAT_NUMBER_00 = BUILTIN_FORMATS[2]
FORMAT_NUMBER_COMMA_SEPARATED1 = BUILTIN_FORMATS[4]
FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'
FORMAT_PERCENTAGE = BUILTIN_FORMATS[9]
FORMAT_PERCENTAGE_00 = BUILTIN_FORMATS[10]
FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'
FORMAT_DATE_YYMMDD = 'yy-mm-dd'
FORMAT_DATE_DDMMYY = 'dd/mm/yy'
FORMAT_DATE_DMYSLASH = 'd/m/y'
FORMAT_DATE_DMYMINUS = 'd-m-y'
FORMAT_DATE_DMMINUS = 'd-m'
FORMAT_DATE_MYMINUS = 'm-y'
FORMAT_DATE_XLSX14 = BUILTIN_FORMATS[14]
FORMAT_DATE_XLSX15 = BUILTIN_FORMATS[15]
FORMAT_DATE_XLSX16 = BUILTIN_FORMATS[16]
FORMAT_DATE_XLSX17 = BUILTIN_FORMATS[17]
FORMAT_DATE_XLSX22 = BUILTIN_FORMATS[22]
FORMAT_DATE_DATETIME = 'yyyy-mm-dd h:mm:ss'
FORMAT_DATE_TIME1 = BUILTIN_FORMATS[18]
FORMAT_DATE_TIME2 = BUILTIN_FORMATS[19]
FORMAT_DATE_TIME3 = BUILTIN_FORMATS[20]
FORMAT_DATE_TIME4 = BUILTIN_FORMATS[21]
FORMAT_DATE_TIME5 = BUILTIN_FORMATS[45]
FORMAT_DATE_TIME6 = BUILTIN_FORMATS[21]
FORMAT_DATE_TIME7 = 'i:s.S'
FORMAT_DATE_TIME8 = 'h:mm:ss@'
FORMAT_DATE_TIMEDELTA = '[hh]:mm:ss'
FORMAT_DATE_YYMMDDSLASH = 'yy/mm/dd@'
FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'
FORMAT_CURRENCY_USD = '$#,##0_-'
FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-'


COLORS = r"\[(BLACK|BLUE|CYAN|GREEN|MAGENTA|RED|WHITE|YELLOW)\]"
LITERAL_GROUP = r'".*?"' # anything in quotes
LOCALE_GROUP = r'\[(?!hh?\]|mm?\]|ss?\])[^\]]*\]' # anything in square brackets, except hours or minutes or seconds
STRIP_RE = re.compile(f"{LITERAL_GROUP}|{LOCALE_GROUP}")
TIMEDELTA_RE = re.compile(r'\[hh?\](:mm(:ss(\.0*)?)?)?|\[mm?\](:ss(\.0*)?)?|\[ss?\](\.0*)?', re.I)


# Spec 18.8.31 numFmts
# +ve;-ve;zero;text

[docs]def is_date_format(fmt):
    if fmt is None:
        return False
    fmt = fmt.split(";")[0] # only look at the first format
    fmt = STRIP_RE.sub("", fmt) # ignore some formats
    return re.search(r"(?<!\\)[dmhysDMHYS]", fmt) is not None



[docs]def is_timedelta_format(fmt):
    if fmt is None:
        return False
    fmt = fmt.split(";")[0] # only look at the first format
    return TIMEDELTA_RE.search(fmt) is not None



[docs]def is_datetime(fmt):
    """
    Return date, time or datetime
    """
    if not is_date_format(fmt):
        return

    DATE = TIME = False

    if any((x in fmt for x in 'dy')):
        DATE = True
    if any((x in fmt for x in 'hs')):
        TIME = True

    if DATE and TIME:
        return "datetime"
    if DATE:
        return "date"
    return "time"



[docs]def is_builtin(fmt):
    return fmt in BUILTIN_FORMATS.values()



[docs]def builtin_format_code(index):
    """Return one of the standard format codes by index."""
    try:
        fmt = BUILTIN_FORMATS[index]
    except KeyError:
        fmt = None
    return fmt



[docs]def builtin_format_id(fmt):
    """Return the id of a standard style."""
    return BUILTIN_FORMATS_REVERSE.get(fmt)



[docs]class NumberFormatDescriptor(String):

    def __set__(self, instance, value):
        if value is None:
            value = FORMAT_GENERAL
        super(NumberFormatDescriptor, self).__set__(instance, value)



[docs]class NumberFormat(Serialisable):

    numFmtId = Integer()
    formatCode = String()

    def __init__(self,
                 numFmtId=None,
                 formatCode=None,
                ):
        self.numFmtId = numFmtId
        self.formatCode = formatCode



[docs]class NumberFormatList(Serialisable):

    count = Integer(allow_none=True)
    numFmt = Sequence(expected_type=NumberFormat)

    __elements__ = ('numFmt',)
    __attrs__ = ("count",)

    def __init__(self,
                 count=None,
                 numFmt=(),
                ):
        self.numFmt = numFmt


    @property
    def count(self):
        return len(self.numFmt)


    def __getitem__(self, idx):
        return self.numFmt[idx]

21、使用筛选器和排序

可以通过添加自动筛选来筛选工作表中的单个值范围。如果需要筛选多个区域，可以使用表并为每个表应用单独的筛选器。
若要添加筛选器，请定义一个范围，然后添加列。您可以通过设置 ref 属性来设置筛选器的范围。然后使用从零开始的索引将过滤器应用于范围内的列，例如。在 A1：H10 的范围内，colId 1 引用列 B.Openpyxl 不检查此类赋值的有效性

from openpyxl import Workbook
from openpyxl.worksheet.filters import (
    FilterColumn,
    CustomFilter,
    CustomFilters,
    DateGroupItem,
    Filters,
    )

wb = Workbook()
ws = wb.active

data = [
    ["Fruit", "Quantity"],
    ["Kiwi", 3],
    ["Grape", 15],
    ["Apple", 3],
    ["Peach", 3],
    ["Pomegranate", 3],
    ["Pear", 3],
    ["Tangerine", 3],
    ["Blueberry", 3],
    ["Mango", 3],
    ["Watermelon", 3],
    ["Blackberry", 3],
    ["Orange", 3],
    ["Raspberry", 3],
    ["Banana", 3]
]

for r in data:
    ws.append(r)

filters = ws.auto_filter
filters.ref = "A1:B15"
col = FilterColumn(colId=0) # for column A
col.filters = Filters(filter=["Kiwi", "Apple", "Mango"]) # add selected values
filters.filterColumn.append(col) # add filter to the worksheet

ws.auto_filter.add_sort_condition("B2:B15")

wb.save("filtered.xlsx")

22、插入一列和插入多列　

# 插入一列
sheet.insert_cols(idx=2)  #在第2列的左边插入1个空列
work_book.save(filename=filename)

# 插入多列
sheet.insert_cols(idx=2,amount=3)  #在第2列的左边插入3个空列
work_book.save(filename=filename)

23、插入一行和插入多行　

# 插入一行
sheet.insert_rows(idx=2)  #在第2行的上面插入1行
work_book.save(filename=filename)

# 插入多行
sheet.insert_rows(idx=2,amount=3)  #在第2行的上面插入3行
work_book.save(filename=filename)

24、.删除一列和删除多列

# 删除一列
sheet.delete_cols(idx=2) #删除第2列
work_book.save(filename=filename)


# 删除多列
sheet.delete_cols(idx=2,amount=3) #从第2列开始删除3列 即 2 3 4 列
work_book.save(filename=filename)　

25、删除一行和删除多行　

# 删除一行
sheet.delete_rows(idx=2) #删除第2行
work_book.save(filename=filename)

# 删除多行
sheet.delete_rows(idx=2,amount=3) #从第2行开始删除3行 即 2 3 4 行
work_book.save(filename=filename)

26、移动格子

# 将 C1和D4直接的格子 向下 2行 向左2行   正整数时 row下 col为左，反之 row为上 col为右
sheet.move_range("C1:D4",rows=2,cols=-2)
work_book.save(filename=filename)

27、创建新sheet

work_book.create_sheet("表格2")
work_book.save(filename=filename)

28、删除sheet

work_book.remove("表格2")
work_book.save(filename=filename

29、复制一个sheet

work_book.copy_worksheet(sheet)
work_book.save(filename=filename) #新sheet名称为 原sheet名称+ ' Copy' 注意有空格

30、修改sheet名称

sheet.title = "表格3"
work_book.save(filename=filename)

31、冻结窗格　

sheet.freeze_panes= "G2" #冻结G列第2行  左右移动时 G列之前冻结  上下滚动第1行冻结，不包括第2行
work_book.save(filename=filename)

32、添加筛选

sheet.auto_filter.ref = sheet.dimensions  # 对整张表格都筛选，执行结果第一行所有列都有筛选下拉选项
work_book.save(filename=filename)　

33、追加写入

import openpyxl


def write_to_excel(filename,arr):
    try:
        # 尝试加载现有的工作簿文件
        workbook = openpyxl.load_workbook(filename)
        sheet = workbook.active
    except FileNotFoundError:
        # 如果文件不存在，则创建新的工作簿
        workbook = openpyxl.Workbook()
        sheet = workbook.active

    # 将数组写入Excel文件的第一行
    sheet.append(arr)

    workbook.save(filename)
filename="ddd.xlsx"
arr = ['Value 1', 'Value 2', 'Value 3', 'Value 4']
write_to_excel(filename,arr)