Excel sheet related data

 @action(detail=False, methods=["GET"])

def download_sample_xls(self, request, *args, **kwargs):

header = [

"Created",

"Updated",

"Assset Type",

"Asset Value",

"Description",

"Department",

"Category",

"Location",

"Country",

"Type",

"Designation",

"Manager",

"Owner",

"Subnet",

"Internal IP",

"External IP",

"Operating System",

"Domain",

"Mac Address",

"File Name",

"Vendor",

"Sub-category",

]

custom_header = []

for x in Asset.objects.all().values_list("custom_fields_data"):

data1 = [i for i in x[0]]

custom_header.append(data1)

customdata = []

for i in custom_header:

customdata = customdata + i

c_header = list(dict.fromkeys(customdata))

header.extend(c_header)

header = [i.title() for i in header]

field_names = header

xlsx_file = tempfile.NamedTemporaryFile()

workbook = xlsxwriter.Workbook(xlsx_file.name, {"remove_timezone": True})

worksheet = workbook.add_worksheet()

bold = workbook.add_format({"bold": 1})

worksheet.write_row(0, 0, field_names, bold)

workbook.close()

xlsx_file.file.seek(0)

file_contents = xlsx_file.file.read()

response = HttpResponse(

file_contents,

content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",

)

return response

@action(detail=False, methods=["POST"])

def export_assets_xls(self, request, *args, **kwargs):

# request selected id from frontend

id_list = request.data["selected_ids"]

AssetListData = Asset.objects.filter(id__in=id_list)

Serializer = AssetSerializer(AssetListData, many=True)

AssetData = Response(Serializer).data

AssetData = AssetData.data

final_data = [dict(i) for i in AssetData]

header = [

"Created",

"Updated",

"Assset Type",

"Asset Value",

"Description",

]

extra_header = []

custom_header = []

for x in Asset.objects.filter(id__in=id_list).values_list(

"extra_data", "custom_fields_data"

):

data1 = [i for i in x[0]]

extra_header.append(data1)

custom1 = [i for i in x[1]]

custom_header.append(custom1)

extradata = []

for i in extra_header:

extradata = extradata + i

e_header = list(dict.fromkeys(extradata))

customdata = []

for i in custom_header:

customdata = customdata + i

c_header = list(dict.fromkeys(customdata))

# creating dataframe for first 5 fields df1

dict_val = [

{

"created": j["created"],

"updated": j["updated"],

"asset_type": j["asset_type"],

"value": j["value"],

"description": j["description"],

}

for j in final_data

]

df1 = pd.DataFrame(dict_val)

# creating dataframe for extra_fields df2

extra_f = []

cust_f = []

for i in final_data:

extra_field = []

for j in i.items():

extra_field.append(j[1])

extra_f.append(extra_field[7])

cust_f.append(extra_field[8])

df7 = pd.DataFrame(dict_val)

df7.columns=pd.MultiIndex.from_product([[''],df7.columns])

df5 = pd.DataFrame(extra_f)

df5.columns=pd.MultiIndex.from_product([['extra data'],df5.columns])

e_header.extend(c_header)

header.extend(e_header)

header = [i.title() for i in header]

df4 = pd.DataFrame(cust_f)

df4.columns=pd.MultiIndex.from_product([['custom data'],df4.columns])

df2 = pd.DataFrame(extra_f)

df3 = pd.DataFrame(cust_f)

df = pd.concat([df1, df2, df3], ignore_index=True, sort=False, axis=1)

df.columns = header

df6 = pd.concat([df7,df5,df4], axis=1)

response = HttpResponse(

content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",

)

df6.to_excel(response, sheet_name="Sheet")

response["Content-Disposition"] = "attachment; filename=assets.xlsx"

return response