Feature request: Add support for non-contiguous ranges in charts about xlsxwriter HOT 6 CLOSED

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)

from xlsxwriter.

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:

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)'

from xlsxwriter.

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.

from xlsxwriter.

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.

from xlsxwriter.

Is there something similar to this but for data_validation? So for example I drop duplicates on my Pandas DataFrame, but then want to use the left of values as the source values, however I cannot use it as it exceeds 255 characters. My initial attempt was to just get the index of the series and add two to the values, however I am not sure how this would be written in source

from xlsxwriter.

@mayurpande please don't ask unrelated questions on closed issues like this. It spams everyone on the thread. Anyway, answered on StackOverflow: https://stackoverflow.com/a/66047007/10238

from xlsxwriter.