sheet.py 104 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701170217031704170517061707170817091710171117121713171417151716171717181719172017211722172317241725172617271728172917301731173217331734173517361737173817391740174117421743174417451746174717481749175017511752175317541755175617571758175917601761176217631764176517661767176817691770177117721773177417751776177717781779178017811782178317841785178617871788178917901791179217931794179517961797179817991800180118021803180418051806180718081809181018111812181318141815181618171818181918201821182218231824182518261827182818291830183118321833183418351836183718381839184018411842184318441845184618471848184918501851185218531854185518561857185818591860186118621863186418651866186718681869187018711872187318741875187618771878187918801881188218831884188518861887188818891890189118921893189418951896189718981899190019011902190319041905190619071908190919101911191219131914191519161917191819191920192119221923192419251926192719281929193019311932193319341935193619371938193919401941194219431944194519461947194819491950195119521953195419551956195719581959196019611962196319641965196619671968196919701971197219731974197519761977197819791980198119821983198419851986198719881989199019911992199319941995199619971998199920002001200220032004200520062007200820092010201120122013201420152016201720182019202020212022202320242025202620272028202920302031203220332034203520362037203820392040204120422043204420452046204720482049205020512052205320542055205620572058205920602061206220632064206520662067206820692070207120722073207420752076207720782079208020812082208320842085208620872088208920902091209220932094209520962097209820992100210121022103210421052106210721082109211021112112211321142115211621172118211921202121212221232124212521262127212821292130213121322133213421352136213721382139214021412142214321442145214621472148214921502151215221532154215521562157215821592160216121622163216421652166216721682169217021712172217321742175217621772178217921802181218221832184218521862187218821892190219121922193219421952196219721982199220022012202220322042205220622072208220922102211221222132214221522162217221822192220222122222223222422252226222722282229223022312232223322342235223622372238223922402241224222432244224522462247224822492250225122522253225422552256225722582259226022612262226322642265226622672268226922702271227222732274227522762277227822792280228122822283228422852286228722882289229022912292229322942295229622972298229923002301230223032304230523062307230823092310231123122313231423152316231723182319232023212322232323242325232623272328232923302331233223332334233523362337233823392340234123422343234423452346234723482349235023512352235323542355235623572358235923602361236223632364236523662367236823692370237123722373237423752376237723782379238023812382238323842385238623872388238923902391239223932394239523962397239823992400240124022403240424052406240724082409241024112412241324142415241624172418241924202421242224232424242524262427242824292430243124322433243424352436243724382439244024412442244324442445244624472448244924502451245224532454245524562457245824592460246124622463246424652466246724682469247024712472247324742475247624772478247924802481248224832484248524862487248824892490
  1. # -*- coding: utf-8 -*-
  2. # Copyright (c) 2005-2013 Stephen John Machin, Lingfo Pty Ltd
  3. # This module is part of the xlrd package, which is released under a
  4. # BSD-style licence.
  5. from __future__ import print_function
  6. from array import array
  7. from struct import calcsize, unpack
  8. from .biffh import *
  9. from .formatting import Format, nearest_colour_index
  10. from .formula import (
  11. FMLA_TYPE_CELL, FMLA_TYPE_SHARED, decompile_formula, dump_formula,
  12. rangename2d,
  13. )
  14. from .timemachine import *
  15. DEBUG = 0
  16. OBJ_MSO_DEBUG = 0
  17. _WINDOW2_options = (
  18. # Attribute names and initial values to use in case
  19. # a WINDOW2 record is not written.
  20. ("show_formulas", 0),
  21. ("show_grid_lines", 1),
  22. ("show_sheet_headers", 1),
  23. ("panes_are_frozen", 0),
  24. ("show_zero_values", 1),
  25. ("automatic_grid_line_colour", 1),
  26. ("columns_from_right_to_left", 0),
  27. ("show_outline_symbols", 1),
  28. ("remove_splits_if_pane_freeze_is_removed", 0),
  29. # Multiple sheets can be selected, but only one can be active
  30. # (hold down Ctrl and click multiple tabs in the file in OOo)
  31. ("sheet_selected", 0),
  32. # "sheet_visible" should really be called "sheet_active"
  33. # and is 1 when this sheet is the sheet displayed when the file
  34. # is open. More than likely only one sheet should ever be set as
  35. # visible.
  36. # This would correspond to the Book's sheet_active attribute, but
  37. # that doesn't exist as WINDOW1 records aren't currently processed.
  38. # The real thing is the visibility attribute from the BOUNDSHEET record.
  39. ("sheet_visible", 0),
  40. ("show_in_page_break_preview", 0),
  41. )
  42. class Sheet(BaseObject):
  43. """
  44. Contains the data for one worksheet.
  45. In the cell access functions, ``rowx`` is a row index, counting from
  46. zero, and ``colx`` is a column index, counting from zero.
  47. Negative values for row/column indexes and slice positions are supported in
  48. the expected fashion.
  49. For information about cell types and cell values, refer to the documentation
  50. of the :class:`Cell` class.
  51. .. warning::
  52. You don't instantiate this class yourself. You access :class:`Sheet`
  53. objects via the :class:`~xlrd.book.Book` object that
  54. was returned when you called :func:`xlrd.open_workbook`.
  55. """
  56. #: Name of sheet.
  57. name = ''
  58. #: A reference to the :class:`~xlrd.book.Book` object to which this sheet
  59. #: belongs.
  60. #:
  61. #: Example usage: ``some_sheet.book.datemode``
  62. book = None
  63. #: Number of rows in sheet. A row index is in ``range(thesheet.nrows)``.
  64. nrows = 0
  65. #: Nominal number of columns in sheet. It is one more than the maximum
  66. #: column index found, ignoring trailing empty cells.
  67. #: See also the ``ragged_rows`` parameter to :func:`~xlrd.open_workbook`
  68. #: and :meth:`~xlrd.sheet.Sheet.row_len`.
  69. ncols = 0
  70. #: The map from a column index to a :class:`Colinfo` object. Often there is
  71. #: an entry in ``COLINFO`` records for all column indexes in ``range(257)``.
  72. #:
  73. #: .. note::
  74. #: xlrd ignores the entry for the non-existent
  75. #: 257th column.
  76. #:
  77. #: On the other hand, there may be no entry for unused columns.
  78. #:
  79. #: .. versionadded:: 0.6.1
  80. #:
  81. #: Populated only if ``open_workbook(..., formatting_info=True)``
  82. colinfo_map = {}
  83. #: The map from a row index to a :class:`Rowinfo` object.
  84. #:
  85. #: ..note::
  86. #: It is possible to have missing entries -- at least one source of
  87. #: XLS files doesn't bother writing ``ROW`` records.
  88. #:
  89. #: .. versionadded:: 0.6.1
  90. #:
  91. #: Populated only if ``open_workbook(..., formatting_info=True)``
  92. rowinfo_map = {}
  93. #: List of address ranges of cells containing column labels.
  94. #: These are set up in Excel by Insert > Name > Labels > Columns.
  95. #:
  96. #: .. versionadded:: 0.6.0
  97. #:
  98. #: How to deconstruct the list:
  99. #:
  100. #: .. code-block:: python
  101. #:
  102. #: for crange in thesheet.col_label_ranges:
  103. #: rlo, rhi, clo, chi = crange
  104. #: for rx in xrange(rlo, rhi):
  105. #: for cx in xrange(clo, chi):
  106. #: print "Column label at (rowx=%d, colx=%d) is %r" \
  107. #: (rx, cx, thesheet.cell_value(rx, cx))
  108. col_label_ranges = []
  109. #: List of address ranges of cells containing row labels.
  110. #: For more details, see :attr:`col_label_ranges`.
  111. #:
  112. #: .. versionadded:: 0.6.0
  113. row_label_ranges = []
  114. #: List of address ranges of cells which have been merged.
  115. #: These are set up in Excel by Format > Cells > Alignment, then ticking
  116. #: the "Merge cells" box.
  117. #:
  118. #: .. note::
  119. #: The upper limits are exclusive: i.e. ``[2, 3, 7, 9]`` only
  120. #: spans two cells.
  121. #:
  122. #: .. note:: Extracted only if ``open_workbook(..., formatting_info=True)``
  123. #:
  124. #: .. versionadded:: 0.6.1
  125. #:
  126. #: How to deconstruct the list:
  127. #:
  128. #: .. code-block:: python
  129. #:
  130. #: for crange in thesheet.merged_cells:
  131. #: rlo, rhi, clo, chi = crange
  132. #: for rowx in xrange(rlo, rhi):
  133. #: for colx in xrange(clo, chi):
  134. #: # cell (rlo, clo) (the top left one) will carry the data
  135. #: # and formatting info; the remainder will be recorded as
  136. #: # blank cells, but a renderer will apply the formatting info
  137. #: # for the top left cell (e.g. border, pattern) to all cells in
  138. #: # the range.
  139. merged_cells = []
  140. #: Mapping of ``(rowx, colx)`` to list of ``(offset, font_index)`` tuples.
  141. #: The offset defines where in the string the font begins to be used.
  142. #: Offsets are expected to be in ascending order.
  143. #: If the first offset is not zero, the meaning is that the cell's ``XF``'s
  144. #: font should be used from offset 0.
  145. #:
  146. #: This is a sparse mapping. There is no entry for cells that are not
  147. #: formatted with rich text.
  148. #:
  149. #: How to use:
  150. #:
  151. #: .. code-block:: python
  152. #:
  153. #: runlist = thesheet.rich_text_runlist_map.get((rowx, colx))
  154. #: if runlist:
  155. #: for offset, font_index in runlist:
  156. #: # do work here.
  157. #: pass
  158. #:
  159. #: .. versionadded:: 0.7.2
  160. #:
  161. #: Populated only if ``open_workbook(..., formatting_info=True)``
  162. rich_text_runlist_map = {}
  163. #: Default column width from ``DEFCOLWIDTH`` record, else ``None``.
  164. #: From the OOo docs:
  165. #:
  166. #: Column width in characters, using the width of the zero character
  167. #: from default font (first FONT record in the file). Excel adds some
  168. #: extra space to the default width, depending on the default font and
  169. #: default font size. The algorithm how to exactly calculate the resulting
  170. #: column width is not known.
  171. #: Example: The default width of 8 set in this record results in a column
  172. #: width of 8.43 using Arial font with a size of 10 points.
  173. #:
  174. #: For the default hierarchy, refer to the :class:`Colinfo` class.
  175. #:
  176. #: .. versionadded:: 0.6.1
  177. defcolwidth = None
  178. #: Default column width from ``STANDARDWIDTH`` record, else ``None``.
  179. #:
  180. #: From the OOo docs:
  181. #:
  182. #: Default width of the columns in 1/256 of the width of the zero
  183. #: character, using default font (first FONT record in the file).
  184. #:
  185. #: For the default hierarchy, refer to the :class:`Colinfo` class.
  186. #:
  187. #: .. versionadded:: 0.6.1
  188. standardwidth = None
  189. #: Default value to be used for a row if there is
  190. #: no ``ROW`` record for that row.
  191. #: From the *optional* ``DEFAULTROWHEIGHT`` record.
  192. default_row_height = None
  193. #: Default value to be used for a row if there is
  194. #: no ``ROW`` record for that row.
  195. #: From the *optional* ``DEFAULTROWHEIGHT`` record.
  196. default_row_height_mismatch = None
  197. #: Default value to be used for a row if there is
  198. #: no ``ROW`` record for that row.
  199. #: From the *optional* ``DEFAULTROWHEIGHT`` record.
  200. default_row_hidden = None
  201. #: Default value to be used for a row if there is
  202. #: no ``ROW`` record for that row.
  203. #: From the *optional* ``DEFAULTROWHEIGHT`` record.
  204. default_additional_space_above = None
  205. #: Default value to be used for a row if there is
  206. #: no ``ROW`` record for that row.
  207. #: From the *optional* ``DEFAULTROWHEIGHT`` record.
  208. default_additional_space_below = None
  209. #: Visibility of the sheet:
  210. #: ::
  211. #:
  212. #: 0 = visible
  213. #: 1 = hidden (can be unhidden by user -- Format -> Sheet -> Unhide)
  214. #: 2 = "very hidden" (can be unhidden only by VBA macro).
  215. visibility = 0
  216. #: A 256-element tuple corresponding to the contents of the GCW record for
  217. #: this sheet. If no such record, treat as all bits zero.
  218. #: Applies to BIFF4-7 only. See docs of the :class:`Colinfo` class for
  219. #: discussion.
  220. gcw = (0, ) * 256
  221. #: A list of :class:`Hyperlink` objects corresponding to ``HLINK`` records
  222. #: found in the worksheet.
  223. #:
  224. #: .. versionadded:: 0.7.2
  225. hyperlink_list = []
  226. #: A sparse mapping from ``(rowx, colx)`` to an item in
  227. #: :attr:`~xlrd.sheet.Sheet.hyperlink_list`.
  228. #: Cells not covered by a hyperlink are not mapped.
  229. #: It is possible using the Excel UI to set up a hyperlink that
  230. #: covers a larger-than-1x1 rectangle of cells.
  231. #: Hyperlink rectangles may overlap (Excel doesn't check).
  232. #: When a multiply-covered cell is clicked on, the hyperlink that is
  233. #: activated
  234. #: (and the one that is mapped here) is the last in
  235. #: :attr:`~xlrd.sheet.Sheet.hyperlink_list`.
  236. #:
  237. #: .. versionadded:: 0.7.2
  238. hyperlink_map = {}
  239. #: A sparse mapping from ``(rowx, colx)`` to a :class:`Note` object.
  240. #: Cells not containing a note ("comment") are not mapped.
  241. #:
  242. #: .. versionadded:: 0.7.2
  243. cell_note_map = {}
  244. #: Number of columns in left pane (frozen panes; for split panes, see
  245. #: comments in code)
  246. vert_split_pos = 0
  247. #: Number of rows in top pane (frozen panes; for split panes, see comments
  248. #: in code)
  249. horz_split_pos = 0
  250. #: Index of first visible row in bottom frozen/split pane
  251. horz_split_first_visible = 0
  252. #: Index of first visible column in right frozen/split pane
  253. vert_split_first_visible = 0
  254. #: Frozen panes: ignore it. Split panes: explanation and diagrams in
  255. #: OOo docs.
  256. split_active_pane = 0
  257. #: Boolean specifying if a ``PANE`` record was present, ignore unless you're
  258. #: ``xlutils.copy``
  259. has_pane_record = 0
  260. #: A list of the horizontal page breaks in this sheet.
  261. #: Breaks are tuples in the form
  262. #: ``(index of row after break, start col index, end col index)``.
  263. #:
  264. #: Populated only if ``open_workbook(..., formatting_info=True)``
  265. #:
  266. #: .. versionadded:: 0.7.2
  267. horizontal_page_breaks = []
  268. #: A list of the vertical page breaks in this sheet.
  269. #: Breaks are tuples in the form
  270. #: ``(index of col after break, start row index, end row index)``.
  271. #:
  272. #: Populated only if ``open_workbook(..., formatting_info=True)``
  273. #:
  274. #: .. versionadded:: 0.7.2
  275. vertical_page_breaks = []
  276. def __init__(self, book, position, name, number):
  277. self.book = book
  278. self.biff_version = book.biff_version
  279. self._position = position
  280. self.logfile = book.logfile
  281. self.bt = array('B', [XL_CELL_EMPTY])
  282. self.bf = array('h', [-1])
  283. self.name = name
  284. self.number = number
  285. self.verbosity = book.verbosity
  286. self.formatting_info = book.formatting_info
  287. self.ragged_rows = book.ragged_rows
  288. if self.ragged_rows:
  289. self.put_cell = self.put_cell_ragged
  290. else:
  291. self.put_cell = self.put_cell_unragged
  292. self._xf_index_to_xl_type_map = book._xf_index_to_xl_type_map
  293. self.nrows = 0 # actual, including possibly empty cells
  294. self.ncols = 0
  295. self._maxdatarowx = -1 # highest rowx containing a non-empty cell
  296. self._maxdatacolx = -1 # highest colx containing a non-empty cell
  297. self._dimnrows = 0 # as per DIMENSIONS record
  298. self._dimncols = 0
  299. self._cell_values = []
  300. self._cell_types = []
  301. self._cell_xf_indexes = []
  302. self.defcolwidth = None
  303. self.standardwidth = None
  304. self.default_row_height = None
  305. self.default_row_height_mismatch = 0
  306. self.default_row_hidden = 0
  307. self.default_additional_space_above = 0
  308. self.default_additional_space_below = 0
  309. self.colinfo_map = {}
  310. self.rowinfo_map = {}
  311. self.col_label_ranges = []
  312. self.row_label_ranges = []
  313. self.merged_cells = []
  314. self.rich_text_runlist_map = {}
  315. self.horizontal_page_breaks = []
  316. self.vertical_page_breaks = []
  317. self._xf_index_stats = [0, 0, 0, 0]
  318. self.visibility = book._sheet_visibility[number] # from BOUNDSHEET record
  319. for attr, defval in _WINDOW2_options:
  320. setattr(self, attr, defval)
  321. self.first_visible_rowx = 0
  322. self.first_visible_colx = 0
  323. self.gridline_colour_index = 0x40
  324. self.gridline_colour_rgb = None # pre-BIFF8
  325. self.hyperlink_list = []
  326. self.hyperlink_map = {}
  327. self.cell_note_map = {}
  328. # Values calculated by xlrd to predict the mag factors that
  329. # will actually be used by Excel to display your worksheet.
  330. # Pass these values to xlwt when writing XLS files.
  331. # Warning 1: Behaviour of OOo Calc and Gnumeric has been observed to differ from Excel's.
  332. # Warning 2: A value of zero means almost exactly what it says. Your sheet will be
  333. # displayed as a very tiny speck on the screen. xlwt will reject attempts to set
  334. # a mag_factor that is not (10 <= mag_factor <= 400).
  335. self.cooked_page_break_preview_mag_factor = 60
  336. self.cooked_normal_view_mag_factor = 100
  337. # Values (if any) actually stored on the XLS file
  338. self.cached_page_break_preview_mag_factor = 0 # default (60%), from WINDOW2 record
  339. self.cached_normal_view_mag_factor = 0 # default (100%), from WINDOW2 record
  340. self.scl_mag_factor = None # from SCL record
  341. self._ixfe = None # BIFF2 only
  342. self._cell_attr_to_xfx = {} # BIFF2.0 only
  343. if self.biff_version >= 80:
  344. self.utter_max_rows = 65536
  345. else:
  346. self.utter_max_rows = 16384
  347. self.utter_max_cols = 256
  348. self._first_full_rowx = -1
  349. # self._put_cell_exceptions = 0
  350. # self._put_cell_row_widenings = 0
  351. # self._put_cell_rows_appended = 0
  352. # self._put_cell_cells_appended = 0
  353. def cell(self, rowx, colx):
  354. """
  355. :class:`Cell` object in the given row and column.
  356. """
  357. if self.formatting_info:
  358. xfx = self.cell_xf_index(rowx, colx)
  359. else:
  360. xfx = None
  361. return Cell(
  362. self._cell_types[rowx][colx],
  363. self._cell_values[rowx][colx],
  364. xfx,
  365. )
  366. def cell_value(self, rowx, colx):
  367. "Value of the cell in the given row and column."
  368. return self._cell_values[rowx][colx]
  369. def cell_type(self, rowx, colx):
  370. """
  371. Type of the cell in the given row and column.
  372. Refer to the documentation of the :class:`Cell` class.
  373. """
  374. return self._cell_types[rowx][colx]
  375. def cell_xf_index(self, rowx, colx):
  376. """
  377. XF index of the cell in the given row and column.
  378. This is an index into :attr:`~xlrd.book.Book.xf_list`.
  379. .. versionadded:: 0.6.1
  380. """
  381. self.req_fmt_info()
  382. xfx = self._cell_xf_indexes[rowx][colx]
  383. if xfx > -1:
  384. self._xf_index_stats[0] += 1
  385. return xfx
  386. # Check for a row xf_index
  387. try:
  388. xfx = self.rowinfo_map[rowx].xf_index
  389. if xfx > -1:
  390. self._xf_index_stats[1] += 1
  391. return xfx
  392. except KeyError:
  393. pass
  394. # Check for a column xf_index
  395. try:
  396. xfx = self.colinfo_map[colx].xf_index
  397. if xfx == -1: xfx = 15
  398. self._xf_index_stats[2] += 1
  399. return xfx
  400. except KeyError:
  401. # If all else fails, 15 is used as hardwired global default xf_index.
  402. self._xf_index_stats[3] += 1
  403. return 15
  404. def row_len(self, rowx):
  405. """
  406. Returns the effective number of cells in the given row. For use with
  407. ``open_workbook(ragged_rows=True)`` which is likely to produce rows
  408. with fewer than :attr:`~Sheet.ncols` cells.
  409. .. versionadded:: 0.7.2
  410. """
  411. return len(self._cell_values[rowx])
  412. def row(self, rowx):
  413. """
  414. Returns a sequence of the :class:`Cell` objects in the given row.
  415. """
  416. return [
  417. self.cell(rowx, colx)
  418. for colx in xrange(len(self._cell_values[rowx]))
  419. ]
  420. def __getitem__(self, item):
  421. """
  422. Takes either rowindex or (rowindex, colindex) as an index,
  423. and returns either row or cell respectively.
  424. """
  425. try:
  426. rowix, colix = item
  427. except TypeError:
  428. # it's not a tuple (or of right size), let's try indexing as is
  429. # if this is a problem, let this error propagate back
  430. return self.row(item)
  431. else:
  432. return self.cell(rowix, colix)
  433. def get_rows(self):
  434. "Returns a generator for iterating through each row."
  435. return (self.row(index) for index in range(self.nrows))
  436. # makes `for row in sheet` natural and intuitive
  437. __iter__ = get_rows
  438. def row_types(self, rowx, start_colx=0, end_colx=None):
  439. """
  440. Returns a slice of the types of the cells in the given row.
  441. """
  442. if end_colx is None:
  443. return self._cell_types[rowx][start_colx:]
  444. return self._cell_types[rowx][start_colx:end_colx]
  445. def row_values(self, rowx, start_colx=0, end_colx=None):
  446. """
  447. Returns a slice of the values of the cells in the given row.
  448. """
  449. if end_colx is None:
  450. return self._cell_values[rowx][start_colx:]
  451. return self._cell_values[rowx][start_colx:end_colx]
  452. def row_slice(self, rowx, start_colx=0, end_colx=None):
  453. """
  454. Returns a slice of the :class:`Cell` objects in the given row.
  455. """
  456. nc = len(self._cell_values[rowx])
  457. if start_colx < 0:
  458. start_colx += nc
  459. if start_colx < 0:
  460. start_colx = 0
  461. if end_colx is None or end_colx > nc:
  462. end_colx = nc
  463. elif end_colx < 0:
  464. end_colx += nc
  465. return [
  466. self.cell(rowx, colx)
  467. for colx in xrange(start_colx, end_colx)
  468. ]
  469. def col_slice(self, colx, start_rowx=0, end_rowx=None):
  470. """
  471. Returns a slice of the :class:`Cell` objects in the given column.
  472. """
  473. nr = self.nrows
  474. if start_rowx < 0:
  475. start_rowx += nr
  476. if start_rowx < 0:
  477. start_rowx = 0
  478. if end_rowx is None or end_rowx > nr:
  479. end_rowx = nr
  480. elif end_rowx < 0:
  481. end_rowx += nr
  482. return [
  483. self.cell(rowx, colx)
  484. for rowx in xrange(start_rowx, end_rowx)
  485. ]
  486. def col_values(self, colx, start_rowx=0, end_rowx=None):
  487. """
  488. Returns a slice of the values of the cells in the given column.
  489. """
  490. nr = self.nrows
  491. if start_rowx < 0:
  492. start_rowx += nr
  493. if start_rowx < 0:
  494. start_rowx = 0
  495. if end_rowx is None or end_rowx > nr:
  496. end_rowx = nr
  497. elif end_rowx < 0:
  498. end_rowx += nr
  499. return [
  500. self._cell_values[rowx][colx]
  501. for rowx in xrange(start_rowx, end_rowx)
  502. ]
  503. def col_types(self, colx, start_rowx=0, end_rowx=None):
  504. """
  505. Returns a slice of the types of the cells in the given column.
  506. """
  507. nr = self.nrows
  508. if start_rowx < 0:
  509. start_rowx += nr
  510. if start_rowx < 0:
  511. start_rowx = 0
  512. if end_rowx is None or end_rowx > nr:
  513. end_rowx = nr
  514. elif end_rowx < 0:
  515. end_rowx += nr
  516. return [
  517. self._cell_types[rowx][colx]
  518. for rowx in xrange(start_rowx, end_rowx)
  519. ]
  520. col = col_slice
  521. # === Following methods are used in building the worksheet.
  522. # === They are not part of the API.
  523. def tidy_dimensions(self):
  524. if self.verbosity >= 3:
  525. fprintf(
  526. self.logfile,
  527. "tidy_dimensions: nrows=%d ncols=%d \n",
  528. self.nrows, self.ncols,
  529. )
  530. if 1 and self.merged_cells:
  531. nr = nc = 0
  532. umaxrows = self.utter_max_rows
  533. umaxcols = self.utter_max_cols
  534. for crange in self.merged_cells:
  535. rlo, rhi, clo, chi = crange
  536. if not (0 <= rlo < rhi <= umaxrows) or not (0 <= clo < chi <= umaxcols):
  537. fprintf(self.logfile,
  538. "*** WARNING: sheet #%d (%r), MERGEDCELLS bad range %r\n",
  539. self.number, self.name, crange)
  540. if rhi > nr: nr = rhi
  541. if chi > nc: nc = chi
  542. if nc > self.ncols:
  543. self.ncols = nc
  544. self._first_full_rowx = -2
  545. if nr > self.nrows:
  546. # we put one empty cell at (nr-1,0) to make sure
  547. # we have the right number of rows. The ragged rows
  548. # will sort out the rest if needed.
  549. self.put_cell(nr-1, 0, XL_CELL_EMPTY, UNICODE_LITERAL(''), -1)
  550. if (self.verbosity >= 1 and
  551. (self.nrows != self._dimnrows or self.ncols != self._dimncols)):
  552. fprintf(
  553. self.logfile,
  554. "NOTE *** sheet %d (%r): DIMENSIONS R,C = %d,%d should be %d,%d\n",
  555. self.number,
  556. self.name,
  557. self._dimnrows,
  558. self._dimncols,
  559. self.nrows,
  560. self.ncols,
  561. )
  562. if not self.ragged_rows:
  563. # fix ragged rows
  564. ncols = self.ncols
  565. s_cell_types = self._cell_types
  566. s_cell_values = self._cell_values
  567. s_cell_xf_indexes = self._cell_xf_indexes
  568. s_fmt_info = self.formatting_info
  569. # for rowx in xrange(self.nrows):
  570. if self._first_full_rowx == -2:
  571. ubound = self.nrows
  572. else:
  573. ubound = self._first_full_rowx
  574. for rowx in xrange(ubound):
  575. trow = s_cell_types[rowx]
  576. rlen = len(trow)
  577. nextra = ncols - rlen
  578. if nextra > 0:
  579. s_cell_values[rowx][rlen:] = [UNICODE_LITERAL('')] * nextra
  580. trow[rlen:] = self.bt * nextra
  581. if s_fmt_info:
  582. s_cell_xf_indexes[rowx][rlen:] = self.bf * nextra
  583. def put_cell_ragged(self, rowx, colx, ctype, value, xf_index):
  584. if ctype is None:
  585. # we have a number, so look up the cell type
  586. ctype = self._xf_index_to_xl_type_map[xf_index]
  587. assert 0 <= colx < self.utter_max_cols
  588. assert 0 <= rowx < self.utter_max_rows
  589. fmt_info = self.formatting_info
  590. try:
  591. nr = rowx + 1
  592. if self.nrows < nr:
  593. scta = self._cell_types.append
  594. scva = self._cell_values.append
  595. scxa = self._cell_xf_indexes.append
  596. bt = self.bt
  597. bf = self.bf
  598. for _unused in xrange(self.nrows, nr):
  599. scta(bt * 0)
  600. scva([])
  601. if fmt_info:
  602. scxa(bf * 0)
  603. self.nrows = nr
  604. types_row = self._cell_types[rowx]
  605. values_row = self._cell_values[rowx]
  606. if fmt_info:
  607. fmt_row = self._cell_xf_indexes[rowx]
  608. ltr = len(types_row)
  609. if colx >= self.ncols:
  610. self.ncols = colx + 1
  611. num_empty = colx - ltr
  612. if not num_empty:
  613. # most common case: colx == previous colx + 1
  614. # self._put_cell_cells_appended += 1
  615. types_row.append(ctype)
  616. values_row.append(value)
  617. if fmt_info:
  618. fmt_row.append(xf_index)
  619. return
  620. if num_empty > 0:
  621. num_empty += 1
  622. # self._put_cell_row_widenings += 1
  623. # types_row.extend(self.bt * num_empty)
  624. # values_row.extend([UNICODE_LITERAL('')] * num_empty)
  625. # if fmt_info:
  626. # fmt_row.extend(self.bf * num_empty)
  627. types_row[ltr:] = self.bt * num_empty
  628. values_row[ltr:] = [UNICODE_LITERAL('')] * num_empty
  629. if fmt_info:
  630. fmt_row[ltr:] = self.bf * num_empty
  631. types_row[colx] = ctype
  632. values_row[colx] = value
  633. if fmt_info:
  634. fmt_row[colx] = xf_index
  635. except:
  636. print("put_cell", rowx, colx, file=self.logfile)
  637. raise
  638. def put_cell_unragged(self, rowx, colx, ctype, value, xf_index):
  639. if ctype is None:
  640. # we have a number, so look up the cell type
  641. ctype = self._xf_index_to_xl_type_map[xf_index]
  642. # assert 0 <= colx < self.utter_max_cols
  643. # assert 0 <= rowx < self.utter_max_rows
  644. try:
  645. self._cell_types[rowx][colx] = ctype
  646. self._cell_values[rowx][colx] = value
  647. if self.formatting_info:
  648. self._cell_xf_indexes[rowx][colx] = xf_index
  649. except IndexError:
  650. # print >> self.logfile, "put_cell extending", rowx, colx
  651. # self.extend_cells(rowx+1, colx+1)
  652. # self._put_cell_exceptions += 1
  653. nr = rowx + 1
  654. nc = colx + 1
  655. assert 1 <= nc <= self.utter_max_cols
  656. assert 1 <= nr <= self.utter_max_rows
  657. if nc > self.ncols:
  658. self.ncols = nc
  659. # The row self._first_full_rowx and all subsequent rows
  660. # are guaranteed to have length == self.ncols. Thus the
  661. # "fix ragged rows" section of the tidy_dimensions method
  662. # doesn't need to examine them.
  663. if nr < self.nrows:
  664. # cell data is not in non-descending row order *AND*
  665. # self.ncols has been bumped up.
  666. # This very rare case ruins this optimisation.
  667. self._first_full_rowx = -2
  668. elif rowx > self._first_full_rowx > -2:
  669. self._first_full_rowx = rowx
  670. if nr <= self.nrows:
  671. # New cell is in an existing row, so extend that row (if necessary).
  672. # Note that nr < self.nrows means that the cell data
  673. # is not in ascending row order!!
  674. trow = self._cell_types[rowx]
  675. nextra = self.ncols - len(trow)
  676. if nextra > 0:
  677. # self._put_cell_row_widenings += 1
  678. trow.extend(self.bt * nextra)
  679. if self.formatting_info:
  680. self._cell_xf_indexes[rowx].extend(self.bf * nextra)
  681. self._cell_values[rowx].extend([UNICODE_LITERAL('')] * nextra)
  682. else:
  683. scta = self._cell_types.append
  684. scva = self._cell_values.append
  685. scxa = self._cell_xf_indexes.append
  686. fmt_info = self.formatting_info
  687. nc = self.ncols
  688. bt = self.bt
  689. bf = self.bf
  690. for _unused in xrange(self.nrows, nr):
  691. # self._put_cell_rows_appended += 1
  692. scta(bt * nc)
  693. scva([UNICODE_LITERAL('')] * nc)
  694. if fmt_info:
  695. scxa(bf * nc)
  696. self.nrows = nr
  697. # === end of code from extend_cells()
  698. try:
  699. self._cell_types[rowx][colx] = ctype
  700. self._cell_values[rowx][colx] = value
  701. if self.formatting_info:
  702. self._cell_xf_indexes[rowx][colx] = xf_index
  703. except:
  704. print("put_cell", rowx, colx, file=self.logfile)
  705. raise
  706. except:
  707. print("put_cell", rowx, colx, file=self.logfile)
  708. raise
  709. # === Methods after this line neither know nor care about how cells are stored.
  710. def read(self, bk):
  711. global rc_stats
  712. DEBUG = 0
  713. blah = DEBUG or self.verbosity >= 2
  714. blah_rows = DEBUG or self.verbosity >= 4
  715. blah_formulas = 0 and blah
  716. r1c1 = 0
  717. oldpos = bk._position
  718. bk._position = self._position
  719. XL_SHRFMLA_ETC_ETC = (
  720. XL_SHRFMLA, XL_ARRAY, XL_TABLEOP, XL_TABLEOP2,
  721. XL_ARRAY2, XL_TABLEOP_B2,
  722. )
  723. self_put_cell = self.put_cell
  724. local_unpack = unpack
  725. bk_get_record_parts = bk.get_record_parts
  726. bv = self.biff_version
  727. fmt_info = self.formatting_info
  728. do_sst_rich_text = fmt_info and bk._rich_text_runlist_map
  729. rowinfo_sharing_dict = {}
  730. txos = {}
  731. eof_found = 0
  732. while 1:
  733. # if DEBUG: print "SHEET.READ: about to read from position %d" % bk._position
  734. rc, data_len, data = bk_get_record_parts()
  735. # if rc in rc_stats:
  736. # rc_stats[rc] += 1
  737. # else:
  738. # rc_stats[rc] = 1
  739. # if DEBUG: print "SHEET.READ: op 0x%04x, %d bytes %r" % (rc, data_len, data)
  740. if rc == XL_NUMBER:
  741. # [:14] in following stmt ignores extraneous rubbish at end of record.
  742. # Sample file testEON-8.xls supplied by Jan Kraus.
  743. rowx, colx, xf_index, d = local_unpack('<HHHd', data[:14])
  744. # if xf_index == 0:
  745. # fprintf(self.logfile,
  746. # "NUMBER: r=%d c=%d xfx=%d %f\n", rowx, colx, xf_index, d)
  747. self_put_cell(rowx, colx, None, d, xf_index)
  748. elif rc == XL_LABELSST:
  749. rowx, colx, xf_index, sstindex = local_unpack('<HHHi', data)
  750. # print "LABELSST", rowx, colx, sstindex, bk._sharedstrings[sstindex]
  751. self_put_cell(rowx, colx, XL_CELL_TEXT, bk._sharedstrings[sstindex], xf_index)
  752. if do_sst_rich_text:
  753. runlist = bk._rich_text_runlist_map.get(sstindex)
  754. if runlist:
  755. self.rich_text_runlist_map[(rowx, colx)] = runlist
  756. elif rc == XL_LABEL:
  757. rowx, colx, xf_index = local_unpack('<HHH', data[0:6])
  758. if bv < BIFF_FIRST_UNICODE:
  759. strg = unpack_string(data, 6, bk.encoding or bk.derive_encoding(), lenlen=2)
  760. else:
  761. strg = unpack_unicode(data, 6, lenlen=2)
  762. self_put_cell(rowx, colx, XL_CELL_TEXT, strg, xf_index)
  763. elif rc == XL_RSTRING:
  764. rowx, colx, xf_index = local_unpack('<HHH', data[0:6])
  765. if bv < BIFF_FIRST_UNICODE:
  766. strg, pos = unpack_string_update_pos(data, 6, bk.encoding or bk.derive_encoding(), lenlen=2)
  767. nrt = BYTES_ORD(data[pos])
  768. pos += 1
  769. runlist = []
  770. for _unused in xrange(nrt):
  771. runlist.append(unpack('<BB', data[pos:pos+2]))
  772. pos += 2
  773. assert pos == len(data)
  774. else:
  775. strg, pos = unpack_unicode_update_pos(data, 6, lenlen=2)
  776. nrt = unpack('<H', data[pos:pos+2])[0]
  777. pos += 2
  778. runlist = []
  779. for _unused in xrange(nrt):
  780. runlist.append(unpack('<HH', data[pos:pos+4]))
  781. pos += 4
  782. assert pos == len(data)
  783. self_put_cell(rowx, colx, XL_CELL_TEXT, strg, xf_index)
  784. self.rich_text_runlist_map[(rowx, colx)] = runlist
  785. elif rc == XL_RK:
  786. rowx, colx, xf_index = local_unpack('<HHH', data[:6])
  787. d = unpack_RK(data[6:10])
  788. self_put_cell(rowx, colx, None, d, xf_index)
  789. elif rc == XL_MULRK:
  790. mulrk_row, mulrk_first = local_unpack('<HH', data[0:4])
  791. mulrk_last, = local_unpack('<H', data[-2:])
  792. pos = 4
  793. for colx in xrange(mulrk_first, mulrk_last+1):
  794. xf_index, = local_unpack('<H', data[pos:pos+2])
  795. d = unpack_RK(data[pos+2:pos+6])
  796. pos += 6
  797. self_put_cell(mulrk_row, colx, None, d, xf_index)
  798. elif rc == XL_ROW:
  799. # Version 0.6.0a3: ROW records are just not worth using (for memory allocation).
  800. # Version 0.6.1: now used for formatting info.
  801. if not fmt_info: continue
  802. rowx, bits1, bits2 = local_unpack('<H4xH4xi', data[0:16])
  803. if not(0 <= rowx < self.utter_max_rows):
  804. print("*** NOTE: ROW record has row index %d; "
  805. "should have 0 <= rowx < %d -- record ignored!"
  806. % (rowx, self.utter_max_rows), file=self.logfile)
  807. continue
  808. key = (bits1, bits2)
  809. r = rowinfo_sharing_dict.get(key)
  810. if r is None:
  811. rowinfo_sharing_dict[key] = r = Rowinfo()
  812. # Using upkbits() is far too slow on a file
  813. # with 30 sheets each with 10K rows :-(
  814. # upkbits(r, bits1, (
  815. # ( 0, 0x7FFF, 'height'),
  816. # (15, 0x8000, 'has_default_height'),
  817. # ))
  818. # upkbits(r, bits2, (
  819. # ( 0, 0x00000007, 'outline_level'),
  820. # ( 4, 0x00000010, 'outline_group_starts_ends'),
  821. # ( 5, 0x00000020, 'hidden'),
  822. # ( 6, 0x00000040, 'height_mismatch'),
  823. # ( 7, 0x00000080, 'has_default_xf_index'),
  824. # (16, 0x0FFF0000, 'xf_index'),
  825. # (28, 0x10000000, 'additional_space_above'),
  826. # (29, 0x20000000, 'additional_space_below'),
  827. # ))
  828. # So:
  829. r.height = bits1 & 0x7fff
  830. r.has_default_height = (bits1 >> 15) & 1
  831. r.outline_level = bits2 & 7
  832. r.outline_group_starts_ends = (bits2 >> 4) & 1
  833. r.hidden = (bits2 >> 5) & 1
  834. r.height_mismatch = (bits2 >> 6) & 1
  835. r.has_default_xf_index = (bits2 >> 7) & 1
  836. r.xf_index = (bits2 >> 16) & 0xfff
  837. r.additional_space_above = (bits2 >> 28) & 1
  838. r.additional_space_below = (bits2 >> 29) & 1
  839. if not r.has_default_xf_index:
  840. r.xf_index = -1
  841. self.rowinfo_map[rowx] = r
  842. if 0 and r.xf_index > -1:
  843. fprintf(self.logfile,
  844. "**ROW %d %d %d\n",
  845. self.number, rowx, r.xf_index)
  846. if blah_rows:
  847. print('ROW', rowx, bits1, bits2, file=self.logfile)
  848. r.dump(self.logfile,
  849. header="--- sh #%d, rowx=%d ---" % (self.number, rowx))
  850. elif rc in XL_FORMULA_OPCODES: # 06, 0206, 0406
  851. # DEBUG = 1
  852. # if DEBUG: print "FORMULA: rc: 0x%04x data: %r" % (rc, data)
  853. if bv >= 50:
  854. rowx, colx, xf_index, result_str, flags = local_unpack('<HHH8sH', data[0:16])
  855. elif bv >= 30:
  856. rowx, colx, xf_index, result_str, flags = local_unpack('<HHH8sH', data[0:16])
  857. else: # BIFF2
  858. rowx, colx, cell_attr, result_str, flags = local_unpack('<HH3s8sB', data[0:16])
  859. xf_index = self.fixed_BIFF2_xfindex(cell_attr, rowx, colx)
  860. if blah_formulas: # testing formula dumper
  861. #### XXXX FIXME
  862. fprintf(self.logfile, "FORMULA: rowx=%d colx=%d\n", rowx, colx)
  863. fmlalen = local_unpack("<H", data[20:22])[0]
  864. decompile_formula(bk, data[22:], fmlalen, FMLA_TYPE_CELL,
  865. browx=rowx, bcolx=colx, blah=1, r1c1=r1c1)
  866. if result_str[6:8] == b"\xFF\xFF":
  867. first_byte = BYTES_ORD(result_str[0])
  868. if first_byte == 0:
  869. # need to read next record (STRING)
  870. gotstring = 0
  871. # if flags & 8:
  872. if 1: # "flags & 8" applies only to SHRFMLA
  873. # actually there's an optional SHRFMLA or ARRAY etc record to skip over
  874. rc2, data2_len, data2 = bk.get_record_parts()
  875. if rc2 == XL_STRING or rc2 == XL_STRING_B2:
  876. gotstring = 1
  877. elif rc2 == XL_ARRAY:
  878. row1x, rownx, col1x, colnx, array_flags, tokslen = \
  879. local_unpack("<HHBBBxxxxxH", data2[:14])
  880. if blah_formulas:
  881. fprintf(self.logfile, "ARRAY: %d %d %d %d %d\n",
  882. row1x, rownx, col1x, colnx, array_flags)
  883. # dump_formula(bk, data2[14:], tokslen, bv, reldelta=0, blah=1)
  884. elif rc2 == XL_SHRFMLA:
  885. row1x, rownx, col1x, colnx, nfmlas, tokslen = \
  886. local_unpack("<HHBBxBH", data2[:10])
  887. if blah_formulas:
  888. fprintf(self.logfile, "SHRFMLA (sub): %d %d %d %d %d\n",
  889. row1x, rownx, col1x, colnx, nfmlas)
  890. decompile_formula(bk, data2[10:], tokslen, FMLA_TYPE_SHARED,
  891. blah=1, browx=rowx, bcolx=colx, r1c1=r1c1)
  892. elif rc2 not in XL_SHRFMLA_ETC_ETC:
  893. raise XLRDError(
  894. "Expected SHRFMLA, ARRAY, TABLEOP* or STRING record; found 0x%04x" % rc2)
  895. # if DEBUG: print "gotstring:", gotstring
  896. # now for the STRING record
  897. if not gotstring:
  898. rc2, _unused_len, data2 = bk.get_record_parts()
  899. if rc2 not in (XL_STRING, XL_STRING_B2):
  900. raise XLRDError("Expected STRING record; found 0x%04x" % rc2)
  901. # if DEBUG: print "STRING: data=%r BIFF=%d cp=%d" % (data2, self.biff_version, bk.encoding)
  902. strg = self.string_record_contents(data2)
  903. self.put_cell(rowx, colx, XL_CELL_TEXT, strg, xf_index)
  904. # if DEBUG: print "FORMULA strg %r" % strg
  905. elif first_byte == 1:
  906. # boolean formula result
  907. value = BYTES_ORD(result_str[2])
  908. self_put_cell(rowx, colx, XL_CELL_BOOLEAN, value, xf_index)
  909. elif first_byte == 2:
  910. # Error in cell
  911. value = BYTES_ORD(result_str[2])
  912. self_put_cell(rowx, colx, XL_CELL_ERROR, value, xf_index)
  913. elif first_byte == 3:
  914. # empty ... i.e. empty (zero-length) string, NOT an empty cell.
  915. self_put_cell(rowx, colx, XL_CELL_TEXT, "", xf_index)
  916. else:
  917. raise XLRDError("unexpected special case (0x%02x) in FORMULA" % first_byte)
  918. else:
  919. # it is a number
  920. d = local_unpack('<d', result_str)[0]
  921. self_put_cell(rowx, colx, None, d, xf_index)
  922. elif rc == XL_BOOLERR:
  923. rowx, colx, xf_index, value, is_err = local_unpack('<HHHBB', data[:8])
  924. # Note OOo Calc 2.0 writes 9-byte BOOLERR records.
  925. # OOo docs say 8. Excel writes 8.
  926. cellty = (XL_CELL_BOOLEAN, XL_CELL_ERROR)[is_err]
  927. # if DEBUG: print "XL_BOOLERR", rowx, colx, xf_index, value, is_err
  928. self_put_cell(rowx, colx, cellty, value, xf_index)
  929. elif rc == XL_COLINFO:
  930. if not fmt_info: continue
  931. c = Colinfo()
  932. first_colx, last_colx, c.width, c.xf_index, flags \
  933. = local_unpack("<HHHHH", data[:10])
  934. #### Colinfo.width is denominated in 256ths of a character,
  935. #### *not* in characters.
  936. if not(0 <= first_colx <= last_colx <= 256):
  937. # Note: 256 instead of 255 is a common mistake.
  938. # We silently ignore the non-existing 257th column in that case.
  939. print("*** NOTE: COLINFO record has first col index %d, last %d; "
  940. "should have 0 <= first <= last <= 255 -- record ignored!"
  941. % (first_colx, last_colx), file=self.logfile)
  942. del c
  943. continue
  944. upkbits(c, flags, (
  945. ( 0, 0x0001, 'hidden'),
  946. ( 1, 0x0002, 'bit1_flag'),
  947. # *ALL* colinfos created by Excel in "default" cases are 0x0002!!
  948. # Maybe it's "locked" by analogy with XFProtection data.
  949. ( 8, 0x0700, 'outline_level'),
  950. (12, 0x1000, 'collapsed'),
  951. ))
  952. for colx in xrange(first_colx, last_colx+1):
  953. if colx > 255: break # Excel does 0 to 256 inclusive
  954. self.colinfo_map[colx] = c
  955. if 0:
  956. fprintf(self.logfile,
  957. "**COL %d %d %d\n",
  958. self.number, colx, c.xf_index)
  959. if blah:
  960. fprintf(
  961. self.logfile,
  962. "COLINFO sheet #%d cols %d-%d: wid=%d xf_index=%d flags=0x%04x\n",
  963. self.number, first_colx, last_colx, c.width, c.xf_index, flags,
  964. )
  965. c.dump(self.logfile, header='===')
  966. elif rc == XL_DEFCOLWIDTH:
  967. self.defcolwidth, = local_unpack("<H", data[:2])
  968. if 0: print('DEFCOLWIDTH', self.defcolwidth, file=self.logfile)
  969. elif rc == XL_STANDARDWIDTH:
  970. if data_len != 2:
  971. print('*** ERROR *** STANDARDWIDTH', data_len, repr(data), file=self.logfile)
  972. self.standardwidth, = local_unpack("<H", data[:2])
  973. if 0: print('STANDARDWIDTH', self.standardwidth, file=self.logfile)
  974. elif rc == XL_GCW:
  975. if not fmt_info: continue # useless w/o COLINFO
  976. assert data_len == 34
  977. assert data[0:2] == b"\x20\x00"
  978. iguff = unpack("<8i", data[2:34])
  979. gcw = []
  980. for bits in iguff:
  981. for j in xrange(32):
  982. gcw.append(bits & 1)
  983. bits >>= 1
  984. self.gcw = tuple(gcw)
  985. if 0:
  986. showgcw = "".join(map(lambda x: "F "[x], gcw)).rstrip().replace(' ', '.')
  987. print("GCW:", showgcw, file=self.logfile)
  988. elif rc == XL_BLANK:
  989. if not fmt_info: continue
  990. rowx, colx, xf_index = local_unpack('<HHH', data[:6])
  991. # if 0: print >> self.logfile, "BLANK", rowx, colx, xf_index
  992. self_put_cell(rowx, colx, XL_CELL_BLANK, '', xf_index)
  993. elif rc == XL_MULBLANK: # 00BE
  994. if not fmt_info: continue
  995. nitems = data_len >> 1
  996. result = local_unpack("<%dH" % nitems, data)
  997. rowx, mul_first = result[:2]
  998. mul_last = result[-1]
  999. # print >> self.logfile, "MULBLANK", rowx, mul_first, mul_last, data_len, nitems, mul_last + 4 - mul_first
  1000. assert nitems == mul_last + 4 - mul_first
  1001. pos = 2
  1002. for colx in xrange(mul_first, mul_last + 1):
  1003. self_put_cell(rowx, colx, XL_CELL_BLANK, '', result[pos])
  1004. pos += 1
  1005. elif rc == XL_DIMENSION or rc == XL_DIMENSION2:
  1006. if data_len == 0:
  1007. # Four zero bytes after some other record. See github issue 64.
  1008. continue
  1009. # if data_len == 10:
  1010. # Was crashing on BIFF 4.0 file w/o the two trailing unused bytes.
  1011. # Reported by Ralph Heimburger.
  1012. if bv < 80:
  1013. dim_tuple = local_unpack('<HxxH', data[2:8])
  1014. else:
  1015. dim_tuple = local_unpack('<ixxH', data[4:12])
  1016. self.nrows, self.ncols = 0, 0
  1017. self._dimnrows, self._dimncols = dim_tuple
  1018. if bv in (21, 30, 40) and self.book.xf_list and not self.book._xf_epilogue_done:
  1019. self.book.xf_epilogue()
  1020. if blah:
  1021. fprintf(
  1022. self.logfile,
  1023. "sheet %d(%r) DIMENSIONS: ncols=%d nrows=%d\n",
  1024. self.number, self.name, self._dimncols, self._dimnrows
  1025. )
  1026. elif rc == XL_HLINK:
  1027. self.handle_hlink(data)
  1028. elif rc == XL_QUICKTIP:
  1029. self.handle_quicktip(data)
  1030. elif rc == XL_EOF:
  1031. DEBUG = 0
  1032. if DEBUG: print("SHEET.READ: EOF", file=self.logfile)
  1033. eof_found = 1
  1034. break
  1035. elif rc == XL_OBJ:
  1036. # handle SHEET-level objects; note there's a separate Book.handle_obj
  1037. saved_obj = self.handle_obj(data)
  1038. if saved_obj: saved_obj_id = saved_obj.id
  1039. else: saved_obj_id = None
  1040. elif rc == XL_MSO_DRAWING:
  1041. self.handle_msodrawingetc(rc, data_len, data)
  1042. elif rc == XL_TXO:
  1043. txo = self.handle_txo(data)
  1044. if txo and saved_obj_id:
  1045. txos[saved_obj_id] = txo
  1046. saved_obj_id = None
  1047. elif rc == XL_NOTE:
  1048. self.handle_note(data, txos)
  1049. elif rc == XL_FEAT11:
  1050. self.handle_feat11(data)
  1051. elif rc in bofcodes: ##### EMBEDDED BOF #####
  1052. version, boftype = local_unpack('<HH', data[0:4])
  1053. if boftype != 0x20: # embedded chart
  1054. print("*** Unexpected embedded BOF (0x%04x) at offset %d: version=0x%04x type=0x%04x"
  1055. % (rc, bk._position - data_len - 4, version, boftype), file=self.logfile)
  1056. while 1:
  1057. code, data_len, data = bk.get_record_parts()
  1058. if code == XL_EOF:
  1059. break
  1060. if DEBUG: print("---> found EOF", file=self.logfile)
  1061. elif rc == XL_COUNTRY:
  1062. bk.handle_country(data)
  1063. elif rc == XL_LABELRANGES:
  1064. pos = 0
  1065. pos = unpack_cell_range_address_list_update_pos(
  1066. self.row_label_ranges, data, pos, bv, addr_size=8,
  1067. )
  1068. pos = unpack_cell_range_address_list_update_pos(
  1069. self.col_label_ranges, data, pos, bv, addr_size=8,
  1070. )
  1071. assert pos == data_len
  1072. elif rc == XL_ARRAY:
  1073. row1x, rownx, col1x, colnx, array_flags, tokslen = \
  1074. local_unpack("<HHBBBxxxxxH", data[:14])
  1075. if blah_formulas:
  1076. print("ARRAY:", row1x, rownx, col1x, colnx, array_flags, file=self.logfile)
  1077. # dump_formula(bk, data[14:], tokslen, bv, reldelta=0, blah=1)
  1078. elif rc == XL_SHRFMLA:
  1079. row1x, rownx, col1x, colnx, nfmlas, tokslen = \
  1080. local_unpack("<HHBBxBH", data[:10])
  1081. if blah_formulas:
  1082. print("SHRFMLA (main):", row1x, rownx, col1x, colnx, nfmlas, file=self.logfile)
  1083. decompile_formula(bk, data[10:], tokslen, FMLA_TYPE_SHARED,
  1084. blah=1, browx=rowx, bcolx=colx, r1c1=r1c1)
  1085. elif rc == XL_CONDFMT:
  1086. if not fmt_info: continue
  1087. assert bv >= 80
  1088. num_CFs, needs_recalc, browx1, browx2, bcolx1, bcolx2 = \
  1089. unpack("<6H", data[0:12])
  1090. if self.verbosity >= 1:
  1091. fprintf(
  1092. self.logfile,
  1093. "\n*** WARNING: Ignoring CONDFMT (conditional formatting) record\n"
  1094. "*** in Sheet %d (%r).\n"
  1095. "*** %d CF record(s); needs_recalc_or_redraw = %d\n"
  1096. "*** Bounding box is %s\n",
  1097. self.number, self.name, num_CFs, needs_recalc,
  1098. rangename2d(browx1, browx2+1, bcolx1, bcolx2+1),
  1099. )
  1100. olist = [] # updated by the function
  1101. pos = unpack_cell_range_address_list_update_pos(
  1102. olist, data, 12, bv, addr_size=8)
  1103. # print >> self.logfile, repr(result), len(result)
  1104. if self.verbosity >= 1:
  1105. fprintf(
  1106. self.logfile,
  1107. "*** %d individual range(s):\n"
  1108. "*** %s\n",
  1109. len(olist),
  1110. ", ".join(rangename2d(*coords) for coords in olist),
  1111. )
  1112. elif rc == XL_CF:
  1113. if not fmt_info: continue
  1114. cf_type, cmp_op, sz1, sz2, flags = unpack("<BBHHi", data[0:10])
  1115. font_block = (flags >> 26) & 1
  1116. bord_block = (flags >> 28) & 1
  1117. patt_block = (flags >> 29) & 1
  1118. if self.verbosity >= 1:
  1119. fprintf(
  1120. self.logfile,
  1121. "\n*** WARNING: Ignoring CF (conditional formatting) sub-record.\n"
  1122. "*** cf_type=%d, cmp_op=%d, sz1=%d, sz2=%d, flags=0x%08x\n"
  1123. "*** optional data blocks: font=%d, border=%d, pattern=%d\n",
  1124. cf_type, cmp_op, sz1, sz2, flags,
  1125. font_block, bord_block, patt_block,
  1126. )
  1127. # hex_char_dump(data, 0, data_len, fout=self.logfile)
  1128. pos = 12
  1129. if font_block:
  1130. (font_height, font_options, weight, escapement, underline,
  1131. font_colour_index, two_bits, font_esc, font_underl) = unpack("<64x i i H H B 3x i 4x i i i 18x", data[pos:pos+118])
  1132. font_style = (two_bits > 1) & 1
  1133. posture = (font_options > 1) & 1
  1134. font_canc = (two_bits > 7) & 1
  1135. cancellation = (font_options > 7) & 1
  1136. if self.verbosity >= 1:
  1137. fprintf(
  1138. self.logfile,
  1139. "*** Font info: height=%d, weight=%d, escapement=%d,\n"
  1140. "*** underline=%d, colour_index=%d, esc=%d, underl=%d,\n"
  1141. "*** style=%d, posture=%d, canc=%d, cancellation=%d\n",
  1142. font_height, weight, escapement, underline,
  1143. font_colour_index, font_esc, font_underl,
  1144. font_style, posture, font_canc, cancellation,
  1145. )
  1146. pos += 118
  1147. if bord_block:
  1148. pos += 8
  1149. if patt_block:
  1150. pos += 4
  1151. fmla1 = data[pos:pos+sz1]
  1152. pos += sz1
  1153. if blah and sz1:
  1154. fprintf(self.logfile, "*** formula 1:\n")
  1155. dump_formula(bk, fmla1, sz1, bv, reldelta=0, blah=1)
  1156. fmla2 = data[pos:pos+sz2]
  1157. pos += sz2
  1158. assert pos == data_len
  1159. if blah and sz2:
  1160. fprintf(self.logfile, "*** formula 2:\n")
  1161. dump_formula(bk, fmla2, sz2, bv, reldelta=0, blah=1)
  1162. elif rc == XL_DEFAULTROWHEIGHT:
  1163. if data_len == 4:
  1164. bits, self.default_row_height = unpack("<HH", data[:4])
  1165. elif data_len == 2:
  1166. self.default_row_height, = unpack("<H", data)
  1167. bits = 0
  1168. fprintf(self.logfile,
  1169. "*** WARNING: DEFAULTROWHEIGHT record len is 2, "
  1170. "should be 4; assuming BIFF2 format\n")
  1171. else:
  1172. bits = 0
  1173. fprintf(self.logfile,
  1174. "*** WARNING: DEFAULTROWHEIGHT record len is %d, "
  1175. "should be 4; ignoring this record\n",
  1176. data_len)
  1177. self.default_row_height_mismatch = bits & 1
  1178. self.default_row_hidden = (bits >> 1) & 1
  1179. self.default_additional_space_above = (bits >> 2) & 1
  1180. self.default_additional_space_below = (bits >> 3) & 1
  1181. elif rc == XL_MERGEDCELLS:
  1182. if not fmt_info: continue
  1183. pos = unpack_cell_range_address_list_update_pos(
  1184. self.merged_cells, data, 0, bv, addr_size=8)
  1185. if blah:
  1186. fprintf(self.logfile,
  1187. "MERGEDCELLS: %d ranges\n", (pos - 2) // 8)
  1188. assert pos == data_len, \
  1189. "MERGEDCELLS: pos=%d data_len=%d" % (pos, data_len)
  1190. elif rc == XL_WINDOW2:
  1191. if bv >= 80 and data_len >= 14:
  1192. (
  1193. options,
  1194. self.first_visible_rowx, self.first_visible_colx,
  1195. self.gridline_colour_index,
  1196. self.cached_page_break_preview_mag_factor,
  1197. self.cached_normal_view_mag_factor
  1198. ) = unpack("<HHHHxxHH", data[:14])
  1199. else:
  1200. assert bv >= 30 # BIFF3-7
  1201. (
  1202. options,
  1203. self.first_visible_rowx, self.first_visible_colx,
  1204. ) = unpack("<HHH", data[:6])
  1205. self.gridline_colour_rgb = unpack("<BBB", data[6:9])
  1206. self.gridline_colour_index = nearest_colour_index(
  1207. self.book.colour_map, self.gridline_colour_rgb, debug=0)
  1208. # options -- Bit, Mask, Contents:
  1209. # 0 0001H 0 = Show formula results 1 = Show formulas
  1210. # 1 0002H 0 = Do not show grid lines 1 = Show grid lines
  1211. # 2 0004H 0 = Do not show sheet headers 1 = Show sheet headers
  1212. # 3 0008H 0 = Panes are not frozen 1 = Panes are frozen (freeze)
  1213. # 4 0010H 0 = Show zero values as empty cells 1 = Show zero values
  1214. # 5 0020H 0 = Manual grid line colour 1 = Automatic grid line colour
  1215. # 6 0040H 0 = Columns from left to right 1 = Columns from right to left
  1216. # 7 0080H 0 = Do not show outline symbols 1 = Show outline symbols
  1217. # 8 0100H 0 = Keep splits if pane freeze is removed 1 = Remove splits if pane freeze is removed
  1218. # 9 0200H 0 = Sheet not selected 1 = Sheet selected (BIFF5-BIFF8)
  1219. # 10 0400H 0 = Sheet not visible 1 = Sheet visible (BIFF5-BIFF8)
  1220. # 11 0800H 0 = Show in normal view 1 = Show in page break preview (BIFF8)
  1221. # The freeze flag specifies, if a following PANE record (6.71) describes unfrozen or frozen panes.
  1222. for attr, _unused_defval in _WINDOW2_options:
  1223. setattr(self, attr, options & 1)
  1224. options >>= 1
  1225. elif rc == XL_SCL:
  1226. num, den = unpack("<HH", data)
  1227. result = 0
  1228. if den:
  1229. result = (num * 100) // den
  1230. if not(10 <= result <= 400):
  1231. if DEBUG or self.verbosity >= 0:
  1232. print(
  1233. "WARNING *** SCL rcd sheet %d: should have 0.1 <= num/den <= 4; got %d/%d"
  1234. % (self.number, num, den),
  1235. file=self.logfile,
  1236. )
  1237. result = 100
  1238. self.scl_mag_factor = result
  1239. elif rc == XL_PANE:
  1240. (
  1241. self.vert_split_pos,
  1242. self.horz_split_pos,
  1243. self.horz_split_first_visible,
  1244. self.vert_split_first_visible,
  1245. self.split_active_pane,
  1246. ) = unpack("<HHHHB", data[:9])
  1247. self.has_pane_record = 1
  1248. elif rc == XL_HORIZONTALPAGEBREAKS:
  1249. if not fmt_info: continue
  1250. num_breaks, = local_unpack("<H", data[:2])
  1251. assert num_breaks * (2 + 4 * (bv >= 80)) + 2 == data_len
  1252. pos = 2
  1253. if bv < 80:
  1254. while pos < data_len:
  1255. self.horizontal_page_breaks.append((local_unpack("<H", data[pos:pos+2])[0], 0, 255))
  1256. pos += 2
  1257. else:
  1258. while pos < data_len:
  1259. self.horizontal_page_breaks.append(local_unpack("<HHH", data[pos:pos+6]))
  1260. pos += 6
  1261. elif rc == XL_VERTICALPAGEBREAKS:
  1262. if not fmt_info: continue
  1263. num_breaks, = local_unpack("<H", data[:2])
  1264. assert num_breaks * (2 + 4 * (bv >= 80)) + 2 == data_len
  1265. pos = 2
  1266. if bv < 80:
  1267. while pos < data_len:
  1268. self.vertical_page_breaks.append((local_unpack("<H", data[pos:pos+2])[0], 0, 65535))
  1269. pos += 2
  1270. else:
  1271. while pos < data_len:
  1272. self.vertical_page_breaks.append(local_unpack("<HHH", data[pos:pos+6]))
  1273. pos += 6
  1274. #### all of the following are for BIFF <= 4W
  1275. elif bv <= 45:
  1276. if rc == XL_FORMAT or rc == XL_FORMAT2:
  1277. bk.handle_format(data, rc)
  1278. elif rc == XL_FONT or rc == XL_FONT_B3B4:
  1279. bk.handle_font(data)
  1280. elif rc == XL_STYLE:
  1281. if not self.book._xf_epilogue_done:
  1282. self.book.xf_epilogue()
  1283. bk.handle_style(data)
  1284. elif rc == XL_PALETTE:
  1285. bk.handle_palette(data)
  1286. elif rc == XL_BUILTINFMTCOUNT:
  1287. bk.handle_builtinfmtcount(data)
  1288. elif rc == XL_XF4 or rc == XL_XF3 or rc == XL_XF2: #### N.B. not XL_XF
  1289. bk.handle_xf(data)
  1290. elif rc == XL_DATEMODE:
  1291. bk.handle_datemode(data)
  1292. elif rc == XL_CODEPAGE:
  1293. bk.handle_codepage(data)
  1294. elif rc == XL_FILEPASS:
  1295. bk.handle_filepass(data)
  1296. elif rc == XL_WRITEACCESS:
  1297. bk.handle_writeaccess(data)
  1298. elif rc == XL_IXFE:
  1299. self._ixfe = local_unpack('<H', data)[0]
  1300. elif rc == XL_NUMBER_B2:
  1301. rowx, colx, cell_attr, d = local_unpack('<HH3sd', data)
  1302. self_put_cell(rowx, colx, None, d, self.fixed_BIFF2_xfindex(cell_attr, rowx, colx))
  1303. elif rc == XL_INTEGER:
  1304. rowx, colx, cell_attr, d = local_unpack('<HH3sH', data)
  1305. self_put_cell(rowx, colx, None, float(d), self.fixed_BIFF2_xfindex(cell_attr, rowx, colx))
  1306. elif rc == XL_LABEL_B2:
  1307. rowx, colx, cell_attr = local_unpack('<HH3s', data[0:7])
  1308. strg = unpack_string(data, 7, bk.encoding or bk.derive_encoding(), lenlen=1)
  1309. self_put_cell(rowx, colx, XL_CELL_TEXT, strg, self.fixed_BIFF2_xfindex(cell_attr, rowx, colx))
  1310. elif rc == XL_BOOLERR_B2:
  1311. rowx, colx, cell_attr, value, is_err = local_unpack('<HH3sBB', data)
  1312. cellty = (XL_CELL_BOOLEAN, XL_CELL_ERROR)[is_err]
  1313. # if DEBUG: print "XL_BOOLERR_B2", rowx, colx, cell_attr, value, is_err
  1314. self_put_cell(rowx, colx, cellty, value, self.fixed_BIFF2_xfindex(cell_attr, rowx, colx))
  1315. elif rc == XL_BLANK_B2:
  1316. if not fmt_info: continue
  1317. rowx, colx, cell_attr = local_unpack('<HH3s', data[:7])
  1318. self_put_cell(rowx, colx, XL_CELL_BLANK, '', self.fixed_BIFF2_xfindex(cell_attr, rowx, colx))
  1319. elif rc == XL_EFONT:
  1320. bk.handle_efont(data)
  1321. elif rc == XL_ROW_B2:
  1322. if not fmt_info: continue
  1323. rowx, bits1, bits2 = local_unpack('<H4xH2xB', data[0:11])
  1324. if not(0 <= rowx < self.utter_max_rows):
  1325. print("*** NOTE: ROW_B2 record has row index %d; "
  1326. "should have 0 <= rowx < %d -- record ignored!"
  1327. % (rowx, self.utter_max_rows), file=self.logfile)
  1328. continue
  1329. if not (bits2 & 1): # has_default_xf_index is false
  1330. xf_index = -1
  1331. elif data_len == 18:
  1332. # Seems the XF index in the cell_attr is dodgy
  1333. xfx = local_unpack('<H', data[16:18])[0]
  1334. xf_index = self.fixed_BIFF2_xfindex(cell_attr=None, rowx=rowx, colx=-1, true_xfx=xfx)
  1335. else:
  1336. cell_attr = data[13:16]
  1337. xf_index = self.fixed_BIFF2_xfindex(cell_attr, rowx, colx=-1)
  1338. key = (bits1, bits2, xf_index)
  1339. r = rowinfo_sharing_dict.get(key)
  1340. if r is None:
  1341. rowinfo_sharing_dict[key] = r = Rowinfo()
  1342. r.height = bits1 & 0x7fff
  1343. r.has_default_height = (bits1 >> 15) & 1
  1344. r.has_default_xf_index = bits2 & 1
  1345. r.xf_index = xf_index
  1346. # r.outline_level = 0 # set in __init__
  1347. # r.outline_group_starts_ends = 0 # set in __init__
  1348. # r.hidden = 0 # set in __init__
  1349. # r.height_mismatch = 0 # set in __init__
  1350. # r.additional_space_above = 0 # set in __init__
  1351. # r.additional_space_below = 0 # set in __init__
  1352. self.rowinfo_map[rowx] = r
  1353. if 0 and r.xf_index > -1:
  1354. fprintf(self.logfile,
  1355. "**ROW %d %d %d\n",
  1356. self.number, rowx, r.xf_index)
  1357. if blah_rows:
  1358. print('ROW_B2', rowx, bits1, file=self.logfile)
  1359. r.dump(self.logfile,
  1360. header="--- sh #%d, rowx=%d ---" % (self.number, rowx))
  1361. elif rc == XL_COLWIDTH: # BIFF2 only
  1362. if not fmt_info: continue
  1363. first_colx, last_colx, width\
  1364. = local_unpack("<BBH", data[:4])
  1365. if not(first_colx <= last_colx):
  1366. print("*** NOTE: COLWIDTH record has first col index %d, last %d; "
  1367. "should have first <= last -- record ignored!"
  1368. % (first_colx, last_colx), file=self.logfile)
  1369. continue
  1370. for colx in xrange(first_colx, last_colx+1):
  1371. if colx in self.colinfo_map:
  1372. c = self.colinfo_map[colx]
  1373. else:
  1374. c = Colinfo()
  1375. self.colinfo_map[colx] = c
  1376. c.width = width
  1377. if blah:
  1378. fprintf(
  1379. self.logfile,
  1380. "COLWIDTH sheet #%d cols %d-%d: wid=%d\n",
  1381. self.number, first_colx, last_colx, width,
  1382. )
  1383. elif rc == XL_COLUMNDEFAULT: # BIFF2 only
  1384. if not fmt_info: continue
  1385. first_colx, last_colx = local_unpack("<HH", data[:4])
  1386. #### Warning OOo docs wrong; first_colx <= colx < last_colx
  1387. if blah:
  1388. fprintf(
  1389. self.logfile,
  1390. "COLUMNDEFAULT sheet #%d cols in range(%d, %d)\n",
  1391. self.number, first_colx, last_colx,
  1392. )
  1393. if not(0 <= first_colx < last_colx <= 256):
  1394. print("*** NOTE: COLUMNDEFAULT record has first col index %d, last %d; "
  1395. "should have 0 <= first < last <= 256"
  1396. % (first_colx, last_colx), file=self.logfile)
  1397. last_colx = min(last_colx, 256)
  1398. for colx in xrange(first_colx, last_colx):
  1399. offset = 4 + 3 * (colx - first_colx)
  1400. cell_attr = data[offset:offset+3]
  1401. xf_index = self.fixed_BIFF2_xfindex(cell_attr, rowx=-1, colx=colx)
  1402. if colx in self.colinfo_map:
  1403. c = self.colinfo_map[colx]
  1404. else:
  1405. c = Colinfo()
  1406. self.colinfo_map[colx] = c
  1407. c.xf_index = xf_index
  1408. elif rc == XL_WINDOW2_B2: # BIFF 2 only
  1409. attr_names = ("show_formulas", "show_grid_lines", "show_sheet_headers",
  1410. "panes_are_frozen", "show_zero_values")
  1411. for attr, char in zip(attr_names, data[0:5]):
  1412. setattr(self, attr, int(char != b'\0'))
  1413. (
  1414. self.first_visible_rowx, self.first_visible_colx,
  1415. self.automatic_grid_line_colour,
  1416. ) = unpack("<HHB", data[5:10])
  1417. self.gridline_colour_rgb = unpack("<BBB", data[10:13])
  1418. self.gridline_colour_index = nearest_colour_index(
  1419. self.book.colour_map, self.gridline_colour_rgb, debug=0)
  1420. else:
  1421. # if DEBUG: print "SHEET.READ: Unhandled record type %02x %d bytes %r" % (rc, data_len, data)
  1422. pass
  1423. if not eof_found:
  1424. raise XLRDError("Sheet %d (%r) missing EOF record"
  1425. % (self.number, self.name))
  1426. self.tidy_dimensions()
  1427. self.update_cooked_mag_factors()
  1428. bk._position = oldpos
  1429. return 1
  1430. def string_record_contents(self, data):
  1431. bv = self.biff_version
  1432. bk = self.book
  1433. lenlen = (bv >= 30) + 1
  1434. nchars_expected = unpack("<" + "BH"[lenlen - 1], data[:lenlen])[0]
  1435. offset = lenlen
  1436. if bv < 80:
  1437. enc = bk.encoding or bk.derive_encoding()
  1438. nchars_found = 0
  1439. result = UNICODE_LITERAL("")
  1440. while 1:
  1441. if bv >= 80:
  1442. flag = BYTES_ORD(data[offset]) & 1
  1443. enc = ("latin_1", "utf_16_le")[flag]
  1444. offset += 1
  1445. chunk = unicode(data[offset:], enc)
  1446. result += chunk
  1447. nchars_found += len(chunk)
  1448. if nchars_found == nchars_expected:
  1449. return result
  1450. if nchars_found > nchars_expected:
  1451. msg = ("STRING/CONTINUE: expected %d chars, found %d"
  1452. % (nchars_expected, nchars_found))
  1453. raise XLRDError(msg)
  1454. rc, _unused_len, data = bk.get_record_parts()
  1455. if rc != XL_CONTINUE:
  1456. raise XLRDError(
  1457. "Expected CONTINUE record; found record-type 0x%04X" % rc)
  1458. offset = 0
  1459. def update_cooked_mag_factors(self):
  1460. # Cached values are used ONLY for the non-active view mode.
  1461. # When the user switches to the non-active view mode,
  1462. # if the cached value for that mode is not valid,
  1463. # Excel pops up a window which says:
  1464. # "The number must be between 10 and 400. Try again by entering a number in this range."
  1465. # When the user hits OK, it drops into the non-active view mode
  1466. # but uses the magn from the active mode.
  1467. # NOTE: definition of "valid" depends on mode ... see below
  1468. blah = DEBUG or self.verbosity > 0
  1469. if self.show_in_page_break_preview:
  1470. if self.scl_mag_factor is None: # no SCL record
  1471. self.cooked_page_break_preview_mag_factor = 100 # Yes, 100, not 60, NOT a typo
  1472. else:
  1473. self.cooked_page_break_preview_mag_factor = self.scl_mag_factor
  1474. zoom = self.cached_normal_view_mag_factor
  1475. if not (10 <= zoom <=400):
  1476. if blah:
  1477. print(
  1478. "WARNING *** WINDOW2 rcd sheet %d: Bad cached_normal_view_mag_factor: %d"
  1479. % (self.number, self.cached_normal_view_mag_factor),
  1480. file=self.logfile,
  1481. )
  1482. zoom = self.cooked_page_break_preview_mag_factor
  1483. self.cooked_normal_view_mag_factor = zoom
  1484. else:
  1485. # normal view mode
  1486. if self.scl_mag_factor is None: # no SCL record
  1487. self.cooked_normal_view_mag_factor = 100
  1488. else:
  1489. self.cooked_normal_view_mag_factor = self.scl_mag_factor
  1490. zoom = self.cached_page_break_preview_mag_factor
  1491. if not zoom:
  1492. # VALID, defaults to 60
  1493. zoom = 60
  1494. elif not (10 <= zoom <= 400):
  1495. if blah:
  1496. print(
  1497. "WARNING *** WINDOW2 rcd sheet %r: Bad cached_page_break_preview_mag_factor: %r"
  1498. % (self.number, self.cached_page_break_preview_mag_factor),
  1499. file=self.logfile,
  1500. )
  1501. zoom = self.cooked_normal_view_mag_factor
  1502. self.cooked_page_break_preview_mag_factor = zoom
  1503. def fixed_BIFF2_xfindex(self, cell_attr, rowx, colx, true_xfx=None):
  1504. DEBUG = 0
  1505. blah = DEBUG or self.verbosity >= 2
  1506. if self.biff_version == 21:
  1507. if self.book.xf_list:
  1508. if true_xfx is not None:
  1509. xfx = true_xfx
  1510. else:
  1511. xfx = BYTES_ORD(cell_attr[0]) & 0x3F
  1512. if xfx == 0x3F:
  1513. if self._ixfe is None:
  1514. raise XLRDError("BIFF2 cell record has XF index 63 but no preceding IXFE record.")
  1515. xfx = self._ixfe
  1516. # OOo docs are capable of interpretation that each
  1517. # cell record is preceded immediately by its own IXFE record.
  1518. # Empirical evidence is that (sensibly) an IXFE record applies to all
  1519. # following cell records until another IXFE comes along.
  1520. return xfx
  1521. # Have either Excel 2.0, or broken 2.1 w/o XF records -- same effect.
  1522. self.biff_version = self.book.biff_version = 20
  1523. #### check that XF slot in cell_attr is zero
  1524. xfx_slot = BYTES_ORD(cell_attr[0]) & 0x3F
  1525. assert xfx_slot == 0
  1526. xfx = self._cell_attr_to_xfx.get(cell_attr)
  1527. if xfx is not None:
  1528. return xfx
  1529. if blah:
  1530. fprintf(self.logfile, "New cell_attr %r at (%r, %r)\n", cell_attr, rowx, colx)
  1531. if not self.book.xf_list:
  1532. for xfx in xrange(16):
  1533. self.insert_new_BIFF20_xf(cell_attr=b"\x40\x00\x00", style=xfx < 15)
  1534. xfx = self.insert_new_BIFF20_xf(cell_attr=cell_attr)
  1535. return xfx
  1536. def insert_new_BIFF20_xf(self, cell_attr, style=0):
  1537. DEBUG = 0
  1538. blah = DEBUG or self.verbosity >= 2
  1539. book = self.book
  1540. xfx = len(book.xf_list)
  1541. xf = self.fake_XF_from_BIFF20_cell_attr(cell_attr, style)
  1542. xf.xf_index = xfx
  1543. book.xf_list.append(xf)
  1544. if blah:
  1545. xf.dump(self.logfile, header="=== Faked XF %d ===" % xfx, footer="======")
  1546. if xf.format_key not in book.format_map:
  1547. if xf.format_key:
  1548. msg = "ERROR *** XF[%d] unknown format key (%d, 0x%04x)\n"
  1549. fprintf(self.logfile, msg,
  1550. xf.xf_index, xf.format_key, xf.format_key)
  1551. fmt = Format(xf.format_key, FUN, UNICODE_LITERAL("General"))
  1552. book.format_map[xf.format_key] = fmt
  1553. book.format_list.append(fmt)
  1554. cellty_from_fmtty = {
  1555. FNU: XL_CELL_NUMBER,
  1556. FUN: XL_CELL_NUMBER,
  1557. FGE: XL_CELL_NUMBER,
  1558. FDT: XL_CELL_DATE,
  1559. FTX: XL_CELL_NUMBER, # Yes, a number can be formatted as text.
  1560. }
  1561. fmt = book.format_map[xf.format_key]
  1562. cellty = cellty_from_fmtty[fmt.type]
  1563. self._xf_index_to_xl_type_map[xf.xf_index] = cellty
  1564. self._cell_attr_to_xfx[cell_attr] = xfx
  1565. return xfx
  1566. def fake_XF_from_BIFF20_cell_attr(self, cell_attr, style=0):
  1567. from .formatting import XF, XFAlignment, XFBorder, XFBackground, XFProtection
  1568. xf = XF()
  1569. xf.alignment = XFAlignment()
  1570. xf.alignment.indent_level = 0
  1571. xf.alignment.shrink_to_fit = 0
  1572. xf.alignment.text_direction = 0
  1573. xf.border = XFBorder()
  1574. xf.border.diag_up = 0
  1575. xf.border.diag_down = 0
  1576. xf.border.diag_colour_index = 0
  1577. xf.border.diag_line_style = 0 # no line
  1578. xf.background = XFBackground()
  1579. xf.protection = XFProtection()
  1580. (prot_bits, font_and_format, halign_etc) = unpack('<BBB', cell_attr)
  1581. xf.format_key = font_and_format & 0x3F
  1582. xf.font_index = (font_and_format & 0xC0) >> 6
  1583. upkbits(xf.protection, prot_bits, (
  1584. (6, 0x40, 'cell_locked'),
  1585. (7, 0x80, 'formula_hidden'),
  1586. ))
  1587. xf.alignment.hor_align = halign_etc & 0x07
  1588. for mask, side in ((0x08, 'left'), (0x10, 'right'), (0x20, 'top'), (0x40, 'bottom')):
  1589. if halign_etc & mask:
  1590. colour_index, line_style = 8, 1 # black, thin
  1591. else:
  1592. colour_index, line_style = 0, 0 # none, none
  1593. setattr(xf.border, side + '_colour_index', colour_index)
  1594. setattr(xf.border, side + '_line_style', line_style)
  1595. bg = xf.background
  1596. if halign_etc & 0x80:
  1597. bg.fill_pattern = 17
  1598. else:
  1599. bg.fill_pattern = 0
  1600. bg.background_colour_index = 9 # white
  1601. bg.pattern_colour_index = 8 # black
  1602. xf.parent_style_index = (0x0FFF, 0)[style]
  1603. xf.alignment.vert_align = 2 # bottom
  1604. xf.alignment.rotation = 0
  1605. attr_stems = [
  1606. 'format',
  1607. 'font',
  1608. 'alignment',
  1609. 'border',
  1610. 'background',
  1611. 'protection',
  1612. ]
  1613. for attr_stem in attr_stems:
  1614. attr = "_" + attr_stem + "_flag"
  1615. setattr(xf, attr, 1)
  1616. return xf
  1617. def req_fmt_info(self):
  1618. if not self.formatting_info:
  1619. raise XLRDError("Feature requires open_workbook(..., formatting_info=True)")
  1620. def computed_column_width(self, colx):
  1621. """
  1622. Determine column display width.
  1623. :param colx:
  1624. Index of the queried column, range 0 to 255.
  1625. Note that it is possible to find out the width that will be used to
  1626. display columns with no cell information e.g. column IV (colx=255).
  1627. :return:
  1628. The column width that will be used for displaying
  1629. the given column by Excel, in units of 1/256th of the width of a
  1630. standard character (the digit zero in the first font).
  1631. .. versionadded:: 0.6.1
  1632. """
  1633. self.req_fmt_info()
  1634. if self.biff_version >= 80:
  1635. colinfo = self.colinfo_map.get(colx, None)
  1636. if colinfo is not None:
  1637. return colinfo.width
  1638. if self.standardwidth is not None:
  1639. return self.standardwidth
  1640. elif self.biff_version >= 40:
  1641. if self.gcw[colx]:
  1642. if self.standardwidth is not None:
  1643. return self.standardwidth
  1644. else:
  1645. colinfo = self.colinfo_map.get(colx, None)
  1646. if colinfo is not None:
  1647. return colinfo.width
  1648. elif self.biff_version == 30:
  1649. colinfo = self.colinfo_map.get(colx, None)
  1650. if colinfo is not None:
  1651. return colinfo.width
  1652. # All roads lead to Rome and the DEFCOLWIDTH ...
  1653. if self.defcolwidth is not None:
  1654. return self.defcolwidth * 256
  1655. return 8 * 256 # 8 is what Excel puts in a DEFCOLWIDTH record
  1656. def handle_hlink(self, data):
  1657. # DEBUG = 1
  1658. if DEBUG: print("\n=== hyperlink ===", file=self.logfile)
  1659. record_size = len(data)
  1660. h = Hyperlink()
  1661. h.frowx, h.lrowx, h.fcolx, h.lcolx, guid0, dummy, options = unpack('<HHHH16s4si', data[:32])
  1662. assert guid0 == b"\xD0\xC9\xEA\x79\xF9\xBA\xCE\x11\x8C\x82\x00\xAA\x00\x4B\xA9\x0B"
  1663. assert dummy == b"\x02\x00\x00\x00"
  1664. if DEBUG: print("options: %08X" % options, file=self.logfile)
  1665. offset = 32
  1666. def get_nul_terminated_unicode(buf, ofs):
  1667. nb = unpack('<L', buf[ofs:ofs+4])[0] * 2
  1668. ofs += 4
  1669. uc = unicode(buf[ofs:ofs+nb], 'UTF-16le')[:-1]
  1670. ofs += nb
  1671. return uc, ofs
  1672. if options & 0x14: # has a description
  1673. h.desc, offset = get_nul_terminated_unicode(data, offset)
  1674. if options & 0x80: # has a target
  1675. h.target, offset = get_nul_terminated_unicode(data, offset)
  1676. if (options & 1) and not (options & 0x100): # HasMoniker and not MonikerSavedAsString
  1677. # an OLEMoniker structure
  1678. clsid, = unpack('<16s', data[offset:offset + 16])
  1679. if DEBUG: fprintf(self.logfile, "clsid=%r\n", clsid)
  1680. offset += 16
  1681. if clsid == b"\xE0\xC9\xEA\x79\xF9\xBA\xCE\x11\x8C\x82\x00\xAA\x00\x4B\xA9\x0B":
  1682. # E0H C9H EAH 79H F9H BAH CEH 11H 8CH 82H 00H AAH 00H 4BH A9H 0BH
  1683. # URL Moniker
  1684. h.type = UNICODE_LITERAL('url')
  1685. nbytes = unpack('<L', data[offset:offset + 4])[0]
  1686. offset += 4
  1687. h.url_or_path = unicode(data[offset:offset + nbytes], 'UTF-16le')
  1688. if DEBUG: fprintf(self.logfile, "initial url=%r len=%d\n", h.url_or_path, len(h.url_or_path))
  1689. endpos = h.url_or_path.find('\x00')
  1690. if DEBUG: print("endpos=%d" % endpos, file=self.logfile)
  1691. h.url_or_path = h.url_or_path[:endpos]
  1692. true_nbytes = 2 * (endpos + 1)
  1693. offset += true_nbytes
  1694. extra_nbytes = nbytes - true_nbytes
  1695. extra_data = data[offset:offset + extra_nbytes]
  1696. offset += extra_nbytes
  1697. if DEBUG:
  1698. fprintf(
  1699. self.logfile,
  1700. "url=%r\nextra=%r\nnbytes=%d true_nbytes=%d extra_nbytes=%d\n",
  1701. h.url_or_path, extra_data, nbytes, true_nbytes, extra_nbytes,
  1702. )
  1703. assert extra_nbytes in (24, 0)
  1704. elif clsid == b"\x03\x03\x00\x00\x00\x00\x00\x00\xC0\x00\x00\x00\x00\x00\x00\x46":
  1705. # file moniker
  1706. h.type = UNICODE_LITERAL('local file')
  1707. uplevels, nbytes = unpack("<Hi", data[offset:offset + 6])
  1708. offset += 6
  1709. shortpath = b"..\\" * uplevels + data[offset:offset + nbytes - 1] #### BYTES, not unicode
  1710. if DEBUG: fprintf(self.logfile, "uplevels=%d shortpath=%r\n", uplevels, shortpath)
  1711. offset += nbytes
  1712. offset += 24 # OOo: "unknown byte sequence"
  1713. # above is version 0xDEAD + 20 reserved zero bytes
  1714. sz = unpack('<i', data[offset:offset + 4])[0]
  1715. if DEBUG: print("sz=%d" % sz, file=self.logfile)
  1716. offset += 4
  1717. if sz:
  1718. xl = unpack('<i', data[offset:offset + 4])[0]
  1719. offset += 4
  1720. offset += 2 # "unknown byte sequence" MS: 0x0003
  1721. extended_path = unicode(data[offset:offset + xl], 'UTF-16le') # not zero-terminated
  1722. offset += xl
  1723. h.url_or_path = extended_path
  1724. else:
  1725. h.url_or_path = shortpath
  1726. #### MS KLUDGE WARNING ####
  1727. # The "shortpath" is bytes encoded in the **UNKNOWN** creator's "ANSI" encoding.
  1728. else:
  1729. fprintf(self.logfile, "*** unknown clsid %r\n", clsid)
  1730. elif options & 0x163 == 0x103: # UNC
  1731. h.type = UNICODE_LITERAL('unc')
  1732. h.url_or_path, offset = get_nul_terminated_unicode(data, offset)
  1733. elif options & 0x16B == 8:
  1734. h.type = UNICODE_LITERAL('workbook')
  1735. else:
  1736. h.type = UNICODE_LITERAL('unknown')
  1737. if options & 0x8: # has textmark
  1738. h.textmark, offset = get_nul_terminated_unicode(data, offset)
  1739. if DEBUG:
  1740. h.dump(header="... object dump ...")
  1741. print("offset=%d record_size=%d" % (offset, record_size))
  1742. extra_nbytes = record_size - offset
  1743. if extra_nbytes > 0:
  1744. fprintf(
  1745. self.logfile,
  1746. "*** WARNING: hyperlink at R%dC%d has %d extra data bytes: %s\n",
  1747. h.frowx + 1,
  1748. h.fcolx + 1,
  1749. extra_nbytes,
  1750. REPR(data[-extra_nbytes:]),
  1751. )
  1752. # Seen: b"\x00\x00" also b"A\x00", b"V\x00"
  1753. elif extra_nbytes < 0:
  1754. raise XLRDError("Bug or corrupt file, send copy of input file for debugging")
  1755. self.hyperlink_list.append(h)
  1756. for rowx in xrange(h.frowx, h.lrowx+1):
  1757. for colx in xrange(h.fcolx, h.lcolx+1):
  1758. self.hyperlink_map[rowx, colx] = h
  1759. def handle_quicktip(self, data):
  1760. rcx, frowx, lrowx, fcolx, lcolx = unpack('<5H', data[:10])
  1761. assert rcx == XL_QUICKTIP
  1762. assert self.hyperlink_list
  1763. h = self.hyperlink_list[-1]
  1764. assert (frowx, lrowx, fcolx, lcolx) == (h.frowx, h.lrowx, h.fcolx, h.lcolx)
  1765. assert data[-2:] == b'\x00\x00'
  1766. h.quicktip = unicode(data[10:-2], 'utf_16_le')
  1767. def handle_msodrawingetc(self, recid, data_len, data):
  1768. if not OBJ_MSO_DEBUG:
  1769. return
  1770. DEBUG = 1
  1771. if self.biff_version < 80:
  1772. return
  1773. o = MSODrawing()
  1774. pos = 0
  1775. while pos < data_len:
  1776. tmp, fbt, cb = unpack('<HHI', data[pos:pos+8])
  1777. ver = tmp & 0xF
  1778. inst = (tmp >> 4) & 0xFFF
  1779. if ver == 0xF:
  1780. ndb = 0 # container
  1781. else:
  1782. ndb = cb
  1783. if DEBUG:
  1784. hex_char_dump(data, pos, ndb + 8, base=0, fout=self.logfile)
  1785. fprintf(self.logfile,
  1786. "fbt:0x%04X inst:%d ver:0x%X cb:%d (0x%04X)\n",
  1787. fbt, inst, ver, cb, cb)
  1788. if fbt == 0xF010: # Client Anchor
  1789. assert ndb == 18
  1790. (o.anchor_unk,
  1791. o.anchor_colx_lo, o.anchor_rowx_lo,
  1792. o.anchor_colx_hi, o.anchor_rowx_hi) = unpack('<Hiiii', data[pos+8:pos+8+ndb])
  1793. elif fbt == 0xF011: # Client Data
  1794. # must be followed by an OBJ record
  1795. assert cb == 0
  1796. assert pos + 8 == data_len
  1797. else:
  1798. pass
  1799. pos += ndb + 8
  1800. else:
  1801. # didn't break out of while loop
  1802. assert pos == data_len
  1803. if DEBUG:
  1804. o.dump(self.logfile, header="=== MSODrawing ===", footer= " ")
  1805. def handle_obj(self, data):
  1806. if self.biff_version < 80:
  1807. return None
  1808. o = MSObj()
  1809. data_len = len(data)
  1810. pos = 0
  1811. if OBJ_MSO_DEBUG:
  1812. fprintf(self.logfile, "... OBJ record len=%d...\n", data_len)
  1813. while pos < data_len:
  1814. ft, cb = unpack('<HH', data[pos:pos+4])
  1815. if OBJ_MSO_DEBUG:
  1816. fprintf(self.logfile, "pos=%d ft=0x%04X cb=%d\n", pos, ft, cb)
  1817. hex_char_dump(data, pos, cb + 4, base=0, fout=self.logfile)
  1818. if pos == 0 and not (ft == 0x15 and cb == 18):
  1819. if self.verbosity:
  1820. fprintf(self.logfile, "*** WARNING Ignoring antique or corrupt OBJECT record\n")
  1821. return None
  1822. if ft == 0x15: # ftCmo ... s/b first
  1823. assert pos == 0
  1824. o.type, o.id, option_flags = unpack('<HHH', data[pos+4:pos+10])
  1825. upkbits(o, option_flags, (
  1826. ( 0, 0x0001, 'locked'),
  1827. ( 4, 0x0010, 'printable'),
  1828. ( 8, 0x0100, 'autofilter'), # not documented in Excel 97 dev kit
  1829. ( 9, 0x0200, 'scrollbar_flag'), # not documented in Excel 97 dev kit
  1830. (13, 0x2000, 'autofill'),
  1831. (14, 0x4000, 'autoline'),
  1832. ))
  1833. elif ft == 0x00:
  1834. if data[pos:data_len] == b'\0' * (data_len - pos):
  1835. # ignore "optional reserved" data at end of record
  1836. break
  1837. msg = "Unexpected data at end of OBJECT record"
  1838. fprintf(self.logfile, "*** ERROR %s\n" % msg)
  1839. hex_char_dump(data, pos, data_len - pos, base=0, fout=self.logfile)
  1840. raise XLRDError(msg)
  1841. elif ft == 0x0C: # Scrollbar
  1842. values = unpack('<5H', data[pos+8:pos+18])
  1843. for value, tag in zip(values, ('value', 'min', 'max', 'inc', 'page')):
  1844. setattr(o, 'scrollbar_' + tag, value)
  1845. elif ft == 0x0D: # "Notes structure" [used for cell comments]
  1846. # not documented in Excel 97 dev kit
  1847. if OBJ_MSO_DEBUG: fprintf(self.logfile, "*** OBJ record has ft==0x0D 'notes' structure\n")
  1848. elif ft == 0x13: # list box data
  1849. if o.autofilter: # non standard exit. NOT documented
  1850. break
  1851. else:
  1852. pass
  1853. pos += cb + 4
  1854. else:
  1855. # didn't break out of while loop
  1856. pass
  1857. if OBJ_MSO_DEBUG:
  1858. o.dump(self.logfile, header="=== MSOBj ===", footer= " ")
  1859. return o
  1860. def handle_note(self, data, txos):
  1861. if OBJ_MSO_DEBUG:
  1862. fprintf(self.logfile, '... NOTE record ...\n')
  1863. hex_char_dump(data, 0, len(data), base=0, fout=self.logfile)
  1864. o = Note()
  1865. data_len = len(data)
  1866. if self.biff_version < 80:
  1867. o.rowx, o.colx, expected_bytes = unpack('<HHH', data[:6])
  1868. nb = len(data) - 6
  1869. assert nb <= expected_bytes
  1870. pieces = [data[6:]]
  1871. expected_bytes -= nb
  1872. while expected_bytes > 0:
  1873. rc2, data2_len, data2 = self.book.get_record_parts()
  1874. assert rc2 == XL_NOTE
  1875. dummy_rowx, nb = unpack('<H2xH', data2[:6])
  1876. assert dummy_rowx == 0xFFFF
  1877. assert nb == data2_len - 6
  1878. pieces.append(data2[6:])
  1879. expected_bytes -= nb
  1880. assert expected_bytes == 0
  1881. enc = self.book.encoding or self.book.derive_encoding()
  1882. o.text = unicode(b''.join(pieces), enc)
  1883. o.rich_text_runlist = [(0, 0)]
  1884. o.show = 0
  1885. o.row_hidden = 0
  1886. o.col_hidden = 0
  1887. o.author = UNICODE_LITERAL('')
  1888. o._object_id = None
  1889. self.cell_note_map[o.rowx, o.colx] = o
  1890. return
  1891. # Excel 8.0+
  1892. o.rowx, o.colx, option_flags, o._object_id = unpack('<4H', data[:8])
  1893. o.show = (option_flags >> 1) & 1
  1894. o.row_hidden = (option_flags >> 7) & 1
  1895. o.col_hidden = (option_flags >> 8) & 1
  1896. # XL97 dev kit book says NULL [sic] bytes padding between string count and string data
  1897. # to ensure that string is word-aligned. Appears to be nonsense.
  1898. o.author, endpos = unpack_unicode_update_pos(data, 8, lenlen=2)
  1899. # There is a random/undefined byte after the author string (not counted in the
  1900. # string length).
  1901. # Issue 4 on github: Google Spreadsheet doesn't write the undefined byte.
  1902. assert (data_len - endpos) in (0, 1)
  1903. if OBJ_MSO_DEBUG:
  1904. o.dump(self.logfile, header="=== Note ===", footer= " ")
  1905. txo = txos.get(o._object_id)
  1906. if txo:
  1907. o.text = txo.text
  1908. o.rich_text_runlist = txo.rich_text_runlist
  1909. self.cell_note_map[o.rowx, o.colx] = o
  1910. def handle_txo(self, data):
  1911. if self.biff_version < 80:
  1912. return
  1913. o = MSTxo()
  1914. fmt = '<HH6sHHH'
  1915. fmtsize = calcsize(fmt)
  1916. option_flags, o.rot, controlInfo, cchText, cbRuns, o.ifntEmpty = unpack(fmt, data[:fmtsize])
  1917. o.fmla = data[fmtsize:]
  1918. upkbits(o, option_flags, (
  1919. ( 3, 0x000E, 'horz_align'),
  1920. ( 6, 0x0070, 'vert_align'),
  1921. ( 9, 0x0200, 'lock_text'),
  1922. (14, 0x4000, 'just_last'),
  1923. (15, 0x8000, 'secret_edit'),
  1924. ))
  1925. totchars = 0
  1926. o.text = UNICODE_LITERAL('')
  1927. while totchars < cchText:
  1928. rc2, data2_len, data2 = self.book.get_record_parts()
  1929. assert rc2 == XL_CONTINUE
  1930. if OBJ_MSO_DEBUG:
  1931. hex_char_dump(data2, 0, data2_len, base=0, fout=self.logfile)
  1932. nb = BYTES_ORD(data2[0]) # 0 means latin1, 1 means utf_16_le
  1933. nchars = data2_len - 1
  1934. if nb:
  1935. assert nchars % 2 == 0
  1936. nchars //= 2
  1937. utext, endpos = unpack_unicode_update_pos(data2, 0, known_len=nchars)
  1938. assert endpos == data2_len
  1939. o.text += utext
  1940. totchars += nchars
  1941. o.rich_text_runlist = []
  1942. totruns = 0
  1943. while totruns < cbRuns: # counts of BYTES, not runs
  1944. rc3, data3_len, data3 = self.book.get_record_parts()
  1945. # print totruns, cbRuns, rc3, data3_len, repr(data3)
  1946. assert rc3 == XL_CONTINUE
  1947. assert data3_len % 8 == 0
  1948. for pos in xrange(0, data3_len, 8):
  1949. run = unpack('<HH4x', data3[pos:pos+8])
  1950. o.rich_text_runlist.append(run)
  1951. totruns += 8
  1952. # remove trailing entries that point to the end of the string
  1953. while o.rich_text_runlist and o.rich_text_runlist[-1][0] == cchText:
  1954. del o.rich_text_runlist[-1]
  1955. if OBJ_MSO_DEBUG:
  1956. o.dump(self.logfile, header="=== MSTxo ===", footer= " ")
  1957. print(o.rich_text_runlist, file=self.logfile)
  1958. return o
  1959. def handle_feat11(self, data):
  1960. if not OBJ_MSO_DEBUG:
  1961. return
  1962. # rt: Record type; this matches the BIFF rt in the first two bytes of the record; =0872h
  1963. # grbitFrt: FRT cell reference flag (see table below for details)
  1964. # Ref0: Range reference to a worksheet cell region if grbitFrt=1 (bitFrtRef). Otherwise blank.
  1965. # isf: Shared feature type index =5 for Table
  1966. # fHdr: =0 since this is for feat not feat header
  1967. # reserved0: Reserved for future use =0 for Table
  1968. # cref: Count of ref ranges this feature is on
  1969. # cbFeatData: Count of byte for the current feature data.
  1970. # reserved1: =0 currently not used
  1971. # Ref1: Repeat of Ref0. UNDOCUMENTED
  1972. rt, grbitFrt, Ref0, isf, fHdr, reserved0, cref, cbFeatData, reserved1, Ref1 = unpack('<HH8sHBiHiH8s', data[0:35])
  1973. assert reserved0 == 0
  1974. assert reserved1 == 0
  1975. assert isf == 5
  1976. assert rt == 0x872
  1977. assert fHdr == 0
  1978. assert Ref1 == Ref0
  1979. print(self.logfile, "FEAT11: grbitFrt=%d Ref0=%r cref=%d cbFeatData=%d\n", grbitFrt, Ref0, cref, cbFeatData)
  1980. # lt: Table data source type:
  1981. # =0 for Excel Worksheet Table =1 for read-write SharePoint linked List
  1982. # =2 for XML mapper Table =3 for Query Table
  1983. # idList: The ID of the Table (unique per worksheet)
  1984. # crwHeader: How many header/title rows the Table has at the top
  1985. # crwTotals: How many total rows the Table has at the bottom
  1986. # idFieldNext: Next id to try when assigning a unique id to a new field
  1987. # cbFSData: The size of the Fixed Data portion of the Table data structure.
  1988. # rupBuild: the rupBuild that generated the record
  1989. # unusedShort: UNUSED short that can be used later. The value is reserved during round-tripping.
  1990. # listFlags: Collection of bit flags: (see listFlags' bit setting table below for detail.)
  1991. # lPosStmCache: Table data stream position of cached data
  1992. # cbStmCache: Count of bytes of cached data
  1993. # cchStmCache: Count of characters of uncompressed cached data in the stream
  1994. # lem: Table edit mode (see List (Table) Editing Mode (lem) setting table below for details.)
  1995. # rgbHashParam: Hash value for SharePoint Table
  1996. # cchName: Count of characters in the Table name string rgbName
  1997. (lt, idList, crwHeader, crwTotals, idFieldNext, cbFSData,
  1998. rupBuild, unusedShort, listFlags, lPosStmCache, cbStmCache,
  1999. cchStmCache, lem, rgbHashParam, cchName) = unpack('<iiiiiiHHiiiii16sH', data[35:35+66])
  2000. print("lt=%d idList=%d crwHeader=%d crwTotals=%d idFieldNext=%d cbFSData=%d\n"
  2001. "rupBuild=%d unusedShort=%d listFlags=%04X lPosStmCache=%d cbStmCache=%d\n"
  2002. "cchStmCache=%d lem=%d rgbHashParam=%r cchName=%d" % (
  2003. lt, idList, crwHeader, crwTotals, idFieldNext, cbFSData,
  2004. rupBuild, unusedShort,listFlags, lPosStmCache, cbStmCache,
  2005. cchStmCache, lem, rgbHashParam, cchName), file=self.logfile)
  2006. def __repr__(self):
  2007. return "Sheet {:>2}:<{}>".format(self.number, self.name)
  2008. class MSODrawing(BaseObject):
  2009. pass
  2010. class MSObj(BaseObject):
  2011. pass
  2012. class MSTxo(BaseObject):
  2013. pass
  2014. class Note(BaseObject):
  2015. """
  2016. Represents a user "comment" or "note".
  2017. Note objects are accessible through :attr:`Sheet.cell_note_map`.
  2018. .. versionadded:: 0.7.2
  2019. """
  2020. #: Author of note
  2021. author = UNICODE_LITERAL('')
  2022. #: ``True`` if the containing column is hidden
  2023. col_hidden = 0
  2024. #: Column index
  2025. colx = 0
  2026. #: List of ``(offset_in_string, font_index)`` tuples.
  2027. #: Unlike :attr:`Sheet.rich_text_runlist_map`, the first offset should
  2028. #: always be 0.
  2029. rich_text_runlist = None
  2030. #: True if the containing row is hidden
  2031. row_hidden = 0
  2032. #: Row index
  2033. rowx = 0
  2034. #: True if note is always shown
  2035. show = 0
  2036. #: Text of the note
  2037. text = UNICODE_LITERAL('')
  2038. class Hyperlink(BaseObject):
  2039. """
  2040. Contains the attributes of a hyperlink.
  2041. Hyperlink objects are accessible through :attr:`Sheet.hyperlink_list`
  2042. and :attr:`Sheet.hyperlink_map`.
  2043. .. versionadded:: 0.7.2
  2044. """
  2045. #: Index of first row
  2046. frowx = None
  2047. #: Index of last row
  2048. lrowx = None
  2049. #: Index of first column
  2050. fcolx = None
  2051. #: Index of last column
  2052. lcolx = None
  2053. #: Type of hyperlink. Unicode string, one of 'url', 'unc',
  2054. #: 'local file', 'workbook', 'unknown'
  2055. type = None
  2056. #: The URL or file-path, depending in the type. Unicode string, except
  2057. #: in the rare case of a local but non-existent file with non-ASCII
  2058. #: characters in the name, in which case only the "8.3" filename is
  2059. #: available, as a :class:`bytes` (3.x) or :class:`str` (2.x) string,
  2060. #: *with unknown encoding.*
  2061. url_or_path = None
  2062. #: Description.
  2063. #: This is displayed in the cell,
  2064. #: and should be identical to the cell value. Unicode string, or ``None``.
  2065. #: It seems impossible NOT to have a description created by the Excel UI.
  2066. desc = None
  2067. #: Target frame. Unicode string.
  2068. #:
  2069. #: .. note::
  2070. #: No cases of this have been seen in the wild.
  2071. #: It seems impossible to create one in the Excel UI.
  2072. target = None
  2073. #: The piece after the "#" in
  2074. #: "http://docs.python.org/library#struct_module", or the ``Sheet1!A1:Z99``
  2075. #: part when type is "workbook".
  2076. textmark = None
  2077. #: The text of the "quick tip" displayed when the cursor
  2078. #: hovers over the hyperlink.
  2079. quicktip = None
  2080. # === helpers ===
  2081. def unpack_RK(rk_str):
  2082. flags = BYTES_ORD(rk_str[0])
  2083. if flags & 2:
  2084. # There's a SIGNED 30-bit integer in there!
  2085. i, = unpack('<i', rk_str)
  2086. i >>= 2 # div by 4 to drop the 2 flag bits
  2087. if flags & 1:
  2088. return i / 100.0
  2089. return float(i)
  2090. else:
  2091. # It's the most significant 30 bits of an IEEE 754 64-bit FP number
  2092. d, = unpack('<d', b'\0\0\0\0' + BYTES_LITERAL(chr(flags & 252)) + rk_str[1:4])
  2093. if flags & 1:
  2094. return d / 100.0
  2095. return d
  2096. ##### =============== Cell ======================================== #####
  2097. cellty_from_fmtty = {
  2098. FNU: XL_CELL_NUMBER,
  2099. FUN: XL_CELL_NUMBER,
  2100. FGE: XL_CELL_NUMBER,
  2101. FDT: XL_CELL_DATE,
  2102. FTX: XL_CELL_NUMBER, # Yes, a number can be formatted as text.
  2103. }
  2104. ctype_text = {
  2105. XL_CELL_EMPTY: 'empty',
  2106. XL_CELL_TEXT: 'text',
  2107. XL_CELL_NUMBER: 'number',
  2108. XL_CELL_DATE: 'xldate',
  2109. XL_CELL_BOOLEAN: 'bool',
  2110. XL_CELL_ERROR: 'error',
  2111. XL_CELL_BLANK: 'blank',
  2112. }
  2113. class Cell(BaseObject):
  2114. """
  2115. Contains the data for one cell.
  2116. .. warning::
  2117. You don't call this class yourself. You access :class:`Cell` objects
  2118. via methods of the :class:`Sheet` object(s) that you found in the
  2119. :class:`~xlrd.book.Book` object that was returned when you called
  2120. :func:`~xlrd.open_workbook`
  2121. Cell objects have three attributes: ``ctype`` is an int, ``value``
  2122. (which depends on ``ctype``) and ``xf_index``.
  2123. If ``formatting_info`` is not enabled when the workbook is opened,
  2124. ``xf_index`` will be ``None``.
  2125. The following table describes the types of cells and how their values
  2126. are represented in Python.
  2127. .. raw:: html
  2128. <table border="1" cellpadding="7">
  2129. <tr>
  2130. <th>Type symbol</th>
  2131. <th>Type number</th>
  2132. <th>Python value</th>
  2133. </tr>
  2134. <tr>
  2135. <td>XL_CELL_EMPTY</td>
  2136. <td align="center">0</td>
  2137. <td>empty string ''</td>
  2138. </tr>
  2139. <tr>
  2140. <td>XL_CELL_TEXT</td>
  2141. <td align="center">1</td>
  2142. <td>a Unicode string</td>
  2143. </tr>
  2144. <tr>
  2145. <td>XL_CELL_NUMBER</td>
  2146. <td align="center">2</td>
  2147. <td>float</td>
  2148. </tr>
  2149. <tr>
  2150. <td>XL_CELL_DATE</td>
  2151. <td align="center">3</td>
  2152. <td>float</td>
  2153. </tr>
  2154. <tr>
  2155. <td>XL_CELL_BOOLEAN</td>
  2156. <td align="center">4</td>
  2157. <td>int; 1 means TRUE, 0 means FALSE</td>
  2158. </tr>
  2159. <tr>
  2160. <td>XL_CELL_ERROR</td>
  2161. <td align="center">5</td>
  2162. <td>int representing internal Excel codes; for a text representation,
  2163. refer to the supplied dictionary error_text_from_code</td>
  2164. </tr>
  2165. <tr>
  2166. <td>XL_CELL_BLANK</td>
  2167. <td align="center">6</td>
  2168. <td>empty string ''. Note: this type will appear only when
  2169. open_workbook(..., formatting_info=True) is used.</td>
  2170. </tr>
  2171. </table>
  2172. """
  2173. __slots__ = ['ctype', 'value', 'xf_index']
  2174. def __init__(self, ctype, value, xf_index=None):
  2175. self.ctype = ctype
  2176. self.value = value
  2177. self.xf_index = xf_index
  2178. def __repr__(self):
  2179. if self.xf_index is None:
  2180. return "%s:%r" % (ctype_text[self.ctype], self.value)
  2181. else:
  2182. return "%s:%r (XF:%r)" % (ctype_text[self.ctype], self.value, self.xf_index)
  2183. empty_cell = Cell(XL_CELL_EMPTY, UNICODE_LITERAL(''))
  2184. ##### =============== Colinfo and Rowinfo ============================== #####
  2185. class Colinfo(BaseObject):
  2186. """
  2187. Width and default formatting information that applies to one or
  2188. more columns in a sheet. Derived from ``COLINFO`` records.
  2189. Here is the default hierarchy for width, according to the OOo docs:
  2190. In BIFF3, if a ``COLINFO`` record is missing for a column,
  2191. the width specified in the record ``DEFCOLWIDTH`` is used instead.
  2192. In BIFF4-BIFF7, the width set in this ``COLINFO`` record is only used,
  2193. if the corresponding bit for this column is cleared in the ``GCW``
  2194. record, otherwise the column width set in the ``DEFCOLWIDTH`` record
  2195. is used (the ``STANDARDWIDTH`` record is always ignored in this case [#f1]_).
  2196. In BIFF8, if a ``COLINFO`` record is missing for a column,
  2197. the width specified in the record ``STANDARDWIDTH`` is used.
  2198. If this ``STANDARDWIDTH`` record is also missing,
  2199. the column width of the record ``DEFCOLWIDTH`` is used instead.
  2200. .. [#f1] The docs on the ``GCW`` record say this:
  2201. If a bit is set, the corresponding column uses the width set in the
  2202. ``STANDARDWIDTH`` record. If a bit is cleared, the corresponding column
  2203. uses the width set in the ``COLINFO`` record for this column.
  2204. If a bit is set, and the worksheet does not contain the ``STANDARDWIDTH``
  2205. record, or if the bit is cleared, and the worksheet does not contain the
  2206. ``COLINFO`` record, the ``DEFCOLWIDTH`` record of the worksheet will be
  2207. used instead.
  2208. xlrd goes with the GCW version of the story.
  2209. Reference to the source may be useful: see
  2210. :meth:`Sheet.computed_column_width`.
  2211. .. versionadded:: 0.6.1
  2212. """
  2213. #: Width of the column in 1/256 of the width of the zero character,
  2214. #: using default font (first ``FONT`` record in the file).
  2215. width = 0
  2216. #: XF index to be used for formatting empty cells.
  2217. xf_index = -1
  2218. #: 1 = column is hidden
  2219. hidden = 0
  2220. #: Value of a 1-bit flag whose purpose is unknown
  2221. #: but is often seen set to 1
  2222. bit1_flag = 0
  2223. #: Outline level of the column, in ``range(7)``.
  2224. #: (0 = no outline)
  2225. outline_level = 0
  2226. #: 1 = column is collapsed
  2227. collapsed = 0
  2228. _USE_SLOTS = 1
  2229. class Rowinfo(BaseObject):
  2230. """
  2231. Height and default formatting information that applies to a row in a sheet.
  2232. Derived from ``ROW`` records.
  2233. .. versionadded:: 0.6.1
  2234. """
  2235. if _USE_SLOTS:
  2236. __slots__ = (
  2237. "height",
  2238. "has_default_height",
  2239. "outline_level",
  2240. "outline_group_starts_ends",
  2241. "hidden",
  2242. "height_mismatch",
  2243. "has_default_xf_index",
  2244. "xf_index",
  2245. "additional_space_above",
  2246. "additional_space_below",
  2247. )
  2248. def __init__(self):
  2249. #: Height of the row, in twips. One twip == 1/20 of a point.
  2250. self.height = None
  2251. #: 0 = Row has custom height; 1 = Row has default height.
  2252. self.has_default_height = None
  2253. #: Outline level of the row (0 to 7)
  2254. self.outline_level = None
  2255. #: 1 = Outline group starts or ends here (depending on where the
  2256. #: outline buttons are located, see ``WSBOOL`` record, which is not
  2257. #: parsed by xlrd), *and* is collapsed.
  2258. self.outline_group_starts_ends = None
  2259. #: 1 = Row is hidden (manually, or by a filter or outline group)
  2260. self.hidden = None
  2261. #: 1 = Row height and default font height do not match.
  2262. self.height_mismatch = None
  2263. #: 1 = the xf_index attribute is usable; 0 = ignore it.
  2264. self.has_default_xf_index = None
  2265. #: Index to default :class:`~xlrd.formatting.XF` record for empty cells
  2266. #: in this row. Don't use this if ``has_default_xf_index == 0``.
  2267. self.xf_index = None
  2268. #: This flag is set if the upper border of at least one cell in this
  2269. #: row or if the lower border of at least one cell in the row above is
  2270. #: formatted with a thick line style. Thin and medium line styles are
  2271. #: not taken into account.
  2272. self.additional_space_above = None
  2273. #: This flag is set if the lower border of at least one cell in this row
  2274. #: or if the upper border of at least one cell in the row below is
  2275. #: formatted with a medium or thick line style. Thin line styles are not
  2276. #: taken into account.
  2277. self.additional_space_below = None
  2278. def __getstate__(self):
  2279. return (
  2280. self.height,
  2281. self.has_default_height,
  2282. self.outline_level,
  2283. self.outline_group_starts_ends,
  2284. self.hidden,
  2285. self.height_mismatch,
  2286. self.has_default_xf_index,
  2287. self.xf_index,
  2288. self.additional_space_above,
  2289. self.additional_space_below,
  2290. )
  2291. def __setstate__(self, state):
  2292. (
  2293. self.height,
  2294. self.has_default_height,
  2295. self.outline_level,
  2296. self.outline_group_starts_ends,
  2297. self.hidden,
  2298. self.height_mismatch,
  2299. self.has_default_xf_index,
  2300. self.xf_index,
  2301. self.additional_space_above,
  2302. self.additional_space_below,
  2303. ) = state