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')
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容