python第8次打卡

#更换路径

import os
os.chdir(r’C:\Users\元素\Desktop’)

#打开文档

import openpyxl
wb=openpyxl.load_workbook(‘example.xlsx’)

#课本上sheet=wb.get_sheet_by_name(‘Sheet1’) 报错
##应该是

sheet=wb[‘Sheet1’]

#方法1：

sheet[‘A1’]
<Cell ‘Sheet1’.A1>
sheet[‘A1’].value
‘4/5/2015 1:34:02 PM’

##方法2：

sheet.cell(row=2,column=2)
<Cell ‘Sheet1’.B2>
sheet.cell(row=2,column=2).value
‘樱桃’

##数字-字母转化
##数字转字母

get_column_letter(20)
‘T’
get_column_letter(220)
‘HL’
get_column_letter(1220)
‘ATX’

##字母转数字

column_index_from_string(‘BGF’)
1540

##获取切片数据

for rowOfCellObjects in sheet[‘A1’:‘C3’]:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate,cellObj.value)
print(‘END OF ROW’)
A1 4/5/2015 1:34:02 PM
B1 苹果
C1 74
END OF ROW
A2 4/5/2015 2:54:03 PM
B2 樱桃
C2 67
END OF ROW
A3 4/5/2015 7:24:04 PM
B3 梨
C3 44
END OF ROW

##获取列表

for cellObj in sheet[“B”]:
print(cellObj.value)
##或是
for cellObj in list(sheet.columns)[1]:
print(cellObj.value)

##实践

import openpyxl,pprint
print(‘打开工作表’)
wb=openpyxl.load_workbook(‘censuspopdata.xlsx’)
sheet=wb[‘Population by Census Tract’]
countyData={}

print(‘读取行’)
##课本旧内容：for row in range(2,sheet.get_highest_row()+1):
for row in range(2,sheet.max_row+1):
state=sheet[‘B’+str(row)].value
county=sheet[‘C’+str(row)].value
pop=sheet[‘D’+str(row)].value
countyData.setdefault(state,{})
countyData[state].setdefault(county,{‘tracts’:0,‘pop’:0})
countyData[state][county][‘tracts’]+=1
countyData[state][county][‘pop’]+=int(pop)
print(‘写入结果中…’)
resultFile=open(‘census2010.py’,‘w’)
resultFile.write(‘allData=’+pprint.pformat(countyData))
resultFile.close()
print(‘Done.’)

##创建并保存工作表

import os,openpyxl
os.chdir(r’C:\Users\元素\Desktop’)
wb=openpyxl.Workbook()
sheet.title=‘hello’
wb.get_sheet_names()
[‘Sheet’]

sheet=wb.get_active_sheet()
sheet.title
‘Sheet’

sheet.title=‘hello’
wb.get_sheet_names()
[‘hello’]

wb=openpyxl.load_Workbook(‘epp.xlsx’)
sheet=wb.get_active_sheet()
sheet.title=‘spam’
wb.save(‘epp_copy.xlsx’)

##创建工作表

import openpyxl
wb = openpyxl.Workbook()
wb.get_sheet_names()
[‘Sheet’]

wb.create_sheet()
<Workbook “Sheet1”>
wb.get_sheet_names()
[‘Sheet’, ‘Sheet1’]
wb.create_sheet(index=0, title=‘First Sheet’)
<Worksheet “First Sheet”>
wb.get_sheet_names()
[‘First Sheet’,‘Sheet’,‘Sheet1’]
wb.create_sheet(index=2, title=‘Middle Sheet’)
<Workbook “Middle Sheet”>
wb.get_sheet_names()
[‘First Sheet’,‘Sheet’,‘Middle Sheet’,‘Sheet1’]

##删除工作表

wb.remove_sheet(wb.get_sheet_by_name(‘Sheet1’))
wb.get_sheet_names()
[‘First Sheet’,‘Sheet’,‘Middle Sheet’]

##写入单元格

wb=openpyxl.Workbook()
sheet=wb.get_sheet_by_name(‘Sheet’)
sheet[‘A1’]=‘Hello world!’
sheet[‘A1’].value
‘Hello world’

##更新数据

import os,openpyxl
wb=openpyxl.load_workbook(‘produceSales.xlsx’)
sheet=wb.get_sheet_by_name(‘Sheet’)
PRICE_UPDATES={‘Garlic’:3.07,
‘Celery’:1.19,
‘Lemon’:1.27}
for rowNum in range(2,sheet.max_row+1):
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’)

##后面内容对个人来说作用不大，手动调整就很快了，所以就不做了