Close file when done

Created originally on Bitbucket by mattkatz (Matt Katz)

We love openpyxl where I work, particularly when using petl with it.

However, it seems there is no way to close an openpyxl workbook and destroy it.

This means that if later I want to move a file that I've read and dealt with, I can't - the file is in use. I have to kill an excel process to do it.

It would be better to close the file on __exit__

That way if I use it in a with block it auto shuts the file at the end of the block.

#!python

import openpyxl
from pathlib import Path
inputFile = Path(r'\\server\path\to\file.xlsx').resolve()

wb = openpyxl.load_workbook(str(inputFile), use_iterators=True)
ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
for row in ws.iter_rows():
    print('doing important work')
del(ws)
del(wb)
inputFile.rename( inputFile.parent / 'processed' / inputFile.name)

This throws a PermissionError: [WinError 32] The process cannot access the file because it is being used by another process:

I would expect this to work though, and it would be a really nice pattern for use:

#!python
import openpyxl
from pathlib import Path
inputFile = Path(r'\\server\path\to\file.xlsx').resolve()

with openpyxl.load_workbook(str(inputFile), use_iterators=True) as wb:
  with wb.get_sheet_by_name(wb.get_sheet_names()[0]) as ws:
    for row in ws.iter_rows():
        print('doing important work')
inputFile.rename( inputFile.parent / 'processed' / inputFile.name)