11

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.

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

  • 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. – Bojan Hrnkas Jul 10 '18 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 '18 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.

  • 2
    This doesn't work for me, because I want to automate something on a headless server. – Bojan Hrnkas Jul 10 '18 at 17:38
  • 2
    @pipe. It is possible to run LibreOffice in headless mode, is it not? – TRiG Jul 10 '18 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 '18 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 '18 at 23:42
  • 1
    Opening LibreOffice in Headless mode is as simple as using the --headless option. – Johnny Jul 11 '18 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.