【python 让繁琐工作自动化】第13章处理 Excel 电子表格 (1)_=if(row('table s3'!b3)<column('table s3'!b3),'tabl-CSDN博客

本文链接：https://blog.csdn.net/HPP_CSDN/article/details/108218177

Automate the Boring Stuff with Python: Practical Programming for Total Beginners (2nd Edition)
Copyright © 2020 by Al Sweigart.

尽管 Excel 是 Microsoft 的专有软件，但是在 Windows，macOS 和 Linux 上可以运行免费的替代软件。LibreOffice Calc 和 OpenOffice Calc 都可以使用 Excel 的 .xlsx 文件。可以分别从 https://www.libreoffice.org/ 和 https://www.openoffice.org/ 下载该软件。

Excel 文档的一些基本定义：

Excel 电子表格文档称为工作簿 (workbook)。一个工作簿保存在扩展名为 .xlsx 的文件中。
每个工作簿可以包含多个表 (sheet)，也称为工作表 (worksheet)。
用户当前正在查看的工作表（或在关闭 Excel 之前最后查看的工作表）称为活动工作表 (active sheet)。
每张纸都有列（column：以A开头的字母寻址）和行（row：以1开头的数字寻址）。
特定列和行上的框称为单元格 (cell)。每个单元格可以包含一个数字或文本值。
包含数据的单元格网格构成了一张工作表。

13.1 安装 openpyxl 模块

OpenPyXL 不是 Python 自带的，所以需要自己安装 openpyxl 模块。

pip install --user -U openpyxl==2.6.2

上面的命令是安装 2.6.2 版本的 OpenPyXL。

可以在 https://openpyxl.readthedocs.org/ 上找到有关OpenPyXL的完整文档。

13.2 读取 Excel 文档

表13-1：example.xlsx 电子表格

	A	B	C
1	4/5/2015 1:34:02 PM	Apples	73
2	4/5/2015 3:41:23 AM	Cherries	85
3	4/6/2015 12:46:51 PM	Pears	14
4	4/8/2015 8:59:43 AM	Oranges	52
5	4/10/2015 2:07:00 AM	Apples	152
6	4/10/2015 6:10:37 PM	Bananas	23
7	4/10/2015 2:40:46 AM	Strawberries	98

使用 OpenPyXL 打开 Excel 文档

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>	# This Workbook object represents the Excel file

从工作簿中获取工作表

可以通过访问 sheetnames 属性来获取工作簿中所有工作表名称的列表。

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.sheetnames # The workbook's sheets' names.
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3'] # Get a sheet from the workbook.
>>> sheet
<Worksheet "Sheet3">	# Each sheet is represented by a Worksheet object
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title # Get the sheet's title as a string.
'Sheet3'
>>> anotherSheet = wb.active # Get the active sheet.
>>> anotherSheet
<Worksheet "Sheet1">

从工作表中获取单元格

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1'] # Get a sheet from the workbook.
>>> sheet['A1'] # Get a cell from the sheet.
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value # Get the value from the cell.
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c = sheet['B1'] # Get another cell from the sheet.
>>> c.value
'Apples'
>>> # Get the row, column, and value from the cell.
>>> 'Row %s, Column %s is %s' % (c.row, c.column, c.value)
'Row 1, Column B is Apples'
>>> 'Cell %s is %s' % (c.coordinate, c.value)
'Cell B1 is Apples'
>>> sheet['C1'].value
73

使用工作表的 cell 方法：

>>> sheet.cell(row=1, column=2)
<Cell 'Sheet1'.B1>
>>> sheet.cell(row=1, column=2).value
'Apples'
>>> for i in range(1, 8, 2): # Go through every other row:
...     print(i, sheet.cell(row=i, column=2).value)
...
1 Apples
3 Pears
5 Apples
7 Strawberries

使用 Worksheet 对象的 max_row 和 max_column 属性确定表的大小：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet.max_row # Get the highest row number.
7
>>> sheet.max_column # Get the highest column number.
3

在表示列的字母和数字之间转换

openpyxl.utils.column_index_from_string() 函数：将字母转换成数字。
openpyxl.utils.get_column_letter() 函数：将数字转换成字母。

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(1) # Translate column 1 to a letter.
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A') # Get A's number.
1
>>> column_index_from_string('AA')
27

从工作表中获取行和列

访问工作表中指定矩形区域中单元格的值：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> tuple(sheet['A1':'C3']) # Get all cells from A1 to 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 ---
A2 2015-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2015-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---

上面的程序指定要在从A1到C3的矩形区域中使用Cell对象，并获得一个Generator对象，其中包含该区域中的Cell对象。

使用 Worksheet 对象的 rows 和 columns 属性，访问工作表中指定行或列中的单元格的值：

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
>>> list(sheet.columns)[1] # Get second column's cells.
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cellObj in list(sheet.columns)[1]:
        print(cellObj.value)

Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

13.3 项目：从电子表格读取数据

给定一个来自2010年美国人口普查数据的电子表格，其文件名称为 censuspopdata.xlsx，可以从 https://nostarch.com/automatestuff2/ 下载。
遍历数千行以计算每个县的总人口和普查区域的数量。每行代表一个人口普查区。

编写一个脚本，该脚本可以从人口普查电子表格文件中读取并在几秒钟内计算每个县的统计信息。

程序需要执行的操作：

从 Excel 电子表格中读取数据
计算每个县的人口普查区（census tracts）数量
计算每个县的总人口
打印结果

这意味着代码将需要执行以下操作：

使用 openpyxl 模块打开并读取 Excel 文档的单元格。
计算所有区域和人口数据并将其存储在数据结构中。
使用 pprint 模块将数据结构写入扩展名为 .py 的文本文件。

#! python3
# readCensusExcel.py - Tabulates population and number of census tracts for each county.

import openpyxl, pprint
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}

