Source code

Download source archive ↓

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)