How to edit Excel file (xlsx) using linux shell

Question

Is there a way (tool) to edit XLSX files using Linux shell? What I need is a way to remove the last three not empty rows from the first worksheet.

I know that XLSX is just a zip file, packed with different XML files, which I could individually edit. However, I would like to avoid analyzing and changing the XML files myself, if possible.

Answer 1

My idea is a python script like this:

import pandas as pd
filename=argv[1]
df = pd.read_excel(filename,sheet_name="Sheet1").ix[:-3] ## read the xlsx without last 3 rows to a dataframe
df.write_excel("output_sheet.xlsx") #write dataframe to xlsx file

to check "non-emptiness" you can use df.notna()

Answer 2

Libreoffice (and probably also OpenOffice) can read .xlsx files created by Excel. If you don't have a lot of files to process in this way, doing it manually in Libreoffice is the simplest way to go. If you have a lot of files, and really need an automated solution, Libreoffice is scriptable. See the API docs or start with a more tutorial introduction.