JXLS模板设置分组求和折叠合并

1、Age:$i：group.item.age11ine也tmiI二*fm礙forEachitems=z$group,iternsvar二emialoyee囂日rSt日tus二st日1：115-令Status；index+1i$empISiemt：馥J农：fo.rEach-.效果图:类别名称Age:.01组1张三遨李四Age：迪组Age:饷组1张三Age:0堆且JforEmc：hiterns二$怙叩!;grouEy二耳A分组求和类别名称forEachitems=z/$emp1乂总电冷.groupBy=/yae/Age:group,item.agSl.&tatus.indeK-+1

2、$empIQV$em|2效甲图-遂?Jx：f?+1$empIS(empIoyee.payment$(empIWj：总forEach:小计SSUM(06)$COUNrEac贬效果图：合计$SUM(C8)$UM序号名称数量其Age:01组1张三1111通用2同于小计1333Age:百2组1张三1111通用2李四宓同于3王五1111通用4i马六同于小计2666Age:03组1张三1111通用中计1111Age:0继且1张三1111通用2李四222同于3王五11114马六222-同于5血启邂分组求和类别序号名称敎量其forEachitiempIQy.egZJsroupB

3、y=age捞/ar=/yempIoeevrStatusstatus錢Age:$grQup.item.age：$.gt日tu0.index?+1t$(empI：,$.empfoyee.payment$employe*forEach中计$SUM(D5)$COUNT仏吏yE西hg效果图：合计$SUM(D7)$SUM(类别序号名称数量其Age:01组1永三1111通用Age:01组2李四卫姿同于中计1333Age:02组1张三1111通用Age:02组2222同于Age:蹩组3王五1111通用Age:02组4昌六同于小计26664Age:饷组1张三1111通用小计11111Age:04

4、组彳张三1111通用Age:0继目2李四：222同于Age:CM组3王五1111通用Age:04*14222：同于Age:04组5赵启渔阖于d小计$COUNT05)-1$SUM(D5)八ky木3：合计$亂MO$SUM(D7)按Ctrl+Shift+A执行合并笫一列类别序号名称数量其Age:01组1张三1111通用2李四222同于小计1333Age:.02组1张三1111通用2李四222同于3.王五1111通用4滋同于小计42666Age:03组1张三1111通用小计11111Age:0继且1.张三1111通用2|辜四蕊|同于3王五1111通用4马六222同于5赵启泌同于小计52888合计127

5、998SubMyMerge0Dim1%Application.DisplayAlerts=FalseForI=A65536.End(3).RowTo3Step-1IfCellstl,1)二Cellstl-1,1)ThenRange(AfeI&I-1).MergeRange&I&:A&I一1.HoriEcirrtal盧ignm亡nt=Excel.XlHAlign.xl.HAlignCenterRange(A&I&:止&I一1).V亡rtiual恵1ignm亡nt=Excel.XlVAlign.xlVAlignCenterEndIfNextApplication.DisplayAlerts二Tru

6、eExcel的VBA宏：SubMyMerge()DimI%Application.DisplayAlerts=FalseForI=A65536.End(3).RowTo3Step-1IfCells(I,1)=Cells(I-1,1)ThenRange(A&I&：A&I-1).MergeRange(A&I&：A&I-1).HorizontalAlignment=Excel.XlHAlign.xlHAlignCenterRange(A&I&：A&I-1).VerticalAlignment=Excel.XlVAlign.xlVAlignCenterEndIfNextApplication.Disp

7、layAlerts=TrueEndSub数据填充：importjava.io.IOException;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importnet.sf.jxls.exception.ParsePropertyException;importnet.sf.jxls.transformer.XLSTransformer;importorg.apache.poi.openxml4j.exceptions.InvalidFormatExcepti

8、on;publicclassJxlsGrouppublicstaticvoidmain(Stringargs)throwsParsePropertyException,InvalidFormatException,IOExceptionListdepartment=newArrayList();HashMapemployee;employee=newHashMap();employee.put(age,01组);employee.put(name,张三);employee.put(payment,1111);employee.put(bonus,通用);department.add(emplo

9、yee);employee=newHashMap();employee.put(age,01组);employee.put(name,李四);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);employee=newHashMap();employee.put(age,02组);employee.put(name,张三);employee.put(payment,1111);employee.put(bonus,通用);department.add(employee);employee=newHa

10、shMap();employee.put(age,02组);employee.put(name,李四);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);employee=newHashMap();employee.put(age,02组);employee.put(name,王五);employee.put(payment,1111);employee.put(bonus,通用);department.add(employee);employee=newHashMap();employee.pu

11、t(age,02组);employee.put(name,马六);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);employee=newHashMap();employee.put(age,03组);employee.put(name,张三);employee.put(payment,1111);employee.put(bonus,通用);department.add(employee);employee=newHashMap();employee.put(age,04组);employee

12、.put(name,张三);employee.put(payment,1111);employee.put(bonus,通用);department.add(employee);employee=newHashMap();employee.put(age,04组);employee.put(name,李四);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);employee=newHashMap();employee.put(age,04组);employee.put(name,王五);emplo

13、yee.put(payment,1111);employee.put(bonus,通用);department.add(employee);employee=newHashMap();employee.put(age,04组);employee.put(name,马六);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);employee=newHashMap();employee.put(age,04组);employee.put(name,赵启);employee.put(payment,222);employee.put(bonus,同于);department.add(employee);Mapbeans=newHashMap();beans.put(employees,department);StringdestFileName=C:/C/待退人员花名册1.xls;StringtemplateFileName=;templateFileName=C:/C/00分组求和折叠.xls;templateFileName=C:/C/01分