在 Python 3 中使用 xlsxwriter 交替行颜色 [英] Alternating row color using xlsxwriter in Python 3
问题描述
有没有人在 Python3 中使用 xlsxwriter 生成 excel 时实现交替行颜色?
Has anybody implemented alternating row color while generating excel using xlsxwriter in Python3?
data_format = workbook.add_format(
{
'bg_color': '#FFC7CE'
})
worksheet.write(data_row, data_col + 1, row[1], data_format)
这将设置每列的颜色.
推荐答案
没有什么可以阻止您手动设置格式,如下所示.有两种方法:
There is nothing stopping you from setting the formats manually as follows. There are two approaches:
使用
.add_format()
为每一行添加格式.
使用 .add_table()
使用 Excel 表格添加数据.此样式允许自动带状行.这也有一个好处,如果表被排序,则条带不受影响.
Add the data using an Excel table using .add_table()
. The style for this allows for automatic banded rows. This also has the advantage that if the table is sorted, the banding is unaffected.
上下文管理器可用于事后自动关闭工作簿.
A context manager can be used to automatically close the workbook afterwards.
方法 1:
手动将单元格格式应用于每一行:
Manually applying a cell format to each row:
import xlsxwriter
with xlsxwriter.Workbook('hello.xlsx') as workbook:
worksheet = workbook.add_worksheet()
data_format1 = workbook.add_format({'bg_color': '#FFC7CE'})
data_format2 = workbook.add_format({'bg_color': '#00C7CE'})
for row in range(0, 10, 2):
worksheet.set_row(row, cell_format=data_format1)
worksheet.set_row(row + 1, cell_format=data_format2)
worksheet.write(row, 0, "Hello")
worksheet.write(row + 1, 0, "world")
这将为您提供如下所示的输出:
This would give you output looking as follows:
要将其应用于数据列表,您可以使用以下方法.这也显示了如何扩展它以使用其他格式:
To apply this to a list of data, you could use the following approach. This also shows how it could be extended to use additional formats:
import xlsxwriter
from itertools import cycle
data = ["Row 1", "Row 2", "Row 3", "Row 4", "Row 5", "Row 6"]
with xlsxwriter.Workbook('hello.xlsx') as workbook:
data_format1 = workbook.add_format({'bg_color': '#EEEEEE'})
data_format2 = workbook.add_format({'bg_color': '#DDDDDD'})
data_format3 = workbook.add_format({'bg_color': '#CCCCCC'})
formats = cycle([data_format1, data_format2, data_format3])
worksheet = workbook.add_worksheet()
for row, value in enumerate(data):
data_format = next(formats)
worksheet.set_row(row, cell_format=data_format)
worksheet.write(row, 0, value)
方法 2:
使用带条带样式的 .add_table()
添加数据:
Adding the data using .add_table()
with a banded style:
import xlsxwriter
data = [["Row 1"], ["Row 2"], ["Row 3"], ["Row 4"], ["Row 5"], ["Row 6"]]
with xlsxwriter.Workbook('hello.xlsx') as workbook:
worksheet = workbook.add_worksheet()
worksheet.add_table('A1:A6', {'data' : data, 'header_row' : False})
Excel 带有许多不同的预定义表格样式,可以通过传递一个 style
参数来选择这些样式,如下所示:
Excel comes with a number of different pre-defined table styles, these can be selected by passing a style
paramter as follows:
'style': 'Table Style Light 11'
有关更多信息,请查看:使用工作表
For more information have a look at: Working with Worksheet Tables
这篇关于在 Python 3 中使用 xlsxwriter 交替行颜色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!