#!/usr/bin/env python
# -*- coding: windows-1251 -*-
# Copyright (C) 2005 Roman V. Kiseliov
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
#
# 1. Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
#
# 2. Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in
# the documentation and/or other materials provided with the
# distribution.
#
# 3. All advertising materials mentioning features or use of this
# software must display the following acknowledgment:
# "This product includes software developed by
# Roman V. Kiseliov <roman@kiseliov.ru>."
#
# 4. Redistributions of any form whatsoever must retain the following
# acknowledgment:
# "This product includes software developed by
# Roman V. Kiseliov <roman@kiseliov.ru>."
#
# THIS SOFTWARE IS PROVIDED BY Roman V. Kiseliov ``AS IS'' AND ANY
# EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
# PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL Roman V. Kiseliov OR
# ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
# NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
# STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
# ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
# OF THE POSSIBILITY OF SUCH DAMAGE.
__rev_id__ = """$Id: BIFFRecords.py,v 1.7 2005/10/26 07:44:24 rvk Exp $"""
from struct import pack
from UnicodeUtils import *
import sys
[docs]class SharedStringTable(object):
_SST_ID = 0x00FC
_CONTINUE_ID = 0x003C
def __init__(self):
self._sst_record = ''
self._continues = []
self._current_piece = pack('<II', 0, 0)
self._pos = len(self._current_piece)
self._str_indexes = {}
self._add_calls = 0
[docs] def add_str(self, s):
self._add_calls += 1
if s not in self._str_indexes:
self._str_indexes[s] = len(self._str_indexes)
self._add_to_sst(s)
return self._str_indexes[s]
[docs] def str_index(self, s):
return self._str_indexes[s]
[docs] def get_biff_record(self):
self._new_piece()
result = pack('<2HII', self._SST_ID, len(self._sst_record), self._add_calls, len(self._str_indexes))
result += self._sst_record[8:]
result += ''.join(self._continues)
return result
def _add_to_sst(self, s):
u_str = upack2(s)
if len(u_str) > 0xFFFF:
raise Exception('error: very long string.')
is_unicode_str = u_str[2] == '\x01'
if is_unicode_str:
atom_len = 5 # 2 byte -- len,
# 1 byte -- options,
# 2 byte -- 1st sym
else:
atom_len = 4 # 2 byte -- len,
# 1 byte -- options,
# 1 byte -- 1st sym
self._save_atom(u_str[0:atom_len])
self._save_splitted(u_str[atom_len:], is_unicode_str)
def _new_piece(self):
if self._sst_record == '':
self._sst_record = self._current_piece
else:
curr_piece_len = len(self._current_piece)
self._continues.append(pack('<2H%ds'%curr_piece_len, self._CONTINUE_ID, curr_piece_len, self._current_piece))
self._current_piece = ''
self._pos = len(self._current_piece)
def _save_atom(self, s):
atom_len = len(s)
free_space = 0x2020 - len(self._current_piece)
if free_space < atom_len:
self._new_piece()
self._current_piece += s
def _save_splitted(self, s, is_unicode_str):
i = 0
str_len = len(s)
while i < str_len:
piece_len = len(self._current_piece)
free_space = 0x2020 - piece_len
tail_len = str_len - i
need_more_space = free_space < tail_len
if not need_more_space:
atom_len = tail_len
else:
if is_unicode_str:
atom_len = free_space & 0xFFFE
else:
atom_len = free_space
self._current_piece += s[i:i+atom_len]
if need_more_space:
self._new_piece()
if is_unicode_str:
self._current_piece += '\x01'
else:
self._current_piece += '\x00'
i += atom_len
[docs]class BiffRecord(object):
def __init__(self):
self._rec_data = ''
[docs] def get_rec_id(self):
return _REC_ID
[docs] def get_rec_data(self):
return self._rec_data
[docs] def get(self):
data = self.get_rec_data()
if len(data) > 0x2020: # limit for BIFF7/8
chunks = []
pos = 0
while pos < len(data):
chunk_pos = pos + 0x2020
chunk = data[pos:chunk_pos]
chunks.append(chunk)
pos = chunk_pos
continues = pack('<2H', self._REC_ID, len(chunks[0])) + chunks[0]
for chunk in chunks[1:]:
continues += pack('<2H%ds'%len(chunk), 0x003C, len(chunk), chunk)
# 0x003C -- CONTINUE record id
return continues
else:
return self.get_rec_header() + data
[docs]class Biff8BOFRecord(BiffRecord):
"""
Offset Size Contents
0 2 Version, contains 0600H for BIFF8 and BIFF8X
2 2 Type of the following data:
0005H = Workbook globals
0006H = Visual Basic module
0010H = Worksheet
0020H = Chart
0040H = Macro sheet
0100H = Workspace file
4 2 Build identifier
6 2 Build year
8 4 File history flags
12 4 Lowest Excel version that can read all records in this file
"""
_REC_ID = 0x0809
# stream types
BOOK_GLOBAL = 0x0005
VB_MODULE = 0x0006
WORKSHEET = 0x0010
CHART = 0x0020
MACROSHEET = 0x0040
WORKSPACE = 0x0100
def __init__(self, rec_type):
BiffRecord.__init__(self)
version = 0x0600
build = 0x0DBB
year = 0x07CC
file_hist_flags = 0x00L
ver_can_read = 0x06L
self._rec_data = pack('<4H2I', version, rec_type, build, year, file_hist_flags, ver_can_read)
[docs]class InteraceHdrRecord(BiffRecord):
_REC_ID = 0x00E1
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('BB', 0xB0, 0x04)
[docs]class InteraceEndRecord(BiffRecord):
_REC_ID = 0x00E2
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = ''
[docs]class MMSRecord(BiffRecord):
_REC_ID = 0x00C1
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class WriteAccessRecord(BiffRecord):
"""
This record is part of the file protection. It contains the name of the
user that has saved the file. The user name is always stored as an
equal-sized string. All unused characters after the name are filled
with space characters. It is not required to write the mentioned string
length. Every other length will be accepted too.
"""
_REC_ID = 0x005C
def __init__(self, owner):
BiffRecord.__init__(self)
uowner = owner[0:0x30]
uowner_len = len(uowner)
self._rec_data = pack('%ds%ds' % (uowner_len, 0x70 - uowner_len), uowner, ' '*(0x70 - uowner_len))
[docs]class DSFRecord(BiffRecord):
"""
This record specifies if the file contains an additional BIFF5/BIFF7
workbook stream.
Record DSF, BIFF8:
Offset Size Contents
0 2 0 = Only the BIFF8 Workbook stream is present
1 = Additional BIFF5/BIFF7 Book stream is in the file
A double stream file can be read by Excel 5.0 and Excel 95, and still
contains all new features added to BIFF8 (which are left out in the
BIFF5/BIFF7 Book stream).
"""
_REC_ID = 0x0161
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class TabIDRecord(BiffRecord):
_REC_ID = 0x013D
def __init__(self, sheetcount):
BiffRecord.__init__(self)
for i in range(sheetcount):
self._rec_data += pack('<H', i+1)
[docs]class FnGroupCountRecord(BiffRecord):
_REC_ID = 0x009C
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('BB', 0x0E, 0x00)
[docs]class WindowProtectRecord(BiffRecord):
"""
This record is part of the worksheet/workbook protection. It determines
whether the window configuration of this document is protected. Window
protection is not active, if this record is omitted.
"""
_REC_ID = 0x0019
def __init__(self, wndprotect):
BiffRecord.__init__(self)
self._rec_data = pack('<H', wndprotect)
[docs]class ObjectProtectRecord(BiffRecord):
"""
This record is part of the worksheet/workbook protection.
It determines whether the objects of the current sheet are protected.
Object protection is not active, if this record is omitted.
"""
_REC_ID = 0x0063
def __init__(self, objprotect):
BiffRecord.__init__(self)
self._rec_data = pack('<H', objprotect)
[docs]class ScenProtectRecord(BiffRecord):
"""
This record is part of the worksheet/workbook protection. It
determines whether the scenarios of the current sheet are protected.
Scenario protection is not active, if this record is omitted.
"""
_REC_ID = 0x00DD
def __init__(self, scenprotect):
BiffRecord.__init__(self)
self._rec_data = pack('<H', scenprotect)
[docs]class ProtectRecord(BiffRecord):
"""
This record is part of the worksheet/workbook protection. It specifies
whether a worksheet or a workbook is protected against modification.
Protection is not active, if this record is omitted.
"""
_REC_ID = 0x0012
def __init__(self, protect):
BiffRecord.__init__(self)
self._rec_data = pack('<H', protect)
[docs]class PasswordRecord(BiffRecord):
"""
This record is part of the worksheet/workbook protection. It
stores a 16-bit hash value, calculated from the worksheet or workbook
protection password.
"""
_REC_ID = 0x0013
[docs] def passwd_hash(self, plaintext):
"""
Based on the algorithm provided by Daniel Rentz of OpenOffice.
"""
if plaintext == "":
return 0
passwd_hash = 0x0000
for i, char in enumerate(plaintext):
c = ord(char) << (i + 1)
low_15 = c & 0x7fff
high_15 = c & 0x7fff << 15
high_15 = high_15 >> 15
c = low_15 | high_15
passwd_hash ^= c
passwd_hash ^= len(plaintext)
passwd_hash ^= 0xCE4B
return passwd_hash
def __init__(self, passwd = ""):
BiffRecord.__init__(self)
self._rec_data = pack('<H', self.passwd_hash(passwd))
[docs]class Prot4RevRecord(BiffRecord):
_REC_ID = 0x01AF
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class Prot4RevPassRecord(BiffRecord):
_REC_ID = 0x01BC
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class BackupRecord(BiffRecord):
"""
This record contains a Boolean value determining whether Excel makes
a backup of the file while saving.
"""
_REC_ID = 0x0040
def __init__(self, backup):
BiffRecord.__init__(self)
self._rec_data = pack('<H', backup)
[docs]class HideObjRecord(BiffRecord):
"""
This record specifies whether and how to show objects in the workbook.
Record HIDEOBJ, BIFF3-BIFF8:
Offset Size Contents
0 2 Viewing mode for objects:
0 = Show all objects
1 = Show placeholders
2 = Do not show objects
"""
_REC_ID = 0x008D
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class RefreshAllRecord(BiffRecord):
"""
"""
_REC_ID = 0x01B7
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class BookBoolRecord(BiffRecord):
"""
This record contains a Boolean value determining whether to save values
linked from external workbooks (CRN records and XCT records). In BIFF3
and BIFF4 this option is stored in the WSBOOL record.
Record BOOKBOOL, BIFF5-BIFF8:
Offset Size Contents
0 2 0 = Save external linked values;
1 = Do not save external linked values
"""
_REC_ID = 0x00DA
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x00)
[docs]class CountryRecord(BiffRecord):
"""
This record stores two Windows country identifiers. The first
represents the user interface language of the Excel version that has
saved the file, and the second represents the system regional settings
at the time the file was saved.
Record COUNTRY, BIFF3-BIFF8:
Offset Size Contents
0 2 Windows country identifier of the user interface language of Excel
2 2 Windows country identifier of the system regional settings
The following table shows most of the used country identifiers. Most
of these identifiers are equal to the international country calling
codes.
1 USA
2 Canada
7 Russia
"""
_REC_ID = 0x00DA
def __init__(self, ui_id, sys_settings_id):
BiffRecord.__init__(self)
self._rec_data = pack('<2H', ui_id, sys_settings_id)
[docs]class UseSelfsRecord(BiffRecord):
"""
This record specifies if the formulas in the workbook can use natural
language formulas”. This type of formula can refer to cells by its
content or the content of the column or row header cell.
Record USESELFS, BIFF8:
Offset Size Contents
0 2 0 = Do not use natural language formulas
1 = Use natural language formulas
"""
_REC_ID = 0x0160
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', 0x01)
[docs]class EOFRecord(BiffRecord):
_REC_ID = 0x000A
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = ''
[docs]class DateModeRecord(BiffRecord):
"""
This record specifies the base date for displaying date values. All
dates are stored as count of days past this base date. In BIFF2-BIFF4
this record is part of the Calculation Settings Block.
In BIFF5-BIFF8 it is stored in the Workbook Globals Substream.
Record DATEMODE, BIFF2-BIFF8:
Offset Size Contents
0 2 0 = Base is 1899-Dec-31 (the cell = 1 represents 1900-Jan-01)
1 = Base is 1904-Jan-01 (the cell = 1 represents 1904-Jan-02)
"""
_REC_ID = 0x0022
def __init__(self, from1904):
BiffRecord.__init__(self)
if from1904:
self._rec_data = pack('<H', 1)
else:
self._rec_data = pack('<H', 0)
[docs]class PrecisionRecord(BiffRecord):
"""
This record stores if formulas use the real cell values for calculation
or the values displayed on the screen. In BIFF2- BIFF4 this record
is part of the Calculation Settings Block. In BIFF5-BIFF8 it is stored
in the Workbook Globals Substream.
Record PRECISION, BIFF2-BIFF8:
Offset Size Contents
0 2 0 = Use displayed values;
1 = Use real cell values
"""
_REC_ID = 0x000E
def __init__(self, use_real_values):
BiffRecord.__init__(self)
if use_real_values:
self._rec_data = pack('<H', 1)
else:
self._rec_data = pack('<H', 0)
[docs]class CodepageBiff8Record(BiffRecord):
"""
This record stores the text encoding used to write byte strings, stored
as MS Windows code page identifier. The CODEPAGE record in BIFF8 always
contains the code page 1200 (UTF-16). Therefore it is not
possible to obtain the encoding used for a protection password (it is
not UTF-16).
Record CODEPAGE, BIFF2-BIFF8:
Offset Size Contents
0 2 Code page identifier used for byte string text encoding:
016FH = 367 = ASCII
01B5H = 437 = IBM PC CP-437 (US)
02D0H = 720 = IBM PC CP-720 (OEM Arabic)
02E1H = 737 = IBM PC CP-737 (Greek)
0307H = 775 = IBM PC CP-775 (Baltic)
0352H = 850 = IBM PC CP-850 (Latin I)
0354H = 852 = IBM PC CP-852 (Latin II (Central European))
0357H = 855 = IBM PC CP-855 (Cyrillic)
0359H = 857 = IBM PC CP-857 (Turkish)
035AH = 858 = IBM PC CP-858 (Multilingual Latin I with Euro)
035CH = 860 = IBM PC CP-860 (Portuguese)
035DH = 861 = IBM PC CP-861 (Icelandic)
035EH = 862 = IBM PC CP-862 (Hebrew)
035FH = 863 = IBM PC CP-863 (Canadian (French))
0360H = 864 = IBM PC CP-864 (Arabic)
0361H = 865 = IBM PC CP-865 (Nordic)
0362H = 866 = IBM PC CP-866 (Cyrillic (Russian))
0365H = 869 = IBM PC CP-869 (Greek (Modern))
036AH = 874 = Windows CP-874 (Thai)
03A4H = 932 = Windows CP-932 (Japanese Shift-JIS)
03A8H = 936 = Windows CP-936 (Chinese Simplified GBK)
03B5H = 949 = Windows CP-949 (Korean (Wansung))
03B6H = 950 = Windows CP-950 (Chinese Traditional BIG5)
04B0H = 1200 = UTF-16 (BIFF8)
04E2H = 1250 = Windows CP-1250 (Latin II) (Central European)
04E3H = 1251 = Windows CP-1251 (Cyrillic)
04E4H = 1252 = Windows CP-1252 (Latin I) (BIFF4-BIFF7)
04E5H = 1253 = Windows CP-1253 (Greek)
04E6H = 1254 = Windows CP-1254 (Turkish)
04E7H = 1255 = Windows CP-1255 (Hebrew)
04E8H = 1256 = Windows CP-1256 (Arabic)
04E9H = 1257 = Windows CP-1257 (Baltic)
04EAH = 1258 = Windows CP-1258 (Vietnamese)
0551H = 1361 = Windows CP-1361 (Korean (Johab))
2710H = 10000 = Apple Roman
8000H = 32768 = Apple Roman
8001H = 32769 = Windows CP-1252 (Latin I) (BIFF2-BIFF3)
"""
_REC_ID = 0x0042
UTF_16 = 0x04B0
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = pack('<H', self.UTF_16)
[docs]class Window1Record(BiffRecord):
"""
Offset Size Contents
0 2 Horizontal position of the document window (in twips = 1/20 of a point)
2 2 Vertical position of the document window (in twips = 1/20 of a point)
4 2 Width of the document window (in twips = 1/20 of a point)
6 2 Height of the document window (in twips = 1/20 of a point)
8 2 Option flags:
Bits Mask Contents
0 0001H 0 = Window is visible 1 = Window is hidden
1 0002H 0 = Window is open 1 = Window is minimised
3 0008H 0 = Horizontal scroll bar hidden 1 = Horizontal scroll bar visible
4 0010H 0 = Vertical scroll bar hidden 1 = Vertical scroll bar visible
5 0020H 0 = Worksheet tab bar hidden 1 = Worksheet tab bar visible
10 2 Index to active (displayed) worksheet
12 2 Index of first visible tab in the worksheet tab bar
14 2 Number of selected worksheets (highlighted in the worksheet tab bar)
16 2 Width of worksheet tab bar (in 1/1000 of window width). The remaining space is used by the
horizontal scrollbar.
"""
_REC_ID = 0x003D
# flags
def __init__(self,
hpos_twips, vpos_twips,
width_twips, height_twips,
flags,
active_sheet,
first_tab_index, selected_tabs, tab_width):
BiffRecord.__init__(self)
self._rec_data = pack('<9H', hpos_twips, vpos_twips,
width_twips, height_twips,
flags,
active_sheet,
first_tab_index, selected_tabs, tab_width)
[docs]class FontRecord(BiffRecord):
"""
WARNING
The font with index 4 is omitted in all BIFF versions.
This means the first four fonts have zero-based indexes, and
the fifth font and all following fonts are referenced with one-based
indexes.
Offset Size Contents
0 2 Height of the font (in twips = 1/20 of a point)
2 2 Option flags:
Bit Mask Contents
0 0001H 1 = Characters are bold (redundant, see below)
1 0002H 1 = Characters are italic
2 0004H 1 = Characters are underlined (redundant, see below)
3 0008H 1 = Characters are struck out
0010H 1 = Outline
0020H 1 = Shadow
4 2 Colour index
6 2 Font weight (100-1000).
Standard values are 0190H (400) for normal text and 02BCH
(700) for bold text.
8 2 Escapement type:
0000H = None
0001H = Superscript
0002H = Subscript
10 1 Underline type:
00H = None
01H = Single
21H = Single accounting
02H = Double
22H = Double accounting
11 1 Font family:
00H = None (unknown or don't care)
01H = Roman (variable width, serifed)
02H = Swiss (variable width, sans-serifed)
03H = Modern (fixed width, serifed or sans-serifed)
04H = Script (cursive)
05H = Decorative (specialised, i.e. Old English, Fraktur)
12 1 Character set:
00H = 0 = ANSI Latin
01H = 1 = System default
02H = 2 = Symbol
4DH = 77 = Apple Roman
80H = 128 = ANSI Japanese Shift-JIS
81H = 129 = ANSI Korean (Hangul)
82H = 130 = ANSI Korean (Johab)
86H = 134 = ANSI Chinese Simplified GBK
88H = 136 = ANSI Chinese Traditional BIG5
A1H = 161 = ANSI Greek
A2H = 162 = ANSI Turkish
A3H = 163 = ANSI Vietnamese
B1H = 177 = ANSI Hebrew
B2H = 178 = ANSI Arabic
BAH = 186 = ANSI Baltic
CCH = 204 = ANSI Cyrillic
DEH = 222 = ANSI Thai
EEH = 238 = ANSI Latin II (Central European)
FFH = 255 = OEM Latin I
13 1 Not used
14 var. Font name:
BIFF5/BIFF7: Byte string, 8-bit string length
BIFF8: Unicode string, 8-bit string length
The boldness and underline flags are still set in the options field,
but not used on reading the font. Font weight and underline type
are specified in separate fields instead.
"""
_REC_ID = 0x0031
def __init__(self,
height, options, colour_index, weight, escapement,
underline, family, charset,
name):
BiffRecord.__init__(self)
uname = upack1(name)
uname_len = len(uname)
self._rec_data = pack('<5H4B%ds' % uname_len, height, options, colour_index, weight, escapement,
underline, family, charset, 0x00,
uname)
[docs]class XFRecord(BiffRecord):
"""
XF Substructures
-------------------------------------------------------------------------
XF_TYPE_PROT XF Type and Cell Protection (3 Bits), BIFF3-BIFF8
These 3 bits are part of a specific data byte.
Bit Mask Contents
0 01H 1 = Cell is locked
1 02H 1 = Formula is hidden
2 04H 0 = Cell XF; 1 = Style XF
XF_USED_ATTRIB Attributes Used from Parent Style XF (6 Bits),
BIFF3-BIFF8 Each bit describes the validity of a specific group
of attributes. In cell XFs a cleared bit means the attributes of the
parent style XF are used (but only if the attributes are valid there),
a set bit means the attributes of this XF are used. In style XFs
a cleared bit means the attribute setting is valid, a set bit means the
attribute should be ignored.
Bit Mask Contents
0 01H Flag for number format
1 02H Flag for font
2 04H Flag for horizontal and vertical alignment, text wrap, indentation, orientation, rotation, and
text direction
3 08H Flag for border lines
4 10H Flag for background area style
5 20H Flag for cell protection (cell locked and formula hidden)
XF_HOR_ALIGN Horizontal Alignment (3 Bits), BIFF2-BIFF8 The horizontal
alignment consists of 3 bits and is part of a specific data byte.
Value Horizontal alignment
00H General
01H Left
02H Centred
03H Right
04H Filled
05H Justified (BIFF4-BIFF8X)
06H Centred across selection (BIFF4-BIFF8X)
07H Distributed (BIFF8X)
XF_VERT_ALIGN Vertical Alignment (2 or 3 Bits), BIFF4-BIFF8
The vertical alignment consists of 2 bits (BIFF4) or 3 bits (BIFF5-BIFF8)
and is part of a specific data byte. Vertical alignment is not available
in BIFF2 and BIFF3.
Value Vertical alignment
00H Top
01H Centred
02H Bottom
03H Justified (BIFF5-BIFF8X)
04H Distributed (BIFF8X)
XF_ORIENTATION Text Orientation (2 Bits), BIFF4-BIFF7 In the BIFF
versions BIFF4-BIFF7, text can be rotated in steps of 90 degrees
or stacked. The orientation mode consists of 2 bits and is part of
a specific data byte. In BIFF8 a rotation angle occurs instead of these
flags.
Value Text orientation
00H Not rotated
01H Letters are stacked top-to-bottom, but not rotated
02H Text is rotated 90 degrees counterclockwise
03H Text is rotated 90 degrees clockwise
XF_ROTATION Text Rotation Angle (1 Byte), BIFF8
Value Text rotation
0 Not rotated
1-90 1 to 90 degrees counterclockwise
91-180 1 to 90 degrees clockwise
255 Letters are stacked top-to-bottom, but not rotated
XF_BORDER_34 Cell Border Style (4 Bytes), BIFF3-BIFF4 Cell borders
contain a line style and a line colour for each line of the border.
Bit Mask Contents
2-0 00000007H Top line style
7-3 000000F8H Colour index for top line colour
10-8 00000700H Left line style
15-11 0000F800H Colour index for left line colour
18-16 00070000H Bottom line style
23-19 00F80000H Colour index for bottom line colour
26-24 07000000H Right line style
31-27 F8000000H Colour index for right line colour
XF_AREA_34 Cell Background Area Style (2 Bytes), BIFF3-BIFF4 A cell
background area style contains an area pattern and a foreground and
background colour.
Bit Mask Contents
5-0 003FH Fill pattern
10-6 07C0H Colour index for pattern colour
15-11 F800H Colour index for pattern background
---------------------------------------------------------------------------------------------
Record XF, BIFF8:
Offset Size Contents
0 2 Index to FONT record
2 2 Index to FORMAT record
4 2 Bit Mask Contents
2-0 0007H XF_TYPE_PROT . XF type, cell protection (see above)
15-4 FFF0H Index to parent style XF (always FFFH in style XFs)
6 1 Bit Mask Contents
2-0 07H XF_HOR_ALIGN . Horizontal alignment (see above)
3 08H 1 = Text is wrapped at right border
6-4 70H XF_VERT_ALIGN . Vertical alignment (see above)
7 1 XF_ROTATION: Text rotation angle (see above)
8 1 Bit Mask Contents
3-0 0FH Indent level
4 10H 1 = Shrink content to fit into cell
5 merge
7-6 C0H Text direction (BIFF8X only)
00b = According to context
01b = Left-to-right
10b = Right-to-left
9 1 Bit Mask Contents
7-2 FCH XF_USED_ATTRIB . Used attributes (see above)
10 4 Cell border lines and background area:
Bit Mask Contents
3-0 0000000FH Left line style
7-4 000000F0H Right line style
11-8 00000F00H Top line style
15-12 0000F000H Bottom line style
22-16 007F0000H Colour index for left line colour
29-23 3F800000H Colour index for right line colour
30 40000000H 1 = Diagonal line from top left to right bottom
31 80000000H 1 = Diagonal line from bottom left to right top
14 4 Bit Mask Contents
6-0 0000007FH Colour index for top line colour
13-7 00003F80H Colour index for bottom line colour
20-14 001FC000H Colour index for diagonal line colour
24-21 01E00000H Diagonal line style
31-26 FC000000H Fill pattern
18 2 Bit Mask Contents
6-0 007FH Colour index for pattern colour
13-7 3F80H Colour index for pattern background
"""
_REC_ID = 0x00E0
def __init__(self, xf, xftype='cell'):
BiffRecord.__init__(self)
font_xf_idx, fmt_str_xf_idx, alignment, borders, pattern, protection = xf
fnt = struct.pack('<H', font_xf_idx)
fmt = struct.pack('<H', fmt_str_xf_idx)
if xftype == 'cell':
prt = struct.pack('<H',
((protection.cell_locked & 0x01) << 0) |
((protection.formula_hidden & 0x01) << 1)
)
else:
prt = struct.pack('<H', 0xFFF5)
aln = struct.pack('B',
((alignment.horz & 0x07) << 0) |
((alignment.wrap & 0x01) << 3) |
((alignment.vert & 0x07) << 4)
)
rot = struct.pack('B', alignment.rota)
txt = struct.pack('B',
((alignment.inde & 0x0F) << 0) |
((alignment.shri & 0x01) << 4) |
((alignment.merg & 0x01) << 5) |
((alignment.dire & 0x03) << 6)
)
if xftype == 'cell':
used_attr = struct.pack('B', 0xF8)
else:
used_attr = struct.pack('B', 0xF4)
if borders.left == borders.NO_LINE:
borders.left_colour = 0x00
if borders.right == borders.NO_LINE:
borders.right_colour = 0x00
if borders.top == borders.NO_LINE:
borders.top_colour = 0x00
if borders.bottom == borders.NO_LINE:
borders.bottom_colour = 0x00
if borders.diag == borders.NO_LINE:
borders.diag_colour = 0x00
brd1 = struct.pack('<L',
((borders.left & 0x0F) << 0 ) |
((borders.right & 0x0F) << 4 ) |
((borders.top & 0x0F) << 8 ) |
((borders.bottom & 0x0F) << 12) |
((borders.left_colour & 0x7F) << 16) |
((borders.right_colour & 0x7F) << 23) |
((borders.need_diag1 & 0x01) << 30) |
((borders.need_diag2 & 0x01) << 31)
)
brd2 = struct.pack('<L',
((borders.top_colour & 0x7F) << 0 ) |
((borders.bottom_colour & 0x7F) << 7 ) |
((borders.diag_colour & 0x7F) << 14) |
((borders.diag & 0x0F) << 21) |
((pattern.pattern & 0x0F) << 26)
)
pat = struct.pack('<H',
((pattern.pattern_fore_colour & 0x7F) << 0 ) |
((pattern.pattern_back_colour & 0x7F) << 7 )
)
self._rec_data = fnt + fmt + prt + \
aln + rot + txt + used_attr + \
brd1 + brd2 + \
pat
[docs]class StyleRecord(BiffRecord):
"""
STYLE record for user-defined cell styles, BIFF3-BIFF8:
Offset Size Contents
0 2 Bit Mask Contents
11-0 0FFFH Index to style XF record
15 8000H Always 0 for user-defined styles
2 var. BIFF2-BIFF7: Non-empty byte string, 8-bit string length
BIFF8: Non-empty Unicode string, 16-bit string length
STYLE record for built-in cell styles, BIFF3-BIFF8:
Offset Size Contents
0 2 Bit Mask Contents
11-0 0FFFH Index to style XF record
15 8000H Always 1 for built-in styles
2 1 Identifier of the built-in cell style:
00H = Normal
01H = RowLevel_lv (see next field)
02H = ColLevel_lv (see next field)
03H = Comma
04H = Currency
05H = Percent
06H = Comma [0] (BIFF4-BIFF8)
07H = Currency [0] (BIFF4-BIFF8)
08H = Hyperlink (BIFF8)
09H = Followed Hyperlink (BIFF8)
3 1 Level for RowLevel or ColLevel style
(zero-based, lv), FFH otherwise
The RowLevel and ColLevel styles specify the formatting of subtotal
cells in a specific outline level. The level is specified by the last
field in the STYLE record. Valid values are 0-6 for the outline levels
1-7.
"""
_REC_ID = 0x0293
def __init__(self):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<HBB', 0x8000, 0x00, 0xFF)
# TODO: implement user-defined styles???
[docs]class PaletteRecord(BiffRecord):
"""
This record contains the definition of all user-defined colours
available for cell and object formatting.
Record PALETTE, BIFF3-BIFF8:
Offset Size Contents
0 2 Number of following colours (nm). Contains 16 in BIFF3-BIFF4 and 56 in BIFF5-BIFF8.
2 4*nm List of nm RGB colours
The following table shows how colour indexes are used in other records:
Colour index Resulting colour or internal list index
00H Built-in Black (R = 00H, G = 00H, B = 00H)
01H Built-in White (R = FFH, G = FFH, B = FFH)
02H Built-in Red (R = FFH, G = 00H, B = 00H)
03H Built-in Green (R = 00H, G = FFH, B = 00H)
04H Built-in Blue (R = 00H, G = 00H, B = FFH)
05H Built-in Yellow (R = FFH, G = FFH, B = 00H)
06H Built-in Magenta (R = FFH, G = 00H, B = FFH)
07H Built-in Cyan (R = 00H, G = FFH, B = FFH)
08H First user-defined colour from the PALETTE record (entry 0 from record colour list)
.........................
17H (BIFF3-BIFF4) Last user-defined colour from the PALETTE record (entry 15 or 55 from record colour list)
3FH (BIFF5-BIFF8)
18H (BIFF3-BIFF4) System window text colour for border lines (used in records XF, CF, and
40H (BIFF5-BIFF8) WINDOW2 (BIFF8 only))
19H (BIFF3-BIFF4) System window background colour for pattern background (used in records XF, and CF)
41H (BIFF5-BIFF8)
43H System face colour (dialogue background colour)
4DH System window text colour for chart border lines
4EH System window background colour for chart areas
4FH Automatic colour for chart border lines (seems to be always Black)
50H System ToolTip background colour (used in note objects)
51H System ToolTip text colour (used in note objects)
7FFFH System window text colour for fonts (used in records FONT, EFONT, and CF)
"""
_REC_ID = 0x0092
[docs]class BoundSheetRecord(BiffRecord):
"""
This record is located in the workbook globals area and represents
a sheet inside of the workbook. For each sheet a BOUNDSHEET record
is written. It stores the sheet name and a stream offset to the BOF
record within the workbook stream. The record is also known
as BUNDLESHEET.
Record BOUNDSHEET, BIFF5-BIFF8:
Offset Size Contents
0 4 Absolute stream position of the BOF record of the sheet represented by this record. This
field is never encrypted in protected files.
4 1 Visibility:
00H = Visible
01H = Hidden
02H = Strong hidden
5 1 Sheet type:
00H = Worksheet
02H = Chart
06H = Visual Basic module
6 var. Sheet name:
BIFF5/BIFF7: Byte string, 8-bit string length
BIFF8: Unicode string, 8-bit string length
"""
_REC_ID = 0x0085
def __init__(self, stream_pos, visibility, sheetname):
BiffRecord.__init__(self)
usheetname = upack1(sheetname)
uusheetname_len = len(usheetname)
self._rec_data = pack('<LBB%ds' % uusheetname_len, stream_pos, visibility, 0x00, usheetname)
[docs]class ContinueRecord(BiffRecord):
"""
Whenever the content of a record exceeds the given limits (see table),
the record must be split. Several CONTINUE records containing the
additional data are added after the parent record.
BIFF version Maximum data size of a record
BIFF2-BIFF7 2080 bytes (2084 bytes including record header)
BIFF8 8224 bytes (8228 bytes including record header) (0x2020)
Record CONTINUE, BIFF2-BIFF8:
Offset Size Contents
0 var. Data continuation of the previous record
Unicode strings are split in a special way. At the beginning of each
CONTINUE record the option flags byte is repeated. Only the character
size flag will be set in this flags byte, the Rich-Text flag and the
Far-East flag are set to zero. In each CONTINUE record it is possible
that the character size changes from 8-bit characters to 16-bit
characters and vice versa.
Never a Unicode string is split until and including the first
character. That means, all header fields (string length, option flags,
optional Rich-Text size, and optional Far-East data size) and the first
character of the string have to occur together in the leading record,
or have to be moved completely into the CONTINUE record. Formatting
runs cannot be split between their components (character index and FONT
record index). If a string is split between two formatting runs, the
option flags field will not be repeated in the CONTINUE record.
"""
_REC_ID = 0x003C
[docs]class SSTRecord(BiffRecord):
"""
This record contains a list of all strings used anywhere in the
workbook. Each string occurs only once. The workbook uses indexes into
the list to reference the strings.
Record SST, BIFF8:
Offset Size Contents
0 4 Total number of strings in the workbook (see below)
4 4 Number of following strings (nm)
8 var. List of nm Unicode strings, 16-bit string length
The first field of the SST record counts the total occurrence
of strings in the workbook. For instance, the string AAA is used
3 times and the string BBB is used 2 times. The first field contains
5 and the second field contains 2, followed by the two strings.
"""
_REC_ID = 0x00FC
[docs]class ExtSSTRecord(BiffRecord):
"""
This record occurs in conjunction with the SST record. It is used
by Excel to create a hash table with stream offsets to the SST record
to optimise string search operations. Excel may not shorten this record
if strings are deleted from the shared string table, so the last part
might contain invalid data. The stream indexes in this record divide
the SST into portions containing a constant number of strings.
Record EXTSST, BIFF8:
Offset Size Contents
0 2 Number of strings in a portion, this number is >=8
2 var. List of OFFSET structures for all portions. Each OFFSET contains the following data:
Offset Size Contents
0 4 Absolute stream position of first string of the portion
4 2 Position of first string of the portion inside of current record,
including record header. This counter restarts at zero, if the SST
record is continued with a CONTINUE record.
6 2 Not used
"""
_REC_ID = 0x00FF
def __init__(self, sst_stream_pos, str_placement, portions_len):
BiffRecord.__init__(self)
extsst = {}
abs_stream_pos = sst_stream_pos
str_counter = 0
portion_counter = 0
while str_counter < len(str_placement):
str_chunk_num, pos_in_chunk = str_placement[str_counter]
if str_chunk_num <> portion_counter:
portion_counter = str_chunk_num
abs_stream_pos += portions_len[portion_counter-1]
#print hex(abs_stream_pos)
str_stream_pos = abs_stream_pos + pos_in_chunk + 4 # header
extsst[str_counter] = (pos_in_chunk, str_stream_pos)
str_counter += 1
exsst_str_count_delta = max(8, len(str_placement)*8/0x2000) # maybe smth else?
self._rec_data = pack('<H', exsst_str_count_delta)
str_counter = 0
while str_counter < len(str_placement):
self._rec_data += pack('<IHH', extsst[str_counter][1], extsst[str_counter][0], 0)
str_counter += exsst_str_count_delta
[docs]class DimensionsRecord(BiffRecord):
"""
Record DIMENSIONS, BIFF8:
Offset Size Contents
0 4 Index to first used row
4 4 Index to last used row, increased by 1
8 2 Index to first used column
10 2 Index to last used column, increased by 1
12 2 Not used
"""
_REC_ID = 0x0200
def __init__(self, first_used_row, last_used_row, first_used_col, last_used_col):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<2L3H',
first_used_row, last_used_row + 1,
first_used_col, last_used_col + 1,
0x00)
[docs]class Window2Record(BiffRecord):
"""
Record WINDOW2, BIFF8:
Offset Size Contents
0 2 Option flags (see below)
2 2 Index to first visible row
4 2 Index to first visible column
6 2 Colour index of grid line colour. Note that in BIFF2-BIFF7 an RGB colour is
written instead.
8 2 Not used
10 2 Cached magnification factor in page break preview (in percent); 0 = Default (60%)
12 2 Cached magnification factor in normal view (in percent); 0 = Default (100%)
14 4 Not used
In BIFF8 this record stores used magnification factors for page break
preview and normal view. These values are used to restore the
magnification, when the view is changed. The real magnification of the
currently active view is stored in the SCL record. The type of the
active view is stored in the option flags field (see below).
0 0001H 0 = Show formula results 1 = Show formulas
1 0002H 0 = Do not show grid lines 1 = Show grid lines
2 0004H 0 = Do not show sheet headers 1 = Show sheet headers
3 0008H 0 = Panes are not frozen 1 = Panes are frozen (freeze)
4 0010H 0 = Show zero values as empty cells 1 = Show zero values
5 0020H 0 = Manual grid line colour 1 = Automatic grid line colour
6 0040H 0 = Columns from left to right 1 = Columns from right to left
7 0080H 0 = Do not show outline symbols 1 = Show outline symbols
8 0100H 0 = Keep splits if pane freeze is removed 1 = Remove splits if pane freeze is removed
9 0200H 0 = Sheet not selected 1 = Sheet selected (BIFF5-BIFF8)
10 0400H 0 = Sheet not visible 1 = Sheet visible (BIFF5-BIFF8)
11 0800H 0 = Show in normal view 1 = Show in page break preview (BIFF8)
The freeze flag specifies, if a following PANE record describes unfrozen or frozen panes.
"""
_REC_ID = 0x023E
def __init__(self, options, first_visible_row, first_visible_col, grid_colour, preview_magn, normal_magn):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<7HL', options,
first_visible_row, first_visible_col,
grid_colour,
0x00,
preview_magn, normal_magn,
0x00L)
[docs]class PanesRecord(BiffRecord):
"""
This record stores the position of window panes. It is part of the Sheet
View Settings Block. If the sheet does not contain any splits, this
record will not occur.
A sheet can be split in two different ways, with unfrozen panes or with
frozen panes. A flag in the WINDOW2 record specifies, if the panes are
frozen, which affects the contents of this record.
Record PANE, BIFF2-BIFF8:
Offset Size Contents
0 2 Position of the vertical split
(px, 0 = No vertical split):
Unfrozen pane: Width of the left pane(s)
(in twips = 1/20 of a point)
Frozen pane: Number of visible
columns in left pane(s)
2 2 Position of the horizontal split
(py, 0 = No horizontal split):
Unfrozen pane: Height of the top pane(s)
(in twips = 1/20 of a point)
Frozen pane: Number of visible
rows in top pane(s)
4 2 Index to first visible row
in bottom pane(s)
6 2 Index to first visible column
in right pane(s)
8 1 Identifier of pane with active
cell cursor
[9] 1 Not used (BIFF5-BIFF8 only, not written
in BIFF2-BIFF4)
If the panes are frozen, pane 0 is always active, regardless
of the cursor position. The correct identifiers for all possible
combinations of visible panes are shown in the following pictures.
px = 0, py = 0 px = 0, py > 0
-------------------------- ------------|-------------
| | | |
| | | 3 |
| | | |
- 3 - --------------------------
| | | |
| | | 2 |
| | | |
-------------------------- ------------|-------------
px > 0, py = 0 px > 0, py > 0
------------|------------- ------------|-------------
| | | | | |
| | | | 3 | 2 |
| | | | | |
- 3 | 1 - --------------------------
| | | | | |
| | | | 1 | 0 |
| | | | | |
------------|------------- ------------|-------------
"""
_REC_ID = 0x0041
def __init__(self, px, py, first_row_bottom, first_col_right, active_pane):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<5H',
px, py,
first_row_bottom, first_col_right,
active_pane)
[docs]class RowRecord(BiffRecord):
"""
This record contains the properties of a single row in a sheet. Rows
and cells in a sheet are divided into blocks of 32 rows.
Record ROW, BIFF3-BIFF8:
Offset Size Contents
0 2 Index of this row
2 2 Index to column of the first cell which is described by a cell record
4 2 Index to column of the last cell which is described by a cell record,
increased by 1
6 2 Bit Mask Contents
14-0 7FFFH Height of the row, in twips = 1/20 of a point
15 8000H 0 = Row has custom height; 1 = Row has default height
8 2 Not used
10 2 In BIFF3-BIFF4 this field contains a relative offset
to calculate stream position of the first cell record
for this row. In BIFF5-BIFF8 this field is not used
anymore, but the DBCELL record instead.
12 4 Option flags and default row formatting:
Bit Mask Contents
2-0 00000007H Outline level of the row
4 00000010H 1 = Outline group starts or ends here (depending
on where the outline buttons are located,
see WSBOOL record), and is collapsed
5 00000020H 1 = Row is hidden (manually, or by a filter or outline group)
6 00000040H 1 = Row height and default font height do not match
7 00000080H 1 = Row has explicit default format (fl)
8 00000100H Always 1
27-16 0FFF0000H If fl=1: Index to default XF record
28 10000000H 1 = Additional space above the row. This flag is set,
if the upper border of at least one cell in this row
or if the lower border of at least one cell in the row
above is formatted with a thick line style.
Thin and medium line styles are not taken into account.
29 20000000H 1 = Additional space below the row. This flag is set,
if the lower border of at least one cell in this row
or if the upper border of at least one cell in the row
below is formatted with a medium or thick line style.
Thin line styles are not taken into account.
"""
_REC_ID = 0x0208
def __init__(self, index, first_col, last_col, height_options, options):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<6HL', index, first_col, last_col + 1,
height_options,
0x00, 0x00,
options)
[docs]class LabelSSTRecord(BiffRecord):
"""
This record represents a cell that contains a string. It replaces the
LABEL record and RSTRING record used in BIFF2-BIFF7.
"""
_REC_ID = 0x00FD
def __init__(self, row, col, xf_idx, sst_idx):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<3HL', row, col, xf_idx, sst_idx)
[docs]class MergedCellsRecord(BiffRecord):
"""
This record contains all merged cell ranges of the current sheet.
Record MERGEDCELLS, BIFF8:
Offset Size Contents
0 var. Cell range address list with all merged ranges
------------------------------------------------------------------
A cell range address list consists of a field with the number of ranges
and the list of the range addresses.
Cell range address list, BIFF8:
Offset Size Contents
0 2 Number of following cell range addresses (nm)
2 8*nm List of nm cell range addresses
---------------------------------------------------------------------
Cell range address, BIFF8:
Offset Size Contents
0 2 Index to first row
2 2 Index to last row
4 2 Index to first column
6 2 Index to last column
"""
_REC_ID = 0x00E5
def __init__(self, merged_list):
BiffRecord.__init__(self)
i = len(merged_list) - 1
while i >= 0:
j = 0
merged = ''
while (i >= 0) and (j < 0x403):
r1, r2, c1, c2 = merged_list[i]
merged += struct.pack('<4H', r1, r2, c1, c2)
i -= 1
j += 1
self._rec_data += struct.pack('<3H', self._REC_ID, len(merged) + 2, j) + \
merged
# for some reason Excel doesn't use CONTINUE
[docs] def get(self):
return self._rec_data
[docs]class MulBlankRecord(BiffRecord):
"""
This record represents a cell range of empty cells. All cells are
located in the same row.
Record MULBLANK, BIFF5-BIFF8:
Offset Size Contents
0 2 Index to row
2 2 Index to first column (fc)
4 2*nc List of nc=lc-fc+1 16-bit indexes to XF records
4+2*nc 2 Index to last column (lc)
"""
_REC_ID = 0x00BE
def __init__(self, row, first_col, last_col, xf_index):
BiffRecord.__init__(self)
blanks_count = last_col-first_col+1
self._rec_data = struct.pack('%dH' % blanks_count, *([xf_index]*blanks_count))
self._rec_data = struct.pack('<2H', row, first_col) + self._rec_data + struct.pack('<H', last_col)
[docs]class BlankRecord(BiffRecord):
"""
This record represents an empty cell.
Record BLANK, BIFF5-BIFF8:
Offset Size Contents
0 2 Index to row
2 2 Index to first column (fc)
4 2 indexes to XF record
"""
_REC_ID = 0x0201
def __init__(self, row, col, xf_index):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<3H', row, col, xf_index)
[docs]class RKRecord(BiffRecord):
"""
This record represents a cell that contains an RK value (encoded integer or
floating-point value). If a floating-point value cannot be encoded to an RK value,
a NUMBER record will be written.
"""
_REC_ID = 0x027E
def __init__(self, row, col, xf_index, rk_encoded):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<3HI', row, col, xf_index, rk_encoded)
[docs]class NumberRecord(BiffRecord):
"""
This record represents a cell that contains an IEEE-754 floating-point value.
"""
_REC_ID = 0x0203
def __init__(self, row, col, xf_index, number):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<3Hd', row, col, xf_index, number)
[docs]class StringRecord(BiffRecord):
"""
Offset Size Contents
0 1 or 2 Length of the string (character count, ln)
1 or 2 1 Option flags:
Bit Mask Contents
01H
0 Character compression (ccompr):
02 = Compressed (8-bit characters)
12 = Uncompressed (16-bit characters)
04H
2 Asian phonetic settings (phonetic):
02 = Does not contain Asian phonetic settings
12 = Contains Asian phonetic settings
08H
3 Rich-Text settings (richtext):
02 = Does not contain Rich-Text settings
12 = Contains Rich-Text settings
[2 or 3] 2 (optional, only if richtext=1) Number of Rich-Text formatting runs (rt)
[var.] 4 (optional, only if phonetic=1) Size of Asian phonetic settings block (in bytes, sz)
var. ln or 2ln Character array (8-bit characters or 16-bit characters, dependent on ccompr)
[var.] 4rt (optional, only if richtext=1) List of rt formatting runs (3.2)
sz
[var.] (optional, only if phonetic=1) Asian Phonetic Settings Block (3.4.2)
"""
_REC_ID = 0x0207
def __init__(self, s):
BiffRecord.__init__(self)
uncompressed = 0x01
asian_phonetic_settings = 0x04
rich_text_settings = 0x08
s = s.encode('UTF-16le')
slen = len(s)/2
options = uncompressed
if not s[1::2].strip(chr(0x00)):
# if high bytes are all zero, do string compression
s = s[::2]
slen = len(s)
options &= ~uncompressed
self._rec_data = struct.pack('<HB%ds'%len(s), slen, options, s)
[docs]class HyperlinkRecord(BiffRecord):
"""
Each HLINK record starts with the same data items and continues with
special data related to the current type of hyperlink. It starts with a
cell range. Each cell of this range will contain the same hyperlink.
Record HLINK, BIFF8:
Offset Size Contents
0 8 Cell range address of all cells containing this hyperlink (3.13.1)
8 16 GUID of StdLink:
D0H C9H EAH 79H F9H BAH CEH 11H 8CH 82H 00H AAH 00H 4BH A9H 0BH
(79EAC9D0-BAF9-11CE-8C82-00AA004BA90B)
24 4 Unknown value: 00000002H
28 4 Option flags (see below)
[32] 4 (optional, see option flags) Character count of description text, including trailing zero word
(dl)
[36] 2dl (optional, see option flags) Character array of description text, no Unicode string header,
always 16-bit characters, zero-terminated
[var.] 4 (optional, see option flags) Character count of target frame, including trailing zero word
(fl)
[var.] 2fl (optional, see option flags) Character array of target frame, no Unicode string header,
always 16-bit characters, zero-terminated
var. var. Special data (6.53.2 and following)
[var.] 4 (optional, see option flags) Character count of the text mark, including trailing zero word
(tl)
[var.] 2tl (optional, see option flags) Character array of the text mark without "#" sign, no Unicode
string header, always 16-bit characters, zero-terminated
"""
_REC_ID = 0x01B8
def __init__(self, frow, lrow, fcol, lcol, url, target=None, description=None):
BiffRecord.__init__(self)
options = 0x00
special=None
textmark=None
#~ assert url is not None or textmark is not None, 'must specify either url or textmark arguments'
if description is not None:
options |= 0x14
description = description.encode('UTF-16le') + '\x00\x00'
if target is not None:
options |= 0x80
target = target.encode('UTF-16le') + '\x00\x00'
u16url = url.encode('UTF-16le') + '\x00\x00'
if url[0].isalpha() and url.find(':') > 3:
# hyperlink containing a URL (6.53.2)
assert url[:url.find(':')].isalpha(), 'not valid URL'%url
options |= 0x03
special = pack('<4L', 0x79eac9e0, 0x11cebaf9, 0xaa00828c, 0x0ba94b00)
special += pack('<L%ds'%len(u16url), len(u16url), u16url)
elif url[0].isalnum() or url[0] in ('.',):
# hyperlink to the current workbook (6.53.3)
options |= 0x01
uplvl_cnt=0
if url[0].isalpha() and url[1:].startswith(':\\'):
# this is an absolute path
options |= 0x02
else:
uplvl_cnt += 1
while url.startswith('..\\'):
url = url[3:]
url += '\x00'
special = pack('<4L', 0x00000303, 0x00000000, 0x000000C0, 0x46000000)
special += pack('<HL%ds'%len(url), uplvl_cnt, len(url), url)
special += pack('<4s20x', '\xff\xff\xad\xde')
# I don't understand the first field here, setting to zero is safe
# till I figure out what it is
special += pack('<LL2s%ds'%len(u16url[:-2]), 0, len(u16url[:-2]),
'\x03\x00', u16url[:-2])
elif url.startswith('\\\\'):
# hyperlink to a File with UNC Path (6.53.4)
options |= 0x103
special = pack('<L%ds'%len(u16url), len(u16url), u16url)
elif url.startswith('#'):
# hyperlink to the current workbook (6.53.5)
options |= 0x08
textmark = url[1:]
if textmark is not None:
textmark = textmark.encode('UTF-16le') + '\x00\x00'
self._rec_data = pack('<4H', frow, lrow, fcol, lcol)
# GUID of StdLink
self._rec_data += pack('<4L', 0x79eac9d0, 0x11cebaf9, 0xaa00828c, 0x0ba94b00)
#~ self._rec_data += '\xd0\xc9\xea\x79\xf9\xba\xce\x11\x8c\x82\x00\xaa\x00\x4b\xa9\x0b'
self._rec_data += pack('<LL', 0x2, options)
if description is not None:
self._rec_data += pack('<L%ds'%len(description), len(description)/2, description)
if target is not None:
self._rec_data += pack('<L%ds'%len(target), len(target)/2, target)
if special is not None:
self._rec_data += pack('<%ds'%len(special), special)
if textmark is not None:
self._rec_data += pack('<L%ds'%len(textmark), len(textmark)/2, textmark)
[docs]class QuicktipRecord(BiffRecord):
"""
This record contains the cell range and text for a tool tip. It occurs in
conjunction with the HLINK record for hyperlinks (6.53) in the Hyperlink
Table (5.13). This record is only available in Excel 9.0 (Excel 2000) and
later.
Record QUICKTIP, BIFF8:
Offset Size Contents
0 2 0800H (repeated record identifier)
2 8 Cell range address of all cells containing the tool tip (3.13.1)
10 var. Character array of the tool tip, no Unicode string header, always 16-bit characters, zero-
terminated
"""
_REC_ID = 0x0800
def __init__(self, frow, lrow, fcol, lcol, hint):
BiffRecord.__init__(self)
hint = hint.encode('UTF-16le')
self._rec_data = pack('<5H%dsx'%len(hint), self._REC_ID, frow, lrow, fcol, lcol, hint)
[docs]class GutsRecord(BiffRecord):
"""
This record contains information about the layout of outline symbols.
Record GUTS, BIFF3-BIFF8:
Offset Size Contents
0 2 Width of the area to display row outlines (left of the sheet), in pixel
2 2 Height of the area to display column outlines (above the sheet), in pixel
4 2 Number of visible row outline levels (used row levels + 1; or 0, if not used)
6 2 Number of visible column outline levels (used column levels + 1; or 0, if not used)
"""
_REC_ID = 0x0080
def __init__(self, row_gut_width, col_gut_height, row_visible_levels, col_visible_levels):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<4H', row_gut_width, col_gut_height, row_visible_levels, col_visible_levels)
[docs]class WSBoolRecord(BiffRecord):
"""
This record stores a 16 bit value with Boolean options for the current
sheet. From BIFF5 on the "Save external linked values" option is moved
to the record BOOKBOOL.
Option flags of record WSBOOL, BIFF3-BIFF8:
Bit Mask Contents
0 0001H 0 = Do not show automatic page breaks
1 = Show automatic page breaks
4 0010H 0 = Standard sheet
1 = Dialogue sheet (BIFF5-BIFF8)
5 0020H 0 = No automatic styles in outlines
1 = Apply automatic styles to outlines
6 0040H 0 = Outline buttons above outline group
1 = Outline buttons below outline group
7 0080H 0 = Outline buttons left of outline group
1 = Outline buttons right of outline group
8 0100H 0 = Scale printout in percent
1 = Fit printout to number of pages
9 0200H 0 = Save external linked values (BIFF3?BIFF4 only)
1 = Do not save external linked values (BIFF3?BIFF4 only)
10 0400H 0 = Do not show row outline symbols
1 = Show row outline symbols
11 0800H 0 = Do not show column outline symbols
1 = Show column outline symbols
13-12 3000H These flags specify the arrangement of windows.
They are stored in BIFF4 only.
00 = Arrange windows tiled
01 = Arrange windows horizontal
10 = Arrange windows vertical112 = Arrange windows cascaded
The following flags are valid for BIFF4-BIFF8 only:
14 4000H 0 = Standard expression evaluation
1 = Alternative expression evaluation
15 8000H 0 = Standard formula entries
1 = Alternative formula entries
"""
_REC_ID = 0x0081
def __init__(self, options):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', options)
[docs]class ColInfoRecord(BiffRecord):
"""
This record specifies the width for a given range of columns.
If a column does not have a corresponding COLINFO record,
the width specified in the record STANDARDWIDTH is used. If
this record is also not present, the contents of the record
DEFCOLWIDTH is used instead.
This record also specifies a default XF record to use for
cells in the columns that are not described by any cell record
(which contain the XF index for that cell). Additionally,
the option flags field contains hidden, outline, and collapsed
options applied at the columns.
Record COLINFO, BIFF3-BIFF8:
Offset Size Contents
0 2 Index to first column in the range
2 2 Index to last column in the range
4 2 Width of the columns in 1/256 of the width of the zero character, using default font
(first FONT record in the file)
6 2 Index to XF record for default column formatting
8 2 Option flags:
Bits Mask Contents
0 0001H 1 = Columns are hidden
10-8 0700H Outline level of the columns (0 = no outline)
12 1000H 1 = Columns are collapsed
10 2 Not used
"""
_REC_ID = 0x007D
def __init__(self, first_col, last_col, width, xf_index, options):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<6H', first_col, last_col, width, xf_index, options, 0)
[docs]class CalcModeRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block.
It specifies whether to calculate formulas manually,
automatically or automatically except for multiple table operations.
Record CALCMODE, BIFF2-BIFF8:
Offset Size Contents
0 2 FFFFH = automatic except for multiple table operations
0000H = manually
0001H = automatically (default)
"""
_REC_ID = 0x000D
def __init__(self, calc_mode):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<h', calc_mode)
[docs]class CalcCountRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block. It specifies the maximum
number of times the formulas should be iteratively calculated. This is a fail-safe
against mutually recursive formulas locking up a spreadsheet application.
Record CALCCOUNT, BIFF2-BIFF8:
Offset Size Contents
0 2 Maximum number of iterations allowed in circular references
"""
_REC_ID = 0x000C
def __init__(self, calc_count):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', calc_count)
[docs]class RefModeRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block.
It stores which method is used to show cell addresses in formulas.
The “RC” mode uses numeric indexes for rows and columns,
i.e. “R(1)C(-1)”, or “R1C1:R2C2”.
The “A1” mode uses characters for columns and numbers for rows,
i.e. “B1”, or “$A$1:$B$2”.
Record REFMODE, BIFF2-BIFF8:
Offset Size Contents
0 2 0 = RC mode; 1 = A1 mode
"""
_REC_ID = 0x00F
def __init__(self, ref_mode):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', ref_mode)
[docs]class IterationRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block.
It stores if iterations are allowed while calculating recursive formulas.
Record ITERATION, BIFF2-BIFF8:
Offset Size Contents
0 2 0 = Iterations off; 1 = Iterations on
"""
_REC_ID = 0x011
def __init__(self, iterations_on):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', iterations_on)
[docs]class DeltaRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block.
It stores the maximum change of the result to exit an iteration.
Record DELTA, BIFF2-BIFF8:
Offset Size Contents
0 8 Maximum change in iteration
(IEEE 754 floating-point value,
64bit double precision)
"""
_REC_ID = 0x010
def __init__(self, delta):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<d', delta)
[docs]class SaveRecalcRecord(BiffRecord):
"""
This record is part of the Calculation Settings Block.
It contains the “Recalculate before save” option in
Excel's calculation settings dialogue.
Record SAVERECALC, BIFF3-BIFF8:
Offset Size Contents
0 2 0 = Do not recalculate;
1 = Recalculate before saving the document
"""
_REC_ID = 0x05F
def __init__(self, recalc):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', recalc)
[docs]class PrintGridLinesRecord(BiffRecord):
"""
This record stores if sheet grid lines will be printed.
Record PRINTGRIDLINES, BIFF2-BIFF8:
Offset Size Contents
0 2 0 = Do not print sheet grid lines;
1 = Print sheet grid lines
"""
_REC_ID = 0x02B
def __init__(self, print_grid):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', print_grid)
[docs]class GridSetRecord(BiffRecord):
"""
This record specifies if the option to print sheet grid lines
(record PRINTGRIDLINES) has ever been changed.
Record GRIDSET, BIFF3-BIFF8:
Offset Size Contents
0 2 0 = Print grid lines option never changed
1 = Print grid lines option changed
"""
_REC_ID = 0x082
def __init__(self, print_grid_changed):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', print_grid_changed)
[docs]class DefaultRowHeight(BiffRecord):
"""
This record specifies the default height and default flags
for rows that do not have a corresponding ROW record.
Record DEFAULTROWHEIGHT, BIFF3-BIFF8:
Offset Size Contents
0 2 Option flags:
Bit Mask Contents
0 0001H 1 = Row height and default font height do not match
1 0002H 1 = Row is hidden
2 0004H 1 = Additional space above the row
3 0008H 1 = Additional space below the row
2 2 Default height for unused rows, in twips = 1/20 of a point
"""
_REC_ID = 0x0225
def __init__(self, options, def_height):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<2H', options, def_height)
[docs]class DefColWidthRecord(BiffRecord):
"""
This record specifies the default column width for columns that
do not have a specific width set using the record COLINFO or COLWIDTH.
This record has no effect, if a STANDARDWIDTH record is present in the file.
Record DEFCOLWIDTH, BIFF2-BIFF8:
Offset Size Contents
0 2 Column width in characters, using the width of the zero
character from default font (first FONT record in the file)
"""
_REC_ID = 0x0055
def __init__(self, def_width):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', options, def_width)
[docs]class HorizontalPageBreaksRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains all
horizontal manual page breaks.
Record HORIZONTALPAGEBREAKS, BIFF8:
Offset Size Contents
0 2 Number of following row index structures (nm)
2 6nm List of nm row index structures. Each row index
structure contains:
Offset Size Contents
0 2 Index to first row below the page break
2 2 Index to first column of this page break
4 2 Index to last column of this page break
The row indexes in the lists must be ordered ascending.
If in BIFF8 a row contains several page breaks, they must be ordered
ascending by start column index.
"""
_REC_ID = 0x001B
def __init__(self, breaks_list):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', len(breaks_list))
for r, c1, c2 in breaks_list:
self._rec_data += struct.pack('<3H', r, c1, c2)
[docs]class VerticalPageBreaksRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains all
vertical manual page breaks.
Record VERTICALPAGEBREAKS, BIFF8:
Offset Size Contents
0 2 Number of following column index structures (nm)
2 6nm List of nm column index structures. Each column index
structure contains:
Offset Size Contents
0 2 Index to first column following the page
break
2 2 Index to first row of this page break
4 2 Index to last row of this page break
The column indexes in the lists must be ordered ascending.
If in BIFF8 a column contains several page breaks, they must be ordered
ascending by start row index.
"""
_REC_ID = 0x001A
def __init__(self, breaks_list):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', len(breaks_list))
for r, c1, c2 in breaks_list:
self._rec_data += struct.pack('<3H', r, c1, c2)
[docs]class HCenterRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It specifies if the
sheet is centred horizontally when printed.
Record HCENTER, BIFF3-BIFF8:
Offset Size Contents
0 2 0 = Print sheet left aligned
1 = Print sheet centred horizontally
"""
_REC_ID = 0x0083
def __init__(self, is_horz_center):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', is_horz_center)
[docs]class VCenterRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It specifies if the
sheet is centred vertically when printed.
Record VCENTER, BIFF3-BIFF8:
Offset Size Contents
0 2 0 = Print sheet aligned at top page border
1 = Print sheet vertically centred
"""
_REC_ID = 0x0084
def __init__(self, is_vert_center):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<H', is_vert_center)
[docs]class LeftMarginRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains the left
page margin of the current worksheet.
Record LEFTMARGIN, BIFF2-BIFF8:
Offset Size Contents
0 8 Left page margin in inches
(IEEE 754 floating-point value, 64bit double precision)
"""
_REC_ID = 0x0026
def __init__(self, margin):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<d', margin)
[docs]class RightMarginRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains the right
page margin of the current worksheet.
Offset Size Contents
0 8 Right page margin in inches
(IEEE 754 floating-point value, 64?bit double precision)
"""
_REC_ID = 0x0027
def __init__(self, margin):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<d', margin)
[docs]class TopMarginRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains the top
page margin of the current worksheet.
Offset Size Contents
0 8 Top page margin in inches
(IEEE 754 floating-point value, 64?bit double precision)
"""
_REC_ID = 0x0028
def __init__(self, margin):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<d', margin)
[docs]class BottomMarginRecord(BiffRecord):
"""
This record is part of the Page Settings Block. It contains the bottom
page margin of the current worksheet.
Offset Size Contents
0 8 Bottom page margin in inches
(IEEE 754 floating-point value, 64?bit double precision)
"""
_REC_ID = 0x0029
def __init__(self, margin):
BiffRecord.__init__(self)
self._rec_data = struct.pack('<d', margin)
[docs]class NameRecord(BiffRecord):
"""
This record is part of a Link Table. It contains the name and the token
array of an internal defined name. Token arrays of defined names
contain tokens with aberrant token classes.
Record NAME, BIFF5/BIFF7:
Offset Size Contents
0 2 Option flags, see below
2 1 Keyboard shortcut (only for command macro names, see below)
3 1 Length of the name (character count, ln)
4 2 Size of the formula data (sz)
6 2 0 = Global name, otherwise index to EXTERNSHEET record (one-based)
8 2 0 = Global name, otherwise index to sheet (one-based)
10 1 Length of menu text (character count, lm)
11 1 Length of description text (character count, ld)
12 1 Length of help topic text (character count, lh)
13 1 Length of status bar text (character count, ls)
14 ln Character array of the name
14+ln sz Formula data (RPN token array without size field, 4)
14+ln+sz lm Character array of menu text
var. ld Character array of description text
var. lh Character array of help topic text
var. ls Character array of status bar text
Record NAME, BIFF8:
Offset Size Contents
0 2 Option flags, see below
2 1 Keyboard shortcut (only for command macro names, see below)
3 1 Length of the name (character count, ln)
4 2 Size of the formula data (sz)
6 2 Not used
8 2 0 = Global name, otherwise index to sheet (one-based)
10 1 Length of menu text (character count, lm)
11 1 Length of description text (character count, ld)
12 1 Length of help topic text (character count, lh)
13 1 Length of status bar text (character count, ls)
14 var. Name (Unicode string without length field, 3.4)
var. sz Formula data (RPN token array without size field, 4)
[var.] var. (optional, only if lm > 0) Menu text (Unicode string without length field, 3.4)
[var.] var. (optional, only if ld > 0) Description text (Unicode string without length field, 3.4)
[var.] var. (optional, only if lh > 0) Help topic text (Unicode string without length field, 3.4)
[var.] var. (optional, only if ls > 0) Status bar text (Unicode string without length field, 3.4)
"""
_REC_ID = 0x0018
def __init__(self, options, keyboard_shortcut, name, sheet_index, rpn, menu_text='', desc_text='', help_text='', status_text=''):
BiffRecord.__init__(self)
if type(name) == int:
uname = chr(name)
else:
uname = upack1(name)[1:]
uname_len = len(uname)
#~ self._rec_data = struct.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
self._rec_data = struct.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
#~ print repr(self.get())
#~ print repr(self._rec_data)
#~ print len(self._rec_data)
[docs]class ExternSheetRecord(BiffRecord):
"""
In BIFF8 the record stores a list with indexes to SUPBOOK
records (list of REF structures, 6.100). See 5.10.3 for
details about external references in BIFF8.
Record EXTERNSHEET, BIFF8:
Offset Size Contents
0 2 Number of following REF structures (nm)
2 6nm List of nm REF structures. Each REF contains the following data:
Offset Size Contents
0 2 Index to SUPBOOK record
2 2 Index to first SUPBOOK sheet
4 2 Index to last SUPBOOK sheet
"""
_REC_ID = 0x0017
def __init__(self, refs=[]):
BiffRecord.__init__(self)
# do we always need this ref? or only if there are no refs?
refs.insert(0, (0,0,0))
#~ ref_data = ''.join([ref.get() for r in refs])
ref_data = ''.join([struct.pack('<HHH', *r) for r in refs])
self._rec_data = struct.pack('<H%ds' % (len(refs)*6), len(refs), ref_data)
[docs]class SupBookRecord(BiffRecord):
"""
This record mainly stores the URL of an external document
and a list of sheet names inside this document. Furthermore
it is used to store DDE and OLE object links, or to indicate
an internal 3D reference or an add-in function. See 5.10.3
for details about external references in BIFF8.
"""
_REC_ID = 0x01AE
def __init__(self):
BiffRecord.__init__(self)
[docs]class InternalReferenceSupBookRecord(SupBookRecord):
"""
In each file occurs a SUPBOOK that is used for internal 3D
references. It stores the number of sheets of the own document.
Record SUPBOOK for 3D references, BIFF8:
Offset Size Contents
0 2 Number of sheets in this document
2 2 01H 04H (relict of BIFF5/BIFF7, the byte string "<04H>", see 3.9.1)
"""
def __init__(self, num_sheets):
SupBookRecord.__init__(self)
self._rec_data = struct.pack('<HBB', num_sheets, 0x01, 0x04)