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.
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.
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