class DummyWorkbook:
data_only = False
_colors = []
encoding = "utf8"
epoch = CALENDAR_WINDOWS_1900
def __init__(self):
self._differential_styles = [DifferentialStyle()] * 5
self.shared_strings = ['a'] * 30
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList([Border()] * 30)
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i] * 9)))
self._cell_styles.add(StyleArray(
[0, 4, 6, 0, 0, 1, 0, 0,
0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
self._date_formats = set()
def create_sheet(self, title):
return Worksheet(self)
class DummyWorkbook:
def __init__(self):
self.shared_styles = IndexedList()
self._cell_styles = IndexedList()
self._cell_styles.add(StyleArray())
self._cell_styles.add(StyleArray([10, 0, 0, 0, 0, 0, 0, 0, 0, 0]))
self.sheetnames = []
class DummyWorkbook:
def __init__(self):
self.shared_styles = IndexedList()
self._cell_styles = IndexedList()
self._cell_styles.add(StyleArray())
self._cell_styles.add(StyleArray([10,0,0,0,0,0,0,0,0,0]))
self.sheetnames = []
class DummyWorkbook:
def __init__(self):
self.shared_styles = IndexedList()
self._cell_styles = IndexedList()
self._cell_styles.add(StyleId())
self._cell_styles.add(StyleId(fontId=10, numFmtId=0, borderId=0, fillId=0, protectionId=0, alignmentId=0))
def get_sheet_names(self):
return []
def WorkSheetParser():
"""Setup a parser instance with an empty source"""
from .._reader import WorkSheetParser
styles = IndexedList()
for i in range(29):
styles.add((StyleArray([i]*9)))
styles.add(StyleArray([0,4,6,14,0,1,0,0,0])) #fillId=4, borderId=6, number_format=14 alignmentId=1))
date_formats = set([1, 29])
return WorkSheetParser(None, {0:'a'}, date_formats=date_formats)
class DummyWorkbook:
def __init__(self):
self.shared_styles = IndexedList()
self._cell_styles = IndexedList()
self._cell_styles.add(StyleId())
self._cell_styles.add(
StyleId(fontId=10,
numFmtId=0,
borderId=0,
fillId=0,
protectionId=0,
alignmentId=0))
def get_sheet_names(self):
return []
class CellStyleList(Serialisable):
tagname = "cellXfs"
__attrs__ = ("count",)
count = Integer(allow_none=True)
xf = Sequence(expected_type=CellStyle)
alignment = Sequence(expected_type=Alignment)
protection = Sequence(expected_type=Protection)
__elements__ = ('xf',)
def __init__(self,
count=None,
xf=(),
):
self.xf = xf
@property
def count(self):
return len(self.xf)
def __getitem__(self, idx):
return self.xf[idx]
def _to_array(self):
"""
Extract protection and alignments, convert to style array
"""
self.prots = IndexedList([Protection()])
self.alignments = IndexedList([Alignment()])
styles = [] # allow duplicates
for xf in self.xf:
style = xf.to_array()
if xf.alignment is not None:
style.alignmentId = self.alignments.add(xf.alignment)
if xf.protection is not None:
style.protectionId = self.prots.add(xf.protection)
styles.append(style)
return IndexedList(styles)
class CellStyleList(Serialisable):
tagname = "cellXfs"
__attrs__ = ("count", )
count = Integer(allow_none=True)
xf = Sequence(expected_type=CellStyle)
alignment = Sequence(expected_type=Alignment)
protection = Sequence(expected_type=Protection)
__elements__ = ('xf', )
def __init__(
self,
count=None,
xf=(),
):
self.xf = xf
@property
def count(self):
return len(self.xf)
def __getitem__(self, idx):
return self.xf[idx]
def _to_array(self):
"""
Extract protection and alignments, convert to style array
"""
self.prots = IndexedList([Protection()])
self.alignments = IndexedList([Alignment()])
styles = [] # allow duplicates
for xf in self.xf:
style = xf.to_array()
if xf.alignment is not None:
style.alignmentId = self.alignments.add(xf.alignment)
if xf.protection is not None:
style.protectionId = self.prots.add(xf.protection)
styles.append(style)
return IndexedList(styles)
def from_comments(cls, comments):
"""
Create a comment sheet from a list of comments for a particular worksheet
"""
authors = IndexedList()
# dedupe authors and get indexes
for comment in comments:
comment.authorId = authors.add(comment.author)
return cls(authors=AuthorList(authors), commentList=comments)
def from_comments(cls, comments):
"""
Create a comment sheet from a list of comments for a particular worksheet
"""
authors = IndexedList()
# dedupe authors and get indexes
for comment in comments:
comment.authorId = authors.add(comment.author)
return cls(authors=AuthorList(authors), commentList=comments)
class DummyWorkbook:
guess_types = False
data_only = False
_colors = []
encoding = "utf8"
def __init__(self):
self._differential_styles = []
self.shared_strings = IndexedList()
self.shared_strings.add("hello world")
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i]*9)))
self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
def create_sheet(self, title):
return Worksheet(self)
class DummyWorkbook:
_guess_types = False
data_only = False
_colors = []
def __init__(self):
self._differential_styles = []
self.shared_strings = IndexedList()
self.shared_strings.add("hello world")
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleArray([i]*9)))
self._cell_styles.add(StyleArray([0,4,6,0,0,1,0,0,0])) #fillId=4, borderId=6, alignmentId=1))
self.sheetnames = []
def create_sheet(self, title):
return Worksheet(self)
def write_comments(self):
"""
Create list of comments and authors
"""
authors = IndexedList()
# dedupe authors and get indexes
for comment in self.comments:
comment.authorId = authors.add(comment.author)
root = CommentSheet(authors=AuthorList(authors), commentList=self.comments)
return tostring(root.to_tree())
class DummyWorkbook:
guess_types = False
data_only = False
_colors = []
encoding = "utf8"
epoch = CALENDAR_MAC_1904
def __init__(self):
self._differential_styles = []
self.shared_strings = IndexedList()
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
self._cell_styles.add(StyleArray([0, 0, 0, 14, 0, 0, 0, 0, 0]))
self.sheetnames = []
def create_sheet(self, title):
return Worksheet(self)
def write_comments(self):
"""
Create list of comments and authors
"""
authors = IndexedList()
# dedupe authors and get indexes
for comment in self.comments:
comment.authorId = authors.add(comment.author)
root = CommentSheet(authors=AuthorList(authors),
commentList=self.comments)
return tostring(root.to_tree())
def write_comments(self):
"""
Create list of comments and authors
Sorted by row, col
"""
# produce xml
authors = IndexedList()
for comment in self.comments:
comment.authorId = authors.add(comment.author)
author_list = AuthorList(authors)
root = CommentSheet(authors=author_list, commentList=self.comments)
return tostring(root.to_tree())
def write_comments(self):
"""
Create list of comments and authors
Sorted by row, col
"""
# produce xml
authors = IndexedList()
for comment in self.comments:
comment.authorId = authors.add(comment.author)
author_list = AuthorList(authors)
root = CommentSheet(authors=author_list, commentList=self.comments)
return tostring(root.to_tree())
def write_comments(self):
"""
Create list of comments and authors
Sorted by row, col
"""
# produce xml
authors = IndexedList()
for _coord, cell in sorted(self.sheet._cells.items()):
if cell.comment is not None:
comment = Comment(ref=cell.coordinate)
comment.authorId = authors.add(cell.comment.author)
comment.text.t = cell.comment.text
comment.height = cell.comment.height
comment.width = cell.comment.width
self.comments.append(comment)
author_list = AuthorList(authors)
root = CommentSheet(authors=author_list, commentList=self.comments)
return tostring(root.to_tree())
def write_comments(self):
"""
Create list of comments and authors
Sorted by row, col
"""
# produce xml
authors = IndexedList()
for _coord, cell in sorted(self.sheet._cells.items()):
if cell.comment is not None:
comment = Comment(ref=cell.coordinate)
comment.authorId = authors.add(cell.comment.author)
comment.text.t = cell.comment.text
comment.height = cell.comment.height
comment.width = cell.comment.width
self.comments.append(comment)
author_list = AuthorList(authors)
root = CommentSheet(authors=author_list, commentList=self.comments)
return tostring(root.to_tree())
class DummyWorkbook:
_guess_types = False
data_only = False
def __init__(self):
self.shared_strings = IndexedList()
self.shared_strings.add("hello world")
self.shared_styles = 28*[DummyStyle()]
self.shared_styles.append(Style())
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
for i in range(29):
self._cell_styles.add((StyleId(i, i, i, i, i, i)))
self._cell_styles.add(StyleId(fillId=4, borderId=6, alignmentId=1, protectionId=0))
class DummyWorkbook:
_guess_types = False
data_only = False
def __init__(self):
self.shared_strings = IndexedList()
self.shared_strings.add("hello world")
self.shared_styles = 28*[DummyStyle()]
self.shared_styles.append(Style())
self._fonts = IndexedList()
self._fills = IndexedList()
self._number_formats = IndexedList()
self._borders = IndexedList()
self._alignments = IndexedList()
self._protections = IndexedList()
self._cell_styles = IndexedList()
self.vba_archive = None
for i in range(29):
self._cell_styles.add((StyleId(i, i, i, i, i, i)))
self._cell_styles.add(StyleId(fillId=4, borderId=6, alignmentId=1, protectionId=0))
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
_read_only = False
_data_only = False
_keep_links = True
template = False
path = "/xl/workbook.xml"
def __init__(self,
write_only=False,
):
self._sheets = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyleList
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([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0))
@property
def read_only(self):
return self._read_only
@property
def data_only(self):
return self._data_only
@property
def write_only(self):
return self.__write_only
@property
def keep_links(self):
return self._keep_links
@deprecated("Use the .active property")
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def active(self):
"""Get the currently active sheet or None"""
try:
return self._sheets[self._active_sheet_index]
except IndexError:
pass
@active.setter
def active(self, value):
"""Set the active sheet"""
self._active_sheet_index = value
def create_sheet(self, title=None, index=None):
"""Create a worksheet (at an optional index).
:param title: optional title of the sheet
:type tile: unicode
: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 = WriteOnlyWorksheet(parent=self, title=title)
else:
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
def _add_sheet(self, sheet, index=None):
"""Add an worksheet (at an optional index)."""
if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
raise TypeError("Cannot be added to a workbook")
if sheet.parent != self:
raise ValueError("You cannot add worksheets from another workbook.")
if index is None:
self._sheets.append(sheet)
else:
self._sheets.insert(index, sheet)
def remove(self, worksheet):
"""Remove a worksheet from this workbook."""
self._sheets.remove(worksheet)
@deprecated("Use wb.remove(worksheet) or del wb[sheetname]")
def remove_sheet(self, worksheet):
"""Remove a worksheet from this workbook."""
self.remove(worksheet)
def create_chartsheet(self, title=None, index=None):
if self.read_only:
raise ReadOnlyWorkbookException("Cannot create new sheet in a read-only workbook")
cs = Chartsheet(parent=self, title=title)
self._add_sheet(cs, index)
return cs
@deprecated("Use wb[sheetname]")
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 index(self, worksheet):
"""Return the index of a worksheet."""
return self.worksheets.index(worksheet)
@deprecated("Use wb.index(worksheet)")
def get_index(self, worksheet):
"""Return the index of the worksheet."""
return self.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)
def __iter__(self):
return iter(self.worksheets)
@deprecated("Use wb.sheetnames")
def get_sheet_names(self):
return self.sheetnames
@property
def worksheets(self):
return [s for s in self._sheets if isinstance(s, (Worksheet, ReadOnlyWorksheet, WriteOnlyWorksheet))]
@property
def chartsheets(self):
return [s for s in self._sheets if isinstance(s, Chartsheet)]
@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._sheets]
def create_named_range(self, name, worksheet=None, value=None, scope=None):
"""Create a new named_range on a worksheet"""
defn = DefinedName(name=name, localSheetId=scope)
if worksheet is not None:
defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value)
else:
defn.value = value
self.defined_names.append(defn)
def add_named_style(self, style):
"""
Add a named style
"""
self._named_styles.append(style)
style.bind(self)
@property
def named_styles(self):
"""
List available named styles
"""
return self._named_styles.names
@deprecated("Use workbook.defined_names.definedName")
def get_named_ranges(self):
"""Return all named ranges"""
return self.defined_names.definedName
@deprecated("Use workbook.defined_names.append")
def add_named_range(self, named_range):
"""Add an existing named_range to the list of named_ranges."""
self.defined_names.append(named_range)
@deprecated("Use workbook.defined_names[name]")
def get_named_range(self, name):
"""Return the range specified by name."""
return self.defined_names[name]
@deprecated("Use del workbook.defined_names[name]")
def remove_named_range(self, named_range):
"""Remove a named_range from this workbook."""
del self.defined_names[named_range]
@property
def mime_type(self):
"""
The mime type is determined by whether a workbook is a template or
not and whether it contains macros or not. Excel requires the file
extension to match but openpyxl does not enforce this.
"""
ct = self.template and XLTX or XLSX
if self.vba_archive:
ct = self.template and XLTM or XLSM
return ct
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)
@property
def style_names(self):
"""
List of named styles
"""
return [s.name for s in self._named_styles]
def copy_worksheet(self, from_worksheet):
"""Copy an existing worksheet in the current workbook
:warning: This function cannot copy worksheets between workbooks.
worksheets can only be copied within the workbook that they belong
:param from_worksheet: the worksheet to be copied from
:return: copy of the initial worksheet
"""
if self.__write_only or self._read_only:
raise ValueError("Cannot copy worksheets in read-only or write-only mode")
new_title = u"{0} Copy".format(from_worksheet.title)
to_worksheet = self.create_sheet(title=new_title)
cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet)
cp.copy_worksheet()
return to_worksheet
def close(self):
"""
Close workbook file if open. Only affects read-only and write-only modes.
"""
if hasattr(self, '_archive'):
self._archive.close()
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.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 num_fmt_node in num_fmts:
fmt_code = num_fmt_node.get('formatCode').lower()
self.number_formats.append(fmt_code)
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))
if numFmtId < 164:
format_code = builtin_formats.get(numFmtId, 'General')
else:
format_code = self.number_formats[numFmtId-165]
_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)
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
_read_only = False
_data_only = False
def __init__(
self,
write_only=False,
):
self._sheets = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyles
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([StyleArray()])
self._named_styles = NamedStyles(
[NamedStyle(font=DEFAULT_FONT, builtinId=0)])
@property
def read_only(self):
return self._read_only
@property
def data_only(self):
return self._data_only
@property
def write_only(self):
return self.__write_only
@deprecated("Use the .active property")
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def active(self):
"""Get the currently active sheet"""
return self._sheets[self._active_sheet_index]
@active.setter
def active(self, value):
"""Set the active sheet"""
self._active_sheet_index = value
def create_sheet(self, title=None, index=None):
"""Create a worksheet (at an optional index).
:param title: optional title of the sheet
:type tile: unicode
: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 = WriteOnlyWorksheet(parent=self, title=title)
else:
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
def _add_sheet(self, sheet, index=None):
"""Add an worksheet (at an optional index)."""
if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
raise TypeError("Cannot be added to a workbook")
if sheet.parent != self:
raise ValueError(
"You cannot add worksheets from another workbook.")
if index is None:
self._sheets.append(sheet)
else:
self._sheets.insert(index, sheet)
def remove(self, worksheet):
"""Remove a worksheet from this workbook."""
self._sheets.remove(worksheet)
@deprecated("Use wb.remove(worksheet) or del wb[sheetname]")
def remove_sheet(self, worksheet):
"""Remove a worksheet from this workbook."""
self.remove(worksheet)
def create_chartsheet(self, title=None, index=None):
if self.read_only:
raise ReadOnlyWorkbookException(
"Cannot create new sheet in a read-only workbook")
cs = Chartsheet(parent=self, title=title)
self._add_sheet(cs, index)
return cs
@deprecated("Use wb[sheetname]")
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 index(self, worksheet):
"""Return the index of a worksheet."""
return self.worksheets.index(worksheet)
@deprecated("Use wb.index(worksheet)")
def get_index(self, worksheet):
"""Return the index of the worksheet."""
return self.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)
def __iter__(self):
return iter(self.worksheets)
@deprecated("Use wb.sheetnames")
def get_sheet_names(self):
return self.sheetnames
@property
def worksheets(self):
return [
s for s in self._sheets
if isinstance(s, (Worksheet, ReadOnlyWorksheet,
WriteOnlyWorksheet))
]
@property
def chartsheets(self):
return [s for s in self._sheets if isinstance(s, Chartsheet)]
@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._sheets]
def create_named_range(self, name, worksheet=None, value=None, scope=None):
"""Create a new named_range on a worksheet"""
defn = DefinedName(name=name, localSheetId=scope)
if worksheet is not None:
defn.value = "{0}!{1}".format(worksheet.title, value)
else:
defn.value = value
self.defined_names.append(defn)
@deprecated("Use workbook.defined_names.definedName")
def get_named_ranges(self):
"""Return all named ranges"""
return self.defined_names.definedName
@deprecated("Use workbook.defined_names.append")
def add_named_range(self, named_range):
"""Add an existing named_range to the list of named_ranges."""
self.defined_names.append(named_range)
@deprecated("Use workbook.defined_names[name]")
def get_named_range(self, name):
"""Return the range specified by name."""
return self.defined_names[name]
@deprecated("Use del workbook.defined_names[name]")
def remove_named_range(self, named_range):
"""Remove a named_range from this workbook."""
del self.defined_names[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)
@property
def style_names(self):
"""
List of named styles
"""
return [s.name for s in self._named_styles]
def copy_worksheet(self, from_worksheet):
"""Copy an existing worksheet in the current workbook
:warning: This function cannot copy worksheets between workbooks.
worksheets can only be copied within the workbook that they belong
:param from_worksheet: the worksheet to be copied from
:return: copy of the initial worksheet
"""
if self.__write_only or self._read_only:
raise ValueError(
"Cannot copy worksheets in read-only or write-only mode")
new_title = "{0} Copy".format(from_worksheet.title)
to_worksheet = self.create_sheet(title=new_title)
cp = WorksheetCopy(source_worksheet=from_worksheet,
target_worksheet=to_worksheet)
cp.copy_worksheet()
return to_worksheet
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
_read_only = False
_data_only = False
template = False
path = "/xl/workbook.xml"
def __init__(
self,
write_only=False,
iso_dates=False,
):
self._sheets = []
self._pivots = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.epoch = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
self.iso_dates = iso_dates
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
self.calculation = CalcProperties()
self.views = [BookView()]
def _setup_styles(self):
"""Bootstrap styles"""
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([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0))
self._table_styles = TableStyleList()
@property
def read_only(self):
return self._read_only
@property
def data_only(self):
return self._data_only
@property
def write_only(self):
return self.__write_only
@property
def guess_types(self):
return getattr(self, '__guess_types', False)
@guess_types.setter
def guess_types(self, value):
self.__guess_types = value
@deprecated("Use the .active property")
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def excel_base_date(self):
return self.epoch
@property
def active(self):
"""Get the currently active sheet or None
:type: :class:`openpyxl.worksheet.worksheet.Worksheet`
"""
try:
return self._sheets[self._active_sheet_index]
except IndexError:
pass
@active.setter
def active(self, value):
"""Set the active sheet"""
if not isinstance(value, (_WorkbookChild, INTEGER_TYPES)):
raise TypeError(
"Value must be either a worksheet, chartsheet or numerical index"
)
if isinstance(value, INTEGER_TYPES):
self._active_sheet_index = value
return
#if self._sheets and 0 <= value < len(self._sheets):
#value = self._sheets[value]
#else:
#raise ValueError("Sheet index is outside the range of possible values", value)
if value not in self._sheets:
raise ValueError("Worksheet is not in the workbook")
if value.sheet_state != "visible":
raise ValueError("Only visible sheets can be made active")
idx = self._sheets.index(value)
self._active_sheet_index = idx
def create_sheet(self, title=None, index=None):
"""Create a worksheet (at an optional index).
:param title: optional title of the sheet
:type title: unicode
: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 = WriteOnlyWorksheet(parent=self, title=title)
else:
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
def _add_sheet(self, sheet, index=None):
"""Add an worksheet (at an optional index)."""
if not isinstance(sheet, (Worksheet, WriteOnlyWorksheet, Chartsheet)):
raise TypeError("Cannot be added to a workbook")
if sheet.parent != self:
raise ValueError(
"You cannot add worksheets from another workbook.")
if index is None:
self._sheets.append(sheet)
else:
self._sheets.insert(index, sheet)
def remove(self, worksheet):
"""Remove `worksheet` from this workbook."""
idx = self._sheets.index(worksheet)
localnames = self.defined_names.localnames(scope=idx)
for name in localnames:
self.defined_names.delete(name, scope=idx)
self._sheets.remove(worksheet)
@deprecated("Use wb.remove(worksheet) or del wb[sheetname]")
def remove_sheet(self, worksheet):
"""Remove `worksheet` from this workbook."""
self.remove(worksheet)
def create_chartsheet(self, title=None, index=None):
if self.read_only:
raise ReadOnlyWorkbookException(
"Cannot create new sheet in a read-only workbook")
cs = Chartsheet(parent=self, title=title)
self._add_sheet(cs, index)
return cs
@deprecated("Use wb[sheetname]")
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 index(self, worksheet):
"""Return the index of a worksheet."""
return self.worksheets.index(worksheet)
@deprecated("Use wb.index(worksheet)")
def get_index(self, worksheet):
"""Return the index of the worksheet."""
return self.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 + self.chartsheets:
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)
def __iter__(self):
return iter(self.worksheets)
@deprecated("Use wb.sheetnames")
def get_sheet_names(self):
return self.sheetnames
@property
def worksheets(self):
"""A list of sheets in this workbook
:type: list of :class:`openpyxl.worksheet.worksheet.Worksheet`
"""
return [
s for s in self._sheets
if isinstance(s, (Worksheet, ReadOnlyWorksheet,
WriteOnlyWorksheet))
]
@property
def chartsheets(self):
"""A list of Chartsheets in this workbook
:type: list of :class:`openpyxl.chartsheet.chartsheet.Chartsheet`
"""
return [s for s in self._sheets if isinstance(s, Chartsheet)]
@property
def sheetnames(self):
"""Returns the list of the names of worksheets in this workbook.
Names are returned in the worksheets order.
:type: list of strings
"""
return [s.title for s in self._sheets]
def create_named_range(self, name, worksheet=None, value=None, scope=None):
"""Create a new named_range on a worksheet"""
defn = DefinedName(name=name, localSheetId=scope)
if worksheet is not None:
defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title),
value)
else:
defn.value = value
self.defined_names.append(defn)
def add_named_style(self, style):
"""
Add a named style
"""
self._named_styles.append(style)
style.bind(self)
@property
def named_styles(self):
"""
List available named styles
"""
return self._named_styles.names
@deprecated("Use workbook.defined_names.definedName")
def get_named_ranges(self):
"""Return all named ranges"""
return self.defined_names.definedName
@deprecated("Use workbook.defined_names.append")
def add_named_range(self, named_range):
"""Add an existing named_range to the list of named_ranges."""
self.defined_names.append(named_range)
@deprecated("Use workbook.defined_names[name]")
def get_named_range(self, name):
"""Return the range specified by name."""
return self.defined_names[name]
@deprecated("Use del workbook.defined_names[name]")
def remove_named_range(self, named_range):
"""Remove a named_range from this workbook."""
del self.defined_names[named_range]
@property
def mime_type(self):
"""
The mime type is determined by whether a workbook is a template or
not and whether it contains macros or not. Excel requires the file
extension to match but openpyxl does not enforce this.
"""
ct = self.template and XLTX or XLSX
if self.vba_archive:
ct = self.template and XLTM or XLSM
return ct
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 and not self.worksheets:
self.create_sheet()
save_workbook(self, filename)
@property
def style_names(self):
"""
List of named styles
"""
return [s.name for s in self._named_styles]
def copy_worksheet(self, from_worksheet):
"""Copy an existing worksheet in the current workbook
.. warning::
This function cannot copy worksheets between workbooks.
worksheets can only be copied within the workbook that they belong
:param from_worksheet: the worksheet to be copied from
:return: copy of the initial worksheet
"""
if self.__write_only or self._read_only:
raise ValueError(
"Cannot copy worksheets in read-only or write-only mode")
new_title = u"{0} Copy".format(from_worksheet.title)
to_worksheet = self.create_sheet(title=new_title)
cp = WorksheetCopy(source_worksheet=from_worksheet,
target_worksheet=to_worksheet)
cp.copy_worksheet()
return to_worksheet
def close(self):
"""
Close workbook file if open. Only affects read-only and write-only modes.
"""
if hasattr(self, '_archive'):
self._archive.close()
class SharedStylesParser(object):
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.cell_styles = IndexedList()
self.differential_styles = []
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.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()
self.parse_named_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.differential_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
"""
node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
styles = self._parse_xfs(node)
names = self._parse_style_names()
for style in names.values():
_id = styles[style.xfId]
style.border = self.border_list[_id.borderId]
style.fill = self.fill_list[_id.fillId]
style.font = self.font_list[_id.fontId]
if _id.alignmentId:
style.alignment = self.alignments[_id.alignmentId]
if _id.protectionId:
style.protection = self.protections[_id.protectionId]
self.named_styles = names
def _parse_style_names(self):
"""
Extract style names. There can be duplicates in which case last wins
"""
node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS)
names = {}
for _name in node:
name = _name.get("name")
style = NamedStyle(name=name,
builtinId=_name.get("builtinId"),
hidden=_name.get("hidden"))
style.xfId = int(_name.get("xfId"))
names[name] = style
return names
def parse_cell_styles(self):
"""
Extract individual cell styles
"""
node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
if node is not None:
self.cell_styles = self._parse_xfs(node)
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_style_ids = []
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for xf in xfs:
style = StyleArray.from_tree(xf)
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
style.alignmentId = self.alignments.add(alignment)
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
style.protectionId = self.protections.add(protection)
numFmtId = int(xf.get("numFmtId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
style.numFmtId = self.number_formats.add(format_code) + 164
_style_ids.append(style)
return IndexedList(_style_ids)
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.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 num_fmt_node in num_fmts:
fmt_code = num_fmt_node.get('formatCode').lower()
self.number_formats.append(fmt_code)
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))
if numFmtId < 164:
format_code = builtin_formats.get(numFmtId, 'General')
else:
try:
format_code = self.number_formats[numFmtId - 164]
except IndexError:
if self.number_formats:
format_code = self.number_formats[0]
else:
format_code = None
_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)
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.conditional_formats = []
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 openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.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(0, 0, 0, 0, 0, 0)])
@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.write_only:
save_dump(self, filename)
else:
save_workbook(self, filename)
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
def __init__(self,
optimized_write=False,
encoding='utf-8',
guess_types=False,
data_only=False,
read_only=False,
write_only=False):
self._sheets = []
self._active_sheet_index = 0
self._named_ranges = []
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = []
self._guess_types = guess_types
self.data_only = data_only
self._drawings = []
self._charts = []
self._images = []
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = encoding
if not self.write_only:
self._sheets.append(Worksheet(self))
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.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([StyleArray()])
self._named_styles = {'Normal': NamedStyle(font=DEFAULT_FONT)}
@property
def read_only(self):
return self.__read_only
@property
def write_only(self):
return self.__write_only
@deprecated("Use the .active property")
def get_active_sheet(self):
"""Returns the current active sheet."""
return self.active
@property
def active(self):
"""Get the currently active sheet"""
return self._sheets[self._active_sheet_index]
@active.setter
def active(self, value):
"""Set the active sheet"""
self._active_sheet_index = value
def create_sheet(self, title=None, index=None):
"""Create a worksheet (at an optional index).
:param title: optional title of the sheet
:type tile: unicode
: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 = WriteOnlyWorksheet(parent_workbook=self, title=title)
else:
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
def _add_sheet(self, sheet, index=None):
"""Add an worksheet (at an optional index)."""
if not isinstance(sheet, (Worksheet, Chartsheet)):
raise TypeError("Cannot be added to a workbook")
if sheet.parent != self:
raise ValueError(
"You cannot add worksheets from another workbook.")
if index is None:
self._sheets.append(sheet)
else:
self._sheets.insert(index, sheet)
def remove_sheet(self, worksheet):
"""Remove a worksheet from this workbook."""
self._sheets.remove(worksheet)
def create_chartsheet(self, title=None, index=None):
if self.read_only:
raise ReadOnlyWorkbookException(
"Cannot create new sheet in a read-only workbook")
cs = Chartsheet(parent=self, title=title)
self._add_sheet(cs, index=None)
return cs
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 worksheets(self):
return [s for s in self._sheets if isinstance(s, Worksheet)]
@property
def chartsheets(self):
return [s for s in self._sheets if isinstance(s, Chartsheet)]
@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._sheets]
def create_named_range(self, name, worksheet, range, scope=None):
"""Create a new named_range on a worksheet"""
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)
class ExcelRenderer(mistune.Renderer):
"""
Renders a Markdown formatted text to an OOXML worksheet.
Each block item (paragraph, headline, code...) is rendered in a
separate cell, while inline elements (including newlines) are
placed into cells as rich text.
Some features of Markdown are not supported:
- Tables
- Images
- HTML tags
- Footnotes (for the moment)
- List item continuation after a nested list (is considered as a new
list item)
Links are output in text as footnotes and target is placed at the end,
after a blank cell.
When using this renderer, the markdown processor will output an
instance of :py:class:`Collector`. Use its :py:func:`~Collector.render`
function to get the result in a worksheet.
.. sourcecode:: python
from openpyxl import Workbook
renderer = ExcelRenderer()
markdown = mistune.Markdown(renderer=renderer)
collector = markdown(text)
wb = Workbook()
ws = wb.active
collector.render(ws)
wb.save(<output path>)
"""
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.links = IndexedList()
def placeholder(self):
return Collector(self)
# ==== Block elements ====
def _get_block(self, bucket):
if bucket.inline is not None:
return BlockCollector(bucket.inline)
else:
return bucket.block
def block_code(self, code, language=None):
#print("block_code: ", code, language)
return self._get_block(code).nest("code")
def block_quote(self, text):
#print("block_quote: ", text)
return self._get_block(text).nest("quote")
def block_html(self, html):
#print("block_html: ", html)
return None
def header(self, text, level, raw=None):
#print("header: ", text, level, raw)
return self._get_block(text).nest("h" + str(level))
def hrule(self):
#print("hrule: ")
return BlockCollector(InlineCollector("")).nest("hrule")
def list(self, body, ordered=True):
#print("list: ", body, ordered)
return self._get_block(body).nest("list")
def list_item(self, text):
#print("list_item: ", text)
return self._get_block(text).nest("list_item")
def paragraph(self, text):
#print("paragraph: ", text)
return self._get_block(text).nest("paragraph")
def table(self, header, body):
#print("table: ", header, body)
return None
def table_row(self, content):
#print("table_row", content)
return None
def table_cell(self, content, **flags):
#print("table_cell: ", content, flags)
return None
# ==== Inline elements ====
def autolink(self, link, is_email=False):
#print("autolink: ", link, is_email)
return link.inline.apply_marker("l")
def codespan(self, text):
#print("codespan: ", text)
return InlineCollector(text).apply_marker("c")
def double_emphasis(self, text):
return text.inline.apply_marker("i").apply_marker("b")
def emphasis(self, text):
#print("emphasis: ", text)
return text.inline.apply_marker("i")
def image(self, src, title, alt_text):
return None
def linebreak(self):
return InlineCollector("\n")
def newline(self):
return None
def link(self, link, title, content):
#print("link: ", link, title, content)
self.links.add(link)
idx = self.links.index(link) + 1
return content.inline.apply_marker("l").append("[{:d}]".format(idx))
def strikethrough(self, text):
#print("strikethrough: ", text)
return text.inline.apply_marker("s")
def text(self, text):
#print("text: ", text)
return InlineCollector(text)
def inline_html(self, text):
return text.inline
def terminate(self):
"""
Called by the collector when the render function is called
"""
collector = Collector(self)
if len(self.links) > 0:
# Blank line
collector += BlockCollector(InlineCollector(""))
# Each link in a special cell with format
# "[<idx>] <link>"
for idx, link in enumerate(self.links, start=1):
content = InlineCollector("[{:d}] ".format(idx))
content += InlineCollector(link).apply_marker("l")
block = BlockCollector(content).nest("link")
collector += block
# Reset links
self.links = IndexedList()
return collector
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 openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.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.border]
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)
class SharedStylesParser(object):
def __init__(self, xml_source):
self.root = fromstring(xml_source)
self.cell_styles = IndexedList()
self.differential_styles = []
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.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()
self.parse_named_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.differential_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
"""
node = self.root.find("{%s}cellStyleXfs" % SHEET_MAIN_NS)
styles = self._parse_xfs(node)
names = self._parse_style_names()
for style in names.values():
_id = styles[style.xfId]
style.border = self.border_list[_id.borderId]
style.fill = self.fill_list[_id.fillId]
style.font = self.font_list[_id.fontId]
if _id.alignmentId:
style.alignment = self.alignments[_id.alignmentId]
if _id.protectionId:
style.protection = self.protections[_id.protectionId]
self.named_styles = names
def _parse_style_names(self):
"""
Extract style names. There can be duplicates in which case last wins
"""
node = self.root.find("{%s}cellStyles" % SHEET_MAIN_NS)
names = {}
for _name in safe_iterator(node, '{%s}cellStyle' % SHEET_MAIN_NS):
name = _name.get("name")
style = NamedStyle(name=name,
builtinId=_name.get("builtinId"),
hidden=_name.get("hidden")
)
style.xfId = int(_name.get("xfId"))
names[name] = style
return names
def parse_cell_styles(self):
"""
Extract individual cell styles
"""
node = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
if node is not None:
self.cell_styles = self._parse_xfs(node)
def _parse_xfs(self, node):
"""Read styles from the shared style table"""
_style_ids = []
xfs = safe_iterator(node, '{%s}xf' % SHEET_MAIN_NS)
for xf in xfs:
style = StyleArray.from_tree(xf)
al = xf.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = Alignment(**al.attrib)
style.alignmentId = self.alignments.add(alignment)
prot = xf.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection = Protection(**prot.attrib)
style.protectionId = self.protections.add(protection)
numFmtId = int(xf.get("numFmtId", 0))
# check for custom formats and normalise indices
if numFmtId in self.custom_number_formats:
format_code = self.custom_number_formats[numFmtId]
style.numFmtId = self.number_formats.add(format_code) + 164
_style_ids.append(style)
return IndexedList(_style_ids)
class Workbook(object):
"""Workbook is the container for all other parts of the document."""
def __init__(self,
optimized_write=False,
encoding='utf-8',
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.security = DocumentSecurity()
self.__write_only = write_only or optimized_write
self.__read_only = read_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = []
self._guess_types = guess_types
self.data_only = data_only
self._drawings = []
self._charts = []
self._images = []
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = encoding
if not self.write_only:
self.worksheets.append(Worksheet(parent_workbook=self))
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.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()])
self._named_styles = {'Normal': NamedStyle(font=DEFAULT_FONT)}
@property
def read_only(self):
return self.__read_only
@property
def write_only(self):
return self.__write_only
@deprecated("Use the .active property")
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 = WriteOnlyWorksheet(parent_workbook=self, title=title)
else:
new_ws = Worksheet(parent_workbook=self, title=title)
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)."""
cls = Worksheet
if self.write_only:
cls = WriteOnlyWorksheet
if not isinstance(worksheet, cls):
raise TypeError("The parameter you have given is not of the type '%s'" % cls.__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"""
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)