JXLS模板設(shè)置分組求和折疊合并

1、Age:$i：group.item.age11ine也tmiI二*fm礙forEachitems=z$group,iternsvar二emialoyee囂日rSt日tus二st日1：115-令Status；index+1i$empISiemt：馥J農(nóng)：fo.rEach-.效果圖:類(lèi)別名稱(chēng)Age:.01組1張三遨李四Age：迪組Age:餉組1張三Age:0堆且JforEmc：hiterns二$怙叩!;grouEy二耳A分組求和類(lèi)別名稱(chēng)forEachitems=z/$emp1乂總電冷.groupBy=/yae/Age:group,item.agSl.&tatus.indeK-+1

2、$empIQV$em|2效甲圖-遂?Jx：f?+1$empIS(empIoyee.payment$(empIWj：總forEach:小計(jì)SSUM(06)$COUNrEac貶效果圖：合計(jì)$SUM(C8)$UM序號(hào)名稱(chēng)數(shù)量其Age:01組1張三1111通用2同于小計(jì)1333Age:百2組1張三1111通用2李四宓同于3王五1111通用4i馬六同于小計(jì)2666Age:03組1張三1111通用中計(jì)1111Age:0繼且1張三1111通用2李四222同于3王五11114馬六222-同于5血啟邂分組求和類(lèi)別序號(hào)名稱(chēng)敎量其forEachitiempIQy.egZJsroupB

3、y=age撈/ar=/yempIoeevrStatusstatus錢(qián)Age:$grQup.item.age：$.gt日tu0.index?+1t$(empI：,$.empfoyee.payment$employe*forEach中計(jì)$SUM(D5)$COUNT仏吏yE西hg效果圖：合計(jì)$SUM(D7)$SUM(類(lèi)別序號(hào)名稱(chēng)數(shù)量其Age:01組1永三1111通用Age:01組2李四衛(wèi)姿同于中計(jì)1333Age:02組1張三1111通用Age:02組2222同于Age:蹩組3王五1111通用Age:02組4昌六同于小計(jì)26664Age:餉組1張三1111通用小計(jì)11111Age:04

4、組彳張三1111通用Age:0繼目2李四：222同于Age:CM組3王五1111通用Age:04*14222：同于Age:04組5趙啟漁闔于d小計(jì)$COUNT05)-1$SUM(D5)八ky木3：合計(jì)$亂MO$SUM(D7)按Ctrl+Shift+A執(zhí)行合并笫一列類(lèi)別序號(hào)名稱(chēng)數(shù)量其Age:01組1張三1111通用2李四222同于小計(jì)1333Age:.02組1張三1111通用2李四222同于3.王五1111通用4滋同于小計(jì)42666Age:03組1張三1111通用小計(jì)11111Age:0繼且1.張三1111通用2|辜四蕊|同于3王五1111通用4馬六222同于5趙啟泌同于小計(jì)52888合計(jì)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數(shù)據(jù)填充：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/待退人員花名冊(cè)1.xls;StringtemplateFileName=;templateFileName=C:/C/00分組求和折疊.xls;templateFileName=C:/C/01分