def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.__thread_local_data = threading.local()
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.code_name = u'ThisWorkbook'
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
def __init__(self):
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self._local_data = DummyLocalData()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
class DummyWorkbook:
_guess_types = False
data_only = False
def __init__(self):
self.shared_styles = IndexedList(range(28))
self.shared_styles.add(Style())
class DummyWorkbook:
_guess_types = False
data_only = False
def __init__(self):
self.shared_styles = IndexedList(range(28))
self.shared_styles.add(Style())
class DummyWorkbook:
def __init__(self):
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self._local_data = DummyLocalData()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
def get_sheet_names(self):
return []
class DummyWorkbook:
def __init__(self):
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self._local_data = DummyLocalData()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
def get_sheet_names(self):
return []
def test_read_standalone_worksheet(datadir):
class DummyWb(object):
encoding = 'utf-8'
excel_base_date = CALENDAR_WINDOWS_1900
_guess_types = True
data_only = False
def get_sheet_by_name(self, value):
return None
def get_sheet_names(self):
return []
datadir.join("reader").chdir()
ws = None
shared_strings = IndexedList(['hello'])
with open('sheet2.xml') as src:
ws = read_worksheet(src.read(), DummyWb(), 'Sheet 2', shared_strings,
{1: Style()})
assert isinstance(ws, Worksheet)
assert ws.cell('G5').value == 'hello'
assert ws.cell('D30').value == 30
assert ws.cell('K9').value == 0.09
def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.__thread_local_data = threading.local()
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
def __init__(self):
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self._local_data = DummyLocalData()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
def create_string_table(workbook):
"""Compile the string table for a workbook."""
strings = set()
for sheet in workbook.worksheets:
for cell in itervalues(sheet._cells):
if cell.data_type == cell.TYPE_STRING and cell._value is not None:
strings.add(cell.value)
return IndexedList(sorted(strings))
def parse_cell_xfs(self):
"""Read styles from the shared style table"""
cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
_styles = []
if cell_xfs is None: # can happen on bad OOXML writers (e.g. Gnumeric)
return
builtin_formats = numbers.BUILTIN_FORMATS
xfs = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
for index, xf in enumerate(xfs):
_style = {}
num_fmt = xf.get('numFmtId')
if num_fmt is not None:
num_fmt = int(num_fmt)
if num_fmt < 164:
format_code = builtin_formats.get(num_fmt, 'General')
else:
fmt_code = self.custom_num_formats.get(num_fmt)
if fmt_code is not None:
format_code = fmt_code
else:
raise MissingNumberFormat('%s' % num_fmt)
_style['number_format'] = format_code
if bool_attrib(xf, 'applyAlignment'):
alignment = {}
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = al.attrib
_style['alignment'] = Alignment(**alignment)
if bool_attrib(xf, 'applyFont'):
_style['font'] = self.font_list[int(xf.get('fontId'))].copy()
if bool_attrib(xf, 'applyFill'):
_style['fill'] = self.fill_list[int(xf.get('fillId'))].copy()
if bool_attrib(xf, 'applyBorder'):
_style['border'] = self.border_list[int(
xf.get('borderId'))].copy()
if bool_attrib(xf, 'applyProtection'):
protection = {}
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection.update(prot.attrib)
_style['protection'] = Protection(**protection)
_styles.append(Style(**_style))
self.shared_styles = IndexedList(_styles)
def __init__(self):
self.shared_styles = IndexedList()
def __init__(self, sheet):
self.sheet = sheet
self.authors = IndexedList()
self.comments = []
self.extract_comments()
class DummyWorkbook(object):
shared_styles = IndexedList()
shared_styles.add(None) # Workbooks always have a default style
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
_optimized_worksheet_class = DumpWorksheet
def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.__thread_local_data = threading.local()
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
@deprecated('this method is private and should not be called directly')
def read_workbook_settings(self, xml_source):
self._read_workbook_settings(xml_source)
def _read_workbook_settings(self, xml_source):
root = fromstring(xml_source)
view = root.find('*/' '{%s}workbookView' % SHEET_MAIN_NS)
if view is None:
return
if 'activeTab' in view.attrib:
self.active = int(view.attrib['activeTab'])
@property
def _local_data(self):
return self.__thread_local_data
@property
def excel_base_date(self):
return self.properties.excel_base_date
@property
def read_only(self):
return self.__read_only
@property
def write_only(self):
return self.__write_only
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def active(self):
"""Get the currently active sheet"""
return self.worksheets[self._active_sheet_index]
@active.setter
def active(self, value):
"""Set the active sheet"""
self._active_sheet_index = value
def create_sheet(self, index=None, title=None):
"""Create a worksheet (at an optional index).
:param index: optional position at which the sheet will be inserted
:type index: int
"""
if self.read_only:
raise ReadOnlyWorkbookException('Cannot create new sheet in a read-only workbook')
if self.write_only :
new_ws = self._optimized_worksheet_class(parent_workbook=self,
title=title)
self._worksheet_class = self._optimized_worksheet_class
else:
if title is not None:
new_ws = self._worksheet_class(
parent_workbook=self, title=title)
else:
new_ws = self._worksheet_class(parent_workbook=self)
self._add_sheet(worksheet=new_ws, index=index)
return new_ws
@deprecated("you probably want to use Workbook.create_sheet('sheet name') instead")
def add_sheet(self, worksheet, index=None):
self._add_sheet(worksheet, index)
def _add_sheet(self, worksheet, index=None):
"""Add an existing worksheet (at an optional index)."""
if not isinstance(worksheet, self._worksheet_class):
raise TypeError("The parameter you have given is not of the type '%s'" % self._worksheet_class.__name__)
if worksheet.parent != self:
raise ValueError("You cannot add worksheets from another workbook.")
if index is None:
self.worksheets.append(worksheet)
else:
self.worksheets.insert(index, worksheet)
def remove_sheet(self, worksheet):
"""Remove a worksheet from this workbook."""
self.worksheets.remove(worksheet)
def get_sheet_by_name(self, name):
"""Returns a worksheet by its name.
Returns None if no worksheet has the name specified.
:param name: the name of the worksheet to look for
:type name: string
"""
try:
return self[name]
except KeyError:
return
def __contains__(self, key):
return key in set(self.sheetnames)
def get_index(self, worksheet):
"""Return the index of the worksheet."""
return self.worksheets.index(worksheet)
def __getitem__(self, key):
"""Returns a worksheet by its name.
:param name: the name of the worksheet to look for
:type name: string
"""
for sheet in self.worksheets:
if sheet.title == key:
return sheet
raise KeyError("Worksheet {0} does not exist.".format(key))
def __delitem__(self, key):
sheet = self[key]
self.remove_sheet(sheet)
def __iter__(self):
return iter(self.worksheets)
def get_sheet_names(self):
return self.sheetnames
@property
def sheetnames(self):
"""Returns the list of the names of worksheets in the workbook.
Names are returned in the worksheets order.
:rtype: list of strings
"""
return [s.title for s in self.worksheets]
def create_named_range(self, name, worksheet, range, scope=None):
"""Create a new named_range on a worksheet"""
if not isinstance(worksheet, self._worksheet_class):
raise TypeError("Worksheet is not of the right type")
named_range = NamedRange(name, [(worksheet, range)], scope)
self.add_named_range(named_range)
def get_named_ranges(self):
"""Return all named ranges"""
return self._named_ranges
def add_named_range(self, named_range):
"""Add an existing named_range to the list of named_ranges."""
self._named_ranges.append(named_range)
def get_named_range(self, name):
"""Return the range specified by name."""
requested_range = None
for named_range in self._named_ranges:
if named_range.name == name:
requested_range = named_range
break
return requested_range
def remove_named_range(self, named_range):
"""Remove a named_range from this workbook."""
self._named_ranges.remove(named_range)
def save(self, filename):
"""Save the current workbook under the given `filename`.
Use this function instead of using an `ExcelWriter`.
.. warning::
When creating your workbook using `write_only` set to True,
you will only be able to call this function once. Subsequents attempts to
modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception.
"""
if self.write_only:
save_dump(self, filename)
else:
save_workbook(self, filename)
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.style_prop = {'table': {}, 'list': IndexedList()}
self.color_index = COLOR_INDEX
def __init__(self):
self.shared_styles = IndexedList(range(28))
self.shared_styles.add(Style())
class WB():
style_properties = None
shared_styles = IndexedList()
worksheets = []
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.shared_styles = IndexedList()
self.cond_styles = []
self.style_prop = {}
self.color_index = COLOR_INDEX
def _write_cell_xfs(self, number_format_node, fonts_node, fills_node,
borders_node):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
{'count': '%d' % len(self.styles)})
# default
def _get_default_vals():
return dict(numFmtId='0',
fontId='0',
fillId='0',
xfId='0',
borderId='0')
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_custom_fmts = IndexedList()
for st in self.styles:
vals = _get_default_vals()
font = st.font
if font != DEFAULTS.font:
if font not in _fonts:
font_id = _fonts.add(font)
self._write_font(fonts_node, st.font)
else:
font_id = _fonts.index(font)
vals['fontId'] = "%d" % (font_id + 1
) # There is one default font
vals['applyFont'] = '1'
border = st.border
if st.border != DEFAULTS.border:
if border not in _borders:
border_id = _borders.add(border)
self._write_border(borders_node, border)
else:
border_id = _borders.index(border)
vals['borderId'] = "%d" % (border_id + 1
) # There is one default border
vals['applyBorder'] = '1'
fill = st.fill
if fill != DEFAULTS.fill:
if fill not in _fills:
fill_id = _fills.add(st.fill)
fill_node = SubElement(fills_node, 'fill')
if isinstance(fill, PatternFill):
self._write_pattern_fill(fill_node, fill)
elif isinstance(fill, GradientFill):
self._write_gradient_fill(fill_node, fill)
else:
fill_id = _fills.index(fill)
vals['fillId'] = "%d" % (fill_id + 2
) # There are two default fills
vals['applyFill'] = '1'
nf = st.number_format
if nf != DEFAULTS.number_format:
fmt_id = numbers.builtin_format_id(nf)
if fmt_id is None:
if nf not in _custom_fmts:
fmt_id = _custom_fmts.add(nf) + 165
self._write_number_format(number_format_node, fmt_id,
nf)
else:
fmt_id = _custom_fmts.index(nf)
vals['numFmtId'] = '%d' % fmt_id
vals['applyNumberFormat'] = '1'
if st.alignment != DEFAULTS.alignment:
vals['applyAlignment'] = '1'
if st.protection != DEFAULTS.protection:
vals['applyProtection'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != DEFAULTS.alignment:
self._write_alignment(node, st.alignment)
if st.protection != DEFAULTS.protection:
self._write_protection(node, st.protection)
fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
class CommentWriter(object):
def extract_comments(self):
"""
extract list of comments and authors
"""
for _coord, cell in iteritems(self.sheet._cells):
if cell.comment is not None:
self.authors.add(cell.comment.author)
self.comments.append(cell.comment)
def __init__(self, sheet):
self.sheet = sheet
self.authors = IndexedList()
self.comments = []
self.extract_comments()
def write_comments(self):
# produce xml
root = Element("{%s}comments" % SHEET_MAIN_NS)
authorlist_tag = SubElement(root, "{%s}authors" % SHEET_MAIN_NS)
for author in self.authors:
leaf = SubElement(authorlist_tag, "{%s}author" % SHEET_MAIN_NS)
leaf.text = author
commentlist_tag = SubElement(root, "{%s}commentList" % SHEET_MAIN_NS)
for comment in self.comments:
attrs = {'ref': comment._parent.coordinate,
'authorId': '%d' % self.authors.index(comment.author),
'shapeId': '0'}
comment_tag = SubElement(commentlist_tag,
"{%s}comment" % SHEET_MAIN_NS, attrs)
text_tag = SubElement(comment_tag, "{%s}text" % SHEET_MAIN_NS)
run_tag = SubElement(text_tag, "{%s}r" % SHEET_MAIN_NS)
SubElement(run_tag, "{%s}rPr" % SHEET_MAIN_NS)
t_tag = SubElement(run_tag, "{%s}t" % SHEET_MAIN_NS)
t_tag.text = comment.text
return tostring(root)
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens,
{"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout,
"{%s}idmap" % officens,
{"{%s}ext" % vmlns: "edit", "data": "1"})
shape_type = SubElement(root,
"{%s}shapetype" % vmlns,
{"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type,
"{%s}path" % vmlns,
{"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"})
for i, comment in enumerate(self.comments):
self._write_comment_shape(root, comment, i)
return tostring(root)
def _write_comment_shape(self, root, comment, idx):
# get zero-indexed coordinates of the comment
row = comment._parent.row - 1
column = column_index_from_string(comment._parent.column) - 1
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:%(width)s;height:%(height)s;"
"z-index:1;visibility:hidden") % {'height': comment._height,
'width': comment._width}
attrs = {
"id": "_x0000_s%s" % (idx + 1026),
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = SubElement(root, "{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns,
{"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns,
{"color": "black", "obscured": "t"})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = str(row)
SubElement(client_data, "{%s}Column" % excelns).text = str(column)
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
_optimized_worksheet_class = DumpWorksheet
def __init__(self,
optimized_write=False,
encoding='utf-8',
worksheet_class=Worksheet,
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self.worksheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.style = Style()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.__thread_local_data = threading.local()
self.shared_strings = IndexedList()
self.shared_styles = IndexedList()
self.shared_styles.add(Style())
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.style_properties = None
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.code_name = u'ThisWorkbook'
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
@deprecated('this method is private and should not be called directly')
def read_workbook_settings(self, xml_source):
self._read_workbook_settings(xml_source)
def _read_workbook_settings(self, xml_source):
root = fromstring(xml_source)
view = root.find('*/' '{%s}workbookView' % SHEET_MAIN_NS)
if view is None:
return
if 'activeTab' in view.attrib:
self.active = int(view.attrib['activeTab'])
@property
def _local_data(self):
return self.__thread_local_data
@property
def excel_base_date(self):
return self.properties.excel_base_date
@property
def read_only(self):
return self.__read_only
@property
def write_only(self):
return self.__write_only
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def active(self):
"""Get the currently active sheet"""
return self.worksheets[self._active_sheet_index]
@active.setter
def active(self, value):
"""Set the active sheet"""
self._active_sheet_index = value
def create_sheet(self, index=None, title=None):
"""Create a worksheet (at an optional index).
:param index: optional position at which the sheet will be inserted
:type index: int
"""
if self.read_only:
raise ReadOnlyWorkbookException(
'Cannot create new sheet in a read-only workbook')
if self.write_only:
new_ws = self._optimized_worksheet_class(parent_workbook=self,
title=title)
self._worksheet_class = self._optimized_worksheet_class
else:
if title is not None:
new_ws = self._worksheet_class(parent_workbook=self,
title=title)
else:
new_ws = self._worksheet_class(parent_workbook=self)
self._add_sheet(worksheet=new_ws, index=index)
return new_ws
@deprecated(
"you probably want to use Workbook.create_sheet('sheet name') instead")
def add_sheet(self, worksheet, index=None):
self._add_sheet(worksheet, index)
def _add_sheet(self, worksheet, index=None):
"""Add an existing worksheet (at an optional index)."""
if not isinstance(worksheet, self._worksheet_class):
raise TypeError(
"The parameter you have given is not of the type '%s'" %
self._worksheet_class.__name__)
if worksheet.parent != self:
raise ValueError(
"You cannot add worksheets from another workbook.")
if index is None:
self.worksheets.append(worksheet)
else:
self.worksheets.insert(index, worksheet)
def remove_sheet(self, worksheet):
"""Remove a worksheet from this workbook."""
self.worksheets.remove(worksheet)
def get_sheet_by_name(self, name):
"""Returns a worksheet by its name.
Returns None if no worksheet has the name specified.
:param name: the name of the worksheet to look for
:type name: string
"""
try:
return self[name]
except KeyError:
return
def __contains__(self, key):
return key in set(self.sheetnames)
def get_index(self, worksheet):
"""Return the index of the worksheet."""
return self.worksheets.index(worksheet)
def __getitem__(self, key):
"""Returns a worksheet by its name.
:param name: the name of the worksheet to look for
:type name: string
"""
for sheet in self.worksheets:
if sheet.title == key:
return sheet
raise KeyError("Worksheet {0} does not exist.".format(key))
def __delitem__(self, key):
sheet = self[key]
self.remove_sheet(sheet)
def __iter__(self):
return iter(self.worksheets)
def get_sheet_names(self):
return self.sheetnames
@property
def sheetnames(self):
"""Returns the list of the names of worksheets in the workbook.
Names are returned in the worksheets order.
:rtype: list of strings
"""
return [s.title for s in self.worksheets]
def create_named_range(self, name, worksheet, range, scope=None):
"""Create a new named_range on a worksheet"""
if not isinstance(worksheet, self._worksheet_class):
raise TypeError("Worksheet is not of the right type")
named_range = NamedRange(name, [(worksheet, range)], scope)
self.add_named_range(named_range)
def get_named_ranges(self):
"""Return all named ranges"""
return self._named_ranges
def add_named_range(self, named_range):
"""Add an existing named_range to the list of named_ranges."""
self._named_ranges.append(named_range)
def get_named_range(self, name):
"""Return the range specified by name."""
requested_range = None
for named_range in self._named_ranges:
if named_range.name == name:
requested_range = named_range
break
return requested_range
def remove_named_range(self, named_range):
"""Remove a named_range from this workbook."""
self._named_ranges.remove(named_range)
def save(self, filename):
"""Save the current workbook under the given `filename`.
Use this function instead of using an `ExcelWriter`.
.. warning::
When creating your workbook using `write_only` set to True,
you will only be able to call this function once. Subsequents attempts to
modify or save the file will raise an :class:`openpyxl.shared.exc.WorkbookAlreadySaved` exception.
"""
if self.write_only:
save_dump(self, filename)
else:
save_workbook(self, filename)
class DummyWorkbook(object):
shared_styles = IndexedList()
def _write_cell_xfs(self, number_format_node, fonts_node, fills_node, borders_node):
""" write styles combinations based on ids found in tables """
# writing the cellXfs
cell_xfs = SubElement(self._root, 'cellXfs',
{'count':'%d' % len(self.styles)})
# default
def _get_default_vals():
return dict(numFmtId='0', fontId='0', fillId='0',
xfId='0', borderId='0')
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_custom_fmts = IndexedList()
for st in self.styles:
vals = _get_default_vals()
font = st.font
if font != DEFAULTS.font:
if font not in _fonts:
font_id = _fonts.add(font)
self._write_font(fonts_node, st.font)
else:
font_id = _fonts.index(font)
vals['fontId'] = "%d" % (font_id + 1) # There is one default font
vals['applyFont'] = '1'
border = st.border
if st.border != DEFAULTS.border:
if border not in _borders:
border_id = _borders.add(border)
self._write_border(borders_node, border)
else:
border_id = _borders.index(border)
vals['borderId'] = "%d" % (border_id + 1) # There is one default border
vals['applyBorder'] = '1'
fill = st.fill
if fill != DEFAULTS.fill:
if fill not in _fills:
fill_id = _fills.add(st.fill)
fill_node = SubElement(fills_node, 'fill')
if isinstance(fill, PatternFill):
self._write_pattern_fill(fill_node, fill)
elif isinstance(fill, GradientFill):
self._write_gradient_fill(fill_node, fill)
else:
fill_id = _fills.index(fill)
vals['fillId'] = "%d" % (fill_id + 2) # There are two default fills
vals['applyFill'] = '1'
nf = st.number_format
if nf != DEFAULTS.number_format:
fmt_id = numbers.builtin_format_id(nf)
if fmt_id is None:
if nf not in _custom_fmts:
fmt_id = _custom_fmts.add(nf) + 165
self._write_number_format(number_format_node, fmt_id, nf)
else:
fmt_id = _custom_fmts.index(nf)
vals['numFmtId'] = '%d' % fmt_id
vals['applyNumberFormat'] = '1'
if st.alignment != DEFAULTS.alignment:
vals['applyAlignment'] = '1'
if st.protection != DEFAULTS.protection:
vals['applyProtection'] = '1'
node = SubElement(cell_xfs, 'xf', vals)
if st.alignment != DEFAULTS.alignment:
self._write_alignment(node, st.alignment)
if st.protection != DEFAULTS.protection:
self._write_protection(node, st.protection)
fonts_node.attrib["count"] = "%d" % (len(_fonts) + 1)
borders_node.attrib["count"] = "%d" % (len(_borders) + 1)
fills_node.attrib["count"] = "%d" % (len(_fills) + 2)
number_format_node.attrib['count'] = '%d' % len(_custom_fmts)
def __init__(self):
self.shared_styles = IndexedList(range(28))
self.shared_styles.add(Style())
def read_string_table(xml_source):
"""Read in all shared strings in the table"""
root = fromstring(text=xml_source)
nodes = safe_iterator(root, '{%s}si' % SHEET_MAIN_NS)
strings = (get_string(node) for node in nodes)
return IndexedList(strings)