...

Filter Data by Multi-Condition

 

Example

The Excel file "emp.xlsx" is an employee table, and part of the data is as follows:

..

We may filter the data by multiple conditions of name (NAME), birthday (BIRTHDAY), and department (DEPT). For example, find the record with the name Ryan and a birthday greater than 1980-01-01, and the result is as follows:

..

Write SPL script:

A

1

=if(name!="" &&   name,"NAME==name",true)

2

=if(dept!="" &&   dept,"DEPT==dept",true)

3

=if(bday!="" &&   bday,"BIRTHDAY>date(bday,\"yyyy/MM/dd\")",true)

4

=filter=[A1:A3].concat("&&")

5

=file("emp.xlsx").xlsimport@t().select(${filter})

6

=file("result.xlsx").xlsexport@t(A5)

 

The script parameters are set as follows:

 

..

A1 Spell the condition string for the name, and if the name parameter is a null value or an empty string, it will return true

 

A2 Spell the condition string for the department, and if the department parameter is a null value or an empty string, it will return true

 

A3 Spell the condition string for the birthday. If the birthday parameter is a null value or an empty string, it will return true

 

A4 Use && to concatenate these condition strings

 

A5 Read the employee table and filter the data

 

A6 Export the result to result.xlsx

 

 


 
 •  250 View  •