目录
一、安装openpyxl
pip install openpyxl==3.0.10
想要在文件中插入图片文件，需要安装pillow：
py -3 -m pip install pillow
二、创建一个excel 文件，并写入不同类的内容
创建空excel文件，默认会有一个sheet
from openpyxl import Workbook
wb = Workbook()
wb.save(r"F:\python_18_excel\test.xlsx")?
# -*- coding: utf-8 -*-
import locale
from openpyxl import Workbook
wb = Workbook() #创建文件对象
# grab the active worksheet
ws = wb.active #获取第一个sheet
# Data can be assigned directly to cells
ws['A1'] = 42 #写入数字
ws['B1'] = "光荣之路"+"automation test" #写入中文
# Rows can also be appended
ws.append([1, 2, 3]) #写入多个单元格
import datetime
import time
import locale
ws['A3'] = datetime.datetime.now() #写入一个当前时间
#写入一个自定义的时间格式
locale.setlocale(locale.LC_ALL,'en')
locale.setlocale(locale.LC_CTYPE, 'chinese')
ws['A4'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())
# Save the file
wb.save(r"F:\python_18_excel\sample.xlsx")
三、创建一个sheet
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet("Mysheet1") #创建一个sheet,名字：Mysheet1
ws1 = wb.create_sheet("Mysheet") #创建一个sheet名字：Mysheet
ws1.title = "New Title" #Mysheet改名字为New Title
ws2 = wb.create_sheet("Mysheet", 0) #设定sheet的插入位置
ws2.title = "光荣之路自动化测试培训" #设定一个sheet的名字
ws1.sheet_properties.tabColor = "1072BA" #设定sheet的标签的背景颜色
#获取某个sheet对象
print(wb["光荣之路自动化测试培训"])
print(wb["New Title" ])
#获取全部sheet 的名字，遍历sheet名字
print(wb.sheetnames)
for sheet_name in wb.sheetnames:
print(sheet_name)
print ("*"*50)
#遍历获取sheet对象，按照sheet顺序获取
for sheet in wb:
print(sheet)
for sheet in wb:
print(sheet.title)
wb["New Title"]["A1"]="gloryroad"
source = wb["New Title"]
target = wb.copy_worksheet(source) #复制一个sheet
#删除某个sheet
del wb["New Title" ]
# Save the file
wb.save(r"F:\python_18_excel\sample2.xlsx")
?
四、操作单元格?
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=123.11
ws1["B2"]="光荣之路"
d = ws1.cell(row=4, column=2, value=10)
print(ws1["A1"].value)
print(ws1["B2"].value)
print(d.value)
print(ws1.cell(row=4,column=2).value) #行号和列号从1开始
# Save the file
wb.save(r"F:\python_18_excel\sample3.xlsx")
?
五、批量操作单元格?
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
# ws1["A6"]=100
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
#操作单列
print(ws1["A"])
for cell in ws1["A"]:
print(cell.value)
#操作多列,获取每一个值
print(ws1["A:C"])
for column in ws1["A:C"]:
for cell in column:
print(cell.value)
#操作多行
row_range = ws1[1:3]
print(row_range)
for row in row_range:
for cell in row:
print(cell.value)
print("*"*50)
for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
for cell in row:
print(cell.value)
#获取所有行
print(ws1.rows)
for row in ws1.rows:
print(row)
print("*"*50)
#获取所有列
print(ws1.columns)
for col in ws1.columns:
print(col)
print(ws1.max_row,ws1.max_column)
print(ws1.min_row,ws1.min_column)
# Save the file
wb.save(r"F:\python_18_excel\sample4.xlsx")(<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.A2>, <Cell 'Mysheet'.A3>)
1
2
3
((<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.A2>, <Cell 'Mysheet'.A3>), (<Cell 'Mysheet'.B1>, <Cell 'Mysheet'.B2>, <Cell 'Mysheet'.B3>), (<Cell 'Mysheet'.C1>, <Cell 'Mysheet'.C2>, <Cell 'Mysheet'.C3>))
1
2
3
4
5
6
7
8
9
((<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.B1>, <Cell 'Mysheet'.C1>), (<Cell 'Mysheet'.A2>, <Cell 'Mysheet'.B2>, <Cell 'Mysheet'.C2>), (<Cell 'Mysheet'.A3>, <Cell 'Mysheet'.B3>, <Cell 'Mysheet'.C3>))
1
4
7
2
5
8
3
6
9
**************************************************
1
4
7
2
5
8
3
6
9
<generator object Worksheet._cells_by_row at 0x00000220039C6570>
(<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.B1>, <Cell 'Mysheet'.C1>)
(<Cell 'Mysheet'.A2>, <Cell 'Mysheet'.B2>, <Cell 'Mysheet'.C2>)
(<Cell 'Mysheet'.A3>, <Cell 'Mysheet'.B3>, <Cell 'Mysheet'.C3>)
**************************************************
<generator object Worksheet._cells_by_col at 0x00000220039C6570>
(<Cell 'Mysheet'.A1>, <Cell 'Mysheet'.A2>, <Cell 'Mysheet'.A3>)
(<Cell 'Mysheet'.B1>, <Cell 'Mysheet'.B2>, <Cell 'Mysheet'.B3>)
(<Cell 'Mysheet'.C1>, <Cell 'Mysheet'.C2>, <Cell 'Mysheet'.C3>)
3 3
1 1
?
?
5.1 打印百分数或小数
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\sample4.xlsx") #从现有的文件中读取内容，不会删除原有内容
wb.guess_types = True #猜测单元格类型，True时将单元格的百分数显示为小数；False时将单元格显示为百分数
ws=wb.active
ws["D1"]="12%"
print(type(ws["D1"].value))
print(ws["D1"].value)
ws["D3"]=12
print(type(ws["D3"].value))
print(ws["D3"].value)
ws["D4"]=3.14
print(type(ws["D4"].value))
print(ws["D4"].value)
ws["D5"]="test"
print(type(ws["D5"].value))
print(ws["D5"].value)
# Save the file
wb.save(r"F:\python_18_excel\sample4.xlsx")但我不管设置?wb.guess_types = True orwb.guess_types = False时，print(ws["D1"].value)输出结果都是12%。不知道为什么？
?
5.2 获取所有行对象
#coding=utf-8
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
ws=wb.active #获取默认的sheet进行操作
rows=[] #存储行对象
for row in ws.iter_rows():#遍历所有行对象
rows.append(row)
print(rows) #所有行
print(rows[0]) #获取第一行
print(rows[0][0]) #获取第一行第一列的单元格对象
print(rows[0][0].value) #获取第一行第一列的单元格对象的值
print(rows[len(rows)-1]) #获取最后行
print(rows[len(rows)-1][len(rows[0])-1]) #获取第后一行和最后一列的单元格对象
print(rows[len(rows)-1][len(rows[0])-1].value) #获取第后一行和最后一列的单元格对象的值
print(rows[-1]) #获取最后行
print(rows[-1][len(rows[-1])-1]) #获取第后一行和最后一列的单元格对象
print(rows[-1][len(rows[-1])-1].value) #获取第后一行和最后一列的单元格对象的值?
[(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>, <Cell 'Sheet'.D1>, <Cell 'Sheet'.E1>), (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.E2>), (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>)]
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>, <Cell 'Sheet'.D1>, <Cell 'Sheet'.E1>)
<Cell 'Sheet'.A1>
1
(<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>)
<Cell 'Sheet'.E3>
10
(<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>)
<Cell 'Sheet'.E3>
105.3 获取所有列对象
#coding=utf-8
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
ws=wb.active
cols=[]
cols = []
for col in ws.iter_cols():
cols.append(col)
print(cols) #所有列
print(cols[0]) #获取第一列
print(cols[0][0]) #获取第一列的第一行的单元格对象
print(cols[0][0].value) #获取第一列的第一行的值
print("*"*30)
print(cols[len(cols)-1]) #获取最后一列
print(cols[len(cols)-1][len(cols[0])-1]) #获取最后一列的最后一行的单元格对象
print(cols[len(cols)-1][len(cols[0])-1].value) #获取最后一列的最后一行的单元格对象的值[(<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>), (<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>), (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>), (<Cell 'Sheet'.D1>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.D3>), (<Cell 'Sheet'.E1>, <Cell 'Sheet'.E2>, <Cell 'Sheet'.E3>)]
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>)
<Cell 'Sheet'.A1>
1
******************************
(<Cell 'Sheet'.E1>, <Cell 'Sheet'.E2>, <Cell 'Sheet'.E3>)
<Cell 'Sheet'.E3>
10六、操作已存在的文件
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\sample6.xlsx")
wb.guess_types = True #猜测格式类型
ws=wb.active
ws["D1"]="12%"
print (ws["D1"].value)
# Save the file
wb.save("e:\\sample.xlsx")
#注意如果原文件有一些图片或者图标，则保存的时候可能会导致图片丢失七、单元格类型
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
import datetime
wb = load_workbook(r"F:\python_18_excel\test.xlsx")
ws=wb.active
wb.guess_types = True
ws["A1"]=datetime.datetime(2010, 7, 21)
print(ws["A1"].number_format)
ws["A2"]="12%"
print(ws["A2"].number_format)
ws["A3"]= 1.1
print(ws["A3"].number_format)
ws["A4"]= "中国"
print(ws["A4"].number_format)
# Save the file
wb.save(r"F:\python_18_excel\test.xlsx")
八、使用公式?
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\test.xlsx")
ws1=wb.active
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"
print (ws1["A4"].value) #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
print (ws1["A5"].value) #打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
# Save the file
wb.save(r"F:\python_18_excel\test.xlsx")
?
九、合并/拆分单元格?
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(r"F:\python_18_excel\a.xlsx")
ws1=wb.active
from openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active
ws.merge_cells('A5:D5') #合并单元格
ws.unmerge_cells('A5:D5') #拆分单元格
# or equivalently
ws.merge_cells(start_row=6,start_column=1,end_row=6,end_column=4)
ws.unmerge_cells(start_row=6,start_column=1,end_row=2,end_column=4)
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
十、插入一个图片
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook(r"F:\python_18_excel\a.xlsx")
ws1=wb.active
img = Image(r"F:\python_18_excel\1.jpg")
ws1.add_image(img, 'A1')
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")十一、隐藏单元格
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook(r"F:\python_18_excel\a.xlsx")
ws1=wb.active
ws1.column_dimensions.group('A', 'D', hidden=True) #隐藏a到d列范围内的列
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")十二、画一个柱状图
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series
wb = load_workbook(r"F:\python_18_excel\a.xlsx")
ws1=wb.active
wb = Workbook()
ws = wb.active
for i in range(10):
ws.append([i])
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.title = "Bar Chart"
chart.y_axis.title = 'SN'
chart.x_axis.title = 'Sample value'
chart.add_data(values)
ws.add_chart(chart, "E15")
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
十三、画一个饼图
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import (
PieChart,
ProjectedPieChart,
Reference
)
from openpyxl.chart.series import DataPoint
#5行数据
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:#把5行的数据加入到excel的sheet中
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)#idx表示让第几行突出，explosion=0则不突出
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
# 映射图
ws = wb.create_sheet(title="Projection")
data = [
['Page', 'Views'],
['Search', 95],
['Products', 4],
['Offers', 0.5],
['Sales', 0.5],
]
for row in data:
ws.append(row)
projected_pie = ProjectedPieChart()
projected_pie.type = "pie"
projected_pie.splitType = "val" # split by value
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)
ws.add_chart(projected_pie, "A10")
from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar"
projected_bar.splitType = 'pos' # split by position
ws.add_chart(projected_bar, "A27")
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
映射图：?
?explosion=0时：
?十四、设定一个表格区域，并设定表格的样式
# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True,showLastColumn=True, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
?十五、给单元格设定字体颜色
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
ft = Font(color='FFBB00') # color="FFBB00"，颜色编码也可以设定颜色
a1.font = ft
d4.font = ft
a1.font = Font(name="楷体",color=colors.BLUE,size=50,bold=True,underline="single",family=14) # the change only affects A1# the change only affects A1
a1.value = "abc"
#给某一个区域设置字体的格式：
for i in range(1,4):
for j in range(1,4):
ws.cell(row=i, column=j).font = Font(name="楷体",color=colors.BLUE,size=50,bold=True,underline="single",family=1)
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
十六、设定字体和大小?
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
a1.value = "abc"
d4.value = 123
from openpyxl.styles import Font
from copy import copy
ft1 = Font(name='宋体', size=14)
ft2 = copy(ft1) #复制字体对象
ft2.name = "Tahoma"
print (ft1.name)
print (ft2.name)
print (ft2.size)
a1.font = ft1
d4.font = ft2
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
?
?
十七、设定行和列的字体?
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
col = ws.column_dimensions['A']
col.font = Font(bold=True) #将A列设定为粗体
row = ws.row_dimensions[1]
row.font = Font(underline="single") #将第一行设定为下划线格式
col.width=10 #设定A列的宽度为10，否则A列会被隐藏
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
?
十八、设定单元格的边框、字体、颜色、大小和边框背景
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
wb = Workbook()
ws = wb.active
highlight1 = NamedStyle(name="highlight")
highlight1.font = Font(bold=True, size=20,color= "ff0100")
highlight1.fill = PatternFill("solid", fgColor="DDDDDD")
bd = Side(style='thick', color="000000")
highlight1.border = Border(left=bd, top=bd, right=bd, bottom=bd)
#print (dir(ws["A1"]))
ws["A1"].value="我们"
ws["A1"].style =highlight1
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
十九、常用的样式和属性设置
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
wb = Workbook()
ws = wb.active
ft = Font(name=u'微软雅黑',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type="solid",
start_color='FFEEFFFF',
end_color='FF001100')
#边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
#diagonal 表示对角线
bd = Border(left=Side(border_style="thin",
color='FF001000'),
right=Side(border_style="thin",
color='FF110000'),
top=Side(border_style="thin",
color='FF110000'),
bottom=Side(border_style="thin",
color='FF110000'),
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='FF110000')
)
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)
ws["B5"].font = ft
ws["B5"].fill =fill
ws["B5"].border = bd
ws["B5"].alignment = alignment
ws["B5"].number_format = number_format
ws["B5"].value ="glory road"
# Save the file
wb.save(r"F:\python_18_excel\a.xlsx")
?
二十、给某个范围设定样式
示例1：给合并后的单元格添加样式
from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
from openpyxl.styles import Font, Border, Side,PatternFill
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_style="thin",color = "000000")
double = Side(border_style="double",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(r"F:\LiFuChe\光荣之路\课堂编码练习\python_18_excel\a.xlsx")
?
示例2：给每个单元格设定相同的样式
from openpyxl.styles import Border,Side,PatternFill,Font,GradientFill,Alignment
from openpyxl.styles import Font, Border, Side,PatternFill
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]]#"A1:D4"，获取指定范围中的第一个单元格
rows = ws[cell_range]#取到指定单元格的范围，是一个元组
if font:#是否传入了font参数，如有，用你传入的字体样式
first_cell.font = font
for row in rows:
for cell in row:
cell.border = cell.border + top + bottom + right + left
cell.fill = fill
cell.font = font
cell.alignment = alignment#对齐方式
wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"
thin = Side(border_style="thin",color = "000000")
double = Side(border_style="double",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(r"F:\python_18_excel\a.xlsx")?
?