CODEWITHSUNDEEP
pythonexcelpandasopenpyxlxls
Tiger Bailey
Tiger Bailey

Reputation: 51

Outputting A .xls File In Python

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

Upvotes: 3

Views: 5414

Answers (4)

Madhur Dahale
Madhur Dahale

Reputation: 1

If your excel is Microsoft excel 97 then try this simple step:

import pandas as pd

import xlrd

data = pd.read_html('filename.xls')

Upvotes: 0

martyca65
martyca65

Reputation: 13

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.

Upvotes: 1

Reuben
Reuben

Reputation: 19

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.

Upvotes: 0

felipe
felipe

Reputation: 8025

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.

Upvotes: 5

Related Questions