Python处理Excel文档，个人总结，基于《python编程快速上手——让繁琐的工作自动化》

1.用openpyxl模块打开Excel文档

import openpyxl
>>> wb=openpyxl.load_workbook('C:\MyPythonScripts\example.xlsx')#由于路径有问题，尝试了好多次，后来决定使用绝对路径，绝对路径最好不要有中文
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>

绝对路径

2.从工作簿中取得工作表

注意：这里和教材的内容有出入，可能只是代码版本过时问题，没有深究，且IDLE里面也会提示应当使用何种代码
可参考：【DeprecationWarning】openpyxl遍历所有sheet遇到【‘list’ object is not callable】
【python】openpyxl中错误警告UserWarning: Call to deprecated function的原因

>>> wb.get_sheet_names()
Warning (from warnings module):
  File "<pyshell#6>", line 1
DeprecationWarning: Call to deprecated function get_sheet_names (Use wb.sheetnames).
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet=wb.get_sheet_by_name('Sheet3')
Warning (from warnings module):
  File "<pyshell#7>", line 1
DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
>>> sheet=wb["Sheet3"]
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>> anotherSheet=wb.get_active_sheet()
Warning (from warnings module):
  File "<pyshell#12>", line 1
DeprecationWarning: Call to deprecated function get_active_sheet (Use the .active property).
>>> anotherSheet=wb.active#取得工作簿的活动表
>>> anotherSheet
<Worksheet "Sheet1">

3.从表中取得单元格

>>>import openpyxl
>>> wb=openpyxl.load_workbook('C:\MyPythonScripts\example.xlsx')#
>>> sheet=wb["Sheet1"]#调用“Sheet1”
>>> sheet['A1']
<Cell 'Sheet1'.A1>
>>> sheet['A1'].value#可查找出对应单元格内容，以列表形式展现
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c=sheet['B1']
>>> c.value#可查找出对应单元格内容，以列表形式展现
'Apples'
>>> c.row#行号
1
>>> c.column#列号
2
>>>'Row'+str(int(c.row))+',Column'+str(int(c.column))+'is'+c.value
'Row1,Column2isApples'#注意加空格，不然不好看，像这样。。。
>>> c.coordinate#定位
'B1'
>>> 'Cell'+c.coordinate+' is '+c.value
'CellB1 is Apples'
>>> sheet['C1'].value
73

##下面使用cell()办法，获得和Sheet['B1']一样的结果
>>> import openpyxl
>>> wb=openpyxl.load_workbook('C:\MyPythonScripts\example.xlsx')
>>> sheet=wb["Sheet1"]
>>> sheet.cell(row=1,column=2)
<Cell 'Sheet1'.B1>
>>> sheet.cell(row=1,column=2).value
'Apples'
>>> for i in range(1,8,2):#编写for循环，打印出一系列单元格的值              ##例如：B列打印出所有奇数行的值

       print(i,sheet.cell(row=i,column=2).value)

1 Apples
3 Pears
5 Apples
7 Strawberries

##确认表的大小
 >>> sheet=wb["Sheet1"]
>>> sheet.get_highest_row()#注意这一步书上的程序有问题，详见下面
Traceback (most recent call last):
  File "<pyshell#9>", line 1, in <module>
    sheet.get_highest_row()
AttributeError: 'Worksheet' object has no attribute 'get_highest_row'
>>> sheet.max_row
7
>>> sheet.max_column
3

*新版的openpyxl中已重写，所以报错，具体参照了：
python3 调用get_highest_row()错误

——截至3.15

4.列字母和数字间的转换

column_index_from_string() 列字母转换为数字
get_column_letter() 列数字转换为字母
注意：这里教材上使用的是旧版本的函数导入，我使用的Python3.8会报错，没试过其他的，具体参考下方博文：
Python错误：ImportError: cannot import name get_column_letter 解决办法
代码如下，已使用新版本语言：

>>> import openpyxl
>>> from openpyxl.utils import get_column_letter,column_index_from_string
#注意，上面使用的是utils
>>> get_column_letter(1)
'A'
>>> get_column_letter(2)
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb=openpyxl.load_workbook('C:\MyPythonScripts\example.xlsx')
>>> sheet=wb["Sheet1"]
>>> get_column_letter(sheet.max_column()) #这里错误在于多写了个括号，等本章学完，我梳理一下涉及本章变更的语言
Traceback (most recent call last):
  File "<pyshell#9>", line 1, in <module>
    get_column_letter(sheet.max_column())
TypeError: 'int' object is not callable
>>> get_column_letter(sheet.max_column)#目的在于将该表格最大列号以字母形式输出
'C'
>>> column_index_from_string('A')
1
>>> column_index_from_string('AA')
27
>>> 

5.从表种取得行和列

切片，取得某行、列、矩形区域

sheet=wb['Sheet1']
>>>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 row0fCell0bjects in sheet['A1':'C3']:
        for cell0bj in row0fCell0bjects:
             print(cell0bj.coordinate,cell0bj.value)
             print('---END OF ROW---')#这里忘了退一格了，有出入，不影响
     
 A1 2015-04-05 13:34:02
---END OF ROW---
B1 Apples
---END OF ROW---
C1 73
---END OF ROW---
A2 2015-04-05 03:41:23
---END OF ROW---
B2 Cherries
---END OF ROW---
C2 85
---END OF ROW---
A3 2015-04-06 12:46:51
---END OF ROW---
B3 Pears
---END OF ROW---
C3 14
---END OF ROW---

下面是内部的分段单独测试：

>>> for row0fCell0bjects in sheet['A1':'C3']:#单独测试展开的样子
       print(row0fCell0bjects)

(<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 row0fCell0bjects in sheet['A1':'C4']: #这里测试了一下，好像不使用tuple也可以****🕵️‍待验证****
        for cell0bj in row0fCell0bjects:
               print(cell0bj.coordinate,cell0bj.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---
A4 2015-04-08 08:59:43
B4 Oranges
C4 52
---END OF ROW---

外层for循环遍历切片中每一行，内层for循环针对每一行遍历该行中的单元格
以上↑

访问特定行或列的单元格的值（使用columns和rows属性）
又是一个版本不一致 openpyxl使用sheet.rows或sheet.columns报TypeError: ‘generator’ object is not subscriptable解决方式…

>>> import openpyxl
>>> wb=openpyxl.load_workbook('C:\MyPythonScripts\example.xlsx')
>>> sheet=wb.active
>>> sheet.columns[1]##这里是课本上的，但新版本与此不符合，因此报错
Traceback (most recent call last):
  File "<pyshell#3>", line 1, in <module>
    sheet.columns[1]
TypeError: 'generator' object is not subscriptable
>>> list(sheet.columns)[1]#新版本使用list
(<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
>>> list(sheet.columns)[0]#注意，使用columns和rows属性，下标都是从0开始
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>, <Cell 'Sheet1'.A5>, <Cell 'Sheet1'.A6>, <Cell 'Sheet1'.A7>)
>>>(sheet.rows)[0]
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
>>> for cellObj in list(sheet.rows)[0]:
 print(cellObj.value)

2015-04-05 13:34:02
Apples
73