python进阶宝典10-Excel处理

# pip install openpyxl
## Excel 基本操作及读
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')     # 打开excel
print(wb.sheetnames)                            # 显示工作表列表
sheet = wb['Sheet1']                            # 按名字获取工作表
print(sheet.max_row)                            # 工作表最大行数
print(sheet.max_column)                         # 工作表最大列数
print(sheet.title)                              # 工作表名字
print(sheet)
type(sheet)
sheetb = wb.active                              # 获取当前活动工作表
print(sheetb)
print(sheet['A1'])                              # 获取Cell单元格A1
print(sheet['A1'].value)
c = sheet['B1']
print(c.value)
print('Row ' + str(c.row) + ',Column ' + c.column + ' is ' + c.value)
print('Cell ' + c.coordinate + ' is ' + c.value) 
# cell()方法取单元格
sheet.cell(row=1,column=2)                      # <Cell 'Sheet1'.B1>
print(sheet.cell(row=1,column=2).value )
for i in range(1,8,2):
    print(i,sheet.cell(row=i,column=2).value)
# 列字母与数字转换
import openpyxl
from openpyxl.utils  import get_column_letter,column_index_from_string
print(get_column_letter(1))                     # 列A
print(get_column_letter(27))                    # 列AA
print(get_column_letter(703))                   # 列AAA
print(column_index_from_string('A'))            # 列1
print(column_index_from_string('AHP'))          # 列900 
# 取行和列
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']
get_column_letter(sheet.max_column)
tuple(sheet['A1':'C3'])                         # sheet['A1':'C3']选取从A1到C3的矩形区域的Cell对象
for rowOfCellObjects in sheet['A1':'C3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate,cellObj.value )
    print('--- End of Row ---')
print('--- 下面仅显示第二列---')
for row in range(1,sheet.max_row ):             # 循环显示一列
    print(sheet['B'+str(row)].value)  
    # print(sheet.cell(row=row,column=2).value)  # 与上一句效果等同


## example1：人口普查结果计算
# Tabulates population and number of census tracts for each country.
import openpyxl,pprint
print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}
# Fill in countyData with each county's population and tracts.
#   {'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1},
#        'Aleutians West': {'pop': 5561, 'tracts': 2},
print('Reading rows...')
for row in range(2,sheet.max_row + 1):
    # Each row in the spreadsheet has data for one census tract.
    state = sheet['B'+str(row)].value
    county = sheet['C'+str(row)].value
    pop = sheet['D'+str(row)].value
    # Make sure the key of this state exists.
    countyData.setdefault(state,{})
    # Make sure the key of this county in this state exists.
    countyData[state].setdefault(county,{'tracts':0,'pop':0})
    # Each row represents one census tract,so increment by one.
    countyData[state][county]['tracts'] += 1
    # Increase the county pop by the pop in this census tract.
    countyData[state][county]['pop'] += int(pop)
# Open a new text file and write the contents of countyData to it.
print('Writing results...')
resultFile = open('census2010.py','w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')
# Read the resultdata...
import census2010
print(census2010.allData['AK']['Anchorage']['pop'])


## 写入 Excel
import openpyxl
# 修改并拷贝表
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.title = 'Spam_1'
wb.save('example_copy.xlsx')
# 创建工作表
wb = openpyxl.Workbook()
print(wb.sheetnames)
sheet = wb.active
sheet.title = 'Spam_2'
print(wb.sheetnames)
wb.create_sheet()                               # 创建sheet
print(wb.sheetnames)
wb.create_sheet(index=0,title='First sheet')    # 按参数创建sheet
wb.create_sheet(index=2,title='Middle sheet')
print(wb.sheetnames)
del wb['Middle sheet']                          # 删除sheet
sheet = wb['Sheet']
sheet['A1'] = 'Hello'                           # 修改 Cell 值
print(sheet['A1'].value)


## example2: 修改销售清单中某几个产品对应的价格
import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']
# The produce types and their updated prices
PRICE_UPDATES = {'Garlic':3.07,
                 'Celery':1.19,
                 'Lemon':1.27}
# Loop through the rows and update the prices.
for rowNum in range(2,sheet.max_row):
    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('produceSales_U.xlsx')


## 设置字体、公式、行高、列宽、合并拆分单元格
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb['Sheet']
italic24Font = Font(size=24,italic=True)        # 定义字体
sheet['A1'].font = italic24Font                 # 设置单元格字体
sheet['A1'] = 'HelloFont'
sheet['B3'].font = Font(name='Times New Roman',bold=True)  # 设置单元格字体
sheet['B3'] = 'Bold Times New Roman'
sheet['F1'] = 200
sheet['F2'] = 300
sheet['F3'] = '=sum(F1:F2)'                     # 设置公式
sheet['D4'] = 'Tall row'
sheet['E5'] = 'Wide column'
sheet.row_dimensions[4].height = 70             # 设置行高
sheet.column_dimensions['E'].width = 20         # 设置列宽
sheet.merge_cells('A6:D7')                      # 合并单元格
sheet['A6'] = 'Merge 8 cells together'
sheet.merge_cells('A8:C9')                      # 合并单元格
sheet['A8'] = 'Merge 6 cells'
sheet.unmerge_cells('A8:C9')                    # 拆分单元格
wb.save('styled.xlsx')


# 加载文件时显示公式或公式结果
import openpyxl
wb = openpyxl.load_workbook('styled.xlsx')
sheet = wb['Sheet']
print(sheet['F3'].value)
wb1 = openpyxl.load_workbook('styled.xlsx',data_only=True)
sheet1 = wb1['Sheet']
print(sheet1['F3'].value)


## 冻结窗口
# 单元格上边所有行和左边所有列冻结，但单元格所在行列不冻结
import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active
# sheet.freeze_panes = 'B1'                       # 冻结列A
# sheet.freeze_panes = 'C1'                       # 冻结列A和B
# sheet.freeze_panes = 'C2'                       # 冻结行1和列A和列B
# sheet.freeze_panes = 'A1'                       # 无冻结
# sheet.freeze_panes = None                       # 无冻结
sheet.freeze_panes = 'A2'                         # 设置第一行为冻结
wb.save('freezeExample.xlsx')


## 创建图表
import openpyxl
from openpyxl.chart import BarChart,Series,Reference
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1,11):
    sheet['A'+str(i)] = i
refObj = Reference(sheet,min_col=1,min_row=1,max_row=10,max_col=1)
chartObj = BarChart()
chartObj.type='col'                              # col为纵向,bar为横向
chartObj.title='Bar chart'
chartObj.y_axis.title='Test_y'
chartObj.x_axis.title='Test_x'
chartObj.add_data(refObj,titles_from_data=True)
sheet.add_chart(chartObj)
wb.save('sampleChart.xlsx')