package framework.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
/**
 * poi 3.6
 * jdk1.4
 */
public final class ExcelWriter {
	
	public static final int STYLE_BORDER = 0X01;
	public static final int STYLE_BACK_GRAY = 0x02;
	public static final int STYLE_BACK_YELLOW = 0x04;
	
	public static final int STYLE_ALIGN_LEFT = 0x0100;
	public static final int STYLE_ALIGN_CENTER = 0x0200;
	public static final int STYLE_ALIGN_RIGHT = 0x0400;
	
	public static final int MAX_ROWS = 65500;
	
	private ExcelWriter(){
		
	}
	private static String modulePath = null;
	
	/**
	 * 设置模板路径
	 * @param path
	 */
	public static void setModulePath(String path){
		if(path.endsWith("/")){
			path = path.substring(0, path.length()-1);
		}
		modulePath = path;
	}
	private static String downloadPath = null;
	/**
	 * 设置生成路径
	 * @param path
	 */
	public static void setTempPath(String path){
		if(path.endsWith("/")){
			path = path.substring(0, path.length()-1);
		}
		downloadPath = path;
	}
	protected static Map<String, byte[]> moduleMap = new HashMap<String, byte[]>();
	
	protected static byte[] getModuleContent(String moduleName) 
			throws Exception {
		byte[] b = (byte[])moduleMap.get(moduleName);
		if(b==null){
			synchronized(moduleMap){
				b = (byte[])moduleMap.get(moduleName);
				if(b==null){
					String moduleFileName = modulePath +"/"+moduleName;
					FileInputStream fis = null;
					try{
						fis = new FileInputStream(moduleFileName);
						b = new byte[fis.available()];
						fis.read(b);
						moduleMap.put(moduleName, b);
					}catch(Exception e){
						throw e;
					}finally{
						try{
							fis.close();
						}catch(Exception e){
							
						}
					}
				}
			}
		}
		return b;
	}
	static {
		String moduleName = "blank.xls";
		InputStream is = ExcelWriter.class.getResourceAsStream(moduleName);
		try{
			byte[] b = new byte[is.available()];
			is.read(b);
			moduleMap.put(moduleName, b);
		}catch(Exception e){

		}finally{
			try{
				is.close();
			}catch(Exception e){
				
			}
		}
	}
	/**
	 * 使用默认模板创建Writer
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static ExcelWriter open(String fileName) throws Exception {
		return open(fileName, "blank");
	}
	private static SimpleDateFormat timeFmt = new SimpleDateFormat("yyyy-MM-dd_HHmmss.SSSS");
	/**
	 * 使用模板创建Writer
	 * @param fileName
	 * @param moduleName
	 * @return
	 * @throws Exception
	 */
	public static ExcelWriter open(String fileName, String moduleName) 
			throws Exception {
		ExcelWriter writer = new ExcelWriter();
		String time = "_"+timeFmt.format(new Date());

		fileName = fileName.replaceAll("[\r\n\\*?<>|/]", "");
		
		writer.fileName = downloadPath + "/" + fileName + time +".xls";

		FileOutputStream fos = null;
		try{
			fos = new FileOutputStream(writer.fileName);
			fos.write(getModuleContent(moduleName+".xls"));
			fos.flush();
		}catch(Exception e){
			throw e;
		}finally{
			fos.close();
		}
		
		writer.fis = new FileInputStream(writer.fileName);
		writer.book = new HSSFWorkbook(writer.fis);
		
		writer.dateStyle = writer.book.createCellStyle();
		writer.dateStyle.setDataFormat(writer.book.createDataFormat()
				.getFormat("yyyy-mm-dd"));

		writer.timeStyle = writer.book.createCellStyle();
		writer.timeStyle.setDataFormat(writer.book.createDataFormat()
				.getFormat("hh:mm:ss"));
		
		writer.datetimeStyle = writer.book.createCellStyle();
		writer.datetimeStyle.setDataFormat(writer.book.createDataFormat()
				.getFormat("yyyy-mm-dd hh:mm:ss"));
		
		writer.monthStyle = writer.book.createCellStyle();
		writer.monthStyle.setDataFormat(writer.book.createDataFormat()
				.getFormat("yyyy-mm"));

		writer.sheet = null;	//初始没有sheet
		
		return writer;
	}

