java通过poi模板导出excel

 

java通过poi来读写excel目前很方便，支持xls和xlsx格式，目前代码支持按照sheet模板导出,并且支持sheet页级的模板复制功能。

 附上poi的maven配置：

 

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.16</version>
  </dependency>

 我使用了最新的版本。

 

先贴一下sheet页模板数据的封装：

 

/**
 * sheet页数据定制
 * @author lyf
 *
 */
public class SheetData  {

	/**
	 * sheet页中存储 #{key} 的数据
	 */
	private Map<String, Object> map = new HashMap<String, Object>();
	
	/**
	 * 列表数据存储 sheet页中替换${key} 并以列为单位向下赋值
	 */
	private List<Object>  datas = new LinkedList<Object> ();
	
	private String name ;
	
	public void setName(String name) {
		this.name = name;
	}
	
	public String getName() {
		return name;
	}
	
	
	
	public SheetData(String name) {
		super();
		this.name = name;
	}

	public void put(String key , Object value) {
		map.put(key, value);
	}
	
	public void remove(String key) {
		map.remove(key);
	}
	
	public Object get(String key) {
		return map.get(key);
	}
	
	/**
	 * 清理map存储和数据存储
	 */
	public void clear() {
		map.clear();
		datas.clear();
	}
	
	public void addData(Object t){
		datas.add(t);
	}
	
	public void addDatas(List<? extends Object> list) {
		datas.addAll(list);
	}
	
 
	public List<Object>  getDatas() {
		return datas;
	}
	
}

 

 以下是针对sheet模板的封装类：

/**
 * excel操作公共类-提供excel按照模板输出
 * @author lyf
 *
 */
public class ExcelUtils {
 
	
	  
