openpyxl读/写Excel XLSX文件

写：

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import  sys
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
from openpyxl.writer.excel import ExcelWriter 

#创建一个新的工作簿
wb = Workbook()
#获取默认sheet
ws = wb.active
ws.title = "DefaultSheet"
#新建sheet  - 默认位置
ws1 = wb.create_sheet("NewSheet")
#新建sheet  - 放在第一个
ws2 = wb.create_sheet("FirstSheet",0)
#根据sheet名称获取工作区
ws3 = wb["FirstSheet"]

#样式设置
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Fill
from copy import copy
from openpyxl.styles import colors, Color

#字体
font = Font(color='000000')
a1 = ws['A1']
a2 = ws['D4']

font2 = copy(font)
font2.bold = True

a1.font = font
a2.font = font2

#单元格赋值
a1.value = 'hello'
a2.value = 'hi'

from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting import Rule
dxf = DifferentialStyle(font=Font(bold=True), fill=PatternFill(start_color='EE1111', end_color='EE1111'))
rule = Rule(type='cellIs', dxf=dxf, formula=["10"])
ws2.conditional_formatting.add('A1:F40', rule)

for i in xrange(1,10):
    index = 'B'+str(i)
    print index
    ws2.cell('B'+str(i)).value = 'TEST' + str(i)
    ws2.cell('B'+str(i)).font = Font(size=14)

ws2.oddHeader.left.color = "CC3366"

wb.save('/Users/wangpingyang/Practice/excel_demo.xlsx')

读：

baseXMLPath = '/Users/wangpingyang/Practice/excel_demo.xlsx'
wb = load_workbook(baseXMLPath)
ws = wbYesterday.get_sheet_by_name('Default')