	protected String fileName;
	protected FileInputStream fis;
	protected HSSFWorkbook book;
	protected HSSFCellStyle monthStyle;
	protected HSSFCellStyle dateStyle;
	protected HSSFCellStyle timeStyle;
	protected HSSFCellStyle datetimeStyle;
	
	protected HSSFSheet sheet;
	
	/**
	 * 冻结窗口
	 * @param row	从0开始
	 * @param col	从0开始
	 */
	public ExcelWriter freeze(int row, int col){
		if(sheet!=null){
			sheet.createFreezePane(col, row);
		}
		return this;
	}
	/**
	 * 删除sheet
	 * 
	 * @param sheetName
	 */
	public ExcelWriter deleteSheet(String sheetName){
		try{
			if(sheetName!=null){
				book.removeSheetAt(book.getSheetIndex(sheetName));
			}
		}catch(Throwable e){
			
		}
		return this;
	}
	/**
	 * 转到sheet
	 * 
	 * @param sheetName
	 */
	public ExcelWriter setSheet(String sheetName){
		if(sheetName == null){
			sheet = book.getSheetAt(0);
		}else{
			sheet = book.getSheet(sheetName);
			if(sheet == null){
				int len = book.getNumberOfSheets();
				String s = ""+len+(new Date()).getTime();
				sheet = book.createSheet(s);
				book.setSheetName(book.getSheetIndex(s), sheetName);
			}
			book.setActiveSheet(book.getSheetIndex(sheetName));
		}
		return this;
	}
	/**
	 * 复制sheet并选择新sheet
	 * @param src
	 * @param des
	 * @return
	 * @throws Exception
	 */
	public ExcelWriter copySheet(String src, String des) {
		HSSFSheet sh = book.cloneSheet(book.getSheetIndex(src));
		String sn = sh.getSheetName();
		book.setSheetName(book.getSheetIndex(sn), des);
		sheet = sh;
		return this;
	}
	/**
	 * 自动适应列
	 * 
	 * @param col
	 * @return
	 */
	public ExcelWriter autoSize(int col){
		if(sheet!=null){
			sheet.autoSizeColumn(col);
		}
		return this;
	}
	/**
	 * 得到单元格
	 * 
	 * @param row 从0开始
	 * @param col	从0开始
	 * @return
	 */
	public HSSFCell getCell(int row, int col){
		if(sheet == null){
			setSheet(null);
		}
		HSSFRow curRow = sheet.getRow(row);
		if(curRow == null){
			curRow = sheet.createRow(row);
		}
		HSSFCell cell = curRow.getCell(col);
		if(cell == null){
			cell = curRow.createCell(col);
		}

//		cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		return cell;
	}
	
	/**
	 * 合并单元格
	 * 
	 * @param row1
	 * @param col1
	 * @param row2
	 * @param col2
	 * @return
	 */
	public ExcelWriter margeCell(int row1, int col1, int row2, int col2){
		CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
		sheet.addMergedRegion(region);
		return this;
	}

