def load_cells(self, macrosheet, xls_sheet): try: for xls_cell in xls_sheet.get_used_cells(): cell = Cell() cell.sheet = macrosheet if xls_cell.formula is not None and len(xls_cell.formula) > 0: cell.formula = '=' + xls_cell.formula cell.value = xls_cell.value cell.row = xls_cell.row + 1 cell.column = Cell.convert_to_column_name(xls_cell.column + 1) if cell.value is not None or cell.formula is not None: macrosheet.add_cell(cell) except Exception as error: print('CELL(Formula): ' + str(error.args[2]))
def load_cells(self, macrosheet, xls_sheet): cells = {} try: self._excel.Application.ScreenUpdating = False col_offset = xls_sheet.UsedRange.Column row_offset = xls_sheet.UsedRange.Row formulas = xls_sheet.UsedRange.Formula if formulas is not None: for row_no, row in enumerate(formulas): for col_no, col in enumerate(row): if col: cell = Cell() cell.sheet = macrosheet if len(col) > 1 and col.startswith('='): cell.formula = col else: cell.value = col row_addr = row_offset + row_no col_addr = col_offset + col_no cell.row = row_addr cell.column = Cell.convert_to_column_name(col_addr) cells[(col_addr, row_addr)] = cell self._excel.Application.ScreenUpdating = True except pywintypes.com_error as error: print('CELL(Formula): ' + str(error.args[2])) try: values = xls_sheet.UsedRange.Value if values is not None: for row_no, row in enumerate(values): for col_no, col in enumerate(row): if col: row_addr = row_offset + row_no col_addr = col_offset + col_no if (col_addr, row_addr) in cells: cell = cells[(col_addr, row_addr)] cell.value = col else: cell = Cell() cell.sheet = macrosheet cell.value = col cell.row = row_addr cell.column = Cell.convert_to_column_name( col_addr) cells[(col_addr, row_addr)] = cell except pywintypes.com_error as error: print('CELL(Constant): ' + str(error.args[2])) for cell in cells: macrosheet.add_cell(cells[cell])
def get_cell_info(self, sheet_name, col, row, info_type_id): sheet = self.xls_workbook.sheet_by_name(sheet_name) row = int(row) - 1 column = Cell.convert_to_column_index(col) - 1 info_type_id = int(float(info_type_id)) data = None not_exist = False not_implemented = False if info_type_id == 17: not_exist = False if row in sheet.rowinfo_map: data = sheet.rowinfo_map[row].height else: data = sheet.default_row_height data = Cell.convert_twip_to_point(data) data = round(float(data) * 4) / 4 else: if (row, column) in sheet.used_cells: cell = sheet.cell(row, column) if cell.xf_index is not None and cell.xf_index < len( self.xls_workbook.xf_list): fmt = self.xls_workbook.xf_list[cell.xf_index] font = self.xls_workbook.font_list[fmt.font_index] else: normal_style = self.xls_workbook.style_name_map['Normal'][ 1] fmt = self.xls_workbook.xf_list[normal_style] font = self.xls_workbook.font_list[fmt.font_index] else: normal_style = self.xls_workbook.style_name_map['Normal'][1] fmt = self.xls_workbook.xf_list[normal_style] font = self.xls_workbook.font_list[fmt.font_index] not_exist = False if info_type_id == 8: data = fmt.alignment.hor_align + 1 # elif info_type_id == 9: # data = fmt.border.left_line_style # # elif info_type_id == 10: # data = fmt.border.right_line_style # # elif info_type_id == 11: # data = fmt.border.top_line_style # # elif info_type_id == 12: # data = fmt.border.bottom_line_style # # elif info_type_id == 13: # data = fmt.border.fill_pattern # # elif info_type_id == 14: # data = fmt.protection.cell_locked # # elif info_type_id == 15: # data = fmt.protection.formula_hidden # return data # # elif info_type_id == 18: # data = font.name # return data elif info_type_id == 19: data = font.height data = Cell.convert_twip_to_point(data) # elif info_type_id == 20: # data = font.bold # # elif info_type_id == 21: # data = font.italic # # elif info_type_id == 22: # data = font.underlined # # elif info_type_id == 23: # data = font.struck_out elif info_type_id == 24: data = font.colour_index - 7 if font.colour_index > 7 else font.colour_index # elif info_type_id == 25: # data = font.outline # # elif info_type_id == 26: # data = font.shadow # elif info_type_id == 34: # # Left Color index # data = fmt.border.left_colour_index # # elif info_type_id == 35: # # Right Color index # data = fmt.border.right_colour_index # # elif info_type_id == 36: # # Top Color index # data = fmt.border.top_colour_index # # elif info_type_id == 37: # # Bottom Color index # data = fmt.border.bottom_colour_index elif info_type_id == 38: data = fmt.background.pattern_colour_index - 7 if font.colour_index > 7 else font.colour_index elif info_type_id == 50: data = fmt.alignment.vert_align + 1 # elif info_type_id == 51: # data = fmt.alignment.rotation else: not_implemented = True return data, not_exist, not_implemented
def xlref(self, row, column, zero_indexed=True): if zero_indexed: row += 1 column += 1 return '$' + Cell.convert_to_column_name(column) + '$' + str(row)