前言openpyxl 模块：可以读取和修改 Excel 电子表格文件 软件名及软件下载地址LibreOffice CalcApache OpenOffice - Official Site - The Free and Open Productivity Suite OpenOffice CalcHome
LibreOffice - Free Office Suite - Based on OpenOffice - Compatible with Microsoft 一个 Excel 电子表格文档称为一个工作簿 - - - 扩展名为.xlsx每个工作簿可以包含多个表（也称为工作表）用户当前查看的表（或关闭 Excel 前最后查看的表），称为活动表一、安装openpyxl模块pip install openpyxl
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files二、读取 Excel 文档1.用 openpyxl 模块打开 Excel 文档 openpyxl.load_workbook()函数import openpyxl
# 打开 Excel 文件
wb = openpyxl.load_workbook('example.xlsx')
type(wb) # <class 'openpyxl.workbook.workbook.Workbook'>
2.从工作簿中取得工作表的名称 sheetnameswb.sheetnames # ['Sheet1', 'Sheet2', 'Sheet3']
# 选择工作表
sheet = wb['Sheet3']
print(sheet) # <Worksheet "Sheet3">
type(sheet)
# <class 'openpyxl.worksheet.worksheet.Worksheet'>
sheet.title
# 'Sheet3'
anotherSheet = wb.active # 活动表
anotherSheet # <Worksheet "Sheet1">
3.从表中取得单元格有了 Worksheet 对象后，就可以按名字访问 Cell 对象row行、column列 和 coordinate坐标 属性，提供该单元格的位置信息import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet['A1']
# <Cell Sheet1.A1>
sheet['A1'].value # datetime.datetime(2015, 4, 5, 13, 34, 2)
c = sheet['B1']
c.value
# 'Apples'
'Row ' + str(c.row) + ', Column ' + str(c.column) + ' is ' + c.value # 'Row 1, Column 2 is Apples'
'Cell ' + c.coordinate + ' is ' + c.value # 'Cell B1 is Apples'
sheet['C1'].value # 73
sheet.cell(row=1, column=2) # <Cell Sheet1.B1>
for i in range(1, 8, 2):
print(i, sheet.cell(row=i, column=2).value)
# 1 Apples
# 3 Pears
# 5 Apples
# 7 Strawberries
max_row获取工作表总行数，max_column总列数sheet.max_row
# 7
sheet.max_column # 3
4.列字母和数字之间的转换import openpyxl
import openpyxl.utils
# 列字母转换为数字索引
col_letter = 'D'
col_num = openpyxl.utils.column_index_from_string(col_letter)
print(f"{col_letter} 对应的数字索引为：{col_num}") # D 对应的数字索引为：4
# 数字索引转换为列字母
col_num = 4
col_letter = openpyxl.utils.get_column_letter(col_num)
print(f"{col_num} 对应的列字母为：{col_letter}")
# 4 对应的列字母为：D
from openpyxl.utils import column_index_from_string,get_column_letter
column_index_from_string('aa') # 27
5.从表中取得行和列可以将 Worksheet 对象切片，取得电子表格中一行、一列或一个矩形区域中的所有Cell 对象。然后可以循环遍历这个切片中的所有单元格。tuple(sheet['A1':'C3'])
# ((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>),
#
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>),
#
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
for rowOfCellObjects in sheet['A1':'C3']:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate, cellObj.value)
print('--- END OF ROW ---')
# A1 2015-04-05 13:34:02
# B1 Apples
# C1 73
# --- END OF ROW ---
# -- snip --
sheet.columns 返回的是一个生成器对象，而不是一个列表，因此不能像列表一样进行索引如果想要查看第一列的单元格内容，可以用 iter_cols() 方法，这个方法的参数可以指定列的范围，例如： min_col=1, max_col=1 表示第一列。import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
for cellObj in sheet.iter_cols(min_col=2, max_col=2): # 第2列
for cell in cellObj:
print(cell.value)
# Apples
# Cherries
# Pears
# Oranges
# Apples
# Bananas
# Strawberries
sheet.iter_cols的参数有min_row：起始行号；max_row：终止行号；min_col：起始列号；max_col：终止列号；values_only：bool 类型，默认为 False，如果是 True，表示只返回单元格的值，不包含任何格式信息。三、从电子表格中读取数据1.读取电子表格数据普查区的编号（A），州的简称（B），县的名称（C），普查区的人口（D）import openpyxl,pprint
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}
# range(2,sheet.max_row + 1)
从第二行到最后一行，因为不包含sheet.max_row,所以＋1
for row in range(2,sheet.max_row + 1):
State = sheet['B'+str(row)].value
# 州
county = sheet['C' + str(row)].value # 县
pop = sheet['D' + str(row)].value
# 人口
2.填充数据结构pop人口数 tracts普查区数for row in range(2,sheet.max_row + 1):
state = sheet['B'+str(row)].value
county = sheet['C' + str(row)].value
pop = sheet['D' + str(row)].value
# Make sure the key for this state exists.
countyData.setdefault(state, {})
# Make sure the key for this county in this state exists.
countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})
# Each row represents one census tract, so increment by one.
countyData[state][county]['tracts'] += 1
# Increase the county pop by the pop in this census tract.
countyData[state][county]['pop'] += int(pop)
补充setdefault：setdefault用于向字典中添加键值对。当键不存在时，它会将键值对添加到字典中；当键已存在时，它会更新键的值setdefault 的语法： dictionary.setdefault(key, default_value)key 是要检查或添加的键，default_value 是在字典中未找到键时要分配给该键的值myDict = {'name': 'John', 'age': 26}
address = myDict.setdefault('address', 'Unknown')
print(address)
# 输出：Unknown
print(myDict)
# 输出：{'name': 'John', 'age': 26, 'address': 'Unknown'}
city = myDict.setdefault('city', 'New York')
print(city)
# 输出：New York
print(myDict)
# 输出：{'name': 'John', 'age': 26, 'address': 'Unknown', 'city': 'New York'}
# 如果键已存在，则忽略 setdefault 中分配的值
myDict.setdefault('name', 'Mike')
print(myDict)
# 输出：{'name': 'John', 'age': 26, 'address': 'Unknown', 'city': 'New York'}
3.将结果写入文件resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')
四、写入Excel文档1.创建并保存 Excel 文档 openpyxl.Workbook()import openpyxl
# 创建一个新的空 Workbook 对象
wb = openpyxl.Workbook()
wb.sheetnames # ['Sheet']
sheet = wb.active
sheet.title = 'asdf'
wb.sheetnames # ['asdf']
wb.save('asdf.xlsx') # 以asdf.xlsx为文件名保存文件
2.创建create_sheet()和删除remove_sheet()工作表wb.create_sheet() # 在最后位置创建一个工作表，默认Sheet，Sheet1，Sheet2...
wb.create_sheet(index=0, title='First Sheet') # 在最前面创建一个名为First Sheet的工作表
wb.remove_sheet(wb['Sheet']) # 删除Sheet工作表
del wb['asdf']
# 删除asdf工作表
3.将值写入单元格sheet = wb.active
sheet['A1'] = 'Hello World'
sheet['A1'].value # 'Hello World'
五、更新一个电子表格销售产品的类型（A）、产品每磅的价格（B）、销售的磅数（C），销售的总收入1.利用更新信息建立数据结构import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']
PRICE_UPDATES = {'Garlic': 3.07,
'Celery': 1.19,
'Lemon': 1.27}
2.检查所有行，更新不正确的价格for rowNum in range(2, sheet.max_row + 1): # skip the first row
produceName = sheet.cell(row=rowNum, column=1).value
if produceName in PRICE_UPDATES:
sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]
wb.save('updatedProduceSales.xlsx')
六、设置单元格的字体风格Font 对象import openpyxl
from openpyxl.styles import Font, NamedStyle
wb = openpyxl.Workbook()
sheet = wb.active
italic24Font = Font(size=30, italic=False,bold=True,color="ff0000",name='Calibri')
# name 字体名称
eg：'Times New Roman' 默认字体Calibri
默认大小11
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')
七、公式sheet['B9'] = '=SUM(B1:B8)'
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
data_only = Trueport openpyxl
>>> wbFormulas = openpyxl.load_workbook('writeFormula.xlsx')
>>> sheet = wbFormulas.get_active_sheet()
>>> sheet['A3'].value
'=SUM(A1:A2)'
>>> wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)
>>> sheet = wbDataOnly.get_active_sheet()
>>> sheet['A3'].value
500
八、调整行和列1.设置行高和列宽Worksheet 对象有row_dimensions 和 column_dimensions 属性，控制行高和列宽行的高度可以设置为 0 到 409 之间的整数或浮点值一点等于 1/72 英寸，默认的行高是 12.75列宽可以设置为 0到 255 之间的整数或浮点数。这个值表示使用默认字体大小时（11 点），单元格可以显示的字符数。默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
wb.save('dimensions.xlsx')
2.合并和拆分单元格merge_cells()工作表方法，将一个矩形区域中的单元格合并为一个单元格import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together.'
sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two merged cells.'
wb.save('merged.xlsx')
拆分单元格，调用 unmerge_cells()工作表方法sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
3.冻结窗格freeze_panes属性在 OpenPyXL 中，每个 Worksheet 对象都有一个 freeze_panes属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串。请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。要解冻所有的单元格，就将 freeze_panes 设置为 None 或’A1’。sheet.freeze_panes = 'A2' # 冻结A列
sheet.freeze_panes = 'C2'
# 冻结行1和列A和列B
wb.save('freezeExample.xlsx')
4.图表创建条形图、折线图、散点图和饼图import random
from openpyxl import Workbook
from openpyxl.chart import (
BarChart,
Reference,
Series,
)
# 创建一个 Workbook 对象
wb = Workbook()
# 选择一个工作表
ws = wb.active
# 编辑数据
for i in range(1, 6):
# 第一行是列标题
ws.cell(row=1, column=i).value = f"第{i}个月"
# 填充数据，随机生成 1 到 100 之间的整数作为销售额
for j in range(2, 7):
ws.cell(row=j, column=i).value = random.randint(1, 100)
# 创建一个柱状图
chart = BarChart()
# 设置图表的标题和风格
chart.title = "销售额"
chart.style = 10
# 获取数据的范围
data = Reference(ws, min_col=1, min_row=1, max_col=5, max_row=6)
# 将数据添加到图表中
chart.add_data(data, titles_from_data=True)
# 添加 x 轴标签
cats = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.set_categories(cats)
# 将图表添加到工作表中
ws.add_chart(chart, "A8")
# 保存 Excel 文件
wb.save("sales.xlsx")
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.get_active_sheet()
>>> for i in range(1, 11): # create some data in column A
sheet['A' + str(i)] = i
>>> refObj = openpyxl.charts.Reference(sheet, (1, 1), (10, 1))
>>> seriesObj = openpyxl.charts.Series(refObj, title='First series')
>>> chartObj = openpyxl.charts.BarChart()
>>> chartObj.append(seriesObj)
>>> chartObj.drawing.top = 50 # set the position
>>> chartObj.drawing.left = 100
>>> chartObj.drawing.width = 300 # set the size
>>> chartObj.drawing.height = 200
>>> sheet.add_chart(chartObj)
>>> wb.save('sampleChart.xlsx')
openpyxl.charts.BarChart()，创建一个条形图openpyxl.charts.LineChart()、openpyxl.charts.ScatterChart()和 openpyxl.charts.PieChart()，创建折线图、散点图和饼图。}