举例
有 Excel 文件 country.xlsx,数据如下所示:
UK | Data 1 | Instruction 1 |
UK | Data 2 | Instruction 2 |
UK | Data 3 | Instruction 3 |
USA | Data 4 | Instruction 4 |
USA | Data 5 | Instruction 5 |
India | Data 6 | Instruction 6 |
UAE | Data 7 | Instruction 7 |
UAE | Data 8 | Instruction 8 |
现在需要将每个分类 (A 列) 下除分类列外的每一列(B、C 列)转换为一行,结果如下:
UK | Data 1 | Data 2 | Data 3 |
UK | Instruction 1 | Instruction 2 | Instruction 3 |
USA | Data 4 | Data 5 | |
USA | Instruction 4 | Instruction 5 | |
India | Data 6 | ||
India | Instruction 6 | ||
UAE | Data 7 | Data 8 | |
UAE | Instruction 7 | Instruction 8 |
编写 SPL 脚本:
A | |
1 | =file("country.xlsx").xlsimport@w() |
2 | =A1.group@u(~(1)) |
3 | =A2.(transpose(~.(~.to(2,)))) |
4 | =A3.(~.(A2.(~(1)(1))(A3.#)|~)).conj() |
5 | =file("result.xlsx").xlsexport@w(A4) |
A1 读取 excel 文件内容,读成序列的序列
A2 按第一列(国家)分组
A3 把每个分组内的除国家列外,转置
A4 拼上国家,合并
A5 结果导出至 result.xlsx
以上是列转行的处理方式,转回去(行转列)也是这样处理。