From 3fd81c73cfd8bad36b2a1cf7955006e35c1d9db5 Mon Sep 17 00:00:00 2001
From: Camil Staps
Date: Fri, 23 Oct 2015 16:44:24 +0200
Subject: Assignment 3: code, plots
---
Assignment 3/packages/xlrd/examples/namesdemo.xls | Bin 0 -> 22528 bytes
.../packages/xlrd/examples/xlrdnameAPIdemo.py | 179 +++++++++++++++++++++
2 files changed, 179 insertions(+)
create mode 100644 Assignment 3/packages/xlrd/examples/namesdemo.xls
create mode 100644 Assignment 3/packages/xlrd/examples/xlrdnameAPIdemo.py
(limited to 'Assignment 3/packages/xlrd/examples')
diff --git a/Assignment 3/packages/xlrd/examples/namesdemo.xls b/Assignment 3/packages/xlrd/examples/namesdemo.xls
new file mode 100644
index 0000000..8a16865
Binary files /dev/null and b/Assignment 3/packages/xlrd/examples/namesdemo.xls differ
diff --git a/Assignment 3/packages/xlrd/examples/xlrdnameAPIdemo.py b/Assignment 3/packages/xlrd/examples/xlrdnameAPIdemo.py
new file mode 100644
index 0000000..b29a827
--- /dev/null
+++ b/Assignment 3/packages/xlrd/examples/xlrdnameAPIdemo.py
@@ -0,0 +1,179 @@
+# -*- 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)
--
cgit v1.2.3