Read Excel worksheet names with standard python?

[mtbNick]

Hi,

I'm new to the group and pretty new to Python as well. I spent a good
part of the day trying to figure out a way to read the names of
worksheets in an Excel workbook (2003 or 2007) with just a standard
install of Python (2.6). The sheets are typically named Sheet1,
Sheet2, Sheet3, etc.. Most of the forum discussions I've read focus on
3rd party site-packages like xlrt, xlwt, etc.

Any ideas?

[werner]

Hi,

Just came across xlrd the other day and experimenting with it.

What you are looking for you can find under "The Book Class" on the
following page

https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html

E.g. you could do something like this:

wb = xlrd.open_workbook('yourfile.xls')

sNames = wb.sheet_names()

for sname in sNames:
s = wb.sheet_by_name(sname)

Werner

[mtbNick]

Thanks for the quick response!

Unfortunately, I cannot use any additional packages. I'm limited to
just the standard install of Python 2.6

I came close with a combination of the xml and zipfile modules, but
haven't got it just yet.

[David Avraamides]

Only new .xlsx files are stored as zip files. Older .xls files are stored in a binary (BIFF) format.

If you only need to work with .xlsx files, you can unzip the file and parse the xl/workbook.xml file which contains xml like this:

<workbook>

   <sheets>

      <sheet name="Sheet1" .../>

      <sheet name="Sheet2" .../>

   </sheets>

</workbook>

So parse out the sheet elements and look at their name attribute. 

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[Chris Withers]

On 21/12/2011 11:15, mtbNick wrote:
>
> Thanks for the quick response!
>
> Unfortunately, I cannot use any additional packages. I'm limited to
> just the standard install of Python 2.6

Who is imposing this insane requirement on you?

> I came close with a combination of the xml and zipfile modules, but
> haven't got it just yet.

Are you dealing with .xls or .xlsx?

John, how's the merge of .xlsx support into xlrd coming?

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Ian Kelly]

On Wed, Dec 21, 2011 at 4:15 AM, mtbNick <jacob....@gmail.com> wrote:
>
> Thanks for the quick response!
>
> Unfortunately, I cannot use any additional packages.  I'm limited to
> just the standard install of Python 2.6

Can you include xlrd as part of your program?

[John Yeung]

On Wed, Dec 21, 2011 at 10:16 PM, mtbNick <jacob....@gmail.com> wrote:
> Haha yeah I'm starting to doubt it's possible.  I need to account for
> both .xls AND .xlsx files.  Plus, I'm limited to just the standard
> install of Python for various reasons.

Well, xlrd does have an add-on (currently called xlsxrd) that handles
.xlsx. Since it's pure Python, Ian is suggesting that you just bundle
it with your program. (That is, you don't have to "install" it on the
target system, rather it can just be packaged together with the
program you are writing yourself. The xlrd and xlsxrd parts just need
to retain all the copyright notices, disclaimers, and so forth.)

John Y.

[mtbNick]

Thanks for responding and that info was really useful. I'm going to
work on xlsx files this morning and will come back with some code if I
can figure it out.

[mtbNick]

Ha! Well the short answer is that I am believe it or not =)

The long story:

I'm using some python friendly geospatial software called ArcGIS for
Desktop that comes with a standard install of Python with each new
release (currently Python 2.6 comes w/ an install, next year likely
2.7, and so on...). So, I'm getting a lot from my Python scripts, but
I'm wary of using 3rd party site-packages b/c I won't be around to
reinstall them from year to year as Python gets upgraded along with
the geospatial software. ... and honestly, now that I've gone this
far I just want to figure it out!

The short story:

Yup, I need to account for .xls, xlsx, csv, txt, and zip files. The
trick is I need the worksheet names, so I can import them into ESRI
software. Someone suggested that I write something in C# to work with
the Excel files, but I'm still convinced it can be done entirely in
Python.

- Nick

[mtbNick]

Hmm... I see what you mean now. I'll give it a shot this morning.

Thank you!

- Nick

On Dec 22, 12:11 am, John Yeung <gallium.arsen...@gmail.com> wrote:

[mtbNick]

Just updating this thread in case another GIS type comes across it
with the same challenge.

With some experimentation I was able to successfully read sheet names
from both 2003 and 2007 Microsoft Excel workbooks. For the .xls files
I used the xlrd package and for the .xlsx files I employed a
combination of zipfile and minidom.

For those that use ESRI's site-package for Python, arcpy, I discovered
a neat trick that may be useful. It's possible to set arcpy's
environment setting to an excel workbook (as opposed to a directory or
geodatabase), then use the ListTables() function to return a list of
sheet names. I don't have the code off-hand but it'd look something
similar to this:

#
import arcpy

arcpy.env.workspace = r"\\path\filename.xls"

tableList = arcpy.ListTables()

print tableList[0]
print tableList[1]
print tableList[2]

#-------------------------
# prints
#-------------------------

sheet1$
sheet2$
sheet3$

I'm not current with ESRI's licensing levels at the moment, but I
would guess that their site-package's tools are scaled just as their
software packages are. So I'd imagine this function would be available
under the lowest licensing level, which I believe is still ArcView (as
of 2011'ish). Anyways, just trying to give back a little:)

[John Yeung]

On Thu, Jan 5, 2012 at 6:38 PM, mtbNick <jacob....@gmail.com> wrote:
> With some experimentation I was able to successfully
> read sheet names from both 2003 and 2007 Microsoft
> Excel workbooks.  For the .xls files I used the xlrd
> package and for the .xlsx files I employed a
> combination of zipfile and minidom.

I'm glad that you were able to get done what you needed, but is there
some reason you opted not to use xlsxrd? Especially considering that
you did use xlrd?

John Y.