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));
}
//当前绘制的HeadRow
private Row timeRow = null;
public boolean writeBody(List<List<String>> e ){
if(e.size()==0){
return true;
}
//重置fcol
this.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");
//测试list
List<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:创建head
this.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); //得到excelTitleName
paramMap.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); //获取ExcelHeadMap
List<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 = Completion.CompletionList(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);
//得到该map
String yonghuming=p.get("yonghuming").toString(); //游佐勇
String dengluming=p.get("dengluming").toString(); //kg0069
String day = p.get("riqi").toString(); //2019-06-02
String zhibiao= p.get("zhibiao").toString(); //0
String luduan=p.get("luduan").toString(); // 高岩路8
String xianjin=p.get("xianjin").toString(); //0.00
String dianzhi=p.get("dianzhi").toString(); //296.00
String 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;
}