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

Feature request: Add support for non-contiguous ranges in charts #44

Closed
jmcnamara opened this Issue Sep 11, 2013 · 4 comments

Comments

2 participants
@jmcnamara
Owner

jmcnamara commented Sep 11, 2013

Add support for non-contiguous category/range values in chart data.

@ghost ghost assigned jmcnamara Sep 11, 2013

@vpapaioannou

This comment has been minimized.

Show comment
Hide comment
@vpapaioannou

vpapaioannou Nov 29, 2013

I want to use this feature, as soon as possible because of my diploma thesis. I am willing to try to add it by myself. Would you like to give me any guidelines?

For the moment, I have these categories and values :

categories = '=' + sheet + '!$A$8,' + sheet + '!$A$21,' + sheet + '!$A$34,' + sheet + '!$A$47,' + sheet + '!$A$60'
values = '=' + sheet + '!$E$11,' + sheet + '!$E$24,' + sheet + '!$E$37,' + sheet + '!$E$50,' + sheet + '!$E$63'

and I get this error,

File "cpusToReducers.py", line 608, in
main()
File "cpusToReducers.py", line 143, in main
excel.close()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 235, in close
self._store_workbook()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 394, in _store_workbook
self._add_chart_data()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 1035, in _add_chart_data
(sheetname, cells) = self._get_chart_range(c_range)
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 1072, in _get_chart_range
sheetname, cells = c_range.split('!')
ValueError: too many values to unpack (expected 2)

where, everything irrelevant to xlsxwriter concerns to my code. The problem seems to be the extra '!' which are needed to separate every sheet name from the corresponding cell. When I exclude the extra '!' everything works predictably.

WORKAROUND

A possible, starting workaround following Excel notation for not
contiguous ranges, i.e.

not_contiguous_ranges = '= Sheet1!Range1, Sheet2!Range2, ...., SheetN!RangeN

it would be, inside _get_chart_range(self, c_range)

  1. split on ',' and take comma parts
  2. for each comma part, split on '!' and this way build a list of ( sheet_name, cells)
  3. for each element of ( sheet_name, cells) do the conversion for example from
    Sheet1!$B$1:$B$5 to ( 'Sheet1', 0, 1, 4, 1 ).
  4. return a list of tuples ( sheet_name, start_row, start_col, end_row, end_col)

vpapaioannou commented Nov 29, 2013

I want to use this feature, as soon as possible because of my diploma thesis. I am willing to try to add it by myself. Would you like to give me any guidelines?

For the moment, I have these categories and values :

categories = '=' + sheet + '!$A$8,' + sheet + '!$A$21,' + sheet + '!$A$34,' + sheet + '!$A$47,' + sheet + '!$A$60'
values = '=' + sheet + '!$E$11,' + sheet + '!$E$24,' + sheet + '!$E$37,' + sheet + '!$E$50,' + sheet + '!$E$63'

and I get this error,

File "cpusToReducers.py", line 608, in
main()
File "cpusToReducers.py", line 143, in main
excel.close()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 235, in close
self._store_workbook()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 394, in _store_workbook
self._add_chart_data()
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 1035, in _add_chart_data
(sheetname, cells) = self._get_chart_range(c_range)
File "/usr/local/lib/python3.3/dist-packages/xlsxwriter/workbook.py", line 1072, in _get_chart_range
sheetname, cells = c_range.split('!')
ValueError: too many values to unpack (expected 2)

where, everything irrelevant to xlsxwriter concerns to my code. The problem seems to be the extra '!' which are needed to separate every sheet name from the corresponding cell. When I exclude the extra '!' everything works predictably.

WORKAROUND

A possible, starting workaround following Excel notation for not
contiguous ranges, i.e.

not_contiguous_ranges = '= Sheet1!Range1, Sheet2!Range2, ...., SheetN!RangeN

it would be, inside _get_chart_range(self, c_range)

  1. split on ',' and take comma parts
  2. for each comma part, split on '!' and this way build a list of ( sheet_name, cells)
  3. for each element of ( sheet_name, cells) do the conversion for example from
    Sheet1!$B$1:$B$5 to ( 'Sheet1', 0, 1, 4, 1 ).
  4. return a list of tuples ( sheet_name, start_row, start_col, end_row, end_col)
