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)
