Excel and DataTables : DEMO 2 - Working with Excel files

출처 : UiPath Academy

DEMO 2 - Working with Excel files 2

데이터베이스에서 데이터를 필터링하고 결과를 새 파일로 보낸다.

• Age < 30
• Income > 100000.00

file 1 : Employees300.xlsx

 

Name	Age	Field	Work	Income
Adame,  Jorge A	31	General Services	Machinist (Automotive)	92248
Aguiar,  William M	36	Law	Asst Corporation Counsel Supvsr	106416
.
.
.
Ziomek,  Jeffrey G	39	Transportn	Pool Motor Truck Driver	71781

 

Excel and DataTables : Practice 2 - Working with Excel files

[ 참고 ]

• Filter Wizard의 Filter Rows 섹션의 Column 필드는 대소문자를 구분하므로 열의 정확한 이름을 입력해야한다.
• 비교는 이중 정수값을 사용하여 이루어 지므로 100000는 2개의 소수 값으로 작성된다는 점에 유의한다.

1.1 Sequence (Sequence)							
	Private = False						
	Variables						
		inputDT(DataTable)					
		achieversDT(DataTable)					
	Activities						
		1.50 Read Range employees (ReadRange)					
			DataTable = inputDT				
			AddHeaders = True				
			PreserveFormat = False				
			WorkbookPath = data\Employees300.xlsx				
			SheetName = Sheet1				
			Private = False				
		1.41 Filter Data Table (FilterDataTable)					
			DataTable = inputDT				
			SelectColumnsMode = Keep				
			FilterRowsMode = Keep				
			DataTable = inputDT				
			Private = False				
		1.38 Build Data Table achieversDT (BuildDataTable)					
			DataTable = achieversDT				
			Private = False				
		1.10 For Each Row (ForEachRow)					
			DataTable = inputDT				
			Private = False				
			Body				
				1.11 Body (Sequence)			
					Private = False		
					Variables		
						name(GenericValue)	
						age(GenericValue)	
						income(GenericValue)	
					Activities		
						1.30 Get Row Item name (GetRowItem)	
							Row = row
							ColumnName = Name
							Value = name
							Private = False
						1.24 Get Row Item age (GetRowItem)	
							Row = row
							ColumnName = Age
							Value = age
							Private = False
						1.19 Assign income (Assign)	
							To = income
							Value = row.ItemArray(4).toString
							Private = False
						1.12 Add Data to achieversDT (AddDataRow)	
							DataTable = achieversDT
							ArrayRow = {name, age, income}
							Private = False
		1.2 Excel Application Scope (ExcelApplicationScope)					
			WorkbookPath = data\results.xlsx				
			Visible = True				
			CreateNewFile = True				
			AutoSave = True				
			ReadOnly = False				
			Private = False				
			Body				
				1.3 Do (Sequence)			
					Private = False		
					Activities		
						1.4 Write Range (ExcelWriteRange)	
							StartingCell = A1
							DataTable = achieversDT
							AddHeaders = True
							SheetName = Sheet1
							Private = False

 

 

>> OUTPUT : results.xlsx

Name	Age	Income
Beiriger,  Terry J	24	117078
Bloome Jr,  Robert A	26	105918
Caraballo,  Alexander	25	116724
Deenihan,  Brendan D	25	116724
Edeling,  Timothy S	28	110370
Lopez,  Baudilio	24	106920
Mc Gee Jr,  John T	25	106920
Pope,  Terrence L	27	103590
Stachula,  Christophe D	23	106920
Tully,  Sean F	23	103590
Yonover,  Scott D	27	127824