JSA代码,供参考
- function 表格分组转换(){
- const arr=Sheets.Item("Sheet1").Range("a1").CurrentRegion.Value2.slice(1);
- const regex=/(?<=\().+(?=\))/;
- let mbarr,bt,k=0;
- let res=arr.reduce((res,x)=>{
- if (typeof(x[3])=="string"){
- mbarr=[x[0],x[1],x[2]],bt=x;
- return res;
- }
- return (bt.forEach((y,j)=>(j>2 && y!=null)?res.push([++k,...mbarr,x[2],y,y.match(regex)[0],x[j]]):null),res);
- },[["序号","楼层","房间","构件类型","构件名称","构件代码","单位","工程量"]]);
- Range("m1").CurrentRegion.EntireColumn.Delete();
- let rng=Range("m1").Resize(res.length,res[0].length);
- rng.Value2=res;
- rng.Borders.LineStyle=1,rng.EntireColumn.AutoFit(); //设置格式
- }
复制代码 |