	/**
	 * 设置样式
	 * 
	 * @param row
	 * @param col
	 * @param style
	 * @return
	 */
	public ExcelWriter setCellStyle(int row, int col, int style){
		HSSFCellStyle s = book.createCellStyle();

		if((style & STYLE_BORDER)==STYLE_BORDER){
			s.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			s.setBorderTop(HSSFCellStyle.BORDER_THIN);
			s.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			s.setBorderRight(HSSFCellStyle.BORDER_THIN);
		}
		if((style & STYLE_BACK_GRAY)==STYLE_BACK_GRAY){
			s.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			s.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		}
		if((style & STYLE_BACK_YELLOW)==STYLE_BACK_YELLOW){
			s.setFillForegroundColor(HSSFColor.YELLOW.index);
			s.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		}

		if((style & STYLE_ALIGN_LEFT)==STYLE_ALIGN_LEFT){
			s.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		}else if((style & STYLE_ALIGN_RIGHT)==STYLE_ALIGN_RIGHT){
			s.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		}else if((style & STYLE_ALIGN_CENTER)==STYLE_ALIGN_CENTER){
			s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		}
		this.getCell(row, col).setCellStyle(s);
		
		return this;
	}
	/**
	 * 以 STYLE_BACK_GRAY|STYLE_BORDER 样式填充
	 * @param row
	 * @param col
	 * @param s
	 * @return
	 */
	public ExcelWriter setTitle(int row, int col, String s){
		this.setCellStyle(row, col, STYLE_BACK_GRAY|STYLE_BORDER);
		this.setCellValue(row, col, s);
		this.autoSize(col);
		return this;
	}
	/**
	 * 以STYLE_BACK_GRAY|STYLE_BORDER样式填充第一行,并冻结
	 * @param s
	 * @return
	 */
	public ExcelWriter setTitle(String[] s){
		for(int i=0; i<s.length; i++){
			setTitle(0,i,s[i]);
		}
		this.freeze(1, 0);
		return this;
	}
	public ExcelWriter setCellValue(int row, int col, String str) {
		getCell(row, col).setCellValue(str==null?"":str);
		return this;
	}

	public ExcelWriter setCellValue(int row, int col, Integer d) {
		if(d!=null){
			getCell(row, col).setCellValue(d);
		}
		return this;
	}
	public ExcelWriter setCellValue(int row, int col, Long d) {
		if(d!=null){
			getCell(row, col).setCellValue(d);
		}
		return this;
	}
	public ExcelWriter setCellValue(int row, int col, Double d) {
		if(d!=null){
			getCell(row, col).setCellValue(d);
		}
		return this;
	}
	/**
	 * yyyy-MM-dd
	 * 
	 * @param row
	 * @param col
	 * @param date
	 * @return
	 */
	public ExcelWriter setCellValue(int row, int col, Date date) {
		HSSFCell cell = getCell(row, col);
		cell.setCellStyle(dateStyle);
		if(date!=null){
			cell.setCellValue(date);
		}
		return this;
	}
	/**
	 * yyyy-MM-dd HH:mm:ss
	 * 
	 * @param row
	 * @param col
	 * @param date
	 * @return
	 */
	public ExcelWriter setCellValueDatetime(int row, int col, Date date) {
		HSSFCell cell = getCell(row, col);
		cell.setCellStyle(datetimeStyle);
		if(date!=null){
			cell.setCellValue(date);
		}
		return this;
	}
	/**
	 * HH:mm:ss
	 * 
	 * @param row
	 * @param col
	 * @param date
	 * @return
	 */
	public ExcelWriter setCellValueTime(int row, int col, Date date) {
		HSSFCell cell = getCell(row, col);
		cell.setCellStyle(timeStyle);
		if(date!=null){
			cell.setCellValue(date);
		}
		return this;
	}
	/**
	 * yyyy-MM
	 * 
	 * @param row
	 * @param col
	 * @param date
	 * @return
	 */
	public ExcelWriter setCellValueMonth(int row, int col, Date date) {
		HSSFCell cell = getCell(row, col);
		cell.setCellStyle(monthStyle);
		if(date!=null){
			cell.setCellValue(date);
		}
		return this;
	}

	/**
	 * 关闭，生成文件
	 * 
	 */
	public void saveAndClose() throws Exception {
		FileOutputStream fos = new FileOutputStream(fileName);
		book.write(fos);
		fos.flush();
		fos.close();
		fis.close();
	}
	/**
	 * 得到生成的文件
	 * 
	 * @return
	 */
	public File getFile(){
		return new File(fileName);
	}
	
	public static void main(String[] args) throws Exception{
		ExcelWriter.setTempPath("f:/");
		//System.err.println(moduleMap);
		ExcelWriter w = ExcelWriter.open("测试");
		w.setSheet("sheet1");
		w.setTitle(new String[]{
			"序号","姓\n名","地址"
		});
		w.saveAndClose();
	}
	public final HSSFWorkbook getBook() {
		return book;
	}
	public final HSSFSheet getSheet() {
		return sheet;
	}
}