1. Python处理Excel
需要运行pip install openpyxl 来安装openpyxl模块。
《Python编程快速上手 让繁琐工作自动化》一书中使用的是2.1.4 版,2018年4月27日截止通过网络安装的是2.5.3版,语法上有较大差异。
从工作簿中取得工作表:
2.1.4版
导入openpyxl模块
>>> import openpyxl
打开example.xlsx表文件
>>> wb = openpyxl.load_workbook('example.xlsx')
显示包含的表名字
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
将'Sheet3'表赋给sheet变量
>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
将当前处于活动的表赋给anotherSheet变量
>>> anotherSheet = wb.get_active_sheet()
>>> anotherSheet
<Worksheet "Sheet1">
共几行
>>> sheet.get_highest_row()
7
共几列
>>> sheet.get_highest_column()
3
取得所在列的单元格
>>> sheet.columns[1]
(<Cell Sheet1.B1>, <Cell Sheet1.B2>, <Cell Sheet1.B3>, <Cell Sheet1.B4>,
<Cell Sheet1.B5>, <Cell Sheet1.B6>, <Cell Sheet1.B7>)
2.5.3版
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> wb.sheetnames
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet3']
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>> anotherSheet = wb.active
>>> anotherSheet
<Worksheet "Sheet1">
共几行
>>> sheet.max_row
7
共几列
>>> sheet.max_column
3
如果
>>>from openpyxl.cell import get_column_letter,column_index_from_string
报以下错误:
Traceback (most recent call last):
File "<pyshell#6>", line 1, in <module>
from openpyxl.cell import get_column_letter,column_index_from_string
ImportError: cannot import name 'get_column_letter'
请改成如下命令:
>>> from openpyxl.cell.cell import get_column_letter,column_index_from_string
取得所在列的单元格,方法1:
>>> sheet['B']
(<Cell Sheet1.B1>, <Cell Sheet1.B2>, <Cell Sheet1.B3>, <Cell Sheet1.B4>,
<Cell Sheet1.B5>, <Cell Sheet1.B6>, <Cell Sheet1.B7>)
取得所在列的单元格,方法2:
>>> list(sheet.columns)[1]
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
取得所在行的单元格
>>> sheet[1]
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
取得A1单元格,行从1开始,列从0开始
>>> sheet[1][0]
<Cell 'Sheet1'.A1>