python3.5操作office Excel 表 (二)

Write a workbook：

from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = ‘testexcel.xlsx‘

ws1 = wb.active
ws1.title = "range names"

for row in range(1,40):
ws1.append(range(600))

ws2 = wb.create_sheet(title="Pi")

ws2[‘F5‘] = 3.14

ws3 = wb.create_sheet(title="Data")
for row in range(10,20):
    for col in range(27,54):
      _ = ws3.cell(column=col,row=row,value="{0}".format(get_column_letter(col)))

print(ws3[‘AA10‘].value)

wb.save(filename = dest_filename)

Read an existing workbook:

from openpyxl import load_workbook
wb = load_workbook(filename=‘testexcel.xlsx‘)
sheet_ranges = wb[‘range names‘]
print(sheet_ranges[‘D18‘].value)

在load_workbook中可使用:
在读取单元格时，guess_types将启用或禁用（默认）类型推断。

data_only控制具有公式的单元格是具有公式（默认）还是上次Excel读取工作表时存储的值。

keep_vba控制是否保留任何Visual Basic元素（默认）。如果他们被保留，他们仍然是不可编辑的。

警告:
openpyxl当前不会读取Excel文件中的所有可能项目，因此如果打开并保存相同名称，图像和图表将从现有文件中丢失。

###使用数字格式(Using number formats):

import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws[‘A1‘] = datetime.datetime(2018,6,8)

print(ws[‘A1‘].number_format)

wb.guess_types = True

ws[‘B1‘] = ‘3.14%‘
wb.guess_types = False

print(ws[‘B1‘].value)
print(ws[‘B1‘].number_format)

###使用公式(Using formulae):

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws["A1"] = "=SUM(1,1)"
wb.save("formula.xlsx")

openpyxl从不评估公式，但可以检查公式的名称：

from openpyxl.utils import FORMULAE
print("HEX2DEC" in FORMULAE)

注意:
如果要使用未知的公式，这些公式必须以_xlfn作为前缀。

###合并/取消合并单元格
（Merge / Unmerge cells）

合并单元格时，将从工作表中删除左上角的所有单元格。
格式化合并单元格的信息，参看样式合并单元格。

合并单元格:

from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells(‘A2:D2‘)
#A2:D2合并单元格
ws.merge_cells(start_row=2,start_column=1,end_row=4,end_column=4)
wb.save(‘table\\merge.xlsx‘)

取消合并单元格:

from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

ws.merge_cells(‘A2:D2‘)
#
ws.unmerge_cells(‘A2:D2‘)

ws.merge_cells(start_row=2,start_column=1,end_row=4,end_column=4)

ws.unmerge_cells(start_row=2,start_column=1,end_row=4,end_column=4)

wb.save(‘table\\merge.xlsx‘)

###插入图像(Inserting an image)
from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
ws[‘A1‘] = ‘learning for me happy‘

img = Image(‘images.PNG‘)

ws.add_image(img,‘A1‘)
wb.save(‘image.xlsx‘)

###Fold columns(outline)折叠列(大纲)

import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.column_dimensions.group(‘A‘,‘D‘,hidden=True)
wb.save(‘table\\group.xlsx‘)

###格式化单元格
使用样式(Working with styles):
介绍(Introduction)

样式用于在屏幕上显示时更改数据的外观。它们也用于确定数字的格式。

样式可以应用于以下几个方面:

字体设置字体大小，颜色，下划线等。 填充以设置图案或颜色渐变 边框在单元格上设置边框,单元格对齐保护。
以下是默认值：请参考：
://openpyxl.readthedocs.io/en/2.5/styles.html#styling-merged-cells

from openpyxl.styles import PatternFill,Border,Side,Alignment,Protection

font = Font(name=‘Calibri‘,
size=11,
bold=False,
italic=False,
vertAlign=None,
underline=‘none‘,
strike=False,
color=‘FF000000‘,
)

