POI合并单元格
合并插入列为合并开始列，同一行对象合并要使用开始行对象。
package com.yk.ln.util;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;import java.io.FileOutputStream;import java.util.*;/*** 生成按部门列表*/@SuppressWarnings("all")public class SXSSFUtil2 {//Y轴数据private List<String> yList ;//x轴数据private List<String> xList;//x尾部轴书private List<String> xTail;//当前行号/private int rowLine=1;//当前列号 只为title使用private int rowCol=0;//开始时间列private int fCol=0;//开始常量列private int constFCol=0;//结束列private int lCol=30;//存放行数据private Map<String,Object> rowMap = new HashMap<>();//列数据private Map<String,Object> colMap = new HashMap<>();//总计数据private Map<String,Object> totalMap = new HashMap<>();static Workbook workBook=null;/*** 工作区*/Sheet sheet = null;private int newLine=2;private int frow=0;private int lrow=2;public SXSSFUtil2(){this.init();}/*** TODO 写入Excel Title* @param titleName 月份 -部门名-收费统计* @param frow 起始行 从0开始 下一次为循环中 9+(列*2)* @param lrow 结束行 包含尾 9+(列*2)+2* @param fclo 起始列 从0开始 0* @param lcol 结束列 计算得出 4+x*5+8* @return*/public boolean writeTitle(String titleName,int frow,int lrow,int fclo,int lcol){if(null!=sheet){this.addMergedRegion(frow,lrow,fclo,lcol);Row row = sheet.createRow(frow);Cell titleCeil = row.createCell(fclo);CellStyle cellStyle = workBook.createCellStyle();Font font = workBook.createFont();font.setBold(true);cellStyle.setFont(font);titleCeil.setCellStyle(cellStyle);titleCeil.setCellValue(titleName);this.rowLine = lrow+1; //开启新行return true;}return false;}/*** 数据格式 Map中* time '具体日期' ，路段，指标，现金，电支 差额*//*** 写入Excel头* @param beginRow 序号账号对应的row* @param endRow 日期为* @param endCol* @param headMap* @return*/public boolean writeHead(int beginRow,List<Map<String,Object>> listMap){if(listMap == null){throw new IllegalArgumentException("listMap 不能为空！");}//序号账号Row constRow = null;writeHeadBefore(beginRow);int timeRowLine = this.rowLine-1;int i=0;if(null!=listMap){for(Map<String,Object> headMap:listMap){Set<Map.Entry<String, Object>> entries = headMap.entrySet();for(Map.Entry<String, Object> en:entries){String key = en.getKey();if(key.equals("time")){ //时间合并Object pdaTime = en.getValue();if(timeRow == null){timeRow = sheet.createRow(timeRowLine); //需要-1}// System.out.println(this.fCol+": this.fClol");this.addMergedRegion(timeRowLine,timeRowLine,this.fCol==4?4:this.fCol,this.fCol+4);Cell timeRowCell = timeRow.createCell(this.fCol == 4 ? 4 : this.fCol);timeRowCell.setCellValue(en.getValue().toString());fontMiddle(timeRowCell,workBook,false,false);this.fCol=this.fCol+5; //时间的轴}else {//普通常量if(null==constRow){constRow = sheet.createRow(this.rowLine);}Cell cosntHeadCell = constRow.createCell(this.constFCol++);cosntHeadCell.setCellValue(en.getKey());if(key.equals("差额")){fontMiddle(cosntHeadCell,workBook,false,true);}else {fontMiddle(cosntHeadCell,workBook,false,false);}}}}}this.addMergedRegion(timeRowLine,timeRowLine,this.fCol==4?4:this.fCol,this.fCol+1);Cell timeRowCell = timeRow.createCell(this.fCol);this.fCol=this.fCol+1;timeRowCell.setCellValue("小计金额");fontMiddle(timeRowCell,workBook,false,false);Cell cosntHeadCell = constRow.createCell(this.constFCol++);cosntHeadCell.setCellValue("现金");fontMiddle(cosntHeadCell,workBook,false,false);cosntHeadCell = constRow.createCell(this.constFCol++);cosntHeadCell.setCellValue("电支");fontMiddle(cosntHeadCell,workBook,false,false);appendTail(this.timeRow,workBook,sheet,this.rowLine-1,this.fCol,TestSXSSDomain.tailMap);return true;}/*** 合并单元格* @param beginRow 开始行号* @param endRow 结束行行* @param beginCol 开始列* @param endCol 结束列*/public void addMergedRegion(int beginRow,int endRow,int beginCol,int endCol){sheet.addMergedRegion(new CellRangeAddress(beginRow,endRow,beginCol,endCol));}//当前绘制的HeadRowprivate Row timeRow = null;public boolean writeBody(List<List<String>> e ){if(e.size()==0){return true;}//重置fcolthis.fCol = 0;//前两个占2行2列int eachIndex= 0;int synNumber = 0;int syn2=0;Cell cell = null;int each = 0 ;for(List<String> e2:e){syn2++;this.fCol=0;synNumber = 0;this.rowLine = this.rowLine+1;Row row = sheet.createRow(this.rowLine);int size = e2.size();for (int i = 0; i < size; i++) {synNumber++;if(synNumber==1){this.fCol+=3;row.setHeight((short)(520));this.addMergedRegion(this.rowLine,this.rowLine,0,1); //序号cell =row.createCell(0);cell.setCellValue(syn2);this.addMergedRegion(this.rowLine,this.rowLine,2,3);//姓名cell= row.createCell(2);fontMiddle(cell,workBook,false,false);cell.setCellValue(e2.get(i));//插入姓名-登陆名increatemntFCol();continue;}if(i==size-4) {this.addMergedRegion(this.rowLine,this.rowLine,this.fCol++,this.fCol);Cell cell1 = row.createCell(this.fCol-1);fontMiddle(cell1,workBook,false,false);cell1.setCellValue(e2.get(i));increatemntFCol();this.addMergedRegion(this.rowLine,this.rowLine,this.fCol++,this.fCol);cell1 = row.createCell(this.fCol-1);fontMiddle(cell1,workBook,false,false);cell1.setCellValue(e2.get(i+1));increatemntFCol();this.addMergedRegion(this.rowLine,this.rowLine,this.fCol++,this.fCol);cell1 = row.createCell(this.fCol-1);fontMiddle(cell1,workBook,false,false);cell1.setCellValue(e2.get(i+2));increatemntFCol();this.addMergedRegion(this.rowLine,this.rowLine,this.fCol++,this.fCol);cell1 = row.createCell(this.fCol-1);fontMiddle(cell1,workBook,false,false);cell1.setCellValue(e2.get(i+3));break;}else {row.createCell(this.fCol++).setCellValue(e2.get(i));}}}//尾部合并 Y轴合并 现阶段不做 时间紧 绘制已经完成// appendHeadTail(this.rowLine);neline();return true;}/*private void appendHeadTail(int rowLine) {//移入下一行this.rowLine = rowLine+1;System.out.println(this.rowLine+"appendHeadTail");//测试listList<String> tailList = new ArrayList<>();tailList.add("1");tailList.add("2");Row row = sheet.createRow(this.rowLine);this.addMergedRegion(this.rowLine,this.rowLine,0,3);Cell xiaojijine = row.createCell(0);xiaojijine.setCellValue("小计金额");fontMiddle(xiaojijine,workBook,false,false);//填充Y轴合并数 从第4列开始this.fCol = 4;Cell cell = null;//TODO !!!!!!44444444444!!!!!!!! 这里要插入尾部小计金额数据for(String data:tailList){System.out.println(this.fCol+"::11");cell = row.createCell(this.fCol);cell.setCellValue(data);fontMiddle(cell,workBook,false,false);increatemntFCol();}//生成合计金额incrementRoadLine();this.addMergedRegion(this.rowLine,this.rowLine+1,0,3);Cell hejinjiner = sheet.createRow(this.rowLine).createCell(0);fontMiddle(hejinjiner,workBook,false,false);hejinjiner.setCellValue("合计金额");//TODO !!!!555555555555!!!!!!!!!! 这里要插入尾部合计金额数据incrementRoadLine();}*//*** 行自增*/private void incrementRoadLine() {this.rowLine=this.rowLine+1;}/*** 列自增*/private void increatemntFCol() {this.fCol++;}/*** 得到计算列* @param headSize* @return*/public int getTitleSize(int headSize){if(headSize==0){return this.lCol+4-1;}return 4+(headSize*5)+8-1+2;}/*** 写出表格* @param fileName*/public void write(String fileName){FileOutputStream fileOut1 = null;try {String fileAbsPath = Constant.EXportPath+fileName+".xlsx";File f = new File(fileAbsPath);if(f.exists()){f.delete();}fileOut1 = new FileOutputStream(fileAbsPath);workBook.write(fileOut1);fileOut1.flush();fileOut1.close();workBook.close();}catch (Exception e){e.printStackTrace();}}/*** TODO 文字居中 是否加粗 是否带颜色* @param cell 单元格* @param w 对象* @param bold 是否加粗* @param color 是否带颜色*/public static void fontMiddle(Cell cell,Workbook w,boolean bold,boolean color){CellStyle cellStyle = w.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);if(bold||color){Font font = workBook.createFont();if(bold){font.setBold(true);}if(color){font.setColor(HSSFFont.COLOR_RED);}cellStyle.setFont(font);}cell.setCellStyle(cellStyle);}/*** TODO 写入序号账号endRow为 beginRow+1*/private void writeHeadBefore(int beginRow) {int endRow = beginRow+1; //起新行Row row = sheet.createRow(beginRow);/* System.out.println(beginRow+":beginRow");System.out.println(endRow+":endRow");*/this.rowLine = endRow; //行+1 为下一次合并做准备Set<Map.Entry<String, Integer>> entries = TestSXSSDomain.headMaps.entrySet();for(Map.Entry<String, Integer> entry:entries){String key = entry.getKey();if(key.equals("序号")){this.addMergedRegion(beginRow,endRow,0,1);Cell synCell = row.createCell(0);synCell.setCellValue(key);fontMiddle(synCell,workBook,false,false);}else {if(key.equals("账号")){this.addMergedRegion(beginRow,endRow,2,3);Cell userCountCell = row.createCell(2);userCountCell.setCellValue(key);fontMiddle(userCountCell,workBook,false,false);}}}// 时间轴this.fCol=4;//字段轴this.constFCol=4;timeRow = row;}/*** TODO 1 :初始化workSheet* @return*/public Sheet init(){workBook = new XSSFWorkbook();sheet = workBook.createSheet("工作业绩");return sheet;}/***TODO x轴 尾部追加*/public void appendTail(Row row1,Workbook workBook,Sheet sheet,int row,int col,Map<String,String> dataMap){col=col+1;Set<Map.Entry<String, String>> entries = dataMap.entrySet();for(Map.Entry<String, String> entry:entries){String key = entry.getKey();//1:创建headthis.addMergedRegion(row,row+1,col,col+1);Cell cell = row1.createCell(col);cell.setCellValue(key);col=col+2;if(key.equals("差额合计")){fontMiddle(cell,workBook,false,true);}else {fontMiddle(cell,workBook,false,false);}}}/*** 换行操作开启下一轮*/private void neline(){this.rowLine = this.rowLine+2;this.fCol=0;}}
/*** 导出功能* @param paramMap 请求参数 Month ---- startTime 和 endTime* @param req 获取能看到的部门集合Id* @return*/@ResponseBody@RequestMapping("/export")public Object export(@RequestBody Map<String,Object> paramMap,HttpServletRequest req) {try {String year = paramMap.get("year").toString();String month = paramMap.get("month").toString();paramMap.put("DeptIds",req.getSession().getAttribute("DeptIds"));SXSSFUtil2 sx = new SXSSFUtil2();String yearAndMonth = year+"-"+(Integer.parseInt(month)>10?month:"0"+month);String data = year+"年-"+month+"月-";int frowEach=0;List<Map<String, Object>> mapList = financialDao.selectPdaIdsByGroupDeptId(paramMap);String fileName=yearAndMonth+"部门汇总";int lastDayOfMonth = DateUtils.getLastDayOfMonth();for(Map<String, Object> m:mapList){String pdaUserId = m.get("pdaUserId").toString();String DeptName = m.get("DeptName").toString()+"-";String excel = getExcelTitleName(DeptName, data); //得到excelTitleNameparamMap.put("startTime",yearAndMonth+"-01 00:00:01");paramMap.put("endTime", yearAndMonth+"-"+lastDayOfMonth+" 23:59:59");paramMap.put("pdaIds","("+pdaUserId+")");List<Map<String, Object>> headMap = ExcelUtils.getHeadMap(lastDayOfMonth); //获取ExcelHeadMapList<List<String>> e = new ArrayList<>(); //Excel所需要的BODY数据List<Map<String, Object>> userMapList = financialDao.analayGroupByDept(paramMap);Map<String,List<String>> udata = new LinkedHashMap<>();fillListMapToMapList(userMapList,udata);Set<Map.Entry<String, List<String>>> entries = udata.entrySet();List<String> a = new ArrayList<>();for(Map.Entry<String, List<String>> entry:entries){List<String> p = entry.getValue();p = pletionList(p, lastDayOfMonth,a);e.add(p);}int frow =0;int lrow=2;if(frowEach!=0){frow=sx.getRowLine();lrow=frow+2;}frowEach++;int size = headMap.size();sx.writeTitle(excel,frow,lrow,sx.getfCol(), sx.getTitleSize(size));sx.writeHead(sx.getRowLine(),headMap);sx.writeBody(e);}sx.write(fileName);String userAccessPath = null;if (req.getScheme().equals("https")) {userAccessPath = Constant.FILEDOWNLOADHTTPS + fileName + ".xlsx";} else {userAccessPath = Constant.FILEDOWNLOADHTTP + fileName + ".xlsx";}Map<String,Object> rMap = new HashMap<>();rMap.put("path",userAccessPath);return R.ok(rMap);}catch (Exception e){e.printStackTrace();return R.error();}}/*** 将List转换成map key为登陆用户 value 为 List的值* @param userMapList 数据库查询出的数据 Map Key 为 riqi zhibiao luduan yonghuming dengluming xianjin dianzhi* @param udata 转换后的map*数据格式：*(dengluming,数据库中该登陆名所有行数据)*/private void fillListMapToMapList(List<Map<String,Object>> userMapList, Map<String,List<String>> udata) {/* System.out.println("fillListMapToMapList");System.out.println(userMapList);System.out.println("fillListMapToMapList");*/if(userMapList.size()==0 || null == userMapList){return ;}/*进行转换*/for(int i=0;i<userMapList.size();i++){Map<String, Object> p = userMapList.get(i);//得到该mapString yonghuming=p.get("yonghuming").toString(); //游佐勇String dengluming=p.get("dengluming").toString(); //kg0069String day = p.get("riqi").toString(); //-06-02String zhibiao= p.get("zhibiao").toString(); //0String luduan=p.get("luduan").toString(); // 高岩路8String xianjin=p.get("xianjin").toString(); //0.00String dianzhi=p.get("dianzhi").toString(); //296.00String chae = (Double.valueOf(xianjin==null?"0":xianjin)+Double.valueOf(dianzhi==null?"0":dianzhi)-Double.valueOf(zhibiao))+"";if(udata.containsKey(yonghuming.trim())){putDbDataToList(udata.get(yonghuming),day,zhibiao,luduan,xianjin,dianzhi,chae);}else {List<String>uinfoData = new ArrayList<>();putDbDataToList(uinfoData,day,zhibiao,luduan,yonghuming,dengluming,xianjin,dianzhi,chae);udata.put(yonghuming.trim(),uinfoData);}}}private void putDbDataToList(List<String> resultList, String day, String zhibiao, String luduan, String yonghuming, String dengluming, String xianjin, String dianzhi,String chae) {resultList.add(day);resultList.add(yonghuming+":"+dengluming);resultList.add(luduan);resultList.add(zhibiao);resultList.add(xianjin);resultList.add(dianzhi);resultList.add(chae);}private void putDbDataToList(List<String> resultList, String day, String zhibiao,String luduan, String xianjin, String dianzhi, String chae) {resultList.add(day);resultList.add(luduan);resultList.add(zhibiao);resultList.add(xianjin);resultList.add(dianzhi);resultList.add(chae);}public static final String TEMPPAY="-收费统计";public static String getExcelTitleName(String deptName,String data){return data+deptName+TEMPPAY;}
