Java使用jxl的api实现excel动态数据验证及导入导出

首先从优缺点上来说

一、jxl

优点：

Jxl对中文支持非常好，操作简单，方法看名知意。
Jxl是纯javaAPI，在跨平台上表现的非常完美，代码可以再windows或者Linux上运行而无需重新编写
支持Excel 95-2000的所有版本（网上说目前可以支持Excel2007了，还没有尝试过）
生成Excel 2000标准格式
支持字体、数字、日期操作
能够修饰单元格属性
支持图像和图表,但是这套API对图形和图表的支持很有限，而且仅仅识别PNG格式。

 
 
1
2
3
4
5
6
7
8

缺点：效率低，图片支持不完善，对格式的支持不如POI强大

二、POI

优点：

效率高（数据来源：http://blog.csdn.net/jarvis_java/article/details/4924099）
支持公式，宏，一些企业应用上会非常实用
能够修饰单元格属性
支持字体、数字、日期操作

 
 
1
2
3
4
5

缺点：不成熟,代码不能跨平台，貌似不少同行在使用工程中还碰到让人郁闷的BUG（最近的项目中也是遇到了一些bug，不过目前没有查出来是代码的问题还是POI的问题，总之问题很诡异，数据替代参数总有失败的。关于不能跨平台这一说，我也没有试验过，不过Java不是跨平台吗？POI是JAVA的一个组件，怎么就不能跨平台了呢，总之这些问题还需要在以后的项目中多多实践，才能比较出区别之处。）

适于以上的比较，如果我们可以使用jxl-api简单实现poi的某些功能，如自定义注解，通过反射原理动态验证excel表的数据，导入及导出excel文件。那么使用jxl-api还是十分高效的。

1.定义注解类，如金额可以使用BigDecimal，日期使用Date，数量使用Integer...，我创建了5个注解类

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelBigDecimal {
int scale() default 2;
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelBoolean {
ExcelEnum False();
ExcelEnum True();
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelDate {
String format();
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelValid {
String regexp();
}

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
String name();
int sort();
boolean nullable() default true;
}
通过这些注解类，我们就可验证、排序excel的内容了

2.创建excel表的及字段注解的实体类

布尔类字段输出到excel的内容使用枚举型

public enum ExcelEnum {

TRUE("是"), FALSE("否");

public  String name;

ExcelEnum(String name) {
this.name = name;
}

@Override
public String toString() {
return this.name;
}

}

把exccel的实体类字段的注解存放到一个自定义类

public class ExcelHelper implements Serializable {

private String name;
private String fieldName;
private int sort;
private boolean nullable;
private String regexp;
private String format;
private String falseName;
private String trueName;
private Integer scale;
private Class<?> clazz;
}

这个就是跟excel表内容对于的实体类，sort是输出excel表时字段的排序，对于某些字段可以用正则表达式去验证

public class OrderForm implements Serializable {

@ExcelField(name = "订单号", sort = 0, nullable = false)
@ExcelValid(regexp = "\\d{10}$")
private String orderNo;

@ExcelField(name = "姓名", sort = 1, nullable = false)
private String customerName;

@ExcelField(name = "支付金额", sort = 2, nullable = false)
@ExcelValid(regexp = "^[+-]?[0-9]+(.[0-9]{1,2})?$")
private BigDecimal orderAmt;

@ExcelField(name = "优惠金额", sort = 3, nullable = false)
@ExcelBigDecimal(scale = 2)
private BigDecimal discountAmt;

@ExcelField(name = "日期", sort = 4)
@ExcelDate(format = "yyyy/MM/dd HH:mm")
private Date createDate;

@ExcelField(name = "会员", sort = 5, nullable = false)
@ExcelBoolean(False = ExcelEnum.FALSE, True = ExcelEnum.TRUE)
private Boolean isMember;
}

3.写一个动态方法验证、excel内容转换成实体类及实体类转换成excel格式的方法

Validator是验证功能的实体类，ExcelUtil是一个动态反射及转换的实体类

public class Validator {

public static boolean isEffective(String paramString) {
return (paramString != null) && (!"".equals(paramString)) && (!" ".equals(paramString))
&& (!"null".equals(paramString)) && (!"\n".equals(paramString));
}

public static boolean IsNumber(String paramString) {
if (paramString == null)
return false;
return match("-?[0-9]*$", paramString);
}

public static boolean isValidDate(String str, String formatStr) {
boolean convertSuccess = true;
// 指定日期格式为四位年/两位月份/两位日期，注意yyyy/MM/dd区分大小写；
SimpleDateFormat format = new SimpleDateFormat(formatStr);
try {
// 设置lenient为false.
// 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01
format.setLenient(false);
format.parse(str);
} catch (ParseException e) {
// e.printStackTrace();
// 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
convertSuccess = false;
}
return convertSuccess;
}

public static boolean match(String paramString1, String paramString2) {
if (paramString2 == null) {
return false;
}
return Pattern.compile(paramString1).matcher(paramString2).matches();
}
}

public class ExcelUtil {


public static ExcelUtil excelUitl = null;


public static ExcelUtil getInstance() {
if (excelUitl == null) {
excelUitl = new ExcelUtil();
}
return excelUitl;
}


/**
* 读取注解值、字段名及字段的类型
* 
* @param className
* @return
* @throws Exception
*/


public Map<Integer, ExcelHelper> loadExcelAnnotationFieldVlaue(Class<?> className) throws Exception {
Map<String, ExcelHelper> temp = new HashMap<String, ExcelHelper>();
Field[] fields = className.getDeclaredFields();
for (Field field : fields) {
if (!field.getName().equals("serialVersionUID")) {
if (!field.isAccessible())
field.setAccessible(true);


ExcelHelper helper = new ExcelHelper();
Type type = field.getGenericType();
if (type instanceof Class<?>) {
Class<?> cls = (Class<?>) type;
helper.setClazz(cls);
}
helper.setFieldName(field.getName());
temp.put(field.getName(), helper);
Annotation[] ans = field.getAnnotations();
for (Annotation annotation : ans) {
if (annotation.annotationType().equals(ExcelField.class)) {
ExcelField fd = field.getAnnotation(ExcelField.class);
temp.get(field.getName()).setSort(fd.sort());
temp.get(field.getName()).setName(fd.name());
temp.get(field.getName()).setNullable(fd.nullable());
} else if (annotation.annotationType().equals(ExcelBoolean.class)) {
ExcelBoolean fd = field.getAnnotation(ExcelBoolean.class);
temp.get(field.getName()).setFalseName(fd.False().toString());
temp.get(field.getName()).setTrueName(fd.True().toString());
} else if (annotation.annotationType().equals(ExcelDate.class)) {
ExcelDate fd = field.getAnnotation(ExcelDate.class);
temp.get(field.getName()).setFormat(fd.format());
} else if (annotation.annotationType().equals(ExcelValid.class)) {
ExcelValid fd = field.getAnnotation(ExcelValid.class);
temp.get(field.getName()).setRegexp(fd.regexp());
} else if (annotation.annotationType().equals(ExcelBigDecimal.class)) {
ExcelBigDecimal fd = field.getAnnotation(ExcelBigDecimal.class);
temp.get(field.getName()).setScale(fd.scale());
}
}
}
}


Map<Integer, ExcelHelper> map = new HashMap<>();
for (Map.Entry<String, ExcelHelper> m : temp.entrySet()) {
map.put(m.getValue().getSort(), m.getValue());
}


return map;
}


/**
* 获取Excel显示的中文名及排列的顺序
* 
* @param className
* @return
*/
public Map<Integer, String> getExcelFieldName(Class<?> className) {
Map<Integer, String> map = new HashMap<Integer, String>();
Field[] fields = className.getDeclaredFields();
for (Field field : fields) {
if (!field.getName().equals("serialVersionUID")) {


if (field.isAnnotationPresent(ExcelField.class)) {
ExcelField fd = field.getAnnotation(ExcelField.class);
if (!field.isAccessible())
field.setAccessible(true);
map.put(fd.sort(), fd.name());
}
}
}
return map;
}


/**
* 比较Excel的头字段与实体类的showname数量、名称及顺序是否一致
* 
* @param sheet
* @param map
* @return
*/
public boolean equalsArrays(Sheet sheet, Map<Integer, String> map) {
boolean check = true;
for (int k = 0; k < sheet.getColumns(); k++) {
if (!sheet.getCell(k, 0).getContents().equals(map.get(k))) {
check = false;
break;
}
}
return check;
}


/**
* 校验实体类与Excel的字段是否是相同类型
* 
* @param sheet
* @param clazz
* @return
* @throws Exception
*/
public String checkExcelContent(Sheet sheet, Class<?> clazz) throws Exception {
StringBuilder result = new StringBuilder();
result.append("");
int size = sheet.getRows();
Cell[] heads = sheet.getRow(0);
Map<Integer, ExcelHelper> map = loadExcelAnnotationFieldVlaue(clazz);
for (int i = 1; i < size; i++) {
Cell[] cells = sheet.getRow(i);
int len = cells.length;
for (int j = 0; j < len; j++) {
boolean warnning = false;
ExcelHelper helper = map.get(j);
// 判断字段内容是否为非空字段
if (!helper.isNullable()) {
if (!Validator.isEffective(cells[j].getContents())) {
warnning = true;
}
}


if (!warnning) {
// 判断字段注解是否存在规则过滤
if (Validator.isEffective(cells[j].getContents())) {


if (Validator.isEffective(helper.getRegexp())) {
if (!Validator.match(helper.getRegexp(), cells[j].getContents())) {
warnning = true;
}
}
}
}


if (!warnning) {
if (Date.class.isAssignableFrom(helper.getClazz())) {
if (Validator.isEffective(cells[j].getContents())) {
if (!Validator.isValidDate(cells[j].getContents(), helper.getFormat())) {
warnning = true;
}
}
} else if (Boolean.class.isAssignableFrom(helper.getClazz())) {


if (!(cells[j].getContents().equals(helper.getFalseName())
|| cells[j].getContents().equals(helper.getTrueName()))) {
warnning = true;
}
} else if (Integer.class.isAssignableFrom(helper.getClazz())) {
if (!Validator.IsNumber(cells[j].getContents())) {
warnning = true;
}
} else if (BigDecimal.class.isAssignableFrom(helper.getClazz())) {
String regexp = "^[+-]?[0-9]+(.[0-9]{1," + (helper.getScale() != null ? helper.getScale() : 2)
+ "})?$";
if (!(Validator.match(regexp, cells[j].getContents()))) {
warnning = true;
}
}


}


if (warnning) {
if (result.toString().indexOf(heads[j].getContents()) == -1) {
result.append("[" + heads[j].getContents() + "]").append(",");
}
}
}
}
return result.toString();
}


/**
* 将Excel的内容转换成实体类
* 
* @param sheet
* @param clazz
* @return
* @throws Exception
*/
public <T> List<T> importExcelToEntity(Sheet sheet, Class<T> clazz) throws Exception {
List<T> list = new ArrayList<>();
Map<Integer, ExcelHelper> map = loadExcelAnnotationFieldVlaue(clazz);
int size = sheet.getRows();
for (int i = 1; i < size; i++) {
Cell[] cells = sheet.getRow(i);
int len = cells.length;
T t = (T) clazz.newInstance();
for (int j = 0; j < len; j++) {
ExcelHelper helper = map.get(j);
Field f = t.getClass().getDeclaredField(helper.getFieldName());
if (!f.isAccessible())
f.setAccessible(true);
if (Date.class.isAssignableFrom(helper.getClazz())) {
if (Validator.isEffective(cells[j].getContents())) {
f.set(t, new SimpleDateFormat(helper.getFormat()).parse(cells[j].getContents().toString()));
} else {
f.set(t, null);
}
} else if (BigDecimal.class.isAssignableFrom(helper.getClazz())) {
f.set(t, BigDecimal.valueOf(Double.valueOf(cells[j].getContents().toString()))
.setScale(helper.getScale() != null ? helper.getScale() : 2, BigDecimal.ROUND_HALF_UP));
} else if (Boolean.class.isAssignableFrom(helper.getClazz())) {
f.set(t, cells[j].getContents().toString().equals(helper.getTrueName()) ? true : false);
} else if (String.class.isAssignableFrom(helper.getClazz())) {
f.set(t, cells[j].getContents().toString());
} else if (Integer.class.isAssignableFrom(helper.getClazz())) {
f.set(t, Integer.valueOf(cells[j].getContents().toString()));
}
}
list.add(t);
}
return list;
}


/**
* 将实体类转换成List<Map>格式以便输出到Excel
* 
* @param list
* @return
* @throws Exception
*/
public <T> List<Map<Integer, String>> exportExcel(List<T> list) throws Exception {
Class<?> cls = list.get(0).getClass();
List<Map<Integer, String>> data = new ArrayList<>();
Map<Integer, ExcelHelper> helper = loadExcelAnnotationFieldVlaue(cls);
// 存放Excel文件头部的字段中文名
Map<Integer, String> title = new HashMap<>();
Set<Integer> set = helper.keySet();
for (Integer key : set) {
title.put(key, helper.get(key).getName());
}
data.add(title);


// 存放Excel的内容
for (T l : list) {
Map<Integer, String> contentMap = new HashMap<>();
for (int i = 0; i < helper.size(); i++) {
ExcelHelper excelHelper = helper.get(i);
Field f = cls.getDeclaredField(excelHelper.getFieldName());
if (!f.isAccessible())
f.setAccessible(true);
if (String.class.isAssignableFrom(excelHelper.getClazz())) {
contentMap.put(i, f.get(l) != null ? String.valueOf(f.get(l)) : "");
} else if (Date.class.isAssignableFrom(excelHelper.getClazz())) {
contentMap.put(i, new SimpleDateFormat(excelHelper.getFormat()).format(f.get(l)));
} else if (BigDecimal.class.isAssignableFrom(excelHelper.getClazz())) {
int scale = (excelHelper.getScale() != null && excelHelper.getScale().intValue() > 0)
? excelHelper.getScale().intValue()
: 2;
contentMap.put(i, String.valueOf(BigDecimal.valueOf(Double.valueOf(String.valueOf(f.get(l))))
.setScale(scale, BigDecimal.ROUND_HALF_UP)));
} else if (Boolean.class.isAssignableFrom(excelHelper.getClazz())) {
contentMap.put(i, Boolean.valueOf(f.get(l).toString()) ? excelHelper.getTrueName()
: excelHelper.getFalseName());
} else if (Integer.class.isAssignableFrom(excelHelper.getClazz())) {
contentMap.put(i, String.valueOf(Integer.valueOf(f.get(l).toString())));
}
}
data.add(contentMap);
}


return data;
}
}

通过以上代码，我们只要把Excel表对应的实体类的注解及规则定义好，就可以实现内容格式验证，导入导出功能了

现在开始测试吧

public class Test {


public static void main(String[] args) throws Exception {
String path = Test.class.getResource("import.xls").toURI().getPath();
File file = new File(path);


System.out.println(checkExcelTitleAndSort(file));


System.out.println(checkExcelContent(file));


excelToEntity(file);


// exportExcelFile(loadData(), "d:" + File.separator + "export.xls");
}


/**
* 验证Excel文件首部的列名及排列顺序是否跟定义的OrderForm类一致
* 
* @param file
* @return
*/
public static boolean checkExcelTitleAndSort(File file) {
InputStream stream = null;
Workbook rwb = null;
Boolean check = false;
try {
stream = new FileInputStream(file);
// 获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
// 获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
Map<Integer, String> titleAndSortMap = ExcelUtil.getInstance().getExcelFieldName(OrderForm.class);
check = ExcelUtil.getInstance().equalsArrays(sheet, titleAndSortMap);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (rwb != null) {
rwb.close();
}
}
return check;
}


/**
* 验证Excel文件的内容格式是否正确
* 
* @param file
* @return
*/
public static boolean checkExcelContent(File file) {
InputStream stream = null;
Workbook rwb = null;
Boolean check = false;
try {
stream = new FileInputStream(file);
// 获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
// 获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
// 如有验证失败，该方法会返回错字段的字段名称
String result = ExcelUtil.getInstance().checkExcelContent(sheet, OrderForm.class);


// 如果没有返回错误字段，表示验证通过
if (!Validator.isEffective(result)) {
check = true;
} else {
System.out.println(result);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (rwb != null) {
rwb.close();
}
}
return check;
}


/**
* 将Excel文件的内容转换成实体类
* 
* @param file
*/
public static void excelToEntity(File file) {
InputStream stream = null;
Workbook rwb = null;
try {
stream = new FileInputStream(file);
// 获取Excel文件对象
rwb = Workbook.getWorkbook(stream);
// 获取文件的指定工作表 默认的第一个
Sheet sheet = rwb.getSheet(0);
List<OrderForm> orderForms = ExcelUtil.getInstance().importExcelToEntity(sheet, OrderForm.class);
System.out.println(orderForms);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (rwb != null) {
rwb.close();
}
}
}


/**
* 将实体类的内容输出到Excel文件里
* 
* @param list
* @param filePath
*/
public static <T> void exportExcelFile(List<T> list, String filePath) {
OutputStream os = null;
try {
os = new FileOutputStream(new File(filePath));
List<Map<Integer, String>> dataList = ExcelUtil.getInstance().exportExcel(list);
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
String tmptitle = "订单信息";
WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
// 设置excel标题
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
wcfFC.setBackground(Colour.AQUA);
wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
wcfFC = new WritableCellFormat(wfont);
// 开始生成主体内容
// new Label(横坐标_X，纵坐标_Y,打印的名称)
for (int i = 0; i < dataList.size(); i++) {
for (int j = 0; j < dataList.get(i).size(); j++) {
wsheet.addCell(new Label(j, i, dataList.get(i).get(j)));
}
}


// 写入文件
wbook.write();
// 主体内容生成结束
wbook.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}


public static List<OrderForm> loadData() {
List<OrderForm> orderForms = new ArrayList<>();
OrderForm o = new OrderForm();
o.setCreateDate(new Date());
o.setCustomerName("张三");
o.setOrderNo("1122334455");
o.setOrderAmt(BigDecimal.valueOf(1000d));
o.setDiscountAmt(BigDecimal.valueOf(100d));
o.setIsMember(true);
orderForms.add(o);
OrderForm o2 = new OrderForm();
o2.setCreateDate(new Date());
o2.setCustomerName("李四");
o2.setOrderNo("1234567890");
o2.setOrderAmt(BigDecimal.valueOf(500d));
o2.setDiscountAmt(BigDecimal.valueOf(20d));
o2.setIsMember(false);
orderForms.add(o2);
return orderForms;
}
}

这张表的内容是没有问题的，测试通过。并转换成对应的OrderForm实体类

这个excel的格式有问题的，所以验证失败，并提示有错误的列名。

最后测试导出excel文件了

所有测试全部通过，其实只要把excel对应的实体类及注解定义好，使用jxl-api也可以很方便的使用excel表的，并不一定要用poi。

代码下载地址：http://download.csdn.net/download/u011634836/10127309

github下载地址：https://github.com/gzdavidxiang/MyExcelTools