Excel and DataTables : Practice 1

UiPath/UiPath Practice

Excel and DataTables : Practice 1 - Calculating Sums

kimyosunnyc 2020. 10. 13. 14:00

출처 : UiPath Academy

Practice 1 - Calculating Sums (Calculate the sum in 2 Excel files)

세 가지 방법으로 Excel 파일에서 두 열의 값 합계를 계산한다.

A열의 값을 B열에 추가하고 다른 방식으로 C열에 쓰는 workflow를 만들자.

Excel을 열어두고 결과를 실시간 행별로 기록하므로 변경사항을 볼 수 있다.
Excel을 닫은 상태로 유지하며 DataTable 메모리에 열 값을 설정하고, 모든 DataTable을 한 번에 새 Excel 파일에 추가한다.
원본 파일의 Excel 수식을 사용하여 합계를 계산한다.

[필요사항]

아래 Sample Columns.xlsx 파일을 이 연습의 입력 파일로 사용한다.

Sample Columns.xlsx

0.01MB

Solution 1

Excel을 열어두고 변경내용을 볼 수 있도록 결과를 실시간 행별로 기록

📌 설정방법

'Read Range' Activity를 사용하여 Excel 파일을 읽는다. 전체 시트를 읽을 수 있도록 범위를 ""로 설정하고 Output 매개변수에서 Ctrl + K를 사용하여 "inputTable"이라는 DataTable 변수를 만든다.
'For Each Row' Activity를 사용하여 "inputTable"을 반복하도록 설정한다. "rowIndex"라는 Int32 변수를 생성한다. 그러면 나중에 쓸 행을 추적할 수 있다. Body 안의 내용 :
- 'Assign' Activity를 추가하여 inputTable.Rows.IndexOf(row)+1을 rowIndex에 할당한다. 이렇게하면 loop의 현재 행과 일치하도록 "rowIndex"값이 설정된다. "+1"은 Excel의 행이 1부터 시작하는 반면 DataTable은 Index 0에서 시작하기 때문에 필요하다.
- 'Get Row Item' Activity를 사용하고 ColumnIndex를 0으로 설정하고 Row를 row(임시 loop 변수)로 설정한다. Output 매개변수에서 Ctrl + K를 사용하여 "valueA"라는 변수를 만든다.
- 다른 'Get Row Item' Activity를 사용하여 ColumnIndex는 1로 설정하고 Row를 row로 설정한다. Output 매개변수에서 Ctrl + K를 사용하여 "valueB"라는 변수를 만든다.
- 아래에 'Assign' Activity를 추가하고 'valueA + valueB'를 valueC에 할당한다.(여기에서도 변수생성 바로가기 Ctrl+K 사용)
- 다음에 'Write Cell' Activity를 추가한다. Sheet를 "Sheet1"로 유지하고 범위(Range : sheet에 기록 할 위치)를 "C"+rowIndex.ToString으로 설정한다. Loop 전체에서 "C1", "C2" 그리고 세 번째 열 아래로 이동한다. Value를 valueC로 설정한다.

1.1 Sequence (Sequence)											
	Private = False										
	Activities										
		1.2 Excel Application Scope (ExcelApplicationScope)									
			WorkbookPath = Sample Columns.xlsx								
			Visible = True								
			CreateNewFile = True								
			AutoSave = True								
			ReadOnly = False								
			Private = False								
			Body								
				1.3 Do (Sequence)							
					Private = False						
					Variables						
						inputTable(DataTable)					
						rowIndex(Int32)					
					Activities						
						1.38 Read Range (ExcelReadRange)					
							DataTable = inputTable				
							AddHeaders = False				
							UseFilter = False				
							PreserveFormat(slower) = False				
							SheetName = Sheet1				
							Private = False				
						1.4 For Each Row (ForEachRow)					
							DataTable = inputTable				
							Private = False				
							Body				
								1.5 Body (Sequence)			
									Private = False		
									Variables		
										valueA(GenericValue)	
										valueB(GenericValue)	
										valueC(GenericValue)	
									Activities		
										1.30 Find current row index in excel (Assign)	
											To = rowIndex
											Value = inputTable.Rows.IndexOf(row) + 1
											Private = False
										1.24 Get row item A (GetRowItem)	
											Row = row
											ColumnIndex = 0
											Value = valueA
											Private = False
										1.18 Get row item B (GetRowItem)	
											Row = row
											ColumnIndex = 1
											Value = valueB
											Private = False
										1.12 Assign sum to value C (Assign)	
											To = valueC
											Value = valueA + valueB
											Private = False
										1.6 Write Value in C column of current row (ExcelWriteCell)	
											Range = "C" + rowIndex.ToString
											Value = valueC
											SheetName = Sheet1
											Private = False

✅ 참고사항

이 솔루션의 모든 activities는 Sample Columns.xlsx에 대한 전체 workbook 경로가 있는 Excel Application Scope 박스 안에 배치 되어야 한다. 'visible'옵션에 체크 되어있는지 확인한다.
변수 Type
- valueA, valueB, valueC : GenericValue
  - GenericValue : GenericValue변수는 텍스트, 숫자, 날짜 및 배열을 포함한 모든 종류의 데이터를 저장할 수 있는 변수 유형이며 UiPath Studio에만 해당된다.
- inputTable : DataTable
- rowIndex : Int32
'Read Range'activity Options - AddHeaders : 체크가 해제상태여야 한다.
- AddHeaders : 이 옵션을 선택하면 지정된 스프레드시트 범위의 Column Headers도 추출된다. 기본적으로 이 Checkbox는 선택되어있다. (Set to True)

Solution 2

