All Downloads are FREE. Search and download functionalities are using the official Maven repository.

Explore the source code of the class book.py

# Copyright (c) 2005-2012 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

from .timemachine import *
from .biffh import *
import struct; unpack = struct.unpack
import sys
import time
from . import sheet
from . import compdoc
from .formula import *
from . import formatting
if sys.version.startswith("IronPython"):
    # print >> sys.stderr, "...importing encodings"
    import encodings

empty_cell = sheet.empty_cell # for exposure to the world ...

DEBUG = 0

USE_FANCY_CD = 1

TOGGLE_GC = 0
import gc
# gc.set_debug(gc.DEBUG_STATS)

try:
    import mmap
    MMAP_AVAILABLE = 1
except ImportError:
    MMAP_AVAILABLE = 0
USE_MMAP = MMAP_AVAILABLE

MY_EOF = 0xF00BAAA # not a 16-bit number

SUPBOOK_UNK, SUPBOOK_INTERNAL, SUPBOOK_EXTERNAL, SUPBOOK_ADDIN, SUPBOOK_DDEOLE = range(5)

SUPPORTED_VERSIONS = (80, 70, 50, 45, 40, 30, 21, 20)

_code_from_builtin_name = {
    "Consolidate_Area": "\x00",
    "Auto_Open":        "\x01",
    "Auto_Close":       "\x02",
    "Extract":          "\x03",
    "Database":         "\x04",
    "Criteria":         "\x05",
    "Print_Area":       "\x06",
    "Print_Titles":     "\x07",
    "Recorder":         "\x08",
    "Data_Form":        "\x09",
    "Auto_Activate":    "\x0A",
    "Auto_Deactivate":  "\x0B",
    "Sheet_Title":      "\x0C",
    "_FilterDatabase":  "\x0D",
    }
builtin_name_from_code = {}
code_from_builtin_name = {}
for _bin, _bic in _code_from_builtin_name.items():
    _bin = UNICODE_LITERAL(_bin)
    _bic = UNICODE_LITERAL(_bic)
    code_from_builtin_name[_bin] = _bic
    builtin_name_from_code[_bic] = _bin
del _bin, _bic, _code_from_builtin_name

def open_workbook_xls(filename=None,
    logfile=sys.stdout, verbosity=0, use_mmap=USE_MMAP,
    file_contents=None,
    encoding_override=None,
    formatting_info=False, on_demand=False, ragged_rows=False,
    ):
    t0 = time.clock()
    if TOGGLE_GC:
        orig_gc_enabled = gc.isenabled()
        if orig_gc_enabled:
            gc.disable()
    bk = Book()
    try:
        bk.biff2_8_load(
            filename=filename, file_contents=file_contents,
            logfile=logfile, verbosity=verbosity, use_mmap=use_mmap,
            encoding_override=encoding_override,
            formatting_info=formatting_info,
            on_demand=on_demand,
            ragged_rows=ragged_rows,
            )
        t1 = time.clock()
        bk.load_time_stage_1 = t1 - t0
        biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
        if not biff_version:
            raise XLRDError("Can't determine file's BIFF version")
        if biff_version not in SUPPORTED_VERSIONS:
            raise XLRDError(
                "BIFF version %s is not supported"
                % biff_text_from_num[biff_version]
                )
        bk.biff_version = biff_version
        if biff_version <= 40:
            # no workbook globals, only 1 worksheet
            if on_demand:
                fprintf(bk.logfile,
                    "*** WARNING: on_demand is not supported for this Excel version.\n"
                    "*** Setting on_demand to False.\n")
                bk.on_demand = on_demand = False
            bk.fake_globals_get_sheet()
        elif biff_version == 45:
            # worksheet(s) embedded in global stream
            bk.parse_globals()
            if on_demand:
                fprintf(bk.logfile, "*** WARNING: on_demand is not supported for this Excel version.\n"
                                    "*** Setting on_demand to False.\n")
                bk.on_demand = on_demand = False
        else:
            bk.parse_globals()
            bk._sheet_list = [None for sh in bk._sheet_names]
            if not on_demand:
                bk.get_sheets()
        bk.nsheets = len(bk._sheet_list)
        if biff_version == 45 and bk.nsheets > 1:
            fprintf(bk.logfile,
                "*** WARNING: Excel 4.0 workbook (.XLW) file contains %d worksheets.\n"
                "*** Book-level data will be that of the last worksheet.\n",
                bk.nsheets
                )
        if TOGGLE_GC:
            if orig_gc_enabled:
                gc.enable()
        t2 = time.clock()
        bk.load_time_stage_2 = t2 - t1
    except:
        bk.release_resources()
        raise
    # normal exit
    if not on_demand:
        bk.release_resources()
    return bk

