chapter12——working with excel spreadsheet（1）

import openpyxl
wb = openpyxl.load_workbook('filename.xlsx')
sheet = wb['Sheetname']
# sheet = wb.get_sheet_by_name('Sheetname') 的方法已经弃用
one_cell = sheet['A1']
cell_range = sheet['A1':'C2']
# 获得单个数据和多个数据的方法
sheet['A1'].value #获得A1的数值
sheet['A']

#There is also the Worksheet.cell() method.
#This provides access to cells using row and column notation:
d = ws.cell(row=4, column=2, value=10)

sheet = wb.get_active_sheet()
# 得到workbook活跃的sheet页表
sheet.max_column
# sheet.get_highest_column()的方法已经弃用
sheet.max_row
# sheet.get_highest_row()的方法已经弃用

>>>from openpyxl.utils import get_column_letter,column_index_from_string
# 实现数字1,2,3,4...和字母A，B，C，D之间的相互转换
>>>get_column_letter(1)
'A'
>>>get_column_letter(2)
'B'
>>>get_column_letter(33)
'AG'
>>>column_index_from_string('A')
1
>>>column_index_from_string('d')
4

tuple(sheet['A1':'C3']) #excel矩阵的获取，可用于for循环
result：
((<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)
        #coordinate获取当前表格的坐标值，例如Apples对应在B1表格中
    print('-----END of ROW----')
result:
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----

# python3
# readCensusExcel.py - Tabulates population and number of census tracts for each conuty
import openpyxl,pprint
wb = openpyxl.load_workbook('censuspopdata.xlsx')
# 载入workbook，workbook需放置在当前project文件中
sheet = wb['Population by Census Tract']
# 选择活跃sheet
countydata = {}
for row in range(2,sheet.max_row+1):
    # range(1,i)输出的是1,2,3...,i-1,因此要+1
    state = sheet['B'+str(row)].value
    county = sheet['C'+str(row)].value
    pop = sheet['D'+str(row)].value 
    # setdefault() 函数和 get()方法 类似, 如果键不存在于字典中，将会添加键并将值设为默认值
    # dict.setdefault(key, default=None) 使用该函数，可以有效避免数据不存在时，发生错误。
    # 因为只有当键存在时，才添加值。
    # make sure the key for this state exist
    countydata.setdefault(state,{})
    # make sure the key for this county in this state exists
    countydata[state].setdefault(county,{'tract':0,'pop':0})
    
    # each row represent one census tract， so increment by one
    countydata[state][county]['tract'] +=1
    # increase the county pop by the pop in this census tract
    countydata[state][county]['pop'] += int(pop)
# 打开一个新文件将countydata写入
resultFile = open('census2010.py','w')
resultFile.write('allData=' + pprint.pformat(countydata))
# 将countydata字典数据存入census2010.py的python代码中，并放在allData字典中，以方便今后调用
resultFile.close()

import os
os.chdir(r'C:\Users\Administrator')
# os.chdir(path) 方法用于改变当前工作目录到指定的路径，需要先import os
import census2010
census2010.allData['AK']['Anchorage']
result:
{'pop': 291826, 'tract': 55}
anchoragePop = census2010.allData['AK']['Anchorage']['pop']
print(str(anchoragePop))
result:
291826

help(openpyxl)查看模块信息，从中可以找到模块网站，有进一步的解释

Help on package openpyxl:

NAME
    openpyxl - # Copyright (c) 2010-2018 openpyxl

PACKAGE CONTENTS
    _constants
    cell (package)
    chart (package)
    chartsheet (package)
    comments (package)
    compat (package)
    conftest
    descriptors (package)
    drawing (package)
    formatting (package)
    formula (package)
    packaging (package)
    pivot (package)
    reader (package)
    styles (package)
    utils (package)
    workbook (package)
    worksheet (package)
    writer (package)
    xml (package)

SUBMODULES
    constants

DATA
    DEFUSEDXML = True
    LXML = True
    NUMPY = True
    PANDAS = True
    __author_email__ = 'charlie.clark@clark-consulting.eu'
    __license__ = 'MIT/Expat'
    __maintainer_email__ = 'openpyxl-users@googlegroups.com'
    __url__ = 'https://openpyxl.readthedocs.io'

VERSION
    2.5.12

AUTHOR
    See AUTHORS

FILE
    c:\users\administrator\anaconda3\lib\site-packages\openpyxl\__init__.py