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

Clustered columns chart with 2-level x-axis #180

Closed
baloubaka opened this Issue Nov 11, 2014 · 4 comments

Comments

2 participants
@baloubaka

baloubaka commented Nov 11, 2014

[Python 2.7.8, xlsxwriter 0.5.2]

I was trying to use xlsxwriter to create a clustered column chart with a 2-level x-axis and encountered an exception because the range I use for the categories was 2D vs. the required 1D. Excel 2013 allows categories to span on 2D (not tested on previous excel versions).

Sample source data to create the chart from:

sample_data

Sample code to create a clustered column chart:

import xlsxwriter

sheetName = 'Sample'
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet(name=sheetName)
headers = ['Types', 'Sub Type', 'Value 1', 'Value 2', 'Value 3']
data = [
    ['Type 1',  'Sub Type 1',   5000,   8000,   6000],
    ['',        'Sub Type 2',   2000,   3000,   4000],
    ['',        'Sub Type 3',   250 ,   1000,   2000],
    ['Type 2',  'Sub Type 1',   6000,   6000,   6500],
    ['',        'Sub Type 2',   500,    300,    200],
]

worksheet.write_row(0, 0, headers)
for i, r in enumerate(data):
    worksheet.write_row(i+1, 0, r)

nCols = len(headers)
nRows = len(data)
chart = workbook.add_chart({'type':'column'})

for i in range(1, 4):
    chart.add_series({
            'name':       'Value %d' % i,
            'categories': [sheetName, 1, 0, nRows, 1], # 2-D Range
            'values':     [sheetName, 1, 2+(i-1), nRows, 2+(i-1)],
        })

chart.set_title({'name':'Sample Data'})
chart.set_x_axis({'name': 'Types'})
chart.set_y_axis({'name': 'Values'})

worksheet.insert_chart(1, nCols + 2, chart)
workbook.close()

Desired result (created with the auto-generated clustered column chart option in Excel):

sample

Exception:
Traceback (most recent call last):
File "D:\work\python\sandbox\misc\xlsxwriter_sample.py", line 38, in
workbook.close()
File "C:\Python27\lib\site-packages\xlsxwriter\workbook.py", line 237, in close
self._store_workbook()
File "C:\Python27\lib\site-packages\xlsxwriter\workbook.py", line 396, in _store_workbook
self._add_chart_data()
File "C:\Python27\lib\site-packages\xlsxwriter\workbook.py", line 1037, in _add_chart_data
(sheetname, cells) = self._get_chart_range(c_range)
TypeError: 'NoneType' object is not iterable

Workaround

A quick workaround consisted in modifying _get_chart_range and _add_chart_data to allow categories to span on more than 1D:

*_add_chart_data *

                # Convert the range formula to a sheet name and cell range.
                allow2D = False
                for s in chart.series:
                    allow2D = 'categories' in s and s['categories'].find(c_range) >= 0
                    if allow2D:
                        break
                (sheetname, cells) = self._get_chart_range(c_range, allow2D)

** get_chart_range **

    def _get_chart_range(self, c_range, allow2D=False):
    ...

        # Check that we have a 1D range only.
        if row_start != row_end and col_start != col_end and not allow2D:
            return None

This is not the cleanest solution but it works for what I need to do.

@jmcnamara jmcnamara self-assigned this Nov 11, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Nov 11, 2014

Owner

Hi,

Thanks for the detailed report.

I'll add that as a feature request and see if it gathers a few +1s.

John

Owner

jmcnamara commented Nov 11, 2014

Hi,

Thanks for the detailed report.

I'll add that as a feature request and see if it gathers a few +1s.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Nov 11, 2014

Owner

Hi,

As a workaround in the meantime you can supply your own category and series data, or leave it blank using the undocumented categories_data and values_data as follows:

for i in range(1, 4):
    chart.add_series({
            'name':       'Value %d' % i,
            'categories': [sheetName, 1, 0, nRows, 1], # 2-D Range
            'values':     [sheetName, 1, 2+(i-1), nRows, 2+(i-1)],
            'categories_data': [],
            'values_data':     [],
        })

Output:

untitled

Leaving the lists empty is okay for Excel since it recalculates the data. However, it may give issues with other applications.

John

Owner

jmcnamara commented Nov 11, 2014

Hi,

As a workaround in the meantime you can supply your own category and series data, or leave it blank using the undocumented categories_data and values_data as follows:

for i in range(1, 4):
    chart.add_series({
            'name':       'Value %d' % i,
            'categories': [sheetName, 1, 0, nRows, 1], # 2-D Range
            'values':     [sheetName, 1, 2+(i-1), nRows, 2+(i-1)],
            'categories_data': [],
            'values_data':     [],
        })

Output:

untitled

Leaving the lists empty is okay for Excel since it recalculates the data. However, it may give issues with other applications.

John

@baloubaka

This comment has been minimized.

Show comment
Hide comment
@baloubaka

baloubaka Nov 13, 2014

Thanks for the interest. Glad it's somewhat helpful. Also, great tip !

baloubaka commented Nov 13, 2014

Thanks for the interest. Glad it's somewhat helpful. Also, great tip !

@jmcnamara jmcnamara added the todo label Mar 19, 2015

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 20, 2015

Owner

Hi,

I've added support for clustered charts to XlsxWriter 0.6.9, now on PyPI.

See the example in the docs

Thanks for your input and suggestion.

John

Owner

jmcnamara commented Mar 20, 2015

Hi,

I've added support for clustered charts to XlsxWriter 0.6.9, now on PyPI.

See the example in the docs

Thanks for your input and suggestion.

John

@jmcnamara jmcnamara closed this Mar 20, 2015

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