[Python知识库]python操作excel之openpyxl

一、openpyxl模块安装

pip install openpyxl

二、使用范围

openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，若其它格式，可先转为xlsx格式。

三、常用方法

3.1、生成新的excle

import openpyxl

if __name__ == '__main__':
    
    #生成一个新的空的Excel
    wb = openpyxl.Workbook()

    # 保存可指定保存路径并定义文件名，如下，若不指定路径，则保存到当前py文件目录下，如 wb.save('example222.xlsx')
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx') 
    

3.2、打开已存在的excel

# 导入模块
#coding=utf-8

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') 

    # 选择需要操作的sheet页
    ws1=wb.worksheets[2]  #通过索引获取需要操作的sheet页，也可以通过sheet页名称获取需要操作的sheet页，如：ws1 = wb.get_sheet_by_name('frequency')或ws = wb["frequency"]

    # 更改Sheet页名称
    ws1.title = "WorkSheetTitle"
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

3.3、sheet页重命名

import openpyxl

if __name__ == '__main__':

    #生成一个新的空的Excel
    wb = openpyxl.Workbook
    
    #打开excel默认sheet页
    ws = wb.active

    # 更改默认名称Sheet`  
    ws.title = "WorkSheetTitle"

    # 保存
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')

3.4、excel生成多个sheet页并设置sheet页排序和多sheet页命名

import openpyxl

if __name__ == '__main__':
    
    wb = openpyxl.Workbook()
    #打开默认sheet页
    ws = wb.active

    # #更改默认名称Sheet`
    ws.title = "WorkSheetTitle"

    # 定义第二个sheet页，并重命名
    ws2 = wb.create_sheet("NewWorkSheet2")

    # 定义第三个sheet页，并重命名和定义排序
    # create_sheet()方法，可传title和index两个参数，如`0` 的设定 会将该sheet页 置于wb最前面。
    ws3 = wb.create_sheet("NewWorkSheet3", 0)

    # 保存
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')

3.5、excel写入-单元格赋值

说明：Cell的row和column都是从1开始的

3.5.1、通过指定坐标给单元格赋值

if __name__ == '__main__':
    
    wb = openpyxl.Workbook()
    ws = wb.active

    # 给单元格赋值
    ws["A1"] = "HOGE"  
    ws["B1"] = "FUGA"
    ws['C1'] = '=AVERAGE(C1:C10)'   #值也可以是公式

    # 保存
   wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
    

3.5.2、通过指定行列给单元格赋值

if __name__ == '__main__':
    
    wb = openpyxl.Workbook()
    ws = wb.active

    # 指定行列给单元格赋值
    ws.cell(row=4, column=2, value=10)

    # 保存
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')

3.5.3、excel写入-写入一行或多行数据

方法：sheet.append() 在已有的数据后面追加

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    data1=['100','101','a']
    ws.append(data1)
    data2 = ['200', '201', 'b']
    ws.append(data2)
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

四、设置单元格style

4.1、设置单元格字体

import openpyxl

if __name__ == '__main__':
    """
    设置字体font
    """
    wb = openpyxl.Workbook()
    ws = wb.active

    # 设置font
    font = openpyxl.styles.Font(name = "宋体",size = 15)
    ws['A2'].font = font

    ws['A2'].value = "TEST002"

    # 保存
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')

4.2、单元格颜色填充

import openpyxl
from openpyxl.styles import PatternFill

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active

    # 单元格填充颜色
    fill = PatternFill(fill_type='solid',fgColor='FFFF0000')
    ws['A1'].fill = fill

    ws['A1'] = 'TEST001'

    # 保存
    wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')

五、sheet页中行增删改查

5.1、sheet页中插入1列

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.insert_cols(idx=1)  #  在第一列左侧插入1列
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.2、sheet页中插入多列

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.insert_cols(idx=2,amount=3)  #  在第二列左侧插入3列
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
   

