이번 시간에는 Delete Column/Rows activity를 python 코드로 구현해보는 시간을 가져보자.
(uipath Delete Column/Rows activity 알아보기)
def delete_column(file_name: str, sheet_name: [str, int], column: str):
wb = load_workbook(file_name)
ws = wb[sheet_name]
df = pd.read_excel(file_name, sheet_name=sheet_name)
try:
column_index = df.columns.get_loc(column) + 1
ws.delete_cols(column_index)
wb.save(file_name)
except:
pattern = r'[a-z A-Z]+'
result = re.findall(pattern, column)
alphabet = result
alphabet_count = len(alphabet)
if alphabet_count == 1:
ws.delete_cols(int(ord(alphabet[0])) - 64)
wb.save(file_name)
elif alphabet_count == 2 and ':' in column:
start_column = alphabet[0]
end_column = alphabet[1]
delete_numbers = int(ord(end_column)) - int(ord(start_column)) + 1
ws.delete_cols(int(ord(start_column)) - 64, amount=delete_numbers)
wb.save(file_name)
elif ',' in column:
column_split_str = column.split(',')
column_split_int = [int(ord(i)) for i in column_split_str]
column_split_int = sorted(column_split_int, reverse=True)
for i in column_split_int:
ws.delete_cols(i - 64)
wb.save(file_name)
def delete_column(file_name: str, sheet_name: [str, int], column: str):
try:
column_index = df.columns.get_loc(column) + 1
ws.delete_cols(column_index)
wb.save(file_name)
except:
pattern = r'[a-z A-Z]+'
result = re.findall(pattern, column)
alphabet = result
alphabet_count = len(alphabet)
if alphabet_count == 1:
ws.delete_cols(int(ord(alphabet[0])) - 64)
wb.save(file_name)
elif alphabet_count == 2 and ':' in column:
start_column = alphabet[0]
end_column = alphabet[1]
delete_numbers = int(ord(end_column)) - int(ord(start_column)) + 1
ws.delete_cols(int(ord(start_column)) - 64, amount=delete_numbers)
wb.save(file_name)
elif ',' in column:
column_split_str = column.split(',')
column_split_int = [int(ord(i)) for i in column_split_str]
column_split_int = sorted(column_split_int, reverse=True)
for i in column_split_int:
ws.delete_cols(i - 64)
wb.save(file_name)
def delete_rows(file_name: str, sheet_name: [str, int], row: [str, int]):
wb = load_workbook(file_name)
ws = wb[sheet_name]
try:
row_index = int(row)
ws.delete_rows(idx=row_index)
wb.save(file_name)
except:
if '-' in row:
pattern = r'\d+'
result = re.findall(pattern, row)
numbers = result
row_index = int(numbers[1]) - int(numbers[0]) + 1
for i in range(row_index):
ws.delete_rows(idx=int(numbers[0]))
wb.save(file_name)
elif ',' in row:
pattern = r'\d+'
result = re.findall(pattern, row)
numbers = [int(i) for i in result]
numbers = sorted(numbers, reverse=True)
for i in numbers:
ws.delete_rows(idx=i)
wb.save(file_name)
def delete_rows(file_name: str, sheet_name: [str, int], row: [str, int]):
try:
row_index = int(row)
ws.delete_rows(idx=row_index)
wb.save(file_name)
if '-' in row:
pattern = r'\d+'
result = re.findall(pattern, row)
numbers = result
row_index = int(numbers[1]) - int(numbers[0]) + 1
for i in range(row_index):
ws.delete_rows(idx=int(numbers[0]))
wb.save(file_name)
elif ',' in row:
pattern = r'\d+'
result = re.findall(pattern, row)
numbers = [int(i) for i in result]
numbers = sorted(numbers, reverse=True)
for i in numbers:
ws.delete_rows(idx=i)
wb.save(file_name)
여담
위에서 설명한 코드들 중에서 귀찮에 내림차순으로 정렬 후, 삭제하는 코드가 섞여 있는 경우가 있다.
그냥 기입 된 순으로 삭제를 하면 되지 않을까? 라는 생각이 있을수도 있지만
delete_rows or delete_cols의 경우 삭제 후, 위로 밀기 or 왼쪽으로 밀기 기능으로 되기 때문에 내가 지우고자 하는 위치값들이 변화한다.
따라서 내림차순으로 정렬 후, 가장 위에서부터(row의 경우) 또는 가장 오른쪽에서 부터(column의 경우) 삭제를 진행하면 위의 경우를 해결 할 수 있다.