To view the list of sheets in an Excel spreadsheet, I can use the xlrd module within the Python script below to obtain the list of worksheets within the workbook.
#!/usr/bin/python
import xlrd as xl
file_name = raw_input("File: ")
workbook = xl.open_workbook(file_name)
print workbook.sheet_names()If I use the script to display the list of worksheets in a workbook
named report.xlsx that has three sheets named alpha,
beta, and gamma, I would see the following output:
$ ./sheetlist.py File: report.xlsx [u'alpha', u'beta', u'gamma'] $
In the above output, Python displays the letter "u" before each string. The "u" indicates the object is a Unicode string. I can eliminate the "u" before each entry in the sheet list by changing the print statement to the one below:
print [x.encode('ascii') for x in workbook.sheet_names()]I would then see the following output:
$ ./sheetlist.py File: report.xlsx ['alpha', 'beta', 'gamma']
I could eliminate the brackets in the output using the print command below:
print ', '.join(repr(x.encode('ascii')) for x in workbook.sheet_names())The
repr function returns a string containing a printable representation of an
object. The
string join method returns a string in which the string elements of sequence have been joined by a specified separator, which can be specified before
the .join. In this case, I've specified a comma
followed by a space as the separator. I could also have placed the separator
in a variable as shown below:
str = ', '
print str.join(repr(x.encode('ascii')) for x in workbook.sheet_names())Or to see only the sheet names without the quotes, I could use the code below:
#!/usr/bin/python
import xlrd as xl
file_name = raw_input("File: ")
workbook = xl.open_workbook(file_name)
for x in workbook.sheet_names():
print x.encode('ascii')The output would then be as shown below:
$ ./sheetlist.py File: report.xlsx alpha beta gamma $
If I wanted a count of the number of worksheets in the spreadsheet, I
could include print "Number of sheets:", len(workbook.sheet_names())
in the sheetlist script. E.g.:
#!/usr/bin/python
import xlrd as xl
file_name = raw_input("File: ")
workbook = xl.open_workbook(file_name)
print "Number of sheets:", len(workbook.sheet_names())
for x in workbook.sheet_names():
print x.encode('ascii')Or, I could use nsheets instead of determining the the number of items with len. I.e.:
print "Number of sheets:", workbook.nsheets
I would then see the output below:
$ ./sheetlist.py File: report.xlsx Number of sheets: 3 alpha beta gamma $
Related articles: