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') 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: self.ws.formula_attributes[coordinate] = {'t': formula_type} si = formula.get('si') # Shared group index for shared formulas if si: self.ws.formula_attributes[coordinate]['si'] = si ref = formula.get('ref') # Range for shared formulas if ref: self.ws.formula_attributes[coordinate]['ref'] = ref style = {} if style_id is not None: style_id = int(style_id) style = self.styles[style_id] column, row = coordinate_from_string(coordinate) cell = Cell(self.ws, column, row, **style) self.ws._add_cell(cell) if value is not None: if data_type == 'n': value = cell._cast_numeric(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' else: if data_type == 'inlineStr': data_type = 's' child = element.find(self.INLINE_STRING) if child is None: child = element.find(self.INLINE_RICHTEXT) if child is not None: value = child.text if self.guess_types or value is None: cell.value = value else: cell._value=value cell.data_type=data_type
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') 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: self.ws.formula_attributes[coordinate] = {'t': formula_type} si = formula.get( 'si') # Shared group index for shared formulas if si: self.ws.formula_attributes[coordinate]['si'] = si ref = formula.get('ref') # Range for shared formulas if ref: self.ws.formula_attributes[coordinate]['ref'] = ref style = {} if style_id is not None: style_id = int(style_id) style = self.styles[style_id] column, row = coordinate_from_string(coordinate) cell = Cell(self.ws, column, row, **style) self.ws._add_cell(cell) if value is not None: if data_type == 'n': value = cell._cast_numeric(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' else: if data_type == 'inlineStr': data_type = 's' child = element.find(self.INLINE_STRING) if child is None: child = element.find(self.INLINE_RICHTEXT) if child is not None: value = child.text if self.guess_types or value is None: cell.value = value else: cell._value = value cell.data_type = data_type
def bind_cells(self): for idx, row in self.parser.parse(): for cell in row: style = self.ws.parent._cell_styles[cell['style_id']] c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style) c._value = cell['value'] c.data_type = cell['data_type'] self.ws._cells[(cell['row'], cell['column'])] = c self.ws.formula_attributes = self.parser.array_formulae
def set_cell_to_number(cell: Cell, cell_format: str = '0') -> Cell: """Sets the data type on a cell Sets the data type on a cell to number if the value of the cell is a number. Will not work on a list of numbers (cannot be processed as a single number). :param cell: :param cell_format: :return: """ if cell.value and not cell.column == 'A': if cell.value.isdigit(): cell.data_type = 'n' cell.number_format = cell_format with suppress(ValueError): if float(cell.value): cell.data_type = 'n' cell.number_format = cell_format return cell
def apply_column_type(target: Cell, source: Cell, column_type): if column_type["source_data_type"] == source.data_type and type( source.value).__name__ == column_type["source_value_type"]: if column_type["target_data_type"] == "d": converted_value = from_excel_ordinal(int(source.value)) print( f"trying to convert column value {source.value} to date: {converted_value}" ) target.data_type = 's' target._value = converted_value.strftime("yyyy/mm/dd") if "target_number_format" in column_type: target._value = converted_value.strftime( column_type["target_number_format"])
def bind_cells(self): for idx, row in self.parser.parse(): for cell in row: style = self.ws.parent._cell_styles[cell['style_id']] c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style) c._value = cell['value'] c.data_type = cell['data_type'] self.ws._cells[(cell['row'], cell['column'])] = c if self.ws._cells: self.ws._current_row = self.ws.max_row # use cells not row dimensions
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 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') 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: self.ws.formula_attributes[coordinate] = {'t': formula_type} si = formula.get('si') # Shared group index for shared formulas if si: self.ws.formula_attributes[coordinate]['si'] = si if formula_type == "shared": # 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. try: trans = self.shared_formula_masters[si] except KeyError: # This cell must be master self.shared_formula_masters[si] = Translator( value, coordinate) else: value = trans.translate_formula(coordinate) ref = formula.get('ref') # Range for shared formulas if ref: self.ws.formula_attributes[coordinate]['ref'] = ref style = {} if style_id is not None: style_id = int(style_id) style = self.styles[style_id] row, column = coordinate_to_tuple(coordinate) cell = Cell(self.ws, row=row, col_idx=column, **style) self.ws._cells[(row, column)] = cell if value is not None: if data_type == 'n': value = _cast_number(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' else: if data_type == 'inlineStr': data_type = 's' child = element.find(self.INLINE_STRING) if child is None: child = element.find(self.INLINE_RICHTEXT) if child is not None: value = child.text if self.guess_types or value is None: cell.value = value else: cell._value=value cell.data_type=data_type