How can I print sheetnames of a spreadsheet using Linux command-line?

Question

I am aware of using in2csv to save a particular worksheet as a .csv:

in2csv --sheet "sheet name" file1.xls > sheet-name.csv

But are there any other tools to just print the sheetnames?

Perhaps there are options with Perl?

dessert · Accepted Answer · 2020-04-16T05:37:34.240

14

in2csv from the csvkit package provides the --names or -n option for that: ^[Source]

 -n, --names     Display sheet names from the input Excel file.

In your example the command would be:

in2csv -n file1.xls

This feature was added in csvkit 1.0.2, which is not available from the official package sources for releases older than Bionic. If you’re running Xenial you need to either

compile the program from source or
install it via pip with
```
sudo pip install csvkit
```

to get the latest version.

edited Apr 16 '20 at 05:37

answered Nov 03 '17 at 09:59

dessert

39,392
12
115
163

I would have accepted this answer but turns out I have a version of `in2csv` lacking `-n` option. Weird, trying to figure out how to get the latest but having trouble with csvkit and removing older `in2csv` ... sigh – csheth Nov 03 '17 at 11:32
2

I'd just remove it with `sudo apt remove python3-csvkit` and install a newer one, preferably from https://packages.ubuntu.com/, or else from https://github.com/wireservice/csvkit/tree/1.0.2. The feature was introduced with [this](https://github.com/wireservice/csvkit/commit/829e176929934bade37cd85dded91df3413b44db#diff-a14fd01737ab485de9d4254287a53055) commit tagged “1.0.2”, so any version from that on should have this option. – dessert Nov 03 '17 at 11:40
Unfortunately it seems that none of the packaged versions reach this version number, so compiling the source from github seems to be the only way to go – in this case, I'd rather *not* uninstall the package version but just write a wrapper function named `in2csv` that calls `/path/to/new/in2csv` in case it's called with the `-n` option and the usual `/usr/bin/in2csv` else. – dessert Nov 03 '17 at 11:51
1

ok. I used `sudo apt remove python3-csvkit` , installed the newer one and it worked. The wrapper function is very useful yes! – csheth Nov 03 '17 at 11:56

score 9 · Answer 2 · answered Nov 03 '17 at 10:07

in2csv is the simpler option, but I'll leave this in case somebody might find it useful. There's a nice command called xlhtml for converting XLS files to HTML or XML. And once you have the XML, various XML processing tools can be used to do a wide variety of queries on it. In this case:

$ xlhtml -xml ~/foo.xls | xmlstarlet sel -t -m '//pagetitle' -v . -n
Sheet1
Sheet2

The XML that xlhtml generates is like so:

<?xml version="1.0" encoding="iso-8859-1" ?>
<excel_workbook>
    <sheets>
        <sheet>
            <page>0</page>
            <pagetitle>Sheet1</pagetitle>
            <firstrow>0</firstrow>
            <lastrow>11</lastrow>
            <firstcol>0</firstcol>
            <lastcol>0</lastcol>

So, for the sheet names, we can query the pagetitle nodes, for which I used xmlstarlet.

It isn't available for 16.04 Xenial. Maybe useful to add it to your answer? — csheth, Nov 05 '17 at 06:38

How can I print sheetnames of a spreadsheet using Linux command-line?

2 Answers2