##
# For debugging: dump the file's BIFF records in char & hex.
# @param filename The path to the file to be dumped.
# @param outfile An open file, to which the dump is written.
# @param unnumbered If true, omit offsets (for meaningful diffs).

def dump(filename, outfile=sys.stdout, unnumbered=False):
    bk = Book()
    bk.biff2_8_load(filename=filename, logfile=outfile, )
    biff_dump(bk.mem, bk.base, bk.stream_len, 0, outfile, unnumbered)

##
# For debugging and analysis: summarise the file's BIFF records.
# I.e. produce a sorted file of (record_name, count).
# @param filename The path to the file to be summarised.
# @param outfile An open file, to which the summary is written.

def count_records(filename, outfile=sys.stdout):
    bk = Book()
    bk.biff2_8_load(filename=filename, logfile=outfile, )
    biff_count_records(bk.mem, bk.base, bk.stream_len, outfile)

##
# Information relating to a named reference, formula, macro, etc.
# 
-- New in version 0.6.0 #
-- Name information is not extracted from files older than # Excel 5.0 (Book.biff_version < 50) class Name(BaseObject): _repr_these = ['stack'] book = None # parent ## # 0 = Visible; 1 = Hidden hidden = 0 ## # 0 = Command macro; 1 = Function macro. Relevant only if macro == 1 func = 0 ## # 0 = Sheet macro; 1 = VisualBasic macro. Relevant only if macro == 1 vbasic = 0 ## # 0 = Standard name; 1 = Macro name macro = 0 ## # 0 = Simple formula; 1 = Complex formula (array formula or user defined)
# No examples have been sighted. complex = 0 ## # 0 = User-defined name; 1 = Built-in name # (common examples: Print_Area, Print_Titles; see OOo docs for full list) builtin = 0 ## # Function group. Relevant only if macro == 1; see OOo docs for values. funcgroup = 0 ## # 0 = Formula definition; 1 = Binary data
No examples have been sighted. binary = 0 ## # The index of this object in book.name_obj_list name_index = 0 ## # A Unicode string. If builtin, decoded as per OOo docs. name = UNICODE_LITERAL("") ## # An 8-bit string. raw_formula = b'' ## # -1: The name is global (visible in all calculation sheets).
# -2: The name belongs to a macro sheet or VBA sheet.
# -3: The name is invalid.
# 0 <= scope < book.nsheets: The name is local to the sheet whose index is scope. scope = -1 ## # The result of evaluating the formula, if any. # If no formula, or evaluation of the formula encountered problems, # the result is None. Otherwise the result is a single instance of the # Operand class. # result = None ## # This is a convenience method for the frequent use case where the name # refers to a single cell. # @return An instance of the Cell class. # @throws XLRDError The name is not a constant absolute reference # to a single cell. def cell(self): res = self.result if res: # result should be an instance of the Operand class kind = res.kind value = res.value if kind == oREF and len(value) == 1: ref3d = value[0] if (0 <= ref3d.shtxlo == ref3d.shtxhi - 1 and ref3d.rowxlo == ref3d.rowxhi - 1 and ref3d.colxlo == ref3d.colxhi - 1): sh = self.book.sheet_by_index(ref3d.shtxlo) return sh.cell(ref3d.rowxlo, ref3d.colxlo) self.dump(self.book.logfile, header="=== Dump of Name object ===", footer="======= End of dump =======", ) raise XLRDError("Not a constant absolute reference to a single cell") ## # This is a convenience method for the use case where the name # refers to one rectangular area in one worksheet. # @param clipped If true (the default), the returned rectangle is clipped # to fit in (0, sheet.nrows, 0, sheet.ncols) -- it is guaranteed that # 0 <= rowxlo <= rowxhi <= sheet.nrows and that the number of usable rows # in the area (which may be zero) is rowxhi - rowxlo; likewise for columns. # @return a tuple (sheet_object, rowxlo, rowxhi, colxlo, colxhi). # @throws XLRDError The name is not a constant absolute reference # to a single area in a single sheet. def area2d(self, clipped=True): res = self.result if res: # result should be an instance of the Operand class kind = res.kind value = res.value if kind == oREF and len(value) == 1: # only 1 reference ref3d = value[0] if 0 <= ref3d.shtxlo == ref3d.shtxhi - 1: # only 1 usable sheet sh = self.book.sheet_by_index(ref3d.shtxlo) if not clipped: return sh, ref3d.rowxlo, ref3d.rowxhi, ref3d.colxlo, ref3d.colxhi rowxlo = min(ref3d.rowxlo, sh.nrows) rowxhi = max(rowxlo, min(ref3d.rowxhi, sh.nrows)) colxlo = min(ref3d.colxlo, sh.ncols) colxhi = max(colxlo, min(ref3d.colxhi, sh.ncols)) assert 0 <= rowxlo <= rowxhi <= sh.nrows assert 0 <= colxlo <= colxhi <= sh.ncols return sh, rowxlo, rowxhi, colxlo, colxhi self.dump(self.book.logfile, header="=== Dump of Name object ===", footer="======= End of dump =======", ) raise XLRDError("Not a constant absolute reference to a single area in a single sheet") ## # Contents of a "workbook". #

