How can I get JSON from excel file for Nifi using openpyxl?

I am trying to get data from Excel cells. I need only particular columns and rows. I want to put the information from these cells to Nifi. For that I have to convert data I receive from Excel to Json. And I'm stuck with that. I can't use pandas, only openpyxl, because Nifi doesn't work with Jython. I need a string in json format, not json file. And I can't convert the data to json string.

So, I used that code to receive my data:



sheet = ws1



data = []

for i in range(3, 13):

        filial = sheet.cell(row=i, column=1).value

        kvartal1 = sheet.cell(row=i, column=2).value

        kvartal2 = sheet.cell(row=i, column=3).value

        kvartal3 = sheet.cell(row=i, column=4).value

        kvartal4 = sheet.cell(row=i, column=5).value



        row1 = {filial:kvartal1}

        data.append(row1)

What it gives me is that:

[{'data1': 0}, {'data2': 0}, {'data3': 0}, {'data4': 0}, {'data5': 0}, {'data6': 0}, {'data7': None}, {'data8': None}, {'data9': None}]

And so on. How can I receive the correct json string or make it correct? Json.dump doesn't work properly too. I need a string in a format like:

{"data1": 0, "data2": 0, "data3": 0}

Any suggestions? Maybe I'm getting it all wrong and there are other ways to put an excel data in Nifi.

UPD maybe I should convert excel to xml and then load it to Nifi?

Solution

use dictionary comprehension,example:

data = [{'data1': 0}, {'data2': 0}, {'data3': 0}, {'data4': 0}, {'data5': 0}, {'data6': 0}, {'data7': None}, {'data8': None}, {'data9': None}]

print({k: v for d in data for k, v in d.items()})
{'data1': 0, 'data2': 0, 'data3': 0, 'data4': 0, 'data5': 0, 'data6': 0, 'data7': None, 'data8': None, 'data9': None}

hope this helps.