POI 兼容导入Excel

package ycl.learn.excel;

import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import ycl.learn.excel.ExcelHeader.ColumnHeader;

public class ExcelParameter {

	private OutputStream outputStream;

	private String sheetName;

	private ColumnHeader[] chs;

	private List<Map<String, String>> dataMapList;

	private Boolean verstion2007;

	public ColumnHeader[] getChs() {
		return chs;
	}

	public void setChs(ColumnHeader[] chs) {
		this.chs = chs;
	}

	public List<Map<String, String>> getDataMapList() {
		return dataMapList;
	}

	public void setDataMapList(List<Map<String, String>> dataMapList) {
		this.dataMapList = dataMapList;
	}

	public OutputStream getOutputStream() {
		return outputStream;
	}

	public void setOutputStream(OutputStream outputStream) {
		this.outputStream = outputStream;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public Boolean getVerstion2007() {
		return verstion2007;
	}

	public void setVerstion2007(Boolean verstion2007) {
		this.verstion2007 = verstion2007;
	}
}

defination of the parameter

package ycl.learn.excel;

import ycl.learn.excel.ExcelTypeConvert.HeaderType;


public class ExcelHeader {

	public interface ColumnHeader{
		public String getName(); 
		public HeaderType getHeaderType();
	}

	public enum UserHeader implements ColumnHeader{
		ACTION("ACTION",HeaderType.BOOLEAN),FIRST_NAME("FIRST NAME",HeaderType.DATE),LAST_NAME("LAST NAME",HeaderType.DOUBLE),E_MAIL("E-MAIL",HeaderType.STRING),PHONE("PHONE",HeaderType.STRING);

		private String name=null;
		private HeaderType ht = null;
		private UserHeader(String name,HeaderType ht){
			this.name = name;
			this.ht = ht;
		}
		public String getName(){
			return name;
		} 
		public HeaderType getHeaderType(){
			return ht;
		}
	} 

	public enum PhoneHeader  implements ColumnHeader{
		//ACTION("ACTION"),
		NAME("NAME",HeaderType.BOOLEAN),PHONE_NUMBER("PHONE NUMBER",HeaderType.BOOLEAN);
		private String name=null;
		private HeaderType ht = null;
		private PhoneHeader(String name,HeaderType ht){
			this.name = name;
			this.ht = ht;
		}
		public String getName(){
			return name;
		} 
		public HeaderType getHeaderType(){
			return ht;
		}
	}


}

defination of the header, this is add TypeConvert.

package ycl.learn.excel;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

public class ExcelTypeConvert {

	public interface TypeConvert{
		public Object getConvertValue(String value);
	}

	public enum HeaderType implements TypeConvert{
		DOUBLE{
			public Object getConvertValue(String value) {
				Double ret = new Double(value);
				return ret;
			}
		}
		,DATE("yyyy-MM-DD"){
			public Object getConvertValue(String value) {
				Date date = null;
				try {
					SimpleDateFormat format = new SimpleDateFormat(this.getFormat());
					date = format.parse(value);
				} catch (ParseException e) {
				}
				return date;
			}
		}
		,STRING{
			public Object getConvertValue(String value) {
				return value;
			}
		}
		,BOOLEAN{
			public Object getConvertValue(String value) {
				Boolean bol = new Boolean(value);
				return bol;
			}
		}; 
		private String format;
		private HeaderType(){

		}
		private HeaderType(String format){
			this.format=format;
		}

		public String getFormat(){
			return format;
		}

		public abstract Object getConvertValue(String value);
	}


