Python Forum

Full Version: Exporting to Excel
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
i am trying to Export the data to excel

list = [{'ip': u'9.11.180.3','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'14.10.5.5','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"]
            else:
                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"))
        excel_file.save(filename)
[/python]

Link Below : Current and Expected Output :

[Image: iLsQkJc.png]

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 ?
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!
@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
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?
well its like value against each item ... so its in dictionary..
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" ?