Unicode support in charts

Issue #548 resolved
Mikhail Nazarov
created an issue

Hey guys. Thanks for awesome work!

I've tried create a chart and got

'ascii' codec can't encode characters in position 0-9: ordinal not in range(128)
Exception Location: .../openpyxl/utils/__init__.py in quote_sheetname, line 182

The string that could not be encoded/decoded was: Количество пост

Tried to avoid it but got into same error with reference.py line 72

Comments (27)

  1. CharlieC

    Thanks. Since switching to Python 3 for development I don't hit this kind of error as often. I think I have a fix that I'll commit shortly. Can you work with a checkout?

  2. Mikhail Nazarov reporter

    OK, so I moved further, but still get an error.

    I've also fixed:

    range_string = u"{0}!{1}:{2}".format(data.sheetname, v[0], v[-1])
    ../openpyxl/chart/_chart.py in add_data, line 160
    title = u"{0}!{1}".format(values.sheetname, cell)
    .../openpyxl/chart/series_factory.py in SeriesFactory, line 20

    But this thing still throws an error expected <type 'unicode'>

    raise TypeError('expected ' + str(expected_type))
    .../openpyxl/descriptors/base.py in _convert, line 57
  3. CharlieC

    It's very difficult to work with the errors piecemeal. Can you please either provide a sample script for what your doing; or, preferably, write test cases for the relevant parts of the code?

  4. Mikhail Nazarov reporter

    Sorry, I'm not yet that confident with tests. But here's a sample script that throws the error

    # -*- coding: utf-8 -*-
    from openpyxl import Workbook
    from openpyxl.chart import BarChart, Reference, Series
    wb = Workbook()
    ws = wb.active
    title = u'Количество постов'
    ws.title = title
    for i in range(10):
    values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart = BarChart()
  5. Mikhail Nazarov reporter

    Got it, will try to keep my future code samples as clean as possible. It's just a copy-paste from openpyxl documentation. I though it would be nice to see some colorful bars when the thing finally works.

    So will you be able to look into the issue? Thanks again!

  6. CharlieC

    Yes, working on it at the moment. It's an interesting one. Excel sprays references to the "parent" worksheet throughout the chart and I worked hard to avoid this in client code while keeping the code robust and generic.

  7. Anton Yanchenko


    Looks like it was broken again. If you try example from this ticket you'll get xlsx without charts and if you change worksheet's title on something like "qwe qwe" everything would work fine.

    Could you look at this one more time please? Thanks in advance.

  8. Anton Yanchenko

    I'm trying with python 2.7.10 and openpyxl 2.4.5.

    Python 3 isn't an option for me, but I've tested it too and after a few attempts found that this isn't so trivial bug and somehow it depends on worksheet title. For example if I set title in code from this ticket to u"тест тест" than I would get xlsx whith charts. But if I set title to u"тест не тест" - charts are missing (in both cases: python 2.7.10 and 3.6.0).

  9. CharlieC

    I think you're experiencing something different. I can create files with charts with that title but you will get problems if you change the worksheet title after creating the chart because the worksheet title is hardwired into data references.

  10. Anton Yanchenko

    Originally I'd met this issue in the code which wasn't modifying sheet's title.

    Tried to reproduce this with creating new sheet with given title instead of modifying existing one and still have no charts in it. But then I'd uploaded it to google drive and found chart in google's document preview. Looks like it reproducable only with OS X's Numbers (unfortunately I have no MS Excel).

    Just in case, you could find my generated xlsx file here:


    Thank you for help in diagnosing the root of the problem. Let me know if you would wish to investigate this problem further. :)

  11. CharlieC

    Then it's a separate issue but basically Apple's problem as Numbers seems to struggle with the non-ascii title. Excel and LibreOffice have no problem and the generated file is valid.

    Apple have in the past fixed problems related to Numbers, presumably because they value compatibility more the MS does, notably with the change in packaging we made in 2.4 I suggest you create and issue here and that is linked to bug report made to Apple.

  12. CharlieC

    The problem is not unicode per se but the combination of unicode and spaces in worksheet titles which means the sheet name has to be quoted. So a sheet called "Düsseldorf" with a chart is fine but if the sheet is called "In Düsseldorf" Numbers which might make it easier for the Apple developers to create a test case.

  13. Anton Yanchenko

    I'm still not sure that the problem is exactly in Numbers because I can add charts manually through interface on sheet with problem title and after reopening document they are still present. So either Numbers saves charts in some another way or openpyxl makes some difference for this case.

  14. CharlieC

    It's not about what you think is the problem, it's about how well the files conform to the specification. You can work with the XML source and make only that change. openpyxl produces valid OOXML but requires "consuming applications" to do some work to render charts.

    Unfortunately, too many applications and libraries seem to rely more on reverse engineering what MS Excel produces rather than sticking to an ECMA / ISO standard.

    See #677 for a similar example which Apple seems to have patched in the meantime, though I never got any notifications.

  15. Log in to comment