Shuffle the Data Order

Example

There is an Excel file Book1.xlsx, and the data is as follows:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

 

The task is to shuffle the order of data in specified area (the whole area, in the row, or in the column)

Write SPL script:


A

1

=file("Book1.xlsx").xlsimport@w()

2

/shuffle whole area

3

=A1.conj().sort(rand()).group(#%5)

4

=file("total.xlsx").xlsexport@w(A3)

5

/ shuffle in row

6

=A1.(~.sort(rand()))

7

=file("row.xlsx").xlsexport@w(A6)

8

/ shuffle in column

9

=transpose(A1).(~.sort(rand()))

10

=transpose(A9)

11

=file("col.xlsx").xlsexport@w(A10)

A1 Read the excel file and read it as a sequence of sequences

 

A2 Annotation

 

A3 Combine the sequence of sequences into one sequence, and after shuffling the sequence, group the new sequence according to the sequence of the original sequences

 

A4 Shuffle the order of the entire area and export it to total.xlsx. The result is as follows:

11

12

20

10

4

14

5

1

15

16

8

7

9

13

2

3

17

19

18

6

 

A5 Annotation

 

A6 Shuffle the data of each row

 

A7 Shuffle the order in the row and export it to row.xlsx. The result is as follows:

4

2

3

1

6

5

7

8

9

12

10

11

16

14

15

13

18

20

17

19

 

A8 Annotation

 

A9 Transpose this area of data and then shuffle the order within the row

 

A10 Transpose the result of A9 again

 

A11 Shuffle the order in the column and export it to col.xlsx. The result is as follows:

13

6

7

8

9

18

19

12

1

2

15

4

5

14

3

20

17

10

11

16