Plot a graph with results from excel

🗪 Support (Q&A)
1

Hi all,

I currently working on a tool that uses Excel spreadsheets to calculate the results. Now I would like to show the results in a line-graph. It keeps only showing the x- and y-axis with no line.

This is my code snippet, maybe someone can help me with this:

excel_file_path = Path(__file__).parent / "break-even-kalk.xlsx"
      workbook = File.from_path(excel_file_path)
      sheet = SpreadsheetCalculation(workbook, input_list)
      result = sheet.evaluate()

      x_data = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
      y_data = [result.values["kumulierter Barwert_0"], result.values["kumulierter Barwert_1"], result.values["kumulierter Barwert_2"], result.values["kumulierter Barwert_3"], result.values["kumulierter Barwert_4"], result.values["kumulierter Barwert_5"],
        result.values["kumulierter Barwert_6"],result.values["kumulierter Barwert_7"],result.values["kumulierter Barwert_8"],result.values["kumulierter Barwert_9"],result.values["kumulierter Barwert_10"],
        result.values["kumulierter Barwert_11"],result.values["kumulierter Barwert_12"],result.values["kumulierter Barwert_13"],result.values["kumulierter Barwert_14"],result.values["kumulierter Barwert_15"],
        result.values["kumulierter Barwert_16"],result.values["kumulierter Barwert_17"],result.values["kumulierter Barwert_18"],result.values["kumulierter Barwert_19"],result.values["kumulierter Barwert_20"],
        result.values["kumulierter Barwert_21"],result.values["kumulierter Barwert_22"],result.values["kumulierter Barwert_23"],result.values["kumulierter Barwert_24"],result.values["kumulierter Barwert_25"],
        result.values["kumulierter Barwert_26"],result.values["kumulierter Barwert_27"],result.values["kumulierter Barwert_28"],result.values["kumulierter Barwert_29"],result.values["kumulierter Barwert_30"]]

      fig_3 = {
        "data": [
          {"type": "line",
          "x": x_data, 
          "y": y_data }],
        "layout": {
          "title": {"text": f"Amortisationsdauer der BIPV(T)-Anlage"},
          "xaxis": {"title": {"text": "Lebensdauer in Jahren"}},
          "yaxis": {"title": {"text": "Kumulierter Barwert in €"}},
        },
      }
      
      summary = DataGroup(
        DataItem(
          label = "Investitionskosten",
          value = result.values["Investitionskosten"],
          suffix="€"
        ), 
        DataItem(
          label = "Amortisationsdauer",
          value = result.values["Break-Even-Point"], 
          suffix="Jahre"
        )
      )

      return PlotlyAndDataResult(fig_3, summary)
2

Hi @Nanebe,

Welcome to the community!

I don’t see an error in your snippet. Most likely your result.values returns empty, could you check that by printing the value?

Could you check this tutorial, to verify that your calculation sheet is properly set-up? (it needs a viktor-input-sheet and viktor-output-sheet)

Thanks!

3

Hi,

eher values from my excel are returned empty. But in the Excel the results are shown as one can see in the following screenshot:

Bildschirmfoto 2023-10-05 um 15.38.59
Bildschirmfoto 2023-10-05 um 15.38.59471×578 10.6 KB

The printed values only work for 2 out of 33 results.
Do you know what might be the issue here?

{'Barwert_0': -4316.666666666667, 'Barwert_1': None, 'Barwert_2': None, 'Barwert_3': None, 'Barwert_4': None, 'Barwert_5': None, 'Barwert_6': None, 'Barwert_7': None, 'Barwert_8': None, 'Barwert_9': None, 'Barwert_10': None, 'Barwert_11': None, 'Barwert_12': None, 'Barwert_13': None, 'Barwert_14': None, 'Barwert_15': None, 'Barwert_16': None, 'Barwert_17': None, 'Barwert_18': None, 'Barwert_19': None, 'Barwert_20': None, 'Barwert_21': None, 'Barwert_22': None, 'Barwert_23': None, 'Barwert_24': None, 'Barwert_25': None, 'Barwert_26': None, 'Barwert_27': None, 'Barwert_28': None, 'Barwert_29': None, 'Barwert_30': None, 'Investitionskosten': 4316.67, 'Break-Even-Point': None}
4

Hi,
I found the problem within the excel sheet.
There seemed to be an issue with a calculation including a percentage.

Thanks for the help.
Nane

2 Likes
5

That’s great Nane!
Hope to see more of the app you’re making in the future :wink: