from __future__ import absolute_import
# Copyright (c) 2010-2019 openpyxl
"""Write worksheets to xml representations in an optimized way"""
import atexit
from inspect import isgenerator
import os
from tempfile import NamedTemporaryFile
from openpyxl.cell import Cell, WriteOnlyCell
from openpyxl.drawing.spreadsheet_drawing import SpreadsheetDrawing
from openpyxl.workbook.child import _WorkbookChild
from .worksheet import Worksheet
from .related import Related
from openpyxl.utils.exceptions import WorkbookAlreadySaved
from openpyxl.writer.etree_worksheet import write_cell
from openpyxl.writer.worksheet import write_drawing, write_conditional_formatting
from openpyxl.xml.constants import SHEET_MAIN_NS
from openpyxl.xml.functions import xmlfile
ALL_TEMP_FILES = []
@atexit.register
def _openpyxl_shutdown():
ALL_TEMP_FILES
for path in ALL_TEMP_FILES:
if os.path.exists(path):
os.remove(path)
[docs]def create_temporary_file(suffix=''):
fobj = NamedTemporaryFile(mode='w+', suffix=suffix,
prefix='openpyxl.', delete=False)
filename = fobj.name
fobj.close()
ALL_TEMP_FILES.append(filename)
return filename
[docs]class WriteOnlyWorksheet(_WorkbookChild):
"""
Streaming worksheet. Optimised to reduce memory by writing rows just in
time.
Cells can be styled and have comments Styles for rows and columns
must be applied before writing cells
"""
__saved = False
writer = None
_rel_type = Worksheet._rel_type
_path = Worksheet._path
mime_type = Worksheet.mime_type
def __init__(self, parent, title):
super(WriteOnlyWorksheet, self).__init__(parent, title)
self._max_col = 0
self._max_row = 0
self._fileobj_name = create_temporary_file()
# Methods from Worksheet
self._add_row = Worksheet._add_row.__get__(self)
self._add_column = Worksheet._add_column.__get__(self)
self.add_chart = Worksheet.add_chart.__get__(self)
self.add_image = Worksheet.add_image.__get__(self)
self.add_table = Worksheet.add_table.__get__(self)
setup = Worksheet._setup.__get__(self)
setup()
self.print_titles = Worksheet.print_titles.__get__(self)
self.sheet_view = Worksheet.sheet_view.__get__(self)
@property
def freeze_panes(self):
return Worksheet.freeze_panes.__get__(self)
@freeze_panes.setter
def freeze_panes(self, value):
Worksheet.freeze_panes.__set__(self, value)
@property
def print_title_cols(self):
return Worksheet.print_title_cols.__get__(self)
@print_title_cols.setter
def print_title_cols(self, value):
Worksheet.print_title_cols.__set__(self, value)
@property
def print_title_rows(self):
return Worksheet.print_title_rows.__get__(self)
@print_title_rows.setter
def print_title_rows(self, value):
Worksheet.print_title_rows.__set__(self, value)
@property
def print_area(self):
return Worksheet.print_area.__get__(self)
@print_area.setter
def print_area(self, value):
Worksheet.print_area.__set__(self, value)
@property
def filename(self):
return self._fileobj_name
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.conditional_formatting:
cfs = write_conditional_formatting(self)
for cf in cfs:
xf.write(cf)
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
if bool(self.HeaderFooter):
xf.write(self.HeaderFooter.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
[docs] def close(self):
if self.__saved:
self._already_saved()
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
self.writer.close()
self.__saved = True
def _cleanup(self):
os.remove(self.filename)
ALL_TEMP_FILES.remove(self.filename
)
[docs] def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isgenerator(row) and
not isinstance(row, (list, tuple, range))
):
self._invalid_row(row)
self._max_row += 1
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
try:
self.writer.send(row)
except StopIteration:
self._already_saved()
def _already_saved(self):
raise WorkbookAlreadySaved('Workbook has already been saved and cannot be modified or saved anymore.')
def _invalid_row(self, iterable):
raise TypeError('Value must be a list, tuple, range or a generator Supplied value is {0}'.format(
type(iterable))
)
def _write(self):
self._drawing = SpreadsheetDrawing()
self._drawing.charts = self._charts
self._drawing.images = self._images
if not self.__saved:
self.close()
with open(self.filename) as src:
out = src.read()
self._cleanup()
return out