1. DataTables
1-1. What Are DataTables?
데이터 테이블은 2차원 데이터를 의미한다.(python pandas의 DataFrame) 데이터 테이블에는 Cell이라는 개념이 있다. 그냥 column과 row를 인덱싱해서 하나의 값에 접근한 것을 cell이라고 한다.
데이터 테이블을 만드는 방법은 아래와 같다.
-
Build Data Table Activity
이 액티비티로 데이터 테이블 변수를 만들 수 있다. 컬럼과 컬럼의 제약조건을 설정할 수 있으며 기본적으로 들어가는 row도 지정할 수 있다. -
Read Range Activity
엑셀 파일에서 어떤 범위 값을 지정해서 데이터 테이블로 읽을 때 사용한다. -
Read CSV Activity
csv 파일에서 값을 읽어들여 데이터 테이블로 반환하는 액티비티이다. -
Data Scraping Action
브라우저, 어플리캐이선, 여러 문서에서 정형 데이터를 추출해 데이터 테이블로 반환하는 액션이다. [디자인 > 스크래핑 > 데이터 스크래핑] 리본 메뉴에서 사용 가능하다. -
Generate Data table From Text Activity
텍스트 데이터로부터 데이터 테이블을 추출하는 액티비티이다. pandas.read_csv()에서sep=파라미터 조정하듯 사용자가 분리 문자를 지정할 수 있다.
1-2. Working With DataTables
데이터 테이블을 다루는 매우 다양한 액티비티가 있다. 아래는 데이터 테이블에 사용할 수 있는 액티비티들이다.
| Activity | 설명 |
|---|---|
| Build Data Table | 데이터 테이블의 구조를 만드는 액티비티, 컬럼과 컬럼의 제약조건 지정 |
| For Each Row in Data Table | 액티빝티를 입력받은 데이터 테이블의 로우로 For Each 액티비티 수행 |
| Filter Data Table | 기준을 제시해서 데이터 테이블을 필터링, 출력으로 새로운 데이터 테이블로 지정할 수 있음 |
| Add Data Column | 이미 존재하는 데이터 테이블을 입력으로 받고, 해당 데이터 테이블에 새로운 컬럼을 지정, 해당 링크 참고 |
| Add Data Row | 이미 존재하는 데이터 테이블을 입력으로 받고, 해당 데이터 테이블에 새로운 로우 추가, 추가되는 데이터는 DataRow 타입이거나 Array Row 타입이어야 하고 데이터 테이블 구조와 동일해야 함 |
| Clear Data Table | 입력받은 데이터 테이블을 초기화 |
| Generate Data Table From Text | 텍스트 데이터로부터 데이터 테이블을 추출, 분리 문자 지정 가능 |
| Join Data Tables | 두 개의 데이터 테이블을 입력 받고 join하는 액티비티, left/right/inner 중 옵션 선택 가능 |
| Lookup Data Table | 엑셀의 VLookop과 비슷한 동작을 함, 출력으로 로우의 인덱스를 반환하거나 값 자체를 반환할 수 있음 |
| Merge Data Table | 하나의 데이터 테이블을 다른 데이터 테이블에 이어 붙일 ㄸ대 사용함, 이어붙일 때 스키마가 다른 경우를 대비해놔서 Join Data Table보다 더 편리함 |
| Output Data Table | 데이터 테이블을 csv 포멧의 문자열 값으로 변환함(변환된 문자열을 다른 변수에 넣고 출력하면 데이터 테이블의 상태를 확인할 수 있음) |
| Remove Data Column | 입력 받은 데이터 테이블에서 특정 컬럼을 삭제함, 삭제할 컬럼을 타게팅 하는 방법으로는 컬럼 인덱스 제시/컬럼 이름 제시/Data Column 변수를 사용하는 방법이 있음 |
| Remove Data Row | 입력 받은 데이터 테이블에서 특정 로우를 삭제함, 로우 인덱스나 Data Row 변수를 지정해서 동작 |
| Remove Duplicate Rows | 입력 받은 데이터 테이블에서 중복된 로우를 제거하고 첫 번째 로우만 남김 |
| Sort Data Table | 입력받은 데이터 테이블에서 특정 컬럼을 기준으로 오름차순/내림차순 정렬 |
| Get Row Item | 하나의 로우에서 지정된 컬럼 이름이나 순서에 해당하는 값을 반환 |
| Update Row Item | 데이터 테이블 로우에서 특정 컬럼의 값을 변경 |
2. Workbooks
2-1. Workbooks and Common Activity
많은 경우 데이터베이스는 워크북에 저장되어있다. 이 데이터베이스는 앞서 살펴본 데이터 테이블을 다루는 액티비티들로 처리할 수 있다. 그러면 워크북은 어떻게 다룰 수 있을까?
UIPath는 두 가지 디자인이 있고 각 디자인은 워크북에 접근하고 처리하기 위해 워크북과 엑셀 두 가지 방법을 제공한다.
- Workbook
- 파일 수준의 접근
- workbook 액티비티는 모두 백그라운드에서 동작함
- 엑셀 어플리케이션이 설치되어있지 않아도 사용 가능
- 엑셀 어플리케이션을 열지 않으므로 더 빠르고 정확한 동작 가능
- .xls, .xlsx 파일에만 작업 가능(.xlsm 파일은 작업 불가)
- 모든 파일은 엑셀에서 열리면 액티비티 수행 불가
- Excel(Moder design과 classic design이 제공하는 액티비티가 다름)
- 사람이 동작하는 것과 같이 엑셀 어플리케이션을 켜서 동작
- .xls, .xlsx, .xlsm 파일 모두 작업 가능(몇 가지 액티비티는 .csv 파일에도 동작 가능)
- 엑셀 어플리케이션이 설치되어 있어야함
엑셀에 접근하는 액티비티는 modern design과 classic design에서 제공하는 액티비티가 다른데, 아래는 classic design에서 제공하는 액티비티이다.
| Activity | 설명 |
|---|---|
| Append Range | 지정된 엑셀 시트 끝 부분에 입력받은 데이터 테이블을 추가함, 지정된 엑셀시트가 존재하지 않는 시트라면, 그 시트를 생성하고 데이터를 추가함 |
| Get Table Range | 엑셀에서 테이블을 추출 |
| Read Cell | 하나의 셀에서 값을 읽어들여 문자열로 저장 |
| Read Cell Formula | 지정된 셀에서 수식을 읽어들여 문자열로 저장 |
| Read Column | 시작지점을 입력받고(예 : "A2") 그 이후부터 해당 컬럼의 값을 읽어들여 IEnumerable \ 데이터 타입으로 저장 |
| Read Range | 특정 범위의 데이터들을 데이터 테이블 타입으로 저장함, classic design에서는 Use Filter 속성이 있어 추출 시 데이터 필터링 가능 |
| Read Row | 시작지점을 입력받고(예 : "B4") 해당 로우의 값을 읽어들여 IEnumerable \ 데이터 타입으로 저장 |
| Write Cell | 지정한 셀에 원하는 값을 넣음, 이미 값이 있는 경우에는 덮어쓰고 시트가 없는 경우에는 시트를 새로 생성함 |
| Write Range | 데이터 테이블을 원하는 위치에 입력 |
2-2. Excel Application Scope and Specific Activities
classic design에는 Excel Application Scope라는 액티비티가 있다. 원래 파일 작업을 할 때 파일을 열고, 저장하고, 닫는 동작이 필요하고 사용자는 이런 명령을 내려주어야 오류없이 파일 작업을 할 수 있다. 그런데 일일이 이런 동작을 추가하는 것은 귀찮고 잊기도 쉽다. 그래서 해당 동작을 대신해주는 액티비티가 Excel Application Scope이다. 우리는 Excel Application Scope 컨테이너 안에 원하는 동작만 넣어주면 된다.
엑셀 어플리케이션에서 사용이 가능한 액티비티가 아래와 같이 카테고리별로 존재한다. 카테고리에 대한 설명을 먼저 해보자.
| Activity Category | 설명 |
|---|---|
| csv Activities | CSV 파일을 읽고 쓰는 액티비티들, Excel Application Scope 컨테이너 안에 없더라도 독립적으로 사용이 가능 |
| Range Activities | 데이터를 읽기 쓰기 추가 삭제 복사/붙여넣기, 데이터 테이블을 다루는 액티비티들과 비슷하지만 엑셀 어플리케이션에서 작동한다는 차이가 있음 |
| Table Activities | 엑셀 파일을 추가, 필터링, 정렬함, Range Activities와 동일하게 엑셀 파일 안에서 사용되는 액티비티들 |
| File Activities | 엑셀 파일을 열고 닫음 |
| Cell Color Activities | 엑셀 파일에서 셀의 색깔 지정 |
| Sheet Activities | 엑셀 파일의 시트에 동작 |
| Pivot Table Activities | 피벗 테이블을 조작 |
| Macro Activities | 이미 지정되어있는 엑셀 메크로를 실행하거나 다른 파일의 메크로를 가져옴, xslm 파일에서만 동작 |
이번엔 각 카테고리에 속하는 액티비티와 그 설명을 보자.
| Activity Category | Activity | 설명 |
|---|---|---|
| CSV Activities | Append to CSV | 데이터 테이블을 csv 파일에 추가함 / 데이터 테이블이 없으면 추가 / 덮어쓰기가 아님 |
| CSV Activities | Read CSV | csv파일 전체를 읽어들여 데이터 테이블로 반환 |
| CSV Activities | Write CSV | 데이터 테이블 데이터를 csv 파일에 덮어 쓰기 |
| Range Activities | Delete Column | 이름을 기준으로 컬럼을 제거함 |
| Range Activities | Insert Column | 엑셀 파일에서 특정 위치에 빈 컬럼 추가 |
| Range Activities | Insert/Delete Columns | 빈 컬럼들을 추가하거나 이미 존재하는 컬럼들을 삭제 |
| Range Activities | Read Column | 입력받은 시작 셀을 시작지점으로 그 열의 값들을 IEnumberable \ 변수로 반환 |
| Range Activities | Insert/Delete Rows | 비어있는 로우들을 추가하거나 이미 존재하는 로우들을 삭제 |
| Range Activities | Select Range | 엑셀 파일에서 특정한 범위를 선택, 선택한 데이터들로 다른 동작을 할 때 함께 사용되는 액티비티 |
| Range Activities | Get Select Range | 문자열로 원하는 범위를 출력 |
| Range Activities | Delete Range | 특정한 범위를 제거 |
| Range Activities | Auto Fill Range | 주어진 범위에 주어진 수식 채워넣기 |
| Range Activities | Copy Paste Range | 전체 범위를 복사/붙여넣음(값, 수식, 포멧 전부) |
| Range Activities | Lookup Range | 주어진 범위에서 셀 값을 검색 |
| Range Activities | Remove Duplicate Range | 주어진 범위에서 중복 제거 |
| Range Activities | Read Range | 원하는 범위의 값들을 데이터 테이블 변수로 저장, 범위가 지정되지 않거나 셀 하나만 지정된 경우 엑셀시트 전체를 선택 |
| Range Activities | Append Range | 데이터 테이블 변수에 저장된 값을 스프레드시트 아래에 추가, 시트가 없으면 시트를 만들고 추가 |
| Range Activities | Write Range | 입력받은 시작 셀을 시작지점으로 데이터 테이블 변수에 있는 데이터를 붙여넣음, 시작 셀을 입력받지 못했으면 A1 셀에서부터 시작 |
| Table Activities | Filter Table | 컬럼으로부터 값들에 필터 적용, 필터링 된 데이터들은 삭제되는 것이 아니라 보이지 않게 처리한 것 뿐임 |
| Table Activities | Sort Table | 주어진 컬럼을 기준으로 값을 정렬 |
| Table Activities | Creage Table | 특정 범위에 테이블을 만듦 |
| File Activities | Close Workbook | 엑셀 닫기 |
| File Activities | Save Workbook | 엑셀 저장 |
| Cell Color Activities | Get Cell Color | 원하는 셀 주소를 입력받고 그 셀의 셀 색깔을 변수로 저장 |
| Cell Color Activities | Set Range Color | 범위를 입력받고 그 범위의 셀 색깔을 입력받은 변수값으로 변경 |
| Sheet Activities | Get Workbook Sheets | 인덱스로 시트의 이름 읽기 |
| Sheet Activities | Get Workbook Sheets | 시트 이름들을 추출하고 정렬 |
| Sheet Activities | Copy Sheet | 시트를 복사하고 다른 엑셀 파일로 붙여넣음 |
| Pivot Table Activities | Refresh Pivot Table | 피벗 테이블을 초기화, 피벗 테이블 데이터가 변경될 때 사용함(자동으로 적용되는 것이 아님) |
| Pivot Table Activities | Create Pivot Table | 원하는 시트에 피벗 테이블을 생성 |
| Macro Activities | Execute Macro | 메크로 실행 |
| Macro Activities | Invoke VBA | 다른 파일로부터 메트로를 가져옴 |
3. Excel Modern Design Experience
3-1. Modern Design Excel Activities
Use Excel File 액티비티는 엑셀 파일에 대해 여러 작업을 할 수 있게 해주는 액티비터이다.
아래는 modern design애서 주요하게 사용되는 액티비티들이다.
- Auto Fill
- Creat Pivot Table
- Delete Column
- Delete Sheet
- Export to CSV
- Filter
- FOr Each Excel Row
- Format as Table
- Get Chart
- Insert Rows
- Read Cell Value
- Read Range
- Remove Duplicates
- Save Excel File As
- Sort Range
- VLookup
- Write Cell
- Write Data Table to Excel
classic design 액티비티들을 더 많이 사욜하므로 modern design 액티비티들은 뭐가 있는지만 알고 가자.(필요하면 찾아보기)
4. Practice
4-1. Calcuate the sum in two Excel files
4-1-1. 과업 설명
이번 실습 과제에서는 A열의 데이터를 B열에 더한 값을 C열에 붙여넣도록 하자.
이 과제는 아래와 같이 세 가지 방법으로 처리할 수 있으므로 고민해보자.
- 데이터 하나씩 더해서 C열에 붙여넣는 과정이 눈에 보이도록
- 엑셀파일이 닫혀있는 상태로 작업하는데, 데이터 테이블을 메모리에 저장해놓고 마지막에 한 번에 붙여넣기
- 원본 파일에서 엑셀 수식을 사용해서 계산
4-1-2. 방법 1
데이터 테이블로 값을 받아와서 개별적으로 계산하고 셀에 입력한다.
-
Excel Application Scope
엑셀 파일을 열어 작업하기 위해 Excel Application Scope 액티비티 사용 -
Read Range
원하는 값을 데이터 테이블로 가져온다. -
For Each Row in Data Table
데이터를 하나씩 가져와서 계산 후 C 열에 붙여야하기 때문에 행을 가져오기 위해 해당 액티비티를 사용한다.(Row의 데이터타입은 RowData임)
-
Assign
계산한 값을 원하는 위치에 입력하기 위해 데이터 테이블에서 현재 행의 인덱스에 접근한다. 이 때 아래와 같이 쓴다. 1을 더해주는 이유는 인덱스는 0부터 시작인데, 엑셀에서는 인덱스가 1부터 시작하기 때문이다.
-
Get Row Item
RowData 타입의 변수에서 원하는 위치의 값을 가져온다. A값과 B값을 저장한다. -
Assign & Write Cell
계산한 값을 원하는 위치에 작성한다. 이 때 4에서 구한 RowIndex 값을 사용한다.
4-1-3. 방법 2
-
Read Range Workbook
엑셀 어플리케이션이 닫혀있는 상태로 작업하기 위해서 excel application scope를 사용하지 않고 워크북 관련 액티비티를 사용한다. -
Add Data Column
가져온 데이터 테이블에 컬럼 C를 추가한다. 데이터 테이블 안에서 계산을 한 뒤 한 번에 붙여넣을 것이기 때문이다.
3, For Each Row in Data Table
가져온 데이터 테이블의 로우를 하나씩 돌며 컬럼 C에 데이터를 채워 넣는다.
-
Assign
DataRow 변수에 담긴 데이터는 인덱싱으로 편리하게 가져올 수 있다. 인덱싱 하는 방법은 Array와 동일하게 소괄호를 사용해 인덱싱한다. 이번 실습에서는 변수를 사용하지 않고 직접 값에 접근해 넣어보자.
위와 같이 Row(0)은 A열, Row(1)은 B열이다. 이 값들은 문자열 타입이므로 double형으로 바꿔주기 위해Convert.ToDouble메소드를 사용해주었다. -
Write Range Workbook
이렇게 만든 데이터 테이블을 엑셀을 열지 않고 붙여넣기 위해 이번에도 workbook 액티비티를 사용했다.
4-1-4. 방법 3
-
Excel Application Scope
엑셀을 연다. -
Read Range
데이터 테이블로 값들을 가져온다. -
For Each Row in Data Table
로우를 하나씩 돌면서 작업한다. -
Assign
현재 로우의 인덱스를 가져오는데, 방법 1에서 사용한 방식으로 가져오면 된다. -
Write Cell
원하는 지점에 수식을 써준다.
4-2. Calcuate Loss Invoices
4-2-1. 과업 설명
이번 과제는 'Clients.csv'에서 고객별로 파산 여부를 확인해 파산한 고객에게 발행된 대금을 'Invoice.xslx'에서 종합하는 과제이다.
4-2-2. 구현
-
Read CSV, Read Range Workbook
두 데이터 테이블을 읽어옴 -
Add Data Column
Client 데이터 테이블에 각 클라이언트 마다 미수금을 넣기 위한 'Total' 컬럼을 추가한다. 만약 파산 여부가 TRUE이면 미수금 총액이, FALSE면 0이 들어간다. -
For Each Row in Data Table
Client 데이터 테이블에서 로우를 하나씩 돌며 각 클라이언트마다 파산 여부를 판단한다. -
Assign
매 반복마다 미수금 총액을 0으로 초기화해준다. -
If, Filter Data Table
파산 여부가 TURE인지 확인한다. 파산 여부가 TRUE일 떄만 미수금 총액을 계산한다. 계산을 위해 파산한 클라이언트 이름만 invoice 데이터 테이블에서 걸러낸다.
-
For Each Row in Data Table, Assign
걸러낸 데이터 테이블을 한 번 더 돌며 각 미수금을 더해준다.
-
Assign
미수금 총액을 Taotal 열에 추가해준다. 여기서 포인트는 각 for each row 안에 위치해서 클라이언트마다 총액이 들어간다는 점이다. 파산을 하지 않았다면 0, 파산을 했다면 총액이 들어간다.
-
Write CSV
client 데이터 테이블을 원본 client 파일에 붙여넣는다. 컬럼만 추가해서 넣으려 헀으나 csv 파일은 데이터 테이블을 전부 넣어야하므로(덮어쓰기이므로) 해당 액티비티를 사용했다.
4-3. Calculating Percentages of Expenses
4-3-1. 과업 설명
카드, 현금 지출 내역에서 각 학목별, 월별로 모아 각각의 비율을 구해보자.
4-3-2. 구현
-
Read Range, Merge Data Table
두 개의 엑셀 파일을 읽고, 카드 데이터에서 현금 데이터를 합친다. -
Add Data Column
카드 데이터에 월을 저장할 컬럼을 새로 지정한다.
-
Build Data Table
결과를 담을 데이터 테이블을 선언한다. 스키마는 아래와 같다.
-
For Each Row in Data Table
카드 데이터에서 로우를 하나씩 돌며 필요한 작업을 한다. 필요한 작업이라 함은, 날짜 컬럼에서 월•년 부분만 걸러내기, 퍼센트를 구하기 위한 총액 구하기, 결과 데이터에 데이터 추가하기가 있다. -
Get Row Item, Assign
월•년만 필요하므로 날짜 데이터를 가져온다. 그리고 VBA 문법을 사용하여 필요한 부분만 빼내어 저장한다.
-
Assign
퍼센트 계산을 위한 총액을 계산한다.
-
Add Data Row
결과 데이터 테이블에 지출 내역, 월 정보를 넣는다. 열을 추가할 때는 배열 모양으로 넣어야하므로 대괄호({})로 묶어준다.
-
Remove Duplicatie Rows
결과 데이터 테이블에서 중복을 제거해준다. 왜냐하면 카테고리별로, 월 별로 하나의 로우만 있어야하기 때문이다. -
For Each Row In Data Table, Filter Data Table
결과 데이터 테이블을 돌면서 지출 카테고리, 월을 기준으로 지출 내역 데이터 테이블을 필터링한다.
-
For Each Row in Data Table
위에서 필터링한 데이터는 동일한 지출 카테고리, 월 데이터들이다. 이를 소계 내서 결과 데이터 테이블에 넣어주면 된다. 그래서 필터링 된 데이터 테이블로 반복을 돌리고 소계를 내준다.
-
Assign
위에서 계산한 소계와 그에 대한 퍼센트 값을 결과 데이터 테이블에 입력해준다.
-
Write Range Workbook
결과 데이터 테이블을 새로운 엑셀 파일에 저장한다.
5. 느낀점
나는 반복문을 너무 중첩해서 많이 사용하는 것 같다. 이를 해결하는 방법을 고안해야할 필요가 있을 것 같다.
