How do I populate (update) an excel workbook with data from a django model?

user_7114664 0
Viewed 263

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.

This works well. Now what I am trying to do is to update an excel file with these data.

So I am accessing the data in the Django model and trying to use openpyxl to populate an excel with the new data data.

However I am running into problems as I am getting the error saying that it cannot convert to excel.

Is there 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 I'm 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 cannot get step 3.

openpyxl python-3.x django-models django python
3 Answers

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

df = pd.DataFrame(dictionary_from_query)

df.to_excel(filepath)

Or if you 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 you can just use the second method.

user_171807780

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

Please check your openpyxl version.

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

user_89429660
Related