Coverage for /var/srv/projects/api.amasfac.comuna18.com/tmp/venv/lib/python3.9/site-packages/xlwt/BIFFRecords.py: 51%
577 statements
« prev ^ index » next coverage.py v6.4.4, created at 2023-07-17 14:22 -0600
1# -*- coding: cp1252 -*-
2from struct import pack
3from .UnicodeUtils import upack1, upack2, upack2rt
4from .compat import basestring, unicode, unicode_type, xrange, iteritems
6class SharedStringTable(object):
7 _SST_ID = 0x00FC
8 _CONTINUE_ID = 0x003C
10 def __init__(self, encoding):
11 self.encoding = encoding
12 self._str_indexes = {}
13 self._rt_indexes = {}
14 self._tally = []
15 self._add_calls = 0
16 # Following 3 attrs are used for temporary storage in the
17 # get_biff_record() method and methods called by it. The pseudo-
18 # initialisation here is for documentation purposes only.
19 self._sst_record = None
20 self._continues = None
21 self._current_piece = None
23 def add_str(self, s):
24 if self.encoding != 'ascii' and not isinstance(s, unicode_type):
25 s = unicode(s, self.encoding)
26 self._add_calls += 1
27 if s not in self._str_indexes:
28 idx = len(self._str_indexes) + len(self._rt_indexes)
29 self._str_indexes[s] = idx
30 self._tally.append(1)
31 else:
32 idx = self._str_indexes[s]
33 self._tally[idx] += 1
34 return idx
36 def add_rt(self, rt):
37 rtList = []
38 for s, xf in rt:
39 if self.encoding != 'ascii' and not isinstance(s, unicode_type):
40 s = unicode(s, self.encoding)
41 rtList.append((s, xf))
42 rt = tuple(rtList)
43 self._add_calls += 1
44 if rt not in self._rt_indexes:
45 idx = len(self._str_indexes) + len(self._rt_indexes)
46 self._rt_indexes[rt] = idx
47 self._tally.append(1)
48 else:
49 idx = self._rt_indexes[rt]
50 self._tally[idx] += 1
51 return idx
53 def del_str(self, idx):
54 # This is called when we are replacing the contents of a string cell.
55 # handles both regular and rt strings
56 assert self._tally[idx] > 0
57 self._tally[idx] -= 1
58 self._add_calls -= 1
60 def str_index(self, s):
61 return self._str_indexes[s]
63 def rt_index(self, rt):
64 return self._rt_indexes[rt]
66 def get_biff_record(self):
67 self._sst_record = b''
68 self._continues = [None, None]
69 self._current_piece = pack('<II', 0, 0)
70 data = [(idx, s) for s, idx in iteritems(self._str_indexes)]
71 data.extend((idx, s) for s, idx in iteritems(self._rt_indexes))
72 data.sort() # in index order
73 for idx, s in data:
74 if self._tally[idx] == 0:
75 s = u''
76 if isinstance(s, basestring):
77 self._add_to_sst(s)
78 else:
79 self._add_rt_to_sst(s)
80 del data
81 self._new_piece()
82 self._continues[0] = pack('<2HII', self._SST_ID, len(self._sst_record), self._add_calls, len(self._str_indexes) + len(self._rt_indexes))
83 self._continues[1] = self._sst_record[8:]
84 self._sst_record = None
85 self._current_piece = None
86 result = b''.join(self._continues)
87 self._continues = None
88 return result
91 def _add_to_sst(self, s):
92 u_str = upack2(s, self.encoding)
94 is_unicode_str = u_str[2] == b'\x01'[0]
95 if is_unicode_str:
96 atom_len = 5 # 2 byte -- len,
97 # 1 byte -- options,
98 # 2 byte -- 1st sym
99 else:
100 atom_len = 4 # 2 byte -- len,
101 # 1 byte -- options,
102 # 1 byte -- 1st sym
104 self._save_atom(u_str[0:atom_len])
105 self._save_splitted(u_str[atom_len:], is_unicode_str)
107 def _add_rt_to_sst(self, rt):
108 rt_str, rt_fr = upack2rt(rt, self.encoding)
109 is_unicode_str = rt_str[2] == b'\x09'[0]
110 if is_unicode_str:
111 atom_len = 7 # 2 byte -- len,
112 # 1 byte -- options,
113 # 2 byte -- number of rt runs
114 # 2 byte -- 1st sym
115 else:
116 atom_len = 6 # 2 byte -- len,
117 # 1 byte -- options,
118 # 2 byte -- number of rt runs
119 # 1 byte -- 1st sym
120 self._save_atom(rt_str[0:atom_len])
121 self._save_splitted(rt_str[atom_len:], is_unicode_str)
122 for i in range(0, len(rt_fr), 4):
123 self._save_atom(rt_fr[i:i+4])
125 def _new_piece(self):
126 if self._sst_record == b'':
127 self._sst_record = self._current_piece
128 else:
129 curr_piece_len = len(self._current_piece)
130 self._continues.append(pack('<2H%ds'%curr_piece_len, self._CONTINUE_ID, curr_piece_len, self._current_piece))
131 self._current_piece = b''
133 def _save_atom(self, s):
134 atom_len = len(s)
135 free_space = 0x2020 - len(self._current_piece)
136 if free_space < atom_len:
137 self._new_piece()
138 self._current_piece += s
140 def _save_splitted(self, s, is_unicode_str):
141 i = 0
142 str_len = len(s)
143 while i < str_len:
144 piece_len = len(self._current_piece)
145 free_space = 0x2020 - piece_len
146 tail_len = str_len - i
147 need_more_space = free_space < tail_len
149 if not need_more_space:
150 atom_len = tail_len
151 else:
152 if is_unicode_str:
153 atom_len = free_space & 0xFFFE
154 else:
155 atom_len = free_space
157 self._current_piece += s[i:i+atom_len]
159 if need_more_space:
160 self._new_piece()
161 if is_unicode_str:
162 self._current_piece += b'\x01'
163 else:
164 self._current_piece += b'\x00'
166 i += atom_len
169class BiffRecord(object):
171 _rec_data = b'' # class attribute; child classes need to set this.
173 def get_rec_header(self):
174 return pack('<2H', self._REC_ID, len(self._rec_data))
176 # Not over-ridden by any child classes, never called (except by "get"; see below).
177 # def get_rec_data(self):
178 # return self._rec_data
180 def get(self):
181 # data = self.get_rec_data()
182 data = self._rec_data
183 if len(data) > 0x2020: # limit for BIFF7/8
184 chunks = []
185 pos = 0
186 while pos < len(data):
187 chunk_pos = pos + 0x2020
188 chunk = data[pos:chunk_pos]
189 chunks.append(chunk)
190 pos = chunk_pos
191 continues = pack('<2H', self._REC_ID, len(chunks[0])) + chunks[0]
192 for chunk in chunks[1:]:
193 continues += pack('<2H%ds'%len(chunk), 0x003C, len(chunk), chunk)
194 # 0x003C -- CONTINUE record id
195 return continues
196 else:
197 return self.get_rec_header() + data
200class Biff8BOFRecord(BiffRecord):
201 """
202 Offset Size Contents
203 0 2 Version, contains 0600H for BIFF8 and BIFF8X
204 2 2 Type of the following data:
205 0005H = Workbook globals
206 0006H = Visual Basic module
207 0010H = Worksheet
208 0020H = Chart
209 0040H = Macro sheet
210 0100H = Workspace file
211 4 2 Build identifier
212 6 2 Build year
213 8 4 File history flags
214 12 4 Lowest Excel version that can read all records in this file
215 """
216 _REC_ID = 0x0809
217 # stream types
218 BOOK_GLOBAL = 0x0005
219 VB_MODULE = 0x0006
220 WORKSHEET = 0x0010
221 CHART = 0x0020
222 MACROSHEET = 0x0040
223 WORKSPACE = 0x0100
225 def __init__(self, rec_type):
226 version = 0x0600
227 build = 0x0DBB
228 year = 0x07CC
229 file_hist_flags = 0x00
230 ver_can_read = 0x06
232 self._rec_data = pack('<4H2I', version, rec_type, build, year, file_hist_flags, ver_can_read)
235class InteraceHdrRecord(BiffRecord):
236 _REC_ID = 0x00E1
238 def __init__(self):
239 self._rec_data = pack('BB', 0xB0, 0x04)
242class InteraceEndRecord(BiffRecord):
243 _REC_ID = 0x00E2
245 def __init__(self):
246 self._rec_data = b''
249class MMSRecord(BiffRecord):
250 _REC_ID = 0x00C1
252 def __init__(self):
253 self._rec_data = pack('<H', 0x00)
256class WriteAccessRecord(BiffRecord):
257 """
258 This record is part of the file protection. It contains the name of the
259 user that has saved the file. The user name is always stored as an
260 equal-sized string. All unused characters after the name are filled
261 with space characters. It is not required to write the mentioned string
262 length. Every other length will be accepted too.
263 """
264 _REC_ID = 0x005C
266 def __init__(self, owner):
267 uowner = owner[0:0x30]
268 uowner_len = len(uowner)
269 if isinstance(uowner, unicode_type):
270 uowner = uowner.encode('ascii') # probably not ascii, but play it safe until we know more
271 self._rec_data = pack('%ds%ds' % (uowner_len, 0x70 - uowner_len), uowner, b' '*(0x70 - uowner_len))
274class DSFRecord(BiffRecord):
275 """
276 This record specifies if the file contains an additional BIFF5/BIFF7
277 workbook stream.
278 Record DSF, BIFF8:
279 Offset Size Contents
280 0 2 0 = Only the BIFF8 Workbook stream is present
281 1 = Additional BIFF5/BIFF7 Book stream is in the file
282 A double stream file can be read by Excel 5.0 and Excel 95, and still
283 contains all new features added to BIFF8 (which are left out in the
284 BIFF5/BIFF7 Book stream).
285 """
286 _REC_ID = 0x0161
288 def __init__(self):
289 self._rec_data = pack('<H', 0x00)
292class TabIDRecord(BiffRecord):
293 _REC_ID = 0x013D
295 def __init__(self, sheetcount):
296 for i in range(sheetcount):
297 self._rec_data += pack('<H', i+1)
300class FnGroupCountRecord(BiffRecord):
301 _REC_ID = 0x009C
303 def __init__(self):
304 self._rec_data = pack('BB', 0x0E, 0x00)
307class WindowProtectRecord(BiffRecord):
308 """
309 This record is part of the worksheet/workbook protection. It determines
310 whether the window configuration of this document is protected. Window
311 protection is not active, if this record is omitted.
312 """
313 _REC_ID = 0x0019
315 def __init__(self, wndprotect):
316 self._rec_data = pack('<H', wndprotect)
319class ObjectProtectRecord(BiffRecord):
320 """
321 This record is part of the worksheet/workbook protection.
322 It determines whether the objects of the current sheet are protected.
323 Object protection is not active, if this record is omitted.
324 """
325 _REC_ID = 0x0063
328 def __init__(self, objprotect):
329 self._rec_data = pack('<H', objprotect)
332class ScenProtectRecord(BiffRecord):
333 """
334 This record is part of the worksheet/workbook protection. It
335 determines whether the scenarios of the current sheet are protected.
336 Scenario protection is not active, if this record is omitted.
337 """
338 _REC_ID = 0x00DD
341 def __init__(self, scenprotect):
342 self._rec_data = pack('<H', scenprotect)
345class ProtectRecord(BiffRecord):
346 """
347 This record is part of the worksheet/workbook protection. It specifies
348 whether a worksheet or a workbook is protected against modification.
349 Protection is not active, if this record is omitted.
350 """
352 _REC_ID = 0x0012
354 def __init__(self, protect):
355 self._rec_data = pack('<H', protect)
358class PasswordRecord(BiffRecord):
359 """
360 This record is part of the worksheet/workbook protection. It
361 stores a 16-bit hash value, calculated from the worksheet or workbook
362 protection password.
363 """
364 _REC_ID = 0x0013
365 def passwd_hash(self, plaintext):
366 """
367 Based on the algorithm provided by Daniel Rentz of OpenOffice.
368 """
369 if plaintext == "":
370 return 0
372 passwd_hash = 0x0000
373 for i, char in enumerate(plaintext):
374 c = ord(char) << (i + 1)
375 low_15 = c & 0x7fff
376 high_15 = c & 0x7fff << 15
377 high_15 = high_15 >> 15
378 c = low_15 | high_15
379 passwd_hash ^= c
380 passwd_hash ^= len(plaintext)
381 passwd_hash ^= 0xCE4B
382 return passwd_hash
384 def __init__(self, passwd = ""):
385 self._rec_data = pack('<H', self.passwd_hash(passwd))
388class Prot4RevRecord(BiffRecord):
389 _REC_ID = 0x01AF
391 def __init__(self):
392 self._rec_data = pack('<H', 0x00)
395class Prot4RevPassRecord(BiffRecord):
396 _REC_ID = 0x01BC
398 def __init__(self):
399 self._rec_data = pack('<H', 0x00)
402class BackupRecord(BiffRecord):
403 """
404 This record contains a Boolean value determining whether Excel makes
405 a backup of the file while saving.
406 """
407 _REC_ID = 0x0040
409 def __init__(self, backup):
410 self._rec_data = pack('<H', backup)
412class HideObjRecord(BiffRecord):
413 """
414 This record specifies whether and how to show objects in the workbook.
416 Record HIDEOBJ, BIFF3-BIFF8:
417 Offset Size Contents
418 0 2 Viewing mode for objects:
419 0 = Show all objects
420 1 = Show placeholders
421 2 = Do not show objects
422 """
423 _REC_ID = 0x008D
425 def __init__(self):
426 self._rec_data = pack('<H', 0x00)
430class RefreshAllRecord(BiffRecord):
431 """
432 """
434 _REC_ID = 0x01B7
436 def __init__(self):
437 self._rec_data = pack('<H', 0x00)
440class BookBoolRecord(BiffRecord):
441 """
442 This record contains a Boolean value determining whether to save values
443 linked from external workbooks (CRN records and XCT records). In BIFF3
444 and BIFF4 this option is stored in the WSBOOL record.
446 Record BOOKBOOL, BIFF5-BIFF8:
448 Offset Size Contents
449 0 2 0 = Save external linked values;
450 1 = Do not save external linked values
451 """
453 _REC_ID = 0x00DA
455 def __init__(self):
456 self._rec_data = pack('<H', 0x00)
459class CountryRecord(BiffRecord):
460 """
461 This record stores two Windows country identifiers. The first
462 represents the user interface language of the Excel version that has
463 saved the file, and the second represents the system regional settings
464 at the time the file was saved.
466 Record COUNTRY, BIFF3-BIFF8:
468 Offset Size Contents
469 0 2 Windows country identifier of the user interface language of Excel
470 2 2 Windows country identifier of the system regional settings
472 The following table shows most of the used country identifiers. Most
473 of these identifiers are equal to the international country calling
474 codes.
476 1 USA
477 2 Canada
478 7 Russia
479 """
481 _REC_ID = 0x008C
483 def __init__(self, ui_id, sys_settings_id):
484 self._rec_data = pack('<2H', ui_id, sys_settings_id)
487class UseSelfsRecord(BiffRecord):
488 """
489 This record specifies if the formulas in the workbook can use natural
490 language formulas. This type of formula can refer to cells by its
491 content or the content of the column or row header cell.
493 Record USESELFS, BIFF8:
495 Offset Size Contents
496 0 2 0 = Do not use natural language formulas
497 1 = Use natural language formulas
499 """
501 _REC_ID = 0x0160
503 def __init__(self):
504 self._rec_data = pack('<H', 0x01)
507class EOFRecord(BiffRecord):
508 _REC_ID = 0x000A
510 def __init__(self):
511 self._rec_data = b''
514class DateModeRecord(BiffRecord):
515 """
516 This record specifies the base date for displaying date values. All
517 dates are stored as count of days past this base date. In BIFF2-BIFF4
518 this record is part of the Calculation Settings Block.
519 In BIFF5-BIFF8 it is stored in the Workbook Globals Substream.
521 Record DATEMODE, BIFF2-BIFF8:
523 Offset Size Contents
524 0 2 0 = Base is 1899-Dec-31 (the cell = 1 represents 1900-Jan-01)
525 1 = Base is 1904-Jan-01 (the cell = 1 represents 1904-Jan-02)
526 """
527 _REC_ID = 0x0022
529 def __init__(self, from1904):
530 if from1904:
531 self._rec_data = pack('<H', 1)
532 else:
533 self._rec_data = pack('<H', 0)
536class PrecisionRecord(BiffRecord):
537 """
538 This record stores if formulas use the real cell values for calculation
539 or the values displayed on the screen. In BIFF2- BIFF4 this record
540 is part of the Calculation Settings Block. In BIFF5-BIFF8 it is stored
541 in the Workbook Globals Substream.
543 Record PRECISION, BIFF2-BIFF8:
545 Offset Size Contents
546 0 2 0 = Use displayed values;
547 1 = Use real cell values
548 """
549 _REC_ID = 0x000E
551 def __init__(self, use_real_values):
552 if use_real_values:
553 self._rec_data = pack('<H', 1)
554 else:
555 self._rec_data = pack('<H', 0)
558class CodepageBiff8Record(BiffRecord):
559 """
560 This record stores the text encoding used to write byte strings, stored
561 as MS Windows code page identifier. The CODEPAGE record in BIFF8 always
562 contains the code page 1200 (UTF-16). Therefore it is not
563 possible to obtain the encoding used for a protection password (it is
564 not UTF-16).
566 Record CODEPAGE, BIFF2-BIFF8:
568 Offset Size Contents
569 0 2 Code page identifier used for byte string text encoding:
570 016FH = 367 = ASCII
571 01B5H = 437 = IBM PC CP-437 (US)
572 02D0H = 720 = IBM PC CP-720 (OEM Arabic)
573 02E1H = 737 = IBM PC CP-737 (Greek)
574 0307H = 775 = IBM PC CP-775 (Baltic)
575 0352H = 850 = IBM PC CP-850 (Latin I)
576 0354H = 852 = IBM PC CP-852 (Latin II (Central European))
577 0357H = 855 = IBM PC CP-855 (Cyrillic)
578 0359H = 857 = IBM PC CP-857 (Turkish)
579 035AH = 858 = IBM PC CP-858 (Multilingual Latin I with Euro)
580 035CH = 860 = IBM PC CP-860 (Portuguese)
581 035DH = 861 = IBM PC CP-861 (Icelandic)
582 035EH = 862 = IBM PC CP-862 (Hebrew)
583 035FH = 863 = IBM PC CP-863 (Canadian (French))
584 0360H = 864 = IBM PC CP-864 (Arabic)
585 0361H = 865 = IBM PC CP-865 (Nordic)
586 0362H = 866 = IBM PC CP-866 (Cyrillic (Russian))
587 0365H = 869 = IBM PC CP-869 (Greek (Modern))
588 036AH = 874 = Windows CP-874 (Thai)
589 03A4H = 932 = Windows CP-932 (Japanese Shift-JIS)
590 03A8H = 936 = Windows CP-936 (Chinese Simplified GBK)
591 03B5H = 949 = Windows CP-949 (Korean (Wansung))
592 03B6H = 950 = Windows CP-950 (Chinese Traditional BIG5)
593 04B0H = 1200 = UTF-16 (BIFF8)
594 04E2H = 1250 = Windows CP-1250 (Latin II) (Central European)
595 04E3H = 1251 = Windows CP-1251 (Cyrillic)
596 04E4H = 1252 = Windows CP-1252 (Latin I) (BIFF4-BIFF7)
597 04E5H = 1253 = Windows CP-1253 (Greek)
598 04E6H = 1254 = Windows CP-1254 (Turkish)
599 04E7H = 1255 = Windows CP-1255 (Hebrew)
600 04E8H = 1256 = Windows CP-1256 (Arabic)
601 04E9H = 1257 = Windows CP-1257 (Baltic)
602 04EAH = 1258 = Windows CP-1258 (Vietnamese)
603 0551H = 1361 = Windows CP-1361 (Korean (Johab))
604 2710H = 10000 = Apple Roman
605 8000H = 32768 = Apple Roman
606 8001H = 32769 = Windows CP-1252 (Latin I) (BIFF2-BIFF3)
607 """
608 _REC_ID = 0x0042
609 UTF_16 = 0x04B0
611 def __init__(self):
612 self._rec_data = pack('<H', self.UTF_16)
614class Window1Record(BiffRecord):
615 """
616 Offset Size Contents
617 0 2 Horizontal position of the document window (in twips = 1/20 of a point)
618 2 2 Vertical position of the document window (in twips = 1/20 of a point)
619 4 2 Width of the document window (in twips = 1/20 of a point)
620 6 2 Height of the document window (in twips = 1/20 of a point)
621 8 2 Option flags:
622 Bits Mask Contents
623 0 0001H 0 = Window is visible 1 = Window is hidden
624 1 0002H 0 = Window is open 1 = Window is minimised
625 3 0008H 0 = Horizontal scroll bar hidden 1 = Horizontal scroll bar visible
626 4 0010H 0 = Vertical scroll bar hidden 1 = Vertical scroll bar visible
627 5 0020H 0 = Worksheet tab bar hidden 1 = Worksheet tab bar visible
628 10 2 Index to active (displayed) worksheet
629 12 2 Index of first visible tab in the worksheet tab bar
630 14 2 Number of selected worksheets (highlighted in the worksheet tab bar)
631 16 2 Width of worksheet tab bar (in 1/1000 of window width). The remaining space is used by the
632 horizontal scrollbar.
633 """
634 _REC_ID = 0x003D
635 # flags
637 def __init__(self,
638 hpos_twips, vpos_twips,
639 width_twips, height_twips,
640 flags,
641 active_sheet,
642 first_tab_index, selected_tabs, tab_width):
643 self._rec_data = pack('<9H', hpos_twips, vpos_twips,
644 width_twips, height_twips,
645 flags,
646 active_sheet,
647 first_tab_index, selected_tabs, tab_width)
649class FontRecord(BiffRecord):
650 """
651 WARNING
652 The font with index 4 is omitted in all BIFF versions.
653 This means the first four fonts have zero-based indexes, and
654 the fifth font and all following fonts are referenced with one-based
655 indexes.
657 Offset Size Contents
658 0 2 Height of the font (in twips = 1/20 of a point)
659 2 2 Option flags:
660 Bit Mask Contents
661 0 0001H 1 = Characters are bold (redundant, see below)
662 1 0002H 1 = Characters are italic
663 2 0004H 1 = Characters are underlined (redundant, see below)
664 3 0008H 1 = Characters are struck out
665 0010H 1 = Outline
666 0020H 1 = Shadow
667 4 2 Colour index
668 6 2 Font weight (100-1000).
669 Standard values are 0190H (400) for normal text and 02BCH
670 (700) for bold text.
671 8 2 Escapement type:
672 0000H = None
673 0001H = Superscript
674 0002H = Subscript
675 10 1 Underline type:
676 00H = None
677 01H = Single
678 21H = Single accounting
679 02H = Double
680 22H = Double accounting
681 11 1 Font family:
682 00H = None (unknown or don't care)
683 01H = Roman (variable width, serifed)
684 02H = Swiss (variable width, sans-serifed)
685 03H = Modern (fixed width, serifed or sans-serifed)
686 04H = Script (cursive)
687 05H = Decorative (specialised, i.e. Old English, Fraktur)
688 12 1 Character set:
689 00H = 0 = ANSI Latin
690 01H = 1 = System default
691 02H = 2 = Symbol
692 4DH = 77 = Apple Roman
693 80H = 128 = ANSI Japanese Shift-JIS
694 81H = 129 = ANSI Korean (Hangul)
695 82H = 130 = ANSI Korean (Johab)
696 86H = 134 = ANSI Chinese Simplified GBK
697 88H = 136 = ANSI Chinese Traditional BIG5
698 A1H = 161 = ANSI Greek
699 A2H = 162 = ANSI Turkish
700 A3H = 163 = ANSI Vietnamese
701 B1H = 177 = ANSI Hebrew
702 B2H = 178 = ANSI Arabic
703 BAH = 186 = ANSI Baltic
704 CCH = 204 = ANSI Cyrillic
705 DEH = 222 = ANSI Thai
706 EEH = 238 = ANSI Latin II (Central European)
707 FFH = 255 = OEM Latin I
708 13 1 Not used
709 14 var. Font name:
710 BIFF5/BIFF7: Byte string, 8-bit string length
711 BIFF8: Unicode string, 8-bit string length
712 The boldness and underline flags are still set in the options field,
713 but not used on reading the font. Font weight and underline type
714 are specified in separate fields instead.
715 """
716 _REC_ID = 0x0031
718 def __init__(self,
719 height, options, colour_index, weight, escapement,
720 underline, family, charset,
721 name):
722 uname = upack1(name)
723 uname_len = len(uname)
725 self._rec_data = pack('<5H4B%ds' % uname_len, height, options, colour_index, weight, escapement,
726 underline, family, charset, 0x00,
727 uname)
729class NumberFormatRecord(BiffRecord):
730 """
731 Record FORMAT, BIFF8:
732 Offset Size Contents
733 0 2 Format index used in other records
734 2 var. Number format string (Unicode string, 16-bit string length)
736 From BIFF5 on, the built-in number formats will be omitted. The built-in
737 formats are dependent on the current regional settings of the operating
738 system. The following table shows which number formats are used by default
739 in a US-English environment. All indexes from 0 to 163 are reserved for
740 built-in formats. The first user-defined format starts at 164.
742 The built-in number formats, BIFF5-BIFF8
744 Index Type Format string
745 0 General General
746 1 Decimal 0
747 2 Decimal 0.00
748 3 Decimal #,##0
749 4 Decimal #,##0.00
750 5 Currency "$"#,##0_);("$"#,##
751 6 Currency "$"#,##0_);[Red]("$"#,##
752 7 Currency "$"#,##0.00_);("$"#,##
753 8 Currency "$"#,##0.00_);[Red]("$"#,##
754 9 Percent 0%
755 10 Percent 0.00%
756 11 Scientific 0.00E+00
757 12 Fraction # ?/?
758 13 Fraction # ??/??
759 14 Date M/D/YY
760 15 Date D-MMM-YY
761 16 Date D-MMM
762 17 Date MMM-YY
763 18 Time h:mm AM/PM
764 19 Time h:mm:ss AM/PM
765 20 Time h:mm
766 21 Time h:mm:ss
767 22 Date/Time M/D/YY h:mm
768 37 Account _(#,##0_);(#,##0)
769 38 Account _(#,##0_);[Red](#,##0)
770 39 Account _(#,##0.00_);(#,##0.00)
771 40 Account _(#,##0.00_);[Red](#,##0.00)
772 41 Currency _("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
773 42 Currency _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
774 43 Currency _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
775 44 Currency _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
776 45 Time mm:ss
777 46 Time [h]:mm:ss
778 47 Time mm:ss.0
779 48 Scientific ##0.0E+0
780 49 Text @
781 """
782 _REC_ID = 0x041E
784 def __init__(self, idx, fmtstr):
785 ufmtstr = upack2(fmtstr)
786 ufmtstr_len = len(ufmtstr)
788 self._rec_data = pack('<H%ds' % ufmtstr_len, idx, ufmtstr)
791class XFRecord(BiffRecord):
792 """
793 XF Substructures
794 -------------------------------------------------------------------------
795 XF_TYPE_PROT XF Type and Cell Protection (3 Bits), BIFF3-BIFF8
796 These 3 bits are part of a specific data byte.
797 Bit Mask Contents
798 0 01H 1 = Cell is locked
799 1 02H 1 = Formula is hidden
800 2 04H 0 = Cell XF; 1 = Style XF
802 XF_USED_ATTRIB Attributes Used from Parent Style XF (6 Bits),
803 BIFF3-BIFF8 Each bit describes the validity of a specific group
804 of attributes. In cell XFs a cleared bit means the attributes of the
805 parent style XF are used (but only if the attributes are valid there),
806 a set bit means the attributes of this XF are used. In style XFs
807 a cleared bit means the attribute setting is valid, a set bit means the
808 attribute should be ignored.
809 Bit Mask Contents
810 0 01H Flag for number format
811 1 02H Flag for font
812 2 04H Flag for horizontal and vertical alignment, text wrap, indentation, orientation, rotation, and
813 text direction
814 3 08H Flag for border lines
815 4 10H Flag for background area style
816 5 20H Flag for cell protection (cell locked and formula hidden)
818 XF_HOR_ALIGN Horizontal Alignment (3 Bits), BIFF2-BIFF8 The horizontal
819 alignment consists of 3 bits and is part of a specific data byte.
820 Value Horizontal alignment
821 00H General
822 01H Left
823 02H Centred
824 03H Right
825 04H Filled
826 05H Justified (BIFF4-BIFF8X)
827 06H Centred across selection (BIFF4-BIFF8X)
828 07H Distributed (BIFF8X)
830 XF_VERT_ALIGN Vertical Alignment (2 or 3 Bits), BIFF4-BIFF8
831 The vertical alignment consists of 2 bits (BIFF4) or 3 bits (BIFF5-BIFF8)
832 and is part of a specific data byte. Vertical alignment is not available
833 in BIFF2 and BIFF3.
834 Value Vertical alignment
835 00H Top
836 01H Centred
837 02H Bottom
838 03H Justified (BIFF5-BIFF8X)
839 04H Distributed (BIFF8X)
841 XF_ORIENTATION Text Orientation (2 Bits), BIFF4-BIFF7 In the BIFF
842 versions BIFF4-BIFF7, text can be rotated in steps of 90 degrees
843 or stacked. The orientation mode consists of 2 bits and is part of
844 a specific data byte. In BIFF8 a rotation angle occurs instead of these
845 flags.
846 Value Text orientation
847 00H Not rotated
848 01H Letters are stacked top-to-bottom, but not rotated
849 02H Text is rotated 90 degrees counterclockwise
850 03H Text is rotated 90 degrees clockwise
852 XF_ROTATION Text Rotation Angle (1 Byte), BIFF8
853 Value Text rotation
854 0 Not rotated
855 1-90 1 to 90 degrees counterclockwise
856 91-180 1 to 90 degrees clockwise
857 255 Letters are stacked top-to-bottom, but not rotated
859 XF_BORDER_34 Cell Border Style (4 Bytes), BIFF3-BIFF4 Cell borders
860 contain a line style and a line colour for each line of the border.
861 Bit Mask Contents
862 2-0 00000007H Top line style
863 7-3 000000F8H Colour index for top line colour
864 10-8 00000700H Left line style
865 15-11 0000F800H Colour index for left line colour
866 18-16 00070000H Bottom line style
867 23-19 00F80000H Colour index for bottom line colour
868 26-24 07000000H Right line style
869 31-27 F8000000H Colour index for right line colour
871 XF_AREA_34 Cell Background Area Style (2 Bytes), BIFF3-BIFF4 A cell
872 background area style contains an area pattern and a foreground and
873 background colour.
874 Bit Mask Contents
875 5-0 003FH Fill pattern
876 10-6 07C0H Colour index for pattern colour
877 15-11 F800H Colour index for pattern background
878 ---------------------------------------------------------------------------------------------
879 Record XF, BIFF8:
880 Offset Size Contents
881 0 2 Index to FONT record
882 2 2 Index to FORMAT record
883 4 2 Bit Mask Contents
884 2-0 0007H XF_TYPE_PROT . XF type, cell protection (see above)
885 15-4 FFF0H Index to parent style XF (always FFFH in style XFs)
886 6 1 Bit Mask Contents
887 2-0 07H XF_HOR_ALIGN . Horizontal alignment (see above)
888 3 08H 1 = Text is wrapped at right border
889 6-4 70H XF_VERT_ALIGN . Vertical alignment (see above)
890 7 1 XF_ROTATION: Text rotation angle (see above)
891 8 1 Bit Mask Contents
892 3-0 0FH Indent level
893 4 10H 1 = Shrink content to fit into cell
894 5 merge
895 7-6 C0H Text direction (BIFF8X only)
896 00b = According to context
897 01b = Left-to-right
898 10b = Right-to-left
899 9 1 Bit Mask Contents
900 7-2 FCH XF_USED_ATTRIB . Used attributes (see above)
901 10 4 Cell border lines and background area:
902 Bit Mask Contents
903 3-0 0000000FH Left line style
904 7-4 000000F0H Right line style
905 11-8 00000F00H Top line style
906 15-12 0000F000H Bottom line style
907 22-16 007F0000H Colour index for left line colour
908 29-23 3F800000H Colour index for right line colour
909 30 40000000H 1 = Diagonal line from top left to right bottom
910 31 80000000H 1 = Diagonal line from bottom left to right top
911 14 4 Bit Mask Contents
912 6-0 0000007FH Colour index for top line colour
913 13-7 00003F80H Colour index for bottom line colour
914 20-14 001FC000H Colour index for diagonal line colour
915 24-21 01E00000H Diagonal line style
916 31-26 FC000000H Fill pattern
917 18 2 Bit Mask Contents
918 6-0 007FH Colour index for pattern colour
919 13-7 3F80H Colour index for pattern background
921 """
922 _REC_ID = 0x00E0
924 def __init__(self, xf, xftype='cell'):
925 font_xf_idx, fmt_str_xf_idx, alignment, borders, pattern, protection = xf
926 fnt = pack('<H', font_xf_idx)
927 fmt = pack('<H', fmt_str_xf_idx)
928 if xftype == 'cell':
929 prt = pack('<H',
930 ((protection.cell_locked & 0x01) << 0) |
931 ((protection.formula_hidden & 0x01) << 1)
932 )
933 else:
934 prt = pack('<H', 0xFFF5)
935 aln = pack('B',
936 ((alignment.horz & 0x07) << 0) |
937 ((alignment.wrap & 0x01) << 3) |
938 ((alignment.vert & 0x07) << 4)
939 )
940 rot = pack('B', alignment.rota)
941 txt = pack('B',
942 ((alignment.inde & 0x0F) << 0) |
943 ((alignment.shri & 0x01) << 4) |
944 ((alignment.merg & 0x01) << 5) |
945 ((alignment.dire & 0x03) << 6)
946 )
947 if xftype == 'cell':
948 used_attr = pack('B', 0xF8)
949 else:
950 used_attr = pack('B', 0xF4)
952 if borders.left == borders.NO_LINE:
953 borders.left_colour = 0x00
954 if borders.right == borders.NO_LINE:
955 borders.right_colour = 0x00
956 if borders.top == borders.NO_LINE:
957 borders.top_colour = 0x00
958 if borders.bottom == borders.NO_LINE:
959 borders.bottom_colour = 0x00
960 if borders.diag == borders.NO_LINE:
961 borders.diag_colour = 0x00
962 brd1 = pack('<L',
963 ((borders.left & 0x0F) << 0 ) |
964 ((borders.right & 0x0F) << 4 ) |
965 ((borders.top & 0x0F) << 8 ) |
966 ((borders.bottom & 0x0F) << 12) |
967 ((borders.left_colour & 0x7F) << 16) |
968 ((borders.right_colour & 0x7F) << 23) |
969 ((borders.need_diag1 & 0x01) << 30) |
970 ((borders.need_diag2 & 0x01) << 31)
971 )
972 brd2 = pack('<L',
973 ((borders.top_colour & 0x7F) << 0 ) |
974 ((borders.bottom_colour & 0x7F) << 7 ) |
975 ((borders.diag_colour & 0x7F) << 14) |
976 ((borders.diag & 0x0F) << 21) |
977 ((pattern.pattern & 0x3F) << 26)
978 )
979 pat = pack('<H',
980 ((pattern.pattern_fore_colour & 0x7F) << 0 ) |
981 ((pattern.pattern_back_colour & 0x7F) << 7 )
982 )
983 self._rec_data = fnt + fmt + prt + \
984 aln + rot + txt + used_attr + \
985 brd1 + brd2 + \
986 pat
988class StyleRecord(BiffRecord):
989 """
990 STYLE record for user-defined cell styles, BIFF3-BIFF8:
991 Offset Size Contents
992 0 2 Bit Mask Contents
993 11-0 0FFFH Index to style XF record
994 15 8000H Always 0 for user-defined styles
995 2 var. BIFF2-BIFF7: Non-empty byte string, 8-bit string length
996 BIFF8: Non-empty Unicode string, 16-bit string length
997 STYLE record for built-in cell styles, BIFF3-BIFF8:
998 Offset Size Contents
999 0 2 Bit Mask Contents
1000 11-0 0FFFH Index to style XF record
1001 15 8000H Always 1 for built-in styles
1002 2 1 Identifier of the built-in cell style:
1003 00H = Normal
1004 01H = RowLevel_lv (see next field)
1005 02H = ColLevel_lv (see next field)
1006 03H = Comma
1007 04H = Currency
1008 05H = Percent
1009 06H = Comma [0] (BIFF4-BIFF8)
1010 07H = Currency [0] (BIFF4-BIFF8)
1011 08H = Hyperlink (BIFF8)
1012 09H = Followed Hyperlink (BIFF8)
1013 3 1 Level for RowLevel or ColLevel style
1014 (zero-based, lv), FFH otherwise
1015 The RowLevel and ColLevel styles specify the formatting of subtotal
1016 cells in a specific outline level. The level is specified by the last
1017 field in the STYLE record. Valid values are 0-6 for the outline levels
1018 1-7.
1019 """
1020 _REC_ID = 0x0293
1022 def __init__(self):
1023 self._rec_data = pack('<HBB', 0x8000, 0x00, 0xFF)
1024 # TODO: implement user-defined styles???
1027class PaletteRecord(BiffRecord):
1028 """
1029 This record contains the definition of all user-defined colours
1030 available for cell and object formatting.
1032 Record PALETTE, BIFF3-BIFF8:
1034 Offset Size Contents
1035 0 2 Number of following colours (nm). Contains 16 in BIFF3-BIFF4 and 56 in BIFF5-BIFF8.
1036 2 4*nm List of nm RGB colours
1038 The following table shows how colour indexes are used in other records:
1040 Colour index Resulting colour or internal list index
1041 00H Built-in Black (R = 00H, G = 00H, B = 00H)
1042 01H Built-in White (R = FFH, G = FFH, B = FFH)
1043 02H Built-in Red (R = FFH, G = 00H, B = 00H)
1044 03H Built-in Green (R = 00H, G = FFH, B = 00H)
1045 04H Built-in Blue (R = 00H, G = 00H, B = FFH)
1046 05H Built-in Yellow (R = FFH, G = FFH, B = 00H)
1047 06H Built-in Magenta (R = FFH, G = 00H, B = FFH)
1048 07H Built-in Cyan (R = 00H, G = FFH, B = FFH)
1049 08H First user-defined colour from the PALETTE record (entry 0 from record colour list)
1050 .........................
1052 17H (BIFF3-BIFF4) Last user-defined colour from the PALETTE record (entry 15 or 55 from record colour list)
1053 3FH (BIFF5-BIFF8)
1055 18H (BIFF3-BIFF4) System window text colour for border lines (used in records XF, CF, and
1056 40H (BIFF5-BIFF8) WINDOW2 (BIFF8 only))
1058 19H (BIFF3-BIFF4) System window background colour for pattern background (used in records XF, and CF)
1059 41H (BIFF5-BIFF8)
1061 43H System face colour (dialogue background colour)
1062 4DH System window text colour for chart border lines
1063 4EH System window background colour for chart areas
1064 4FH Automatic colour for chart border lines (seems to be always Black)
1065 50H System ToolTip background colour (used in note objects)
1066 51H System ToolTip text colour (used in note objects)
1067 7FFFH System window text colour for fonts (used in records FONT, EFONT, and CF)
1069 """
1070 _REC_ID = 0x0092
1072 def __init__(self, custom_palette):
1073 n_colours = len(custom_palette)
1074 assert n_colours == 56
1075 # Pack number of colors with little-endian, what xlrd and excel expect.
1076 self._rec_data = pack('<H', n_colours)
1077 # Microsoft lists colors in big-endian format with 24 bits/color.
1078 # Pad LSB of each color with 0x00, and write out in big-endian.
1079 fmt = '>%dI' % n_colours
1080 self._rec_data += pack(fmt, *(custom_palette))
1082class BoundSheetRecord(BiffRecord):
1083 """
1084 This record is located in the workbook globals area and represents
1085 a sheet inside of the workbook. For each sheet a BOUNDSHEET record
1086 is written. It stores the sheet name and a stream offset to the BOF
1087 record within the workbook stream. The record is also known
1088 as BUNDLESHEET.
1090 Record BOUNDSHEET, BIFF5-BIFF8:
1091 Offset Size Contents
1092 0 4 Absolute stream position of the BOF record of the sheet represented by this record. This
1093 field is never encrypted in protected files.
1094 4 1 Visibility:
1095 00H = Visible
1096 01H = Hidden
1097 02H = Strong hidden
1098 5 1 Sheet type:
1099 00H = Worksheet
1100 02H = Chart
1101 06H = Visual Basic module
1102 6 var. Sheet name:
1103 BIFF5/BIFF7: Byte string, 8-bit string length
1104 BIFF8: Unicode string, 8-bit string length
1105 """
1106 _REC_ID = 0x0085
1108 def __init__(self, stream_pos, visibility, sheetname, encoding='ascii'):
1109 usheetname = upack1(sheetname, encoding)
1110 uusheetname_len = len(usheetname)
1112 self._rec_data = pack('<LBB%ds' % uusheetname_len, stream_pos, visibility, 0x00, usheetname)
1115class ContinueRecord(BiffRecord):
1116 """
1117 Whenever the content of a record exceeds the given limits (see table),
1118 the record must be split. Several CONTINUE records containing the
1119 additional data are added after the parent record.
1121 BIFF version Maximum data size of a record
1122 BIFF2-BIFF7 2080 bytes (2084 bytes including record header)
1123 BIFF8 8224 bytes (8228 bytes including record header) (0x2020)
1125 Record CONTINUE, BIFF2-BIFF8:
1126 Offset Size Contents
1127 0 var. Data continuation of the previous record
1129 Unicode strings are split in a special way. At the beginning of each
1130 CONTINUE record the option flags byte is repeated. Only the character
1131 size flag will be set in this flags byte, the Rich-Text flag and the
1132 Far-East flag are set to zero. In each CONTINUE record it is possible
1133 that the character size changes from 8-bit characters to 16-bit
1134 characters and vice versa.
1136 Never a Unicode string is split until and including the first
1137 character. That means, all header fields (string length, option flags,
1138 optional Rich-Text size, and optional Far-East data size) and the first
1139 character of the string have to occur together in the leading record,
1140 or have to be moved completely into the CONTINUE record. Formatting
1141 runs cannot be split between their components (character index and FONT
1142 record index). If a string is split between two formatting runs, the
1143 option flags field will not be repeated in the CONTINUE record.
1144 """
1145 _REC_ID = 0x003C
1148class SSTRecord(BiffRecord):
1149 """
1150 This record contains a list of all strings used anywhere in the
1151 workbook. Each string occurs only once. The workbook uses indexes into
1152 the list to reference the strings.
1154 Record SST, BIFF8:
1155 Offset Size Contents
1156 0 4 Total number of strings in the workbook (see below)
1157 4 4 Number of following strings (nm)
1158 8 var. List of nm Unicode strings, 16-bit string length
1160 The first field of the SST record counts the total occurrence
1161 of strings in the workbook. For instance, the string AAA is used
1162 3 times and the string BBB is used 2 times. The first field contains
1163 5 and the second field contains 2, followed by the two strings.
1164 """
1165 _REC_ID = 0x00FC
1168class ExtSSTRecord(BiffRecord):
1169 """
1170 This record occurs in conjunction with the SST record. It is used
1171 by Excel to create a hash table with stream offsets to the SST record
1172 to optimise string search operations. Excel may not shorten this record
1173 if strings are deleted from the shared string table, so the last part
1174 might contain invalid data. The stream indexes in this record divide
1175 the SST into portions containing a constant number of strings.
1177 Record EXTSST, BIFF8:
1179 Offset Size Contents
1180 0 2 Number of strings in a portion, this number is >=8
1181 2 var. List of OFFSET structures for all portions. Each OFFSET contains the following data:
1182 Offset Size Contents
1183 0 4 Absolute stream position of first string of the portion
1184 4 2 Position of first string of the portion inside of current record,
1185 including record header. This counter restarts at zero, if the SST
1186 record is continued with a CONTINUE record.
1187 6 2 Not used
1188 """
1189 _REC_ID = 0x00FF
1191 def __init__(self, sst_stream_pos, str_placement, portions_len):
1192 extsst = {}
1193 abs_stream_pos = sst_stream_pos
1194 str_counter = 0
1195 portion_counter = 0
1196 while str_counter < len(str_placement):
1197 str_chunk_num, pos_in_chunk = str_placement[str_counter]
1198 if str_chunk_num != portion_counter:
1199 portion_counter = str_chunk_num
1200 abs_stream_pos += portions_len[portion_counter-1]
1201 #print hex(abs_stream_pos)
1202 str_stream_pos = abs_stream_pos + pos_in_chunk + 4 # header
1203 extsst[str_counter] = (pos_in_chunk, str_stream_pos)
1204 str_counter += 1
1206 exsst_str_count_delta = max(8, len(str_placement)*8/0x2000) # maybe smth else?
1207 self._rec_data = pack('<H', exsst_str_count_delta)
1208 str_counter = 0
1209 while str_counter < len(str_placement):
1210 self._rec_data += pack('<IHH', extsst[str_counter][1], extsst[str_counter][0], 0)
1211 str_counter += exsst_str_count_delta
1213class DimensionsRecord(BiffRecord):
1214 """
1215 Record DIMENSIONS, BIFF8:
1217 Offset Size Contents
1218 0 4 Index to first used row
1219 4 4 Index to last used row, increased by 1
1220 8 2 Index to first used column
1221 10 2 Index to last used column, increased by 1
1222 12 2 Not used
1223 """
1224 _REC_ID = 0x0200
1225 def __init__(self, first_used_row, last_used_row, first_used_col, last_used_col):
1226 if first_used_row > last_used_row or first_used_col > last_used_col:
1227 # Special case: empty worksheet
1228 first_used_row = first_used_col = 0
1229 last_used_row = last_used_col = -1
1230 self._rec_data = pack('<2L3H',
1231 first_used_row, last_used_row + 1,
1232 first_used_col, last_used_col + 1,
1233 0x00)
1236class Window2Record(BiffRecord):
1237 """
1238 Record WINDOW2, BIFF8:
1240 Offset Size Contents
1241 0 2 Option flags (see below)
1242 2 2 Index to first visible row
1243 4 2 Index to first visible column
1244 6 2 Colour index of grid line colour. Note that in BIFF2-BIFF7 an RGB colour is
1245 written instead.
1246 8 2 Not used
1247 10 2 Cached magnification factor in page break preview (in percent); 0 = Default (60%)
1248 12 2 Cached magnification factor in normal view (in percent); 0 = Default (100%)
1249 14 4 Not used
1251 In BIFF8 this record stores used magnification factors for page break
1252 preview and normal view. These values are used to restore the
1253 magnification, when the view is changed. The real magnification of the
1254 currently active view is stored in the SCL record. The type of the
1255 active view is stored in the option flags field (see below).
1257 0 0001H 0 = Show formula results 1 = Show formulas
1258 1 0002H 0 = Do not show grid lines 1 = Show grid lines
1259 2 0004H 0 = Do not show sheet headers 1 = Show sheet headers
1260 3 0008H 0 = Panes are not frozen 1 = Panes are frozen (freeze)
1261 4 0010H 0 = Show zero values as empty cells 1 = Show zero values
1262 5 0020H 0 = Manual grid line colour 1 = Automatic grid line colour
1263 6 0040H 0 = Columns from left to right 1 = Columns from right to left
1264 7 0080H 0 = Do not show outline symbols 1 = Show outline symbols
1265 8 0100H 0 = Keep splits if pane freeze is removed 1 = Remove splits if pane freeze is removed
1266 9 0200H 0 = Sheet not selected 1 = Sheet selected (BIFF5-BIFF8)
1267 10 0400H 0 = Sheet not visible 1 = Sheet visible (BIFF5-BIFF8)
1268 11 0800H 0 = Show in normal view 1 = Show in page break preview (BIFF8)
1270 The freeze flag specifies, if a following PANE record describes unfrozen or frozen panes.
1272 *** This class appends the optional SCL record ***
1274 Record SCL, BIFF4-BIFF8:
1276 This record stores the magnification of the active view of the current worksheet.
1277 In BIFF8 this can be either the normal view or the page break preview.
1278 This is determined in the WINDOW2 record. The SCL record is part of the
1279 Sheet View Settings Block.
1281 Offset Size Contents
1282 0 2 Numerator of the view magnification fraction (num)
1283 2 2 Denumerator [denominator] of the view magnification fraction (den)
1284 The magnification is stored as reduced fraction. The magnification results from num/den.
1286 SJM note: Excel expresses (e.g.) 25% in reduced form i.e. 1/4. Reason unknown. This code
1287 writes 25/100, and Excel is happy with that.
1289 """
1290 _REC_ID = 0x023E
1292 def __init__(self, options, first_visible_row, first_visible_col,
1293 grid_colour, preview_magn, normal_magn, scl_magn):
1294 self._rec_data = pack('<7HL', options,
1295 first_visible_row, first_visible_col,
1296 grid_colour,
1297 0x00,
1298 preview_magn, normal_magn,
1299 0x00)
1300 if scl_magn is not None:
1301 self._scl_rec = pack('<4H', 0x00A0, 4, scl_magn, 100)
1302 else:
1303 self._scl_rec = b''
1305 def get(self):
1306 return self.get_rec_header() + self._rec_data + self._scl_rec
1309class PanesRecord(BiffRecord):
1310 """
1311 This record stores the position of window panes. It is part of the Sheet
1312 View Settings Block. If the sheet does not contain any splits, this
1313 record will not occur.
1314 A sheet can be split in two different ways, with unfrozen panes or with
1315 frozen panes. A flag in the WINDOW2 record specifies, if the panes are
1316 frozen, which affects the contents of this record.
1318 Record PANE, BIFF2-BIFF8:
1319 Offset Size Contents
1320 0 2 Position of the vertical split
1321 (px, 0 = No vertical split):
1322 Unfrozen pane: Width of the left pane(s)
1323 (in twips = 1/20 of a point)
1324 Frozen pane: Number of visible
1325 columns in left pane(s)
1326 2 2 Position of the horizontal split
1327 (py, 0 = No horizontal split):
1328 Unfrozen pane: Height of the top pane(s)
1329 (in twips = 1/20 of a point)
1330 Frozen pane: Number of visible
1331 rows in top pane(s)
1332 4 2 Index to first visible row
1333 in bottom pane(s)
1334 6 2 Index to first visible column
1335 in right pane(s)
1336 8 1 Identifier of pane with active
1337 cell cursor
1338 [9] 1 Not used (BIFF5-BIFF8 only, not written
1339 in BIFF2-BIFF4)
1341 If the panes are frozen, pane 0 is always active, regardless
1342 of the cursor position. The correct identifiers for all possible
1343 combinations of visible panes are shown in the following pictures.
1345 px = 0, py = 0 px = 0, py > 0
1346 -------------------------- ------------|-------------
1347 | | | |
1348 | | | 3 |
1349 | | | |
1350 - 3 - --------------------------
1351 | | | |
1352 | | | 2 |
1353 | | | |
1354 -------------------------- ------------|-------------
1356 px > 0, py = 0 px > 0, py > 0
1357 ------------|------------- ------------|-------------
1358 | | | | | |
1359 | | | | 3 | 2 |
1360 | | | | | |
1361 - 3 | 1 - --------------------------
1362 | | | | | |
1363 | | | | 1 | 0 |
1364 | | | | | |
1365 ------------|------------- ------------|-------------
1366 """
1367 _REC_ID = 0x0041
1369 valid_active_pane = {
1370 # entries are of the form:
1371 # (int(px > 0),int(px>0)) -> allowed values
1372 (0,0):(3,),
1373 (0,1):(2,3),
1374 (1,0):(1,3),
1375 (1,1):(0,1,2,3),
1376 }
1378 def __init__(self, px, py, first_row_bottom, first_col_right, active_pane):
1379 allowed = self.valid_active_pane.get(
1380 (int(px > 0),int(py > 0))
1381 )
1382 if active_pane not in allowed:
1383 raise ValueError('Cannot set active_pane to %i, must be one of %s' % (
1384 active_pane, ', '.join(allowed)
1385 ))
1386 self._rec_data = pack('<5H',
1387 px, py,
1388 first_row_bottom, first_col_right,
1389 active_pane)
1392class RowRecord(BiffRecord):
1393 """
1394 This record contains the properties of a single row in a sheet. Rows
1395 and cells in a sheet are divided into blocks of 32 rows.
1397 Record ROW, BIFF3-BIFF8:
1399 Offset Size Contents
1400 0 2 Index of this row
1401 2 2 Index to column of the first cell which is described by a cell record
1402 4 2 Index to column of the last cell which is described by a cell record,
1403 increased by 1
1404 6 2 Bit Mask Contents
1405 14-0 7FFFH Height of the row, in twips = 1/20 of a point
1406 15 8000H 0 = Row has custom height; 1 = Row has default height
1407 8 2 Not used
1408 10 2 In BIFF3-BIFF4 this field contains a relative offset
1409 to calculate stream position of the first cell record
1410 for this row. In BIFF5-BIFF8 this field is not used
1411 anymore, but the DBCELL record instead.
1412 12 4 Option flags and default row formatting:
1413 Bit Mask Contents
1414 2-0 00000007H Outline level of the row
1415 4 00000010H 1 = Outline group starts or ends here (depending
1416 on where the outline buttons are located,
1417 see WSBOOL record), and is collapsed
1418 5 00000020H 1 = Row is hidden (manually, or by a filter or outline group)
1419 6 00000040H 1 = Row height and default font height do not match
1420 7 00000080H 1 = Row has explicit default format (fl)
1421 8 00000100H Always 1
1422 27-16 0FFF0000H If fl=1: Index to default XF record
1423 28 10000000H 1 = Additional space above the row. This flag is set,
1424 if the upper border of at least one cell in this row
1425 or if the lower border of at least one cell in the row
1426 above is formatted with a thick line style.
1427 Thin and medium line styles are not taken into account.
1428 29 20000000H 1 = Additional space below the row. This flag is set,
1429 if the lower border of at least one cell in this row
1430 or if the upper border of at least one cell in the row
1431 below is formatted with a medium or thick line style.
1432 Thin line styles are not taken into account.
1433 """
1435 _REC_ID = 0x0208
1437 def __init__(self, index, first_col, last_col, height_options, options):
1438 self._rec_data = pack('<6HL', index, first_col, last_col + 1,
1439 height_options,
1440 0x00, 0x00,
1441 options)
1443class LabelSSTRecord(BiffRecord):
1444 """
1445 This record represents a cell that contains a string. It replaces the
1446 LABEL record and RSTRING record used in BIFF2-BIFF7.
1447 """
1448 _REC_ID = 0x00FD
1450 def __init__(self, row, col, xf_idx, sst_idx):
1451 self._rec_data = pack('<3HL', row, col, xf_idx, sst_idx)
1454class MergedCellsRecord(BiffRecord):
1455 """
1456 This record contains all merged cell ranges of the current sheet.
1458 Record MERGEDCELLS, BIFF8:
1460 Offset Size Contents
1461 0 var. Cell range address list with all merged ranges
1463 ------------------------------------------------------------------
1465 A cell range address list consists of a field with the number of ranges
1466 and the list of the range addresses.
1468 Cell range address list, BIFF8:
1470 Offset Size Contents
1471 0 2 Number of following cell range addresses (nm)
1472 2 8*nm List of nm cell range addresses
1474 ---------------------------------------------------------------------
1475 Cell range address, BIFF8:
1477 Offset Size Contents
1478 0 2 Index to first row
1479 2 2 Index to last row
1480 4 2 Index to first column
1481 6 2 Index to last column
1483 """
1484 _REC_ID = 0x00E5
1486 def __init__(self, merged_list):
1487 i = len(merged_list) - 1
1488 while i >= 0:
1489 j = 0
1490 merged = b''
1491 while (i >= 0) and (j < 0x403):
1492 r1, r2, c1, c2 = merged_list[i]
1493 merged += pack('<4H', r1, r2, c1, c2)
1494 i -= 1
1495 j += 1
1496 self._rec_data += pack('<3H', self._REC_ID, len(merged) + 2, j) + \
1497 merged
1499 # for some reason Excel doesn't use CONTINUE
1500 def get(self):
1501 return self._rec_data
1503class MulBlankRecord(BiffRecord):
1504 """
1505 This record represents a cell range of empty cells. All cells are
1506 located in the same row.
1508 Record MULBLANK, BIFF5-BIFF8:
1510 Offset Size Contents
1511 0 2 Index to row
1512 2 2 Index to first column (fc)
1513 4 2*nc List of nc=lc-fc+1 16-bit indexes to XF records
1514 4+2*nc 2 Index to last column (lc)
1515 """
1516 _REC_ID = 0x00BE
1518 def __init__(self, row, first_col, last_col, xf_index):
1519 blanks_count = last_col-first_col+1
1520 self._rec_data = pack('<%dH' % blanks_count, *([xf_index] * blanks_count))
1521 self._rec_data = pack('<2H', row, first_col) + self._rec_data + pack('<H', last_col)
1524class BlankRecord(BiffRecord):
1525 """
1526 This record represents an empty cell.
1528 Record BLANK, BIFF5-BIFF8:
1530 Offset Size Contents
1531 0 2 Index to row
1532 2 2 Index to first column (fc)
1533 4 2 indexes to XF record
1534 """
1535 _REC_ID = 0x0201
1537 def __init__(self, row, col, xf_index):
1538 self._rec_data = pack('<3H', row, col, xf_index)
1541class RKRecord(BiffRecord):
1542 """
1543 This record represents a cell that contains an RK value (encoded integer or
1544 floating-point value). If a floating-point value cannot be encoded to an RK value,
1545 a NUMBER record will be written.
1546 """
1547 _REC_ID = 0x027E
1549 def __init__(self, row, col, xf_index, rk_encoded):
1550 self._rec_data = pack('<3Hi', row, col, xf_index, rk_encoded)
1553class NumberRecord(BiffRecord):
1554 """
1555 This record represents a cell that contains an IEEE-754 floating-point value.
1556 """
1557 _REC_ID = 0x0203
1559 def __init__(self, row, col, xf_index, number):
1560 self._rec_data = pack('<3Hd', row, col, xf_index, number)
1562class BoolErrRecord(BiffRecord):
1563 """
1564 This record represents a cell that contains a boolean or error value.
1565 """
1566 _REC_ID = 0x0205
1568 def __init__(self, row, col, xf_index, number, is_error):
1569 self._rec_data = pack('<3HBB', row, col, xf_index, number, is_error)
1572class FormulaRecord(BiffRecord):
1573 """
1574 Offset Size Contents
1575 0 2 Index to row
1576 2 2 Index to column
1577 4 2 Index to XF record
1578 6 8 Result of the formula
1579 14 2 Option flags:
1580 Bit Mask Contents
1581 0 0001H 1 = Recalculate always
1582 1 0002H 1 = Calculate on open
1583 3 0008H 1 = Part of a shared formula
1584 16 4 Not used
1585 20 var. Formula data (RPN token array)
1587 """
1588 _REC_ID = 0x0006
1590 def __init__(self, row, col, xf_index, rpn, calc_flags=0):
1591 self._rec_data = pack('<3HQHL', row, col, xf_index, 0xFFFF000000000003, calc_flags & 3, 0) + rpn
1594class GutsRecord(BiffRecord):
1595 """
1596 This record contains information about the layout of outline symbols.
1598 Record GUTS, BIFF3-BIFF8:
1600 Offset Size Contents
1601 0 2 Width of the area to display row outlines (left of the sheet), in pixel
1602 2 2 Height of the area to display column outlines (above the sheet), in pixel
1603 4 2 Number of visible row outline levels (used row levels + 1; or 0, if not used)
1604 6 2 Number of visible column outline levels (used column levels + 1; or 0, if not used)
1606 """
1608 _REC_ID = 0x0080
1610 def __init__(self, row_gut_width, col_gut_height, row_visible_levels, col_visible_levels):
1611 self._rec_data = pack('<4H', row_gut_width, col_gut_height, row_visible_levels, col_visible_levels)
1613class WSBoolRecord(BiffRecord):
1614 """
1615 This record stores a 16 bit value with Boolean options for the current
1616 sheet. From BIFF5 on the "Save external linked values" option is moved
1617 to the record BOOKBOOL.
1619 Option flags of record WSBOOL, BIFF3-BIFF8:
1621 Bit Mask Contents
1622 0 0001H 0 = Do not show automatic page breaks
1623 1 = Show automatic page breaks
1624 4 0010H 0 = Standard sheet
1625 1 = Dialogue sheet (BIFF5-BIFF8)
1626 5 0020H 0 = No automatic styles in outlines
1627 1 = Apply automatic styles to outlines
1628 6 0040H 0 = Outline buttons above outline group
1629 1 = Outline buttons below outline group
1630 7 0080H 0 = Outline buttons left of outline group
1631 1 = Outline buttons right of outline group
1632 8 0100H 0 = Scale printout in percent
1633 1 = Fit printout to number of pages
1634 9 0200H 0 = Save external linked values (BIFF3?BIFF4 only)
1635 1 = Do not save external linked values (BIFF3?BIFF4 only)
1636 10 0400H 0 = Do not show row outline symbols
1637 1 = Show row outline symbols
1638 11 0800H 0 = Do not show column outline symbols
1639 1 = Show column outline symbols
1640 13-12 3000H These flags specify the arrangement of windows.
1641 They are stored in BIFF4 only.
1642 00 = Arrange windows tiled
1643 01 = Arrange windows horizontal
1644 10 = Arrange windows vertical112 = Arrange windows cascaded
1645 The following flags are valid for BIFF4-BIFF8 only:
1646 14 4000H 0 = Standard expression evaluation
1647 1 = Alternative expression evaluation
1648 15 8000H 0 = Standard formula entries
1649 1 = Alternative formula entries
1651 """
1652 _REC_ID = 0x0081
1654 def __init__(self, options):
1655 self._rec_data = pack('<H', options)
1657class ColInfoRecord(BiffRecord):
1658 """
1659 This record specifies the width for a given range of columns.
1660 If a column does not have a corresponding COLINFO record,
1661 the width specified in the record STANDARDWIDTH is used. If
1662 this record is also not present, the contents of the record
1663 DEFCOLWIDTH is used instead.
1664 This record also specifies a default XF record to use for
1665 cells in the columns that are not described by any cell record
1666 (which contain the XF index for that cell). Additionally,
1667 the option flags field contains hidden, outline, and collapsed
1668 options applied at the columns.
1670 Record COLINFO, BIFF3-BIFF8:
1672 Offset Size Contents
1673 0 2 Index to first column in the range
1674 2 2 Index to last column in the range
1675 4 2 Width of the columns in 1/256 of the width of the zero character, using default font
1676 (first FONT record in the file)
1677 6 2 Index to XF record for default column formatting
1678 8 2 Option flags:
1679 Bits Mask Contents
1680 0 0001H 1 = Columns are hidden
1681 10-8 0700H Outline level of the columns (0 = no outline)
1682 12 1000H 1 = Columns are collapsed
1683 10 2 Not used
1685 """
1686 _REC_ID = 0x007D
1688 def __init__(self, first_col, last_col, width, xf_index, options, unused):
1689 self._rec_data = pack('<6H', first_col, last_col, width, xf_index, options, unused)
1691class CalcModeRecord(BiffRecord):
1692 """
1693 This record is part of the Calculation Settings Block.
1694 It specifies whether to calculate formulas manually,
1695 automatically or automatically except for multiple table operations.
1697 Record CALCMODE, BIFF2-BIFF8:
1699 Offset Size Contents
1700 0 2 FFFFH = automatic except for multiple table operations
1701 0000H = manually
1702 0001H = automatically (default)
1703 """
1704 _REC_ID = 0x000D
1706 def __init__(self, calc_mode):
1707 self._rec_data = pack('<h', calc_mode)
1710class CalcCountRecord(BiffRecord):
1711 """
1712 This record is part of the Calculation Settings Block. It specifies the maximum
1713 number of times the formulas should be iteratively calculated. This is a fail-safe
1714 against mutually recursive formulas locking up a spreadsheet application.
1716 Record CALCCOUNT, BIFF2-BIFF8:
1718 Offset Size Contents
1719 0 2 Maximum number of iterations allowed in circular references
1720 """
1722 _REC_ID = 0x000C
1724 def __init__(self, calc_count):
1725 self._rec_data = pack('<H', calc_count)
1727class RefModeRecord(BiffRecord):
1728 """
1729 This record is part of the Calculation Settings Block.
1730 It stores which method is used to show cell addresses in formulas.
1731 The “RC” mode uses numeric indexes for rows and columns,
1732 i.e. “R(1)C(-1)”, or “R1C1:R2C2”.
1733 The “A1” mode uses characters for columns and numbers for rows,
1734 i.e. “B1”, or “$A$1:$B$2”.
1736 Record REFMODE, BIFF2-BIFF8:
1738 Offset Size Contents
1739 0 2 0 = RC mode; 1 = A1 mode
1741 """
1742 _REC_ID = 0x00F
1744 def __init__(self, ref_mode):
1745 self._rec_data = pack('<H', ref_mode)
1747class IterationRecord(BiffRecord):
1748 """
1749 This record is part of the Calculation Settings Block.
1750 It stores if iterations are allowed while calculating recursive formulas.
1752 Record ITERATION, BIFF2-BIFF8:
1754 Offset Size Contents
1755 0 2 0 = Iterations off; 1 = Iterations on
1756 """
1757 _REC_ID = 0x011
1759 def __init__(self, iterations_on):
1760 self._rec_data = pack('<H', iterations_on)
1762class DeltaRecord(BiffRecord):
1763 """
1764 This record is part of the Calculation Settings Block.
1765 It stores the maximum change of the result to exit an iteration.
1767 Record DELTA, BIFF2-BIFF8:
1769 Offset Size Contents
1770 0 8 Maximum change in iteration
1771 (IEEE 754 floating-point value,
1772 64bit double precision)
1773 """
1774 _REC_ID = 0x010
1776 def __init__(self, delta):
1777 self._rec_data = pack('<d', delta)
1779class SaveRecalcRecord(BiffRecord):
1780 """
1781 This record is part of the Calculation Settings Block.
1782 It contains the “Recalculate before save” option in
1783 Excel's calculation settings dialogue.
1785 Record SAVERECALC, BIFF3-BIFF8:
1787 Offset Size Contents
1788 0 2 0 = Do not recalculate;
1789 1 = Recalculate before saving the document
1791 """
1792 _REC_ID = 0x05F
1794 def __init__(self, recalc):
1795 self._rec_data = pack('<H', recalc)
1797class PrintHeadersRecord(BiffRecord):
1798 """
1799 This record stores if the row and column headers
1800 (the areas with row numbers and column letters) will be printed.
1802 Record PRINTHEADERS, BIFF2-BIFF8:
1804 Offset Size Contents
1805 0 2 0 = Do not print row/column headers;
1806 1 = Print row/column headers
1807 """
1808 _REC_ID = 0x02A
1810 def __init__(self, print_headers):
1811 self._rec_data = pack('<H', print_headers)
1814class PrintGridLinesRecord(BiffRecord):
1815 """
1816 This record stores if sheet grid lines will be printed.
1818 Record PRINTGRIDLINES, BIFF2-BIFF8:
1820 Offset Size Contents
1821 0 2 0 = Do not print sheet grid lines;
1822 1 = Print sheet grid lines
1824 """
1825 _REC_ID = 0x02B
1827 def __init__(self, print_grid):
1828 self._rec_data = pack('<H', print_grid)
1831class GridSetRecord(BiffRecord):
1832 """
1833 This record specifies if the option to print sheet grid lines
1834 (record PRINTGRIDLINES) has ever been changed.
1836 Record GRIDSET, BIFF3-BIFF8:
1838 Offset Size Contents
1839 0 2 0 = Print grid lines option never changed
1840 1 = Print grid lines option changed
1841 """
1842 _REC_ID = 0x082
1844 def __init__(self, print_grid_changed):
1845 self._rec_data = pack('<H', print_grid_changed)
1848class DefaultRowHeightRecord(BiffRecord):
1849 """
1850 This record specifies the default height and default flags
1851 for rows that do not have a corresponding ROW record.
1853 Record DEFAULTROWHEIGHT, BIFF3-BIFF8:
1855 Offset Size Contents
1856 0 2 Option flags:
1857 Bit Mask Contents
1858 0 0001H 1 = Row height and default font height do not match
1859 1 0002H 1 = Row is hidden
1860 2 0004H 1 = Additional space above the row
1861 3 0008H 1 = Additional space below the row
1862 2 2 Default height for unused rows, in twips = 1/20 of a point
1864 """
1865 _REC_ID = 0x0225
1867 def __init__(self, options, def_height):
1868 self._rec_data = pack('<2H', options, def_height)
1871class DefColWidthRecord(BiffRecord):
1872 """
1873 This record specifies the default column width for columns that
1874 do not have a specific width set using the record COLINFO or COLWIDTH.
1875 This record has no effect, if a STANDARDWIDTH record is present in the file.
1877 Record DEFCOLWIDTH, BIFF2-BIFF8:
1879 Offset Size Contents
1880 0 2 Column width in characters, using the width of the zero
1881 character from default font (first FONT record in the file)
1882 """
1883 _REC_ID = 0x0055
1885 def __init__(self, def_width):
1886 self._rec_data = pack('<H', options, def_width)
1888class HorizontalPageBreaksRecord(BiffRecord):
1889 """
1890 This record is part of the Page Settings Block. It contains all
1891 horizontal manual page breaks.
1893 Record HORIZONTALPAGEBREAKS, BIFF8:
1894 Offset Size Contents
1895 0 2 Number of following row index structures (nm)
1896 2 6nm List of nm row index structures. Each row index
1897 structure contains:
1898 Offset Size Contents
1899 0 2 Index to first row below the page break
1900 2 2 Index to first column of this page break
1901 4 2 Index to last column of this page break
1903 The row indexes in the lists must be ordered ascending.
1904 If in BIFF8 a row contains several page breaks, they must be ordered
1905 ascending by start column index.
1906 """
1907 _REC_ID = 0x001B
1909 def __init__(self, breaks_list):
1910 self._rec_data = pack('<H', len(breaks_list))
1911 for r, c1, c2 in breaks_list:
1912 self._rec_data += pack('<3H', r, c1, c2)
1914class VerticalPageBreaksRecord(BiffRecord):
1915 """
1916 This record is part of the Page Settings Block. It contains all
1917 vertical manual page breaks.
1919 Record VERTICALPAGEBREAKS, BIFF8:
1920 Offset Size Contents
1921 0 2 Number of following column index structures (nm)
1922 2 6nm List of nm column index structures. Each column index
1923 structure contains:
1924 Offset Size Contents
1925 0 2 Index to first column following the page
1926 break
1927 2 2 Index to first row of this page break
1928 4 2 Index to last row of this page break
1930 The column indexes in the lists must be ordered ascending.
1931 If in BIFF8 a column contains several page breaks, they must be ordered
1932 ascending by start row index.
1933 """
1934 _REC_ID = 0x001A
1936 def __init__(self, breaks_list):
1937 self._rec_data = pack('<H', len(breaks_list))
1938 for r, c1, c2 in breaks_list:
1939 self._rec_data += pack('<3H', r, c1, c2)
1941class HeaderRecord(BiffRecord):
1942 """
1943 This record is part of the Page Settings Block. It specifies the
1944 page header string for the current worksheet. If this record is not
1945 present or completely empty (record size is 0), the sheet does not
1946 contain a page header.
1948 Record HEADER for non-empty page header, BIFF2-BIFF8:
1949 Offset Size Contents
1950 0 var. Page header string
1951 BIFF2-BIFF7: Non-empty byte string, 8bit string
1952 length
1953 BIFF8: Non-empty Unicode string, 16bit string length
1954 The header string may contain special commands, i.e. placeholders for
1955 the page number, current date, or text formatting attributes. These
1956 fields are represented by single letters (exception: font name and
1957 size, see below) with a leading ampersand ("&"). If the ampersand
1958 is part of the regular header text, it will be duplicated ("&&"). The
1959 page header is divided into 3 sections: the left, the centred, and the
1960 right section. Each section is introduced by a special command. All
1961 text and all commands following are part of the selected section. Each
1962 section starts with the text formatting specified in the default font
1963 (first FONT record in the file). Active formatting attributes from
1964 a previous section do not go into the next section.
1966 The following table shows all available commands:
1968 Command Contents
1969 && The "&" character itself
1970 &L Start of the left section
1971 &C Start of the centred section
1972 &R Start of the right section
1973 &P Current page number
1974 &N Page count
1975 &D Current date
1976 &T Current time
1977 &A Sheet name (BIFF5-BIFF8)
1978 &F File name without path
1979 &Z File path without file name (BIFF8X)
1980 &G Picture (BIFF8X)
1981 &B Bold on/off (BIFF2-BIFF4)
1982 &I Italic on/off (BIFF2-BIFF4)
1983 &U Underlining on/off
1984 &E Double underlining on/off (BIFF5-BIFF8)
1985 &S Strikeout on/off
1986 &X Superscript on/off (BIFF5-BIFF8)
1987 &Y Subscript on/off (BIFF5-BIFF8)
1988 &"<fontname>" Set new font <fontname>
1989 &"<fontname>,<fontstyle>"
1990 Set new font with specified style <fontstyle>.
1991 The style <fontstyle> is in most cases one of
1992 "Regular", "Bold", "Italic", or "Bold Italic".
1993 But this setting is dependent on the used font,
1994 it may differ (localised style names, or "Standard",
1995 "Oblique", ...). (BIFF5-BIFF8)
1996 &<fontheight> Set font height in points (<fontheight> is a decimal value).
1997 If this command is followed by a plain number to be printed
1998 in the header, it will be separated from the font height
1999 with a space character.
2001 """
2002 _REC_ID = 0x0014
2004 def __init__(self, header_str):
2005 self._rec_data = upack2(header_str)
2007class FooterRecord(BiffRecord):
2008 """
2009 Semantic is equal to HEADER record
2010 """
2011 _REC_ID = 0x0015
2013 def __init__(self, footer_str):
2014 self._rec_data = upack2(footer_str)
2017class HCenterRecord(BiffRecord):
2018 """
2019 This record is part of the Page Settings Block. It specifies if the
2020 sheet is centred horizontally when printed.
2022 Record HCENTER, BIFF3-BIFF8:
2024 Offset Size Contents
2025 0 2 0 = Print sheet left aligned
2026 1 = Print sheet centred horizontally
2028 """
2029 _REC_ID = 0x0083
2031 def __init__(self, is_horz_center):
2032 self._rec_data = pack('<H', is_horz_center)
2035class VCenterRecord(BiffRecord):
2036 """
2037 This record is part of the Page Settings Block. It specifies if the
2038 sheet is centred vertically when printed.
2040 Record VCENTER, BIFF3-BIFF8:
2042 Offset Size Contents
2043 0 2 0 = Print sheet aligned at top page border
2044 1 = Print sheet vertically centred
2046 """
2047 _REC_ID = 0x0084
2049 def __init__(self, is_vert_center):
2050 self._rec_data = pack('<H', is_vert_center)
2053class LeftMarginRecord(BiffRecord):
2054 """
2055 This record is part of the Page Settings Block. It contains the left
2056 page margin of the current worksheet.
2058 Record LEFTMARGIN, BIFF2-BIFF8:
2060 Offset Size Contents
2061 0 8 Left page margin in inches
2062 (IEEE 754 floating-point value, 64bit double precision)
2064 """
2065 _REC_ID = 0x0026
2067 def __init__(self, margin):
2068 self._rec_data = pack('<d', margin)
2071class RightMarginRecord(BiffRecord):
2072 """
2073 This record is part of the Page Settings Block. It contains the right
2074 page margin of the current worksheet.
2076 Offset Size Contents
2077 0 8 Right page margin in inches
2078 (IEEE 754 floating-point value, 64?bit double precision)
2080 """
2081 _REC_ID = 0x0027
2083 def __init__(self, margin):
2084 self._rec_data = pack('<d', margin)
2086class TopMarginRecord(BiffRecord):
2087 """
2088 This record is part of the Page Settings Block. It contains the top
2089 page margin of the current worksheet.
2091 Offset Size Contents
2092 0 8 Top page margin in inches
2093 (IEEE 754 floating-point value, 64?bit double precision)
2095 """
2096 _REC_ID = 0x0028
2098 def __init__(self, margin):
2099 self._rec_data = pack('<d', margin)
2102class BottomMarginRecord(BiffRecord):
2103 """
2104 This record is part of the Page Settings Block. It contains the bottom
2105 page margin of the current worksheet.
2107 Offset Size Contents
2108 0 8 Bottom page margin in inches
2109 (IEEE 754 floating-point value, 64?bit double precision)
2111 """
2112 _REC_ID = 0x0029
2114 def __init__(self, margin):
2115 self._rec_data = pack('<d', margin)
2117class SetupPageRecord(BiffRecord):
2118 """
2119 This record is part of the Page Settings Block. It stores the page
2120 format settings of the current sheet. The pages may be scaled in
2121 percent or by using an absolute number of pages. This setting is
2122 located in the WSBOOL record. If pages are scaled in percent,
2123 the scaling factor in this record is used, otherwise the "Fit to
2124 pages" values. One of the "Fit to pages" values may be 0. In this case
2125 the sheet is scaled to fit only to the other value.
2127 Record SETUP, BIFF5-BIFF8:
2129 Offset Size Contents
2130 0 2 Paper size (see below)
2131 2 2 Scaling factor in percent
2132 4 2 Start page number
2133 6 2 Fit worksheet width to this number of pages
2134 (0 = use as many as needed)
2135 8 2 Fit worksheet height to this number of pages
2136 (0 = use as many as needed)
2137 10 2 Option flags:
2138 Bit Mask Contents
2139 0 0001H 0 = Print pages in columns
2140 1 = Print pages in rows
2141 1 0002H 0 = Landscape
2142 1 = Portrait
2143 2 0004H 1 = Paper size, scaling factor,
2144 paper orientation (portrait/landscape),
2145 print resolution and number of copies
2146 are not initialised
2147 3 0008H 0 = Print coloured
2148 1 = Print black and white
2149 4 0010H 0 = Default print quality
2150 1 = Draft quality
2151 5 0020H 0 = Do not print cell notes
2152 1 = Print cell notes
2153 6 0040H 0 = Paper orientation setting is valid
2154 1 = Paper orientation setting not
2155 initialised
2156 7 0080H 0 = Automatic page numbers
2157 1 = Use start page number
2158 The following flags are valid for BIFF8 only:
2159 9 0200H 0 = Print notes as displayed
2160 1 = Print notes at end of sheet
2161 11-10 0C00H 00 = Print errors as displayed
2162 01 = Do not print errors
2163 10 = Print errors as "--"
2164 11 = Print errors as "#N/A!"
2165 12 2 Print resolution in dpi
2166 14 2 Vertical print resolution in dpi
2167 16 8 Header margin (IEEE 754 floating-point value,
2168 64bit double precision)
2169 24 8 Footer margin (IEEE 754 floating-point value,
2170 64bit double precision)
2171 32 2 Number of copies to print
2174 PAPER TYPES:
2176 Index Paper type Paper size
2177 0 Undefined
2178 1 Letter 8 1/2" x 11"
2179 2 Letter small 8 1/2" x 11"
2180 3 Tabloid 11" x 17"
2181 4 Ledger 17" x 11"
2182 5 Legal 8 1/2" x 14"
2183 6 Statement 5 1/2" x 8 1/2"
2184 7 Executive 7 1/4" x 10 1/2"
2185 8 A3 297mm x 420mm
2186 9 A4 210mm x 297mm
2187 10 A4 small 210mm x 297mm
2188 11 A5 148mm x 210mm
2189 12 B4 (JIS) 257mm x 364mm
2190 13 B5 (JIS) 182mm x 257mm
2191 14 Folio 8 1/2" x 13"
2192 15 Quarto 215mm x 275mm
2193 16 10x14 10" x 14"
2194 17 11x17 11" x 17"
2195 18 Note 8 1/2" x 11"
2196 19 Envelope #9 3 7/8" x 8 7/8"
2197 20 Envelope #10 4 1/8" x 9 1/2"
2198 21 Envelope #11 4 1/2" x 10 3/8"
2199 22 Envelope #12 4 3/4" x 11"
2200 23 Envelope #14 5" x 11 1/2"
2201 24 C 17" x 22"
2202 25 D 22" x 34"
2203 26 E 34" x 44"
2204 27 Envelope DL 110mm x 220mm
2205 28 Envelope C5 162mm x 229mm
2206 29 Envelope C3 324mm x 458mm
2207 30 Envelope C4 229mm x 324mm
2208 31 Envelope C6 114mm x 162mm
2209 32 Envelope C6/C5 114mm x 229mm
2210 33 B4 (ISO) 250mm x 353mm
2211 34 B5 (ISO) 176mm x 250mm
2212 35 B6 (ISO) 125mm x 176mm
2213 36 Envelope Italy 110mm x 230mm
2214 37 Envelope Monarch 3 7/8" x 7 1/2"
2215 38 63/4 Envelope 3 5/8" x 6 1/2"
2216 39 US Standard Fanfold 14 7/8" x 11"
2217 40 German Std. Fanfold 8 1/2" x 12"
2218 41 German Legal Fanfold 8 1/2" x 13"
2219 42 B4 (ISO) 250mm x 353mm
2220 43 Japanese Postcard 100mm x 148mm
2221 44 9x11 9" x 11"
2222 45 10x11 10" x 11"
2223 46 15x11 15" x 11"
2224 47 Envelope Invite 220mm x 220mm
2225 48 Undefined
2226 49 Undefined
2227 50 Letter Extra 9 1/2" x 12"
2228 51 Legal Extra 9 1/2" x 15"
2229 52 Tabloid Extra 11 11/16" x 18"
2230 53 A4 Extra 235mm x 322mm
2231 54 Letter Transverse 8 1/2" x 11"
2232 55 A4 Transverse 210mm x 297mm
2233 56 Letter Extra Transv. 9 1/2" x 12"
2234 57 Super A/A4 227mm x 356mm
2235 58 Super B/A3 305mm x 487mm
2236 59 Letter Plus 8 1/2" x 12 11/16"
2237 60 A4 Plus 210mm x 330mm
2238 61 A5 Transverse 148mm x 210mm
2239 62 B5 (JIS) Transverse 182mm x 257mm
2240 63 A3 Extra 322mm x 445mm
2241 64 A5 Extra 174mm x 235mm
2242 65 B5 (ISO) Extra 201mm x 276mm
2243 66 A2 420mm x 594mm
2244 67 A3 Transverse 297mm x 420mm
2245 68 A3 Extra Transverse 322mm x 445mm
2246 69 Dbl. Japanese Postcard 200mm x 148mm
2247 70 A6 105mm x 148mm
2248 71
2249 72
2250 73
2251 74
2252 75 Letter Rotated 11" x 8 1/2"
2253 76 A3 Rotated 420mm x 297mm
2254 77 A4 Rotated 297mm x 210mm
2255 78 A5 Rotated 210mm x 148mm
2256 79 B4 (JIS) Rotated 364mm x 257mm
2257 80 B5 (JIS) Rotated 257mm x 182mm
2258 81 Japanese Postcard Rot. 148mm x 100mm
2259 82 Dbl. Jap. Postcard Rot. 148mm x 200mm
2260 83 A6 Rotated 148mm x 105mm
2261 84
2262 85
2263 86
2264 87
2265 88 B6 (JIS) 128mm x 182mm
2266 89 B6 (JIS) Rotated 182mm x 128mm
2267 90 12x11 12" x 11"
2269 """
2270 _REC_ID = 0x00A1
2271 def __init__(self, paper, scaling, start_num, fit_width_to, fit_height_to,
2272 options,
2273 hres, vres,
2274 header_margin, footer_margin,
2275 num_copies):
2276 self._rec_data = pack('<8H2dH', paper, scaling, start_num,
2277 fit_width_to, fit_height_to, \
2278 options,
2279 hres, vres,
2280 header_margin, footer_margin,
2281 num_copies)
2283class NameRecord(BiffRecord):
2284 """
2285 This record is part of a Link Table. It contains the name and the token
2286 array of an internal defined name. Token arrays of defined names
2287 contain tokens with aberrant token classes.
2289 Record NAME, BIFF5/BIFF7:
2290 Offset Size Contents
2291 0 2 Option flags, see below
2292 2 1 Keyboard shortcut (only for command macro names, see below)
2293 3 1 Length of the name (character count, ln)
2294 4 2 Size of the formula data (sz)
2295 6 2 0 = Global name, otherwise index to EXTERNSHEET record (one-based)
2296 8 2 0 = Global name, otherwise index to sheet (one-based)
2297 10 1 Length of menu text (character count, lm)
2298 11 1 Length of description text (character count, ld)
2299 12 1 Length of help topic text (character count, lh)
2300 13 1 Length of status bar text (character count, ls)
2301 14 ln Character array of the name
2302 14+ln sz Formula data (RPN token array without size field, 4)
2303 14+ln+sz lm Character array of menu text
2304 var. ld Character array of description text
2305 var. lh Character array of help topic text
2306 var. ls Character array of status bar text
2308 Record NAME, BIFF8:
2309 Offset Size Contents
2310 0 2 Option flags, see below
2311 2 1 Keyboard shortcut (only for command macro names, see below)
2312 3 1 Length of the name (character count, ln)
2313 4 2 Size of the formula data (sz)
2314 6 2 Not used
2315 8 2 0 = Global name, otherwise index to sheet (one-based)
2316 10 1 Length of menu text (character count, lm)
2317 11 1 Length of description text (character count, ld)
2318 12 1 Length of help topic text (character count, lh)
2319 13 1 Length of status bar text (character count, ls)
2320 14 var. Name (Unicode string without length field, 3.4)
2321 var. sz Formula data (RPN token array without size field, 4)
2322 [var.] var. (optional, only if lm > 0) Menu text (Unicode string without length field, 3.4)
2323 [var.] var. (optional, only if ld > 0) Description text (Unicode string without length field, 3.4)
2324 [var.] var. (optional, only if lh > 0) Help topic text (Unicode string without length field, 3.4)
2325 [var.] var. (optional, only if ls > 0) Status bar text (Unicode string without length field, 3.4)
2326 """
2327 _REC_ID = 0x0018
2329 def __init__(self, options, keyboard_shortcut, name, sheet_index, rpn, menu_text='', desc_text='', help_text='', status_text=''):
2330 if type(name) == int:
2331 uname = chr(name)
2332 else:
2333 uname = upack1(name)[1:]
2334 uname_len = len(uname)
2336 #~ self._rec_data = pack('<HBBHHHBBBB%ds%ds' % (uname_len, len(rpn)), options, keyboard_shortcut, uname_len, len(rpn), 0x0000, sheet_index, len(menu_text), len(desc_text), len(help_text), len(status_text), uname, rpn) + menu_text + desc_text + help_text + status_text
2337 self._rec_data = pack('<HBBHHHBBBBB%ds%ds' % (uname_len, len(rpn)), options, keyboard_shortcut, uname_len, len(rpn), 0x0000, sheet_index, 0x00, len(menu_text), len(desc_text), len(help_text), len(status_text), uname, rpn) + menu_text + desc_text + help_text + status_text
2339# Excel (both 2003 and 2007) don't like refs
2340# split over a record boundary, which is what the
2341# standard BiffRecord.get method does.
2343# 8224 max data bytes in a BIFF record
2344# 6 bytes per ref
2345# 1370 = floor((8224 - 2) / 6.0) max refs in a record
2347_maxRefPerRecord = 1370
2349class ExternSheetRecord(BiffRecord):
2350 """
2351 In BIFF8 the record stores a list with indexes to SUPBOOK
2352 records (list of REF structures, 6.100). See 5.10.3 for
2353 details about external references in BIFF8.
2355 Record EXTERNSHEET, BIFF8:
2356 Offset Size Contents
2357 0 2 Number of following REF structures (nm)
2358 2 6nm List of nm REF structures. Each REF contains the following data:
2359 Offset Size Contents
2360 0 2 Index to SUPBOOK record
2361 2 2 Index to first SUPBOOK sheet
2362 4 2 Index to last SUPBOOK sheet
2363 """
2364 _REC_ID = 0x0017
2366 def __init__(self, refs):
2368 # do we always need this ref? or only if there are no refs?
2369 # (I believe that if there are no refs then we should not generate the link table - Ruben)
2370 #refs.insert(0, (0,0,0))
2372 self.refs = refs
2374 def get(self):
2375 res = []
2376 nrefs = len(self.refs)
2377 for idx in xrange(0, nrefs, _maxRefPerRecord):
2378 chunk = self.refs[idx:idx+_maxRefPerRecord]
2379 krefs = len(chunk)
2380 if idx: # CONTINUE record
2381 header = pack("<HH", 0x003C, 6 * krefs)
2382 else: # ExternSheetRecord
2383 header = pack("<HHH", self._REC_ID, 6 * krefs + 2, nrefs)
2384 res.append(header)
2385 res.extend(pack("<HHH", *r) for r in chunk)
2386 return b''.join(res)
2388class SupBookRecord(BiffRecord):
2389 """
2390 This record mainly stores the URL of an external document
2391 and a list of sheet names inside this document. Furthermore
2392 it is used to store DDE and OLE object links, or to indicate
2393 an internal 3D reference or an add-in function. See 5.10.3
2394 for details about external references in BIFF8.
2396 """
2397 _REC_ID = 0x01AE
2399class InternalReferenceSupBookRecord(SupBookRecord):
2400 """
2401 In each file occurs a SUPBOOK that is used for internal 3D
2402 references. It stores the number of sheets of the own document.
2404 Record SUPBOOK for 3D references, BIFF8:
2405 Offset Size Contents
2406 0 2 Number of sheets in this document
2407 2 2 01H 04H (relict of BIFF5/BIFF7, the byte string "<04H>", see 3.9.1)
2409 """
2411 def __init__(self, num_sheets):
2412 self._rec_data = pack('<HBB', num_sheets, 0x01, 0x04)
2414class XcallSupBookRecord(SupBookRecord):
2415 """
2416 Add-in function names are stored in EXTERNNAME records following this record.
2418 Offset Size Contents
2419 0 2 0001H
2420 2 2 01H 3AH (relict of BIFF5, the byte string ':', see EXTERNSHEET record, 5.41)
2422 """
2424 def __init__(self):
2425 self._rec_data = pack('<HBB', 1, 0x01, 0x3A)
2428class ExternnameRecord(BiffRecord):
2429 """
2430 Record EXTERNNAME for external names and Analysis add-in functions, BIFF5-BIFF8:
2431 Offset Size Contents
2432 0 2 Option flags (see below)
2433 2 2 0 for global names, or:
2434 BIFF5: One-based index to EXTERNSHEET record containing the sheet name,
2435 BIFF8: One-based index to sheet list in preceding EXTERNALBOOK record.
2436 4 2 Not used
2437 6 var. BIFF5: Name (byte string, 8-bit string length, ?2.5.2).
2438 BIFF8: Name (Unicode string, 8-bit string length, ?2.5.3).
2439 See DEFINEDNAME record (?5.33) for a list of built-in names, if the built-in flag is set
2440 in the option flags above.
2441 var. var. Formula data (RPN token array, ?3)
2443 Option flags for external names (BIFF5-BIFF8)
2444 Bit Mask Contents
2445 0 0001H 0 = Standard name; 1 = Built-in name
2446 1 0002H 0 = Manual link; 1 = Automatic link (DDE links and OLE links only)
2447 2 0004H 1 = Picture link (DDE links and OLE links only)
2448 3 0008H 1 = This is the “StdDocumentName” identifier (DDE links only)
2449 4 0010H 1 = OLE link
2450 14-5 7FE0H Clipboard format of last successful update (DDE links and OLE links only)
2451 15 8000H 1 = Iconified picture link (BIFF8 OLE links only)
2452 """
2453 _REC_ID = 0x0023
2455 def __init__(self, options=0, index=0, name=None, fmla=None):
2456 self._rec_data = pack('<HHH', options, index, 0) + upack1(name) + fmla