/usr/lib/python2.7/dist-packages/xlrd/examples/xlrdnameAPIdemo.py is in python-xlrd 1.1.0-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | # -*- coding: cp1252 -*-
##
# Module/script example of the xlrd API for extracting information
# about named references, named constants, etc.
#
# <p>Copyright © 2006 Stephen John Machin, Lingfo Pty Ltd</p>
# <p>This module is part of the xlrd package, which is released under a BSD-style licence.</p>
##
from __future__ import print_function
import xlrd
from xlrd.timemachine import REPR
import sys
import glob
def scope_as_string(book, scope):
if 0 <= scope < book.nsheets:
return "sheet #%d (%r)" % (scope, REPR(book.sheet_names()[scope]))
if scope == -1:
return "Global"
if scope == -2:
return "Macro/VBA"
return "Unknown scope value (%r)" % REPR(scope)
def do_scope_query(book, scope_strg, show_contents=0, f=sys.stdout):
try:
qscope = int(scope_strg)
except ValueError:
if scope_strg == "*":
qscope = None # means "all'
else:
# so assume it's a sheet name ...
qscope = book.sheet_names().index(scope_strg)
print("%r => %d" % (scope_strg, qscope), file=f)
for nobj in book.name_obj_list:
if qscope is None or nobj.scope == qscope:
show_name_object(book, nobj, show_contents, f)
def show_name_details(book, name, show_contents=0, f=sys.stdout):
"""
book -- Book object obtained from xlrd.open_workbook().
name -- The name that's being investigated.
show_contents -- 0: Don't; 1: Non-empty cells only; 2: All cells
f -- Open output file handle.
"""
name_lcase = name.lower() # Excel names are case-insensitive.
nobj_list = book.name_map.get(name_lcase)
if not nobj_list:
print("%r: unknown name" % name, file=f)
return
for nobj in nobj_list:
show_name_object(book, nobj, show_contents, f)
def show_name_details_in_scope(
book, name, scope_strg, show_contents=0, f=sys.stdout,
):
try:
scope = int(scope_strg)
except ValueError:
# so assume it's a sheet name ...
scope = book.sheet_names().index(scope_strg)
print("%r => %d" % (scope_strg, scope), file=f)
name_lcase = name.lower() # Excel names are case-insensitive.
while 1:
nobj = book.name_and_scope_map.get((name_lcase, scope))
if nobj:
break
print("Name %s not found in scope %d" % (REPR(name), scope), file=f)
if scope == -1:
return
scope = -1 # Try again with global scope
print("Name %s found in scope %d" % (REPR(name), scope), file=f)
show_name_object(book, nobj, show_contents, f)
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def show_name_object(book, nobj, show_contents=0, f=sys.stdout):
print("\nName: %s, scope: %s (%s)"
% (REPR(nobj.name), REPR(nobj.scope), scope_as_string(book, nobj.scope)), file=f)
res = nobj.result
print("Formula eval result: %s" % REPR(res), file=f)
if res is None:
return
# result should be an instance of the Operand class
kind = res.kind
value = res.value
if kind >= 0:
# A scalar, or unknown ... you've seen all there is to see.
pass
elif kind == xlrd.oREL:
# A list of Ref3D objects representing *relative* ranges
for i in range(len(value)):
ref3d = value[i]
print("Range %d: %s ==> %s"% (i, REPR(ref3d.coords), REPR(xlrd.rangename3drel(book, ref3d))), file=f)
elif kind == xlrd.oREF:
# A list of Ref3D objects
for i in range(len(value)):
ref3d = value[i]
print("Range %d: %s ==> %s"% (i, REPR(ref3d.coords), REPR(xlrd.rangename3d(book, ref3d))), file=f)
if not show_contents:
continue
datemode = book.datemode
for shx in range(ref3d.shtxlo, ref3d.shtxhi):
sh = book.sheet_by_index(shx)
print(" Sheet #%d (%s)" % (shx, sh.name), file=f)
rowlim = min(ref3d.rowxhi, sh.nrows)
collim = min(ref3d.colxhi, sh.ncols)
for rowx in range(ref3d.rowxlo, rowlim):
for colx in range(ref3d.colxlo, collim):
cty = sh.cell_type(rowx, colx)
if cty == xlrd.XL_CELL_EMPTY and show_contents == 1:
continue
cval = sh.cell_value(rowx, colx)
sval = showable_cell_value(cty, cval, datemode)
print(" (%3d,%3d) %-5s: %s"
% (rowx, colx, xlrd.cellname(rowx, colx), REPR(sval)), file=f)
if __name__ == "__main__":
def usage():
text = """
usage: xlrdnameAIPdemo.py glob_pattern name scope show_contents
where:
"glob_pattern" designates a set of files
"name" is a name or '*' (all names)
"scope" is -1 (global) or a sheet number
or a sheet name or * (all scopes)
"show_contents" is one of 0 (no show),
1 (only non-empty cells), or 2 (all cells)
Examples (script name and glob_pattern arg omitted for brevity)
[Searching through book.name_obj_list]
* * 0 lists all names
* * 1 lists all names, showing referenced non-empty cells
* 1 0 lists all names local to the 2nd sheet
* Northern 0 lists all names local to the 'Northern' sheet
* -1 0 lists all names with global scope
[Initial direct access through book.name_map]
Sales * 0 lists all occurrences of "Sales" in any scope
[Direct access through book.name_and_scope_map]
Revenue -1 0 checks if "Revenue" exists in global scope
"""
sys.stdout.write(text)
if len(sys.argv) != 5:
usage()
sys.exit(0)
arg_pattern = sys.argv[1] # glob pattern e.g. "foo*.xls"
arg_name = sys.argv[2] # see below
arg_scope = sys.argv[3] # see below
arg_show_contents = int(sys.argv[4]) # 0: no show, 1: only non-empty cells,
# 2: all cells
for fname in glob.glob(arg_pattern):
book = xlrd.open_workbook(fname)
if arg_name == "*":
# Examine book.name_obj_list to find all names
# in a given scope ("*" => all scopes)
do_scope_query(book, arg_scope, arg_show_contents)
elif arg_scope == "*":
# Using book.name_map to find all usage of a name.
show_name_details(book, arg_name, arg_show_contents)
else:
# Using book.name_and_scope_map to find which if any instances
# of a name are visible in the given scope, which can be supplied
# as -1 (global) or a sheet number or a sheet name.
show_name_details_in_scope(book, arg_name, arg_scope, arg_show_contents)
|