fill = PatternFill(fill_type=None,
   start_color=‘FFFFFFFF‘,
   end_color=‘FF000000‘)
border = Border(left=Side(border_style=None,
  color=‘FF000000‘),
right=Side(border_style=None,
  color=‘FF000000‘),
top=Side(border_style=None,
 color=‘FF000000‘),
bottom = Side(border_style=None,
  color=‘FF000000‘),
diagonal=Side(border_style=None,
  color=‘FF000000‘),
diagonal_direction=0,
outline=Side(border_style=None,
 color=‘FF000000‘),
vertical=Side(border_style=None,
  color=‘FF000000‘),
horizontal=Side(border_style=None,
color=‘FF000000‘)
)

alignment = Alignment(horizontal=‘general‘,
  vertical=‘bottom‘,
  text_rotation=0,
  wrap_text=False,
  shrink_to_fit=False,
  indent=0)

number_format = ‘General‘
protection = Protection(locked=True,hidden=False)

###单元格样式和命名样式（Cell Styles and Named Styles):

有两种类型的样式：单元格样式和命名样式，也称为样式模板。

####Cell Styles
单元格样式在对象之间共享，一旦它们被分配，它们就不能被改变。这样可以防止不必要的副作用，例如改变大量单元格的样式，而不是只改变一个样式。

from openpyxl.styles import colors
from openpyxl.styles import Font,Color
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

a1 = ws[‘A1‘]
d4 = ws[‘D4‘]
ft = Font(color=colors.RED)

a1 = ws[‘A1‘]
d4 = ws[‘D4‘]
ft = Font(color=colors.RED)

a1.font = ft
d4.font = ft

a1.font.italic = True  #is not allowed

a1.font = Font(color=colors.RED,italic=True)

Copying styles(复制样式):
样式也可以被复制.

from openpyxl.styles import Font
from copy import copy

ft1 = Font(name=‘Arial‘,size=14)
ft2 = copy(ft1)
ft2.name = "Tahoma"
print(ft1.name)
print(ft2.name)
print(ft2.size)

####Basic Font Colors(基本字体颜色)
颜色通常是RGB或aRGB的十六进制值。颜色模块包含一些方便的常量.

from openpyxl.styles import Font
from openpyxl.styles.colors import RED

font = Font(color=RED)
font = Font(color="FFBB00")

还支持传统索引颜色以及主题和色彩.

from openpyxl.styles.colors import Color

c = Color(indexed=32)
print(c)
c = Color(theme=6,tint=0.5)
print(c)

####应用样式(Applying Styles):
样式直接应用于单元格.

from openpyxl.workbook import Workbook
from openpyxl.styles import Font,Fill

wb = Workbook()
ws = wb.active
c = ws[‘A1‘]
c.font = Font(size=12)

wb.save(‘table\\style.xlsx‘)

样式还可以应用于列和行，但请注意，这仅适用于文件关闭后创建的单元格（在Excel中）。如果您想将样式应用于整个行和列，那么您必须将样式应用于每个单元格。这是对文件格式的限制：

col = ws.column_dimensions[‘A‘]
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")

####样式合并单元格(Styling Merged Cells):
有时候你想格式化一系列单元格，就好像它们是单个对象一样。 Excel假装可以通过合并单元格（删除除左上角单元格以外的所有单元格）然后重新创建这些单元格来应用伪类型。代码参考：
https://openpyxl.readthedocs.io/en/2.5/styles.html#styling-merged-cells

from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
from openpyxl import Workbook

def style_range(ws,cell_range,border=Border(),fill=None,font=None,alignment=None):
"""
App styles to a range of cells as if they were a single cell.
:param ws:
:param cell_range:
:param border:
:param fill:
:param font:
:param alignment:
:return:
"""

top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)

first_cell = ws[cell_range.split(":")[0]]
if alignment:
    ws.merge_cells(cell_range)
    first_cell.alignment = alignment

rows = ws[cell_range]
if font:
    first_cell.font = font

