book.py 56 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474
  1. # Copyright (c) 2005-2012 Stephen John Machin, Lingfo Pty Ltd
  2. # This module is part of the xlrd package, which is released under a
  3. # BSD-style licence.
  4. from __future__ import print_function
  5. import struct
  6. from . import compdoc, formatting, sheet
  7. from .biffh import *
  8. from .formula import *
  9. from .timemachine import *
  10. try:
  11. from time import perf_counter
  12. except ImportError:
  13. # Python 2.7
  14. from time import clock as perf_counter
  15. from struct import unpack
  16. empty_cell = sheet.empty_cell # for exposure to the world ...
  17. DEBUG = 0
  18. import mmap
  19. MY_EOF = 0xF00BAAA # not a 16-bit number
  20. SUPBOOK_UNK, SUPBOOK_INTERNAL, SUPBOOK_EXTERNAL, SUPBOOK_ADDIN, SUPBOOK_DDEOLE = range(5)
  21. SUPPORTED_VERSIONS = (80, 70, 50, 45, 40, 30, 21, 20)
  22. _code_from_builtin_name = {
  23. "Consolidate_Area": "\x00",
  24. "Auto_Open": "\x01",
  25. "Auto_Close": "\x02",
  26. "Extract": "\x03",
  27. "Database": "\x04",
  28. "Criteria": "\x05",
  29. "Print_Area": "\x06",
  30. "Print_Titles": "\x07",
  31. "Recorder": "\x08",
  32. "Data_Form": "\x09",
  33. "Auto_Activate": "\x0A",
  34. "Auto_Deactivate": "\x0B",
  35. "Sheet_Title": "\x0C",
  36. "_FilterDatabase": "\x0D",
  37. }
  38. builtin_name_from_code = {}
  39. code_from_builtin_name = {}
  40. for _bin, _bic in _code_from_builtin_name.items():
  41. _bin = UNICODE_LITERAL(_bin)
  42. _bic = UNICODE_LITERAL(_bic)
  43. code_from_builtin_name[_bin] = _bic
  44. builtin_name_from_code[_bic] = _bin
  45. del _bin, _bic, _code_from_builtin_name
  46. def open_workbook_xls(filename=None,
  47. logfile=sys.stdout, verbosity=0, use_mmap=True,
  48. file_contents=None,
  49. encoding_override=None,
  50. formatting_info=False, on_demand=False, ragged_rows=False,
  51. ignore_workbook_corruption=False):
  52. t0 = perf_counter()
  53. bk = Book()
  54. try:
  55. bk.biff2_8_load(
  56. filename=filename, file_contents=file_contents,
  57. logfile=logfile, verbosity=verbosity, use_mmap=use_mmap,
  58. encoding_override=encoding_override,
  59. formatting_info=formatting_info,
  60. on_demand=on_demand,
  61. ragged_rows=ragged_rows,
  62. ignore_workbook_corruption=ignore_workbook_corruption
  63. )
  64. t1 = perf_counter()
  65. bk.load_time_stage_1 = t1 - t0
  66. biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
  67. if not biff_version:
  68. raise XLRDError("Can't determine file's BIFF version")
  69. if biff_version not in SUPPORTED_VERSIONS:
  70. raise XLRDError(
  71. "BIFF version %s is not supported"
  72. % biff_text_from_num[biff_version]
  73. )
  74. bk.biff_version = biff_version
  75. if biff_version <= 40:
  76. # no workbook globals, only 1 worksheet
  77. if on_demand:
  78. fprintf(bk.logfile,
  79. "*** WARNING: on_demand is not supported for this Excel version.\n"
  80. "*** Setting on_demand to False.\n")
  81. bk.on_demand = on_demand = False
  82. bk.fake_globals_get_sheet()
  83. elif biff_version == 45:
  84. # worksheet(s) embedded in global stream
  85. bk.parse_globals()
  86. if on_demand:
  87. fprintf(bk.logfile, "*** WARNING: on_demand is not supported for this Excel version.\n"
  88. "*** Setting on_demand to False.\n")
  89. bk.on_demand = on_demand = False
  90. else:
  91. bk.parse_globals()
  92. bk._sheet_list = [None for sh in bk._sheet_names]
  93. if not on_demand:
  94. bk.get_sheets()
  95. bk.nsheets = len(bk._sheet_list)
  96. if biff_version == 45 and bk.nsheets > 1:
  97. fprintf(
  98. bk.logfile,
  99. "*** WARNING: Excel 4.0 workbook (.XLW) file contains %d worksheets.\n"
  100. "*** Book-level data will be that of the last worksheet.\n",
  101. bk.nsheets
  102. )
  103. t2 = perf_counter()
  104. bk.load_time_stage_2 = t2 - t1
  105. except:
  106. bk.release_resources()
  107. raise
  108. # normal exit
  109. if not on_demand:
  110. bk.release_resources()
  111. return bk
  112. class Name(BaseObject):
  113. """
  114. Information relating to a named reference, formula, macro, etc.
  115. .. note::
  116. Name information is **not** extracted from files older than
  117. Excel 5.0 (``Book.biff_version < 50``)
  118. """
  119. _repr_these = ['stack']
  120. book = None # parent
  121. #: 0 = Visible; 1 = Hidden
  122. hidden = 0
  123. #: 0 = Command macro; 1 = Function macro. Relevant only if macro == 1
  124. func = 0
  125. #: 0 = Sheet macro; 1 = VisualBasic macro. Relevant only if macro == 1
  126. vbasic = 0
  127. #: 0 = Standard name; 1 = Macro name
  128. macro = 0
  129. #: 0 = Simple formula; 1 = Complex formula (array formula or user defined).
  130. #:
  131. #: .. note:: No examples have been sighted.
  132. complex = 0
  133. #: 0 = User-defined name; 1 = Built-in name
  134. #:
  135. #: Common examples: ``Print_Area``, ``Print_Titles``; see OOo docs for
  136. #: full list
  137. builtin = 0
  138. #: Function group. Relevant only if macro == 1; see OOo docs for values.
  139. funcgroup = 0
  140. #: 0 = Formula definition; 1 = Binary data
  141. #:
  142. #: .. note:: No examples have been sighted.
  143. binary = 0
  144. #: The index of this object in book.name_obj_list
  145. name_index = 0
  146. # A Unicode string. If builtin, decoded as per OOo docs.
  147. name = UNICODE_LITERAL("")
  148. #: An 8-bit string.
  149. raw_formula = b''
  150. #: ``-1``:
  151. #: The name is global (visible in all calculation sheets).
  152. #: ``-2``:
  153. #: The name belongs to a macro sheet or VBA sheet.
  154. #: ``-3``:
  155. #: The name is invalid.
  156. #: ``0 <= scope < book.nsheets``:
  157. #: The name is local to the sheet whose index is scope.
  158. scope = -1
  159. #: The result of evaluating the formula, if any.
  160. #: If no formula, or evaluation of the formula encountered problems,
  161. #: the result is ``None``. Otherwise the result is a single instance of the
  162. #: :class:`~xlrd.formula.Operand` class.
  163. #
  164. result = None
  165. def cell(self):
  166. """
  167. This is a convenience method for the frequent use case where the name
  168. refers to a single cell.
  169. :returns: An instance of the :class:`~xlrd.sheet.Cell` class.
  170. :raises xlrd.biffh.XLRDError:
  171. The name is not a constant absolute reference
  172. to a single cell.
  173. """
  174. res = self.result
  175. if res:
  176. # result should be an instance of the Operand class
  177. kind = res.kind
  178. value = res.value
  179. if kind == oREF and len(value) == 1:
  180. ref3d = value[0]
  181. if (0 <= ref3d.shtxlo == ref3d.shtxhi - 1 and
  182. ref3d.rowxlo == ref3d.rowxhi - 1 and
  183. ref3d.colxlo == ref3d.colxhi - 1):
  184. sh = self.book.sheet_by_index(ref3d.shtxlo)
  185. return sh.cell(ref3d.rowxlo, ref3d.colxlo)
  186. self.dump(
  187. self.book.logfile,
  188. header="=== Dump of Name object ===",
  189. footer="======= End of dump =======",
  190. )
  191. raise XLRDError("Not a constant absolute reference to a single cell")
  192. def area2d(self, clipped=True):
  193. """
  194. This is a convenience method for the use case where the name
  195. refers to one rectangular area in one worksheet.
  196. :param clipped:
  197. If ``True``, the default, the returned rectangle is clipped
  198. to fit in ``(0, sheet.nrows, 0, sheet.ncols)``.
  199. it is guaranteed that ``0 <= rowxlo <= rowxhi <= sheet.nrows`` and
  200. that the number of usable rows in the area (which may be zero) is
  201. ``rowxhi - rowxlo``; likewise for columns.
  202. :returns: a tuple ``(sheet_object, rowxlo, rowxhi, colxlo, colxhi)``.
  203. :raises xlrd.biffh.XLRDError:
  204. The name is not a constant absolute reference
  205. to a single area in a single sheet.
  206. """
  207. res = self.result
  208. if res:
  209. # result should be an instance of the Operand class
  210. kind = res.kind
  211. value = res.value
  212. if kind == oREF and len(value) == 1: # only 1 reference
  213. ref3d = value[0]
  214. if 0 <= ref3d.shtxlo == ref3d.shtxhi - 1: # only 1 usable sheet
  215. sh = self.book.sheet_by_index(ref3d.shtxlo)
  216. if not clipped:
  217. return sh, ref3d.rowxlo, ref3d.rowxhi, ref3d.colxlo, ref3d.colxhi
  218. rowxlo = min(ref3d.rowxlo, sh.nrows)
  219. rowxhi = max(rowxlo, min(ref3d.rowxhi, sh.nrows))
  220. colxlo = min(ref3d.colxlo, sh.ncols)
  221. colxhi = max(colxlo, min(ref3d.colxhi, sh.ncols))
  222. assert 0 <= rowxlo <= rowxhi <= sh.nrows
  223. assert 0 <= colxlo <= colxhi <= sh.ncols
  224. return sh, rowxlo, rowxhi, colxlo, colxhi
  225. self.dump(
  226. self.book.logfile,
  227. header="=== Dump of Name object ===",
  228. footer="======= End of dump =======",
  229. )
  230. raise XLRDError("Not a constant absolute reference to a single area in a single sheet")
  231. class Book(BaseObject):
  232. """
  233. Contents of a "workbook".
  234. .. warning::
  235. You should not instantiate this class yourself. You use the :class:`Book`
  236. object that was returned when you called :func:`~xlrd.open_workbook`.
  237. """
  238. #: The number of worksheets present in the workbook file.
  239. #: This information is available even when no sheets have yet been loaded.
  240. nsheets = 0
  241. #: Which date system was in force when this file was last saved.
  242. #:
  243. #: 0:
  244. #: 1900 system (the Excel for Windows default).
  245. #:
  246. #: 1:
  247. #: 1904 system (the Excel for Macintosh default).
  248. #:
  249. #: Defaults to 0 in case it's not specified in the file.
  250. datemode = 0
  251. #: Version of BIFF (Binary Interchange File Format) used to create the file.
  252. #: Latest is 8.0 (represented here as 80), introduced with Excel 97.
  253. #: Earliest supported by this module: 2.0 (represented as 20).
  254. biff_version = 0
  255. #: List containing a :class:`Name` object for each ``NAME`` record in the
  256. #: workbook.
  257. #:
  258. #: .. versionadded:: 0.6.0
  259. name_obj_list = []
  260. #: An integer denoting the character set used for strings in this file.
  261. #: For BIFF 8 and later, this will be 1200, meaning Unicode;
  262. #: more precisely, UTF_16_LE.
  263. #: For earlier versions, this is used to derive the appropriate Python
  264. #: encoding to be used to convert to Unicode.
  265. #: Examples: ``1252 -> 'cp1252'``, ``10000 -> 'mac_roman'``
  266. codepage = None
  267. #: The encoding that was derived from the codepage.
  268. encoding = None
  269. #: A tuple containing the telephone country code for:
  270. #:
  271. #: ``[0]``:
  272. #: the user-interface setting when the file was created.
  273. #:
  274. #: ``[1]``:
  275. #: the regional settings.
  276. #:
  277. #: Example: ``(1, 61)`` meaning ``(USA, Australia)``.
  278. #:
  279. #: This information may give a clue to the correct encoding for an
  280. #: unknown codepage. For a long list of observed values, refer to the
  281. #: OpenOffice.org documentation for the ``COUNTRY`` record.
  282. countries = (0, 0)
  283. #: What (if anything) is recorded as the name of the last user to
  284. #: save the file.
  285. user_name = UNICODE_LITERAL('')
  286. #: A list of :class:`~xlrd.formatting.Font` class instances,
  287. #: each corresponding to a FONT record.
  288. #:
  289. #: .. versionadded:: 0.6.1
  290. font_list = []
  291. #: A list of :class:`~xlrd.formatting.XF` class instances,
  292. #: each corresponding to an ``XF`` record.
  293. #:
  294. #: .. versionadded:: 0.6.1
  295. xf_list = []
  296. #: A list of :class:`~xlrd.formatting.Format` objects, each corresponding to
  297. #: a ``FORMAT`` record, in the order that they appear in the input file.
  298. #: It does *not* contain builtin formats.
  299. #:
  300. #: If you are creating an output file using (for example) :mod:`xlwt`,
  301. #: use this list.
  302. #:
  303. #: The collection to be used for all visual rendering purposes is
  304. #: :attr:`format_map`.
  305. #:
  306. #: .. versionadded:: 0.6.1
  307. format_list = []
  308. ##
  309. #: The mapping from :attr:`~xlrd.formatting.XF.format_key` to
  310. #: :class:`~xlrd.formatting.Format` object.
  311. #:
  312. #: .. versionadded:: 0.6.1
  313. format_map = {}
  314. #: This provides access via name to the extended format information for
  315. #: both built-in styles and user-defined styles.
  316. #:
  317. #: It maps ``name`` to ``(built_in, xf_index)``, where
  318. #: ``name`` is either the name of a user-defined style,
  319. #: or the name of one of the built-in styles. Known built-in names are
  320. #: Normal, RowLevel_1 to RowLevel_7,
  321. #: ColLevel_1 to ColLevel_7, Comma, Currency, Percent, "Comma [0]",
  322. #: "Currency [0]", Hyperlink, and "Followed Hyperlink".
  323. #:
  324. #: ``built_in`` has the following meanings
  325. #:
  326. #: 1:
  327. #: built-in style
  328. #:
  329. #: 0:
  330. #: user-defined
  331. #:
  332. #: ``xf_index`` is an index into :attr:`Book.xf_list`.
  333. #:
  334. #: References: OOo docs s6.99 (``STYLE`` record); Excel UI Format/Style
  335. #:
  336. #: .. versionadded:: 0.6.1
  337. #:
  338. #: Extracted only if ``open_workbook(..., formatting_info=True)``
  339. #:
  340. #: .. versionadded:: 0.7.4
  341. style_name_map = {}
  342. #: This provides definitions for colour indexes. Please refer to
  343. #: :ref:`palette` for an explanation
  344. #: of how colours are represented in Excel.
  345. #:
  346. #: Colour indexes into the palette map into ``(red, green, blue)`` tuples.
  347. #: "Magic" indexes e.g. ``0x7FFF`` map to ``None``.
  348. #:
  349. #: :attr:`colour_map` is what you need if you want to render cells on screen
  350. #: or in a PDF file. If you are writing an output XLS file, use
  351. #: :attr:`palette_record`.
  352. #:
  353. #: .. note:: Extracted only if ``open_workbook(..., formatting_info=True)``
  354. #:
  355. #: .. versionadded:: 0.6.1
  356. colour_map = {}
  357. #: If the user has changed any of the colours in the standard palette, the
  358. #: XLS file will contain a ``PALETTE`` record with 56 (16 for Excel 4.0 and
  359. #: earlier) RGB values in it, and this list will be e.g.
  360. #: ``[(r0, b0, g0), ..., (r55, b55, g55)]``.
  361. #: Otherwise this list will be empty. This is what you need if you are
  362. #: writing an output XLS file. If you want to render cells on screen or in a
  363. #: PDF file, use :attr:`colour_map`.
  364. #:
  365. #: .. note:: Extracted only if ``open_workbook(..., formatting_info=True)``
  366. #:
  367. #: .. versionadded:: 0.6.1
  368. palette_record = []
  369. #: Time in seconds to extract the XLS image as a contiguous string
  370. #: (or mmap equivalent).
  371. load_time_stage_1 = -1.0
  372. #: Time in seconds to parse the data from the contiguous string
  373. #: (or mmap equivalent).
  374. load_time_stage_2 = -1.0
  375. def sheets(self):
  376. """
  377. :returns: A list of all sheets in the book.
  378. All sheets not already loaded will be loaded.
  379. """
  380. for sheetx in xrange(self.nsheets):
  381. if not self._sheet_list[sheetx]:
  382. self.get_sheet(sheetx)
  383. return self._sheet_list[:]
  384. def sheet_by_index(self, sheetx):
  385. """
  386. :param sheetx: Sheet index in ``range(nsheets)``
  387. :returns: A :class:`~xlrd.sheet.Sheet`.
  388. """
  389. return self._sheet_list[sheetx] or self.get_sheet(sheetx)
  390. def __iter__(self):
  391. """
  392. Makes iteration through sheets of a book a little more straightforward.
  393. Don't free resources after use since it can be called like `list(book)`
  394. """
  395. for i in range(self.nsheets):
  396. yield self.sheet_by_index(i)
  397. def sheet_by_name(self, sheet_name):
  398. """
  399. :param sheet_name: Name of the sheet required.
  400. :returns: A :class:`~xlrd.sheet.Sheet`.
  401. """
  402. try:
  403. sheetx = self._sheet_names.index(sheet_name)
  404. except ValueError:
  405. raise XLRDError('No sheet named <%r>' % sheet_name)
  406. return self.sheet_by_index(sheetx)
  407. def __getitem__(self, item):
  408. """
  409. Allow indexing with sheet name or index.
  410. :param item: Name or index of sheet enquired upon
  411. :return: :class:`~xlrd.sheet.Sheet`.
  412. """
  413. if isinstance(item, int):
  414. return self.sheet_by_index(item)
  415. else:
  416. return self.sheet_by_name(item)
  417. def sheet_names(self):
  418. """
  419. :returns:
  420. A list of the names of all the worksheets in the workbook file.
  421. This information is available even when no sheets have yet been
  422. loaded.
  423. """
  424. return self._sheet_names[:]
  425. def sheet_loaded(self, sheet_name_or_index):
  426. """
  427. :param sheet_name_or_index: Name or index of sheet enquired upon
  428. :returns: ``True`` if sheet is loaded, ``False`` otherwise.
  429. .. versionadded:: 0.7.1
  430. """
  431. if isinstance(sheet_name_or_index, int):
  432. sheetx = sheet_name_or_index
  433. else:
  434. try:
  435. sheetx = self._sheet_names.index(sheet_name_or_index)
  436. except ValueError:
  437. raise XLRDError('No sheet named <%r>' % sheet_name_or_index)
  438. return bool(self._sheet_list[sheetx])
  439. def unload_sheet(self, sheet_name_or_index):
  440. """
  441. :param sheet_name_or_index: Name or index of sheet to be unloaded.
  442. .. versionadded:: 0.7.1
  443. """
  444. if isinstance(sheet_name_or_index, int):
  445. sheetx = sheet_name_or_index
  446. else:
  447. try:
  448. sheetx = self._sheet_names.index(sheet_name_or_index)
  449. except ValueError:
  450. raise XLRDError('No sheet named <%r>' % sheet_name_or_index)
  451. self._sheet_list[sheetx] = None
  452. def release_resources(self):
  453. """
  454. This method has a dual purpose. You can call it to release
  455. memory-consuming objects and (possibly) a memory-mapped file
  456. (:class:`mmap.mmap` object) when you have finished loading sheets in
  457. ``on_demand`` mode, but still require the :class:`Book` object to
  458. examine the loaded sheets. It is also called automatically (a) when
  459. :func:`~xlrd.open_workbook`
  460. raises an exception and (b) if you are using a ``with`` statement, when
  461. the ``with`` block is exited. Calling this method multiple times on the
  462. same object has no ill effect.
  463. """
  464. self._resources_released = 1
  465. if hasattr(self.mem, "close"):
  466. # must be a mmap.mmap object
  467. self.mem.close()
  468. self.mem = None
  469. if hasattr(self.filestr, "close"):
  470. self.filestr.close()
  471. self.filestr = None
  472. self._sharedstrings = None
  473. self._rich_text_runlist_map = None
  474. def __enter__(self):
  475. return self
  476. def __exit__(self, exc_type, exc_value, exc_tb):
  477. self.release_resources()
  478. # return false
  479. #: A mapping from ``(lower_case_name, scope)`` to a single :class:`Name`
  480. #: object.
  481. #:
  482. #: .. versionadded:: 0.6.0
  483. name_and_scope_map = {}
  484. #: A mapping from `lower_case_name` to a list of :class:`Name` objects.
  485. #: The list is sorted in scope order. Typically there will be one item
  486. #: (of global scope) in the list.
  487. #:
  488. #: .. versionadded:: 0.6.0
  489. name_map = {}
  490. def __init__(self):
  491. self._sheet_list = []
  492. self._sheet_names = []
  493. self._sheet_visibility = [] # from BOUNDSHEET record
  494. self.nsheets = 0
  495. self._sh_abs_posn = [] # sheet's absolute position in the stream
  496. self._sharedstrings = []
  497. self._rich_text_runlist_map = {}
  498. self.raw_user_name = False
  499. self._sheethdr_count = 0 # BIFF 4W only
  500. self.builtinfmtcount = -1 # unknown as yet. BIFF 3, 4S, 4W
  501. self.initialise_format_info()
  502. self._all_sheets_count = 0 # includes macro & VBA sheets
  503. self._supbook_count = 0
  504. self._supbook_locals_inx = None
  505. self._supbook_addins_inx = None
  506. self._all_sheets_map = [] # maps an all_sheets index to a calc-sheets index (or -1)
  507. self._externsheet_info = []
  508. self._externsheet_type_b57 = []
  509. self._extnsht_name_from_num = {}
  510. self._sheet_num_from_name = {}
  511. self._extnsht_count = 0
  512. self._supbook_types = []
  513. self._resources_released = 0
  514. self.addin_func_names = []
  515. self.name_obj_list = []
  516. self.colour_map = {}
  517. self.palette_record = []
  518. self.xf_list = []
  519. self.style_name_map = {}
  520. self.mem = b''
  521. self.filestr = b''
  522. def biff2_8_load(self, filename=None, file_contents=None,
  523. logfile=sys.stdout, verbosity=0, use_mmap=True,
  524. encoding_override=None,
  525. formatting_info=False,
  526. on_demand=False,
  527. ragged_rows=False,
  528. ignore_workbook_corruption=False
  529. ):
  530. # DEBUG = 0
  531. self.logfile = logfile
  532. self.verbosity = verbosity
  533. self.use_mmap = use_mmap
  534. self.encoding_override = encoding_override
  535. self.formatting_info = formatting_info
  536. self.on_demand = on_demand
  537. self.ragged_rows = ragged_rows
  538. if not file_contents:
  539. with open(filename, "rb") as f:
  540. f.seek(0, 2) # EOF
  541. size = f.tell()
  542. f.seek(0, 0) # BOF
  543. if size == 0:
  544. raise XLRDError("File size is 0 bytes")
  545. if self.use_mmap:
  546. self.filestr = mmap.mmap(f.fileno(), size, access=mmap.ACCESS_READ)
  547. self.stream_len = size
  548. else:
  549. self.filestr = f.read()
  550. self.stream_len = len(self.filestr)
  551. else:
  552. self.filestr = file_contents
  553. self.stream_len = len(file_contents)
  554. self.base = 0
  555. if self.filestr[:8] != compdoc.SIGNATURE:
  556. # got this one at the antique store
  557. self.mem = self.filestr
  558. else:
  559. cd = compdoc.CompDoc(self.filestr, logfile=self.logfile,
  560. ignore_workbook_corruption=ignore_workbook_corruption)
  561. for qname in ['Workbook', 'Book']:
  562. self.mem, self.base, self.stream_len = \
  563. cd.locate_named_stream(UNICODE_LITERAL(qname))
  564. if self.mem:
  565. break
  566. else:
  567. raise XLRDError("Can't find workbook in OLE2 compound document")
  568. del cd
  569. if self.mem is not self.filestr:
  570. if hasattr(self.filestr, "close"):
  571. self.filestr.close()
  572. self.filestr = b''
  573. self._position = self.base
  574. if DEBUG:
  575. print("mem: %s, base: %d, len: %d" % (type(self.mem), self.base, self.stream_len), file=self.logfile)
  576. def initialise_format_info(self):
  577. # needs to be done once per sheet for BIFF 4W :-(
  578. self.format_map = {}
  579. self.format_list = []
  580. self.xfcount = 0
  581. self.actualfmtcount = 0 # number of FORMAT records seen so far
  582. self._xf_index_to_xl_type_map = {0: XL_CELL_NUMBER}
  583. self._xf_epilogue_done = 0
  584. self.xf_list = []
  585. self.font_list = []
  586. def get2bytes(self):
  587. pos = self._position
  588. buff_two = self.mem[pos:pos+2]
  589. lenbuff = len(buff_two)
  590. self._position += lenbuff
  591. if lenbuff < 2:
  592. return MY_EOF
  593. lo, hi = buff_two
  594. return (BYTES_ORD(hi) << 8) | BYTES_ORD(lo)
  595. def get_record_parts(self):
  596. pos = self._position
  597. mem = self.mem
  598. code, length = unpack('<HH', mem[pos:pos+4])
  599. pos += 4
  600. data = mem[pos:pos+length]
  601. self._position = pos + length
  602. return (code, length, data)
  603. def get_record_parts_conditional(self, reqd_record):
  604. pos = self._position
  605. mem = self.mem
  606. code, length = unpack('<HH', mem[pos:pos+4])
  607. if code != reqd_record:
  608. return (None, 0, b'')
  609. pos += 4
  610. data = mem[pos:pos+length]
  611. self._position = pos + length
  612. return (code, length, data)
  613. def get_sheet(self, sh_number, update_pos=True):
  614. if self._resources_released:
  615. raise XLRDError("Can't load sheets after releasing resources.")
  616. if update_pos:
  617. self._position = self._sh_abs_posn[sh_number]
  618. self.getbof(XL_WORKSHEET)
  619. # assert biff_version == self.biff_version ### FAILS
  620. # Have an example where book is v7 but sheet reports v8!!!
  621. # It appears to work OK if the sheet version is ignored.
  622. # Confirmed by Daniel Rentz: happens when Excel does "save as"
  623. # creating an old version file; ignore version details on sheet BOF.
  624. sh = sheet.Sheet(
  625. self,
  626. self._position,
  627. self._sheet_names[sh_number],
  628. sh_number,
  629. )
  630. sh.read(self)
  631. self._sheet_list[sh_number] = sh
  632. return sh
  633. def get_sheets(self):
  634. # DEBUG = 0
  635. if DEBUG: print("GET_SHEETS:", self._sheet_names, self._sh_abs_posn, file=self.logfile)
  636. for sheetno in xrange(len(self._sheet_names)):
  637. if DEBUG: print("GET_SHEETS: sheetno =", sheetno, self._sheet_names, self._sh_abs_posn, file=self.logfile)
  638. self.get_sheet(sheetno)
  639. def fake_globals_get_sheet(self): # for BIFF 4.0 and earlier
  640. formatting.initialise_book(self)
  641. fake_sheet_name = UNICODE_LITERAL('Sheet 1')
  642. self._sheet_names = [fake_sheet_name]
  643. self._sh_abs_posn = [0]
  644. self._sheet_visibility = [0] # one sheet, visible
  645. self._sheet_list.append(None) # get_sheet updates _sheet_list but needs a None beforehand
  646. self.get_sheets()
  647. def handle_boundsheet(self, data):
  648. # DEBUG = 1
  649. bv = self.biff_version
  650. self.derive_encoding()
  651. if DEBUG:
  652. fprintf(self.logfile, "BOUNDSHEET: bv=%d data %r\n", bv, data)
  653. if bv == 45: # BIFF4W
  654. #### Not documented in OOo docs ...
  655. # In fact, the *only* data is the name of the sheet.
  656. sheet_name = unpack_string(data, 0, self.encoding, lenlen=1)
  657. visibility = 0
  658. sheet_type = XL_BOUNDSHEET_WORKSHEET # guess, patch later
  659. if len(self._sh_abs_posn) == 0:
  660. abs_posn = self._sheetsoffset + self.base
  661. # Note (a) this won't be used
  662. # (b) it's the position of the SHEETHDR record
  663. # (c) add 11 to get to the worksheet BOF record
  664. else:
  665. abs_posn = -1 # unknown
  666. else:
  667. offset, visibility, sheet_type = unpack('<iBB', data[0:6])
  668. abs_posn = offset + self.base # because global BOF is always at posn 0 in the stream
  669. if bv < BIFF_FIRST_UNICODE:
  670. sheet_name = unpack_string(data, 6, self.encoding, lenlen=1)
  671. else:
  672. sheet_name = unpack_unicode(data, 6, lenlen=1)
  673. if DEBUG or self.verbosity >= 2:
  674. fprintf(self.logfile,
  675. "BOUNDSHEET: inx=%d vis=%r sheet_name=%r abs_posn=%d sheet_type=0x%02x\n",
  676. self._all_sheets_count, visibility, sheet_name, abs_posn, sheet_type)
  677. self._all_sheets_count += 1
  678. if sheet_type != XL_BOUNDSHEET_WORKSHEET:
  679. self._all_sheets_map.append(-1)
  680. descr = {
  681. 1: 'Macro sheet',
  682. 2: 'Chart',
  683. 6: 'Visual Basic module',
  684. }.get(sheet_type, 'UNKNOWN')
  685. if DEBUG or self.verbosity >= 1:
  686. fprintf(self.logfile,
  687. "NOTE *** Ignoring non-worksheet data named %r (type 0x%02x = %s)\n",
  688. sheet_name, sheet_type, descr)
  689. else:
  690. snum = len(self._sheet_names)
  691. self._all_sheets_map.append(snum)
  692. self._sheet_names.append(sheet_name)
  693. self._sh_abs_posn.append(abs_posn)
  694. self._sheet_visibility.append(visibility)
  695. self._sheet_num_from_name[sheet_name] = snum
  696. def handle_builtinfmtcount(self, data):
  697. ### N.B. This count appears to be utterly useless.
  698. # DEBUG = 1
  699. builtinfmtcount = unpack('<H', data[0:2])[0]
  700. if DEBUG: fprintf(self.logfile, "BUILTINFMTCOUNT: %r\n", builtinfmtcount)
  701. self.builtinfmtcount = builtinfmtcount
  702. def derive_encoding(self):
  703. if self.encoding_override:
  704. self.encoding = self.encoding_override
  705. elif self.codepage is None:
  706. if self.biff_version < 80:
  707. fprintf(self.logfile,
  708. "*** No CODEPAGE record, no encoding_override: will use 'iso-8859-1'\n")
  709. self.encoding = 'iso-8859-1'
  710. else:
  711. self.codepage = 1200 # utf16le
  712. if self.verbosity >= 2:
  713. fprintf(self.logfile, "*** No CODEPAGE record; assuming 1200 (utf_16_le)\n")
  714. else:
  715. codepage = self.codepage
  716. if codepage in encoding_from_codepage:
  717. encoding = encoding_from_codepage[codepage]
  718. elif 300 <= codepage <= 1999:
  719. encoding = 'cp' + str(codepage)
  720. elif self.biff_version >= 80:
  721. self.codepage = 1200
  722. encoding = 'utf_16_le'
  723. else:
  724. encoding = 'unknown_codepage_' + str(codepage)
  725. if DEBUG or (self.verbosity and encoding != self.encoding) :
  726. fprintf(self.logfile, "CODEPAGE: codepage %r -> encoding %r\n", codepage, encoding)
  727. self.encoding = encoding
  728. if self.codepage != 1200: # utf_16_le
  729. # If we don't have a codec that can decode ASCII into Unicode,
  730. # we're well & truly stuffed -- let the punter know ASAP.
  731. try:
  732. unicode(b'trial', self.encoding)
  733. except BaseException as e:
  734. fprintf(self.logfile,
  735. "ERROR *** codepage %r -> encoding %r -> %s: %s\n",
  736. self.codepage, self.encoding, type(e).__name__.split(".")[-1], e)
  737. raise
  738. if self.raw_user_name:
  739. strg = unpack_string(self.user_name, 0, self.encoding, lenlen=1)
  740. strg = strg.rstrip()
  741. # if DEBUG:
  742. # print "CODEPAGE: user name decoded from %r to %r" % (self.user_name, strg)
  743. self.user_name = strg
  744. self.raw_user_name = False
  745. return self.encoding
  746. def handle_codepage(self, data):
  747. # DEBUG = 0
  748. codepage = unpack('<H', data[0:2])[0]
  749. self.codepage = codepage
  750. self.derive_encoding()
  751. def handle_country(self, data):
  752. countries = unpack('<HH', data[0:4])
  753. if self.verbosity: print("Countries:", countries, file=self.logfile)
  754. # Note: in BIFF7 and earlier, country record was put (redundantly?) in each worksheet.
  755. assert self.countries == (0, 0) or self.countries == countries
  756. self.countries = countries
  757. def handle_datemode(self, data):
  758. datemode = unpack('<H', data[0:2])[0]
  759. if DEBUG or self.verbosity:
  760. fprintf(self.logfile, "DATEMODE: datemode %r\n", datemode)
  761. assert datemode in (0, 1)
  762. self.datemode = datemode
  763. def handle_externname(self, data):
  764. blah = DEBUG or self.verbosity >= 2
  765. if self.biff_version >= 80:
  766. option_flags, other_info =unpack("<HI", data[:6])
  767. pos = 6
  768. name, pos = unpack_unicode_update_pos(data, pos, lenlen=1)
  769. extra = data[pos:]
  770. if self._supbook_types[-1] == SUPBOOK_ADDIN:
  771. self.addin_func_names.append(name)
  772. if blah:
  773. fprintf(self.logfile,
  774. "EXTERNNAME: sbktype=%d oflags=0x%04x oinfo=0x%08x name=%r extra=%r\n",
  775. self._supbook_types[-1], option_flags, other_info, name, extra)
  776. def handle_externsheet(self, data):
  777. self.derive_encoding() # in case CODEPAGE record missing/out of order/wrong
  778. self._extnsht_count += 1 # for use as a 1-based index
  779. blah1 = DEBUG or self.verbosity >= 1
  780. blah2 = DEBUG or self.verbosity >= 2
  781. if self.biff_version >= 80:
  782. num_refs = unpack("<H", data[0:2])[0]
  783. bytes_reqd = num_refs * 6 + 2
  784. while len(data) < bytes_reqd:
  785. if blah1:
  786. fprintf(
  787. self.logfile,
  788. "INFO: EXTERNSHEET needs %d bytes, have %d\n",
  789. bytes_reqd, len(data),
  790. )
  791. code2, length2, data2 = self.get_record_parts()
  792. if code2 != XL_CONTINUE:
  793. raise XLRDError("Missing CONTINUE after EXTERNSHEET record")
  794. data += data2
  795. pos = 2
  796. for k in xrange(num_refs):
  797. info = unpack("<HHH", data[pos:pos+6])
  798. ref_recordx, ref_first_sheetx, ref_last_sheetx = info
  799. self._externsheet_info.append(info)
  800. pos += 6
  801. if blah2:
  802. fprintf(
  803. self.logfile,
  804. "EXTERNSHEET(b8): k = %2d, record = %2d, first_sheet = %5d, last sheet = %5d\n",
  805. k, ref_recordx, ref_first_sheetx, ref_last_sheetx,
  806. )
  807. else:
  808. nc, ty = unpack("<BB", data[:2])
  809. if blah2:
  810. print("EXTERNSHEET(b7-):", file=self.logfile)
  811. hex_char_dump(data, 0, len(data), fout=self.logfile)
  812. msg = {
  813. 1: "Encoded URL",
  814. 2: "Current sheet!!",
  815. 3: "Specific sheet in own doc't",
  816. 4: "Nonspecific sheet in own doc't!!",
  817. }.get(ty, "Not encoded")
  818. print(" %3d chars, type is %d (%s)" % (nc, ty, msg), file=self.logfile)
  819. if ty == 3:
  820. sheet_name = unicode(data[2:nc+2], self.encoding)
  821. self._extnsht_name_from_num[self._extnsht_count] = sheet_name
  822. if blah2: print(self._extnsht_name_from_num, file=self.logfile)
  823. if not (1 <= ty <= 4):
  824. ty = 0
  825. self._externsheet_type_b57.append(ty)
  826. def handle_filepass(self, data):
  827. if self.verbosity >= 2:
  828. logf = self.logfile
  829. fprintf(logf, "FILEPASS:\n")
  830. hex_char_dump(data, 0, len(data), base=0, fout=logf)
  831. if self.biff_version >= 80:
  832. kind1, = unpack('<H', data[:2])
  833. if kind1 == 0: # weak XOR encryption
  834. key, hash_value = unpack('<HH', data[2:])
  835. fprintf(logf,
  836. 'weak XOR: key=0x%04x hash=0x%04x\n',
  837. key, hash_value)
  838. elif kind1 == 1:
  839. kind2, = unpack('<H', data[4:6])
  840. if kind2 == 1: # BIFF8 standard encryption
  841. caption = "BIFF8 std"
  842. elif kind2 == 2:
  843. caption = "BIFF8 strong"
  844. else:
  845. caption = "** UNKNOWN ENCRYPTION METHOD **"
  846. fprintf(logf, "%s\n", caption)
  847. raise XLRDError("Workbook is encrypted")
  848. def handle_name(self, data):
  849. blah = DEBUG or self.verbosity >= 2
  850. bv = self.biff_version
  851. if bv < 50:
  852. return
  853. self.derive_encoding()
  854. # print
  855. # hex_char_dump(data, 0, len(data), fout=self.logfile)
  856. (
  857. option_flags, kb_shortcut, name_len, fmla_len, extsht_index, sheet_index,
  858. menu_text_len, description_text_len, help_topic_text_len, status_bar_text_len,
  859. ) = unpack("<HBBHHH4B", data[0:14])
  860. nobj = Name()
  861. nobj.book = self ### CIRCULAR ###
  862. name_index = len(self.name_obj_list)
  863. nobj.name_index = name_index
  864. self.name_obj_list.append(nobj)
  865. nobj.option_flags = option_flags
  866. attrs = [
  867. ('hidden', 1, 0),
  868. ('func', 2, 1),
  869. ('vbasic', 4, 2),
  870. ('macro', 8, 3),
  871. ('complex', 0x10, 4),
  872. ('builtin', 0x20, 5),
  873. ('funcgroup', 0xFC0, 6),
  874. ('binary', 0x1000, 12),
  875. ]
  876. for attr, mask, nshift in attrs:
  877. setattr(nobj, attr, (option_flags & mask) >> nshift)
  878. macro_flag = " M"[nobj.macro]
  879. if bv < 80:
  880. internal_name, pos = unpack_string_update_pos(data, 14, self.encoding, known_len=name_len)
  881. else:
  882. internal_name, pos = unpack_unicode_update_pos(data, 14, known_len=name_len)
  883. nobj.extn_sheet_num = extsht_index
  884. nobj.excel_sheet_index = sheet_index
  885. nobj.scope = None # patched up in the names_epilogue() method
  886. if blah:
  887. fprintf(
  888. self.logfile,
  889. "NAME[%d]:%s oflags=%d, name_len=%d, fmla_len=%d, extsht_index=%d, sheet_index=%d, name=%r\n",
  890. name_index, macro_flag, option_flags, name_len,
  891. fmla_len, extsht_index, sheet_index, internal_name)
  892. name = internal_name
  893. if nobj.builtin:
  894. name = builtin_name_from_code.get(name, "??Unknown??")
  895. if blah: print(" builtin: %s" % name, file=self.logfile)
  896. nobj.name = name
  897. nobj.raw_formula = data[pos:]
  898. nobj.basic_formula_len = fmla_len
  899. nobj.evaluated = 0
  900. if blah:
  901. nobj.dump(
  902. self.logfile,
  903. header="--- handle_name: name[%d] ---" % name_index,
  904. footer="-------------------",
  905. )
  906. def names_epilogue(self):
  907. blah = self.verbosity >= 2
  908. f = self.logfile
  909. if blah:
  910. print("+++++ names_epilogue +++++", file=f)
  911. print("_all_sheets_map", REPR(self._all_sheets_map), file=f)
  912. print("_extnsht_name_from_num", REPR(self._extnsht_name_from_num), file=f)
  913. print("_sheet_num_from_name", REPR(self._sheet_num_from_name), file=f)
  914. num_names = len(self.name_obj_list)
  915. for namex in range(num_names):
  916. nobj = self.name_obj_list[namex]
  917. # Convert from excel_sheet_index to scope.
  918. # This is done here because in BIFF7 and earlier, the
  919. # BOUNDSHEET records (from which _all_sheets_map is derived)
  920. # come after the NAME records.
  921. if self.biff_version >= 80:
  922. sheet_index = nobj.excel_sheet_index
  923. if sheet_index == 0:
  924. intl_sheet_index = -1 # global
  925. elif 1 <= sheet_index <= len(self._all_sheets_map):
  926. intl_sheet_index = self._all_sheets_map[sheet_index-1]
  927. if intl_sheet_index == -1: # maps to a macro or VBA sheet
  928. intl_sheet_index = -2 # valid sheet reference but not useful
  929. else:
  930. # huh?
  931. intl_sheet_index = -3 # invalid
  932. elif 50 <= self.biff_version <= 70:
  933. sheet_index = nobj.extn_sheet_num
  934. if sheet_index == 0:
  935. intl_sheet_index = -1 # global
  936. else:
  937. sheet_name = self._extnsht_name_from_num[sheet_index]
  938. intl_sheet_index = self._sheet_num_from_name.get(sheet_name, -2)
  939. nobj.scope = intl_sheet_index
  940. for namex in range(num_names):
  941. nobj = self.name_obj_list[namex]
  942. # Parse the formula ...
  943. if nobj.macro or nobj.binary: continue
  944. if nobj.evaluated: continue
  945. evaluate_name_formula(self, nobj, namex, blah=blah)
  946. if self.verbosity >= 2:
  947. print("---------- name object dump ----------", file=f)
  948. for namex in range(num_names):
  949. nobj = self.name_obj_list[namex]
  950. nobj.dump(f, header="--- name[%d] ---" % namex)
  951. print("--------------------------------------", file=f)
  952. #
  953. # Build some dicts for access to the name objects
  954. #
  955. name_and_scope_map = {} # (name.lower(), scope): Name_object
  956. name_map = {} # name.lower() : list of Name_objects (sorted in scope order)
  957. for namex in range(num_names):
  958. nobj = self.name_obj_list[namex]
  959. name_lcase = nobj.name.lower()
  960. key = (name_lcase, nobj.scope)
  961. if key in name_and_scope_map and self.verbosity:
  962. fprintf(f, 'Duplicate entry %r in name_and_scope_map\n', key)
  963. name_and_scope_map[key] = nobj
  964. sort_data = (nobj.scope, namex, nobj)
  965. # namex (a temp unique ID) ensures the Name objects will not
  966. # be compared (fatal in py3)
  967. if name_lcase in name_map:
  968. name_map[name_lcase].append(sort_data)
  969. else:
  970. name_map[name_lcase] = [sort_data]
  971. for key in name_map.keys():
  972. alist = name_map[key]
  973. alist.sort()
  974. name_map[key] = [x[2] for x in alist]
  975. self.name_and_scope_map = name_and_scope_map
  976. self.name_map = name_map
  977. def handle_obj(self, data):
  978. # Not doing much handling at all.
  979. # Worrying about embedded (BOF ... EOF) substreams is done elsewhere.
  980. # DEBUG = 1
  981. obj_type, obj_id = unpack('<HI', data[4:10])
  982. # if DEBUG: print "---> handle_obj type=%d id=0x%08x" % (obj_type, obj_id)
  983. def handle_supbook(self, data):
  984. # aka EXTERNALBOOK in OOo docs
  985. self._supbook_types.append(None)
  986. blah = DEBUG or self.verbosity >= 2
  987. if blah:
  988. print("SUPBOOK:", file=self.logfile)
  989. hex_char_dump(data, 0, len(data), fout=self.logfile)
  990. num_sheets = unpack("<H", data[0:2])[0]
  991. if blah: print("num_sheets = %d" % num_sheets, file=self.logfile)
  992. sbn = self._supbook_count
  993. self._supbook_count += 1
  994. if data[2:4] == b"\x01\x04":
  995. self._supbook_types[-1] = SUPBOOK_INTERNAL
  996. self._supbook_locals_inx = self._supbook_count - 1
  997. if blah:
  998. print("SUPBOOK[%d]: internal 3D refs; %d sheets" % (sbn, num_sheets), file=self.logfile)
  999. print(" _all_sheets_map", self._all_sheets_map, file=self.logfile)
  1000. return
  1001. if data[0:4] == b"\x01\x00\x01\x3A":
  1002. self._supbook_types[-1] = SUPBOOK_ADDIN
  1003. self._supbook_addins_inx = self._supbook_count - 1
  1004. if blah: print("SUPBOOK[%d]: add-in functions" % sbn, file=self.logfile)
  1005. return
  1006. url, pos = unpack_unicode_update_pos(data, 2, lenlen=2)
  1007. if num_sheets == 0:
  1008. self._supbook_types[-1] = SUPBOOK_DDEOLE
  1009. if blah: fprintf(self.logfile, "SUPBOOK[%d]: DDE/OLE document = %r\n", sbn, url)
  1010. return
  1011. self._supbook_types[-1] = SUPBOOK_EXTERNAL
  1012. if blah: fprintf(self.logfile, "SUPBOOK[%d]: url = %r\n", sbn, url)
  1013. sheet_names = []
  1014. for x in range(num_sheets):
  1015. try:
  1016. shname, pos = unpack_unicode_update_pos(data, pos, lenlen=2)
  1017. except struct.error:
  1018. # #### FIX ME ####
  1019. # Should implement handling of CONTINUE record(s) ...
  1020. if self.verbosity:
  1021. print(
  1022. "*** WARNING: unpack failure in sheet %d of %d in SUPBOOK record for file %r"
  1023. % (x, num_sheets, url),
  1024. file=self.logfile,
  1025. )
  1026. break
  1027. sheet_names.append(shname)
  1028. if blah: fprintf(self.logfile, " sheetx=%d namelen=%d name=%r (next pos=%d)\n", x, len(shname), shname, pos)
  1029. def handle_sheethdr(self, data):
  1030. # This a BIFF 4W special.
  1031. # The SHEETHDR record is followed by a (BOF ... EOF) substream containing
  1032. # a worksheet.
  1033. # DEBUG = 1
  1034. self.derive_encoding()
  1035. sheet_len = unpack('<i', data[:4])[0]
  1036. sheet_name = unpack_string(data, 4, self.encoding, lenlen=1)
  1037. sheetno = self._sheethdr_count
  1038. assert sheet_name == self._sheet_names[sheetno]
  1039. self._sheethdr_count += 1
  1040. BOF_posn = self._position
  1041. posn = BOF_posn - 4 - len(data)
  1042. if DEBUG: fprintf(self.logfile, 'SHEETHDR %d at posn %d: len=%d name=%r\n', sheetno, posn, sheet_len, sheet_name)
  1043. self.initialise_format_info()
  1044. if DEBUG: print('SHEETHDR: xf epilogue flag is %d' % self._xf_epilogue_done, file=self.logfile)
  1045. self._sheet_list.append(None) # get_sheet updates _sheet_list but needs a None beforehand
  1046. self.get_sheet(sheetno, update_pos=False)
  1047. if DEBUG: print('SHEETHDR: posn after get_sheet() =', self._position, file=self.logfile)
  1048. self._position = BOF_posn + sheet_len
  1049. def handle_sheetsoffset(self, data):
  1050. # DEBUG = 0
  1051. posn = unpack('<i', data)[0]
  1052. if DEBUG: print('SHEETSOFFSET:', posn, file=self.logfile)
  1053. self._sheetsoffset = posn
  1054. def handle_sst(self, data):
  1055. # DEBUG = 1
  1056. if DEBUG:
  1057. print("SST Processing", file=self.logfile)
  1058. t0 = perf_counter()
  1059. nbt = len(data)
  1060. strlist = [data]
  1061. uniquestrings = unpack('<i', data[4:8])[0]
  1062. if DEBUG or self.verbosity >= 2:
  1063. fprintf(self.logfile, "SST: unique strings: %d\n", uniquestrings)
  1064. while 1:
  1065. code, nb, data = self.get_record_parts_conditional(XL_CONTINUE)
  1066. if code is None:
  1067. break
  1068. nbt += nb
  1069. if DEBUG >= 2:
  1070. fprintf(self.logfile, "CONTINUE: adding %d bytes to SST -> %d\n", nb, nbt)
  1071. strlist.append(data)
  1072. self._sharedstrings, rt_runlist = unpack_SST_table(strlist, uniquestrings)
  1073. if self.formatting_info:
  1074. self._rich_text_runlist_map = rt_runlist
  1075. if DEBUG:
  1076. t1 = perf_counter()
  1077. print("SST processing took %.2f seconds" % (t1 - t0, ), file=self.logfile)
  1078. def handle_writeaccess(self, data):
  1079. DEBUG = 0
  1080. if self.biff_version < 80:
  1081. if not self.encoding:
  1082. self.raw_user_name = True
  1083. self.user_name = data
  1084. return
  1085. strg = unpack_string(data, 0, self.encoding, lenlen=1)
  1086. else:
  1087. try:
  1088. strg = unpack_unicode(data, 0, lenlen=2)
  1089. except UnicodeDecodeError:
  1090. # may have invalid trailing characters
  1091. strg = unpack_unicode(data.strip(), 0, lenlen=2)
  1092. if DEBUG: fprintf(self.logfile, "WRITEACCESS: %d bytes; raw=%s %r\n", len(data), self.raw_user_name, strg)
  1093. strg = strg.rstrip()
  1094. self.user_name = strg
  1095. def parse_globals(self):
  1096. # DEBUG = 0
  1097. # no need to position, just start reading (after the BOF)
  1098. formatting.initialise_book(self)
  1099. while 1:
  1100. rc, length, data = self.get_record_parts()
  1101. if DEBUG: print("parse_globals: record code is 0x%04x" % rc, file=self.logfile)
  1102. if rc == XL_SST:
  1103. self.handle_sst(data)
  1104. elif rc == XL_FONT or rc == XL_FONT_B3B4:
  1105. self.handle_font(data)
  1106. elif rc == XL_FORMAT: # XL_FORMAT2 is BIFF <= 3.0, can't appear in globals
  1107. self.handle_format(data)
  1108. elif rc == XL_XF:
  1109. self.handle_xf(data)
  1110. elif rc == XL_BOUNDSHEET:
  1111. self.handle_boundsheet(data)
  1112. elif rc == XL_DATEMODE:
  1113. self.handle_datemode(data)
  1114. elif rc == XL_CODEPAGE:
  1115. self.handle_codepage(data)
  1116. elif rc == XL_COUNTRY:
  1117. self.handle_country(data)
  1118. elif rc == XL_EXTERNNAME:
  1119. self.handle_externname(data)
  1120. elif rc == XL_EXTERNSHEET:
  1121. self.handle_externsheet(data)
  1122. elif rc == XL_FILEPASS:
  1123. self.handle_filepass(data)
  1124. elif rc == XL_WRITEACCESS:
  1125. self.handle_writeaccess(data)
  1126. elif rc == XL_SHEETSOFFSET:
  1127. self.handle_sheetsoffset(data)
  1128. elif rc == XL_SHEETHDR:
  1129. self.handle_sheethdr(data)
  1130. elif rc == XL_SUPBOOK:
  1131. self.handle_supbook(data)
  1132. elif rc == XL_NAME:
  1133. self.handle_name(data)
  1134. elif rc == XL_PALETTE:
  1135. self.handle_palette(data)
  1136. elif rc == XL_STYLE:
  1137. self.handle_style(data)
  1138. elif rc & 0xff == 9 and self.verbosity:
  1139. fprintf(self.logfile, "*** Unexpected BOF at posn %d: 0x%04x len=%d data=%r\n",
  1140. self._position - length - 4, rc, length, data)
  1141. elif rc == XL_EOF:
  1142. self.xf_epilogue()
  1143. self.names_epilogue()
  1144. self.palette_epilogue()
  1145. if not self.encoding:
  1146. self.derive_encoding()
  1147. if self.biff_version == 45:
  1148. # DEBUG = 0
  1149. if DEBUG: print("global EOF: position", self._position, file=self.logfile)
  1150. # if DEBUG:
  1151. # pos = self._position - 4
  1152. # print repr(self.mem[pos:pos+40])
  1153. return
  1154. else:
  1155. # if DEBUG:
  1156. # print >> self.logfile, "parse_globals: ignoring record code 0x%04x" % rc
  1157. pass
  1158. def read(self, pos, length):
  1159. data = self.mem[pos:pos+length]
  1160. self._position = pos + len(data)
  1161. return data
  1162. def getbof(self, rqd_stream):
  1163. # DEBUG = 1
  1164. # if DEBUG: print >> self.logfile, "getbof(): position", self._position
  1165. if DEBUG: print("reqd: 0x%04x" % rqd_stream, file=self.logfile)
  1166. def bof_error(msg):
  1167. raise XLRDError('Unsupported format, or corrupt file: ' + msg)
  1168. savpos = self._position
  1169. opcode = self.get2bytes()
  1170. if opcode == MY_EOF:
  1171. bof_error('Expected BOF record; met end of file')
  1172. if opcode not in bofcodes:
  1173. bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
  1174. length = self.get2bytes()
  1175. if length == MY_EOF:
  1176. bof_error('Incomplete BOF record[1]; met end of file')
  1177. if not (4 <= length <= 20):
  1178. bof_error(
  1179. 'Invalid length (%d) for BOF record type 0x%04x'
  1180. % (length, opcode))
  1181. padding = b'\0' * max(0, boflen[opcode] - length)
  1182. data = self.read(self._position, length)
  1183. if DEBUG: fprintf(self.logfile, "\ngetbof(): data=%r\n", data)
  1184. if len(data) < length:
  1185. bof_error('Incomplete BOF record[2]; met end of file')
  1186. data += padding
  1187. version1 = opcode >> 8
  1188. version2, streamtype = unpack('<HH', data[0:4])
  1189. if DEBUG:
  1190. print("getbof(): op=0x%04x version2=0x%04x streamtype=0x%04x"
  1191. % (opcode, version2, streamtype), file=self.logfile)
  1192. bof_offset = self._position - 4 - length
  1193. if DEBUG:
  1194. print("getbof(): BOF found at offset %d; savpos=%d"
  1195. % (bof_offset, savpos), file=self.logfile)
  1196. version = build = year = 0
  1197. if version1 == 0x08:
  1198. build, year = unpack('<HH', data[4:8])
  1199. if version2 == 0x0600:
  1200. version = 80
  1201. elif version2 == 0x0500:
  1202. if year < 1994 or build in (2412, 3218, 3321):
  1203. version = 50
  1204. else:
  1205. version = 70
  1206. else:
  1207. # dodgy one, created by a 3rd-party tool
  1208. version = {
  1209. 0x0000: 21,
  1210. 0x0007: 21,
  1211. 0x0200: 21,
  1212. 0x0300: 30,
  1213. 0x0400: 40,
  1214. }.get(version2, 0)
  1215. elif version1 in (0x04, 0x02, 0x00):
  1216. version = {0x04: 40, 0x02: 30, 0x00: 21}[version1]
  1217. if version == 40 and streamtype == XL_WORKBOOK_GLOBALS_4W:
  1218. version = 45 # i.e. 4W
  1219. if DEBUG or self.verbosity >= 2:
  1220. print("BOF: op=0x%04x vers=0x%04x stream=0x%04x buildid=%d buildyr=%d -> BIFF%d"
  1221. % (opcode, version2, streamtype, build, year, version), file=self.logfile)
  1222. got_globals = streamtype == XL_WORKBOOK_GLOBALS or (
  1223. version == 45 and streamtype == XL_WORKBOOK_GLOBALS_4W)
  1224. if (rqd_stream == XL_WORKBOOK_GLOBALS and got_globals) or streamtype == rqd_stream:
  1225. return version
  1226. if version < 50 and streamtype == XL_WORKSHEET:
  1227. return version
  1228. if version >= 50 and streamtype == 0x0100:
  1229. bof_error("Workspace file -- no spreadsheet data")
  1230. bof_error(
  1231. 'BOF not workbook/worksheet: op=0x%04x vers=0x%04x strm=0x%04x build=%d year=%d -> BIFF%d'
  1232. % (opcode, version2, streamtype, build, year, version)
  1233. )
  1234. # === helper functions
  1235. def expand_cell_address(inrow, incol):
  1236. # Ref : OOo docs, "4.3.4 Cell Addresses in BIFF8"
  1237. outrow = inrow
  1238. if incol & 0x8000:
  1239. if outrow >= 32768:
  1240. outrow -= 65536
  1241. relrow = 1
  1242. else:
  1243. relrow = 0
  1244. outcol = incol & 0xFF
  1245. if incol & 0x4000:
  1246. if outcol >= 128:
  1247. outcol -= 256
  1248. relcol = 1
  1249. else:
  1250. relcol = 0
  1251. return outrow, outcol, relrow, relcol
  1252. def colname(colx, _A2Z="ABCDEFGHIJKLMNOPQRSTUVWXYZ"):
  1253. assert colx >= 0
  1254. name = UNICODE_LITERAL('')
  1255. while 1:
  1256. quot, rem = divmod(colx, 26)
  1257. name = _A2Z[rem] + name
  1258. if not quot:
  1259. return name
  1260. colx = quot - 1
  1261. def display_cell_address(rowx, colx, relrow, relcol):
  1262. if relrow:
  1263. rowpart = "(*%s%d)" % ("+-"[rowx < 0], abs(rowx))
  1264. else:
  1265. rowpart = "$%d" % (rowx+1,)
  1266. if relcol:
  1267. colpart = "(*%s%d)" % ("+-"[colx < 0], abs(colx))
  1268. else:
  1269. colpart = "$" + colname(colx)
  1270. return colpart + rowpart
  1271. def unpack_SST_table(datatab, nstrings):
  1272. "Return list of strings"
  1273. datainx = 0
  1274. ndatas = len(datatab)
  1275. data = datatab[0]
  1276. datalen = len(data)
  1277. pos = 8
  1278. strings = []
  1279. strappend = strings.append
  1280. richtext_runs = {}
  1281. local_unpack = unpack
  1282. local_min = min
  1283. local_BYTES_ORD = BYTES_ORD
  1284. latin_1 = "latin_1"
  1285. for _unused_i in xrange(nstrings):
  1286. nchars = local_unpack('<H', data[pos:pos+2])[0]
  1287. pos += 2
  1288. options = local_BYTES_ORD(data[pos])
  1289. pos += 1
  1290. rtcount = 0
  1291. phosz = 0
  1292. if options & 0x08: # richtext
  1293. rtcount = local_unpack('<H', data[pos:pos+2])[0]
  1294. pos += 2
  1295. if options & 0x04: # phonetic
  1296. phosz = local_unpack('<i', data[pos:pos+4])[0]
  1297. pos += 4
  1298. accstrg = UNICODE_LITERAL('')
  1299. charsgot = 0
  1300. while 1:
  1301. charsneed = nchars - charsgot
  1302. if options & 0x01:
  1303. # Uncompressed UTF-16
  1304. charsavail = local_min((datalen - pos) >> 1, charsneed)
  1305. rawstrg = data[pos:pos+2*charsavail]
  1306. # if DEBUG: print "SST U16: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg)
  1307. try:
  1308. accstrg += unicode(rawstrg, "utf_16_le")
  1309. except:
  1310. # print "SST U16: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg)
  1311. # Probable cause: dodgy data e.g. unfinished surrogate pair.
  1312. # E.g. file unicode2.xls in pyExcelerator's examples has cells containing
  1313. # unichr(i) for i in range(0x100000)
  1314. # so this will include 0xD800 etc
  1315. raise
  1316. pos += 2*charsavail
  1317. else:
  1318. # Note: this is COMPRESSED (not ASCII!) encoding!!!
  1319. charsavail = local_min(datalen - pos, charsneed)
  1320. rawstrg = data[pos:pos+charsavail]
  1321. # if DEBUG: print "SST CMPRSD: nchars=%d pos=%d rawstrg=%r" % (nchars, pos, rawstrg)
  1322. accstrg += unicode(rawstrg, latin_1)
  1323. pos += charsavail
  1324. charsgot += charsavail
  1325. if charsgot == nchars:
  1326. break
  1327. datainx += 1
  1328. data = datatab[datainx]
  1329. datalen = len(data)
  1330. options = local_BYTES_ORD(data[0])
  1331. pos = 1
  1332. if rtcount:
  1333. runs = []
  1334. for runindex in xrange(rtcount):
  1335. if pos == datalen:
  1336. pos = 0
  1337. datainx += 1
  1338. data = datatab[datainx]
  1339. datalen = len(data)
  1340. runs.append(local_unpack("<HH", data[pos:pos+4]))
  1341. pos += 4
  1342. richtext_runs[len(strings)] = runs
  1343. pos += phosz # size of the phonetic stuff to skip
  1344. if pos >= datalen:
  1345. # adjust to correct position in next record
  1346. pos = pos - datalen
  1347. datainx += 1
  1348. if datainx < ndatas:
  1349. data = datatab[datainx]
  1350. datalen = len(data)
  1351. else:
  1352. assert _unused_i == nstrings - 1
  1353. strappend(accstrg)
  1354. return strings, richtext_runs