chapter12——working with excel spreadsheet（2）

Project：更新excel表格单元

import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']
# 要更新的内容
PRICE_UPDATES = {
    'Garlic':3.07,
    'Celery':1.19,
    'Lemon':1.27,
}
for rowNum in range(2, sheet.max_row+1):
	# 读取第一列数据，得到ProduceName和excel单元格的联系
    produceName = sheet.cell(row=rowNum, column=1).value
    # 若produceName在需更新的字典PRICE_UPDATES中，就将对应的第二列数据更新。
    if produceName in PRICE_UPDATES:
        sheet.cell(row=rowNum, column=2).value=PRICE_UPDATES[produceName]
wb.save('updateProduceSales.xlsx')

python中excel表格的应用非常灵活的运用了列表、元组、字典的概念！
该程序的逻辑思路

• 打开spreadsheet文件
• 对于每一列，检查第A列的值是否在更新字典中
• 如果是，就将B列的值更新
• 保存spreadsheet文件

Project： 设置单元格字体

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'] = 'Hello world!'
wb.save('styled.xlsx')

Keyword argument	Data Type	Description
name	String	The font name，such as ‘Calibri’ or ‘Times New Roman’
size	Integer	The point size
bold	Boolean	True, for bold font
italic	Boolean	True, for italic font

import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb['Sheet']
fontObj1 = Font(name='Time New Roman', bold=True)
sheet['A1'].font = fontObj1
sheet['A1'] = 'Bold Time New Roman'

fontObj2 = Font(size=24, italic=True)
sheet['B3'].font = fontObj2
sheet['B3'] = '24 pt Italic'
wb.save('styles.xlsx')

Project 单元格公式计算

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

显示该单元格公式

import openpyxl
wbFormulas = openpyxl.load_workbook('writeFormula.xlsx')
sheet = wbFormulas.active
sheet['A3'].value
result：
'=SUM(A1:A2)'

显示该单元格数值，但未成功，该算法没掌握

wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx',data_only=True)
print(wbDataOnly)
sheet = wbDataOnly.active
print(sheet['A3'].value)
result：
None

Project 调整行和列

1. 设置行高和列宽

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Width column'
# 得到Rowdimensions可以设置高度
sheet.row_dimensions[1].height = 70
# 得到Columndimensions可以设置宽度
sheet.column_dimensions['B'].width = 20
# 对于O高行或0宽的列excel可以隐藏
wb.save('dimension.xlsx')

2. 单元格合并和分开

# 合并
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
# merge_cells将excel选中矩阵合并为一个单元格，坐标为开始单元格
sheet.merge_cells('A1:D3')
sheet['A1'] = 'Twelve cells merged together.'
sheet.merge_cells('C5:D5')
sheet['C5'] = 'Two merged cells.'
wb.save('merged.xlsx')

# 分开
import openpyxl
wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb.active
sheet.unmerge_cells('A1:D3')
sheet.unmerge_cells('C5:D5')
wb.save('merged.xlsx'

3. 冻住指定行或列，使其始终可见
   指定位置的上一行和左侧一列会被冻住，但指定位置所在的行或列不会冻住。

import openpyxl
wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb.active
sheet.freeze_panes = 'A2'
wb.save('freezeExample.xlsx')

4. 表格

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1,11):
    # 新建数值从A1到A10:1~10
    sheet['A'+str(i)] = i
# 选择sheet，选择（1,1）到（10,1）矩阵中数据
refObj = openpyxl.chart.Reference(sheet,min_col=1, min_row=1, max_col=1, max_row=10)
# 添加数据标题‘First series’
seriesObj = openpyxl.chart.Series(refObj,title='First series')
# 调用BarChart函数，开始绘图
chartObj = openpyxl.chart.BarChart()
# 添加数据
chartObj.append(seriesObj)
# 设置标题
chartObj.title = 'First Series'
# 设置表格位置
chartObj.top = 5
chartObj.left = 10
# 设置表格大小
chartObj.width = 30
chartObj.height = 20
# 添加表格到sheet中
sheet.add_chart(chartObj)
wb.save('sampleChart.xlsx')