New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Chart does not handle sheet names with special characters #167

Closed
gtzampanakis opened this Issue Oct 14, 2014 · 4 comments

Comments

3 participants
@gtzampanakis

The following has been verified with xlsxwriter version 0.5.9.
The Chart._list_to_formula method does not properly handle sheet names with special characters.
In the following sample code, when the output file is loaded in Excel 2013, the chart labels in the legend will show up as numbers (1,2,3) instead of strings ('Apple', 'Cherry', 'Pecan'). In Excel 2007 the problem is strangely not evident, probably because it auto-corrects the formula.

    import xlsxwriter

    workbook = xlsxwriter.Workbook('chart_pie.xlsx')

    sheet_name = "foo bar" # Remove the space to make the 
                                           # output behave correctly in Excel 2013.

    worksheet = workbook.add_worksheet(sheet_name)

    # Add the worksheet data that the charts will refer to.
    headings = ['Category', 'Values']
    data = [
        ['Apple', 'Cherry', 'Pecan'],
        [60, 30, 10],
    ]

    worksheet.write_column('A2', data[0])
    worksheet.write_column('B2', data[1])

    #######################################################################
    #
    # Create a new chart object.
    #
    chart1 = workbook.add_chart({'type': 'pie'})

    # Configure the series. Note the use of the list syntax to define ranges:
    chart1.add_series({
        'name':       'Pie sales data',
        'categories': [sheet_name, 1, 0, 3, 0],
        'values':     [sheet_name, 1, 1, 3, 1],
    })

    # Insert the chart into the worksheet (with an offset).
    worksheet.insert_chart('C2', chart1)

    workbook.close()

@jmcnamara jmcnamara added the bug label Oct 14, 2014

@jmcnamara jmcnamara self-assigned this Oct 14, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 14, 2014

Owner

Hi,

Thanks for the bug report.

It is mentioned in a few place in the docs, although not in the Chart section, that you need to quote worksheet names like this:

Excel requires that worksheet names containing spaces or non alphanumeric characters are single quoted as follows 'Sales Data'!A1.

So for the case above changing the sheet name to "'foo bar'" should work.

However, the module should try to catch this when the user misses it. That is already happening in the worksheet.py module but not in the chart.py module.

So it is a bug and I'll fix it in an upcoming release. In the meantime, as a workaround, just quote the sheet name like you see it in Excel.

Regards,

John

Owner

jmcnamara commented Oct 14, 2014

Hi,

Thanks for the bug report.

It is mentioned in a few place in the docs, although not in the Chart section, that you need to quote worksheet names like this:

Excel requires that worksheet names containing spaces or non alphanumeric characters are single quoted as follows 'Sales Data'!A1.

So for the case above changing the sheet name to "'foo bar'" should work.

However, the module should try to catch this when the user misses it. That is already happening in the worksheet.py module but not in the chart.py module.

So it is a bug and I'll fix it in an upcoming release. In the meantime, as a workaround, just quote the sheet name like you see it in Excel.

Regards,

John

@jmcnamara jmcnamara changed the title from Chart._list_to_formula does not properly handle sheet names with special characters to Chart does not handle sheet names with special characters Oct 14, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 29, 2014

Owner

Fixed in version 0.6.1

Thanks for the report.

Owner

jmcnamara commented Oct 29, 2014

Fixed in version 0.6.1

Thanks for the report.

@jmcnamara jmcnamara closed this Oct 29, 2014

@dekatzenel

This comment has been minimized.

Show comment
Hide comment
@dekatzenel

dekatzenel Dec 24, 2014

I'm noticing the same bug when I try to set the chart series name using a list. The sheet name contains spaces and a hyphen.

The following code doesn't work: chart.add_series({'values' : ['Term-Document Match Counts', 1, 1, tds_rows, 1], 'name' : ['Term-Document Match Counts', 0, 1], 'categories' : ['Term-Document Match Counts', 1, 0, tds_rows, 0]})

The following code works: chart.add_series({'values' : ['Term-Document Match Counts', 1, 1, tds_rows, 1], 'name' : ["'Term-Document Match Counts'", 0, 1], 'categories' : ['Term-Document Match Counts', 1, 0, tds_rows, 0]})

I'm noticing the same bug when I try to set the chart series name using a list. The sheet name contains spaces and a hyphen.

The following code doesn't work: chart.add_series({'values' : ['Term-Document Match Counts', 1, 1, tds_rows, 1], 'name' : ['Term-Document Match Counts', 0, 1], 'categories' : ['Term-Document Match Counts', 1, 0, tds_rows, 0]})

The following code works: chart.add_series({'values' : ['Term-Document Match Counts', 1, 1, tds_rows, 1], 'name' : ["'Term-Document Match Counts'", 0, 1], 'categories' : ['Term-Document Match Counts', 1, 0, tds_rows, 0]})

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 26, 2014

Owner

Hi @dekatzenel,

I've opened a new issue for this in #205 and pushed a fix as well. You can try it and followup at #205.

Regards,

John

Owner

jmcnamara commented Dec 26, 2014

Hi @dekatzenel,

I've opened a new issue for this in #205 and pushed a fix as well. You can try it and followup at #205.

Regards,

John

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment