xlsx表格制作教程(各种文档表格的做法)

excelperfect

标签:Python与Excel,Xlsxwriter

这是Python xlsxwriter库应用系列第4部分,前3部分请参阅:

>>>使用PythonXlsxwriter创建Excel电子表格

>>>使用PythonXlsxwriter创建Excel电子表格(第2部分:公式,链接与命名区域)

>>>使用PythonXlsxwriter创建Excel电子表格(第3部分:格式,迷你图与图表)

本文介绍Python xlsxwriter库如何为Excel文件创建各种条件格式。

Excel条件格式

条件格式,根据条件设置格式,这是Excel中一个灵巧优雅的功能,允许我们根据特定条件高亮显示(在大多数情况下)单元格。当然,用户可以定义这些条件。

此外,条件格式的优点在于格式是动态的——当值改变时,格式也会改变。

在Excel中,单击“条件格式”,我们会看到一系列选择,当单击“新建规则”,将打开一个对话框,基本上汇总了所有选项。定义条件的方法非常灵活,因此无法涵盖每一种情况。这里将介绍一些常见的场景。

xlsx表格制作教程(各种文档表格的做法)

图1

Python环境准备

让我们进入Python,生成一些值和xlsxwriter工作簿。

创建的三种格式是:format_r(红色)、format_y(黄色)和format_g(绿色)。

import xlsxwriter

wb =xlsxwriter.Workbook(r’D:\conditional_formatting.xlsx’)

ws = wb.add_worksheet(‘格式’)

list_1 = list(range(10))

list_2 = [5,4,3,2,1,9,8,7,6,0]

list_3 = [i for i in range(-10,10)]

list_text =[‘python’,’excel’,’python xlsxwriter’,’python automate excel’,’excel automatepython’,’python’]

