使用xml去解析excel

目录

使用xml去对应的解析excel中数据，在xml中规定 数据的格式，

excel格式

其中 前三项是明显的 特殊信息，但是后面的内容 就是 公共内容了，而且要求可以 将excel 中的列名存入数据库字段中，所以使用xml

xml格式

注意解析的excel中的列名的名字和这个配置文件需要对应起来，这个配置文件是存放在 resource中，

<?xml version="1.0" encoding="UTF-8"?>
<TargetFromatList> 
    <content>
        <name>售后服务综合指标</name>
        <type>百分比</type>
    </content>
    <content>
        <name>售后处理时效（退款时长）</name>
        <type>小数点</type>
        <precision>2</precision>
    </content> 
</TargetFromatList>

xml对应的实体类

用于存放 xml中的信息

public class TargetUploadNumberFormat {
    String name;
    String type;
    int precision; 
    setter getter....

具体实现1

 List<TargetUploadNumberFormat>   uploadFormat=new ArrayList<TargetUploadNumberFormat>();
        try {
              uploadFormat = XmlUtil.getFromatList();
        } catch (Exception e) {
            LOGGER.error("解析xml出错",e); 
        }

xmlutil

import org.dom4j.Element;
import java.io.File; 
import org.dom4j.Document;
import org.dom4j.io.SAXReader; 
import java.lang.reflect.Field; 
import org.springframework.util.ResourceUtils;


public class XmlUtil {
    private static final Logger logger = LoggerFactory.getLogger(XmlUtil.class);

    public static List<TargetUploadNumberFormat>   getFromatList() throws Exception {
        SAXReader reader = new SAXReader();
        List<TargetUploadNumberFormat> list = new ArrayList<TargetUploadNumberFormat>();
        // 通过read方法读取一个文件 转换成Document对象
        File filepath=ResourceUtils.getFile("classpath:TargetUpload.xml");

        Document document = reader.read(filepath);
        //获取根节点元素对象
        Element node = document.getRootElement();
        //遍历所有的元素节点
        Iterator<Element> it = node.elementIterator("content");// 获取根节点下所有content
        while (it.hasNext()) {
            Element elementGroupService = (Element) it.next();
            TargetUploadNumberFormat baseBean = (TargetUploadNumberFormat) fromXmlToBean(
                    elementGroupService, TargetUploadNumberFormat.class);
            list.add(baseBean);
        }
        return list;
    } 
    @SuppressWarnings("rawtypes")
    public static Object fromXmlToBean(Element rootElt, Class pojo) throws Exception
    {
        // 首先得到pojo所定义的字段
        Field[] fields = pojo.getDeclaredFields();
        // 根据传入的Class动态生成pojo对象
        Object obj = pojo.newInstance();
        for (Field field : fields)
        {
            // 设置字段可访问（必须，否则报错）
            field.setAccessible(true);
            // 得到字段的属性名
            String name = field.getName();
            // 这一段的作用是如果字段在Element中不存在会抛出异常，如果出异常，则跳过。
            try
            {
                rootElt.elementTextTrim(name);
            }
            catch (Exception ex)
            {
                continue;
            }
            if (rootElt.elementTextTrim(name) != null && !"".equals(rootElt.elementTextTrim(name)))
            {
                // 根据字段的类型将值转化为相应的类型，并设置到生成的对象中。
                if (field.getType().equals(Long.class) || field.getType().equals(long.class))
                {
                    field.set(obj, Long.parseLong(rootElt.elementTextTrim(name)));
                }
                else if (field.getType().equals(String.class))
                {
                    field.set(obj, rootElt.elementTextTrim(name));
                }
                else if (field.getType().equals(Double.class) || field.getType().equals(double.class))
                {
                    field.set(obj, Double.parseDouble(rootElt.elementTextTrim(name)));
                }
                else if (field.getType().equals(Integer.class) || field.getType().equals(int.class))
                {
                    field.set(obj, Integer.parseInt(rootElt.elementTextTrim(name)));
                }
                else if (field.getType().equals(java.util.Date.class))
                {
                    field.set(obj, Date.parse(rootElt.elementTextTrim(name)));
                }
                else
                {
                    continue;
                }
            }
        }
        return obj;
    }
}

具体实现2

拿到具体的对应 entity后，

 for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            if (row==null)
            {  continue;  }
            String shopsName = ExcelUtil.getStringCellValue(row.getCell(0)).trim();

            for (int j = 0; j <uploadFormat.size() ; j++) {
                TargetUploadNumberFormat fromat=uploadFormat.get(j);
                String source=ExcelUtil.getStringCellValue(row.getCell(5+j)).trim();
                if (StringUtils.isNullOrBlank(source))
                {
                    continue;
                }
                String error= formatCheck(fromat,source);
                if (error!=null&&!error.equals(""))
                {
                    StringBuffer err=new StringBuffer();
                    err.append(" 第 ").append(i+1).append("行数据错误,").append(error);
                    errorMsg.add(err.toString());
                    continue;
                }
                TblTargetUploadInfo uploadInfo=new TblTargetUploadInfo();
                uploadInfo.setShopsName(shopsName); 
                uploadInfo.setTargetName(uploadFormat.get(j).getName());
                if (fromat.getType().equals("百分比")&&source.indexOf("%")>-1){
                    source=source.replace("%","");
                    BigDecimal num = new BigDecimal(0.01);
                    uploadInfo.setScore( new BigDecimal(source).multiply(num));
                }
                else{
                uploadInfo.setScore(new BigDecimal(source));
                }
                targetUploadInfos.add(uploadInfo);
            }

        }

校验的函数

   private String formatCheck(TargetUploadNumberFormat targetUploadNumberFormat, String trim) {
        StringBuffer error = new StringBuffer();
        StringBuffer regExp = new StringBuffer();
        if (targetUploadNumberFormat.getType().equals("整数")) {
            regExp.append("^[0-9]*[1-9][0-9]*$");
        }
        if (targetUploadNumberFormat.getType().equals("小数点")) {
            regExp.append("^[1-9][0-9]*(\\.[0-9]{1," + targetUploadNumberFormat.getPrecision() + "})?$");
        }
        if (targetUploadNumberFormat.getType().equals("百分比")) {
            regExp.append("^\\d+\\.?\\d*\\%?$");
        }
        Pattern p = Pattern.compile(regExp.toString());
        Matcher m = p.matcher(trim);
        if (!m.find()) {
            error.append(" 指标 : ")
                    .append(targetUploadNumberFormat.getName()).append("的类型为")
                    .append(targetUploadNumberFormat.getType())
            ;
            if (targetUploadNumberFormat.getPrecision() != 0) {
                error.append("小数点后最多").append(targetUploadNumberFormat.getPrecision()).append("位");
            }
            error.append(" ,当前excel数据为").append(trim);
            error.append(" , 请检查。");

        }
        return error.toString();
    }