2

First of all sorry for my bad english as it is not first language. I have recently started learning python and I am trying to develop a "simple" program, but I have run into a problem.

I am using xlwings to modify and interact with Excel. What I want to achieve (or to know if its possible) is:

I have excel look into data and plot a graph. However this graph sometimes has for example 20 values for the X-Axis and in other cases let's say 10 values for the X-Axis, thus, leaving 10 #NA empty spaces. Based on this, I want to adjust the graph to show only 10 values by changing the range that shapes the graph .

The function get_prod_hours() looks how many values I want on the X-Axis:

def get_prod_hours():
    """From the input gets the production hours to adapt the graphs"""
    dt = wb.sheets['Calculatrice']
    return dt.range('E24').value

Based on the value gotten from the function I must modify the range of values on the graph (by reducing it).

Solutions as for example create the graphs from scratch are not OK because I would like to only modify the range of the graph because the Excel file is a "standard" on my company.

I hope for something like:

Column A in Excel with values: 1, 2, 3, 4, 5 and get from get_prod_hours() a value of 5, so my graph will have only 5 points and not for example 6 of which one is #NA.

Thank you very much, and sorry for the wall of text.

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
Christian Rodriguez
  • 21
  • 1
  • Rather than building a programmatic solution, especially one that is external to Excel, you could use a well-established protocol to make the chart data dynamic using Excel functions. There is a simple example and some links in my tutorial on [Dynamic Charts](https://peltiertech.com/dynamic-charts/). – Jon Peltier Apr 05 '19 at 03:39

1 Answers1

0

The xlwings API doesn't offer a lot of options for charts (see https://docs.xlwings.org/en/stable/api.html?highlight=charts#xlwings.main.Charts).

Try to find the chart in wb.sheets[0].charts.

The range can then be modified with

range = xw.Range((1,1), (get_prod_hours(),1))
set_source_data(wb.sheets[0].range(range))

But from looking at the API and knowing how many options Excel charts have, the API feels too thin.

If this doesn't work, an option is to add a VBA macro which modifies the chart and call that. See How do I call an Excel macro from Python using xlwings?

Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • Hello Aaron, First of all, thank you very much for your answer. The option of changing ...range('A1') I tried it, however, the data of the graphs come from different sources... Is not there a way to shorten the "Horizontal Axis values" on a line graph in any way? – Christian Rodriguez Apr 03 '19 at 13:59
  • You don't shorten the values, you reduce the range to `A1:E1` (if you only want 5 values). That will omit the values in `F1` and up which you don't have. With xlwings, you can even use the numeric value: `xw.Range((1,1), (get_prod_hours(),1))` – Aaron Digulla Apr 03 '19 at 14:32