# Fill in countyData with each county's population and tracts.
print('Reading rows...')
for row in range(2, sheet.max_row + 1):
	# Each row in the spreadsheet has data for one census tract.
	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)
	
# Open a new text file and write the contents of countyData to it.
print('Writing results...')
resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')

存储在 countyData 中的数据结构与下面类似：

{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
        'Aleutians West': {'pop': 5561, 'tracts': 2},
        'Anchorage': {'pop': 291826, 'tracts': 55},
        'Bethel': {'pop': 17013, 'tracts': 3},
        'Bristol Bay': {'pop': 997, 'tracts': 1},
        --snip--

pprint.pformat() 函数产生一个字符串，该字符串本身被格式化为有效的 Python 代码。将其输出到名为 census2010.py 的文本文件。这样做的好处是：现在可以像导入其他 Python 模块一样导入 census2010.py。

>>> import os
>>> import census2010
>>> census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
>>> anchoragePop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchoragePop))
The 2010 population of Anchorage was 291826

13.4 写入 Excel 文档

创建和保存 Excel 文档

>>> import openpyxl
>>> wb = openpyxl.Workbook() # Create a blank workbook.
>>> wb.sheetnames # It starts with one sheet.
['Sheet']
>>> sheet = wb.active
>>> sheet.title
'Sheet'
>>> sheet.title = 'Spam Bacon Eggs Sheet' # Change title.
>>> wb.sheetnames
['Spam Bacon Eggs Sheet']

任何时候修改 Workbook 对象或其工作表和单元格，在调用 save() 工作簿方法之前，都不会保存电子表格文件。

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
>>> sheet.title = 'Spam Spam Spam'
>>> wb.save('example_copy.xlsx') # Save the workbook.

当编辑从文件加载的电子表格时，最好将编辑后的新电子表格保存为与原始文件不同的文件名。这样，仍然可以使用原始电子表格文件，以防代码中的错误导致新的保存文件中的数据不正确或损坏。

创建和删除工作表

使用 create_sheet() 方法在工作簿中增加工作表：

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> wb.create_sheet() # Add a new sheet.
<Worksheet "Sheet1">
>>> wb.sheetnames
['Sheet', 'Sheet1']
>>> # Create a new sheet at index 0.
>>> wb.create_sheet(index=0, title='First Sheet')
<Worksheet "First Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Sheet1']
>>> wb.create_sheet(index=2, title='Middle Sheet')
<Worksheet "Middle Sheet">
>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']

使用 del 运算符从工作簿删除工作表：

>>> wb.sheetnames
['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']
>>> del wb['Middle Sheet']
>>> del wb['Sheet1']
>>> wb.sheetnames
['First Sheet', 'Sheet']

在向工作簿中添加工作表或从工作簿中删除工作表之后，记得调用 save() 方法来保存更改。

将值写入单元格

>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Hello, world!' # Edit the cell's value.
>>> sheet['A1'].value
'Hello, world!'

13.5 项目：更新电子表格

编写一个程序来更新农产品销售电子表格中的单元格。程序将浏览电子表格，查找特定种类的产品，并更新其价格。
从 https://nostarch.com/automatestuff2/ 下载此电子表格。

每行代表一个销售产品。列是已售产品的类型 (A)，每磅产品的成本 (B)，已售磅数 (C) 和销售总收入 (D)。
D 列设置为 Excel公式 = ROUND(B3*C3, 2)，该公式将每磅成本乘以出售的磅数，然后将结果四舍五入到最接近的美分。使用此公式，如果 B 列或 C 列发生更改，则 D 列中的单元格将自动更新。

程序执行以下操作：

遍历所有行
如果该行记录的是大蒜，芹菜或柠檬，请更改价格

这意味着代码将需要执行以下操作：

打开电子表格文件。
对于每一行，检查 A 列中的值是芹菜 (Celery)，大蒜 (Garlic) 还是柠檬 (Lemon)。
如果是，请更新 B 列中的价格。
将电子表格保存到新文件中（以防丢失原来的电子表格）。

#! python3
# updateProduce.py - Corrects costs in produce sales spreadsheet.

import openpyxl

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']

# The produce types and their updated prices
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}

# Loop through the rows and update the prices.
for rowNum in range(2, sheet.max_row):    # 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')