I tried to output the rpm list of SSH login destination to an Excel sheet with Python + openpyxl.

What is openpyxl

openpyxl is a library for reading and writing * .xlsx in Python.

A Python library to read/write Excel 2010 xlsx/xlsm files https://openpyxl.readthedocs.org/en/default/

It's not very interesting to use it alone, but some people may be happy to be able to pick up the configuration information of the SSH login destination and automatically generate the parameter sheet of Excel, so I tried it a little.

Try to write the code

Here is the sample code that was created by trying out the functions that could be used one by one.

createlPackageList.py

import ssh
from openpyxl import Workbook
from openpyxl.styles.borders import Side, Border
from openpyxl.styles import Style, PatternFill

hostname = 'foo.bar.com'
username = 'root'
password = 'XXXXXXXX'
port = 22

client = ssh.SSHClient()
client.set_missing_host_key_policy(ssh.AutoAddPolicy())
client.connect(hostname, username = username, password = password, port = port)
stdin, stdout, stderr = client.exec_command('rpm -qa --queryformat="%{NAME},%{VERSION},%{RELEASE},%{ARCH},%{GROUP}\n" | sort')

wb = Workbook()
ws = wb.active

ws.append(['Name', 'Version', 'Release', 'Architecture', 'Group'])

for line in stdout.read().split('\n'):
    ws.append(list(line.split(',')))

thin_border = Border(left = Side(style = 'thin'), 
                     right = Side(style = 'thin'), 
                     top = Side(style = 'thin'), 
                     bottom = Side(style = 'thin'))
 
my_style = Style(border = thin_border)

for _row in ws.iter_rows('A1:E' + str(ws.max_row)):
    for _cell in _row:
        _cell.style = my_style

lightblueFill = PatternFill(start_color = '0000FFFF',
                      end_color = '0000FFFF',
                      fill_type = 'solid')

for _row in ws.iter_rows('A1:E1'):
    for _cell in _row:
        _cell.fill = lightblueFill

ws.column_dimensions['A'].width = 45
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 28
ws.column_dimensions['E'].width = 30

wb.save("output.xlsx")

Since openpyxl internally treats Excel rows as a list, here, the rpm -qa… | sort execution result (stdout) executed at the SSH login destination is read line by line, and the column-by-column split is added to the list. You can convert it and append it to the Workbook class worksheet ws.

You can also border cells, specify colors, or change the width of columns in the Styles module.

Generated Excel file

The following Excel file is created by executing the above script. Of course, no artificial editing work is done after the file is generated.

If you have a library that is easy to use in Python, it's very convenient because you can easily do all this with dozens of lines of code.

Environment: Confirmed with Python 2.7.9 + openpyxl 2.3.1.