使用OpenPyXl写Excel文件

创建和保存表格

import openpyxl
from os import path

wb = openpyxl.Workbook()
wb.sheetnames

['Sheet']

sheet = wb.active
sheet.title

'Export Summary'

sheet.title = 'Hello World Sheet'
wb.sheetnames

['Hello World Sheet']

创建和移除工作表

wb = openpyxl.load_workbook(path.join('Chapter12-Write Excel Documents', 'example.xlsx'))
sheet = wb.active
sheet.title = 'Spam Spam Spam'
wb.save(path.join('Chapter12-Write Excel Documents', 'example_copy.xlsx'))

wb = openpyxl.load_workbook(path.join('Chapter12-Write Excel Documents', 'example_copy.xlsx'))
wb.sheetnames

['Spam Spam Spam', 'Sheet 1']

wb.create_sheet(index=0, title='First Sheet')
wb.create_sheet(index=1, title='Second Sheet')
wb.sheetnames

['First Sheet', 'Second Sheet', 'Spam Spam Spam', 'Sheet 1']

wb.remove(wb['Doggy3'])
wb.sheetnames

['First Sheet', 'Second Sheet']

在单元格中写值

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

'Hello world!'

练手项目：更新一个电子表格

在这个项目中，你将会写一个程序来更新一个产品销售电子表格中的单元格。你的项目将会游览这个表格，寻找特定的产品，并且更新他们的价格。
garlic, celery, lemons
你的项目做这些事：

• 遍历所有的列
• 如果这列的产品是大蒜（garlic）,芹菜(celery),或者柠檬（lemons),改变价格

你的代码将会做这些事：

• 打开电子表格
• 遍历每一行，检查它列A的值是不是Celery, Garlic, 或者Lemon.
• 如果是，更新B列的价格
• 保存这个表到一个新的文件

wb = openpyxl.load_workbook(path.join('Chapter12-Write Excel Documents', 'produceSales.xlsx'))

# Open the spreadsheet file and get the target worksheet 
wb.sheetnames
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 row_num in range(2, sheet.max_row):
    produce_name = sheet.cell(row = row_num, column = 1).value
    if produce_name in PRICE_UPDATES:
        sheet.cell(row=row_num, column=2).value = PRICE_UPDATES[produce_name]

# Save the file
wb.save(path.join('Chapter12-Write Excel Documents', 'updatedProduceSales.xlsx'))

---

参考文献:
[1] Automate the Boring Stuff with Python, J Baumann# 使用OpenPyXl写Excel文件