src/xlrd-2.0.2.data/scripts/runxlrd.py
1#!python
2# Copyright (c) 2005-2012 Stephen John Machin, Lingfo Pty Ltd
3# This script is part of the xlrd package, which is released under a
4# BSD-style licence.
5
6from __future__ import print_function
7
8cmd_doc = """
9Commands:
10
112rows Print the contents of first and last row in each sheet
123rows Print the contents of first, second and last row in each sheet
13bench Same as "show", but doesn't print -- for profiling
14biff_count[1] Print a count of each type of BIFF record in the file
15biff_dump[1] Print a dump (char and hex) of the BIFF records in the file
16fonts hdr + print a dump of all font objects
17hdr Mini-overview of file (no per-sheet information)
18hotshot Do a hotshot profile run e.g. ... -f1 hotshot bench bigfile*.xls
19labels Dump of sheet.col_label_ranges and ...row... for each sheet
20name_dump Dump of each object in book.name_obj_list
21names Print brief information for each NAME record
22ov Overview of file
23profile Like "hotshot", but uses cProfile
24show Print the contents of all rows in each sheet
25version[0] Print versions of xlrd and Python and exit
26xfc Print "XF counts" and cell-type counts -- see code for details
27
28[0] means no file arg
29[1] means only one file arg i.e. no glob.glob pattern
30"""
31
32options = None
33if __name__ == "__main__":
34 import xlrd
35 import sys
36 import time
37 import glob
38 import traceback
39 import gc
40
41 from xlrd.timemachine import xrange, REPR
42
43
44 class LogHandler(object):
45
46 def __init__(self, logfileobj):
47 self.logfileobj = logfileobj
48 self.fileheading = None
49 self.shown = 0
50
51 def setfileheading(self, fileheading):
52 self.fileheading = fileheading
53 self.shown = 0
54
55 def write(self, text):
56 if self.fileheading and not self.shown:
57 self.logfileobj.write(self.fileheading)
58 self.shown = 1
59 self.logfileobj.write(text)
60
61 null_cell = xlrd.empty_cell
62
63 def show_row(bk, sh, rowx, colrange, printit):
64 if bk.ragged_rows:
65 colrange = range(sh.row_len(rowx))
66 if not colrange: return
67 if printit: print()
68 if bk.formatting_info:
69 for colx, ty, val, cxfx in get_row_data(bk, sh, rowx, colrange):
70 if printit:
71 print("cell %s%d: type=%d, data: %r, xfx: %s"
72 % (xlrd.colname(colx), rowx+1, ty, val, cxfx))
73 else:
74 for colx, ty, val, _unused in get_row_data(bk, sh, rowx, colrange):
75 if printit:
76 print("cell %s%d: type=%d, data: %r" % (xlrd.colname(colx), rowx+1, ty, val))
77
78 def get_row_data(bk, sh, rowx, colrange):
79 result = []
80 dmode = bk.datemode
81 ctys = sh.row_types(rowx)
82 cvals = sh.row_values(rowx)
83 for colx in colrange:
84 cty = ctys[colx]
85 cval = cvals[colx]
86 if bk.formatting_info:
87 cxfx = str(sh.cell_xf_index(rowx, colx))
88 else:
89 cxfx = ''
90 if cty == xlrd.XL_CELL_DATE:
91 try:
92 showval = xlrd.xldate_as_tuple(cval, dmode)
93 except xlrd.XLDateError as e:
94 showval = "%s:%s" % (type(e).__name__, e)
95 cty = xlrd.XL_CELL_ERROR
96 elif cty == xlrd.XL_CELL_ERROR:
97 showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
98 else:
99 showval = cval
100 result.append((colx, cty, showval, cxfx))
101 return result
102
103 def bk_header(bk):
104 print()
105 print("BIFF version: %s; datemode: %s"
106 % (xlrd.biff_text_from_num[bk.biff_version], bk.datemode))
107 print("codepage: %r (encoding: %s); countries: %r"
108 % (bk.codepage, bk.encoding, bk.countries))
109 print("Last saved by: %r" % bk.user_name)
110 print("Number of data sheets: %d" % bk.nsheets)
111 print("Use mmap: %d; Formatting: %d; On demand: %d"
112 % (bk.use_mmap, bk.formatting_info, bk.on_demand))
113 print("Ragged rows: %d" % bk.ragged_rows)
114 if bk.formatting_info:
115 print("FORMATs: %d, FONTs: %d, XFs: %d"
116 % (len(bk.format_list), len(bk.font_list), len(bk.xf_list)))
117 if not options.suppress_timing:
118 print("Load time: %.2f seconds (stage 1) %.2f seconds (stage 2)"
119 % (bk.load_time_stage_1, bk.load_time_stage_2))
120 print()
121
122 def show_fonts(bk):
123 print("Fonts:")
124 for x in xrange(len(bk.font_list)):
125 font = bk.font_list[x]
126 font.dump(header='== Index %d ==' % x, indent=4)
127
128 def show_names(bk, dump=0):
129 bk_header(bk)
130 if bk.biff_version < 50:
131 print("Names not extracted in this BIFF version")
132 return
133 nlist = bk.name_obj_list
134 print("Name list: %d entries" % len(nlist))
135 for nobj in nlist:
136 if dump:
137 nobj.dump(sys.stdout,
138 header="\n=== Dump of name_obj_list[%d] ===" % nobj.name_index)
139 else:
140 print("[%d]\tName:%r macro:%r scope:%d\n\tresult:%r\n"
141 % (nobj.name_index, nobj.name, nobj.macro, nobj.scope, nobj.result))
142
143 def print_labels(sh, labs, title):
144 if not labs:return
145 for rlo, rhi, clo, chi in labs:
146 print("%s label range %s:%s contains:"
147 % (title, xlrd.cellname(rlo, clo), xlrd.cellname(rhi-1, chi-1)))
148 for rx in xrange(rlo, rhi):
149 for cx in xrange(clo, chi):
150 print(" %s: %r" % (xlrd.cellname(rx, cx), sh.cell_value(rx, cx)))
151
152 def show_labels(bk):
153 # bk_header(bk)
154 hdr = 0
155 for shx in range(bk.nsheets):
156 sh = bk.sheet_by_index(shx)
157 clabs = sh.col_label_ranges
158 rlabs = sh.row_label_ranges
159 if clabs or rlabs:
160 if not hdr:
161 bk_header(bk)
162 hdr = 1
163 print("sheet %d: name = %r; nrows = %d; ncols = %d" %
164 (shx, sh.name, sh.nrows, sh.ncols))
165 print_labels(sh, clabs, 'Col')
166 print_labels(sh, rlabs, 'Row')
167 if bk.on_demand: bk.unload_sheet(shx)
168
169 def show(bk, nshow=65535, printit=1):
170 bk_header(bk)
171 if 0:
172 rclist = xlrd.sheet.rc_stats.items()
173 rclist = sorted(rclist)
174 print("rc stats")
175 for k, v in rclist:
176 print("0x%04x %7d" % (k, v))
177 if options.onesheet:
178 try:
179 shx = int(options.onesheet)
180 except ValueError:
181 shx = bk.sheet_by_name(options.onesheet).number
182 shxrange = [shx]
183 else:
184 shxrange = range(bk.nsheets)
185 # print("shxrange", list(shxrange))
186 for shx in shxrange:
187 sh = bk.sheet_by_index(shx)
188 nrows, ncols = sh.nrows, sh.ncols
189 colrange = range(ncols)
190 anshow = min(nshow, nrows)
191 print("sheet %d: name = %s; nrows = %d; ncols = %d" %
192 (shx, REPR(sh.name), sh.nrows, sh.ncols))
193 if nrows and ncols:
194 # Beat the bounds
195 for rowx in xrange(nrows):
196 nc = sh.row_len(rowx)
197 if nc:
198 sh.row_types(rowx)[nc-1]
199 sh.row_values(rowx)[nc-1]
200 sh.cell(rowx, nc-1)
201 for rowx in xrange(anshow-1):
202 if not printit and rowx % 10000 == 1 and rowx > 1:
203 print("done %d rows" % (rowx-1,))
204 show_row(bk, sh, rowx, colrange, printit)
205 if anshow and nrows:
206 show_row(bk, sh, nrows-1, colrange, printit)
207 print()
208 if bk.on_demand: bk.unload_sheet(shx)
209
210 def count_xfs(bk):
211 bk_header(bk)
212 for shx in range(bk.nsheets):
213 sh = bk.sheet_by_index(shx)
214 nrows = sh.nrows
215 print("sheet %d: name = %r; nrows = %d; ncols = %d" %
216 (shx, sh.name, sh.nrows, sh.ncols))
217 # Access all xfindexes to force gathering stats
218 type_stats = [0, 0, 0, 0, 0, 0, 0]
219 for rowx in xrange(nrows):
220 for colx in xrange(sh.row_len(rowx)):
221 xfx = sh.cell_xf_index(rowx, colx)
222 assert xfx >= 0
223 cty = sh.cell_type(rowx, colx)
224 type_stats[cty] += 1
225 print("XF stats", sh._xf_index_stats)
226 print("type stats", type_stats)
227 print()
228 if bk.on_demand: bk.unload_sheet(shx)
229
230 def main(cmd_args):
231 import optparse
232 global options
233 usage = "\n%prog [options] command [input-file-patterns]\n" + cmd_doc
234 oparser = optparse.OptionParser(usage)
235 oparser.add_option(
236 "-l", "--logfilename",
237 default="",
238 help="contains error messages")
239 oparser.add_option(
240 "-v", "--verbosity",
241 type="int", default=0,
242 help="level of information and diagnostics provided")
243 oparser.add_option(
244 "-m", "--mmap",
245 type="int", default=-1,
246 help="1: use mmap; 0: don't use mmap; -1: accept heuristic")
247 oparser.add_option(
248 "-e", "--encoding",
249 default="",
250 help="encoding override")
251 oparser.add_option(
252 "-f", "--formatting",
253 type="int", default=0,
254 help="0 (default): no fmt info\n"
255 "1: fmt info (all cells)\n",
256 )
257 oparser.add_option(
258 "-g", "--gc",
259 type="int", default=0,
260 help="0: auto gc enabled; 1: auto gc disabled, manual collect after each file; 2: no gc")
261 oparser.add_option(
262 "-s", "--onesheet",
263 default="",
264 help="restrict output to this sheet (name or index)")
265 oparser.add_option(
266 "-u", "--unnumbered",
267 action="store_true", default=0,
268 help="omit line numbers or offsets in biff_dump")
269 oparser.add_option(
270 "-d", "--on-demand",
271 action="store_true", default=0,
272 help="load sheets on demand instead of all at once")
273 oparser.add_option(
274 "-t", "--suppress-timing",
275 action="store_true", default=0,
276 help="don't print timings (diffs are less messy)")
277 oparser.add_option(
278 "-r", "--ragged-rows",
279 action="store_true", default=0,
280 help="open_workbook(..., ragged_rows=True)")
281 options, args = oparser.parse_args(cmd_args)
282 if len(args) == 1 and args[0] in ("version", ):
283 pass
284 elif len(args) < 2:
285 oparser.error("Expected at least 2 args, found %d" % len(args))
286 cmd = args[0]
287 xlrd_version = getattr(xlrd, "__VERSION__", "unknown; before 0.5")
288 if cmd == 'biff_dump':
289 xlrd.dump(args[1], unnumbered=options.unnumbered)
290 sys.exit(0)
291 if cmd == 'biff_count':
292 xlrd.count_records(args[1])
293 sys.exit(0)
294 if cmd == 'version':
295 print("xlrd: %s, from %s" % (xlrd_version, xlrd.__file__))
296 print("Python:", sys.version)
297 sys.exit(0)
298 if options.logfilename:
299 logfile = LogHandler(open(options.logfilename, 'w'))
300 else:
301 logfile = sys.stdout
302 mmap_opt = options.mmap
303 mmap_arg = xlrd.USE_MMAP
304 if mmap_opt in (1, 0):
305 mmap_arg = mmap_opt
306 elif mmap_opt != -1:
307 print('Unexpected value (%r) for mmap option -- assuming default' % mmap_opt)
308 fmt_opt = options.formatting | (cmd in ('xfc', ))
309 gc_mode = options.gc
310 if gc_mode:
311 gc.disable()
312 for pattern in args[1:]:
313 for fname in glob.glob(pattern):
314 print("\n=== File: %s ===" % fname)
315 if logfile != sys.stdout:
316 logfile.setfileheading("\n=== File: %s ===\n" % fname)
317 if gc_mode == 1:
318 n_unreachable = gc.collect()
319 if n_unreachable:
320 print("GC before open:", n_unreachable, "unreachable objects")
321 try:
322 t0 = time.time()
323 bk = xlrd.open_workbook(
324 fname,
325 verbosity=options.verbosity, logfile=logfile,
326 use_mmap=mmap_arg,
327 encoding_override=options.encoding,
328 formatting_info=fmt_opt,
329 on_demand=options.on_demand,
330 ragged_rows=options.ragged_rows,
331 )
332 t1 = time.time()
333 if not options.suppress_timing:
334 print("Open took %.2f seconds" % (t1-t0,))
335 except xlrd.XLRDError as e:
336 print("*** Open failed: %s: %s" % (type(e).__name__, e))
337 continue
338 except KeyboardInterrupt:
339 print("*** KeyboardInterrupt ***")
340 traceback.print_exc(file=sys.stdout)
341 sys.exit(1)
342 except BaseException as e:
343 print("*** Open failed: %s: %s" % (type(e).__name__, e))
344 traceback.print_exc(file=sys.stdout)
345 continue
346 t0 = time.time()
347 if cmd == 'hdr':
348 bk_header(bk)
349 elif cmd == 'ov': # OverView
350 show(bk, 0)
351 elif cmd == 'show': # all rows
352 show(bk)
353 elif cmd == '2rows': # first row and last row
354 show(bk, 2)
355 elif cmd == '3rows': # first row, 2nd row and last row
356 show(bk, 3)
357 elif cmd == 'bench':
358 show(bk, printit=0)
359 elif cmd == 'fonts':
360 bk_header(bk)
361 show_fonts(bk)
362 elif cmd == 'names': # named reference list
363 show_names(bk)
364 elif cmd == 'name_dump': # named reference list
365 show_names(bk, dump=1)
366 elif cmd == 'labels':
367 show_labels(bk)
368 elif cmd == 'xfc':
369 count_xfs(bk)
370 else:
371 print("*** Unknown command <%s>" % cmd)
372 sys.exit(1)
373 del bk
374 if gc_mode == 1:
375 n_unreachable = gc.collect()
376 if n_unreachable:
377 print("GC post cmd:", fname, "->", n_unreachable, "unreachable objects")
378 if not options.suppress_timing:
379 t1 = time.time()
380 print("\ncommand took %.2f seconds\n" % (t1-t0,))
381
382 return None
383
384 av = sys.argv[1:]
385 if not av:
386 main(av)
387 firstarg = av[0].lower()
388 if firstarg == "hotshot":
389 import hotshot
390 import hotshot.stats
391 av = av[1:]
392 prof_log_name = "XXXX.prof"
393 prof = hotshot.Profile(prof_log_name)
394 # benchtime, result = prof.runcall(main, *av)
395 result = prof.runcall(main, *(av, ))
396 print("result", repr(result))
397 prof.close()
398 stats = hotshot.stats.load(prof_log_name)
399 stats.strip_dirs()
400 stats.sort_stats('time', 'calls')
401 stats.print_stats(20)
402 elif firstarg == "profile":
403 import cProfile
404 av = av[1:]
405 cProfile.run('main(av)', 'YYYY.prof')
406 import pstats
407 p = pstats.Stats('YYYY.prof')
408 p.strip_dirs().sort_stats('cumulative').print_stats(30)
409 else:
410 main(av)