Populate an excel with data from a django model

I populated a Django model with a pandas dataframe. In order to do this is had I used the to_dict() function in order for JSONField() Django model to accept the data. Is fine and works well. No what I am trying to do is to populate an excel file with this data. So I am accessing the data in the Django model and trying to use openpyxl to populate an excel with this data. However I am running into problems as I am getting the error saying that it cannot convert to excel. Anyway around this?

My model:

class myvalues(models.Model):
    items_list = models.JSONField()

function that’s trying to populate excel:

values_to_go_into_excel_obtained_from_model = myvalues.objects.values_list(“items_list”, flat=True)
path = file_for_download.my_file.path
wb = load_workbook(path)
ws = wb["Sheet1"]
pd.read_excel(path, engine='openpyxl', sheet_name="Sheet1")

for row, v in enumerate(values_to_go_into_excel_obtained_from_model, 2):
    ws.cell(row, 4, v)

wb.save(path)

Any ideas why I am getting the error. Basically what Im trying to do is:

Step 1: pandas df -> Django model of type Json
Step 2: Access Django model values
Step 3: put values from Django model into excel file

I have step 1 and 2 but for cant get step 3.

If u want to save one particular json into the excel file

df = pd.DataFrame(dictionary_from_query)

df.to_excel(filepath)

Or if u need to add multiple jsons, add them to a list then use that list to initialize the dataframe

ds = []
for ...:      # your loop
    ds += [d] # where d is one of the dicts

df = pd.DataFrame(ds)

df.to_excel(filepath)

In your case I think the dictionaries are already in a list. Therefore u can just use the second method

Following https://stackoverflow.com/a/55131749/1021819, it seems possible that you are trying to write a list to a cell (which is a way to provoke the error you are seeing).

So you need to loop over the list:

values_to_go_into_excel_obtained_from_model = myvalues.objects.values_list(“items_list”, flat=True)
path = file_for_download.my_file.path
wb = load_workbook(path)
ws = wb["Sheet1"]
# pd.read_excel(path, engine='openpyxl', sheet_name="Sheet1") # not required!
    
for row, v in enumerate(values_to_go_into_excel_obtained_from_model, 2):
    for row_val, i_row_val in enumerate(row):
        ws.cell(row_val, 4+i_row_val, v)
    
wb.save(path)

Please check your openpyxl version.

openpyxl 3.0.2 has a bug in generating Excel pages. This may be the issue

Back to Top