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)
def __init__(self, parent_workbook):
Worksheet.__init__(self, parent_workbook)
self._max_col = 0
self._max_row = 0
self._parent = parent_workbook
self._fileobj_header = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.header', delete=False)
self._fileobj_content = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.content', delete=False)
self._fileobj = NamedTemporaryFile(mode='w', prefix='openpyxl.', delete=False)
self.doc = XMLGenerator(self._fileobj_content, 'utf-8')
self.header = XMLGenerator(self._fileobj_header, 'utf-8')
self.title = 'Sheet'
self._shared_date = SharedDate()
self._string_builder = self._parent.strings_table_builder
def __init__(self, parent_workbook, title, sheet_codename, xml_source,
string_table, style_table):
Worksheet.__init__(self, parent_workbook, title)
self._sheet_codename = sheet_codename
self._string_table = string_table
self._style_table = style_table
min_col, min_row, max_col, max_row = read_dimension(
xml_source=self.xml_source)
self.min_col = min_col
self.min_row = min_row
self.max_row = max_row
self.max_col = max_col
self._shared_date = SharedDate(
base_date=parent_workbook.excel_base_date)
def string_to_date_with_xls_validation(cls, date_str):
date_obj = datetime.strptime(date_str, '%Y-%m-%d').date()
try:
SharedDate().datetime_to_julian(date_obj)
except ValueError:
return date_str
else:
return date_obj
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)
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, parent_workbook, title, sheet_codename, xml_source,
string_table, style_table):
Worksheet.__init__(self, parent_workbook, title)
self._sheet_codename = sheet_codename
self._string_table = string_table
self._style_table = style_table
min_col, min_row, max_col, max_row = read_dimension(xml_source=self.xml_source)
self.min_col = min_col
self.min_row = min_row
self.max_row = max_row
self.max_col = max_col
self._shared_date = SharedDate(base_date=parent_workbook.excel_base_date)
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")
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)
@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)):
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 value.strip() in self.ERROR_CODES:
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)):
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)
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)
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 check_error(self, value):
"""Tries to convert Error" else N/A"""
try:
return unicode(value)
except:
return unicode('#N/A')
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,
self.TYPE_ERROR: self.check_error
}
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)
ARC_APP, ARC_STYLE)
from openpyxl.shared.compat import iterparse
from zipfile import ZipFile
import openpyxl.cell
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',
def _get_value(self):
"""Return the value, formatted as a date if needed"""
value = self._value
if self.is_date():
value = SharedDate().from_julian(value)
return value
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
@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 = 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 = 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 = 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 = 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):
dtype = 'datetime'
cell = self._shared_date.datetime_to_julian(cell)
attributes['s'] = STYLES[dtype]['style']
elif isinstance(cell, datetime.date):
dtype = 'date'
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 IterableWorksheet(Worksheet):
def __init__(self, parent_workbook, title, workbook_name,
sheet_codename, xml_source, string_table):
Worksheet.__init__(self, parent_workbook, title)
self.archive = zipfile.ZipFile(workbook_name, 'r')
self._workbook_name = workbook_name
self._sheet_codename = sheet_codename
self._string_table = string_table
min_col, min_row, max_col, max_row = read_dimension(xml_source=self.xml_source)
self.min_col = min_col
self.min_row = min_row
self.max_row = max_row
self.max_col = max_col
self._shared_date = SharedDate(base_date=parent_workbook.excel_base_date)
@property
def xml_source(self):
worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, self._sheet_codename)
return self.archive.open(worksheet_path)
@xml_source.setter
def xml_source(self, value):
"""Base class is always supplied XML source, IteratableWorksheet obtains it on demand."""
pass
def __getitem__(self, key):
if isinstance(key, slice):
key = "{0}:{1}".format(key)
return self.iter_rows(key)
def iter_rows(self, range_string='', row_offset=0, column_offset=1):
""" Returns a squared range based on the `range_string` parameter,
using generators.
:param range_string: range of cells (e.g. 'A1:C4')
:type range_string: string
:param row_offset: additional rows (e.g. 4)
:type row: int
:param column_offset: additonal columns (e.g. 3)
:type column: int
:rtype: generator
"""
if range_string:
min_col, min_row, max_col, max_row = get_range_boundaries(range_string, row_offset, column_offset)
else:
min_col = column_index_from_string(self.min_col)
max_col = column_index_from_string(self.max_col) + 1
min_row = self.min_row
max_row = self.max_row + 6
return self.get_squared_range(min_col, min_row, max_col, max_row)
def get_squared_range(self, min_col, min_row, max_col, max_row):
expected_columns = [get_column_letter(ci) for ci in xrange(min_col, max_col)]
current_row = min_row
style_properties = read_style_table(self.archive.read(ARC_STYLE))
style_table = style_properties.pop('table')
for row, cells in groupby(self.get_cells(min_row, min_col,
max_row, max_col),
operator.attrgetter('row')):
full_row = []
if current_row < row:
for gap_row in xrange(current_row, row):
dummy_cells = get_missing_cells(gap_row, expected_columns)
yield tuple([dummy_cells[column] for column in expected_columns])
current_row = row
temp_cells = list(cells)
retrieved_columns = dict([(c.column, c) for c in temp_cells])
missing_columns = list(set(expected_columns) - set(retrieved_columns.keys()))
replacement_columns = get_missing_cells(row, missing_columns)
for column in expected_columns:
if column in retrieved_columns:
cell = retrieved_columns[column]
if cell.style_id is not None:
style = style_table[int(cell.style_id)]
cell = cell._replace(number_format=style.number_format.format_code) #pylint: disable-msg=W0212
if cell.internal_value is not None:
if cell.data_type in Cell.TYPE_STRING:
cell = cell._replace(internal_value=unicode(self._string_table[int(cell.internal_value)])) #pylint: disable-msg=W0212
elif cell.data_type == Cell.TYPE_BOOL:
cell = cell._replace(internal_value=cell.internal_value == '1')
elif cell.is_date:
cell = cell._replace(internal_value=self._shared_date.from_julian(float(cell.internal_value)))
elif cell.data_type == Cell.TYPE_NUMERIC:
cell = cell._replace(internal_value=float(cell.internal_value))
elif cell.data_type in(Cell.TYPE_INLINE, Cell.TYPE_FORMULA_CACHE_STRING):
cell = cell._replace(internal_value=unicode(cell.internal_value))
full_row.append(cell)
else:
full_row.append(replacement_columns[column])
current_row = row + 1
yield tuple(full_row)
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source)
for _event, element in p:
if element.tag == '{%s}c' % SHEET_MAIN_NS:
coord = element.get('r')
column_str, row = RE_COORDINATE.match(coord).groups()
row = int(row)
column = column_index_from_string(column_str)
if min_col <= column <= max_col and min_row <= row <= max_row:
data_type = element.get('t', 'n')
style_id = element.get('s')
formula = element.findtext('{%s}f' % SHEET_MAIN_NS)
value = element.findtext('{%s}v' % SHEET_MAIN_NS)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=" + formula
yield RawCell(row, column_str, coord, value, data_type, style_id, None)
# sub-elements of cells should be skipped
if (element.tag == '{%s}v' % SHEET_MAIN_NS
or element.tag == '{%s}f' % SHEET_MAIN_NS):
continue
element.clear()
def cell(self, *args, **kwargs):
raise NotImplementedError("use 'iter_rows()' instead")
def range(self, *args, **kwargs):
raise NotImplementedError("use 'iter_rows()' instead")
def calculate_dimension(self):
return '%s%s:%s%s' % (self.min_col, self.min_row, self.max_col, self.max_row)
def get_highest_column(self):
return column_index_from_string(self.max_col)
def get_highest_row(self):
return self.max_row
class IterableWorksheet(Worksheet):
def __init__(self, parent_workbook, title, sheet_codename, xml_source,
string_table, style_table):
Worksheet.__init__(self, parent_workbook, title)
self._sheet_codename = sheet_codename
self._string_table = string_table
self._style_table = style_table
min_col, min_row, max_col, max_row = read_dimension(
xml_source=self.xml_source)
self.min_col = min_col
self.min_row = min_row
self.max_row = max_row
self.max_col = max_col
self._shared_date = SharedDate(
base_date=parent_workbook.excel_base_date)
@property
def xml_source(self):
worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, self._sheet_codename)
return self.parent._archive.open(worksheet_path)
@xml_source.setter
def xml_source(self, value):
"""Base class is always supplied XML source, IteratableWorksheet obtains it on demand."""
pass
def __getitem__(self, key):
if isinstance(key, slice):
key = "{0}:{1}".format(key)
return self.iter_rows(key)
def iter_rows(self, range_string='', row_offset=0, column_offset=1):
""" Returns a squared range based on the `range_string` parameter,
using generators.
:param range_string: range of cells (e.g. 'A1:C4')
:type range_string: string
:param row_offset: additional rows (e.g. 4)
:type row: int
:param column_offset: additonal columns (e.g. 3)
:type column: int
:rtype: generator
"""
if range_string:
min_col, min_row, max_col, max_row = get_range_boundaries(
range_string, row_offset, column_offset)
else:
min_col = column_index_from_string(self.min_col)
max_col = column_index_from_string(self.max_col) + 1
min_row = self.min_row
max_row = self.max_row + 6
return self.get_squared_range(min_col, min_row, max_col, max_row)
def get_squared_range(self, min_col, min_row, max_col, max_row):
expected_columns = [
get_column_letter(ci) for ci in xrange(min_col, max_col)
]
current_row = min_row
style_table = self._style_table
for row, cells in groupby(
self.get_cells(min_row, min_col, max_row, max_col),
operator.attrgetter('row')):
full_row = []
if current_row < row:
for gap_row in xrange(current_row, row):
dummy_cells = get_missing_cells(gap_row, expected_columns)
yield tuple(
[dummy_cells[column] for column in expected_columns])
current_row = row
temp_cells = list(cells)
retrieved_columns = dict([(c.column, c) for c in temp_cells])
missing_columns = list(
set(expected_columns) - set(retrieved_columns.keys()))
replacement_columns = get_missing_cells(row, missing_columns)
for column in expected_columns:
if column in retrieved_columns:
cell = retrieved_columns[column]
if cell.style_id is not None:
style = style_table[int(cell.style_id)]
cell = cell._replace(
number_format=style.number_format.format_code
) #pylint: disable-msg=W0212
if cell.internal_value is not None:
if cell.data_type in Cell.TYPE_STRING:
cell = cell._replace(internal_value=unicode(
self._string_table[int(cell.internal_value)])
) #pylint: disable-msg=W0212
elif cell.data_type == Cell.TYPE_BOOL:
cell = cell._replace(
internal_value=cell.internal_value == '1')
elif cell.is_date:
cell = cell._replace(
internal_value=self._shared_date.from_julian(
float(cell.internal_value)))
elif cell.data_type == Cell.TYPE_NUMERIC:
cell = cell._replace(
internal_value=float(cell.internal_value))
elif cell.data_type in (
Cell.TYPE_INLINE,
Cell.TYPE_FORMULA_CACHE_STRING):
cell = cell._replace(
internal_value=unicode(cell.internal_value))
full_row.append(cell)
else:
full_row.append(replacement_columns[column])
current_row = row + 1
yield tuple(full_row)
def get_cells(self, min_row, min_col, max_row, max_col):
p = iterparse(self.xml_source)
for _event, element in p:
if element.tag == '{%s}c' % SHEET_MAIN_NS:
coord = element.get('r')
column_str, row = RE_COORDINATE.match(coord).groups()
row = int(row)
column = column_index_from_string(column_str)
if min_col <= column <= max_col and min_row <= row <= max_row:
data_type = element.get('t', 'n')
style_id = element.get('s')
formula = element.findtext('{%s}f' % SHEET_MAIN_NS)
value = element.findtext('{%s}v' % SHEET_MAIN_NS)
if formula is not None and not self.parent.data_only:
data_type = Cell.TYPE_FORMULA
value = "=" + formula
if not (value or formula or style_id):
# this cell is pointless and should not have been
# written in the first place
continue
yield RawCell(row, column_str, coord, value, data_type,
style_id, None)
# sub-elements of cells should be skipped
if (element.tag == '{%s}v' % SHEET_MAIN_NS
or element.tag == '{%s}f' % SHEET_MAIN_NS):
continue
element.clear()
def cell(self, *args, **kwargs):
raise NotImplementedError("use 'iter_rows()' instead")
def range(self, *args, **kwargs):
raise NotImplementedError("use 'iter_rows()' instead")
def calculate_dimension(self):
return '%s%s:%s%s' % (self.min_col, self.min_row, self.max_col,
self.max_row)
def get_highest_column(self):
return column_index_from_string(self.max_col)
def get_highest_row(self):
return self.max_row
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):
Worksheet.__init__(self, parent_workbook)
self._max_col = 0
self._max_row = 0
self._parent = parent_workbook
self._fileobj_header = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.header', delete=False)
self._fileobj_content = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.content', delete=False)
self._fileobj = NamedTemporaryFile(mode='w', prefix='openpyxl.', delete=False)
self.doc = XMLGenerator(self._fileobj_content, 'utf-8')
self.header = XMLGenerator(self._fileobj_header, 'utf-8')
self.title = 'Sheet'
self._shared_date = SharedDate()
self._string_builder = self._parent.strings_table_builder
@property
def filename(self):
return self._fileobj.name
def write_header(self):
doc = self.header
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._close_header()
self._write_fileobj(self._fileobj_header)
self._write_fileobj(self._fileobj_content)
self._fileobj.close()
def _write_fileobj(self, fobj):
fobj.flush()
fobj.seek(0)
while True:
chunk = fobj.read(4096)
if not chunk:
break
self._fileobj.write(chunk)
fobj.close()
os.remove(fobj.name)
self._fileobj.flush()
def _close_header(self):
doc = self.header
#doc.endDocument()
def _close_content(self):
doc = self.doc
end_tag(doc, 'sheetData')
end_tag(doc, 'worksheet')
#doc.endDocument()
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 append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
doc = self.doc
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
@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 = 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 = 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 = 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 = 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')
def setup_class(cls):
cls.workbook = Workbook()
cls.worksheet = Worksheet(cls.workbook, 'Test')
cls.sd = SharedDate()
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):
Worksheet.__init__(self, parent_workbook)
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.title = "Sheet"
self._shared_date = SharedDate()
self._string_builder = self._parent.strings_table_builder
@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 = 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 = 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 = 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 = 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")