Excel을 닫은 상태로 유지하며 DataTable 메모리에 column 값을 설정하고, 모든 DataTable을 한 번에 새 Excel 파일에 추가한다.

📌 설정방법

'Read Range' activity를 사용하여 Excel file을 읽는다. 전체 시트를 읽을 수 있도록 범위(Range)를 ""로 설정하고 Output 매개변수에서 단축키 Ctrl + K를 사용하여 'inputTable'이라는 DataTable 변수를 만든다.
'Add Data Column'을 사용하여 새 열 ("C")를 만들고 "inputTable"변수에 추가한다. 인수 유형을 'Object'로 설정했는지 확인한다.
'For Each Row' activity를 사용하여 "inputTable"을 반복하도록 설정한다. Body 안의 내용은 다음과 같다.
- 2개의 'Assign' activity를 사용하여 열"A" 및 열"B"의 내용을 각각 추출하고 row(Index).ToString을 사용하여 새로 생성된 변수("valueA" 및 "valueB")에 각각 저장한다. 'Index'는 열의 실제 index(이 경우 0 또는 1)로 대체된다.
- 다른 'Assign' activity를 사용하여 합계를 계산하고 'Integer.Parse' Method를 사용하여 DataTable의 세 번째 열에 저장한다. row(2) = Integer.Parse(valueA) + Integer.Parse(valueB);
'Write Range' activity를 사용하여 DataTable의 내용을 "Sample Columns - Completed.xlsx" 파일에 쓴다.

1.1 Sequence (Sequence)							
	Private = False						
	Variables						
		inputTable(DataTable)					
	Activities						
		1.2 Read Range (ReadRange)					
			DataTable = inputTable				
			AddHeaders = False				
			PreserveFormat = False				
			Workbook path = data\Sample Columns.xlsx				
			SheetName = Sheet1				
			Private = False				
		1.2 Add Data Column (AddDataColumn<Object>)					
			DataTable = inputTable				
			ColumnName = C				
			Private = False				
			TypeArgument = System.Object				
		1.2 For Each Row (ForEachRow)					
			DataTable = inputTable				
			Private = False				
			Body				
				1.3 Body (Sequence)			
					Private = False		
					Variables		
						valueA(GenericValue)	
						valueB(GenericValue)	
					Activities		
						1.4 Assign (Assign)	
							To = valueA
							Value = row(0).ToString
							Private = False
						1.4 Assign (Assign)	
							To = valueB
							Value = row(1).ToString
							Private = False
						1.4 Assign (Assign)	
							To = row(2)
							Value = Integer.Parse(valueA) + Integer.Parse(valueB)
							Private = False
		1.4 Write Range (WriteRange)					
			DataTable = inputTable				
			AddHeaders = False				
			SheetName = Sheet1				
			Workbook path = data\Sample Columns - completed.xlsx				
			Private = False

✅ 참고사항

이 솔루션에는 Excel Application Scope 컨테이너가 필요하지 않다.
여기서 'Read Range' activity는 'App Integration > Excel > Read Range'가 아닌, 'System > File > Workbook > Read Range' activity이다.

Solution 3

원본 파일의 Excel 수식을 사용하여 합계를 계산한다.

📌 설정방법

'Read Range' activity를 사용하여 Excel 파일을 읽는다. 전체 시트를 읽을 수 있도록 범위(Range)를 ""로 설정하고 Output 매개변수에서 단축키 Ctrl + K를 사용하여 "inputTable"이라는 DataTable 변수를 생성한다.
'Assign' activity를 사용하여 rows 수를 계산하고 새로 만든 변수에 저장한다. '.Rows.Count' Method를 사용한다. rowsCount = inputTable.Rows.Count;
'Write Cell' activity를 사용하여 "=SUM(A1,B1)"를 'C1:Cx'에 쓴다. 여기서 x는 "rowCount"에 저장된 총 행 수이다.

1.1 Sequence (Sequence)							
	Private = False						
	Activities						
		1.2 Excel Application Scope (ExcelApplicationScope)					
			Workbook path = data\Sample Columns.xlsx				
			Visible = True				
			Create if not exists = True				
			Save changes = True				
			Read-only = False				
			MacroSetting = EnableAll				
			InstanceCachePeriod = 3000				
			Private = False				
			Body				
				1.3 Do (Sequence)			
					Private = False		
					Variables		
						inputTable(DataTable)	
						rowsCount(GenericValue)	
					Activities		
						1.4 Read Range (ExcelReadRange)	
							DataTable = inputTable
							AddHeaders = False
							UseFilter = False
							PreserveFormat = False
							SheetName = Sheet1
							Private = False
						1.4 Assign (Assign)	
							To = rowsCount
							Value = inputTable.Rows.Count
							Private = False
						1.4 Write Cell (ExcelWriteCell)	
							Range = "C1:C"+rowsCount
							Value = =SUM(A1,B1)
							SheetName = Sheet1
							Private = False

✅ 참고사항

rowsCount 변수 Type은 GenericValue이다.
이 솔루션은 대부분 Excel 명령어를 사용하는 것을 포함한다. Excel Application Scope에 완전히 포함되어야 한다.
'.Rows' Method : 이 테이블에 속한 행의 컬렉션을 가져온다.
'.Count' Method : 시퀀스의 요소 수를 반환한다.

💡 결과

세 가지 방법으로 실행 한 결과 적용된 엑셀 파일에서 합계값을 확인할 수 있었다.

다른점은 Solution 3 으로 실행했을 때, 엑셀 파일에 수식이 적용되어 수식값까지 확인할 수 있었다는 점이다.

저작자표시 비영리 변경금지 (새창열림)