咪卡四處看

把EXCEL資料爬出成bean
雖然好像有一些人寫了用ANNOCATION去對應BEAN的JAR檔
不過先用簡單的方式來做一次吧

重點是beanTitle 要和EXCEL資料對應到，EXCEL的一行的第一格會是beanTitle[0]
 

public void mainWork(){
	String filePath = "feebeeApSet.xlsx";
	String[] beanTitle = {"id", "type", "startDate"};  //excel欄位對應到bean的tag
	this.translateToBeanFromXls(filePath, TestBean.class, beanTitle);  //call function
}

//testBean.class
public class TestBean implements Serializable {
	
    private String id;
    
    private String type;

    private String startDate;
    
	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getStartDate() {
		return startDate;
	}

	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}
}


public <T> List<T> translateToBeanFromXls(String filePath, Class<T> objT, String[] beanTitle) throws Exception {

		List<T> beanList = new LinkedList<T>();
		
		if(StringUtils.isBlank(filePath) || objT == null || ArrayUtils.isEmpty(beanTitle)){
			//data is null!
		}else{
			FileInputStream fs = null;
			XSSFWorkbook workbook = null;
			try{
				int beanTitleSize = beanTitle.length;
				fs = new FileInputStream(filePath); 
				//EXCEL workbook
				workbook = new XSSFWorkbook(fs);
				//EXCEL sheet
				XSSFSheet sheet = workbook.getSheetAt(0);
				
				//row num
				int rowNum = sheet.getPhysicalNumberOfRows();
				log.info(filePath + "_rowNum: " + rowNum);
				//總行數
				int lastRowIndex = sheet.getLastRowNum();
				log.info(filePath + "_lastRowNum: " + lastRowIndex);
				//TITLE單位格數
				int titleLastCellNum = sheet.getRow(0).getLastCellNum();
				log.info(filePath + "_title.lastCellNum: " + titleLastCellNum);
				
				//判斷EXCEL是否基本資料錯誤
				//1.含title 最低限制為兩列
				//2.TITLE的格數須與beanTitle資料相同
				if (rowNum < 2) {
					//ERROR
				}else if(beanTitleSize!=titleLastCellNum){
					//ERROR
				}else{
					for(int i = 1; i <= lastRowIndex; i++) {
						XSSFRow row = sheet.getRow(i);
						//判斷該行的單元數和beanTitle數是否能相應
						int thisLastCellNum = row.getLastCellNum(); 
						if(beanTitleSize!=thisLastCellNum){
							//ERROR
						}else{
							T bean = objT.newInstance();
							
							//處理格數
							for (int j = 0; j < thisLastCellNum; j++) {
								XSSFCell cell = row.getCell(j);
								Object cellValue = null;
								
								switch (cell.getCellType()) {
									case XSSFCell.CELL_TYPE_BLANK:
										cellValue = "";
										break;
									case XSSFCell.CELL_TYPE_STRING:
										cellValue = cell.getStringCellValue().trim();
										break;
									case XSSFCell.CELL_TYPE_BOOLEAN:
										cellValue = cell.getBooleanCellValue();
										break;
									case XSSFCell.CELL_TYPE_NUMERIC:
										short format = cell.getCellStyle().getDataFormat();
										if (HSSFDateUtil.isCellDateFormatted(cell)) {
											Date date = cell.getDateCellValue();
											DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
											cellValue = formater.format(date);
										}else if(format == 14 || format == 31 || format == 57 || format == 58){
											DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
											Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
											cellValue = formater.format(date);
										}else if (format == 20 || format == 32) {
											DateFormat formater = new SimpleDateFormat("HH:mm");
											Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
											cellValue = formater.format(date);
										} else{
											cellValue = cell.getNumericCellValue();
										}
										break;

									default:
										//其他格式不處理
										cellValue = null;
										break;
								}
								
								if(cellValue!=null){
									// 轉成bean
									BeanUtils.setProperty(bean,
											beanTitle[j],
											cellValue);
								}
							}
							beanList.add(bean);
						}
					}
				}
			}finally{
				if(fs!=null){fs.close();}
			}
		}
		
		return beanList;
}