Outputting A .xls File In Python

2
votes

I have been teaching myself Python to automate some of our work processes. So far reading from Excel files (.xls, .xlsx) has gone great.

Currently I have hit a bit of a snag. Although I can output .xlsx files fine, the software system that we have to use for our primary work task can only take .xls files as an input - it cannot handle .xlsx files, and the vendor sees no reason to add .xlsx support at any point in the foreseeable future.

When I try to output a .xls file using either Pandas or OpenPyXl, and open that file in Excel, I get a warning that the file format and extension of the file do not match, which leads me to think that attempting to open this file using our software could lead to some pretty unexpected consequences (because it's actually a .xlsx file, just not named as such)

I've tried to search for how to fix this all on Google, but all I can find are guides for how to convert a .xls file to a .xlsx file (which is almost the opposite of what I need). So I was wondering if anybody could please help me on whether this can be achieved, and if it can, how.

Thank you very much for your time

3
pythonexcelpandasopenpyxlxls
not sure if it's possible in openpyxl, but you can read/write XLS in xlrd and xlwt modules - Derek Eden
another option: stackoverflow.com/questions/37993868/… - BigBen
I’m curious, why do you need to convert files to an Excel format which was deprecated twelve years ago? - AMC
Use xlrd and xlwt. - Charlie Clark
@TigerBailey Ah yes your right, sorry. I completely forgot you stated that in your post. It’s not a great situation to be in, good luck :/ Out of curiosity, can you share what software this is? - AMC

3 Answers

5
votes

Under the pandas.DataFrame.to_excel documentation you should notice a parameter called engine, which states:

engine : str, optional

Write engine to use, openpyxl or xlsxwriter. You can also set this via the options io.excel.xlsx.writer, io.excel.xls.writer, and io.excel.xlsm.writer.

What it does not state is that the engine param is automatically picked based on your file extension -- therefore, easy fix:

import pandas as pd

df = pd.DataFrame({"data": [1, 2, 3]})
df.to_excel("file.xls") # Notice desired file extension.

This will automatically use the xlwt engine, so make sure you have it installed via pip install xlwt.

0
votes

I FINALLY have the answer! I have libreoffice installed and am using the following in the command line on windows:

"C:\Program Files\LibreOffice\program\soffice.exe" --headless --convert-to xlsx test2.xls

Currently trying to use subprocess to automate this.

0
votes

Felipe is right the filename extension will set the engine parameter.

So basically all it's saying is that the old Excel format ".xls" extension is no longer supported in Pandas. So if you specify the output spreadsheet with the ".xlsx" extension the warning message disappears.