def string_to_date_with_xls_validation(cls, date_str):
if not isinstance(date_str, six.string_types):
return date_str
try:
date_obj = datetime.strptime(date_str, '%Y-%m-%d').date()
to_excel(date_obj)
except ValueError:
return date_str
else:
return date_obj
def _cast_datetime(self, value):
"""Convert Python datetime to Excel and set formatting"""
if isinstance(value, datetime.datetime):
value = to_excel(value, self.base_date)
self.number_format = numbers.FORMAT_DATE_DATETIME
elif isinstance(value, datetime.date):
value = to_excel(value, self.base_date)
self.number_format = numbers.FORMAT_DATE_YYYYMMDD2
elif isinstance(value, datetime.time):
value = time_to_days(value)
self.number_format = numbers.FORMAT_DATE_TIME6
elif isinstance(value, datetime.timedelta):
value = timedelta_to_days(value)
self.number_format = numbers.FORMAT_DATE_TIMEDELTA
return value
def _cast_datetime(self, value):
"""Convert Python datetime to Excel and set formatting"""
if isinstance(value, datetime.datetime):
value = to_excel(value, self.base_date)
self.number_format = numbers.FORMAT_DATE_DATETIME
elif isinstance(value, datetime.date):
value = to_excel(value, self.base_date)
self.number_format = numbers.FORMAT_DATE_YYYYMMDD2
elif isinstance(value, datetime.time):
value = time_to_days(value)
self.number_format = numbers.FORMAT_DATE_TIME6
elif isinstance(value, datetime.timedelta):
value = timedelta_to_days(value)
self.number_format = numbers.FORMAT_DATE_TIMEDELTA
return value
def string_to_date_with_xls_validation(date_str):
""" Try to convert a string to a date object.
:param date_str: string to convert
:returns: object if converted, otherwise date string
"""
if not isinstance(date_str, text):
return date_str
try:
date_obj = datetime.strptime(date_str, '%Y-%m-%d').date()
to_excel(date_obj)
except ValueError:
return date_str
else:
return date_obj
def _set_attributes(cell, styled=None):
"""
Set coordinate and datatype
"""
coordinate = cell.coordinate
attrs = {'r': coordinate}
if styled:
attrs['s'] = f"{cell.style_id}"
if cell.data_type == "s":
attrs['t'] = "inlineStr"
elif cell.data_type != 'f':
attrs['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if hasattr(value, "tzinfo") and value.tzinfo is not None:
raise TypeError(
"Excel does not support timezones in datetimes. "
"The tzinfo in the datetime/time object must be set to None.")
if cell.parent.parent.iso_dates and not isinstance(value, timedelta):
value = to_ISO8601(value)
else:
attrs['t'] = "n"
value = to_excel(value, cell.parent.parent.epoch)
if cell.hyperlink:
cell.parent._hyperlinks.append(cell.hyperlink)
return value, attrs
def value(self, v):
"""Update an excel cell and return the value. If value is not specified, return the current cell value."""
is_date = "s" in self.cell.attrib
if is_date:
assert isinstance(
v, date), ValueError(f"Expected type: {date}, got {type(v)}")
v = to_excel(v)
v = str(v) if v is not None else ''
f_xml = self.cell.find('{0}f'.format(SHEET_MAIN_NS))
if f_xml is not None:
# Remove any existing formula when setting the value
f_xml.getparent().remove(f_xml)
v_xml = self.cell.find('{0}v'.format(SHEET_MAIN_NS))
if v_xml is not None:
if 't' in self.cell.attrib and self.cell.attrib[
't'] == 's': # value is from string table
sid = int(v_xml.text)
self.set_shared_string(sid, v)
else:
v_xml.text = v
elif 't' not in self.cell.attrib or self.cell.attrib['t'] != 's':
if hasattr(etree, 'Element'):
v_xml = etree.Element('{0}v'.format(SHEET_MAIN_NS))
v_xml.text = v
self.cell.append(v_xml)
log.warn('Appending new value on {0} = {1}'.format(
self.name, v))
else:
raise AttributeError(
'Unable to update cell {0} to {1} - etree error.'.format(
self.name, v))
else:
raise AttributeError(
'Unable to update cell {0} to {1} - check cell type.'.format(
self.name, v))
def _set_attributes(cell, styled=None):
"""
Set coordinate and datatype
"""
coordinate = cell.coordinate
attrs = {'r': coordinate}
if styled:
attrs['s'] = '%d' % cell.style_id
if cell.data_type == "s":
attrs['t'] = "inlineStr"
elif cell.data_type != 'f':
attrs['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attrs['t'] = "n"
value = to_excel(value, cell.parent.parent.epoch)
if cell.hyperlink:
cell.parent._hyperlinks.append(cell.hyperlink)
return value, attrs
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)
# Copy code from v2.0.5. Could not find where SharedDate is in
# latest version of openpyxl (and if it's useful)
if isinstance(date_obj, datetime):
to_excel(date_obj)
elif isinstance(date_obj, date):
to_excel(date_obj)
elif isinstance(date_obj, time):
time_to_days(date_obj)
elif isinstance(date_obj, timedelta):
timedelta_to_days(date_obj)
except ValueError:
return date_str
else:
return date_obj
def get_type(value):
if isinstance(value, NUMERIC_TYPES):
dt = xlrd.XL_CELL_NUMBER
elif isinstance(value, STRING_TYPES):
dt = xlrd.XL_CELL_TEXT
elif isinstance(value, TIME_TYPES):
dt = xlrd.XL_CELL_DATE
value = to_excel(value)
elif isinstance(value, BOOL_TYPE):
dt = xlrd.XL_CELL_BOOLEAN
else:
return '', xlrd.XL_CELL_TEXT
return value, dt
def etree_write_cell(xf, worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attributes['t'] = "n"
value = to_excel(value, worksheet.parent.epoch)
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
worksheet._comments.append(comment)
el = Element("c", attributes)
if value is None or value == "":
xf.write(el)
return
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
xf.write(el)
def etree_write_cell(xf, worksheet, cell, styled=None):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attributes['t'] = "n"
value = to_excel(value)
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
worksheet._comments.append(comment)
el = Element("c", attributes)
if value is None or value == "":
xf.write(el)
return
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
formula = SubElement(el, 'f', shared_formula)
if value is not None:
formula.text = value[1:]
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
cell_content = SubElement(el, 'v')
if value is not None:
cell_content.text = safe_string(value)
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
xf.write(el)
def lxml_write_cell(xf, worksheet, cell, styled=False):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attributes['t'] = "n"
value = to_excel(value)
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
worksheet._comments.append(comment)
if value == '' or value is None:
with xf.element("c", attributes):
return
with xf.element('c', attributes):
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
with xf.element('f', shared_formula):
if value is not None:
xf.write(value[1:])
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
with xf.element("v"):
if value is not None:
xf.write(safe_string(value))
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
def lxml_write_cell(xf, worksheet, cell, styled=False):
coordinate = cell.coordinate
attributes = {'r': coordinate}
if styled:
attributes['s'] = '%d' % cell.style_id
if cell.data_type != 'f':
attributes['t'] = cell.data_type
value = cell._value
if cell.data_type == "d":
if cell.parent.parent.iso_dates:
if isinstance(value, timedelta):
value = days_to_time(value)
value = value.isoformat()
else:
attributes['t'] = "n"
value = to_excel(value, worksheet.parent.epoch)
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
worksheet._comments.append(comment)
if value == '' or value is None:
with xf.element("c", attributes):
return
with xf.element('c', attributes):
if cell.data_type == 'f':
shared_formula = worksheet.formula_attributes.get(coordinate, {})
with xf.element('f', shared_formula):
if value is not None:
xf.write(value[1:])
value = None
if cell.data_type == 's':
value = worksheet.parent.shared_strings.add(value)
with xf.element("v"):
if value is not None:
xf.write(safe_string(value))
if cell.hyperlink:
worksheet._hyperlinks.append(cell.hyperlink)
def string_to_date_with_xls_validation(cls, date_str):
date_obj = datetime.strptime(date_str, '%Y-%m-%d').date()
try:
return to_excel(date_obj)
except ValueError:
return date_obj
def cleanValues(val):
# if cell value is a datetime object, then convert to excel number
if isinstance(val, datetime.datetime):
val = to_excel(val)
return val
def _bind_date(value, cell):
cell.data_type = Cell.TYPE_NUMERIC
cell._value = to_excel(value, cell.base_date)
cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2
def string_to_date_with_xls_validation(cls, date_str):
date_obj = datetime.strptime(date_str, '%Y-%m-%d').date()
try:
return to_excel(date_obj)
except ValueError:
return date_obj