Excel基本概念

• 工作簿：一个Excel电子表格文档，扩展名.xlsx
• 工作表：一个工作簿最多可以包含255张工作表
• 活动表：用户当前查看或关闭Excel前最后退出的表
• 列：默认从A开始，行：默认从1开始
• 单元格：行列交叉的方格为单元格

安装openpyxl模块

# pip install openpyxl
import openpyxl
pip show openpyxl #查看包的版本

读取Excel文档

用openpyxl模块打开Excel文档

wb = openpyxl.load_workbook(r"C:\\Users\\Administrator\\example.xlsx") # wb means workbook

从工作簿中取得工作表

# 获取所有sheet_name,可以用于遍历
wb.get_sheet_names()

# 获取指定名称的sheet
sheet3 = wb.get_sheet_by_name('Sheet3') #get sheet by name

sheet3.title #view the title of sheet
anotherSheet = wb.get_active_sheet() #get active sheet
anotherSheet.title #活动单元格的title

从表中取得单元格

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet1 = wb.get_sheet_by_name("Sheet1")

# 使用字母数字的方式，提取元素
sheet1['A1'] # Cell 提取单元格
sheet1['A1'].value #单元格的数据类型和内容
sheet1['A1'].row #单元格所在的行
sheet1['A1'].column #单元格所在的列
sheet1['A1'].coordinate #单元格所在的列

# 使用cell的方式，参数是行、列
sheet1.cell(row=1,column=2) #提取第1行、第2列的单元格
sheet1.cell(row=1,column=2).value #提取第1行、第2列的单元格的值
for i in range(1,8,2):
    print(i,sheet1.cell(row=i,column=2).value)

获取最大的行数和列数

import openpyxl
wb = openpyxl.load_workbook(r"C:\\Users\\Administrator\\example.xlsx")
sheet1 = wb.get_sheet_by_name('Sheet1')
sheet1.max_row  # 最多的行数
sheet1.max_column # 最多的列数

列字母和数字之间的转化

import openpyxl
from openpyxl.utils import get_column_letter,column_index_from_string 
get_column_letter(1)
get_column_letter(100)
column_index_from_string('A')
column_index_from_string('AA')

从表中取得行和列

import openpyxl
wb = openpyxl.load_workbook(r"C:\\Users\\Administrator\\example.xlsx")
sheet1 = wb.get_sheet_by_name('Sheet1')

# 注意整个部分的使用
tuple(sheet1['A1':'C3']) #每一行单独成tuple的一个元素
list(sheet1['A1':'C3']) #每一行单独成list的一个元素

for rowOfCellObjects in sheet1['A1':'C3']:  #打印区域
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate,cellObj.value)
    print('---End of Row---')

单独打印一列

import openpyxl
wb = openpyxl.load_workbook(r"C:\\Users\\Administrator\\example.xlsx")
sheet1 = wb.get_active_sheet()

#打印单列
for cellObj in list(sheet1.columns)[1]: 
    print(cellObj.value)

小结

• 导入openpyxl模块
• 调用openpyxl.load_workbook()函数
• 取得Workbook对象
• 调用get_active_sheet()或get_sheet_by_name()工作簿方法
• 使用索引或工作表的cell()方法，Cell()方法带上row和column参数
• 取得Cell对象
• 读取Cell对象的value/row/column/coordinate属性

写入Excel文档

创建并保存Excel文档

实战中下面的代码会有问题,我们新创建一个wb对象，没有活动页

import openpyxl

#创建空对象
wb = openpyxl.Workbook() 
wb.get_sheet_names() #查看空对象的sheet
sheet = wb.get_active_sheet() #获取当前活动工作表
sheet.title
sheet.title = 'Spam Bacon Eggs Sheet' #修改当前工作表的title
wb.get_sheet_names() #查看已修改的活动工作表title

改进后的代码

import openpyxl

#创建空对象
wb = openpyxl.Workbook() 
wb.get_sheet_names() #查看空对象的sheet

# 注意active 并不是函数
sheet = wb.active #获取当前活动工作表
sheet.title
sheet.title = 'Spam Bacon Eggs Sheet' #修改当前工作表的title
wb.get_sheet_names() #查看已修改的活动工作表title

输出
['Sheet']
Sheet
['Spam Bacon Eggs Sheet']

操作存在的excel，可以使用get_active_sheet的套路

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.get_active_sheet()
sheet.title = 'Spam Spam Spam'
wb.save('example_copy.xlsx') #保存修改工作表名的拷贝

创建和删除工作表

import openpyxl
wb = openpyxl.Workbook()
wb.get_sheet_names()

# 创建一个sheet页
wb.create_sheet()
wb.get_sheet_names()

# 创建一个指定名称和位置的sheet页
wb.create_sheet(index=0,title='First Sheet')
wb.get_sheet_names()
wb.create_sheet(index=2,title = 'Middle Sheet')
wb.get_sheet_names

# 删除sheet页 通过名称
wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet'))
wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))
wb.get_sheet_names()
['First Sheet', 'Sheet']

将值写入单元格

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
sheet['A1'] = 'Hello World'
sheet['A1'].value