	/**
	 * be able to read as string value from a non-string type cell.
	 * Every cell value transform to string format.
	 * 
	 * @param cell
	 * @return
	 */
	public static String getStringCellValue(Cell cell) {
		if (cell == null) {
			return "";
		} else {
			Object ret = null;
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_FORMULA://formula
				switch (cell.getCachedFormulaResultType()) {
				case Cell.CELL_TYPE_STRING:
					ret = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					ret = new Double(cell.getNumericCellValue());
					break;
				default:
					ret = cell.getStringCellValue();
				}
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					ret = cell.getDateCellValue();
				} else {
					ret = new Double(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_STRING:
				ret = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:// blank
				ret = "";
				break;
			case Cell.CELL_TYPE_ERROR:
				ret = "";
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				ret = new Boolean(cell.getBooleanCellValue());
				break;
			default:
				try {
					ret = cell.getStringCellValue();
				} catch (Exception e) {
					ret = "";
				}
			}
			return ret.toString().trim();
		}
	}
}

this is the type convert.

package ycl.learn.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import ycl.learn.excel.ExcelHeader.ColumnHeader;
import ycl.learn.excel.ExcelResult.ExcelErrorEnum;
import ycl.learn.excel.ExcelTypeConvert.HeaderType;

public class ExcelWriter {

	public static ExcelResult writerExcel(ExcelParameter parameter) {
		ExcelResult result = new ExcelResult();
		OutputStream os = parameter.getOutputStream();
		ColumnHeader[] chs = parameter.getChs();
		Boolean version2007 = parameter.getVerstion2007();
		String sheetName = parameter.getSheetName();
		List<Map<String, String>> dataMapList = parameter.getDataMapList();
		Workbook workbook = null;
		try {
			if (version2007)
				workbook = new XSSFWorkbook();
			else
				workbook = new HSSFWorkbook();
		} catch (Exception e) {
			result.addError(ExcelErrorEnum.SYSTEM_ERROR);
			return result;
		}
		Sheet sheet = workbook.createSheet(sheetName);
		result = writerSheet(sheet,chs,dataMapList,workbook);
		try {
			workbook.write(os);
		} catch (IOException e) {
			result.addError(ExcelErrorEnum.SYSTEM_ERROR);
			return result;
		}
		return result; 
	}

	private static ExcelResult writerSheet(Sheet sheet, ColumnHeader[] chs, List<Map<String, String>> dataMapList,Workbook workbook) {
		ExcelResult result = new ExcelResult();
		Row row = sheet.createRow(0);
		for(int i=0;i<chs.length;i++){
			Cell cell = row.createCell(i);
			cell.setCellValue(chs[i].getName());
		}

		for(int j=0;j<dataMapList.size();j++){
			row = sheet.createRow(j+1);
			for(int k=0;k<chs.length;k++){
				Cell dataCell = row.createCell(k);
				Map<String, String> dataMap = dataMapList.get(j);
				String value = dataMap.get(chs[k].getName());
				if(value == null){
					result.addError(ExcelErrorEnum.HEADER_NOT_MATCH);
				} 
				Object convertValue = chs[k].getHeaderType().getConvertValue(value); 
				if(chs[k].getHeaderType().equals(HeaderType.BOOLEAN)){
					Boolean realValue  = (Boolean)convertValue;
					dataCell.setCellValue(realValue); 
				}else if(chs[k].getHeaderType().equals(HeaderType.DATE)){
					Date realValue  = (Date)convertValue;  
					dataCell.setCellValue(realValue);
					dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
					CellStyle dataCellStyle = workbook.createCellStyle();
					dataCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy"));
					dataCell.setCellStyle(dataCellStyle);
				}else if(chs[k].getHeaderType().equals(HeaderType.DOUBLE)){
					Double realValue = (Double)convertValue;
					dataCell.setCellValue(realValue);
					dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
					CellStyle dataCellStyle = workbook.createCellStyle();
					dataCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
					dataCell.setCellStyle(dataCellStyle);
				}else if(chs[k].getHeaderType().equals(HeaderType.STRING)){
					dataCell.setCellValue((String)convertValue);
				}else{
					dataCell.setCellValue((String)convertValue);
				} 
			}
		}
		return result;
	}
}

so you can defination of the ColumnHeader with columnName,columnType.
auto export with String Type, and writer with the type of the user defination.

the real, we get data from excel use String type.
If you want to change type with user defination ,just use the typeConvert.