# -*- coding: cp1252 -*- ## # Module/script example of the xlrd API for extracting information # about named references, named constants, etc. # #

Copyright © 2006 Stephen John Machin, Lingfo Pty Ltd

#

This module is part of the xlrd package, which is released under a BSD-style licence.

## 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, '' % 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)