python使用openpyxl模块操作Excel

1.openpyxl概述

1.1 openpyxl的官网：

https://openpyxl.readthedocs.io/en/stable/index.html

1.2 openpyxl的API文档：

https://openpyxl-chinese-docs.readthedocs.io/zh_CN/latest/api/openpyxl.html

1.3 openpyxl的主要class和各个对象间的加载与访问

class

openpyxl.workbook.workbook.Workbook
opnepyxl.worksheet.worksheet.Worksheet
openpyxl.cell.cell.Cell

访问和加载方式

#加载workbook对象
wb=openpyxl.load_Workbook(Filename)
#创建workbook对象
wb=openpyxl.Workbook()

#根据sheetname加载worksheet对象
sh=workbook[sheetname]
#创建worksheet对象
sh=workbook.creat_sheet(sheetname)

#根据Cell的坐标加载某个单元格
cell=worksheet[cellcoordinate]
#根据列号获取某一列的单元格
cellrange=worksheet[‘A’]
#根据列号获取某几列的单元格
cellrange=worksheet[‘A：C’]
#根据行号获取某一行的单元格
cellrange=worksheet[3]
#根据列号获取某几列的单元格
cellrange=worksheet[3：5]
#根据最小边界范围加载一个区域的单元格
cellrange=worksheet[“A1:C2”]

2.workbook对象的主要属性和方法

Class openpyxl.workbook.workbook.Workbook

主要属性：

workbook.sheetnames 返回workbook中所有sheet的sheename
workbook.active 返回workbook中激活的workbsheet对象
workbook.worksheets 返回workbook中所有的worksheet对象
workbook.chartsheets 返回workbook中所有的charsheet对象

主要方法：

workbook.get_sheet_names() 返回workbook中所有sheet的sheename 已弃用
workbook.get_active_sheet() 返回workbook中激活的workbsheet对象 已弃用
workbook.get_index(n) 返回workbook中索引为n的workbsheet对象 已弃用

操作workbook对象的方法：保存、关闭

workbook.save(filename) Save the current workbook under the given filename.
workbook.close() Close workbook file if open. Only affects read-only and write-only modes.

获取worksheet对象的方法：索引、名称

workbook.index(n) 返回workbook中索引为n的workbsheet对象
workbook.get_sheet_by_name(sheetname) 返回workbook中名称为shetname的workbsheet对象

操作worksheet对象的方法：创建、删除、移动、复制

workbook.creat_sheet(title,index) 创建一个名称为tilte的worksheet
workbook.remove_sheet(worksheet) 从workbook中删除这个worksheet
workbook.move_sheet(sheet.offset) 移动一个worksheet
workbook.copy_worksheet(from worksheet) 复制一个本workbook中的worksheet
workbook.remove(worksheet) 从workbook中删除这个worksheet[同remove_sheet]

3.worksheet对象的主要属性和方法

Class openpyxl.worksheet.worksheet.Worksheet

主要属性

Property	Discription
worksheet.value	Produces all cell values in the worksheet, by row
worksheet.rows	Produces all cells in the worksheet, by row
worksheet.columns	Produces all cells in the worksheet, by column
worksheet.dimensions	Returns the result of the minimum bounding range for all cells containing data
worksheet.max_row	The maximum row index containing data (1-based)
worksheet.max_column	The maximum column index containing data (1-based)
worksheet.min_row	The minimum row index containing data (1-based)
worksheet.min_column	The minimum column index containing data (1-based)

主要方法：

function	Discription
cell=worksheet.cell(row, column）	Returns a cell object based on the given coordinates.
insert_cols(idx, amount=1)	Insert column or columns before col=idx
insert_rows(idx, amount=1)	Insert row or rows before row=idx
delete_cols(idx, amount=1)	Delete column or columns from col=idx
delete_rows(idx, amount=1)	Delete row or rows from row=idx
iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)	Produces cells from the worksheet, by column. Specify the iteration range using indices of rows and columns.
iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)	Produces cells from the worksheet, by row. Specify the iteration range using indices of rows and columns.

4.Cell对象的主要属性和方法

Porperty	Discription
cell.value	Get or set the value held in the cell.
cell.row	Row number of this cell (1-based)
cell.column	Column number of this cell (1-based)
cell.coordinate	This cell’s coordinate (ex. ‘A5’)
cell.comment	Returns the comment associated with this cell
cell.data_type	Returns the data type associated with this cell

5.Font对象的主要属性

Class openpyxl.style.font.Fonts

name = “微软雅黑” #字体型号

size = 20 #字体大小

color = Blue #字体颜色

bold = True # 加粗

italic = True # 倾斜

underline = ‘single’ #单个下划线

strikestrough = True #删除线

5.工程实例

5.1 创建workbook新建sheet写入数据并保存

import openpyxl
#新建workbook
wb = openpyxl.Workbook()
#新建worksheet
sh = wb.create_sheet("Bank Data",index=0)
#将数据写入worksheet
sh['A1'] = "银行"
sh.append(['中国银行'])
sh.cell(3,1,'中国农业银行')
sh.cell(4,1,'中国建设银行')
#保存workbook
wb.save("Bank Deposit Interest Rate Table.xlsx ")
#关闭workbook
wb.save()

5.2 读取workbook并修改

import openpyxl
#加载名字为"Bank Deposit Interest Rate Table.xlsx "的Excel
wb = openpyxl.load_workbook('Bank Deposit Interest Rate Table.xlsx')
#加载名字为”Bank Data“的sheet
sh = wb['Bank Data']
#更改sheet中的数据
sh['B1']='利率'
sh['B2']='1.5%'
sh['B3']='1.4%'
sh['B4']='1.5%'
#输出worksheet的各种属性文件
print(sh.value)
print(sh.rows)
print(sh.columns)
print(sh.dimensions)
#输出各属性对象
for row in sh.values:
    print(row)
for row in sh.rows:
    print(row)
for column in sh.columns:
    print(column)
#根据sheet属性，遍历sheet
for row in sh.values:
    for value in row:
        print(value)
for row in sh.rows:
    for cell in row:
        print(cell.value)
for column in sh.columns:
    for cell in column:
        print(cell.value)

5.3 对Excel中数据的格式进行修改

import openpyxl 

"""load workbook"""
wb = openpyxl.load_workbook('Bank Deposit Interest Rate Table.xlsx')
sh = wb['Bank Data']
sh["B1"]="利率"
sh["B2"]="1.5%"
sh["B3"]="1.4%"
sh["B4"]="1.5%"

cellA = sh['A1']
cellB = sh['B1']
FontA = openpyxl.styles.Font(name='Microsoft YaHei', color='111111',size =20,bold=True, italic=True,underline='single',strikethrough=True,outline=True,scheme="major",shadow=True,)
FontB = openpyxl.styles.Font(name="微软雅黑",size =15,bold=True, italic=True,underline='single',strikethrough=True,outline=False,scheme="minor",shadow=False,)
cellA.font=FontA 
cellB.font=FontB
wb.save('Bank Deposit Interest Rate Table.xlsx')
wb.close()