xlsxwriter module won't open/close Excel file correctly

Question

I'm writing a program that writes data to an Excel file using the xlsxwriter module.

The code that opens the workbook is:

excel = xlsxwriter.Workbook('stock.xlsx')

This used to work. Then I changed some stuff around in the bottom of the program (waaaaay after that line) and now it doesn't work, saying this:

Exception ignored in: <bound method Workbook.__del__ of <xlsxwriter.workbook.Workbook object at 0x02C702B0>>
Traceback (most recent call last):
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 147, in __del__
    raise Exception("Exception caught in workbook destructor. "
Exception: Exception caught in workbook destructor. Explicit close() may be required for workbook.

This has happened when I forget to close the file before running it again (as it is trying to write to a file that's open in Excel which won't work), but I don't even have Excel open and it does this.

How can I fix this? Do I need to restart or something?

Also, I tried to have a try...except loop to stop the program if the initialization doesn't work. Even with except: only, without a specific exception, it still completes the program unless I kill it manually. The script basically opens the Excel file, spends a long time downloading data from the Internet, and then writing that to the Excel file. I want it to stop if the initialization doesn't work so I don't have to wait for the script to complete (it can take up to 15 minutes). I'm pretty sure that it has something to do with the fact that it says "Exception ignored", but I'm not familiar with all the error-fu in Python.

EDIT:

I added an excel.close() command right at the end and now it doesn't give me the first error, but a second (and much larger and scarier) one:

Traceback (most recent call last):
  File "C:\Users\carte_000\Python\stock_get_rev8.py", line 161, in <module>
    excel.close()
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 287, in close
    self._store_workbook()
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 510, in _store_workbook
    xml_files = packager._create_package()
  File "c:\python34\lib\site-packages\xlsxwriter\packager.py", line 132, in _create_package
    self._write_worksheet_files()
  File "c:\python34\lib\site-packages\xlsxwriter\packager.py", line 189, in _write_worksheet_files
    worksheet._assemble_xml_file()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 3395, in _assemble_xml_file
    self._write_sheet_data()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 4802, in _write_sheet_data
    self._write_rows()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 4988, in _write_rows
    self._write_cell(row_num, col_num, col_ref)
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 5148, in _write_cell
    xf_index = cell.format._get_xf_index()
AttributeError: type object 'str' has no attribute '_get_xf_index'

EDIT 2:

The part of the code that actually writes to the file is this:

for r, row in enumerate(data):
    for c, cell in enumerate(row):
        if 'percent' in formats[c]:
            sheet.write(r + r_offset, c + c_offset, cell, eval(formats[c].replace('_f', '')))
        elif '_f' in formats[c]:
            sheet.write(r + r_offset, c + c_offset, cell.format(n=str(r + r_offset)), eval(formats[c].replace('_f', '')))
        else:
            sheet.write(r + r_offset, c + c_offset, cell, eval(formats[c][0] + formats[c].replace('_f', '')[-1]))

If I replace all the fancy if...else stuff with a single

sheet.write(r + r_offset, c + c_offset, cell)

it doesn't give me the error, and seems to work fine.

This doesn't provide the functionality I need, as some columns need to be formulas whose numbers change based on the row. What in the above code is causing the excel.close() line to bug out?

Slipway

Answer 1

In my code I was trying to reference a non-existent format. When I called excel.close(), that seems to be when it actually writes everything, so if there's an invalid format, it'll throw an error there.

Slipway

Answer 2

I got the same issue and successfully resolved it

AttributeError: type object 'str' has no attribute '_get_xf_index'

This error message occurs if to set extra argument to sheet.write() So instead of sheet.write(row, col, "Some_text", variable) please use sheet.write(row, col, "Some_text"+variable)

...and excel.close() if script finished to use excel file

Brain

Answer 3

What in the above code is causing the excel.close() line to bug out?

It crashes at excel.close() because it's when the packager is called. The error comes from the fourth argument in the sheet.write calls: eval isn't returning xlsxwriter.Format objects but str objects, which haven't got the _get_xf_index method and therefore they throw an exception.

You may be able to improve your code avoiding using eval, which is almost always a bad practice, mapping your formats in dicts and/or lists or, if the formats are really complex, creating a function that returns the format.

Aluin

Answer 4

I think you are forgetting to close the file - Once you are done writing data to the file, you will need to close the file handler using

excel.close()

as mentioned in the docs here.

Ceja

See my edit: This just gives me a big error about something being a str when it's not supposed to be. – Slipway

@Slipway You will need to add more code to the post to make it understandable and get further help. See stackoverflow.com/help/mcve – Ceja