5.3、删除1列

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') 

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.delete_cols(idx=2) #删除第二列
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.4、删除多列

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') 

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.delete_cols(idx=5,amount=3) #从第5列开始删除3列
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.5、插入一行

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') 

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.insert_rows(idx=2) #插入第二行
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.6、插入多行

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') 

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.insert_rows(idx=2,amount=3) #从第二行开始插入3行
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.7、删除1行

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.delete_rows(idx=2) #删除第二行
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

5.8、删除多行

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    ws.delete_rows(idx=2,amount=3) #从第二行开始删除3行
    # 保存
    wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')

六、读取excel中内容

6.1、读取excel中内容各种方法

import openpyxl

if __name__ == '__main__':

    #打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    print('---------------读取指定单元格数据方法1--指定坐标-------------------------')
    cell01 = ws['A2'].value  # 通过坐标获取单元格的值
    print(cell01)

    print('---------------读取指定单元格数据方法2--指定行列-------------------------')
    cell01 = ws.cell(2, 1).value  # 通过指定行列获取单元格的值，第一个参数为行、第二个参数为列，即第2行第1列

    print(cell01)

    print('---------------读取第2行数据方法1-------------------------')
    rows = ws['2']
    for cell01 in rows:
        print(cell01.value)
    print('----------------读取第2行数据方法2------------------------')

    for i in range(1, 4):
        a=ws.cell(2,i).value   #第2行，第i列
        print(a)

    print('---------------读取B列数据方法1-------------------------')
    cols = ws['B']
    for cell01 in cols:
        print(cell01.value)
    print('----------------读取B列数据方法2------------------------')
    for i in range(1, 18):
        a=ws.cell(i,2).value  #第i行，第2列
        print(a)

    print('----------------按行读取多行范围内数据方法------------------------')
    rows = ws['1:3']
    for ro in rows:
        for cell in ro:
            print(cell.value)

    print('----------------按列读取多列范围内数据方法------------------------')
    cols = ws['B:C']
    for col in cols:
        for cell in col:
            print(cell.value)

    print('----------------读取多行多列范围内数据方法------------------------')
    cells = ws['B2:C5']
    cell01=ws['B2']
    rowNumber01 = cell01.row  # 获取单元格行号
    columnNumber01 = cell01.column  # 获取单元格列号
    cell02 = ws['C5']
    rowNumber02 = cell02.row  # 获取单元格行号
    columnNumber02 = cell02.column  # 获取单元格列号
    print(rowNumber01,rowNumber02,columnNumber01,columnNumber02)
    for rowNumber in range(rowNumber01,rowNumber02+1):
        for columnNumber in range(columnNumber01,columnNumber02+1):
            a=ws.cell(rowNumber,columnNumber).value
            print(a)

    print('----------------读取所有行数据方法------------------------')
    for row in ws.rows:
        for cell in row:
            cell01 = cell.value  # 按行的维度输出值
            print(cell01)

    print('----------------读取所有列数据方法------------------------')
    for column in ws.columns:
        for cell in column:
            cell01 = cell.value  # 按列的维度输出值
            print(cell01)

    print('----------------读取所有单元格数据方法------------------------')
    for row in ws:
        for cell in row:
            cell01 = cell.value
            print(cell01)

6.2、获取某个单元格的行号、列号、坐标

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    cell01= ws['B5']
    rowNumber=cell01.row  #获取单元格行号
    columnNumber=cell01.column #获取单元格列号
    coordinateNumber=cell01.coordinate  #获取单元格坐标
    print(rowNumber,columnNumber,coordinateNumber)

6.3、获取最大行数、列数 (存在值的有效行列号)

import openpyxl

if __name__ == '__main__':

	#打开已存在的excel
    wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') #

    # 选择需要操作的sheet页
    ws=wb.worksheets[0]

    print(ws.max_row)
    print(ws.max_column)