WARNING: You don't call this class yourself. You use the Book object that # was returned when you called xlrd.open_workbook("myfile.xls").

class Book(BaseObject): ## # The number of worksheets present in the workbook file. # This information is available even when no sheets have yet been loaded. nsheets = 0 ## # Which date system was in force when this file was last saved.
# 0 => 1900 system (the Excel for Windows default).
# 1 => 1904 system (the Excel for Macintosh default).
datemode = 0 # In case it's not specified in the file. ## # Version of BIFF (Binary Interchange File Format) used to create the file. # Latest is 8.0 (represented here as 80), introduced with Excel 97. # Earliest supported by this module: 2.0 (represented as 20). biff_version = 0 ## # List containing a Name object for each NAME record in the workbook. #
-- New in version 0.6.0 name_obj_list = [] ## # An integer denoting the character set used for strings in this file. # For BIFF 8 and later, this will be 1200, meaning Unicode; more precisely, UTF_16_LE. # For earlier versions, this is used to derive the appropriate Python encoding # to be used to convert to Unicode. # Examples: 1252 -> 'cp1252', 10000 -> 'mac_roman' codepage = None ## # The encoding that was derived from the codepage. encoding = None ## # A tuple containing the (telephone system) country code for:
# [0]: the user-interface setting when the file was created.
# [1]: the regional settings.
# Example: (1, 61) meaning (USA, Australia). # This information may give a clue to the correct encoding for an unknown codepage. # For a long list of observed values, refer to the OpenOffice.org documentation for # the COUNTRY record. countries = (0, 0) ## # What (if anything) is recorded as the name of the last user to save the file. user_name = UNICODE_LITERAL('') ## # A list of Font class instances, each corresponding to a FONT record. #
-- New in version 0.6.1 font_list = [] ## # A list of XF class instances, each corresponding to an XF record. #
-- New in version 0.6.1 xf_list = [] ## # A list of Format objects, each corresponding to a FORMAT record, in # the order that they appear in the input file. # It does not contain builtin formats. # If you are creating an output file using (for example) pyExcelerator, # use this list. # The collection to be used for all visual rendering purposes is format_map. #
-- New in version 0.6.1 format_list = [] ## # The mapping from XF.format_key to Format object. #
-- New in version 0.6.1 format_map = {} ## # This provides access via name to the extended format information for # both built-in styles and user-defined styles.
# It maps name to (built_in, xf_index), where:
# name is either the name of a user-defined style, # or the name of one of the built-in styles. Known built-in names are # Normal, RowLevel_1 to RowLevel_7, # ColLevel_1 to ColLevel_7, Comma, Currency, Percent, "Comma [0]", # "Currency [0]", Hyperlink, and "Followed Hyperlink".
# built_in 1 = built-in style, 0 = user-defined
# xf_index is an index into Book.xf_list.
# References: OOo docs s6.99 (STYLE record); Excel UI Format/Style #
-- New in version 0.6.1; since 0.7.4, extracted only if # open_workbook(..., formatting_info=True) style_name_map = {} ## # This provides definitions for colour indexes. Please refer to the # above section "The Palette; Colour Indexes" for an explanation # of how colours are represented in Excel.
# Colour indexes into the palette map into (red, green, blue) tuples. # "Magic" indexes e.g. 0x7FFF map to None. # colour_map is what you need if you want to render cells on screen or in a PDF # file. If you are writing an output XLS file, use palette_record. #
-- New in version 0.6.1. Extracted only if open_workbook(..., formatting_info=True) colour_map = {} ## # If the user has changed any of the colours in the standard palette, the XLS # file will contain a PALETTE record with 56 (16 for Excel 4.0 and earlier) # RGB values in it, and this list will be e.g. [(r0, b0, g0), ..., (r55, b55, g55)]. # Otherwise this list will be empty. This is what you need if you are # writing an output XLS file. If you want to render cells on screen or in a PDF # file, use colour_map. #
-- New in version 0.6.1. Extracted only if open_workbook(..., formatting_info=True) palette_record = [] ## # Time in seconds to extract the XLS image as a contiguous string (or mmap equivalent). load_time_stage_1 = -1.0 ## # Time in seconds to parse the data from the contiguous string (or mmap equivalent). load_time_stage_2 = -1.0 ## # @return A list of all sheets in the book. # All sheets not already loaded will be loaded. def sheets(self): for sheetx in xrange(self.nsheets): if not self._sheet_list[sheetx]: self.get_sheet(sheetx) return self._sheet_list[:] ## # @param sheetx Sheet index in range(nsheets) # @return An object of the Sheet class def sheet_by_index(self, sheetx): return self._sheet_list[sheetx] or self.get_sheet(sheetx) ## # @param sheet_name Name of sheet required # @return An object of the Sheet class def sheet_by_name(self, sheet_name): try: sheetx = self._sheet_names.index(sheet_name) except ValueError: raise XLRDError('No sheet named <%r>' % sheet_name) return self.sheet_by_index(sheetx) ## # @return A list of the names of all the worksheets in the workbook file. # This information is available even when no sheets have yet been loaded. def sheet_names(self): return self._sheet_names[:] ## # @param sheet_name_or_index Name or index of sheet enquired upon # @return true if sheet is loaded, false otherwise #
-- New in version 0.7.1 def sheet_loaded(self, sheet_name_or_index): if isinstance(sheet_name_or_index, int): sheetx = sheet_name_or_index else: try: sheetx = self._sheet_names.index(sheet_name_or_index) except ValueError: raise XLRDError('No sheet named <%r>' % sheet_name_or_index) return bool(self._sheet_list[sheetx]) ## # @param sheet_name_or_index Name or index of sheet to be unloaded. #
-- New in version 0.7.1 def unload_sheet(self, sheet_name_or_index): if isinstance(sheet_name_or_index, int): sheetx = sheet_name_or_index else: try: sheetx = self._sheet_names.index(sheet_name_or_index) except ValueError: raise XLRDError('No sheet named <%r>' % sheet_name_or_index) self._sheet_list[sheetx] = None ## # This method has a dual purpose. You can call it to release # memory-consuming objects and (possibly) a memory-mapped file # (mmap.mmap object) when you have finished loading sheets in # on_demand mode, but still require the Book object to examine the # loaded sheets. It is also called automatically (a) when open_workbook # raises an exception and (b) if you are using a "with" statement, when # the "with" block is exited. Calling this method multiple times on the # same object has no ill effect. def release_resources(self): self._resources_released = 1 if hasattr(self.mem, "close"): # must be a mmap.mmap object self.mem.close() self.mem = None if hasattr(self.filestr, "close"): self.filestr.close() self.filestr = None self._sharedstrings = None self._rich_text_runlist_map = None def __enter__(self): return self def __exit__(self, exc_type, exc_value, exc_tb): self.release_resources() # return false ## # A mapping from (lower_case_name, scope) to a single Name object. #
-- New in version 0.6.0 name_and_scope_map = {} ## # A mapping from lower_case_name to a list of Name objects. The list is # sorted in scope order. Typically there will be one item (of global scope) # in the list. #
-- New in version 0.6.0 name_map = {} def __init__(self): self._sheet_list = [] self._sheet_names = [] self._sheet_visibility = [] # from BOUNDSHEET record self.nsheets = 0 self._sh_abs_posn = [] # sheet's absolute position in the stream self._sharedstrings = [] self._rich_text_runlist_map = {} self.raw_user_name = False self._sheethdr_count = 0 # BIFF 4W only self.builtinfmtcount = -1 # unknown as yet. BIFF 3, 4S, 4W self.initialise_format_info() self._all_sheets_count = 0 # includes macro & VBA sheets self._supbook_count = 0 self._supbook_locals_inx = None self._supbook_addins_inx = None self._all_sheets_map = [] # maps an all_sheets index to a calc-sheets index (or -1) self._externsheet_info = [] self._externsheet_type_b57 = [] self._extnsht_name_from_num = {} self._sheet_num_from_name = {} self._extnsht_count = 0 self._supbook_types = [] self._resources_released = 0 self.addin_func_names = [] self.name_obj_list = [] self.colour_map = {} self.palette_record = [] self.xf_list = [] self.style_name_map = {} self.mem = b'' self.filestr = b'' def biff2_8_load(self, filename=None, file_contents=None, logfile=sys.stdout, verbosity=0, use_mmap=USE_MMAP, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False, ): # DEBUG = 0 self.logfile = logfile self.verbosity = verbosity self.use_mmap = use_mmap and MMAP_AVAILABLE self.encoding_override = encoding_override self.formatting_info = formatting_info self.on_demand = on_demand self.ragged_rows = ragged_rows if not file_contents: with open(filename, "rb") as f: f.seek(0, 2) # EOF size = f.tell() f.seek(0, 0) # BOF if size == 0: raise XLRDError("File size is 0 bytes") if self.use_mmap: self.filestr = mmap.mmap(f.fileno(), size, access=mmap.ACCESS_READ) self.stream_len = size else: self.filestr = f.read() self.stream_len = len(self.filestr) else: self.filestr = file_contents self.stream_len = len(file_contents) self.base = 0 if self.filestr[:8] != compdoc.SIGNATURE: # got this one at the antique store self.mem = self.filestr else: cd = compdoc.CompDoc(self.filestr, logfile=self.logfile) if USE_FANCY_CD: for qname in ['Workbook', 'Book']: self.mem, self.base, self.stream_len = \ cd.locate_named_stream(UNICODE_LITERAL(qname)) if self.mem: break else: raise XLRDError("Can't find workbook in OLE2 compound document") else: for qname in ['Workbook', 'Book']: self.mem = cd.get_named_stream(UNICODE_LITERAL(qname)) if self.mem: break else: raise XLRDError("Can't find workbook in OLE2 compound document") self.stream_len = len(self.mem) del cd if self.mem is not self.filestr: if hasattr(self.filestr, "close"): self.filestr.close() self.filestr = b'' self._position = self.base if DEBUG: print("mem: %s, base: %d, len: %d" % (type(self.mem), self.base, self.stream_len), file=self.logfile) def initialise_format_info(self): # needs to be done once per sheet for BIFF 4W :-( self.format_map = {} self.format_list = [] self.xfcount = 0 self.actualfmtcount = 0 # number of FORMAT records seen so far self._xf_index_to_xl_type_map = {0: XL_CELL_NUMBER} self._xf_epilogue_done = 0 self.xf_list = [] self.font_list = [] def get2bytes(self): pos = self._position buff_two = self.mem[pos:pos+2] lenbuff = len(buff_two) self._position += lenbuff if lenbuff < 2: return MY_EOF lo, hi = buff_two return (BYTES_ORD(hi) << 8) | BYTES_ORD(lo) def get_record_parts(self): pos = self._position mem = self.mem code, length = unpack('= 2: fprintf(self.logfile, "BOUNDSHEET: inx=%d vis=%r sheet_name=%r abs_posn=%d sheet_type=0x%02x\n", self._all_sheets_count, visibility, sheet_name, abs_posn, sheet_type) self._all_sheets_count += 1 if sheet_type != XL_BOUNDSHEET_WORKSHEET: self._all_sheets_map.append(-1) descr = { 1: 'Macro sheet', 2: 'Chart', 6: 'Visual Basic module', }.get(sheet_type, 'UNKNOWN') if DEBUG or self.verbosity >= 1: fprintf(self.logfile, "NOTE *** Ignoring non-worksheet data named %r (type 0x%02x = %s)\n", sheet_name, sheet_type, descr) else: snum = len(self._sheet_names) self._all_sheets_map.append(snum) self._sheet_names.append(sheet_name) self._sh_abs_posn.append(abs_posn) self._sheet_visibility.append(visibility) self._sheet_num_from_name[sheet_name] = snum def handle_builtinfmtcount(self, data): ### N.B. This count appears to be utterly useless. # DEBUG = 1 builtinfmtcount = unpack('= 2: fprintf(self.logfile, "*** No CODEPAGE record; assuming 1200 (utf_16_le)\n") else: codepage = self.codepage if codepage in encoding_from_codepage: encoding = encoding_from_codepage[codepage] elif 300 <= codepage <= 1999: encoding = 'cp' + str(codepage) else: encoding = 'unknown_codepage_' + str(codepage) if DEBUG or (self.verbosity and encoding != self.encoding) : fprintf(self.logfile, "CODEPAGE: codepage %r -> encoding %r\n", codepage, encoding) self.encoding = encoding if self.codepage != 1200: # utf_16_le # If we don't have a codec that can decode ASCII into Unicode, # we're well & truly stuffed -- let the punter know ASAP. try: _unused = unicode(b'trial', self.encoding) except BaseException as e: fprintf(self.logfile, "ERROR *** codepage %r -> encoding %r -> %s: %s\n", self.codepage, self.encoding, type(e).__name__.split(".")[-1], e) raise if self.raw_user_name: strg = unpack_string(self.user_name, 0, self.encoding, lenlen=1) strg = strg.rstrip() # if DEBUG: # print "CODEPAGE: user name decoded from %r to %r" % (self.user_name, strg) self.user_name = strg self.raw_user_name = False return self.encoding def handle_codepage(self, data): # DEBUG = 0 codepage = unpack('= 2 if self.biff_version >= 80: option_flags, other_info =unpack("= 1 blah2 = DEBUG or self.verbosity >= 2 if self.biff_version >= 80: num_refs = unpack("= 2: logf = self.logfile fprintf(logf, "FILEPASS:\n") hex_char_dump(data, 0, len(data), base=0, fout=logf) if self.biff_version >= 80: kind1, = unpack('= 2 bv = self.biff_version if bv < 50: return self.derive_encoding() # print # hex_char_dump(data, 0, len(data), fout=self.logfile) ( option_flags, kb_shortcut, name_len, fmla_len, extsht_index, sheet_index, menu_text_len, description_text_len, help_topic_text_len, status_bar_text_len, ) = unpack("> nshift) macro_flag = " M"[nobj.macro] if bv < 80: internal_name, pos = unpack_string_update_pos(data, 14, self.encoding, known_len=name_len) else: internal_name, pos = unpack_unicode_update_pos(data, 14, known_len=name_len) nobj.extn_sheet_num = extsht_index nobj.excel_sheet_index = sheet_index nobj.scope = None # patched up in the names_epilogue() method if blah: fprintf( self.logfile, "NAME[%d]:%s oflags=%d, name_len=%d, fmla_len=%d, extsht_index=%d, sheet_index=%d, name=%r\n", name_index, macro_flag, option_flags, name_len, fmla_len, extsht_index, sheet_index, internal_name) name = internal_name if nobj.builtin: name = builtin_name_from_code.get(name, "??Unknown??") if blah: print(" builtin: %s" % name, file=self.logfile) nobj.name = name nobj.raw_formula = data[pos:] nobj.basic_formula_len = fmla_len nobj.evaluated = 0 if blah: nobj.dump( self.logfile, header="--- handle_name: name[%d] ---" % name_index, footer="-------------------", ) def names_epilogue(self): blah = self.verbosity >= 2 f = self.logfile if blah: print("+++++ names_epilogue +++++", file=f) print("_all_sheets_map", REPR(self._all_sheets_map), file=f) print("_extnsht_name_from_num", REPR(self._extnsht_name_from_num), file=f) print("_sheet_num_from_name", REPR(self._sheet_num_from_name), file=f) num_names = len(self.name_obj_list) for namex in range(num_names): nobj = self.name_obj_list[namex] # Convert from excel_sheet_index to scope. # This is done here because in BIFF7 and earlier, the # BOUNDSHEET records (from which _all_sheets_map is derived) # come after the NAME records. if self.biff_version >= 80: sheet_index = nobj.excel_sheet_index if sheet_index == 0: intl_sheet_index = -1 # global elif 1 <= sheet_index <= len(self._all_sheets_map): intl_sheet_index = self._all_sheets_map[sheet_index-1] if intl_sheet_index == -1: # maps to a macro or VBA sheet intl_sheet_index = -2 # valid sheet reference but not useful else: # huh? intl_sheet_index = -3 # invalid elif 50 <= self.biff_version <= 70: sheet_index = nobj.extn_sheet_num if sheet_index == 0: intl_sheet_index = -1 # global else: sheet_name = self._extnsht_name_from_num[sheet_index] intl_sheet_index = self._sheet_num_from_name.get(sheet_name, -2) nobj.scope = intl_sheet_index for namex in range(num_names): nobj = self.name_obj_list[namex] # Parse the formula ... if nobj.macro or nobj.binary: continue if nobj.evaluated: continue evaluate_name_formula(self, nobj, namex, blah=blah) if self.verbosity >= 2: print("---------- name object dump ----------", file=f) for namex in range(num_names): nobj = self.name_obj_list[namex] nobj.dump(f, header="--- name[%d] ---" % namex) print("--------------------------------------", file=f) # # Build some dicts for access to the name objects # name_and_scope_map = {} # (name.lower(), scope): Name_object name_map = {} # name.lower() : list of Name_objects (sorted in scope order) for namex in range(num_names): nobj = self.name_obj_list[namex] name_lcase = nobj.name.lower() key = (name_lcase, nobj.scope) if key in name_and_scope_map and self.verbosity: fprintf(f, 'Duplicate entry %r in name_and_scope_map\n', key) name_and_scope_map[key] = nobj sort_data = (nobj.scope, namex, nobj) # namex (a temp unique ID) ensures the Name objects will not # be compared (fatal in py3) if name_lcase in name_map: name_map[name_lcase].append(sort_data) else: name_map[name_lcase] = [sort_data] for key in name_map.keys(): alist = name_map[key] alist.sort() name_map[key] = [x[2] for x in alist] self.name_and_scope_map = name_and_scope_map self.name_map = name_map def handle_obj(self, data): # Not doing much handling at all. # Worrying about embedded (BOF ... EOF) substreams is done elsewhere. # DEBUG = 1 obj_type, obj_id = unpack(' handle_obj type=%d id=0x%08x" % (obj_type, obj_id) def handle_supbook(self, data): # aka EXTERNALBOOK in OOo docs self._supbook_types.append(None) blah = DEBUG or self.verbosity >= 2 if blah: print("SUPBOOK:", file=self.logfile) hex_char_dump(data, 0, len(data), fout=self.logfile) num_sheets = unpack("= 2: fprintf(self.logfile, "SST: unique strings: %d\n", uniquestrings) while 1: code, nb, data = self.get_record_parts_conditional(XL_CONTINUE) if code is None: break nbt += nb if DEBUG >= 2: fprintf(self.logfile, "CONTINUE: adding %d bytes to SST -> %d\n", nb, nbt) strlist.append(data) self._sharedstrings, rt_runlist = unpack_SST_table(strlist, uniquestrings) if self.formatting_info: self._rich_text_runlist_map = rt_runlist if DEBUG: t1 = time.time() print("SST processing took %.2f seconds" % (t1 - t0, ), file=self.logfile) def handle_writeaccess(self, data): DEBUG = 0 if self.biff_version < 80: if not self.encoding: self.raw_user_name = True self.user_name = data return strg = unpack_string(data, 0, self.encoding, lenlen=1) else: strg = unpack_unicode(data, 0, lenlen=2) if DEBUG: fprintf(self.logfile, "WRITEACCESS: %d bytes; raw=%s %r\n", len(data), self.raw_user_name, strg) strg = strg.rstrip() self.user_name = strg def parse_globals(self): # DEBUG = 0 # no need to position, just start reading (after the BOF) formatting.initialise_book(self) while 1: rc, length, data = self.get_record_parts() if DEBUG: print("parse_globals: record code is 0x%04x" % rc, file=self.logfile) if rc == XL_SST: self.handle_sst(data) elif rc == XL_FONT or rc == XL_FONT_B3B4: self.handle_font(data) elif rc == XL_FORMAT: # XL_FORMAT2 is BIFF <= 3.0, can't appear in globals self.handle_format(data) elif rc == XL_XF: self.handle_xf(data) elif rc == XL_BOUNDSHEET: self.handle_boundsheet(data) elif rc == XL_DATEMODE: self.handle_datemode(data) elif rc == XL_CODEPAGE: self.handle_codepage(data) elif rc == XL_COUNTRY: self.handle_country(data) elif rc == XL_EXTERNNAME: self.handle_externname(data) elif rc == XL_EXTERNSHEET: self.handle_externsheet(data) elif rc == XL_FILEPASS: self.handle_filepass(data) elif rc == XL_WRITEACCESS: self.handle_writeaccess(data) elif rc == XL_SHEETSOFFSET: self.handle_sheetsoffset(data) elif rc == XL_SHEETHDR: self.handle_sheethdr(data) elif rc == XL_SUPBOOK: self.handle_supbook(data) elif rc == XL_NAME: self.handle_name(data) elif rc == XL_PALETTE: self.handle_palette(data) elif rc == XL_STYLE: self.handle_style(data) elif rc & 0xff == 9 and self.verbosity: fprintf(self.logfile, "*** Unexpected BOF at posn %d: 0x%04x len=%d data=%r\n", self._position - length - 4, rc, length, data) elif rc == XL_EOF: self.xf_epilogue() self.names_epilogue() self.palette_epilogue() if not self.encoding: self.derive_encoding() if self.biff_version == 45: # DEBUG = 0 if DEBUG: print("global EOF: position", self._position, file=self.logfile) # if DEBUG: # pos = self._position - 4 # print repr(self.mem[pos:pos+40]) return else: # if DEBUG: # print >> self.logfile, "parse_globals: ignoring record code 0x%04x" % rc pass def read(self, pos, length): data = self.mem[pos:pos+length] self._position = pos + len(data) return data def getbof(self, rqd_stream): # DEBUG = 1 # if DEBUG: print >> self.logfile, "getbof(): position", self._position if DEBUG: print("reqd: 0x%04x" % rqd_stream, file=self.logfile) def bof_error(msg): raise XLRDError('Unsupported format, or corrupt file: ' + msg) savpos = self._position opcode = self.get2bytes() if opcode == MY_EOF: bof_error('Expected BOF record; met end of file') if opcode not in bofcodes: bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8]) length = self.get2bytes() if length == MY_EOF: bof_error('Incomplete BOF record[1]; met end of file') if not (4 <= length <= 20): bof_error( 'Invalid length (%d) for BOF record type 0x%04x' % (length, opcode)) padding = b'\0' * max(0, boflen[opcode] - length) data = self.read(self._position, length); if DEBUG: fprintf(self.logfile, "\ngetbof(): data=%r\n", data) if len(data) < length: bof_error('Incomplete BOF record[2]; met end of file') data += padding version1 = opcode >> 8 version2, streamtype = unpack('= 2: print("BOF: op=0x%04x vers=0x%04x stream=0x%04x buildid=%d buildyr=%d -> BIFF%d" \ % (opcode, version2, streamtype, build, year, version), file=self.logfile) got_globals = streamtype == XL_WORKBOOK_GLOBALS or ( version == 45 and streamtype == XL_WORKBOOK_GLOBALS_4W) if (rqd_stream == XL_WORKBOOK_GLOBALS and got_globals) or streamtype == rqd_stream: return version if version < 50 and streamtype == XL_WORKSHEET: return version if version >= 50 and streamtype == 0x0100: bof_error("Workspace file -- no spreadsheet data") bof_error( 'BOF not workbook/worksheet: op=0x%04x vers=0x%04x strm=0x%04x build=%d year=%d -> BIFF%d' \ % (opcode, version2, streamtype, build, year, version) ) # === helper functions def expand_cell_address(inrow, incol): # Ref : OOo docs, "4.3.4 Cell Addresses in BIFF8" outrow = inrow if incol & 0x8000: if outrow >= 32768: outrow -= 65536 relrow = 1 else: relrow = 0 outcol = incol & 0xFF if incol & 0x4000: if outcol >= 128: outcol -= 256 relcol = 1 else: relcol = 0 return outrow, outcol, relrow, relcol def colname(colx, _A2Z="ABCDEFGHIJKLMNOPQRSTUVWXYZ"): assert colx >= 0 name = UNICODE_LITERAL('') while 1: quot, rem = divmod(colx, 26) name = _A2Z[rem] + name if not quot: return name colx = quot - 1 def display_cell_address(rowx, colx, relrow, relcol): if relrow: rowpart = "(*%s%d)" % ("+-"[rowx < 0], abs(rowx)) else: rowpart = "$%d" % (rowx+1,) if relcol: colpart = "(*%s%d)" % ("+-"[colx < 0], abs(colx)) else: colpart = "$" + colname(colx) return colpart + rowpart def unpack_SST_table(datatab, nstrings): "Return list of strings" datainx = 0 ndatas = len(datatab) data = datatab[0] datalen = len(data) pos = 8 strings = [] strappend = strings.append richtext_runs = {} local_unpack = unpack local_min = min local_BYTES_ORD = BYTES_ORD latin_1 = "latin_1" for _unused_i in xrange(nstrings): nchars = local_unpack('> 1, charsneed) rawstrg = data[pos:pos+2*charsavail] # if DEBUG: print "SST U16: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg) try: accstrg += unicode(rawstrg, "utf_16_le") except: # print "SST U16: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg) # Probable cause: dodgy data e.g. unfinished surrogate pair. # E.g. file unicode2.xls in pyExcelerator's examples has cells containing # unichr(i) for i in range(0x100000) # so this will include 0xD800 etc raise pos += 2*charsavail else: # Note: this is COMPRESSED (not ASCII!) encoding!!! charsavail = local_min(datalen - pos, charsneed) rawstrg = data[pos:pos+charsavail] # if DEBUG: print "SST CMPRSD: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg) accstrg += unicode(rawstrg, latin_1) pos += charsavail charsgot += charsavail if charsgot == nchars: break datainx += 1 data = datatab[datainx] datalen = len(data) options = local_BYTES_ORD(data[0]) pos = 1 if rtcount: runs = [] for runindex in xrange(rtcount): if pos == datalen: pos = 0 datainx += 1 data = datatab[datainx] datalen = len(data) runs.append(local_unpack("= datalen: # adjust to correct position in next record pos = pos - datalen datainx += 1 if datainx < ndatas: data = datatab[datainx] datalen = len(data) else: assert _unused_i == nstrings - 1 strappend(accstrg) return strings, richtext_runs