Python新手学习（十一）：处理EXCEL表格

13.处理EXCEL电子表格
  1）EXCEL文档概念 工作簿->工作表->行列->单元格。
  2）openpyxl模块安装
  3）读取EXCEL文档
    打开EXCEL文档
      openpyxl.load_workbook(‘xxx.xlsx’)
    取得工作表
      wb.sheetnames
      WorkSheet sheet = wb[‘Sheet1’]
      sheet.title     表的名称
      wb.active        当前活动表
    取得单元格
      Cell cl = sheet[‘A1’]
      c.value c.row c.column c.coordinate 值、行、列、座标
      sheet.cell(row=1,column=2) 得到单元格
      sheet.max_row sheet.max_column
    列字母和数字之间的转换
      openpyxl.utils.column_index_form_string()
      openpyxl.utils.get_column_letter()
    从表中取得多个行和列
      GenericAlias tuple[sheet[‘A1’:’C3’]]
      rowOfCellObjects = list(sheet.rows)[1]
      column= list(sheet.columns)[1]
    测试程序:test_1301.py

import openpyxl

wb = openpyxl.load_workbook('d:/temp/example.xlsx')
sheet = wb['Sheet1']

#tuple(sheet['A1':'C3'])

for rowOfCellObjects in sheet['A1':'C3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate,cellObj.value)
    print('---END OF ROW  ---')

print('---Done  ---')

    工作簿Workbook、工作表Worksheet、单元格Cell 
  4）项目：从电子表格中读取数据
    字典类型，setdefault（）
    测试程序：test_1302.py

#! python3
# readCensusExcel.py - Tabulates population and number of census tracts for each county.

import openpyxl,pprint,os

os.chdir('d:/temp')

print('Opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population']
countyData = {}

# Fill in countyData with each county's population and tracts.
print('Reading rows...')
for row in range(2,sheet.max_row + 1):
    # Each row in the spreadsheet has data for on census tract.
    state = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop = sheet['D' + str(row)].value

    # Make sure the key for this state exists.
    countyData.setdefault(state,{})
    # Make sure the key for this county in this state exist.
    countyData[state].setdefault(county,{'tracts':0,'pop':0})
    # Each row represents on 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 new text file and write the contents of countyData to it.
print('Write results...')
resultFile = open('census2010.py','w')
resultFile.write('allData =' + pprint.pformat(countyData))
resultFile.close()
print('Done')

  5）写入EXCEL表格
    openpyxl.Workbook() 创建一个空工作簿对象
    wb.save(filename) 保存工作簿
    wb.create_sheet（）创建工作表
    del wb[sheetname] 删除工作表
    sheet[‘A1’]=value 赋值
  6）项目：更新电子表格
    测试程序test_1303.py

#! python3
# UpdateProduce.py - Corrects costs in produce sales spreadsheet.

import openpyxl,os
from openpyxl.styles import Font,colors

os.chdir('d:/temp')

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 }
cellFont = Font(name='times New ROman',bold=True,underline='single',color='FF8F00')

# Loop through the rows and update the prices.
for rowNum in range(2,sheet.max_row + 1):   #skip the first row
    produceName = sheet.cell(row=rowNum,column=1).value
    if produceName in PRICE_UPDATES:
        sheet.cell(row=rowNum,column=2).value = PRICE_UPDATES[produceName]
        sheet.cell(row=rowNum,column=1).font = cellFont
        sheet.row_dimensions[rowNum].height = 20

sumRowNum = sheet.max_row + 1
sheet.merge_cells('A%d:C%d' % (sumRowNum,sumRowNum))
sheet.cell(sumRowNum ,column=1).value = 'SUMMARY:'
sheet.cell(sumRowNum ,column=4).value = '=SUM(D2:D%s)' % str(sumRowNum -1)
sheet.freeze_panes = 'A2'
sheet.row_dimensions[sumRowNum].height =20
sheet.cell(row=sumRowNum,column=1).font = cellFont

wb.save('newProduceSales.xlsx')

  7）设置单元格的字体风格
    openpyxl.styles.Font
  8）Font对象
    name：字体名 size：字号 bold：加粗 italic：斜体
  9）公式
    sheet[‘A1’].value=’=SUM(A2:C2)’
  10）调整行和列
    sheet.rown_dimension[1].height 调整行高
    sheet.column_dimension[‘A’.width 调整列宽
    sheet.merge_cells（‘A1:D3’） 合并单元格。
    sheet.unmerge_cells() 拆分单元格
    sheet.freeze_panes = ‘A2’ 冻结单元格。=None是解冻
  11）图表
    测试程序：test_1304.py

import openpyxl,random

wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1,11):       # create some data in column A
    sheet['A'+str(i)] = random.randint(1,10)
    sheet['B'+str(i)] = i

refObj = openpyxl.chart.Reference(sheet,min_col=1,min_row=1,max_col=1,max_row=10)
seriesObj = openpyxl.chart.Series(refObj,title='first series')

barChartObj = openpyxl.chart.BarChart()
barChartObj.title = 'My BarChart'
barChartObj.append(seriesObj)
sheet.add_chart(barChartObj,'C5')

lineChartObj = openpyxl.chart.LineChart()
lineChartObj.title = 'My lineChart'
lineChartObj.append(seriesObj)
sheet.add_chart(lineChartObj,'J5')

scatterChartObj = openpyxl.chart.ScatterChart()
scatterChartObj.title = 'My ScatterChart'
refObjy = openpyxl.chart.Reference(sheet,min_col=2,min_row=1,max_col=2,max_row=10)
scSeriesObj = openpyxl.chart.Series(refObj,refObjy,title='first series')
scSeriesObj.marker.symbol = "circle"
scSeriesObj.marker.graphicalProperties.solidFill = "FF00FF"  # 点的内部填充颜色
scSeriesObj.marker.graphicalProperties.line.solidFill = "FF0000"  #点的外边框颜色
scSeriesObj.graphicalProperties.line.noFill = True
scatterChartObj.append(scSeriesObj)
sheet.add_chart(scatterChartObj,'C21')

pieChartObj = openpyxl.chart.PieChart()
pieChartObj.title = 'My PieChart'
pieChartObj.append(seriesObj)
sheet.add_chart(pieChartObj,'J21')

wb.save('d:/temp/sampleChart.xlsx')

    Reference 数据对象  Series 数据序列对象 Chart 图表对象
    Sheet 工作表对象
    BarChart 条形图 LineChart 折线图 PieChart 饼图 ScatterChart 散点图