def is_date(self):
"""True if the value is formatted as a date
:type: bool
"""
return self.data_type == 'd' or (self.data_type == 'n' and
is_date_format(self.number_format))
def _bind_value(self, value):
"""Given a value, infer the correct data type"""
self.data_type = "n"
if value is True or value is False:
self.data_type = self.TYPE_BOOL
elif isinstance(value, NUMERIC_TYPES):
pass
elif isinstance(value, TIME_TYPES):
if not is_date_format(self.number_format):
self._set_time_format(value)
self.data_type = "d"
elif isinstance(value, STRING_TYPES):
value = self.check_string(value)
self.data_type = self.TYPE_STRING
if len(value) > 1 and value.startswith("="):
self.data_type = self.TYPE_FORMULA
elif value in self.ERROR_CODES:
self.data_type = self.TYPE_ERROR
elif self.guess_types:
value = self._infer_value(value)
elif value is not None:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
self._value = value
def _bind_value(self, value):
"""Given a value, infer the correct data type"""
self.data_type = "n"
if value is True or value is False:
self.data_type = self.TYPE_BOOL
elif isinstance(value, NUMERIC_TYPES):
pass
elif isinstance(value, TIME_TYPES):
if not is_date_format(self.number_format):
self._set_time_format(value)
self.data_type = "d"
elif isinstance(value, STRING_TYPES):
value = self.check_string(value)
self.data_type = self.TYPE_STRING
if len(value) > 1 and value.startswith("="):
self.data_type = self.TYPE_FORMULA
elif value in self.ERROR_CODES:
self.data_type = self.TYPE_ERROR
elif self.guess_types:
value = self._infer_value(value)
elif value is not None:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
self._value = value
def is_date(self):
"""Whether the value is formatted as a date
:rtype: bool
"""
return (self.has_style and is_date_format(self.number_format)
and self.data_type == self.TYPE_NUMERIC)
def _bind_value(self, value):
"""Given a value, infer the correct data type"""
self.data_type = "n"
t = type(value)
if t in NUMERIC_TYPES:
pass
elif t in TIME_TYPES:
if not is_date_format(self.number_format):
self.number_format = TIME_FORMATS[t]
self.data_type = "d"
elif t in STRING_TYPES:
value = self.check_string(value)
self.data_type = 's'
if len(value) > 1 and value.startswith("="):
self.data_type = 'f'
elif value in ERROR_CODES:
self.data_type = 'e'
elif self.guess_types: # deprecated
value = self._infer_value(value)
elif t is bool:
self.data_type = 'b'
elif value is not None:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
self._value = value
def is_date(self):
"""True if the value is formatted as a date
:type: bool
"""
return self.data_type == 'd' or (
self.data_type == 'n' and is_date_format(self.number_format)
)
def is_date(self):
"""Whether the value is formatted as a date
:rtype: bool
"""
return (self.has_style
and is_date_format(self.number_format)
and self.data_type == self.TYPE_NUMERIC)
def is_date(self):
"""Whether the value is formatted as a date
:rtype: bool
"""
if self.data_type == "n" and self.number_format != "General":
return is_date_format(self.number_format)
return False
def is_date(self):
"""Whether the value is formatted as a date
:rtype: bool
"""
if self.data_type == "n" and self.number_format != "General":
return is_date_format(self.number_format)
return False
def value(self):
if self._value is None:
return
if self.data_type == 'n':
if is_date_format(self.number_format):
return from_excel(self._value, self.base_date)
return self._value
if self.data_type == 'b':
return self._value == '1'
elif self.data_type in(Cell.TYPE_INLINE, Cell.TYPE_FORMULA_CACHE_STRING):
return unicode(self._value)
elif self.data_type == 's':
return unicode(self.shared_strings[int(self._value)])
return self._value
def value(self):
if self._value is None:
return
if self.data_type == 'n':
if is_date_format(self.number_format):
return from_excel(self._value, self.base_date)
return self._value
if self.data_type == 'b':
return self._value == '1'
elif self.data_type in(Cell.TYPE_INLINE, Cell.TYPE_FORMULA_CACHE_STRING):
return unicode(self._value)
elif self.data_type == 's':
return unicode(self.shared_strings[int(self._value)])
return self._value
def values(self):
"""
sheet 的数据集（列表）
xlsx 空表格是 None
xls 空表格是 ''
xlsx 0 是 0
xls 0 是 0.0
xls 的表达式不能显示
# 返回可迭代结果
"""
self.line = 0
# rows 是可迭代的生成器
for row in self.sheet.rows:
if self.conversion_format:
row_vals = []
# row_vals = [c.value for c in row]
# print(row)
for c in row:
# fill = c.fill
# fg_color = fill.start_color.rgb # start_color fgColor
# if 'Values must be of type' in str(fg_color):
# fg_color = '00000000'
# print(fg_color)
# bg_color = fill.end_color.rgb # end_color bgColor
# print(bg_color)
# print(c.data_type, c.number_format, is_date_format(c.number_format), c.value)
if c.value is None and self.none_to_null_characters:
cell_value = ''
elif c.data_type == "n":
if c.number_format != "General" and is_date_format(
c.number_format):
# print('number_format 日期',c.number_format,c.value)
cell_value = c.value.strftime(self.time_fmt)
else:
# cell_value = str(c.value) # 数字转换为字符串
cell_value = c.value
elif c.data_type == "d":
cell_value = c.value.strftime(self.time_fmt)
else:
cell_value = c.value
row_vals.append(cell_value)
else:
row_vals = [c.value for c in row]
# print(row_vals)
yield row_vals
self.line += 1
def is_date(self):
return is_date_format(self.number_format)
def is_date(self):
return self.data_type == 'n' and is_date_format(self.number_format)
def is_date(self):
return self.data_type == 'n' and is_date_format(self.number_format)
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
self._col_count += 1
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
if formula_type != "shared":
self.ws.formula_attributes[coordinate] = dict(
formula.attrib)
else:
si = formula.get(
'si') # Shared group index for shared formulas
# The spec (18.3.1.40) defines shared formulae in
# terms of the following:
#
# `master`: "The first formula in a group of shared
# formulas"
# `ref`: "Range of cells which the formula applies
# to." It's a required attribute on the master
# cell, forbidden otherwise.
# `shared cell`: "A cell is shared only when si is
# used and t is `shared`."
#
# Whether to use the cell's given formula or the
# master's depends on whether the cell is shared,
# whether it's in the ref, and whether it defines its
# own formula, as follows:
#
# Shared? Has formula? | In ref Not in ref
# ========= ==============|======== ===============
# Yes Yes | master impl. defined
# No Yes | own own
# Yes No | master impl. defined
# No No | ?? N/A
#
# The ?? is because the spec is silent on this issue,
# though my inference is that the cell does not
# receive a formula at all.
#
# For this implementation, we are using the master
# formula in the two "impl. defined" cases and no
# formula in the "??" case. This choice of
# implementation allows us to disregard the `ref`
# parameter altogether, and does not require
# computing expressions like `C5 in A1:D6`.
# Presumably, Excel does not generate spreadsheets
# with such contradictions.
if si in self.shared_formula_masters:
trans = self.shared_formula_masters[si]
value = trans.translate_formula(coordinate)
else:
self.shared_formula_masters[si] = Translator(
value, coordinate)
style_array = None
if style_id is not None:
style_id = int(style_id)
style_array = self.styles[style_id]
if coordinate:
row, column = coordinate_to_tuple(coordinate)
else:
row, column = self._row_count, self._col_count
cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array)
self.ws._cells[(row, column)] = cell
if value is not None:
if data_type == 'n':
value = _cast_number(value)
if is_date_format(cell.number_format):
data_type = 'd'
value = from_excel(value, self.epoch)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
elif data_type == 'd':
value = from_ISO8601(value)
else:
if data_type == 'inlineStr':
child = element.find(self.INLINE_STRING)
if child is not None:
data_type = 's'
richtext = Text.from_tree(child)
value = richtext.content
if self.guess_types or value is None:
cell.value = value
else:
cell._value = value
cell.data_type = data_type
def is_date(self):
return self.data_type == Cell.TYPE_NUMERIC and is_date_format(self.number_format)
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
self._col_count += 1
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
if formula_type != "shared":
self.ws.formula_attributes[coordinate] = dict(formula.attrib)
else:
si = formula.get('si') # Shared group index for shared formulas
# The spec (18.3.1.40) defines shared formulae in
# terms of the following:
#
# `master`: "The first formula in a group of shared
# formulas"
# `ref`: "Range of cells which the formula applies
# to." It's a required attribute on the master
# cell, forbidden otherwise.
# `shared cell`: "A cell is shared only when si is
# used and t is `shared`."
#
# Whether to use the cell's given formula or the
# master's depends on whether the cell is shared,
# whether it's in the ref, and whether it defines its
# own formula, as follows:
#
# Shared? Has formula? | In ref Not in ref
# ========= ==============|======== ===============
# Yes Yes | master impl. defined
# No Yes | own own
# Yes No | master impl. defined
# No No | ?? N/A
#
# The ?? is because the spec is silent on this issue,
# though my inference is that the cell does not
# receive a formula at all.
#
# For this implementation, we are using the master
# formula in the two "impl. defined" cases and no
# formula in the "??" case. This choice of
# implementation allows us to disregard the `ref`
# parameter altogether, and does not require
# computing expressions like `C5 in A1:D6`.
# Presumably, Excel does not generate spreadsheets
# with such contradictions.
if si in self.shared_formula_masters:
trans = self.shared_formula_masters[si]
value = trans.translate_formula(coordinate)
else:
self.shared_formula_masters[si] = Translator(value, coordinate)
style_array = None
if style_id is not None:
style_id = int(style_id)
style_array = self.styles[style_id]
if coordinate:
row, column = coordinate_to_tuple(coordinate)
else:
row, column = self._row_count, self._col_count
cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array)
self.ws._cells[(row, column)] = cell
if value is not None:
if data_type == 'n':
value = _cast_number(value)
if is_date_format(cell.number_format):
data_type = 'd'
value = from_excel(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
elif data_type == 'd':
value = from_ISO8601(value)
else:
if data_type == 'inlineStr':
child = element.find(self.INLINE_STRING)
if child is not None:
data_type = 's'
richtext = Text.from_tree(child)
value = richtext.content
if self.guess_types or value is None:
cell.value = value
else:
cell._value = value
cell.data_type = data_type
def is_date(self):
return self.data_type == Cell.TYPE_NUMERIC and is_date_format(
self.number_format)