项目：更新一个电子表格

利用更新信息建立数据结构

import openpyxl
wb  = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
# the produce types and their updated prices
price_updates = {'Garlic':3.07,'Celery':1.19,'Lemon':1.27}
#ToDo:Loop through the rows and update the prices.

检查所有行，更新不正确的价格(实战价值高)

迭代所有行的简单代码 for rowNum in range(2,sheet.max_row+1):

for rowNum in range(2,sheet.max_row+1):
 #the first row is heading, skip it
    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')

设置单元格的字体风格(重点)

from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
italic24Font = Font(size = 24, italic = True)
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')

Font对象

import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')

fontobj1 = Font(name='Times New Roman',italic=True) #name 字体 size 字号 bold 是否加粗 italic 是否斜体
sheet['A1'].font = fontobj1
sheet['A1'] = 'Bold Times New Roman'

fontobj2 = Font(size=24,italic=True)
sheet['B3'].font = fontobj2
sheet['B3']= '23 pt Italic'

wb.save('styles.xlsx') #默认size 11 name Calibri

公式(了解)

写入公式

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

只能读取表格中的公式，不能得到公式的结果

import openpyxl
wb = openpyxl.load_workbook('writeFormula.xlsx')
sheet = wb.get_active_sheet()
sheet['A3'].value

得到公式的结果

import openpyxl
wbDataonly = openpyxl.load_workbook('writeFormula.xlsx',data_only=True)
sheet1 = wbDataonly.get_active_sheet()
sheet1['A3'].value #此处需要手动打开一次.xlsx文件

调整行和列(重点)

调整行高和列宽

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

合并和拆分单元格(重点)

合并单元格

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

拆分单元格

import openpyxl 
wb = openpyxl.load_workbook('merged.xlsx') #拆分单元格
sheet = wb.active
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('merged.xlsx')

冻结窗格(重点)

import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active
sheet.freeze_panes = 'C2' 
# "A2" means freeze row1 'B1' means freeze columnA 'C2' means freeze row1 and columnA/columnB 'A1' or None means no freezed
wb.save('freezeExample.xlsx')

图表(了解)

import openpyxl 
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1,11): #create some data in column A
    sheet['A'+str(i)] = i

refObj = openpyxl.chart.Reference(sheet,min_row = 1,min_col = 1,max_row = 10,max_col = 1) #创建数据区域
seriesObj = openpyxl.chart.Series(refObj,title='First series')
chartObj = openpyxl.chart.BarChart()
chartObj.title = 'My chart'
chartObj.append(seriesObj)

sheet.add_chart(chartObj,'C5') #'C5'表示图表开始的位置
wb.save('sampleChart.xlsx')

习题与小结

• openpyxl.load_workbook()函数返回什么？
  • 返回Workbook对象
• get_sheet_names()工作簿返回什么？
  • 返回所有工作表名称构成的列表
• 如何取得名为“Sheet1”的工作表的Worksheet对象
  • openpyxl.get_sheet_by_name("Sheet1")
• 如何取得工作不得活动工作表的Worksheet对象
  • wb.get_active_sheet() / wb.active
• 如何取得单元格"C5"中的值
  • sheet['C5'].value / sheet.cell(row=5,column=3).value
• 如何将单元格C5中的值设置成‘Hello’
  • sheet['C5'] = 'Hello'
• 如何取得表示单元格的行和列的整数？
  • get_column_letter(int) int --> char
  • column_index_from_string char --> int
• max_row/max_column返回什么，返回值的类型是什么？
  • sheet1.max_row 以整数方式返回单元格区域的行号
  • sheet1.max_column 以整数方式返回单元格区域的列号
• 如果要取得列‘M’的下标，需要调用什么函数
  • column_index_from_string('M')
• 如果要取得列14的字符串名称，需要调用什么函数
  • get_column_letter(14)
• 如何取得从A1到F1的所有Cell对象的元组
  • tuple(sheet['A1':'F1])
• 如何将工作簿保存到文件名example.xlsx?
  • wb.save('example.xlsx')
• 如何在单元格中设置一个公式？
  • sheet['B1'] = '==SUM(A1:B15)'
• 如果需要取得单元格中公式的结果，而不是公式本身，必须先做什么？
  • 读取时使用data_only = True
  • mannual打开和保存工作簿
• 如何将第5行的高度设置为100
  • sheet.row_dimensions[5] = 100
• 如何将列C的宽度设置成70
  • sheet.column_dimensions['C'] = 70
• 列出一些openpyxl2.1.4不会从xlsx中加载的功能
  • 图表 #版本已更新，新版本未知
• 什么是冻结窗格？
  • sheet.freeze_panes = 'C2' 冻结首行和A、B列
• 创建条形图的步骤和方法：
  • 加载数据 openpyxl.load_workbook()
  • 创建reference对象 #图表的数据源区域
  • 创建series对象，并将reference对象加入到series对象中 #数据序列
  • 创建chart对象，并将series对象加入到chart对象中(append方法)
  • 将chart对象加入到sheet中(add_chart方法)