csvExcel.py

# Convert csv files into Excel workbook
# Format col widths to fixed width
#

import time, socket
import pandas as pd
from pandas.io.excel import ExcelWriter
from pathlib import Path
from openpyxl import load_workbook
from string import ascii_uppercase

today = time.strftime("%Y%m%d")
csv_path = Path('.')

def get_host():
 '''Get the hostname'''
 fqdn = socket.gethostname()
 '''Create dict with hostnames to friendly names'''
 env = {
 "devA-001.example.com" : "devA",
 "prodA-001.example.com" : "prodA",
 "prodB-001.example.com" : "prodB",
 "WorkLaptop" : "laptop",
 "ThinkPad" : "thinkpad",
 }
 return env.get(fqdn)


def create_report(dir1):
 '''Look for csv files in dir and create a list of filenames'''
 csv_files_list = [file.stem for file in csv_path.glob('*.csv')]

 '''Create new workbook and add each csv in own sheet'''
 with ExcelWriter('temp.xlsx') as ew:
 for csv_file in csv_files_list:
 pd.read_csv(csv_file + '.csv').to_excel(ew, sheet_name=csv_file, index=False)


def format_width(wbook):
 '''Load workbook created from create_report func'''
 wb = load_workbook(wbook)

 '''Interate over total nmuber of sheets and adjust column widths
 Work out the total number of worksheets, and set each one active'''
 for i in range(len(wb.worksheets)):
 wb.active = i
 '''For each worksheet set the col width for A,B,C, leave the others at default'''
 for column in ascii_uppercase:
 if (column=='A' or column=='B' or column=='C'):
 wb.active.column_dimensions[column].width = 50
 else:
 wb.active.column_dimensions[column].width = 8
 '''Save final workbook'''
 wb.save(f'{get_host()}_rpm_excel_{today}.xlsx')


'''Call the functions'''
create_report(csv_path)
time.sleep(1)
format_width('temp.xlsx')