xlsTemplate 1.0----excel模版工具1.0

package com.kingdee.finance.p2p.utils.xlsTemplate;

public interface XlsDataFormator {

	public abstract String getFormatorName();

	public abstract String formator(Object data);

}

package com.kingdee.finance.p2p.utils.xlsTemplate;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.kingdee.finance.p2p.utils.MapUtil;



/**
 * @author lxn
 * 使用excel模版与数据，合成excel文件
 * 1，加载模版loadTemplate
 * 2，载入数据模型loadModel
 * 3，合成 build
 * 4，输出 write
 * 
 * 目前支持的功能：
 * [@key@] 直接输出model中的值
 * [@key?xxx@]输出经过xxx formator处理过的model中的值
 * [@if then else@]
 */
public class XlsTemplateBuilder {

	private HSSFWorkbook wb;
	private Map<string object=""> model;
	private Map<string xlsdataformator=""> formators = new HashMap<string xlsdataformator="">();	
	
	
	public XlsTemplateBuilder(){
		this.addFormators(new InternalDateFormator());
		this.addFormators(new InternalDatetimeFormator());
		this.addFormators(new InternalPercentFormator());
		this.addFormators(new FenToYuanFormator());
		this.addFormators(new FenToCNFormator());
	}
	
	/**
	 * 载入xls模版文件
	 * @param path
	 * @throws IOException
	 */
	public void loadTemplate(InputStream is) throws IOException{
		this.wb= new HSSFWorkbook(is);
	}
	
	/**
	 * 载入xls模版文件
	 * @param path
	 * @throws IOException
	 */
	public void loadTemplate(String path) throws IOException{
		InputStream is = new FileInputStream(new File(path));  
		loadTemplate(is);
	}
	
	
	
	/**
	 * 载入数据map，可多次载入
	 * @param model
	 */
	public void loadModel(Map<string object=""> model){
		if(this.model==null)
			this.model=model;
		else
			this.model.putAll(model);
	}
	
	/**
	 * 从bean载入数据，
	 * @param object
	 */
	public void loadModel(Object object){
		loadModel(MapUtil.objectToMap(object));
	}
	
	public void addFormators(XlsDataFormator ...formators){
		for(XlsDataFormator eachFormator:formators){
			this.formators.put(eachFormator.getFormatorName(), eachFormator);
		}
	}
	
