Xlsxwriter含空格工作表名及数字存为文本时的图表异常问题

我在使用Xlsxwriter时发现了一个暂未找到合理解释的异常行为，相关信息如下：

问题表现 #

运行复现代码生成的Excel文件中存在两类异常图表：

1. 名为「Chart Ok」的图表看似符合预期，但在Excel中检查可发现数据未被正确引用：原因是数值被识别为通用格式而非数字格式，仅能勉强生成图表，无正确的数据交叉引用关系。
2. 名为「Missing chart」的图表是代码逻辑上的正确写法，但Excel无法正常生成该图表。二者代码唯一的差异是create_chart_missing_chart方法中，add_series的values属性的工作表名前后添加了单引号。

已知修复方案 #

提前将DataFrame中的数值设置为数字格式，建议在数据写入Excel前完成转换。

测试环境 #

• MS Office Professional Plus 2019
• Python 3.8
• Pandas 1.2.2

复现代码 #

import pandas as pd
from xlsxwriter.utility import xl_rowcol_to_cell


def main():
    df = pd.DataFrame({"text": ["some value1", "some value2", "sum of values"], "valeus": ["2", "4", "6"]})
    with pd.ExcelWriter("test.xlsx") as writer:
        create_worksheet(df, writer, "Some name with space")
    return 0


def create_worksheet(df: pd.DataFrame, writer, sheet: str):
    df.to_excel(writer, sheet, index=False, startrow=1, startcol=1, header=False)

    wb = writer.book
    ws = writer.sheets[sheet]
    cell_format = wb.add_format({"bold": True, "border": True, "border_color": "white"})
    cell_grid = wb.add_format({"border": True, "border_color": "white"})
    ws.set_column("A:AA", 10, cell_grid)
    ws.set_column("B:B", 25, cell_format)

    chart_OK = create_chart_ok(wb, df, sheet)
    chart_missing_values = create_chart_missing_values(wb, df, sheet)
    chart_missing_chart = create_chart_missing_chart(wb, df, sheet)
    chart_missing_x = create_chart_missing_x(wb, df, sheet)

    # 即使待展示的值以字符串格式存储在Excel中，该方法仍可生成可见图表，唯一异常是图表与数据之间缺少交叉引用
    ws.insert_chart("B6", chart_OK)
    
    # 该方法生成的图表图例中仅显示“1”和“2”，而非B2和B3对应的取值
    ws.insert_chart("B22", chart_missing_values)

    # 若C2和C3的取值被Excel识别为数字而非“通用”格式，该方法可生成正确交叉引用数据的有效图表
    ws.insert_chart("K6", chart_missing_chart)

    # 因C2和C3的取值被识别为文本，该方法无法生成图表，且图例值也存在缺失
    ws.insert_chart("K22", chart_missing_x)

    return 0


def create_chart_ok(wb, df: pd.DataFrame, sheet: str):
    chart = wb.add_chart({'type': 'pie'})
    chart.set_title({"name": "Chart OK", "name_font": {"bold": False, "size": 14}})
    chart.add_series({
        # 注意values中的工作表名前后没有单引号
        "values": "=" + str(sheet) + "!$C$2:" +
                  xl_rowcol_to_cell(len(df.index) - 1, 2, row_abs=True, col_abs=True),
        # 注意categories中的工作表名前后有单引号
        "categories": "='" + str(sheet) + "'!$B$2:" +
                      xl_rowcol_to_cell(len(df.index) - 1, 1, row_abs=True, col_abs=True),
        'points': [
            {'fill': {'color': "9bbb59"}},
            {'fill': {'color': '4f81bd'}},
        ],
    })

    return chart


def create_chart_missing_values(wb, df: pd.DataFrame, sheet: str):
    chart = wb.add_chart({'type': 'pie'})
    chart.set_title({"name": "Missing Values in legend", "name_font": {"bold": False, "size": 14}})
    chart.add_series({
        # 注意values中的工作表名前后没有单引号
        "values": "=" + str(sheet) + "!$C$2:" +
                  xl_rowcol_to_cell(len(df.index) - 1, 2, row_abs=True, col_abs=True),
        # 注意categories中的工作表名前后没有单引号
        "categories": "=" + str(sheet) + "!$B$2:" +
                      xl_rowcol_to_cell(len(df.index) - 1, 1, row_abs=True, col_abs=True),
        'points': [
            {'fill': {'color': "9bbb59"}},
            {'fill': {'color': '4f81bd'}},
        ],
    })

    return chart


def create_chart_missing_chart(wb, df: pd.DataFrame, sheet: str):
    chart = wb.add_chart({'type': 'pie'})
    chart.set_title({"name": "Missing chart", "name_font": {"bold": False, "size": 14}})
    chart.add_series({
        # 注意values中的工作表名前后有单引号
        "values": "='" + str(sheet) + "'!$C$2:" +
                  xl_rowcol_to_cell(len(df.index) - 1, 2, row_abs=True, col_abs=True),
        # 注意categories中的工作表名前后有单引号
        "categories": "='" + str(sheet) + "'!$B$2:" +
                      xl_rowcol_to_cell(len(df.index) - 1, 1, row_abs=True, col_abs=True),
        'points': [
            {'fill': {'color': "9bbb59"}},
            {'fill': {'color': '4f81bd'}},
        ],
    })

    return chart


def create_chart_missing_x(wb, df: pd.DataFrame, sheet: str):
    chart = wb.add_chart({'type': 'pie'})
    chart.set_title({"name": "Missing whatever", "name_font": {"bold": False, "size": 14}})
    chart.add_series({
        # 注意values中的工作表名前后有单引号
        "values": "='" + str(sheet) + "'!$C$2:" +
                  xl_rowcol_to_cell(len(df.index) - 1, 2, row_abs=True, col_abs=True),
        # 注意categories中的工作表名前后没有单引号
        "categories": "=" + str(sheet) + "!$B$2:" +
                      xl_rowcol_to_cell(len(df.index) - 1, 1, row_abs=True, col_abs=True),
        'points': [
            {'fill': {'color': "9bbb59"}},
            {'fill': {'color': '4f81bd'}},
        ],
    })

    return chart


if __name__ == "__main__":
    main()

内容的提问来源于stack exchange，提问作者Gergo Peltz