Arranging JSON Data in excel file in Django

Question

I am exporting data from database using this function:

def export_packing_xls(request):
    response = HttpResponse(content_type='application/ms-excel')
    file_name = "packing_list_"+str(datetime.now().date())+".xls"
    response['Content-Disposition'] = 'attachment; filename="'+ file_name +'"'
 
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('Packing List')

    date1 = request.GET.get('exportStartDate')
    date2 = request.GET.get('exportEndDate')
 
    # Sheet header, first row
    row_num = 0
 
    font_style = xlwt.XFStyle()
    font_style.font.bold = True
 
    columns = ['Ref Number', 'Description of Goods','QTY','Gross WT',]
 
    for col_num in range(len(columns)):
        ws.write(row_num, col_num, columns[col_num], font_style)
 
    # Sheet body, remaining rows
    font_style = xlwt.XFStyle()
 
    rows = Booking.objects.filter(created_at__range =[date1, date2]).values_list('booking_reference', 'product_list', 'gross_weight',)
    for row in rows:
        row_num += 1
        for col_num in range(len(row)):
            ws.write(row_num, col_num, row[col_num], font_style)
            print(row[col_num][1][:4])

    wb.save(response)
    return response

I am getting this kind of :

Ref Number  Description of Goods    QTY Gross WT
AWBO114 [{"id":1,"Name":"T shirt","Quantity":"10","Weight":"2","WeightTypes":"KG","TotalWeight":"20","CustomsCharge":"20.0","Discount":"12","Subtotal":"188"}]  12  22
AWBO117 [{"id":1,"Name":"T shirt","Quantity":"15","Weight":"45","WeightTypes":"KG","TotalWeight":"675","CustomsCharge":"20.0","Discount":"45","Subtotal":"255"}]    45  455
AWBO118 [{"id":1,"Name":"Fan","Quantity":"12","Weight":"12","WeightTypes":"KG","TotalWeight":"144","CustomsCharge":"100.0","Discount":"12"},{"id":2,"Name":"T shirt","Quantity":"22","Weight":"5","WeightTypes":"KG","TotalWeight":"110","CustomsCharge":"20.0","Discount":""}] 15  15
AWBO121 [{"id":1,"Name":"T shirt","Quantity":"12","Weight":"12","WeightTypes":"KG","TotalWeight":"144","CustomsCharge":"20.0","Discount":"12","Subtotal":"228"}]    0   20
AWBO122 [{"id":1,"Name":"T shirt","Quantity":"12","Weight":"12","WeightTypes":"KG","TotalWeight":"144","CustomsCharge":"20.0","Discount":"12","Subtotal":"228"}]    12  12

But i want to make a list for "Description of Goods" As like this :

How can i list out my JSON data in the Excel file?

If you have anything to know, please let me know in the comment section. Thank you.

Have you tried `write_merge`? https://stackoverflow.com/questions/19672760/how-to-write-a-cell-with-multiple-columns-in-xlwt — Tonio, Nov 04 '21 at 07:45
You need to iterate over the json on the column 2, so u can navigate on the details — Walucas, Nov 04 '21 at 08:48
@Walucas How can i iterate over Json data? I tried with `row[col_num]['Name']` this command , but it's __string indices must be integers__ if i use `row[col_num][0]` this command __'float' object is not subscriptable__ How can i get value please? — Md Azharul Islam Somon, Nov 04 '21 at 09:05
@JoseAntonioCastroCastro Yes i have seen this, but i am not able to get value from Json list. — Md Azharul Islam Somon, Nov 04 '21 at 09:07

score 1 · Answer 1 · answered Nov 04 '21 at 09:06

1

To iterate over json from string you need this:

import json

myJson = json.loads(row[col_num])
#now you can do:
print(myJson['Name'])

answered Nov 04 '21 at 09:06

Walucas

2,549
1
21
44

1

Thanks for your answer, but I have to use `myJson = json.dumps(rows[col_num]) neJson = json.loads(myJson) print(neJson[2])` – Md Azharul Islam Somon Nov 04 '21 at 09:59

Arranging JSON Data in excel file in Django

1 Answers1