package framework.util.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * poi 3.6
 * 
 * @author liuyajun
 * @date 2013-10-23 上午09:35:09
 */
public final class ExcelReader {

	/**
	 * 日期类型
	 */
	protected SimpleDateFormat[] dateFmt = new SimpleDateFormat[]{
			//new SimpleDateFormat("yyyy-MM"),
			//new SimpleDateFormat("yyyy-MM-dd"),
			new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"),
			//new SimpleDateFormat("HH:mm:ss")
	};
	
	protected ExcelReader(){
		
	}
	/**
	 * 打开 *.xls (Excel 2003)
	 * 
	 * @param f
	 * @return
	 * @throws Exception
	 */
	public static ExcelReader open(File f) throws Exception {
		ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
		FileInputStream fis = new FileInputStream(f);
		byte[] by = new byte[512];
		int t = fis.read(by, 0, by.length);
		while (t > 0) {
			byteOS.write(by, 0, 512);
			// 这里别写成t，写够512，呵呵，SB的方法对付SB的java API
			t = fis.read(by, 0, by.length);
		}
		byteOS.close();
		fis.close();
		
		ExcelReader reader = new ExcelReader();
		
		reader.bais = new ByteArrayInputStream(byteOS.toByteArray());
		reader.book = new HSSFWorkbook(reader.bais);

		if (reader.book == null) {
			fis.close();
		}
		return reader;
	}

	protected ByteArrayInputStream bais;
	protected HSSFWorkbook book;

	/**
	 * 得到所有 sheet 名称
	 * 
	 * @return
	 */
	public String[] getSheets() {
		int len = book.getNumberOfSheets();
		String[] name = new String[len];
		for (int i = 0; i < len; i++) {
			name[i] = book.getSheetName(i);
		}
		return name;
	}
	/**
	 * 根据 sheetName 得到内容
	 * @param sheetName
	 * @return 可能为null, 每行的列数可能不同
	 */
	public String[][] getData(String sheetName){
		return this.getData(sheetName, Integer.MAX_VALUE, Integer.MAX_VALUE);
	}
	/**
	 * 根据 sheetName 得到内容
	 * 
	 * @param sheetName
	 * @param rows	最多行数
	 * @param cols	最多列数
	 * @return	 可能为null, 每行的列数可能不同
	 */
	public String[][] getData(String sheetName, int rows, int cols) {
		int sheetId = book.getSheetIndex(sheetName);
		if(sheetId<0){
			return null;
		}

		HSSFSheet sheet = book.getSheetAt(sheetId);
		int rowNum = sheet.getPhysicalNumberOfRows();	//最大已用行数
		if(rows<rowNum){
			rowNum = rows;
		}
		if(rowNum<=0){
			return null;
		}
		String[][] s = new String[rowNum][];
		
		for (int i = 0; i < rowNum; i++) {
			HSSFRow rowData = sheet.getRow(i);
			if(rowData==null){
				s[i] = new String[0];
				continue;
			}
			int colNum = rowData.getPhysicalNumberOfCells();	//列,会多一空白列
			if(cols<colNum){
				colNum = cols;
			}
			if(colNum<=0){
				s[i] = new String[0];
				continue;
			}
			
			String[] ss = new String[colNum];
			for (int j = 0; j < colNum; j++) {
				ss[j] = "";
				
				try {
					HSSFCell cell = rowData.getCell(j);
					
					switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING:		//字符串
						ss[j] = cell.getStringCellValue();//.replaceAll("'", "''");
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							//时间日期
							for(SimpleDateFormat fmt : dateFmt){
								try{
									ss[j] = fmt.format(cell.getDateCellValue());
									break;
								}catch(Exception e){
									
								}
							}
						} else {
							//数字
							ss[j] = getDoubleString(cell.getNumericCellValue());
						}
						break;
					default:
						ss[j] = "";
						break;
					}
				} catch (Exception e) {
					ss[j] = "";
				}
				ss[j] = ss[j].trim();

			}
			s[i] = ss;
		}
		return s;
	}
	protected DecimalFormat df = new DecimalFormat(
	"#.00000000000000000000000000000000000000000000000000000000000000000000");
	
	protected String getDoubleString(double d){
		String s = df.format(d);
		s = s.replaceAll("[0]*$", "");
		s = s.replaceAll("[.]$", "");

		return s;
	}
	/**
	 * 关闭
	 * 
	 */
	public void close() throws Exception {
		bais.close();
	}
	
	public static void main(String[] args) throws Exception {
		String f = "e:/mm.xls";
		
		ExcelReader r = ExcelReader.open(new File(f));
		String[] a=r.getSheets();
		String[][] s = r.getData("Sheet1");
		
		for(int i=0; i<s.length; i++){
			for(int j=0; j<s[i].length; j++){
				//System.err.print(i+", "+j+": ");
				//System.err.println(s[i][j]);
			}
		}
	}
}