一、前言
最近公司项目有很多地方需要导入execel文件,之前用的那个没有对execel导入做统一的处理,是针对每一个execel表格分别处理的,这样处理起来很麻烦,每次都要写很多的逻辑代码,而且还没有对execel表格的内容做约束,这样很可能出现问题。所以,有必要对execel表格文件做统一的处理,而不是针对某一个execel文件。
二、设计概述
对execel表格数据的校验采用Hibernate Validator这个组件,这个校验器很强大,具体有什么功能请参考官方文档;由于数据的内容、格式每次都是变化的,故针对每一行数据需要动态地把它封装成一个对象,这时候就需要用到动态语言,我用的是Spring EL语言,这个特性是Spring3.0 采用的,很强大,具体的可参考官方文档。
三、实现步骤
1、找数据范围
execel表格的数据范围很灵活,不用限定在某行、某列,我们假设execel表格的数据是集中的,也即集中在某一个矩形区域,我们只需要扫描每一行每一列,找到数据开始的单元格,它左边、下面就都是数据内容了。
2、封装成字符串
execel的单元格格式很不统一,我们需要把每一个单元格的数据内容都解析成字符串,然后以一行为单位,封装成字符串数字,再把数据送到需要装载的Bean,让它自己去封装。
四、实现考虑
如果表格数据太多,一次性就把数据都解析完了,这样可能会使系统崩溃了,所有,我的做法是每解析一行数据,就把它插入数据库,这样,再多的数据也不会有问题。
校验的时候,需要写个配置文件,这个配置文件说明了需要导入的表格的内容以及各式,以免非法导入。
五、代码
1、需要引入的包(我只是大概说一下,可能会有遗漏)
解析xml用到的包 dom4j
解析execel用到的包 poi相关包
动态解析用到的包 Spring EL
代码结构
一个异常类、一个校验数据转换类、一个主类、一个配置文件、以及相关测试类。
最主要是一个用来解析Execel的类,另外一个需要校验的类
package test.bean;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.validation.constraints.DecimalMin;
import javax.validation.constraints.Null;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.NotEmpty;
import com.dcrx.execelimport.ValidationExeception;
public class Warehouse {
@NotEmpty
private String warehouseId;
@Length(min = 2, max = 16)
private String warehouseName;
private int id;
//@Range(min=2,max=2)
@NotEmpty
@Pattern(regexp = "[12]")
private String type = "1"; // 1-自有,2-代储
private int status = 0; // 0-正常,1-删除
private String companyId = "";
private Date date;
@DecimalMin("0.1")
private double dou;
private float flo;
private boolean boo;
DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式
public void assertEquals(String[] values,int size) throws ValidationExeception{
if(values.length != size)
throw new ValidationExeception("配置文件提供"+values.length+"个字段"+",而导入数据库需要提供"+size+"个字段");
}
public String getIntString(String val){
if(val.equals(""))
return "";
else{
double d = Double.valueOf(val);
Integer i = (int)d;
return i.toString();
}
}
public int getInt(String val){
return val.equals("")?0:Integer.valueOf(getIntString(val));
}
public float getFloat(String val){
return val.equals("")?0.0f:Float.valueOf(val);
}
public double getDouble(String val){
return val.equals("")?0.0:Double.valueOf(val);
}
public Date getDate(String val) throws ParseException{
return val.equals("") ? new Date() : format.parse(val);
}
public void setValues(String[] values) throws Exception{
assertEquals(values,7);
this.warehouseId = values[0];
this.warehouseName = values[1];
this.type = getIntString(values[2]);
this.date = values[3].equals("") ? new Date() : format.parse(values[3]);
this.dou = values[4].equals("") ? 0.0 : Double.valueOf(values[4]);
this.flo = values[5].equals("") ? 0.0f : Float.valueOf(values[5]);
this.boo = values[6].equals("") ? false : Boolean.valueOf(values[6]);
}
public boolean isBoo() {
return boo;
}
public void setBoo(boolean boo) {
this.boo = boo;
}
public String getWarehouseId() {
return warehouseId;
}
public void setWarehouseId(String warehouseId) {
this.warehouseId = warehouseId;
}
public String getWarehouseName() {
return warehouseName;
}
public void setWarehouseName(String warehouseName) {
this.warehouseName = warehouseName;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public String getCompanyId() {
return companyId;
}
public void setCompanyId(String companyId) {
this.companyId = companyId;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public double getDou() {
return dou;
}
public void setDou(double dou) {
this.dou = dou;
}
public float getFlo() {
return flo;
}
public void setFlo(float flo) {
this.flo = flo;
}
}
以上是一个实体类,具体的校验规则是用到了Hibernate Validator 的语法
下面是主类
package com.dcrx.execelimport;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
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.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.expression.EvaluationException;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
/**
* @Desc 对execel文件的校验并且解析
* @author jackshen
*
*/
@SuppressWarnings("rawtypes")
public class ExecelUtil {
Logger logger = Logger.getLogger(this.getClass());
private String description = "";// 如果校验失败,将会给出详细提示信息
private Sheet sheet;// execel 对象
private List<String> fieldList;//从xml读取到的execel表格信息
private int rowIndex = 0;//当前操作行
private Object objectBean;//每一行数据封装
private Cell cellStart;// 数据的开始单元格
private Class clazz; //需要封装的类
private Validator validator; //hibernate 的校验器
private String[] fieldVals ; //从execel读到的某一行的数据
private int fieldSize = 0; //有效数据的列数
DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式
private Expression exp ;//EL 解析器
private ExpressionParser parser;
public String getDescription() {
return description;
}
public Object getObjectBean() {
return objectBean;
}
/**
*
* @param execelFilename
* execel文件名
* @param xmlFilename
* execel文件所对应的校验文件
* @param calzz 需要封装的类
*/
public ExecelUtil(InputStream execelIS,String xmlFilename,Class clazz) throws ValidationExeception{
// 打开execel工作簿
Workbook wb = null;
try {
wb = new HSSFWorkbook(execelIS);
} catch (IOException e) {
logger.error(e);
throw new ValidationExeception("","加载文件失败,请确保是否是Execel表格");
}
sheet = wb.getSheetAt(0);// 默认取第一个工作簿
//读配置文件,获取所有的属性列描述
fieldList = this.readFieldsFromXML(getAbsolutePath(xmlFilename));
//个数
fieldSize = fieldList.size();
//找到有效数据的开始单元格
cellStart = this.findStartCell();
if(cellStart == null){
throw new ValidationExeception("",this.description);
}
//每次读取一行execel数据,rowIndex每次增1
rowIndex = cellStart.getRowIndex()+1;
//需要封装的对象类
this.clazz = clazz;
//初始化校验器
ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
validator = factory.getValidator();
//初始化EL解析器
parser = new SpelExpressionParser();
exp = parser.parseExpression("values");
}
//是否还有数据
public boolean hasNext(){
Row row = sheet.getRow(rowIndex++);
if(row == null)
return false;
fieldVals = this.getRowValues(row, cellStart.getColumnIndex());
if(Arrays.asList(fieldVals).indexOf("") != -1){
for(String s :fieldVals)//如果每个字段都是空的,则返回false 否则true
if(!s.equals(""))
return true;
return false;
}
return true;
}
//校验
public boolean validate(){
try {
objectBean = Class.forName(clazz.getName()).newInstance();
} catch (Exception e) {
logger.error(e);
}
try{
exp.setValue(objectBean, fieldVals);// 给objectBean的属性赋值
}catch(EvaluationException e){//由于所有的数据类型转换都有objectBean里面来处理,故可能有异常,需要进行相应的处理
List exList = Arrays.asList("ParseException","NumberFormatException");//一般可能发生的异常
Throwable t = e.getCause();
while(t!=null){
String causeClazz = t.getClass().getSimpleName();
if(exList.contains(causeClazz)){
this.description = "第" +rowIndex+"行,类型转换失败:"+t.getMessage();
return false;
}else if(causeClazz.equals("ValidationExeception")){//自定义异常
this.description = "第" +rowIndex+"行,"+t.getMessage();
return false;
}else
t = t.getCause();
}
this.description = parser.parseExpression("message").getValue(objectBean,String.class);
return false;
}
//校验,校验规则是配置在objectBean对象里面
Set<ConstraintViolation<Object>> constraintViolations = validator.validate(objectBean);
if(constraintViolations.size() >0){//校验失败时,提示相应信息
for (ConstraintViolation<Object> vl : constraintViolations) {
this.description = "第" +rowIndex+"行,校验出错:"+ vl.getPropertyPath() + "=" + vl.getInvalidValue() + ":" + vl.getMessage();
return false;
}
}
return true;
}
private String[] getRowValues(Row row,int columnStartIndex){
String[] values = new String[fieldSize];
for(int j = columnStartIndex,t=0;t<fieldSize;j++,t++){
Cell c = row.getCell(j);
if(c==null){
values[t] = "";
continue;
}
switch(c.getCellType()){
case Cell.CELL_TYPE_BLANK:
values[t] = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
values[t] = String.valueOf(c.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(c))
values[t] = format.format(c.getDateCellValue());
else
values[t] = String.valueOf(c.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
values[t] = String.valueOf(c.getStringCellValue());
break;
default:
values[t] = "";
break;
}
}
return values;
}
// 根据某一个单元格,得到更人性化的显示,例如“A4”
private String getCellRef(Cell cell) {
return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
}
private String getAbsolutePath(String file) throws ValidationExeception {
try {
file = this.getClass().getClassLoader().getResource(file).getFile();
} catch (NullPointerException e) {
throw new ValidationExeception(file, "文件不存在");
}
try {
// 解决当出现中文路径时不能解析的bug
file = URLDecoder.decode(file, "UTF-8");
} catch (UnsupportedEncodingException e) {
throw new ValidationExeception(file, "解码失败");
}
return file;
}
private List<String> readFieldsFromXML(String xmlFilename)throws ValidationExeception{
SAXReader reader = new SAXReader();
Document document = null;
try {
document = reader.read(new File(xmlFilename));// 加载配置文件
} catch (DocumentException e) {
e.printStackTrace();
this.description = "IO 异常,读取配置文件失败";
throw new ValidationExeception(xmlFilename,"IO 异常,读取配置文件失败");
}
Element root = document.getRootElement();
List<String> fields = new ArrayList<String>();
for (Iterator iter = root.elementIterator("field"); iter.hasNext();) {
Element field = (Element) iter.next();
fields.add(field.getTextTrim());
}
return fields;
}
/**
* 从execel表中找到数据开始的单元格
* @return
*/
private Cell findStartCell(){
String firstFieldDesc = this.fieldList.get(0);
int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
for(int i = 0;i<=endRow;i++){
Row r = sheet.getRow(i);
if (r == null)
continue;
for(int j = 0;j < r.getLastCellNum();j++){
Cell c = r.getCell(j);
if( c == null)
continue;
if(c.getCellType() == Cell.CELL_TYPE_STRING){
if(c.getStringCellValue().trim().equals(firstFieldDesc)){//找到第一个符合要求的字段,接下来判断它相邻的字段是否都符合要求
if(fieldList.size()>r.getLastCellNum()-j){
this.description = "execel表格与所给配置描述不符,请下载模板文件";
return null;
}
for(int k=j+1,t=1;k<=j+fieldList.size()-1;k++,t++){
Cell c2 = r.getCell(k);
if(c2 == null){
this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
return null;
}
if(c2.getCellType() == Cell.CELL_TYPE_STRING){
if(c2.getStringCellValue().contains(fieldList.get(t)))
continue;
else{
this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
return null;
}
}
}
return c;
}else
continue;
}else
continue;
}
}
this.description = "找不到\""+fieldList.get(0)+"\"这一列";
return null;
}
}
附件有一个完整的Demo,只需要引入相应的包就能跑。
http://download.youkuaiyun.com/detail/jackshen310/4472852