Python Forum
Exporting to Excel - Printable Version

+- Python Forum (
+-- Forum: Python Coding (
+--- Forum: General Coding Help (
+--- Thread: Exporting to Excel (/Thread-Exporting-to-Excel)

Exporting to Excel - Nirmal - Jul-26-2019

i am trying to Export the data to excel

list = [{'ip': u'','endpoint': u'rd2ff5-az2-1519-1520_eth1-18_vpc', 'hostname': u'RD2APIC1', 'encap': u'vlan-42', 'epg': u'19_14_140_172', 'extpaths': u'topology/pod-2/protpaths-1519-1520/pathep-[rd2ff5-az2-1519-1520_eth1-18_vpc]'},{'ip': u'','endpoint': u'eth1/2','hostname': u'RD2APIC1', 'encap': u'vlan-18', 'epg': u'14_70_7_0', 'extpaths': u'topology/pod-2/paths-1511/extpaths-102/pathep-[eth1/2]'}]
My Code :

filename = self.controller + ".xls"
        excel_file = xlwt.Workbook()
        sheet = excel_file.add_sheet('HOSTLIST')
        sheet.write(0, 0, "APIC")            
        sheet.write(0, 1, "VLAN")
        sheet.write(0, 2, "EPG")
        sheet.write(0, 3, "EndPoint")
        sheet.write(0, 4, "Extpath")
        for count in xrange(1, len(list) + 1):
            if list[count - 1]["epg"]:
                epg = list[count - 1]["epg"]
                epg = "UNKNOWN"

            sheet.write(count, 0, list[count - 1].get("hostname","UNKNOWN"))
            sheet.write(count, 1, list[count - 1].get("encap","UNKNOWN"))
            sheet.write(count, 2, epg)
            sheet.write(count, 3, list[count - 1].get("endpoint","UNKNOWN"))
            sheet.write(count, 4, list[count - 1].get("extpaths","UNKNOWN"))

Link Below : Current and Expected Output :


I have uploaded the image , its about current and expected output . Basically i need to split the "endpoint" if it starts with "rd" while exporting it to excel.

in this case "endpoint - rd2ff5-az2-1519-1520_eth1-18_vpc" needs to be splited as rd2ff5-az2-1519 and rd2ff5-az2-1520 in two rows inserted instead of one in the excel sheet.

Any suggestion/idea ?

RE: Exporting to Excel - Nuzvee - Aug-02-2019

This is a shot from the hip but mabye something in this direction?

Before you react to my shitty coding, this code would most likely work if inserted "as is", im tired af atm (friday). This is more of a suggestion of how one could go about it.
And this might also be pure nonsense.... super tired :P

if "rd" in list[1][:2]: 
# list[1] is ment to be the part with endpoint. in my head it makes sense at this moment, when im not as tired, its likely that im way off
	endpoint = list[1].split('-')
	endpoint_1 = str(endpoint[0]) + str(endpoint[1]) + str(endpoint[2]) + str(endpoint[4])
	endpoint_2 = str(endpoint[0]) + str(endpoint[1]) + str(endpoint[3]) + str(endpoint[4])
	list_1 = list
	list_2 = list
	list_1[1] = endpoint_1
	list_2[1] = endpoint_2
Again, if its rubbish or simply stupid, please disregard this reply :).

Have a nice weekend!

RE: Exporting to Excel - Nirmal - Aug-08-2019

@Nuzvee : this says :

----> 2 endpoint = list[1].split('-')
      3 print endpoint

AttributeError: 'dict' object has no attribute 'split'
it says this too :

TypeError: unhashable type

RE: Exporting to Excel - Nuzvee - Aug-08-2019

Aha, missed that it was a dict :) As i mentioned, i was tired xD.

May i though ask why u put it as a dict?
I am fairly new to programming overall so my basic knowledge is less than ideal, but wouldnt it be easier to set it as a list and exclude the column name from the dict and make it a list, and then define the columns with arrays for the list instead?

RE: Exporting to Excel - Nirmal - Aug-08-2019

well its like value against each item ... so its in dictionary..

RE: Exporting to Excel - Nirmal - Aug-08-2019

can we remove the section after "_ " in "endpoint"and export that to excel ? for e:g rd2ff5-az2-1519-1520_eth1-18_vpc needs to be "rd2ff5-az2-1519-1520" ?