Python Forum
beginner level Python:- output XLS spreadsheet instead of using tabulate
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
beginner level Python:- output XLS spreadsheet instead of using tabulate
#1
I have a python script that uses the 'print tabulate' to output the data in CLI.
I would like an easy way output to XLS workbook using 'xlwt' instead.

This is the working python script that outputs to 'print tabulate'
#!/usr/bin/env python
################################################################################
# _ ____ ___ _____ _ _ _ _ #
# / \ / ___|_ _| |_ _|__ ___ | | | _(_) |_ #
# / _ \| | | | | |/ _ \ / _ \| | |/ / | __| #
# / ___ \ |___ | | | | (_) | (_) | | <| | |_ #
# ____ /_/ \_\____|___|___|_|\___/ \___/|_|_|\_\_|\__| #
# / ___|___ __| | ___ / ___| __ _ _ __ ___ _ __ | | ___ ___ #
# | | / _ \ / _ |/ _ \ \___ \ / _ | '_ ` _ \| '_ \| |/ _ \/ __| #
# | |__| (_) | (_| | __/ ___) | (_| | | | | | | |_) | | __/\__ \ #
# \____\___/ \__,_|\___| |____/ \__,_|_| |_| |_| .__/|_|\___||___/ #
# |_| #
################################################################################
# #
# Copyright © 2015 Cisco Systems #
# All Rights Reserved. #
# #
# Licensed under the Apache License, Version 2.0 (the "License"); you may #
# not use this file except in compliance with the License. You may obtain #
# a copy of the License at #
# #
# http://www.apache.org/licenses/LICENSE-2.0 #
# #
# Unless required by applicable law or agreed to in writing, software #
# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT #
# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the #
# License for the specific language governing permissions and limitations #
# under the License. #
# #
################################################################################
"""
Simple application that logs on to the APIC and displays all
of the Endpoints.
"""
import acitoolkit.acitoolkit as aci
from tabulate import tabulate


def main():
"""
Main Show Endpoints Routine
:return: None
"""
# Take login credentials from the command line if provided
# Otherwise, take them from your environment variables file ~/.profile
description = ('Simple application that logs on to the APIC'
' and displays all of the Endpoints.')
creds = aci.Credentials('apic', description)
args = creds.get()

# Login to APIC
session = aci.Session(args.url, args.login, args.password)
resp = session.login()
if not resp.ok:
print('%% Could not login to APIC')
return

# Download all of the interfaces
# and store the data as tuples in a list
data = []
endpoints = aci.Endpoint.get(session)
for ep in endpoints:
epg = ep.get_parent()
app_profile = epg.get_parent()
tenant = app_profile.get_parent()
data.append((ep.mac, ep.ip, ep.if_name, ep.encap,
tenant.name, app_profile.name, epg.name))

# Display the data downloaded
print tabulate(data, headers=["MACADDRESS", "IPADDRESS", "INTERFACE",
"ENCAP", "TENANT", "APP PROFILE", "EPG"])

if __name__ == '__main__':
try:
main()
except KeyboardInterrupt:
pass
Reply
#2
Code in this form is unreadable. Please post it in Python code tags. You can find help here.
Reply
#3
"""
Simple application that logs on to the APIC and displays all
of the Endpoints.
"""
import acitoolkit.acitoolkit as aci
from tabulate import tabulate


def main():
    """
    Main Show Endpoints Routine
    :return: None
    """
    # Take login credentials from the command line if provided
    # Otherwise, take them from your environment variables file ~/.profile
    description = ('Simple application that logs on to the APIC'
                   ' and displays all of the Endpoints.')
    creds = aci.Credentials('apic', description)
    args = creds.get()

    # Login to APIC
    session = aci.Session(args.url, args.login, args.password)
    resp = session.login()
    if not resp.ok:
        print('%% Could not login to APIC')
        return

    # Download all of the interfaces
    # and store the data as tuples in a list
    data = []
    endpoints = aci.Endpoint.get(session)
    for ep in endpoints:
        epg = ep.get_parent()
        app_profile = epg.get_parent()
        tenant = app_profile.get_parent()
        data.append((ep.mac, ep.ip, ep.if_name, ep.encap,
                     tenant.name, app_profile.name, epg.name))

    # Display the data downloaded
    print tabulate(data, headers=["MACADDRESS", "IPADDRESS", "INTERFACE",
                                  "ENCAP", "TENANT", "APP PROFILE", "EPG"])

if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        pass

I have just managed to do a CSV version..
"""
Simple application that logs on to the APIC and displays all
of the Endpoints.
"""
import acitoolkit.acitoolkit as aci
import csv


def main():
    """
    Main Show Endpoints Routine
    :return: None
    """
    # Take login credentials from the command line if provided
    # Otherwise, take them from your environment variables file ~/.profile
    description = ('Simple application that logs on to the APIC'
                   ' and displays all of the Endpoints.')
    creds = aci.Credentials('apic', description)
    args = creds.get()

    # Login to APIC
    session = aci.Session(args.url, args.login, args.password)
    resp = session.login()
    if not resp.ok:
        print('%% Could not login to APIC')
        return

    # Download all of the interfaces
    # and store the data as tuples in a list
    data = []
    endpoints = aci.Endpoint.get(session)
    for ep in endpoints:
        epg = ep.get_parent()
        app_profile = epg.get_parent()
        tenant = app_profile.get_parent()
        data.append((ep.mac, ep.ip, ep.if_name, ep.encap,
                     tenant.name, app_profile.name, epg.name))

    # Display the data downloaded
    myFile = open('ACI-Endpoints.csv', 'w')
    with myFile:
    	writer = csv.writer(myFile)
    	writer.writerows(data)
     
