使用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();    }