excel工具类
import com.iport.framework.util.ValidateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*; /**
* Created by admin on 2016/8/26.
*/
public class PoiUtil {
private static Logger logger = Logger.getLogger(PoiUtil.class); public static <T> List<T> getExcelData(String mapFilePath, Class<T> cla, InputStream inputStream) throws Exception{
List<T> dataList = new ArrayList<T>();
//获取配置数据
JaxbUtil jaxb = new JaxbUtil(TemplateExcelMap.class);
TemplateExcelMap template = jaxb.fromPath(mapFilePath, false);
List<ExcelMap> mapList = template.getColumnList();
Map<String,String> nameFieldMap = new HashMap<String,String>();
Map<Integer,Field> IndexFieldMap = new HashMap<Integer,Field>();
Map<Integer,ExcelMap> mapMap = new HashMap<Integer,ExcelMap>();
Map<String,ExcelMap> strMap = new HashMap<String,ExcelMap>();
for(ExcelMap m : mapList){
nameFieldMap.put(m.getColumnName().trim(), m.getFieldName());
strMap.put(m.getColumnName().trim(), m);
} Workbook book = getWorkbook(inputStream);
Sheet sheet = book.getSheetAt(0);
Row row0 = sheet.getRow(0);
int celNum = row0.getPhysicalNumberOfCells(); //头部数据获取映射
for(int i=0; i<celNum; i++){
Cell cell = row0.getCell(i);
String headDesc = cell.getStringCellValue();
if(!ValidateUtil.isEmpty(headDesc)){
if(nameFieldMap.containsKey(headDesc.trim())){
String fieldName = nameFieldMap.get(headDesc.trim());
IndexFieldMap.put(i, getField(fieldName, cla));
mapMap.put(i, strMap.get(headDesc.trim()));
}
}
}
long s = System.currentTimeMillis();
int rowNum = sheet.getPhysicalNumberOfRows();
Set<Integer> keys = IndexFieldMap.keySet();
if(keys.size() > 0){
for(int i=1;i<rowNum;i++){
Row row = sheet.getRow(i);
T vo = (T)cla.newInstance();
for(Integer columIndex : keys){
Cell cel = row.getCell(columIndex);
Field field = IndexFieldMap.get(columIndex);
ExcelMap mapping = mapMap.get(columIndex);
Object o = getCellValue(cel, field.getType(), mapping);
//try{
field.set(vo,o);
// }catch (Exception e){
// e.printStackTrace();
//} }
dataList.add(vo);
}
}
long e = System.currentTimeMillis();
logger.debug("数据导入解析耗时："+(e-s));
return dataList;
} public static Workbook getWorkbook(InputStream inputStream) throws IOException, InvalidFormatException {
Workbook book = null;
if (!(inputStream.markSupported())) {
inputStream = new PushbackInputStream(inputStream, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
book = new HSSFWorkbook(inputStream);
} else if (POIXMLDocument.hasOOXMLHeader(inputStream)) {
book = new XSSFWorkbook(OPCPackage.open(inputStream));
}
return book;
} private static Field getField(String filedName, Class cla) throws Exception{
Field field = cla.getDeclaredField(filedName);
if(field!=null){
field.setAccessible(true);
}
return field;
}; /**
* 获取单元格内容
* @param cell
* @param fieldClass
* @param mapping
* @return
* @throws Exception
*/
private static Object getCellValue(Cell cell, Class fieldClass, ExcelMap mapping) throws Exception{
if (cell == null) {
return null;
}
Object result = null;
if ("class java.util.Date".equals(fieldClass.toString()) || ("class java.sql.Time").equals(fieldClass.toString())) {
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
result = cell.getDateCellValue();
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
result = getDateData(mapping, cell.getStringCellValue());
}
if ( result != null && ("class java.sql.Time").equals(fieldClass.toString())) {
result = new Time(((Date) result).getTime());
}
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
double doubleVal = cell.getNumericCellValue();
if("class java.lang.Double".equals(fieldClass.toString())){
result = doubleVal;
}else{
long longVal = Math.round(cell.getNumericCellValue());
if(Double.parseDouble(longVal + ".0") == doubleVal){
try{
result = (int) longVal;
}catch (Exception e){
result = longVal;
}
}else {
result = doubleVal;
}
}
} else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
result = cell.getBooleanCellValue();
} else {
result = cell.getStringCellValue();
if(ValidateUtil.isEmpty((String)result)){
result = null;
}
}
return result;
} /**
* 获取日期类型数据
*
* @Author JueYue
* @date 2013年11月26日
* @param entity
* @param value
* @return
*/
private static Date getDateData(ExcelMap entity, String value) {
String formatStr = entity.getFormat();
if(!ValidateUtil.isEmpty(formatStr)){
String[] formats = formatStr.split(";");
for(int i = 0; i<formats.length; i++){
try{
Date d = formartData(formats[i], value);
if(d!=null){
return d;
}
}catch (Exception e){
logger.error(formats[i]+"日期转换"+value+"异常" + e.getMessage());
}
}
}
return null;
} private static Date formartData(String formatStr, String value){
if (StringUtils.isNotEmpty(formatStr) && StringUtils.isNotEmpty(value)) {
SimpleDateFormat format = new SimpleDateFormat(formatStr);
try {
return format.parse(value);
} catch (ParseException e) {
throw new RuntimeException("Excel 值获取失败");
}
}
return null;
} }