Saving XlsxWriter workbook more than once

Question

I am writing software that manipulates Excel sheets. So far, I've been using xlrd and xlwt to do so, and everything works pretty well.

It opens a sheet (xlrd) and copies select columns to a new workbook (xlwt)
It then opens the newly created workbook to read data (xlrd) and does some math and formatting with the data (which couldn't be done if the file isn't saved once) - (xlwt saves once again)

However, I am now willing to add charts in my documents, and this function is not supported by xlwt. I have found that xlsxwriter does, but this adds other complications to my code: xlsxwriter only has xlsxwriter.close(), which saves AND closes the document.

Does anyone know if there's any workaround for this? Whenever I use xlsxwriter.close(), my workbook object containing the document I'm writing isn't usable anymore.

You can't modify an existing excel file using xlsxwriter. You could read in your existing excel file using pandas.read_excel, if that's viable, and go from there using xlsxwriter. Alternatively, openpyxl would probably be a better option for reading in an existing excel file and then modifying it. You can make charts in openpyxl also (link). — patrickjlong1

John Y John Y · Accepted Answer · 2017-06-07T15:36:26

Fundamentally, there is no reason you need to read twice and save twice. For your current (no charts) process, you can just read the data you need using xlrd; then do all your processing; and write once with xlwt.

Following this workflow, it is a relatively simple matter to replace xlwt with XlsxWriter.

Saving XlsxWriter workbook more than once

1 Answers