def _setup_styles(self):
"""Bootstrap styles"""
from ..styles.alignment import Alignment
from ..styles.borders import DEFAULT_BORDER
from ..styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from ..styles.fonts import DEFAULT_FONT
from ..styles.protection import Protection
from ..styles.colors import COLOR_INDEX
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleId()])
def parse(self):
self.parse_custom_num_formats()
self.parse_color_index()
self.style_prop['color_index'] = self.color_index
self.font_list = IndexedList(self.parse_fonts())
self.fill_list = IndexedList(self.parse_fills())
self.border_list = IndexedList(self.parse_borders())
self.parse_dxfs()
self.parse_cell_styles()
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.shared_styles = []
self.cell_styles = IndexedList()
self.cond_styles = []
self.style_prop = {}
self.color_index = COLOR_INDEX
self.font_list = IndexedList()
self.fill_list = IndexedList()
self.border_list = IndexedList()
self.alignments = IndexedList([Alignment()])
self.protections = IndexedList([Protection()])
self.custom_number_formats = {}
self.number_formats = IndexedList()
def parse_color_index(self):
"""Read in the list of indexed colors"""
colors =\
self.root.findall('{%s}colors/{%s}indexedColors/{%s}rgbColor' %
(SHEET_MAIN_NS, SHEET_MAIN_NS, SHEET_MAIN_NS))
if not colors:
return
self.color_index = IndexedList([node.get('rgb') for node in colors])
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_styles = []
_style_ids = []
builtin_formats = numbers.BUILTIN_FORMATS
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for index, xf in enumerate(xfs):
_style = {}
attrs = dict(xf.attrib)
alignmentId = protectionId = 0
numFmtId = int(xf.get("numFmtId", 0))
fontId = int(xf.get("fontId", 0))
fillId = int(xf.get("fillId", 0))
borderId = int(xf.get("borderId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
attrs["numFmtId"] = self.number_formats.add(format_code) + 164
else:
format_code = builtin_formats[numFmtId]
_style['number_format'] = format_code
if bool_attrib(xf, 'applyAlignment'):
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
attrs['alignmentId'] = self.alignments.add(alignment)
_style['alignment'] = alignment
if bool_attrib(xf, 'applyFont'):
_style['font'] = self.font_list[fontId]
if bool_attrib(xf, 'applyFill'):
_style['fill'] = self.fill_list[fillId]
if bool_attrib(xf, 'applyBorder'):
_style['border'] = self.border_list[borderId]
if bool_attrib(xf, 'applyProtection'):
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
attrs['protectionId'] = self.protections.add(protection)
_style['protection'] = protection
_styles.append(Style(**_style))
_style_ids.append(StyleId(**attrs))
self.shared_styles = _styles
self.cell_styles = IndexedList(_style_ids)
def parse_named_styles(self):
"""
Extract named styles
"""
ns = []
styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
self._parse_xfs(styles_node)
_ids = self.cell_styles
for _name, idx in self._parse_style_names():
_id = _ids[idx]
style = NamedStyle(name=_name)
style.border = self.border_list[_id.border]
style.fill = self.fill_list[_id.fill]
style.font = self.font_list[_id.font]
if _id.alignment:
style.alignment = self.alignments[_id.alignment]
if _id.protection:
style.protection = self.protections[_id.protection]
ns.append(style)
self.named_styles = IndexedList(ns)
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._setup_styles()
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.is_template = False
self._differential_styles = []
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
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._setup_styles()
self.loaded_theme = None
self._worksheet_class = worksheet_class
self.vba_archive = None
self.is_template = False
self._differential_styles = []
self._guess_types = guess_types
self.data_only = data_only
self.relationships = []
self.drawings = []
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = encoding
if not self.write_only:
self.worksheets.append(self._worksheet_class(parent_workbook=self))
def _setup_styles(self):
"""Bootstrap styles"""
from ..styles.alignment import Alignment
from ..styles.borders import DEFAULT_BORDER
from ..styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from ..styles.fonts import DEFAULT_FONT
from ..styles.protection import Protection
from ..styles.colors import COLOR_INDEX
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleId()])
@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 not None:
if 'activeTab' in view.attrib:
self.active = int(view.attrib['activeTab'])
@property
def shared_styles(self):
"""
Legacy
On the fly conversion of style references to style objects
"""
styles = []
for sid in self._cell_styles:
font = self._fonts[sid.font]
fill = self._fills[sid.fill]
border = self._borders[sid.fill]
alignment = self._alignments[sid.alignment]
protection = self._protections[sid.protection]
nf_id = sid.number_format
if nf_id < 164:
number_format = BUILTIN_FORMATS.get(nf_id, "General")
else:
number_format = self._number_formats[sid.number_format - 164]
styles.append(
Style(font, fill, border, alignment, number_format,
protection))
return styles
@property
def _local_data(self):
return self.__thread_local_data
@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.
:param name: the name of the worksheet to look for
:type name: string
"""
return self[name]
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.read_only:
raise TypeError("""Workbook is read-only""")
if self.write_only:
save_dump(self, filename)
else:
save_workbook(self, filename)
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)
class SharedStylesParser(object):
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.shared_styles = []
self.cell_styles = IndexedList()
self.cond_styles = []
self.style_prop = {}
self.color_index = COLOR_INDEX
self.font_list = IndexedList()
self.fill_list = IndexedList()
self.border_list = IndexedList()
self.alignments = IndexedList([Alignment()])
self.protections = IndexedList([Protection()])
self.custom_number_formats = {}
self.number_formats = IndexedList()
def parse(self):
self.parse_custom_num_formats()
self.parse_color_index()
self.style_prop['color_index'] = self.color_index
self.font_list = IndexedList(self.parse_fonts())
self.fill_list = IndexedList(self.parse_fills())
self.border_list = IndexedList(self.parse_borders())
self.parse_dxfs()
self.parse_cell_styles()
def parse_custom_num_formats(self):
"""Read in custom numeric formatting rules from the shared style table"""
custom_formats = {}
num_fmts = self.root.findall('{%s}numFmts/{%s}numFmt' %
(SHEET_MAIN_NS, SHEET_MAIN_NS))
for node in num_fmts:
idx = int(node.get('numFmtId'))
self.custom_number_formats[idx] = node.get('formatCode')
self.number_formats.append(node.get('formatCode'))
def parse_color_index(self):
"""Read in the list of indexed colors"""
colors =\
self.root.findall('{%s}colors/{%s}indexedColors/{%s}rgbColor' %
(SHEET_MAIN_NS, SHEET_MAIN_NS, SHEET_MAIN_NS))
if not colors:
return
self.color_index = IndexedList([node.get('rgb') for node in colors])
def parse_dxfs(self):
"""Read in the dxfs effects - used by conditional formatting."""
for node in self.root.findall("{%s}dxfs/{%s}dxf" %
(SHEET_MAIN_NS, SHEET_MAIN_NS)):
self.cond_styles.append(DifferentialStyle.from_tree(node))
def parse_fonts(self):
"""Read in the fonts"""
fonts = self.root.findall('{%s}fonts/{%s}font' %
(SHEET_MAIN_NS, SHEET_MAIN_NS))
for node in fonts:
yield Font.from_tree(node)
def parse_fills(self):
"""Read in the list of fills"""
fills = self.root.findall('{%s}fills/{%s}fill' %
(SHEET_MAIN_NS, SHEET_MAIN_NS))
for fill in fills:
yield Fill.from_tree(fill)
def parse_borders(self):
"""Read in the boarders"""
borders = self.root.findall('{%s}borders/{%s}border' %
(SHEET_MAIN_NS, SHEET_MAIN_NS))
for border_node in borders:
yield Border.from_tree(border_node)
def parse_named_styles(self):
"""
Extract named styles
"""
ns = []
styles_node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
self._parse_xfs(styles_node)
_ids = self.cell_styles
for _name, idx in self._parse_style_names():
_id = _ids[idx]
style = NamedStyle(name=_name)
style.border = self.border_list[_id.border]
style.fill = self.fill_list[_id.fill]
style.font = self.font_list[_id.font]
if _id.alignment:
style.alignment = self.alignments[_id.alignment]
if _id.protection:
style.protection = self.protections[_id.protection]
ns.append(style)
self.named_styles = IndexedList(ns)
def _parse_style_names(self):
names_node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS)
for _name in names_node:
yield _name.get("name"), int(_name.get("xfId"))
def parse_cell_styles(self):
"""
Extract individual cell styles
"""
node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
if node is not None:
self._parse_xfs(node)
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_styles = []
_style_ids = []
builtin_formats = numbers.BUILTIN_FORMATS
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for index, xf in enumerate(xfs):
_style = {}
attrs = dict(xf.attrib)
alignmentId = protectionId = 0
numFmtId = int(xf.get("numFmtId", 0))
fontId = int(xf.get("fontId", 0))
fillId = int(xf.get("fillId", 0))
borderId = int(xf.get("borderId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
attrs["numFmtId"] = self.number_formats.add(format_code) + 164
else:
format_code = builtin_formats[numFmtId]
_style['number_format'] = format_code
if bool_attrib(xf, 'applyAlignment'):
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
attrs['alignmentId'] = self.alignments.add(alignment)
_style['alignment'] = alignment
if bool_attrib(xf, 'applyFont'):
_style['font'] = self.font_list[fontId]
if bool_attrib(xf, 'applyFill'):
_style['fill'] = self.fill_list[fillId]
if bool_attrib(xf, 'applyBorder'):
_style['border'] = self.border_list[borderId]
if bool_attrib(xf, 'applyProtection'):
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
attrs['protectionId'] = self.protections.add(protection)
_style['protection'] = protection
_styles.append(Style(**_style))
_style_ids.append(StyleId(**attrs))
self.shared_styles = _styles
self.cell_styles = IndexedList(_style_ids)
def __init__(self, sheet):
self.sheet = sheet
self.authors = IndexedList()
self.comments = []
self.extract_comments()
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, 1026):
shape = self._write_comment_shape(comment, i)
root.append(shape)
return tostring(root)
def _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 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%04d" % idx,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%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 = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape