Basic XLwings

First things first check out the documentation for this Library. https://www.xlwings.org/

It can be used from within Excel with the add-on, but I almost exclusively use it from python and have used it to save hours of reporting. At one of my companies there was a significant amount of manual reporting done each morning, overall, we are talking 4 hours or so of manual work. This manual work was running queries, copying data into spreadsheets etc. It was painful to do and very laborious. Instantly I knew this was a problem that there must be a solution for and originally, I tried another python library, but it deleted all the graphs and then we stumbled across XLwings.

Once XLwings was installed it was fairly easy to do the basics and as the docs say make Excel fly. Some of the things that I have done using XLwings, sometimes with the help of the win32com client api, are:

• Copy data from pandas dataframe into Excel
• Update ranges
• Set formulas
• Update pivot tables
• Apply a range of formatting options – I found it easiest to create my own function for this
• Adjust column widths or autofit
• Adjust datatypes – important when your reports keep putting large numbers in scientific notation.
• Hide / Unhide sheets
• Turn on Filtering

The code:

	import xlwings as xw
	import glob
	import os
	import datetime
	### Not needed for this script but is used in many others
	import win32com.client
	### set whether to run this section
	Excel = True
	### run this section
	if Excel:
	### Get list of files matching the basic file name
	list_of_files = glob.glob('File Location\\Filename*.xlsx')
	### get the latest file
	latest_file = max(list_of_files, key=os.path.getctime)
	### Set XLwings app to visible - I had a problem with this so just to be sure
	xw.App(visible=True)
	### Open the latest file
	wb = xw.Book(latest_file)
	### Select the Sheet, Sheet Name
	wb.sheets['Sheet Name'].select()
	### Set Filename to filename with todays date
	filename = 'Folder Location\\Filename '+datetime.datetime.today().strftime('%d%m%y')+'.xlsx'
	wb.save(filename)
	wb.close()

view raw XlwingsDemo.py hosted with ❤ by GitHub