이번 시간에는 Fill Range activity를 python 코드로 구현해보는 시간을 가져보자.
(uipath Fill Range activity 알아보기)
def fill_range(file_name: str, sheet_name: [str, int], range_value: str, value: [str, int], value_change: bool = True):
wb = load_workbook(file_name)
ws = wb[sheet_name]
pattern = r'[a-z A-Z]+'
column_result = re.findall(pattern, range_value)
start_column = column_result[0]
start_column = column_index_from_string(start_column)
end_column = column_result[1]
end_column = column_index_from_string(end_column)
pattern = r'\d+'
row_result = re.findall(pattern, range_value)
start_row = int(row_result[0])
end_row = int(row_result[1])
if value_change:
if type(value) == int:
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
new_value = value + col_idx
ws.cell(row=row, column=col, value=new_value)
value = value + 1
else:
if '=' in value:
pattern = r'[A-Z]+\d+'
value_result = re.findall(pattern, value)
dict = {}
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
for i in value_result:
cell_colume_str = i[0]
cell_row_str = i[1:]
new_cell = get_column_letter(column_index_from_string(cell_colume_str) + col_idx)
new_cell = new_cell + str(cell_row_str)
if len(new_cell) != len(i):
new_cell = new_cell + ' '
dict[i] = new_cell
cell_key = ''.join(list(dict.keys()))
cell_value = ''.join(list(dict.values()))
new_col = value.translate(str.maketrans(cell_key, cell_value))
ws.cell(row=row, column=col, value=new_col)
dict = {}
pattern = r'[A-Z]+\d+'
value_result = re.findall(pattern, value)
for i in value_result:
cell_colume_str = i[0]
cell_row_str = i[1:]
new_cell = str(int(cell_row_str) + 1)
new_cell = str(cell_colume_str) + new_cell
dict[i] = new_cell
for i in dict.keys():
value = value.replace(i, dict[i])
dict = {}
else:
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
ws.cell(row=row, column=col, value=value)
else:
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
ws.cell(row=row, column=col, value=value)
wb.save(file_name)
def fill_range(file_name: str, sheet_name: [str, int], range_value: str, value: [str, int], value_change: bool = True):
pattern = r'[a-z A-Z]+'
column_result = re.findall(pattern, range_value)
start_column = column_result[0]
start_column = column_index_from_string(start_column)
end_column = column_result[1]
end_column = column_index_from_string(end_column)
pattern = r'\d+'
row_result = re.findall(pattern, range_value)
start_row = int(row_result[0])
end_row = int(row_result[1])
if value_change:
...
else:...
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
ws.cell(row=row, column=col, value=value)
if type(value) == int:
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
new_value = value + col_idx
ws.cell(row=row, column=col, value=new_value)
value = value + 1
value = value + 1
다.new_value = value + col_idx
다.예를들어 범위가 A5:C8'이고 '8'이라는 값을 기입 하는 경우, 다음과 같은 결과값이 보인다.
if '=' in value:...
else:
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
ws.cell(row=row, column=col, value=value)
if '=' in value:
pattern = r'[A-Z]+\d+'
value_result = re.findall(pattern, value)
dict = {}
for row_idx, row in enumerate(range(start_row, end_row + 1)):
for col_idx, col in enumerate(range(start_column, end_column + 1)):
for i in value_result:
cell_colume_str = i[0]
cell_row_str = i[1:]
new_cell = get_column_letter(column_index_from_string(cell_colume_str) + col_idx)
new_cell = new_cell + str(cell_row_str)
if len(new_cell) != len(i):
new_cell = new_cell + ' '
dict[i] = new_cell
cell_key = ''.join(list(dict.keys()))
cell_value = ''.join(list(dict.values()))
new_col = value.translate(str.maketrans(cell_key, cell_value))
ws.cell(row=row, column=col, value=new_col)
dict = {}
pattern = r'[A-Z]+\d+'
value_result = re.findall(pattern, value)
for i in value_result:
cell_colume_str = i[0]
cell_row_str = i[1:]
new_cell = str(int(cell_row_str) + 1)
new_cell = str(cell_colume_str) + new_cell
dict[i] = new_cell
for i in dict.keys():
value = value.replace(i, dict[i])
dict = {}
value.translate(str.maketrans(cell_key, cell_value))
를 통해 수식을 한번에 변환한다.