print("Writing complete")

if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        pass
Reply
#4
Thanks for posting your solution. I recommend you to alter the part of code you use to open and write to file. See here how context managers (with) are usually used with files.
Reply
#5
Now I would like to output to XLS workbook using 'xlwt' instead.
I would also like to keep the original headers
["MACADDRESS", "IPADDRESS", "INTERFACE","ENCAP", "TENANT", "APP PROFILE", "EPG"]

import xlwt
	wb = xlwt.Workbook()
	ws = wb.add_sheet('EndPoints')

	ws.write(--==Just working on this bit==--)

	wb.save('ACI-Endpoints.xls')  

(Jan-27-2018, 05:49 PM)j.crater Wrote: Thanks for posting your solution. I recommend you to alter the part of code you use to open and write to file. See here how context managers (with) are usually used with files.

is this better?

    # Write downloaded data to ACI-Endpoints.csv
    myFile = open('ACI-Endpoints.csv', 'w')
    with myFile as f:
    	writer = csv.writer(myFile)
    	writer.writerows(data)
    	f.closed
    	
Reply
#6
Took me all day but got there in the end..
If there is a better way please comment (totally new to this)

"""
Simple application that logs on to the APIC and displays all
of the Endpoints.
"""
import acitoolkit.acitoolkit as aci
import xlwt
from datetime import datetime
import time


def main():
    """
    Main Show Endpoints Routine
    :return: None
    """
    # Get timestamp for filename output
    now = time.time()
    local_time = time.localtime(now)
    timestamp = time.strftime("%Y-%m-%d-%H%M", local_time)
    
    # Take login credentials from the command line if provided
    # Otherwise, take them from your environment variables file ~/.profile
    description = ('Simple application that logs on to the APIC'
                   ' and displays all of the Endpoints.')
    creds = aci.Credentials('apic', description)
    args = creds.get()

    # Login to APIC
    session = aci.Session(args.url, args.login, args.password)
    resp = session.login()
    if not resp.ok:
        print('%% Could not login to APIC')
        return

    # Download all of the interfaces
    # and store the data as tuples in a list
    data = []
    endpoints = aci.Endpoint.get(session)
    for ep in endpoints:
        epg = ep.get_parent()
        app_profile = epg.get_parent()
        tenant = app_profile.get_parent()
        data.append((ep.mac, ep.ip, ep.if_name, ep.encap,
                     tenant.name, app_profile.name, epg.name))

    # Write downloaded data to ACI-Endpoints.xls
	result_excel_file_name = "ACI-Endpoints-" + timestamp + ".xls"
	headers=["MACADDRESS", "IPADDRESS", "INTERFACE",  "ENCAP", "TENANT", "APP PROFILE", "EPG"]

	wb = xlwt.Workbook(encoding='utf-8')
	ws = wb.add_sheet('EndPoints')
	
    rowx = 0
    for colx, value in enumerate(headers):
        ws.write(rowx, colx, value)
    ws.set_panes_frozen(True)  # frozen headings instead of split panes
    ws.set_horz_split_pos(rowx + 1)  # in general, freeze after last heading row
    ws.set_remove_splits(True)  # if user does unfreeze, don't leave a split there
    for row in data:
        rowx += 1
        for colx, value in enumerate(row):
        	ws.write(rowx, colx, value.encode('utf-8').decode('utf-8'))
    wb.save(result_excel_file_name)


if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        pass
Reply
#7
(Jan-27-2018, 07:56 PM)haziebaby Wrote: If there is a better way please comment
If the Excel file doesn't need special formatting, you can write a csv and convert to xls by running an external command from python, for example if you have libreoffice installed on your computer, the following should work:
returncode = subprocess.call("soffice --headless --convert-to xls ACI-Endpoints.csv")
if returncode:
    raise RuntimeError(('Conversion to xls failed for', 'ACI-Endpoints.csv'))
You can also convert to xlsx with soffice --headless --convert-to xlsx:"Calc MS Excel 2007 XML" file.csv
Reply
#8
If using Pandas and also Jupyter Notebook the format look better,
The DataFrame and Excel look alike,and Pandas has df.to_excel('ip.xlsx')is all that's needed to save to Excel.
tabulate to also work with Pandas DataFrame.
Here a image with test a did,Anaconda can help if want to try this(without all the installing) my tutorial.
[Image: w6US1S.jpg]
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Counting of rows in Excel Spreadsheet dakrantau 2 1,937 Sep-30-2020, 02:27 AM
Last Post: dakrantau
  How do I write a line of code into an excel spreadsheet using Python code? Emerogork 2 3,152 Feb-07-2018, 06:54 PM
Last Post: snippsat

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020