def apply_stylesheet(archive, wb):
"""
Add styles to workbook if present
"""
try:
src = archive.read(ARC_STYLE)
except KeyError:
return wb
node = fromstring(src)
stylesheet = Stylesheet.from_tree(node)
wb._borders = IndexedList(stylesheet.borders)
wb._fonts = IndexedList(stylesheet.fonts)
wb._fills = IndexedList(stylesheet.fills)
wb._differential_styles.styles = stylesheet.dxfs
wb._number_formats = stylesheet.number_formats
wb._protections = stylesheet.protections
wb._alignments = stylesheet.alignments
wb._table_styles = stylesheet.tableStyles
# need to overwrite openpyxl defaults in case workbook has different ones
wb._cell_styles = stylesheet.cell_styles
wb._named_styles = stylesheet.named_styles
for ns in wb._named_styles:
ns.bind(wb)
if stylesheet.colors is not None:
wb._colors = stylesheet.colors.index
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 = []
def __init__(self):
self.shared_strings = IndexedList()
self._cell_styles = IndexedList(
[StyleArray([0, 0, 0, 0, 0, 0, 0, 0, 0])])
self._number_formats = IndexedList()
self.encoding = "UTF-8"
self.excel_base_date = CALENDAR_WINDOWS_1900
self.sheetnames = []
class DummyWorkbook(object):
shared_styles = IndexedList()
style = StyleArray()
shared_styles.add(style) # Workbooks always have a default style
_cell_styles = IndexedList()
_cell_styles.add(style)
_number_formats = IndexedList()
_fonts = IndexedList()
_fonts.add(None)
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 _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 __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.excel_base_date = 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 __set__(self, instance, seq):
if not isinstance(seq, self.seq_types):
raise TypeError("Value must be a sequence")
seq = [_convert(self.expected_type, value) for value in seq]
if self.unique:
seq = IndexedList(seq)
super(Sequence, self).__set__(instance, seq)
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 = []
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 Wb(object):
excel_base_date = CALENDAR_WINDOWS_1900
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_protections = IndexedList()
_alignments = IndexedList()
_number_formats = IndexedList()
_cell_styles = IndexedList()
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)
def read_string_table(xml_source):
"""Read in all shared strings in the table"""
strings = []
src = _get_xml_iter(xml_source)
for _, node in iterparse(src):
if node.tag == '{%s}si' % SHEET_MAIN_NS:
text = Text.from_tree(node).content
text = text.replace('x005F_', '')
strings.append(text)
node.clear()
return IndexedList(strings)
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=DEFAULT_FONT, builtinId=0))
self._table_styles = TableStyleList()
class DummyWorkbook:
_fonts = IndexedList()
_fills = IndexedList()
_borders = IndexedList()
_protections = IndexedList()
_alignments = IndexedList()
_number_formats = IndexedList()
_named_styles = NamedStyleList()
def add_named_style(self, style):
self._named_styles.append(style)
style.bind(self)
def __init__(self,
numFmts=None,
fonts=(),
fills=(),
borders=(),
cellStyleXfs=None,
cellXfs=None,
cellStyles=None,
dxfs=(),
tableStyles=None,
colors=None,
extLst=None,
):
if numFmts is None:
numFmts = NumberFormatList()
self.numFmts = numFmts
self.number_formats = IndexedList()
self.fonts = fonts
self.fills = fills
self.borders = borders
if cellStyleXfs is None:
cellStyleXfs = CellStyleList()
self.cellStyleXfs = cellStyleXfs
if cellXfs is None:
cellXfs = CellStyleList()
self.cellXfs = cellXfs
if cellStyles is None:
cellStyles = _NamedCellStyleList()
self.cellStyles = cellStyles
self.dxfs = dxfs
self.tableStyles = tableStyles
self.colors = colors
self.cell_styles = self.cellXfs._to_array()
self.alignments = self.cellXfs.alignments
self.protections = self.cellXfs.prots
self._normalise_numbers()
self.named_styles = self._merge_named_styles()
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,
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.excel_base_date = 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=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 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
: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"""
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 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:`openpyxl25.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()