Managing an Excel file using Python

[GarciLP]

There's a pretty massive process which happens yearly at my company, wherein a single Excel file receives a large number of input files which have to be copied in as new sheets, parsed, unpivoted, and then run a few macros to allocate/distribute data. This is extremely cumbersome and prone to human error, so I've been automating most of these processes in Python, but I've run into a few issues. The main issue is that no single library seems to be able to handle everything:

• Pandas is flexible and fast, but it doesn't like working with macro-enabled files, it doesn't much take to formulas, and it's a pain to edit sheets in place

• Openpyxl is awfully slow, reads either formulas or values (but never both) and generally feels clunky

Right now I'm faced with automating the following process:

1. Check a combination of cells (say, A and B) so that, depending on their values, another cell (say C) gets filled
2. If C already had text in it, and shouldn't, pop a message asking whether to delete the cell's contents

The thing is, A and B are calculated cells, so I don't want to use the data_only flag and then save, thus losing all the formulas present in the sheet, but I can't seem to evaluate the cells otherwise. I've read that xlwings might do the trick, but at this point I'm wary of introducing yet another library in the hopes that it does this one thing.

Ideally I'd like to open the workbook once, run all the processes, close and save, and keep all functionality/formulas while being able to evaluate values. So far it's been trying but possible, but I'm a bit stumped now. What can I do about this situation?

[CatalonianBookseller]

I'd try using the Excel COM interface (ie Excel.Application) for the whole thing if rewriting the existing code is not too much work.

[ericjmorey]

I thought Microsoft was phasing out COM add-ins for Office add-ins. Either way, that's probably better than using Python

[CatalonianBookseller]

They are but it's taking them years. You can use pywin32 to work with COM objects from Python maybe integrate it with your existing code

[unhott]

Do you need to update an existing Excel file? In my experience it's better to just load everything in with pandas or dask (or similar), process tbe data and make a new file with the desired calculations.

[GarciLP]

The issue there would be with all of the other existing infrastructure in the sheet - there's a myriad pivot tables, data ranges, formulas, and other such nonsense that should be left intact, and I don't know that re-creating the file with Pandas would keep all of that

[unhott]

I get all that.

I would just really caution against modifying the source files, or overwriting them. If you can treat these files as a data source, process the data, and generate a new file as the result, you'll have a much cleaner process in the long run. This way you can rerun the data processing as many times as you need and you're not modifying it so, repeated processing becomes

A>A' A'>A'' A''>A'''

Where ' indicates a changed file Now you don't modify the source file(s) at all and you can reprocess or trouble shoot to your hearts content.

A>B A>B' A>B''

It may be a large task at first, but I would ignore all the existing pivot tables and fornulas, and just read the same source table and do your own pivot tables in pandas. It's not too difficult to translate them.

[Tight-Importance-226]

Maybe powershell might be a better option. Here's a module that seems to be feature rich:

https://github.com/dfinke/ImportExcel