xlsxwriter - 每当值更改时修改格式 [英] xlsxwriter - Modifying formatting whenever value changes

问题描述

我有一个脚本，它使用 xlsxwriter 生成一个包含大约十二列的工作簿，所有列都来自某个操作过的 df.

I have a script that is using xlsxwriter to produce a workbook with about a dozen columns, all from a certain manipulated df.

在导出到 Excel 之前，我对 df 进行排序并将其添加到表格中.

I sort the df and add it to a table before exporting to Excel.

 worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

它神奇地创建了一个带有交替着色(带)的表格.我想控制格式如下:

It magically creates a table with alternate coloring (bands). I wish to control the formatting is the following fashion:

1. df 按名为 case_id 的列(在其他列中)进行排序
2. 我希望使用不超过两种背景颜色
3. 每次 case_id 中的值发生变化时，我都希望切换到不同的颜色.

1. The df is sorted by a column called case_id (among other columns)
2. I wish to use no more than two bg colors
3. Every time the value in case_id changes, I wish to switch to the different color.

换句话说 - 按值创建波段.

In other words - create bands by the value.

我考虑过使用条件格式，但这并不是我所需要的.我不知道这个值......在伪代码中，它可以是这样的:

I thought about using conditional formatting but it's not quite what I need. I'm agnostic the the value... In pseudocode, it can be something like this:

1. 创建两个变量，一个用于每种所需的格式(format1、format2)和 temp_format = format1
2. 逐行检查工作表

1. 如果 case_id 中的值等于前一行的 case_id，则将 temp_format 切换为另一行.
2. 将行格式设置为 temp_format

推荐答案

实现了伪代码.

 format1 = workbook.add_format({'bg_color': '#777CF4'})
 format2 = workbook.add_format({'bg_color': '#3FCBFF'})
 tmp_format = format1
 tmp_val = 0
 for i in range(0, max_row):
    if df.loc[i]['chain_id'] != tmp_val:
        tmp_format = format2 if tmp_format == format1 else format1
        tmp_val = df.loc[i]['chain_id']
    worksheet.set_row(i+1, None, tmp_format) #Because writer is +1 comapared to df, due to headers

这篇关于xlsxwriter - 每当值更改时修改格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！