java poi 模板填数据库_POI使用模板

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);

}

}

}

}