	/**
	 * 合并 model 和 模版
	 */
	public void build(){
		if(wb==null){
			throw new RuntimeException("xls模版未就绪");
		}
		if(model==null){
			throw new RuntimeException("xls数据未就绪");
		}
		
		//循环sheet
		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			HSSFSheet eachSheet = wb.getSheetAt(i);
			String sheetResult = scanAndReplace(wb.getSheetName(i),model);
			wb.setSheetName(i, sheetResult);
			
			
			//循环row
		    for (int j = 0; j < eachSheet.getPhysicalNumberOfRows(); j++) { 
		    	HSSFRow eachRow = eachSheet.getRow(j);
		    	//循环cell
		    	for (int k = 0; k < eachRow.getLastCellNum(); k++) { 
		    		HSSFCell eachCell=eachRow.getCell(k);
		    			if(eachCell==null)
		    				continue;
		    			if(eachCell.getCellType()!=HSSFCell.CELL_TYPE_STRING)
		    				continue;
		    			String cellValue=eachCell.getStringCellValue();
		    			if(StringUtils.isBlank(cellValue)){
		    				continue;
		    			}
		    			String result= scanAndReplace(cellValue,model);
		    			eachCell.setCellValue(result);
		    	}
		    }
		}
	}
	
	
	
	/**
	 *匹配和替换
	 * @param cellValue
	 * @return
	 */
	private String scanAndReplace(String cellValue,Map<string object=""> model){
		if(StringUtils.isBlank(cellValue)){
			return cellValue;
		}
		String result= new String(cellValue);
		String regex = "\\[@(.*?)\\@]";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(cellValue);
		while(m.find()){
			String withSign=m.group(0);
			//特殊字符处理
			withSign=withSign.replaceAll("\\[", "\\\\[");
			withSign=withSign.replaceAll("\\]", "\\\\]");
			withSign=withSign.replaceAll("\\@", "\\\\@");
			withSign=withSign.replaceAll("\\?", "\\\\?");
			
			String orign = m.group(1);
			if(orign.contains("?")){
				String afterProcess= funcProcess(orign);
				result = result.replaceAll(withSign, afterProcess);
				continue;
			}
			if(orign.contains("if")&&orign.contains("then")){
				
				String afterProcess= ifProcess(orign);
				result = result.replaceAll(withSign, afterProcess);
				continue;
			}
			
//			String[] strs = orign.split("\\?");
//			String func = null;
//			String key = null;
//			if(strs.length>1){
//				key=strs[0];
//				func=strs[1];
//			}else {
//				key=orign;
//			}
//			Object value = model.get(key);
//			if(value==null){
//				result=result.replaceAll("\\[\\@"+key+"\\@\\]","");
//			}else if(StringUtils.isNotBlank(func)&&formators.containsKey(func)){
//				String afterFormate=null;
//				try {
//					afterFormate=formators.get(func).formator(value);
//				} catch (Exception e) {
//					afterFormate=dataFormat(value);
//				}
//				
//				result=result.replaceAll("\\[\\@"+key+"\\?"+func+"\\@\\]", afterFormate);
//			}else {
//				result=result.replaceAll("\\[\\@"+key+"\\@\\]", dataFormat(value));
//			}
			//默认情况
			String afterProcess= dataFormat(model.get(orign));
			result = result.replaceAll(withSign, afterProcess);
			return result;
		}
		return result;
	}
	
	
	private String  funcProcess(String orign){
		String[] strs = orign.split("\\?");
		String func = null;
		String key = null;
		if(strs.length>1){
			key=strs[0];
			func=strs[1];
		}
		if(StringUtils.isBlank(key)||StringUtils.isBlank(func)){
			return dataFormat(model.get(orign));
		}
		Object value = model.get(key);
		if(value==null){
			return "";
		}
		try {
			return formators.get(func).formator(value);
		} catch (Exception e) {
			return dataFormat(value);
		}
		
	}
	
	private String ifProcess(String orign){
		//定位
		Integer ifBegin=orign.indexOf("if");
		Integer thenBegin=orign.indexOf("then");
		Integer elseBegin= orign.indexOf("else");
		//切割
		String condition = orign.substring(ifBegin+2, thenBegin).trim();
		String thenStr=null;
		String elseStr=null;
		if(elseBegin<0){
			thenStr=orign.substring(thenBegin+4).trim();
		}else{
			thenStr=orign.substring(thenBegin+4,elseBegin).trim();
			elseStr=orign.substring(elseBegin+4).trim();
		}
		//条件处理
		String [] cons = condition.split("\\=\\=");
		String con1=cons[0].trim();
		String con2=cons[1].trim();
		if(con1==null||con2==null){
			return "";
		}
		String val1=getValue(con1);
		String val2=getValue(con2);
		if(val1.equals(val2)){
			return getValue(thenStr);
		}
		
		if(elseStr!=null){
			return getValue(elseStr);
		}else {
			return "";
		}
	}
	
	private String getValue(String statement){
		if((statement.startsWith("\'")||statement.startsWith("\""))&&(statement.startsWith("\'")||statement.endsWith("\""))){
			return statement.substring(1, statement.length()-1);
		}else{
			return dataFormat(model.get(statement));
		}
	}
	
	
	
	
	/**
	 * 数据格式处理
	 * @param data
	 * @return
	 */
	private String dataFormat(Object data){
		if(data==null)
			return "";
		return data.toString();
	}
	
	
    /**
     * 输出到路径
     * @param path
     * @throws IOException
     */
    public void write(String path) throws IOException{  
        File outPutFile=new File(path);  
        if(!outPutFile.exists()){  
            outPutFile.createNewFile();  
        }  
        FileOutputStream os = new FileOutputStream(outPutFile);    
       this.write(os);   
    }  
	
	/**
	 * 输出到流
	 * @param os
	 * @throws IOException
	 */
	public void write(OutputStream os) throws IOException{
		wb.write(os);    
        os.close();   
	}
	
	/*public static void main(String[] args) throws IllegalAccessException, InvocationTargetException {
		XlsTemplateBuilder builder=new XlsTemplateBuilder();
		try {
			builder.loadTemplate("C:\\Users\\Admin\\Desktop\\repay.xls");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Map<string object=""> map=new HashMap<string object="">();
		map.put("bidTitle", "测试标的");
		map.put("bidDate", new Date());
		builder.loadModel(map);
		builder.build();
		try {
			builder.write("C:\\Users\\Admin\\Desktop\\repay1.xls");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		
	}*/
	
}




</string></string></string></string></string></string></string>