12

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?

csheth
  • 431

2 Answers2

15

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.

dessert
  • 41,116
  • 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 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
9

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.

muru
  • 207,970