importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.InputStream;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.List;importjava.util.Map;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.VerticalAlignment;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.usermodel.XSSFCellStyle;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.slf4j.Logger;importcom.yusys.innermanager.utils.export.ExportUtil;importcom.yusys.innermanager.utils.export.WDWUtil;public classExcelMergeUtil {private String srcXlsPath="";private String fileName="";private String sheetName="";
POIFSFileSystem fs=null;
Workbook wb= null;
Sheet sheet= null;private Logger logger = org.slf4j.LoggerFactory.getLogger(ExportUtil.class);public ExcelMergeUtil(String srcXlsPath,String fileName,String sheetName){//标题 列名
this.srcXlsPath=srcXlsPath;this.setFileName(fileName);this.sheetName=sheetName;
getSheet();
}publicExcelMergeUtil(){}public voidsetSrcXlsPath(String srcXlsPath) {this.srcXlsPath =srcXlsPath;
}public voidsetFileName(String fileName) {this.fileName =fileName;
}public voidsetSheetName(String sheetName) {this.sheetName =sheetName;
}publicString getFileName() {returnfileName;
}//获得工作表sheet
public voidgetSheet() {try{
InputStream input= ExportUtil.class.getResourceAsStream(srcXlsPath);if(input==null){
logger.info("模板文件:"+srcXlsPath+"不存在");return;
}if(WDWUtil.isExcel2003(srcXlsPath)){
wb= newHSSFWorkbook(input);
}if(WDWUtil.isExcel2007(srcXlsPath)){
wb= newXSSFWorkbook(input);
}
sheet=wb.getSheet(sheetName);
}catch(Exception e){
logger.error("捕获异常",e);
}
}/*** 写进IO流
*@parampath 文件存放位置
*@paramfileName 文件名,包括文件后缀
*@throwsIOException*/
public void exportExcel2007( String path , String fileName) throwsIOException{
FileOutputStream fileOutputStream= new FileOutputStream(path +fileName);
wb.write(fileOutputStream);
fileOutputStream.close();
}/*** 创建行
*@paramlist 从数据库中查到的数据
*@paramargs 数据的字段名
*@paramfirst 从第几行开始创建*/
public void myCreateRow(List> list ,String args[] ,intfirst){for(int i=0;i
Cell cellNum = row.createCell(0); //创建行的第一个单元格
cellNum.setCellValue(i+1);for(int j = 0 ; j < args.length ; j++ ){ //遍历map 即excel中的一行
Cell cell = row.createCell(j+1); //创建行的第一个单元格
cell.setCellValue(list.get(i).get(args[j]));
}
}//countSum(list, args, first );
}/***
*@paramsheet
*@paramlist 传入列表
*@paramargs 数据库字段(可以不用传进来)
*@parammergeArgs 判断是否需要合并的参数(销售点代码)
*@paramsumCol 想要计算合计的列名(月租金)
*@paramfirst 数据从第几行开始
*@paramwriteSumCol 合计租金所在列数*/
public void myMerge(List> list, String mergeArgs ,String sumCol, int first ,intwriteSumCol){
String same= list.get(0).get(mergeArgs); //拿到合并参数的第一个值
int firstRow = first; //合并的第一行行数
int lastRow = 0; //合并的最后一行行数
int sumRent = 0; //合计租金
for(int i=0;i
if(same != list.get(i).get(mergeArgs) ){ //如果发生了不同,进入更新 合并参数和same的过程
same = list.get(i).get(mergeArgs) ; //更新same的值
lastRow = i + first-1;//拿到单元格,把数值放到第一列
Row mergeRow =sheet.getRow(firstRow);
Cell mergeCell=mergeRow.createCell(writeSumCol);
mergeCell.setCellValue(sumRent);//合并单元格
if( (lastRow - firstRow)>0 ){ //如果行数大于一才合并
CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, writeSumCol, writeSumCol); //初始行,终止行,初始列,终止列
sheet.addMergedRegion(region);
}//合计月租更新+牌照费
sumRent = Integer.valueOf(list.get(i).get(sumCol)) + Integer.valueOf(list.get(i).get("LICENSE_FEE"));
firstRow= i+first;//如果相同,则更新合计租金
}else if(same==list.get(i).get(mergeArgs)){
sumRent= sumRent + Integer.valueOf(list.get(i).get(sumCol)) +Integer.valueOf(list.get(i).get("LICENSE_FEE")) ;
}//最后一行单独合并 & 合并到最后一行
if( i ==list.size()-1 && same==list.get(i).get(mergeArgs)){//拿到单元格,把数值放到第一列
Row mergeRow =sheet.getRow(firstRow);
Cell mergeCell=mergeRow.createCell(writeSumCol);
mergeCell.setCellValue(sumRent);if( i - firstRow +first> 0){
CellRangeAddress region= new CellRangeAddress(firstRow,i+first, writeSumCol, writeSumCol); //初始行,终止行,初始列,终止列
sheet.addMergedRegion(region);
}
}
}
}