13

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.

0

2 Answers 2

14

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()

2
  • Sounds promising - will give it a try. Just to be sure - df.ix[:-3] - will delete last three not empty rows? Because an excel sheet has a lot of empty rows, if you look at the xml file in the xlsx. Jul 10, 2018 at 13:53
  • 2
    it deletes last 3 rows. empty rows after last non empty row are ignored. For convenience here: pandas.pydata.org/pandas-docs/version/0.22/generated/… is a reference of pandas dataframe
    – DDS
    Jul 10, 2018 at 13:55
-3

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.

6
  • 2
    This doesn't work for me, because I want to automate something on a headless server. Jul 10, 2018 at 17:38
  • 2
    @pipe. It is possible to run LibreOffice in headless mode, is it not?
    – TRiG
    Jul 10, 2018 at 20:45
  • 3
    @TRiG Sure it is, and an answer detailing how to do that would be a great answer. This is not that answer.
    – pipe
    Jul 10, 2018 at 20:52
  • 1
    @pipe calling LibreOffice a "one-off gui tool" is pretty extreme. It's basically the opposite of what it really is.
    – barbecue
    Jul 10, 2018 at 23:42
  • 1
    Opening LibreOffice in Headless mode is as simple as using the --headless option.
    – Johnny
    Jul 11, 2018 at 7:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .