import com.google.common.base.Strings;
import com.jianwu.util.excel.annotation.ExcelAttribute;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by tookbra on 2016/4/6.
*/
public class ExcelUtil {
private static final Log log = LogFactory.getLog(ExcelUtil.class);
public static <T> List<T> importExcel(Class<T> clazz, HSSFWorkbook wb, String sheetName) {
int maxCol = 0;
List<T> list = new ArrayList<T>();
List<T> errorList = new ArrayList<T>();
if(Strings.isNullOrEmpty(sheetName)) {
//没有制定sheet返回null
return null;
}
HSSFSheet sheet = wb.getSheet(sheetName);
if(!sheetName.trim().equals("")) {
// 获取指定sheet
sheet = wb.getSheet(sheetName);
}
int rows = sheet.getPhysicalNumberOfRows();
try {
if (rows > 0) {
List<Field> allFields = getMappedFiled(clazz, null);
Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
for (Field field : allFields) {
// 将有注解的field存放到map中.
if (field.isAnnotationPresent(ExcelAttribute.class)) {
ExcelAttribute attr = field.getAnnotation(ExcelAttribute.class);
int col = getExcelCol(attr.column());// 获得列号
maxCol = Math.max(col, maxCol);
field.setAccessible(true);// 设置类的私有字段属性可访问.
fieldsMap.put(col, field);
}
}
for (int i = 2; i < rows; i++) {// 从第3行开始取数据,默认第一行是表头.
HSSFRow row = sheet.getRow(i);
int cellNum = maxCol;
T entity = null;
for (int j = 0; j < cellNum; j++) {
String errorMsg = "";
HSSFCell cell = row.getCell(j);
if (cell == null) {
continue;
}
int cellType = cell.getCellType();
String c = "";
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
c = String.valueOf(cell.getNumericCellValue());
} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
c = String.valueOf(cell.getBooleanCellValue());
} else {
c = cell.getStringCellValue();
}
if (c == null || c.equals("")) {
continue;
}
entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
// System.out.println(cells[j].getContents());
Field field = fieldsMap.get(j);// 从map中得到对应列的field.
if (field == null) {
continue;
}
// 取得类型,并根据对象类型设置值.
Class<?> fieldType = field.getType();
if (String.class == fieldType) {
field.set(entity, String.valueOf(c));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
field.set(entity, Integer.parseInt(c));
} else if ((Long.TYPE == fieldType)
|| (Long.class == fieldType)) {
field.set(entity, Long.valueOf(c));
} else if ((Float.TYPE == fieldType)
|| (Float.class == fieldType)) {
field.set(entity, Float.valueOf(c));
} else if ((Short.TYPE == fieldType)
|| (Short.class == fieldType)) {
field.set(entity, Short.valueOf(c));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(entity, Double.valueOf(c));
} else if (Character.TYPE == fieldType) {
if ((c != null) && (c.length() > 0)) {
field.set(entity, Character
.valueOf(c.charAt(0)));
}
}
}
if (entity != null) {
list.add(entity);
}
}
}
} catch (Exception e) {
e.printStackTrace();
log.error("[importExcel]: {}", e);
}
return list;
}
private static List<Field> getMappedFiled(Class clazz, List<Field> fields) {
if (fields == null) {
fields = new ArrayList<Field>();
}
Field[] allFields = clazz.getDeclaredFields();
// 所有field并存放到一个list中.
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelAttribute.class)) {
fields.add(field);
}
}
if (clazz.getSuperclass() != null
&& !clazz.getSuperclass().equals(Object.class)) {
getMappedFiled(clazz.getSuperclass(), fields);
}
return fields;
}
/**
* 将EXCEL中A,B,C,D,E列映射成0,1,2,3
*
* @param col
*/
public static int getExcelCol(String col) {
col = col.toUpperCase();
// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
int count = -1;
char[] cs = col.toCharArray();
for (int i = 0; i < cs.length; i++) {
count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
}
return count;
}
}
、、、、、、、、、、、、、、、、、、、、、、、、、、
import com.jianwu.util.excel.RegexType;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Created by tookbra on 2016/4/7.
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelAttribute {
/**
* 标题， 该列名对应Excel中第一列的内容
*/
String value() default "";
/**
* 该列是否必须
*/
boolean required() default false;
/**
* 列名
* @return
*/
String column();
/**
* 正则表达式校验规则
*
* @return
*/
RegexType regexType() default RegexType.NONE;
/**
* 正则规则校验失败错误提示信息
*
* @return
*/
String regexpErrorMessage() default "";
public abstract boolean isExport() default true;
}
、、、、、、、、、、、、、、、、、、、、
/**
* 将EXCEL中A,B,C,D,E列映射成0,1,2,3
*
* @param col
*/
public static int getExcelCol(String col) {
col = col.toUpperCase();
// 从-1开始计算,字母从1开始运算。这种总数下来算数正好相同。
int count = -1;
char[] cs = col.toCharArray();
for (int i = 0; i < cs.length; i++) {
count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
}
return count;
}
、、、、、、、、、、、、、、、
import com.google.common.base.Strings;
import com.jianwu.util.RegexUtils;
import com.jianwu.util.excel.annotation.ExcelAttribute;
import org.apache.commons.lang3.StringUtils;
import java.lang.reflect.Field;
/**
* Created by tookbra on 2016/4/7.
*/
public class ExcelValidate {
private static ExcelAttribute excelAttribute;
public static String valid(Object object) throws Exception{
//获取object的类型
Class<? extends Object> clazz=object.getClass();
//获取该类型声明的成员
Field[] fields = clazz.getDeclaredFields();
//遍历属性
for(Field field:fields){
//对于private私有化的成员变量，通过setAccessible来修改器访问权限
field.setAccessible(true);
String errorMsg = validate(field,object);
//重新设置会私有权限
field.setAccessible(false);
if(Strings.isNullOrEmpty(errorMsg)) {
continue;
}
return errorMsg;
}
return null;
}
public static String validate(Field field,Object object) throws Exception{
String description;
Object value;
//获取对象的成员的注解信息
excelAttribute = field.getAnnotation(ExcelAttribute.class);
value = field.get(object);
if(excelAttribute == null) {
return "";
} else {
description = excelAttribute.value();
}
if(excelAttribute.required()){
if(value == null || StringUtils.isBlank(value.toString())){
return description + "不能为空";
}
}
if(excelAttribute.regexType() != RegexType.NONE){
switch (excelAttribute.regexType()) {
case NONE:
break;
case SPECIALCHAR:
if(RegexUtils.hasSpecialChar(value.toString())){
return excelAttribute.regexpErrorMessage();
}
break;
case EMAIL:
if(!RegexUtils.isEmail(value.toString())){
return excelAttribute.regexpErrorMessage();
}
break;
case IP:
if(!RegexUtils.isIp(value.toString())){
return excelAttribute.regexpErrorMessage();
}
break;
case NUMBER:
if(!RegexUtils.isNumber(value.toString())){
return excelAttribute.regexpErrorMessage();
}
break;
case PHONENUMBER:
if(!RegexUtils.isPhoneNumber(value.toString())){
return excelAttribute.regexpErrorMessage();
}
break;
default:
break;
}
}
return null;
}
}
、、、、、、、、、、、、、、、、
package com.jianwu.util.excel;
/**
* Created by tookbra on 2016/4/7.
*/
public enum RegexType {
NONE,
SPECIALCHAR,
EMAIL,
IP,
NUMBER,
PHONENUMBER;
}
、、、、、、、、、、、、、、、、、、、、
import com.google.common.base.Strings;
import com.jianwu.util.excel.annotation.ExcelAttribute;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Created by tookbra on 2016/4/6.
*/
public class ExcelUtil {
private static final Log log = LogFactory.getLog(ExcelUtil.class);
public static <T> List<T> importExcel(Class<T> clazz, HSSFWorkbook wb, String sheetName) {
int maxCol = 0;
List<T> list = new ArrayList<T>();
List<T> errorList = new ArrayList<T>();
if(Strings.isNullOrEmpty(sheetName)) {
//没有制定sheet返回null
return null;
}
HSSFSheet sheet = wb.getSheet(sheetName);
if(!sheetName.trim().equals("")) {
// 获取指定sheet
sheet = wb.getSheet(sheetName);
}
int rows = sheet.getPhysicalNumberOfRows();
try {
if (rows > 0) {
List<Field> allFields = getMappedFiled(clazz, null);
Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
for (Field field : allFields) {
// 将有注解的field存放到map中.
if (field.isAnnotationPresent(ExcelAttribute.class)) {
ExcelAttribute attr = field.getAnnotation(ExcelAttribute.class);
int col = getExcelCol(attr.column());// 获得列号
maxCol = Math.max(col, maxCol);
field.setAccessible(true);// 设置类的私有字段属性可访问.
fieldsMap.put(col, field);
}
}
for (int i = 2; i < rows; i++) {// 从第3行开始取数据,默认第一行是表头.
HSSFRow row = sheet.getRow(i);
int cellNum = maxCol;
T entity = null;
for (int j = 0; j < cellNum; j++) {
String errorMsg = "";
HSSFCell cell = row.getCell(j);
if (cell == null) {
continue;
}
int cellType = cell.getCellType();
String c = "";
if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
c = String.valueOf(cell.getNumericCellValue());
} else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
c = String.valueOf(cell.getBooleanCellValue());
} else {
c = cell.getStringCellValue();
}
if (c == null || c.equals("")) {
continue;
}
entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
// System.out.println(cells[j].getContents());
Field field = fieldsMap.get(j);// 从map中得到对应列的field.
if (field == null) {
continue;
}
// 取得类型,并根据对象类型设置值.
Class<?> fieldType = field.getType();
if (String.class == fieldType) {
field.set(entity, String.valueOf(c));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
field.set(entity, Integer.parseInt(c));
} else if ((Long.TYPE == fieldType)
|| (Long.class == fieldType)) {
field.set(entity, Long.valueOf(c));
} else if ((Float.TYPE == fieldType)
|| (Float.class == fieldType)) {
field.set(entity, Float.valueOf(c));
} else if ((Short.TYPE == fieldType)
|| (Short.class == fieldType)) {
field.set(entity, Short.valueOf(c));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(entity, Double.valueOf(c));
} else if (Character.TYPE == fieldType) {
if ((c != null) && (c.length() > 0)) {
field.set(entity, Character
.valueOf(c.charAt(0)));
}
}
}
if (entity != null) {
list.add(entity);
}
}
}
} catch (Exception e) {
e.printStackTrace();
log.error("[importExcel]: {}", e);
}
return list;
}
private static List<Field> getMappedFiled(Class clazz, List<Field> fields) {
if (fields == null) {
fields = new ArrayList<Field>();
}
Field[] allFields = clazz.getDeclaredFields();
// 所有field并存放到一个list中.
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelAttribute.class)) {
fields.add(field);
}
}
if (clazz.getSuperclass() != null
&& !clazz.getSuperclass().equals(Object.class)) {
getMappedFiled(clazz.getSuperclass(), fields);
}
return fields;
}
/**
* 将EXCEL中A,B,C,D,E列映射成0,1,2,3
*
* @param col
*/
public static int getExcelCol(String col) {
col = col.toUpperCase();
// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
int count = -1;
char[] cs = col.toCharArray();
for (int i = 0; i < cs.length; i++) {
count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
}
return count;
}
}