基于apache poi根据模板导出excel的实现方法
需要预先新建编辑好一个excel文件，设置好样式。
编辑好输出的数据，根据excel坐标一一对应。
支持列表数据输出，列表中列合并。
代码如下：
packagecom.icourt.util;
importorg.apache.commons.collections4.CollectionUtils;
importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;
importorg.apache.poi.ss.usermodel.*;
importorg.apache.poi.ss.util.CellRangeAddress;
importjava.io.*;
importjava.util.ArrayList;
importjava.util.HashMap;
importjava.util.List;
importjava.util.Map;
importjava.util.Map.Entry;
importjava.util.regex.Matcher;
importjava.util.regex.Pattern;
/**
*描述:poi根据模板导出excel,根据excel坐标赋值,如(B1)
*/
publicclassExcelExportUtil{
//模板map
privateMaptempWorkbook=newHashMap();
//模板输入流map
privateMaptempStream=newHashMap();
/**
*功能:按模板向Excel中相应地方填充数据
*/
publicvoidwriteData(StringtemplateFilePath,MapdataMap,intsheetNo)throwsIOException,InvalidFormatException{
if(dataMap==null||dataMap.isEmpty()){
return;
}
//读取模板
WorkbookwbModule=getTempWorkbook(templateFilePath);
//数据填充的sheet
Sheetwsheet=wbModule.getSheetAt(sheetNo);
for(Entryentry:dataMap.entrySet()){
Stringpoint=entry.getKey();
Objectdata=entry.getValue();
TempCellcell=getCell(point,data,wsheet);
//指定坐标赋值
setCell(cell,wsheet);
}
//设置生成excel中公式自动计算
wsheet.setForceFormulaRecalculation(true);
}
/**
*功能:按模板向Excel中列表填充数据.只支持列合并
*/
publicvoidwriteDateList(StringtemplateFilePath,String[]heads,List>datalist,intsheetNo)throwsIOException,InvalidFormatException{
if(heads==null||heads.length<=0||CollectionUtils.isEmpty(datalist)){
return;
}
//读取模板
WorkbookwbModule=getTempWorkbook(templateFilePath);
//数据填充的sheet
Sheetwsheet=wbModule.getSheetAt(sheetNo);
//列表数据模板cell
ListtempCells=newArrayList(heads.length);
for(Stringpoint:heads){
TempCelltempCell=getCell(point,null,wsheet);
//取得合并单元格位置-1:表示不是合并单元格
intpos=isMergedRegion(wsheet,tempCell.getRow(),tempCell.getColumn());
if(pos>-1){
CellRangeAddressrange=wsheet.getMergedRegion(pos);
tempCell.setColumnSize(range.getLastColumn()-range.getFirstColumn());
}
tempCells.add(tempCell);
}
//赋值
for(inti=0;idataMap=datalist.get(i);
for(intj=0;j-1){
CellRangeAddressrangeAddress=mergeRegion(sheet,tempCell.getRow(),tempCell.getRow(),tempCell.getColumn(),tempCell.getColumn()+tempCell.getColumnSize());
setRegionStyle(tempCell.getCellStyle(),rangeAddress,sheet);
}
RowrowIn=sheet.getRow(tempCell.getRow());
if(rowIn==null){
copyRows(tempCell.getRow()-1,tempCell.getRow()-1,tempCell.getRow(),sheet);//复制上一行
rowIn=sheet.getRow(tempCell.getRow());
}
CellcellIn=rowIn.getCell(tempCell.getColumn());
if(cellIn==null){
cellIn=rowIn.createCell(tempCell.getColumn());
}
//根据data类型给cell赋值
if(tempCell.getData()instanceofString){
cellIn.setCellValue((String)tempCell.getData());
}elseif(tempCell.getData()instanceofInteger){
cellIn.setCellValue((int)tempCell.getData());
}elseif(tempCell.getData()instanceofDouble){
cellIn.setCellValue((double)tempCell.getData());
}else{
cellIn.setCellValue((String)tempCell.getData());
}
//样式
if(tempCell.getCellStyle()!=null&&tempCell.getColumnSize()==-1){
cellIn.setCellStyle(tempCell.getCellStyle());
}
}
/**
*功能:写到输出流并移除资源
*/
publicvoidwriteAndClose(StringtemplateFilePath,OutputStreamos)throwsIOException,InvalidFormatException{
if(getTempWorkbook(templateFilePath)!=null){
getTempWorkbook(templateFilePath).write(os);
tempWorkbook.remove(templateFilePath);
}
if(getInputStream(templateFilePath)!=null){
getInputStream(templateFilePath).close();
tempStream.remove(templateFilePath);
}
}
/**
*功能:判断指定的单元格是否是合并单元格
*/
privateIntegerisMergedRegion(Sheetsheet,introw,intcolumn){
for(inti=0;i=firstRow&&row<=lastRow){
if(column>=firstColumn&&column<=lastColumn){
returni;
}
}
}
return-1;
}
/**
*功能:合并单元格
*/
privateCellRangeAddressmergeRegion(Sheetsheet,intfirstRow,intlastRow,intfirstCol,intlastCol){
CellRangeAddressrang=newCellRangeAddress(firstRow,lastRow,firstCol,lastCol);
sheet.addMergedRegion(rang);
returnrang;
}
/**
*功能:设置合并单元格样式
*/
privatevoidsetRegionStyle(CellStylecs,CellRangeAddressregion,Sheetsheet){
for(inti=region.getFirstRow();i<=region.getLastRow();i++){
Rowrow=sheet.getRow(i);
if(row==null)row=sheet.createRow(i);
for(intj=region.getFirstColumn();j<=region.getLastColumn();j++){
Cellcell=row.getCell(j);
if(cell==null){
cell=row.createCell(j);
cell.setCellValue("");
}
cell.setCellStyle(cs);
}
}
}
/**
*功能:copyrows
*/
privatevoidcopyRows(intstartRow,intendRow,intpPosition,Sheetsheet){
intpStartRow=startRow-1;
intpEndRow=endRow-1;
inttargetRowFrom;
inttargetRowTo;
intcolumnCount;
CellRangeAddressregion=null;
inti;
intj;
if(pStartRow==-1||pEndRow==-1){
return;
}
//拷贝合并的单元格
for(i=0;i=pStartRow)
&&(region.getLastRow()<=pEndRow)){
targetRowFrom=region.getFirstRow()-pStartRow+pPosition;
targetRowTo=region.getLastRow()-pStartRow+pPosition;
CellRangeAddressnewRegion=region.copy();
newRegion.setFirstRow(targetRowFrom);
newRegion.setFirstColumn(region.getFirstColumn());
newRegion.setLastRow(targetRowTo);
newRegion.setLastColumn(region.getLastColumn());
sheet.addMergedRegion(newRegion);
}
}
//设置列宽
for(i=pStartRow;i<=pEndRow;i++){
RowsourceRow=sheet.getRow(i);
columnCount=sourceRow.getLastCellNum();
if(sourceRow!=null){
RownewRow=sheet.createRow(pPosition-pStartRow+i);
newRow.setHeight(sourceRow.getHeight());
for(j=0;jdataMap=newHashMap();
dataMap.put("B1","03_Alpha_项目工作时间统计表");
dataMap.put("B2","统计时间：2017/01/01-2017/03/31");
excel.writeData(templateFilePath,dataMap,0);
List>datalist=newArrayList>();
Mapdata=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"3/10/17");
data.put(2,"18:50");
data.put(3,"19:00");
data.put(4,"李子鹏");
data.put(5,"新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用");
data.put(6,"代码开发");
data.put(7,"3.17");
datalist.add(data);
data=newHashMap();
data.put(1,"");
data.put(2,"");
data.put(3,"");
data.put(4,"");
data.put(5,"");
data.put(6,"");
data.put(7,"");
datalist.add(data);
String[]heads=newString[]{"B4","C4","D4","E4","F4","G4","H4"};
excel.writeDateList(templateFilePath,heads,datalist,0);
//写到输出流并移除资源
excel.writeAndClose(templateFilePath,os);
os.flush();
os.close();
}
}
大体思路：
最主要是制作好模版
代码根据模版，读取设置好的列的格式，在循环数据行，读取模版中的对应的行，存在该行就取得，不存在看是否需要copy某一行，不需要就手动创建无制定格式的行，后面在为该行的每一列对应的给个单元格制定格式和数据。
以上这篇基于apachepoi根据模板导出excel的实现方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。