python_excel学习笔记

# -*- coding: utf-8 -*-
"""
Created on Sun Aug 11 11:07:01 2019

@author: Administrator
"""
#python_excel项目
#import  openpyxl
#wb =  openpyxl.load_workbook('f:\第一个测试文件.xlsx')#读取已经有的excel文件
#openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.
#getting sheets from the workbook
# =============================================================================
# print(wb.sheetnames)
# #遍历表单
# for sheet in wb:
#     print(sheet.title)
#     print()#添加空白行
# #生成一张表单
# mysheet=wb.create_sheet('mysheet')
# print(wb.sheetnames)
# =============================================================================
# =============================================================================
# #对第二张表单进行操作、引用
# sheet2=wb.get_sheet_by_name('书籍列表')
# #现在sheet2就是第二张表单这个对象
# #另一种方法引用表单sheet
# sheet3=wb['mysheet']
# =============================================================================
#getting cells from the sheets
#ws=wb.active#默认第一个
# =============================================================================
# print(ws)#所有的对象都可以打印出来看一看
# print(ws['A1'])#该单元格
# print(ws['A1'].value)#该单元格的内容
# 
# c=ws['B1']
# =============================================================================
# =============================================================================
# print('Row{},Column{} is {}'.format(c.row,c.column,c.value))
# #好优美的代码
# print('Cell {} is {}'.format(c.coordinate,c.value))
# 
# print(ws.cell(row=1,column=2))
# print(ws.cell(row=1,column=2).value)#exel里行列都是从1开始
# #不输入.value输出位置，输入.value输出内容
# =============================================================================
# =============================================================================
# for i in range(1,6,2):
#     for  j in range (1,3):
#         print(i,j,ws.cell(row=i,column=j).value)#想多打印什么，加个逗号继续写就行
#     
# =============================================================================
#getting rows and columns from the sheets
# =============================================================================
# colC=ws['C']
# print(colC)
# print(colC[2].value)
# row6=ws[6]
# print(row6)
# print(row6[1].value)
# 
# =============================================================================
# col_range=ws['B:C']
# row_range=ws[2:6]
# =============================================================================
# =============================================================================

# =============================================================================
#切片
# for col in col_range:
#     for cell in col:
#         print(cell.value)
# =============================================================================
#此处还没有用到row_range,所以整个列都输出了
# =============================================================================
# for row in row_range:
#     for cell in row:
#         print(cell.value)      
# =============================================================================
#此处还没有用到col_range,所以整个行都输出了
#访问第一行，第二行
# =============================================================================
# for row in ws.iter_rows(min_row=1,max_row=2,max_col=2):
#     for cell in row:
#         print(cell)
#         print(cell.value) 
# =============================================================================
#print(tuple(ws.rows))
#自动化要用循环来做，所以要对他的每一个部分进行控制
#cell_range=ws['A1:C6']
#for rowofcellobjects in cell_range:
#    for cellobj in rowofcellobjects:
#        print(cellobj.coordinate,cellobj.value)
#    print('---------------end of row----------')
#print('{}行*{}列'.format(ws.max_row,ws.max_column))
#import openpyxl
#wb=openpyxl.load_workbook('f:\第一个测试文件.xlsx')#读取已经有的excel文件
#ws=wb.active#默认第一个
#from openpyxl.utils import get_column_letter,column_index_from_string
#print(get_column_letter(500),get_column_letter(261))
#print(column_index_from_string('SB'),column_index_from_string('FG'))
#已经定义的对象不需要加引号，数字不需要加引号，否则加引号就变成了字符串
#import openpyxl,pprint
#C:\Users\Administrator.USER-20190710UX\.spyder-py3\pachong\pythonabc\censuspopdata.xlsx
#数据结构,编程是围绕数据结构来编程的
'''
{'AL':{'Autauga':{tract:12,'pop':1912200}
      
       

       'Baldwin':{}
       'Bibb':{}   
       }
 'AK':{
 
       }
 CountryData['AL']['Autauga']['tract']
}
'''
##read the spreadsheet data
#print('Opening Workbook')
#wb=openpyxl.load_workbook('f:\censuspopdata.xlsx')
#sheet=wb.active
#
#countryData={}
#
##fill in countryData with each city's pop and tract
#for row in range(2,sheet.max_row+1):
#    
#    #each row in the spreadsheet has data
#    state=sheet['B'+str(row)].value
#    #print(state)
#    country=sheet['c'+str(row)].value
#    pop=sheet['D'+str(row)].value
#    
#    #makesure the  key state exists
#    countryData.setdefault(state,{})
#    #make sure the key for country in state exists 
#    countryData[state].setdefault(country,{'tracts':0,'pop':0})
#    #ezch row represents one census tract,so increment by one
#    countryData[state][country]['tracts']+=1
#    #increase the country pop by the pop in this census tract
#    countryData[state][country]['pop']+=int(pop)
#    
##open a new text file and write the contents from countryData to it
#print('writing results...')
#resultFile=open('census2010.py','w')
#resultFile.write('allData='+pprint.pformat(countryData))
#print('wrote results!')
#resultFile=open()
##如何利用这个数据
import census2010
print(census2010.allData['AK']['Anchorage']['pop'])