xlrd demoThis notebook demonstrates the xlrd package which is designed to read MS Excel files. This is not a built-in package, but rather 3rd party package that is installed with ArcGIS Desktop. The PyPy site for xlrd is https://pypi.python.org/pypi/xlrd, and the package's GitHub site (with documentation is here: https://github.com/python-excel/xlrd.
We will use the quickstart on the GitHub site to, well, start quickly with this package. We'll demonstrate the package using it to read USGS Water Use for 2010, retrieved from here: https://water.usgs.gov/watuse/data/2010/index.html, and stored in the W:/859_data/Demo folder as USGSCircular1405-tables1-14.xlsx.
This brief exercise is not intended to cover the xlrd package fully, but rather to familiarize yourself with how to dig quickly into what a package can do and how to use it.
In [ ]:
#Import the os and the xlrd modules
import xlrd
In [ ]:
#Set a variable to the path of the xlsx file
xlFilename = './Data/USGSCircular1405-tables1-14.xlsx'
In [ ]:
#Use the open_workbook function to open the Excel file
book = xlrd.open_workbook(xlFilename)
type(book)
In [ ]:
#Reveal some properties of this workbook
print("The number of worksheets is {0}".format(book.nsheets))
print("Worksheet name(s): {0}".format(book.sheet_names()))
In [ ]:
# Get a worksheet by index and print its name, and the number of rows and columns
sh1 = book.sheet_by_index(0)
print(sh1.name, sh1.nrows, sh1.ncols)
In [ ]:
# Get a worksheet by name
sh2 = book.sheet_by_name('3-TS')
print(sh2.name, sh2.nrows, sh2.ncols)
3-TS worksheet
In [ ]:
#Print the value of the cell in row 10, column 3
print(sh2.cell_value(rowx=10, colx=3))
In [ ]:
#Print an entire row
row4 = sh2.row(3)
for item in row4:
print (item.value)
In [ ]:
#Print an entire column
col3 = sh2.col(3)
print(col3)