@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Nov 29, 2013

Owner

The main issue here is that XlsxWriter needs to parse and read the data from the chart ranges so that it can be added to the chart as cached data.

This isn't strictly required by Excel, it generally recalculates the data when the file is opened. However, it can affect the display of the chart in other third party applications and when the chart is embedded in other applications such as Powerpoint.

However, if you don't need that then you can supply your own (potentially empty) data to the chart using the undocumented categories_data and values_data series options and then XlsxWriter will accept any formula string, including one for non-contiguous ranges.

For example:

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

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

# Write some data to add to plot on the chart.
data = [[1,  2,   3,  4,  5],
        [22, 22, 99, 22, 22]]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])


# Configure the chart.
chart.add_series({'categories': '=(Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5)', 
                  'values':     '=(Sheet1!$B$1:$B$2,Sheet1!$B$4:$B$5)', 
                  'categories_data': [],
                  'values_data':     [],
                  })


# Insert the chart into the worksheet.
worksheet.insert_chart('D3', chart)

workbook.close()

Note that the data (3, 99) isn't shown:

chart1

You just need to ensure that the formulas matches what Excel requires, including the parentheses.

 '=(Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5)'
Owner

jmcnamara commented Nov 29, 2013

The main issue here is that XlsxWriter needs to parse and read the data from the chart ranges so that it can be added to the chart as cached data.

This isn't strictly required by Excel, it generally recalculates the data when the file is opened. However, it can affect the display of the chart in other third party applications and when the chart is embedded in other applications such as Powerpoint.

However, if you don't need that then you can supply your own (potentially empty) data to the chart using the undocumented categories_data and values_data series options and then XlsxWriter will accept any formula string, including one for non-contiguous ranges.

For example:

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

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

# Write some data to add to plot on the chart.
data = [[1,  2,   3,  4,  5],
        [22, 22, 99, 22, 22]]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])


# Configure the chart.
chart.add_series({'categories': '=(Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5)', 
                  'values':     '=(Sheet1!$B$1:$B$2,Sheet1!$B$4:$B$5)', 
                  'categories_data': [],
                  'values_data':     [],
                  })


# Insert the chart into the worksheet.
worksheet.insert_chart('D3', chart)

workbook.close()

Note that the data (3, 99) isn't shown:

chart1

You just need to ensure that the formulas matches what Excel requires, including the parentheses.

 '=(Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5)'
@vpapaioannou

This comment has been minimized.

Show comment
Hide comment
@vpapaioannou

vpapaioannou Nov 30, 2013

Your solution works perfectly. Thanks a lot, not only for the great answer( accurate + example), but also for the instant reply. Generally, I am very satisfied from xlsxwriter. It's the best in its category, excellent documentation full of examples, easy to use, write the minimum, needed amount of code, full of useful features.

Finally, I would suggest undocumented but existent features to be documented in a separate chapter. This chapter could have a title such as "Experimental features" or "Alpha features" to clarify the "nature" of these features and warn somehow the programmer for any "risks" of these features. This way, a greater amount of features would be available for the benefit of all.

vpapaioannou commented Nov 30, 2013

Your solution works perfectly. Thanks a lot, not only for the great answer( accurate + example), but also for the instant reply. Generally, I am very satisfied from xlsxwriter. It's the best in its category, excellent documentation full of examples, easy to use, write the minimum, needed amount of code, full of useful features.

Finally, I would suggest undocumented but existent features to be documented in a separate chapter. This chapter could have a title such as "Experimental features" or "Alpha features" to clarify the "nature" of these features and warn somehow the programmer for any "risks" of these features. This way, a greater amount of features would be available for the benefit of all.

jmcnamara added a commit that referenced this issue Dec 30, 2013

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 30, 2013

Owner

I've added support for non-contiguous chart ranges without having to use the _data workaround.

It is on the master branch now and will be in the next PyPI release.

Owner

jmcnamara commented Dec 30, 2013

I've added support for non-contiguous chart ranges without having to use the _data workaround.

It is on the master branch now and will be in the next PyPI release.

@jmcnamara jmcnamara closed this Dec 30, 2013

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