ExcelWriter does not support BytesIO input · Issue #7074 · pandas-dev/pandas (original) (raw)

At least XlsxWriter supports writing to buffers but the ExcelWriter constructors try to read the extension of the supplied path, so it fails when you initialise it as ExcelWriter(buf, engine="xlsxwriter").

Currently you can hack that into pandas using

b = BytesIO()
w = ExcelWriter("data.xlsx", engine="xlsxwriter")
w.book = xlsxwriter.Workbook(b)
df.to_excel(w)
w.save()
b.getvalue()

but it would of course be nice if this just worked out-of-the-box. I'll try to build a PR for that.