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:
No comments:
Post a Comment