def __init__(self, parent_workbook, title): Worksheet.__init__(self, parent_workbook, title) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_header_name = create_temporary_file(suffix='.header') self._fileobj_content_name = create_temporary_file(suffix='.content') self._fileobj_name = create_temporary_file() self._shared_date = SharedDate() self._string_builder = self._parent.strings_table_builder
def __init__(self, worksheet, column, row, value=None): self.column = column.upper() self.row = row # _value is the stored value, while value is the displayed value self._value = None self._hyperlink_rel = None self._data_type = self.TYPE_NULL if value: self.value = value self.parent = worksheet self.xf_index = 0 self._shared_date = SharedDate( base_date=worksheet.parent.excel_base_date) self.merged = False
def bind_value(self, value): """Given a value, infer type and display options.""" self._data_type = self.data_type_for_value(value) if value is None: self.set_value_explicit('', self.TYPE_NULL) return True elif self._data_type == self.TYPE_STRING: # percentage detection if isinstance(value, unicode): percentage_search = self.RE_PATTERNS['percentage'].match(value) else: percentage_search = self.RE_PATTERNS['percentage'].match( str(value)) if percentage_search and value.strip() != '%': value = float(value.replace('%', '')) / 100.0 self.set_value_explicit(value, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_PERCENTAGE) return True # time detection if isinstance(value, unicode): time_search = self.RE_PATTERNS['time'].match(value) else: time_search = self.RE_PATTERNS['time'].match(str(value)) if time_search: sep_count = value.count(':') # pylint: disable=E1103 if sep_count == 1: hours, minutes = [int(bit) for bit in value.split(':')] # pylint: disable=E1103 seconds = 0 elif sep_count == 2: hours, minutes, seconds = \ [int(bit) for bit in value.split(':')] # pylint: disable=E1103 days = (hours / 24.0) + (minutes / 1440.0) + \ (seconds / 86400.0) self.set_value_explicit(days, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_DATE_TIME3) return True if self._data_type == self.TYPE_NUMERIC: # date detection # if the value is a date, but not a date time, make it a # datetime, and set the time part to 0 if isinstance(value, datetime.date) and not \ isinstance(value, datetime.datetime): value = datetime.datetime.combine(value, datetime.time()) if isinstance( value, (datetime.datetime, datetime.time, datetime.timedelta)): if isinstance(value, datetime.datetime): self._set_number_format(NumberFormat.FORMAT_DATE_YYYYMMDD2) elif isinstance(value, datetime.time): self._set_number_format(NumberFormat.FORMAT_DATE_TIME6) elif isinstance(value, datetime.timedelta): self._set_number_format(NumberFormat.FORMAT_DATE_TIMEDELTA) value = SharedDate().datetime_to_julian(date=value) self.set_value_explicit(value, self.TYPE_NUMERIC) return True self.set_value_explicit(value, self._data_type)
def __init__(self, worksheet, column, row, value=None): self.column = column.upper() self.row = row # _value is the stored value, while value is the displayed value self._value = None self._hyperlink_rel = None self._data_type = self.TYPE_NULL if value: self.value = value self.parent = worksheet self.xf_index = 0 self._shared_date = SharedDate(base_date=worksheet.parent.excel_base_date) self.merged = False
def __init__(self, parent_workbook, title, workbook_name, sheet_codename, xml_source, string_table): Worksheet.__init__(self, parent_workbook, title) self._workbook_name = workbook_name self._sheet_codename = sheet_codename self._xml_source = xml_source self._string_table = string_table min_col, min_row, max_col, max_row = read_dimension( xml_source=xml_source) self._max_row = max_row self._max_column = max_col self._dimensions = '%s%s:%s%s' % (min_col, min_row, max_col, max_row) self._shared_date = SharedDate( base_date=parent_workbook.excel_base_date)
class Cell(object): """Describes cell associated properties. Properties of interest include style, type, value, and address. """ __slots__ = ('column', 'row', '_value', '_data_type', 'parent', 'xf_index', '_hyperlink_rel', '_shared_date', 'merged') ERROR_CODES = { '#NULL!': 0, '#DIV/0!': 1, '#VALUE!': 2, '#REF!': 3, '#NAME?': 4, '#NUM!': 5, '#N/A': 6 } TYPE_STRING = 's' TYPE_FORMULA = 'f' TYPE_NUMERIC = 'n' TYPE_BOOL = 'b' TYPE_NULL = 's' TYPE_INLINE = 'inlineStr' TYPE_ERROR = 'e' TYPE_FORMULA_CACHE_STRING = 'str' VALID_TYPES = [ TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_INLINE, TYPE_ERROR, TYPE_FORMULA_CACHE_STRING ] RE_PATTERNS = { 'percentage': re.compile(r'^\-?[0-9]*\.?[0-9]*\s?\%$'), 'time': re.compile(r'^(\d|[0-1]\d|2[0-3]):[0-5]\d(:[0-5]\d)?$'), 'numeric': re.compile( r'^-?([\d]|[\d]+\.[\d]*|\.[\d]+|[1-9][\d]+\.?[\d]*)((E|e)-?[\d]+)?$' ), } def __init__(self, worksheet, column, row, value=None): self.column = column.upper() self.row = row # _value is the stored value, while value is the displayed value self._value = None self._hyperlink_rel = None self._data_type = self.TYPE_NULL if value: self.value = value self.parent = worksheet self.xf_index = 0 self._shared_date = SharedDate( base_date=worksheet.parent.excel_base_date) self.merged = False @property def encoding(self): return self.parent.encoding def __repr__(self): return unicode("<Cell %s.%s>") % (self.parent.title, self.get_coordinate()) def check_string(self, value): """Check string coding, length, and line break character""" # convert to unicode string if not isinstance(value, unicode): value = unicode(value, self.encoding) value = unicode(value) # string must never be longer than 32,767 characters # truncate if necessary value = value[:32767] # we require that newline is represented as "\n" in core, # not as "\r\n" or "\r" value = value.replace('\r\n', '\n') return value def check_numeric(self, value): """Cast value to int or float if necessary""" if not isinstance(value, NUMERIC_TYPES): try: value = int(value) except ValueError: value = float(value) return value def set_value_explicit(self, value=None, data_type=TYPE_STRING): """Coerce values according to their explicit type""" type_coercion_map = { self.TYPE_INLINE: self.check_string, self.TYPE_STRING: self.check_string, self.TYPE_FORMULA: self.check_string, self.TYPE_NUMERIC: self.check_numeric, self.TYPE_BOOL: bool, } try: self._value = type_coercion_map[data_type](value) except KeyError: if data_type not in self.VALID_TYPES: msg = 'Invalid data type: %s' % data_type raise DataTypeException(msg) self._data_type = data_type def data_type_for_value(self, value): """Given a value, infer the correct data type""" if value is None: data_type = self.TYPE_NULL elif value is True or value is False: data_type = self.TYPE_BOOL elif isinstance(value, NUMERIC_TYPES): data_type = self.TYPE_NUMERIC elif isinstance(value, (datetime.datetime, datetime.date, datetime.time, datetime.timedelta)): data_type = self.TYPE_NUMERIC elif not value: data_type = self.TYPE_STRING elif isinstance(value, basestring) and value[0] == '=': data_type = self.TYPE_FORMULA elif isinstance(value, unicode) and self.RE_PATTERNS['numeric'].match(value): data_type = self.TYPE_NUMERIC elif not isinstance(value, unicode) and self.RE_PATTERNS['numeric'].match( str(value)): data_type = self.TYPE_NUMERIC elif isinstance(value, basestring) and value.strip() in self.ERROR_CODES: data_type = self.TYPE_ERROR elif isinstance(value, list): data_type = self.TYPE_ERROR else: data_type = self.TYPE_STRING return data_type def bind_value(self, value): """Given a value, infer type and display options.""" self._data_type = self.data_type_for_value(value) if value is None: self.set_value_explicit('', self.TYPE_NULL) return True elif self._data_type == self.TYPE_STRING: # percentage detection if isinstance(value, unicode): percentage_search = self.RE_PATTERNS['percentage'].match(value) else: percentage_search = self.RE_PATTERNS['percentage'].match( str(value)) if percentage_search and value.strip() != '%': value = float(value.replace('%', '')) / 100.0 self.set_value_explicit(value, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_PERCENTAGE) return True # time detection if isinstance(value, unicode): time_search = self.RE_PATTERNS['time'].match(value) else: time_search = self.RE_PATTERNS['time'].match(str(value)) if time_search: sep_count = value.count(':') # pylint: disable=E1103 if sep_count == 1: hours, minutes = [int(bit) for bit in value.split(':')] # pylint: disable=E1103 seconds = 0 elif sep_count == 2: hours, minutes, seconds = \ [int(bit) for bit in value.split(':')] # pylint: disable=E1103 days = (hours / 24.0) + (minutes / 1440.0) + \ (seconds / 86400.0) self.set_value_explicit(days, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_DATE_TIME3) return True if self._data_type == self.TYPE_NUMERIC: # date detection # if the value is a date, but not a date time, make it a # datetime, and set the time part to 0 if isinstance(value, datetime.date) and not \ isinstance(value, datetime.datetime): value = datetime.datetime.combine(value, datetime.time()) if isinstance( value, (datetime.datetime, datetime.time, datetime.timedelta)): if isinstance(value, datetime.datetime): self._set_number_format(NumberFormat.FORMAT_DATE_YYYYMMDD2) elif isinstance(value, datetime.time): self._set_number_format(NumberFormat.FORMAT_DATE_TIME6) elif isinstance(value, datetime.timedelta): self._set_number_format(NumberFormat.FORMAT_DATE_TIMEDELTA) value = SharedDate().datetime_to_julian(date=value) self.set_value_explicit(value, self.TYPE_NUMERIC) return True self.set_value_explicit(value, self._data_type) def _get_value(self): """Return the value, formatted as a date if needed""" value = self._value if self.is_date(): value = self._shared_date.from_julian(value) return value def _set_value(self, value): """Set the value and infer type and display options.""" self.bind_value(value) value = property(_get_value, _set_value, doc='Get or set the value held in the cell.\n\n' ':rtype: depends on the value (string, float, int or ' ':class:`datetime.datetime`)') def _set_hyperlink(self, val): """Set value and display for hyperlinks in a cell""" if self._hyperlink_rel is None: self._hyperlink_rel = self.parent.create_relationship("hyperlink") self._hyperlink_rel.target = val self._hyperlink_rel.target_mode = "External" if self._value is None: self.value = val def _get_hyperlink(self): """Return the hyperlink target or an empty string""" return self._hyperlink_rel is not None and \ self._hyperlink_rel.target or '' hyperlink = property( _get_hyperlink, _set_hyperlink, doc='Get or set the hyperlink held in the cell. ' 'Automatically sets the `value` of the cell with link text, ' 'but you can modify it afterwards by setting the ' '`value` property, and the hyperlink will remain.\n\n' ':rtype: string') @property def hyperlink_rel_id(self): """Return the id pointed to by the hyperlink, or None""" return self._hyperlink_rel is not None and \ self._hyperlink_rel.id or None def _set_number_format(self, format_code): """Set a new formatting code for numeric values""" self.style.number_format.format_code = format_code @property def has_style(self): """Check if the parent worksheet has a style for this cell""" return self.get_coordinate() in self.parent._styles # pylint: disable=W0212 @property def style(self): """Returns the :class:`openpyxl.style.Style` object for this cell""" return self.parent.get_style(self.get_coordinate()) @property def data_type(self): """Return the data type represented by this cell""" return self._data_type def get_coordinate(self): """Return the coordinate string for this cell (e.g. 'B12') :rtype: string """ return '%s%s' % (self.column, self.row) @property def address(self): """Return the coordinate string for this cell (e.g. 'B12') :rtype: string """ return self.get_coordinate() def offset(self, row=0, column=0): """Returns a cell location relative to this cell. :param row: number of rows to offset :type row: int :param column: number of columns to offset :type column: int :rtype: :class:`openpyxl.cell.Cell` """ offset_column = get_column_letter( column_index_from_string(column=self.column) + column) offset_row = self.row + row return self.parent.cell('%s%s' % (offset_column, offset_row)) def is_date(self): """Returns whether the value is *probably* a date or not :rtype: bool """ return (self.has_style and self.style.number_format.is_date_format() and isinstance(self._value, NUMERIC_TYPES)) @property def anchor(self): """ returns the expected position of a cell in pixels from the top-left of the sheet. For example, A1 anchor should be (0,0). :rtype: tuple(int, int) """ left_columns = (column_index_from_string(self.column, True) - 1) column_dimensions = self.parent.column_dimensions left_anchor = 0 default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH) for col_idx in range(left_columns): letter = get_column_letter(col_idx + 1) if letter in column_dimensions: cdw = column_dimensions.get(letter).width if cdw > 0: left_anchor += points_to_pixels(cdw) continue left_anchor += default_width row_dimensions = self.parent.row_dimensions top_anchor = 0 top_rows = (self.row - 1) default_height = points_to_pixels(DEFAULT_ROW_HEIGHT) for row_idx in range(1, top_rows + 1): if row_idx in row_dimensions: rdh = row_dimensions[row_idx].height if rdh > 0: top_anchor += points_to_pixels(rdh) continue top_anchor += default_height return (left_anchor, top_anchor)
PACKAGE_WORKSHEETS, MAX_ROW, MIN_ROW, ARC_STYLE) from spreadsheet.openpyxl.shared.compat import iterparse, xrange from zipfile import ZipFile import re import tempfile import zlib import zipfile import struct TYPE_NULL = Cell.TYPE_NULL MISSING_VALUE = None RE_COORDINATE = re.compile('^([A-Z]+)([0-9]+)$') SHARED_DATE = SharedDate() _COL_CONVERSION_CACHE = dict( (get_column_letter(i), i) for i in xrange(1, 18279)) def column_index_from_string(str_col, _col_conversion_cache=_COL_CONVERSION_CACHE): # we use a function argument to get indexed name lookup return _col_conversion_cache[str_col] del _COL_CONVERSION_CACHE RAW_ATTRIBUTES = [ 'row', 'column', 'coordinate', 'internal_value', 'data_type', 'style_id',
class DumpWorksheet(Worksheet): """ .. warning:: You shouldn't initialize this yourself, use :class:`openpyxl.workbook.Workbook` constructor instead, with `optimized_write = True`. """ def __init__(self, parent_workbook, title): Worksheet.__init__(self, parent_workbook, title) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_header_name = create_temporary_file(suffix='.header') self._fileobj_content_name = create_temporary_file(suffix='.content') self._fileobj_name = create_temporary_file() self._shared_date = SharedDate() self._string_builder = self._parent.strings_table_builder def get_temporary_file(self, filename): if filename in self._descriptors_cache: fobj = self._descriptors_cache[filename] # re-insert the value so it does not get evicted # from cache soon del self._descriptors_cache[filename] self._descriptors_cache[filename] = fobj return fobj else: if filename is None: raise WorkbookAlreadySaved( 'this workbook has already been saved ' 'and cannot be modified or saved anymore.') fobj = open(filename, 'r+') self._descriptors_cache[filename] = fobj if len(self._descriptors_cache) > DESCRIPTORS_CACHE_SIZE: filename, fileobj = self._descriptors_cache.popitem(last=False) fileobj.close() return fobj @property def _descriptors_cache(self): try: return self._parent._local_data.cache except AttributeError: self._parent._local_data.cache = OrderedDict() return self._parent._local_data.cache @property def filename(self): return self._fileobj_name @property def _temp_files(self): return (self._fileobj_content_name, self._fileobj_header_name, self._fileobj_name) def _unset_temp_files(self): self._fileobj_header_name = None self._fileobj_content_name = None self._fileobj_name = None def write_header(self): fobj = self.get_temporary_file(filename=self._fileobj_header_name) doc = XMLGenerator(fobj, 'utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '1', 'summaryRight': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())}) start_tag(doc, 'sheetViews') start_tag(doc, 'sheetView', {'workbookViewId': '0'}) tag(doc, 'selection', {'activeCell': 'A1', 'sqref': 'A1'}) end_tag(doc, 'sheetView') end_tag(doc, 'sheetViews') tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) start_tag(doc, 'sheetData') def close(self): self._close_content() self._fileobj = self.get_temporary_file(filename=self._fileobj_name) self._write_fileobj(self._fileobj_header_name) self._write_fileobj(self._fileobj_content_name) self._fileobj.close() def _write_fileobj(self, fobj_name): fobj = self.get_temporary_file(filename=fobj_name) fobj.flush() fobj.seek(0) while True: chunk = fobj.read(4096) if not chunk: break self._fileobj.write(chunk) fobj.close() self._fileobj.flush() def _close_content(self): doc = self._get_content_generator() end_tag(doc, 'sheetData') end_tag(doc, 'worksheet') def get_dimensions(self): if not self._max_col or not self._max_row: return 'A1' else: return '%s%d' % (get_column_letter(self._max_col), (self._max_row)) def _get_content_generator(self): """ XXX: this is ugly, but it allows to resume writing the file even after the handle is closed""" # when I'll recreate the XMLGenerator, it will start writing at the # begining of the file, erasing previously entered rows, so we have # to move to the end of the file before adding new tags handle = self.get_temporary_file(filename=self._fileobj_content_name) handle.seek(0, 2) doc = XMLGenerator(out=handle) return doc def append(self, row): """ :param row: iterable containing values to append :type row: iterable """ doc = self._get_content_generator() self._max_row += 1 span = len(row) self._max_col = max(self._max_col, span) row_idx = self._max_row attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % span} start_tag(doc, 'row', attrs) for col_idx, cell in enumerate(row): if cell is None: continue coordinate = '%s%d' % (get_column_letter(col_idx + 1), row_idx) attributes = {'r': coordinate} if isinstance(cell, bool): dtype = 'boolean' elif isinstance(cell, NUMERIC_TYPES): dtype = 'numeric' elif isinstance(cell, (datetime.datetime, datetime.date)): dtype = 'datetime' cell = self._shared_date.datetime_to_julian(cell) attributes['s'] = STYLES[dtype]['style'] elif cell and cell[0] == '=': dtype = 'formula' else: dtype = 'string' cell = self._string_builder.add(cell) attributes['t'] = STYLES[dtype]['type'] start_tag(doc, 'c', attributes) if dtype == 'formula': tag(doc, 'f', body='%s' % cell[1:]) tag(doc, 'v') elif dtype == 'boolean': tag(doc, 'v', body='%d' % cell) else: tag(doc, 'v', body='%s' % cell) end_tag(doc, 'c') end_tag(doc, 'row')
class Cell(object): """Describes cell associated properties. Properties of interest include style, type, value, and address. """ __slots__ = ('column', 'row', '_value', '_data_type', 'parent', 'xf_index', '_hyperlink_rel', '_shared_date', 'merged') ERROR_CODES = {'#NULL!': 0, '#DIV/0!': 1, '#VALUE!': 2, '#REF!': 3, '#NAME?': 4, '#NUM!': 5, '#N/A': 6} TYPE_STRING = 's' TYPE_FORMULA = 'f' TYPE_NUMERIC = 'n' TYPE_BOOL = 'b' TYPE_NULL = 's' TYPE_INLINE = 'inlineStr' TYPE_ERROR = 'e' TYPE_FORMULA_CACHE_STRING = 'str' VALID_TYPES = [TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_INLINE, TYPE_ERROR, TYPE_FORMULA_CACHE_STRING] RE_PATTERNS = { 'percentage': re.compile(r'^\-?[0-9]*\.?[0-9]*\s?\%$'), 'time': re.compile(r'^(\d|[0-1]\d|2[0-3]):[0-5]\d(:[0-5]\d)?$'), 'numeric': re.compile(r'^-?([\d]|[\d]+\.[\d]*|\.[\d]+|[1-9][\d]+\.?[\d]*)((E|e)-?[\d]+)?$'), } def __init__(self, worksheet, column, row, value=None): self.column = column.upper() self.row = row # _value is the stored value, while value is the displayed value self._value = None self._hyperlink_rel = None self._data_type = self.TYPE_NULL if value: self.value = value self.parent = worksheet self.xf_index = 0 self._shared_date = SharedDate(base_date=worksheet.parent.excel_base_date) self.merged = False @property def encoding(self): return self.parent.encoding def __repr__(self): return unicode("<Cell %s.%s>") % (self.parent.title, self.get_coordinate()) def check_string(self, value): """Check string coding, length, and line break character""" # convert to unicode string if not isinstance(value, unicode): value = unicode(value, self.encoding) value = unicode(value) # string must never be longer than 32,767 characters # truncate if necessary value = value[:32767] # we require that newline is represented as "\n" in core, # not as "\r\n" or "\r" value = value.replace('\r\n', '\n') return value def check_numeric(self, value): """Cast value to int or float if necessary""" if not isinstance(value, NUMERIC_TYPES): try: value = int(value) except ValueError: value = float(value) return value def set_value_explicit(self, value=None, data_type=TYPE_STRING): """Coerce values according to their explicit type""" type_coercion_map = { self.TYPE_INLINE: self.check_string, self.TYPE_STRING: self.check_string, self.TYPE_FORMULA: self.check_string, self.TYPE_NUMERIC: self.check_numeric, self.TYPE_BOOL: bool, } try: self._value = type_coercion_map[data_type](value) except KeyError: if data_type not in self.VALID_TYPES: msg = 'Invalid data type: %s' % data_type raise DataTypeException(msg) self._data_type = data_type def data_type_for_value(self, value): """Given a value, infer the correct data type""" if value is None: data_type = self.TYPE_NULL elif value is True or value is False: data_type = self.TYPE_BOOL elif isinstance(value, NUMERIC_TYPES): data_type = self.TYPE_NUMERIC elif isinstance(value, (datetime.datetime, datetime.date, datetime.time, datetime.timedelta)): data_type = self.TYPE_NUMERIC elif not value: data_type = self.TYPE_STRING elif isinstance(value, basestring) and value[0] == '=': data_type = self.TYPE_FORMULA elif isinstance(value, unicode) and self.RE_PATTERNS['numeric'].match(value): data_type = self.TYPE_NUMERIC elif not isinstance(value, unicode) and self.RE_PATTERNS['numeric'].match(str(value)): data_type = self.TYPE_NUMERIC elif isinstance(value, basestring) and value.strip() in self.ERROR_CODES: data_type = self.TYPE_ERROR elif isinstance(value, list): data_type = self.TYPE_ERROR else: data_type = self.TYPE_STRING return data_type def bind_value(self, value): """Given a value, infer type and display options.""" self._data_type = self.data_type_for_value(value) if value is None: self.set_value_explicit('', self.TYPE_NULL) return True elif self._data_type == self.TYPE_STRING: # percentage detection if isinstance(value, unicode): percentage_search = self.RE_PATTERNS['percentage'].match(value) else: percentage_search = self.RE_PATTERNS['percentage'].match(str(value)) if percentage_search and value.strip() != '%': value = float(value.replace('%', '')) / 100.0 self.set_value_explicit(value, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_PERCENTAGE) return True # time detection if isinstance(value, unicode): time_search = self.RE_PATTERNS['time'].match(value) else: time_search = self.RE_PATTERNS['time'].match(str(value)) if time_search: sep_count = value.count(':') # pylint: disable=E1103 if sep_count == 1: hours, minutes = [int(bit) for bit in value.split(':')] # pylint: disable=E1103 seconds = 0 elif sep_count == 2: hours, minutes, seconds = \ [int(bit) for bit in value.split(':')] # pylint: disable=E1103 days = (hours / 24.0) + (minutes / 1440.0) + \ (seconds / 86400.0) self.set_value_explicit(days, self.TYPE_NUMERIC) self._set_number_format(NumberFormat.FORMAT_DATE_TIME3) return True if self._data_type == self.TYPE_NUMERIC: # date detection # if the value is a date, but not a date time, make it a # datetime, and set the time part to 0 if isinstance(value, datetime.date) and not \ isinstance(value, datetime.datetime): value = datetime.datetime.combine(value, datetime.time()) if isinstance(value, (datetime.datetime, datetime.time, datetime.timedelta)): if isinstance(value, datetime.datetime): self._set_number_format(NumberFormat.FORMAT_DATE_YYYYMMDD2) elif isinstance(value, datetime.time): self._set_number_format(NumberFormat.FORMAT_DATE_TIME6) elif isinstance(value, datetime.timedelta): self._set_number_format(NumberFormat.FORMAT_DATE_TIMEDELTA) value = SharedDate().datetime_to_julian(date=value) self.set_value_explicit(value, self.TYPE_NUMERIC) return True self.set_value_explicit(value, self._data_type) def _get_value(self): """Return the value, formatted as a date if needed""" value = self._value if self.is_date(): value = self._shared_date.from_julian(value) return value def _set_value(self, value): """Set the value and infer type and display options.""" self.bind_value(value) value = property(_get_value, _set_value, doc='Get or set the value held in the cell.\n\n' ':rtype: depends on the value (string, float, int or ' ':class:`datetime.datetime`)') def _set_hyperlink(self, val): """Set value and display for hyperlinks in a cell""" if self._hyperlink_rel is None: self._hyperlink_rel = self.parent.create_relationship("hyperlink") self._hyperlink_rel.target = val self._hyperlink_rel.target_mode = "External" if self._value is None: self.value = val def _get_hyperlink(self): """Return the hyperlink target or an empty string""" return self._hyperlink_rel is not None and \ self._hyperlink_rel.target or '' hyperlink = property(_get_hyperlink, _set_hyperlink, doc='Get or set the hyperlink held in the cell. ' 'Automatically sets the `value` of the cell with link text, ' 'but you can modify it afterwards by setting the ' '`value` property, and the hyperlink will remain.\n\n' ':rtype: string') @property def hyperlink_rel_id(self): """Return the id pointed to by the hyperlink, or None""" return self._hyperlink_rel is not None and \ self._hyperlink_rel.id or None def _set_number_format(self, format_code): """Set a new formatting code for numeric values""" self.style.number_format.format_code = format_code @property def has_style(self): """Check if the parent worksheet has a style for this cell""" return self.get_coordinate() in self.parent._styles # pylint: disable=W0212 @property def style(self): """Returns the :class:`openpyxl.style.Style` object for this cell""" return self.parent.get_style(self.get_coordinate()) @property def data_type(self): """Return the data type represented by this cell""" return self._data_type def get_coordinate(self): """Return the coordinate string for this cell (e.g. 'B12') :rtype: string """ return '%s%s' % (self.column, self.row) @property def address(self): """Return the coordinate string for this cell (e.g. 'B12') :rtype: string """ return self.get_coordinate() def offset(self, row=0, column=0): """Returns a cell location relative to this cell. :param row: number of rows to offset :type row: int :param column: number of columns to offset :type column: int :rtype: :class:`openpyxl.cell.Cell` """ offset_column = get_column_letter(column_index_from_string( column=self.column) + column) offset_row = self.row + row return self.parent.cell('%s%s' % (offset_column, offset_row)) def is_date(self): """Returns whether the value is *probably* a date or not :rtype: bool """ return (self.has_style and self.style.number_format.is_date_format() and isinstance(self._value, NUMERIC_TYPES)) @property def anchor(self): """ returns the expected position of a cell in pixels from the top-left of the sheet. For example, A1 anchor should be (0,0). :rtype: tuple(int, int) """ left_columns = (column_index_from_string(self.column, True) - 1) column_dimensions = self.parent.column_dimensions left_anchor = 0 default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH) for col_idx in range(left_columns): letter = get_column_letter(col_idx + 1) if letter in column_dimensions: cdw = column_dimensions.get(letter).width if cdw > 0: left_anchor += points_to_pixels(cdw) continue left_anchor += default_width row_dimensions = self.parent.row_dimensions top_anchor = 0 top_rows = (self.row - 1) default_height = points_to_pixels(DEFAULT_ROW_HEIGHT) for row_idx in range(1, top_rows + 1): if row_idx in row_dimensions: rdh = row_dimensions[row_idx].height if rdh > 0: top_anchor += points_to_pixels(rdh) continue top_anchor += default_height return (left_anchor, top_anchor)
class DumpWorksheet(Worksheet): """ .. warning:: You shouldn't initialize this yourself, use :class:`openpyxl.workbook.Workbook` constructor instead, with `optimized_write = True`. """ def __init__(self, parent_workbook, title): Worksheet.__init__(self, parent_workbook, title) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_header_name = create_temporary_file(suffix='.header') self._fileobj_content_name = create_temporary_file(suffix='.content') self._fileobj_name = create_temporary_file() self._shared_date = SharedDate() self._string_builder = self._parent.strings_table_builder def get_temporary_file(self, filename): if filename in self._descriptors_cache: fobj = self._descriptors_cache[filename] # re-insert the value so it does not get evicted # from cache soon del self._descriptors_cache[filename] self._descriptors_cache[filename] = fobj return fobj else: if filename is None: raise WorkbookAlreadySaved('this workbook has already been saved ' 'and cannot be modified or saved anymore.') fobj = open(filename, 'r+') self._descriptors_cache[filename] = fobj if len(self._descriptors_cache) > DESCRIPTORS_CACHE_SIZE: filename, fileobj = self._descriptors_cache.popitem(last=False) fileobj.close() return fobj @property def _descriptors_cache(self): try: return self._parent._local_data.cache except AttributeError: self._parent._local_data.cache = OrderedDict() return self._parent._local_data.cache @property def filename(self): return self._fileobj_name @property def _temp_files(self): return (self._fileobj_content_name, self._fileobj_header_name, self._fileobj_name) def _unset_temp_files(self): self._fileobj_header_name = None self._fileobj_content_name = None self._fileobj_name = None def write_header(self): fobj = self.get_temporary_file(filename=self._fileobj_header_name) doc = XMLGenerator(fobj, 'utf-8') start_tag(doc, 'worksheet', {'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'}) start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '1', 'summaryRight': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())}) start_tag(doc, 'sheetViews') start_tag(doc, 'sheetView', {'workbookViewId': '0'}) tag(doc, 'selection', {'activeCell': 'A1', 'sqref': 'A1'}) end_tag(doc, 'sheetView') end_tag(doc, 'sheetViews') tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) start_tag(doc, 'sheetData') def close(self): self._close_content() self._fileobj = self.get_temporary_file(filename=self._fileobj_name) self._write_fileobj(self._fileobj_header_name) self._write_fileobj(self._fileobj_content_name) self._fileobj.close() def _write_fileobj(self, fobj_name): fobj = self.get_temporary_file(filename=fobj_name) fobj.flush() fobj.seek(0) while True: chunk = fobj.read(4096) if not chunk: break self._fileobj.write(chunk) fobj.close() self._fileobj.flush() def _close_content(self): doc = self._get_content_generator() end_tag(doc, 'sheetData') end_tag(doc, 'worksheet') def get_dimensions(self): if not self._max_col or not self._max_row: return 'A1' else: return '%s%d' % (get_column_letter(self._max_col), (self._max_row)) def _get_content_generator(self): """ XXX: this is ugly, but it allows to resume writing the file even after the handle is closed""" # when I'll recreate the XMLGenerator, it will start writing at the # begining of the file, erasing previously entered rows, so we have # to move to the end of the file before adding new tags handle = self.get_temporary_file(filename=self._fileobj_content_name) handle.seek(0, 2) doc = XMLGenerator(out=handle) return doc def append(self, row): """ :param row: iterable containing values to append :type row: iterable """ doc = self._get_content_generator() self._max_row += 1 span = len(row) self._max_col = max(self._max_col, span) row_idx = self._max_row attrs = {'r': '%d' % row_idx, 'spans': '1:%d' % span} start_tag(doc, 'row', attrs) for col_idx, cell in enumerate(row): if cell is None: continue coordinate = '%s%d' % (get_column_letter(col_idx + 1), row_idx) attributes = {'r': coordinate} if isinstance(cell, bool): dtype = 'boolean' elif isinstance(cell, NUMERIC_TYPES): dtype = 'numeric' elif isinstance(cell, (datetime.datetime, datetime.date)): dtype = 'datetime' cell = self._shared_date.datetime_to_julian(cell) attributes['s'] = STYLES[dtype]['style'] elif cell and cell[0] == '=': dtype = 'formula' else: dtype = 'string' cell = self._string_builder.add(cell) attributes['t'] = STYLES[dtype]['type'] start_tag(doc, 'c', attributes) if dtype == 'formula': tag(doc, 'f', body='%s' % cell[1:]) tag(doc, 'v') elif dtype == 'boolean': tag(doc, 'v', body='%d' % cell) else: tag(doc, 'v', body='%s' % cell) end_tag(doc, 'c') end_tag(doc, 'row')