format_r =wb.add_format({‘bg_color’: ‘#FFC7CE’,

‘font_color’: ‘#9C0006’})

format_y =wb.add_format({‘bg_color’: ‘#FFEB9C’,

‘font_color’:’#9C6500′})

format_g =wb.add_format({‘bg_color’: ‘#C6EFCE’,

‘font_color’:’#006100′})

回顾

在本系列的第3部分,我们学到了:

1.需要一个工作簿对象(wb)和一个工作表对象(ws)。

2.可以使用“A1”或(行、列)样式表示法来引用单元格和单元格区域。

3.使用workbook.add_format()方法创建Excel格式。

4.通过将数据和格式同时写入单元格/区域来格式化单元格。

新概念

1.要创建条件格式,使用worksheet.conditional_format(‘A1’,{parameters})。

2.条件格式叠加在现有单元格格式上,并非所有单元格格式属性都可以修改,例如字体名称、大小、对齐方式等。

3.大多数情况下,我们使用条件格式只是为了突出显示单元格(改变单元格颜色)。

Xlsxwriter条件格式参数

conditional_format()方法中的参数必须是一个字典,其中包含描述格式类型和样式的属性。一些主要属性包括:

type:是格式化单元格、数字、文本、排序、平均值、重复还是公式?参见上图1,“类型”指的是“规则类型”。

criteria:是否要查找“大于/小于”、“包含”某些文本、前几项等。

value:通常与条件“大于7”、“介于5和7之间”、“高于”平均值等结合使用。

format:格式,通常只是更改单元格/字体颜色。

现在,让我们看看如何应用它们。

条件格式所有单元格颜色色标

如果你喜欢彩虹,那么可以指定三种颜色(min、mid和max),Excel将为我们打造一道美丽的彩虹。如果只喜欢双色,则将“类型”更改为“双色刻度”,然后只需删除“中间色”。

def color_scale():

ws.write(‘N1′,’三色刻度’)

ws.write_column(‘N2’,list_1)

ws.conditional_format(‘N2:N11’,

{‘type’:’3_color_scale’,

‘min_color’:’red’,

‘mid_color’:’yellow’,

‘max_color’:’green’

})

图2

条件格式所有单元格数据条

可以用数据条做很多不同的很酷的东西,很灵活。

def data_bar():

ws.write(‘P1′,’data bar’)

ws.write_column(‘P2’,list_3)

ws.conditional_format(‘P2:P22’,

{‘type’: ‘data_bar’,

‘bar_color’:’green’,

‘bar_only’: True, #True – 没有数据, False – 显示数据

‘bar_solid’: False, # True – 纯色填充, False – 渐变色

‘bar_negative_color’:’red’,

‘bar_direction’: ‘left’, # 或 ‘right’

‘bar_axis_position’:’middle’, # 或 ‘none’

})

图3

基于数字的条件格式

注意ws.conditional_format方法中的字典,特别是传递给这些属性的值。“criteria”可以是以下任一列(任一列都适用):

‘between’

‘not between’

‘equal to’ ‘==’

‘not equal to’ ‘!=’

‘greater than’ ‘>’

‘less than’ ‘<’

‘greater than or equal to’ ‘>=’

‘less than or equal to’ ‘<=’

def based_on_number():

ws.merge_range(‘B1:C1′,’>7 绿色, <5 红色, 其间黄色’)

ws.write_column(‘B2’, list_1)

ws.write_column(‘C2’, list_2)

## 大于7, 绿色

ws.conditional_format(‘B2:C11’,

{‘type’: ‘cell’,

‘criteria’: ‘>’,

‘value’: 7,

‘format’:format_g

})

## 小于5, 红色

ws.conditional_format(‘B2:C11’,

{‘type’: ‘cell’,

‘criteria’: ‘<‘,

‘value’: 5,

‘format’:format_r

})

## 在5与7之间, 黄色

ws.conditional_format(‘B2:C11’,

{‘type’: ‘cell’,

‘criteria’: ‘between’,

‘minimum’:5,

‘maximum’:7,

‘format’:format_y

})

图4

基于单元格输入的数字的条件格式

这与前面的示例类似,只是我们没有对阈值5和7进行硬编码。我们将让格式取决于单元格值,甚至更动态。

注意下面代码中的“value”属性,我们需要使用绝对引用,否则它将不起作用。通常,对于任何“value”属性,我们都需要使用绝对引用。

在Excel中生成后,你将看到,当我们修改单元格B19和C19中的值时,格式会发生变化。

def based_on_number_input():

ws.merge_range(‘B18:C18′,’使用数字输入’)

ws.write(‘B19’,7) ## 阈值

ws.write(‘C19’,5) ## 阈值

ws.write_column(‘B20’, list_1)

ws.write_column(‘C20’, list_2)

ws.conditional_format(‘B20:C29’,

{‘type’: ‘cell’,

‘criteria’: ‘>’,

‘value’: ‘$B$19’,

‘format’:format_g

})

ws.conditional_format(‘B20:C29’,

{‘type’: ‘cell’,

‘criteria’: ‘<‘,

‘value’: ‘$C$19’,

‘format’:format_r

})

基于文本的条件格式

可以检查单元格是否包含某些文本。criteria属性包括以下内容:

‘containing’

‘not containing’

‘begins with’

‘ends with’

def based_on_text():

ws.write(‘E1’,’包含”python”‘)

ws.write(‘F1′,’以”python” 开始’)

ws.write_column(‘E2’, list_text)

ws.write_column(‘F2’, list_text)

ws.conditional_format(‘E2:E11’,

{‘type’: ‘text’,

‘criteria’: ‘containing’,

‘value’: ‘python’,

‘format’:format_g

})

ws.conditional_format(‘F2:F11’,

{‘type’: ‘text’,

‘criteria’: ‘begins with’,

‘value’: ‘python’,

‘format’:format_g

})

图5

条件格式设置排名靠前/靠后的值

可以通过“顶部”或“底部”值(即前5个最大值)或百分比(即所选值的底部10%)突出显示项目。省略“criteria”表示按计数,而设置“criteria”:%表示按百分比。

def top_n():

ws.write(‘H1′,’前 5’)

ws.write_column(‘H2’,list_2)

ws.conditional_format(‘H2:H11’,

{‘type’: ‘top’,

‘value’: 5,

#’criteria’: ‘%’,

‘format’:format_g

})

图6

设置高于/低于平均值条件格式

Excel将计算所选区域的平均值,然后将区域中的每个数字与平均值进行比较,并相应地设置格式。

def average():

ws.write(‘J1′,’平均值’)

ws.write_column(‘J2’,list_2)

ws.conditional_format(‘J2:J11’,

{‘type’: ‘average’,

‘criteria’: ‘above’,

‘format’:format_g

})

图7

条件格式突出唯一/重复值

可以突出显示选定区域内的重复(或唯一)值。

def duplicate():

ws.write(‘L1′,’重复’)

ws.write_column(‘L2’,list_text)

ws.conditional_format(‘L2:L11’,

{‘type’: ‘duplicate’, #或 ‘unique’

‘format’:format_g

})

图8

基于公式的条件格式

可以根据公式进行条件格式设置,使我们的Excel更加动态。

但是,基于公式的格式可能有点棘手,因为某些情况需要绝对引用,而另一些情况需要非绝对引用。策略是:尝试Excel中的公式,无论单元格引用中是否包含$。如果它在Excel中工作,那么将相同的公式应用到Python中也会起作用。

下面的代码比较R列和S列中的数字,然后突出显示(绿色)两列之间较大的数字。

注意,“type”设置为“formula”,在“criteria”中,我们键入公式,就好像只针对(所选区域中)第一项一样。在conditional_format方法中,正在格式化单元格R2:R11,第一个元素是R2,因此公式为’=R2>S2’。如果想将该格式应用于R3:R11,那么公式需要是’=R3>S3’,以此类推。

另外,在本例中,我们比较两列,因此在公式中不使用绝对引用。在其他情况下,可能需要使用绝对引用来实现基于公式的格式设置工作。

def based_on_formula():

ws.merge_range(‘R1:S1′,’比较 R 和 S, 突出显示大值 #’)

ws.write_column(‘R2’, list_1)

ws.write_column(‘S2’, list_2)

ws.conditional_format(‘R2:R11’,

{‘type’: ‘formula’,

‘criteria’: ‘=R2>S2’,

‘format’:format_g

})

ws.conditional_format(‘S2:S11’,

{‘type’: ‘formula’,

‘criteria’: ‘=S2>R2’,

‘format’:format_g

})

图9

注:本文学习整理自pythoninoffice.com。

欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。

发表评论

登录后才能评论