	/**
	 * Sheet复制
	 * @param fromSheet
	 * @param toSheet
	 * @param copyValueFlag
	 */
	public static void copySheet(Workbook wb,Sheet fromSheet, Sheet toSheet,
			boolean copyValueFlag) {
		//合并区域处理
	 	
		mergerRegion(fromSheet, toSheet);
		int index = 0;
		for (Iterator<Row> rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
			Row tmpRow =  rowIt.next();
			Row newRow = toSheet.createRow(tmpRow.getRowNum());
			 
			CellStyle style = tmpRow.getRowStyle();
			if(style != null)
				newRow.setRowStyle(tmpRow.getRowStyle());
			
			newRow.setHeight(tmpRow.getHeight());
			
			//针对第一行设置行宽
			if(index == 0) {
				int first = tmpRow.getFirstCellNum();
				int last = tmpRow.getLastCellNum();
				for(int i = first ; i < last ; i++) {
					int w = fromSheet.getColumnWidth(i);
					toSheet.setColumnWidth(i, w + 1);
				}
				toSheet.setDefaultColumnWidth(fromSheet.getDefaultColumnWidth());
			}
			
			//行复制
			copyRow(wb,tmpRow,newRow,copyValueFlag);
			
			index++ ;
		}
	}
	/**
	 * 行复制功能
	 * @param fromRow
	 * @param toRow
	 */
	 static void copyRow(Workbook wb,Row fromRow,Row toRow,boolean copyValueFlag){
		for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
			Cell tmpCell = cellIt.next();
			Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
			copyCell(wb,tmpCell, newCell, copyValueFlag);
		}
	}
	/**
	* 复制原有sheet的合并单元格到新创建的sheet
	* 
	* @param sheetCreat 新创建sheet
	* @param sheet      原有的sheet
	*/
	 static void mergerRegion(Sheet fromSheet, Sheet toSheet) {
	   int sheetMergerCount = fromSheet.getNumMergedRegions();
	   for (int i = 0; i < sheetMergerCount; i++) {
		   
		   CellRangeAddress cra = fromSheet.getMergedRegion(i);
	    
		   toSheet.addMergedRegion(cra);
	   }
	}
	/**
	 * 复制单元格
	 * 
	 * @param srcCell
	 * @param distCell
	 * @param copyValueFlag
	 *            true则连同cell的内容一起复制
	 */
	public static void copyCell(Workbook wb,Cell srcCell, Cell distCell,
			boolean copyValueFlag) {
		
		 
		
		CellStyle newstyle=wb.createCellStyle();
		//copyCellStyle(srcCell.getCellStyle(), newstyle);
		//distCell.setEncoding(srcCell.);
		newstyle.cloneStyleFrom(srcCell.getCellStyle());
		//样式
		distCell.setCellStyle(newstyle);
		//评论
		if (srcCell.getCellComment() != null) {
			distCell.setCellComment(srcCell.getCellComment());
		}
		// 不同数据类型处理
		CellType srcCellType = srcCell.getCellTypeEnum();
		distCell.setCellType(srcCellType);
		
		 
		if (copyValueFlag) {
			if (srcCellType == CellType.NUMERIC) {
				if (DateUtil.isCellDateFormatted(srcCell)) {
					distCell.setCellValue(srcCell.getDateCellValue());
				} else {
					distCell.setCellValue(srcCell.getNumericCellValue());
				}
			} else if (srcCellType == CellType.STRING ) {
				distCell.setCellValue(srcCell.getRichStringCellValue());
			} else if (srcCellType == CellType.BLANK ) {
				// nothing21
			} else if (srcCellType == CellType.BOOLEAN  ) {
				distCell.setCellValue(srcCell.getBooleanCellValue());
			} else if (srcCellType == CellType.ERROR ) {
				distCell.setCellErrorValue(srcCell.getErrorCellValue());
			 
			} else if (srcCellType == CellType.FORMULA  ) {
				distCell.setCellFormula(srcCell.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	
	/**
	 * 写入excel数据
	 * @param model 采用的模板 位置在 src/model/下 模板第一个sheet页必须是模板sheet
	 * @param sheetDatas 模板数据
	 */
	 
	public static void writeData(String model , OutputStream out,SheetData... sheetDatas ) {
		
		Workbook wb = null;
		try {
			
			InputStream input = ExcelUtils.class.getResourceAsStream("/model/" + model);
			
			if(input == null) {
				throw new RuntimeException("model excel file load error :/model/" + model + " , check model file is exists !");
			}
			
			if(model.endsWith(".xlsx"))
				wb = new XSSFWorkbook(input);
			else if(model.endsWith(".xls"))
				wb = new HSSFWorkbook(input);
			else
				throw new RuntimeException("model file format is not valid , this : " + model + " , eg:.xlsx or xls");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();

			throw new RuntimeException("model excel file load error :/model/" + model);
		}

		Sheet source =  wb.getSheetAt(0);
		 
		//就一个的话 直接用模板
		int size = sheetDatas.length ;
		for(int i = 0 ; i < size  ; i++) {
			
			if(i == 0) {
				wb.setSheetName(0, sheetDatas[0].getName());
				
			} else {
				Sheet toSheet = wb.createSheet(sheetDatas[i].getName());
				//复制格式
				copySheet(wb, source, toSheet, true);
			}
			 
			 
		}
		
		for(int i = 0 ; i < size  ; i++) {
			//写数据
			writeData(sheetDatas[i], wb.getSheetAt(i));
		}
		 
		try {
			wb.write(out);
			out.flush();
			wb.close();
			out.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	
	/**
	 * 向sheet页中写入数据
	 * @param values 数据Map
	 * @param sheet sheet
	 */
	  public static void writeData(SheetData sheetData , Sheet sheet) {
		
		//从sheet中找到匹配符 #{}表示单个 , ${}表示集合,从该单元格开始向下追加
	 
		for(Iterator<Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) {
			Row row = rowIt.next();
			//取cell
			for(int j = row.getFirstCellNum() ; j < row.getLastCellNum() ; j++) {
				
				Cell cell = row.getCell(j);
 
				//判断cell的内容是否包含 $ 或者#
				if(cell != null && cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue() != null 
							&& (cell.getStringCellValue().contains("$") || cell.getStringCellValue().contains("#") )) {
					//剥离# $
					String[] winds = CommonUtils.getWildcard(cell.getStringCellValue().trim());
					
					for(String wind : winds) {
						
						writeData(sheetData, wind , cell , sheet);
					}
					
					
				}
				
			}
			
		}
	}
	
	/**
	 * 填充数据
	 * @param values
	 * @param keyWind #{name}只替换当前 or ${names} 从当前行开始向下替换
	 */
	static void writeData(SheetData sheetData , String keyWind , Cell cell , Sheet sheet) {
		String key = keyWind.substring(2 , keyWind.length() - 1);
		
		if(keyWind.startsWith("#")) {
			
			//简单替换
				
			Object value = sheetData.get(key);
			//为空则替换为空字符串
			if(value == null) 
				value = "" ;
			
			String cellValue = cell.getStringCellValue();
			cellValue = cellValue.replace(keyWind, value.toString());
			
			cell.setCellValue(cellValue);
			
		} else  if(keyWind.startsWith("$")) {
			
			//从list中每个实体开始解,行数从当前开始
			int rowindex = cell.getRowIndex();
			int columnindex = cell.getColumnIndex();
			
			List<? extends Object> listdata = sheetData.getDatas();
	  
			//不为空的时候开始填充
			if(listdata != null && !listdata.isEmpty()){
				for(Object o : listdata) {
					Object cellValue = CommonUtils.getValue(o, key);
					
					Row row = sheet.getRow(rowindex);
					if(row == null) {
						row = sheet.createRow(rowindex);
					}
					
					 
					//取出cell
					Cell c = row.getCell(columnindex);
					if(c == null) 
						c = row.createCell(columnindex);
					if(cell.getCellStyle() != null){ 
						c.setCellStyle(cell.getCellStyle());
						
					}
						 
					if(cell.getCellTypeEnum() != null) {
						c.setCellType(cell.getCellTypeEnum());
					 
					}
					 
					if(cellValue != null) {
						if(cellValue instanceof Number || CommonUtils.isNumber(cellValue) )
							c.setCellValue( Double.valueOf(cellValue.toString()));
						else if(cellValue instanceof Boolean)
							c.setCellValue((Boolean)cellValue);
						else if(cellValue instanceof Date)
							c.setCellValue((Date)cellValue);
						else
							c.setCellValue(cellValue.toString());
					} else {
						
						//数据为空 如果当前单元格已经有数据则重置为空
						if(c.getStringCellValue() != null) {
							c.setCellValue("");
						}
						
					}
					
					
					
					rowindex++ ;
				}
			} else {
				//list数据为空则将$全部替换空字符串
				String cellValue = "" ;
				 
				cell.setCellValue(cellValue);
				
			}
			
			
			
		}
		
	}
	
}

 

 其中注意我的 模板文件全部放在 source/model/  目录下，大家用的时候改成自己实际的位置。

 

 

其中用到了CommonUtils公共类中封装的几个静态方法：

 /**
	   * 从实体中解析出字段数据
	   * @param data 可能为pojo或者map 从field中解析
	   * @param field 字段名称
	   * @return
	   */
	   
	  @SuppressWarnings("rawtypes")
	public static Object getValue(Object data , String field) {
		  
		  if(data instanceof Map) {
			  
			  Map map = (Map) data;
			  return map.get(field);
		  }
		  try {
			  
			  String method = "get" + field.substring(0 , 1).toUpperCase() + field.substring(1);
			  
			  Method m = data.getClass().getMethod(method, null);
			  
			  if(m != null) {
				  return m.invoke(data, null);
			  }
			  
		  } catch (Exception e) {
			  // TODO Auto-generated catch block
			 // e.printStackTrace();
			  logger.error("data invoke error , data:" + data + " , key:" + field);
			  return null;
		  } 
		  
		  
		  return null ;
		  
	  }
	  
	  /**
	   * 判断是否为数字
	   * @param v
	   * @return
	   */
	  public static boolean isNumber(Object v) {
		  
		  if(v == null) return false; 
		  
		  if(v instanceof Number) {
			  return true ;
		  } else if(v.toString().matches("^\\d+$")) {
			  return true ;
		  } else if(v.toString().matches("^-?\\d+\\.?\\d+$")) {
			  return true ;
		  } else {
			  try{
				  Double.parseDouble(v.toString());
				  return true ;
			  }catch(Exception e) {
				  return false;
			  }
			 
			  
		  }
		  
	  }

 /**
	   * 返回 #{} 或者 ${} 中包含的值
	   * @param str
	   * @param type
	   * @return eg:#{name} ${ages} 
	   */
	  public static String[] getWildcard(String str ) {
		  
		 List<String> list = new ArrayList<String>();
		 
		 int start = 0;
		 while(start < str.length() && start >= 0) {
			 
			 start = str.indexOf("{", start);
			 
			 int end = str.indexOf("}", start);
			 if(start > 0) {
				 String wc = str.substring(start - 1 , end + 1);
				 
				 list.add(wc);
			 }
			
			 if(start < 0) break ;
			 
			 start = end + 1;
			 
		 }
		 
		 return list.toArray(new String[0]);
		  
	  }

 

 

 

下面开始写测试，编辑一个excel模板：

 

 

编写一个测试数据实体（实际使用Map效率会更好一些）：

public class TestData {
	private int id ;
	private int p_id ;
	private String name ;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getP_id() {
		return p_id;
	}
	public void setP_id(int p_id) {
		this.p_id = p_id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public TestData(int id, int p_id, String name) {
		super();
		this.id = id;
		this.p_id = p_id;
		this.name = name;
	}
	
	
}

 

编写测试类，注意模型test.xlsx 已经放入src/model/ 目录下：

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import com.xahl.data.common.ExcelUtils;
import com.xahl.data.pojo.SheetData;

public class TestExcel2 {
	
	

	public static void main(String[] args) {
		
		//获取模板 
		String model = "test.xlsx" ; 
		File f = new File("e:/test.xlsx");

		SheetData[] sds = new SheetData[5];
		
		//创建5个数据sheet
		for( int i = 0 ; i < 5 ; i++) {
			SheetData sd = new SheetData("测试" + i);
			sd.put("name", "张三" + i);
			sd.put("age", 13);
			
			//每个sheet页加入100条测试数据
			//注意这里可以加入pojo也可以直接使用map,理论上map在这里效率更高一些
			for(int j = 0 ; j < 100 ; j++) {
				TestData td = new TestData(j, j * -1, "t" + j);
				sd.addData(td);;
			}
			 
			sds[i] = sd ;
		}
		 
		 	 
		try {
			ExcelUtils.writeData(model, new FileOutputStream(f) ,sds);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 
		

	}

}
 

 

输出文件如下：

 

 

 

  模板中单元格的样式会延续复制，包含颜色，宽度等等。有兴趣大家可以一起扩展一下。