UserWarning: Calling close() on already closed file. warn("Calling close() on already closed file.")

Question

This error is apparently stemming from xlsxwriter. I'm not sure on what line of my code it's coming from because my editor Visual Studio 2019 crashes every time I try to debug. But, I get this error on a laptop while on a VPN and remote desktop connection. If I run the same code from my remote machine I don't get the error. The error doesn't seem to affect the output, though, because the script finishes and saves successfully. But, how do I get rid of this error?

My Code:

import requests
from bs4 import BeautifulSoup
import pandas as pd
from pandas import ExcelWriter
from datetime import datetime
import os

#set the headers as a browser
headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}
#set up file name
file_path = r"C:\Users\jpilbeam"
excel_file = 'bargetraffic' + str(datetime.now().strftime('_%m_%d_%Y')) + '.xlsx'
excel_file_full = os.path.join(file_path, excel_file)

lockName = ['Dresden Island Lock', 'Brandon Rd Lock', 'Lockport Lock']
lockNo = ['02', '03', '04']

results = []
for lock in lockNo: 
    url = 'https://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=IL&in_lock=' + lock
    #print (url)
    link = requests.get(url).text
    soup = BeautifulSoup(link,'lxml')
    
    #get elements of row tags
    rows = soup.find_all('row')

    sheet = pd.DataFrame()
    for row in rows:
        name = row.find('vessel_name').text.strip()
        no = row.find('vessel_no').text.strip()
        dir = row.find('direction').text.strip()
        barno = row.find('num_barges').text.strip()
        arr = row.find('arrival_date').text.strip()

        try:
            end = row.find('end_of_lockage').text.strip()
        except:
            result = ''

        df = pd.DataFrame([[name,no,dir,barno,arr, end]], columns=['Name','Vessel No.','Direction','Number of Barges','Arrival', 'End of Lockage'])
        sheet = sheet.append(df,sort=True).reset_index(drop=True)

    results.append(sheet)


def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'%s' %lockName[n],index=False,)
        writer.save()

save_xls(results,excel_file_full)
print('----done----')

Error:

C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\xlsxwriter\workbook.py:329: UserWarning: Calling close() on already closed file.
  warn("Calling close() on already closed file.")

I put the save part in a try except block according to this help doc, but I must be doing it wrong.

while True:
    try:
        def save_xls(list_dfs, xls_path):
            with ExcelWriter(xls_path) as writer:
                for n, df in enumerate(list_dfs):
                    df.to_excel(writer,'%s' %lockName[n],index=False,)
                writer.save()

        save_xls(results,excel_file_full)
    except xlsxwriter.exceptions.FileCreateError as e:
        print(e)
print('----done----')

Answer 1

The warning occurs because the program is using to_excel() within a with statement which effectively closes/saves the file once it leaves the scope. It then calls save() which tries to close the file again but since it is already closed you get a warning.

In short remove the save() and you won't get the warning but the file will still be saved.