for cell in rows[0]:
    cell.border = cell.border + top
for cell in rows[-1]:
    cell.border = cell.border + bottom

for row in rows:
    L = row[0]
    r = row[-1]
    L.border = L.border + left
    r.border = r.border + right
    if fill:
        for c in row:
            c.fill = fill

wb = Workbook()
ws = wb.active
my_cell = ws[‘B2‘]
my_cell.value = "My Cell"

thin = Side(border_,color = "000000")
double = Side(border_,color="ff0000")

border = Border(top=double,left=thin,right=thin,bottom=double)

fill = PatternFill("solid",fgColor="DDDDDD")
fill = GradientFill(stop=("000000","FFFFFF"))

font = Font(b=True,color="FF0000")
al = Alignment(horizontal="center",vertical="center")

style_range(ws,‘B2:F4‘,border=border,fill=fill,font=font,alignment=al)

wb.save("table\\styled.xlsx")

####编辑页面设置(Edit Page Setup):

from openpyxl.workbook  import Workbook

wb = Workbook()
ws = wb.active

ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
wb.save("table\\styledone.xlsx")

####命名样式(Named Styles):
与单元格样式相比，命名样式是可变的。当您想要将格式一次应用到很多不同的单元格时，它们是有意义的。 NB。一旦您为单元格指定了命名样式，对样式的其他更改将不会影响单元格。
一旦一个已命名的样式被注册到一个工作簿中，它就可以简单地通过名称来引用。

####创建一个命名样式(Creating a Named Style):

from openpyxl.styles import NamedStyle,Font,Border,Side
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True,size=20)
bd = Side(style=‘thick‘,color="000000")
highlight.border = Border(left=bd,top=bd,right=bd,bottom=bd)

一旦创建了命名样式，它就可以在工作簿中注册.

wb.add_named_style(highlight)

但是，第一次将它们分配给单元格时，命名样式也会自动注册：

ws[‘A1‘].style = highlight

一旦注册，使用名称分配样式：

ws[‘D5‘].style = ‘highlight‘

例如:
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
wb.add_named_style(highlight)
ws[‘A1‘].style = highlight

ws[‘D5‘].style = ‘highlight‘

wb.save("table\\styles.xlsx")

####使用内置样式(Using builtin styles):
该规范包括一些内置的样式，也可以使用。不幸的是，这些样式的名称以其本地化形式存储。 openpyxl只会识别英文名称，而且只能写在这里。这些如下：

数字格式(Number formats):

‘Comma’
‘Comma [0]’
‘Currency’
‘Currency [0]’
‘Percent’

信息化(Informative):

‘Calculation’
‘Total’
‘Note’
‘Warning Text’
‘Explanatory Text’

文字样式(Text styles)：

‘Title’
‘Headline 1’
‘Headline 2’
‘Headline 3’
‘Headline 4’
‘Hyperlink’
‘Followed Hyperlink’
‘Linked Cell’

比较(Comparisons)：

‘Input’
‘Output’
‘Check Cell’
‘Good’
‘Bad’
‘Neutral’

强调(Highlights):
‘Accent1’
‘20 % - Accent1’
‘40 % - Accent1’
‘60 % - Accent1’
‘Accent2’
‘20 % - Accent2’
‘40 % - Accent2’
‘60 % - Accent2’
‘Accent3’
‘20 % - Accent3’
‘40 % - Accent3’
‘60 % - Accent3’
‘Accent4’
‘20 % - Accent4’
‘40 % - Accent4’
‘60 % - Accent4’
‘Accent5’
‘20 % - Accent5’
‘40 % - Accent5’
‘60 % - Accent5’
‘Accent6’
‘20 % - Accent6’
‘40 % - Accent6’
‘60 % - Accent6’
‘Pandas’

有关内建样式的更多信息:
openpyxl.styles.builtins

如有错误之处,请指正。

参考:
https://openpyxl.readthedocs.io/en/latest/

原文：http://blog.51cto.com/liyuanjie/2131216