欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

巧用XML配置校验导入Excel的列数据格式

程序员文章站 2024-01-22 08:36:46
巧用XML配置校验导入Excel的列数据格式
<?xml version="1.0"?>
<columnsseting xmlns:xsd="http://www.w3.org/2001/xmlschema" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance">

  <columns>
  <column>
      <columnname>custcode</columnname>
      <columnchinese>客户编码</columnchinese>
      <regcontent></regcontent>
      <datatype></datatype>
      
  </column>
  <column>
      <columnname>custname</columnname>
      <columnchinese>客户名称</columnchinese>
      <regcontent></regcontent>
      <datatype></datatype>
      
  </column>
  <column>
      <columnname>materialcode</columnname>
      <columnchinese>物料编码</columnchinese>
      <regcontent></regcontent>
      <datatype></datatype>
      
  </column>
  <column>
      <columnname>materialname</columnname>
      <columnchinese>物料名称</columnchinese>
      <regcontent></regcontent>
      <datatype></datatype>
      
  </column>
  <column>
      <columnname>num</columnname>
      <columnchinese>数量</columnchinese>
      <regcontent>^(-)?(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,})?$</regcontent>
      <datatype>数字</datatype>
      
  </column>
  <column>
      <columnname>unit</columnname>
      <columnchinese>单位</columnchinese>
      <regcontent></regcontent>
      <datatype></datatype>
      
  </column>
  <column>
      <columnname>money</columnname>
      <columnchinese>金额</columnchinese>
      <regcontent>^(-)?(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,})?$</regcontent>
      <datatype>数字</datatype>
      
  </column>
  </columns>
</columnsseting>
用于匹配excel格式的xml配置内容
        /// <summary>
        /// 根据xml字典逐个单元格比较验证datatable数据格式
        /// </summary>
        /// <param name="dt">excel表格转化为的datatable</param>
        /// <param name="xmlpath">列名和列标题、格式化正则、格式类型xml配置文件路径</param>
        public static void comparecolumnformat(datatable dt, string xmlpath)
        {
            if (dt == null) return;
            xmldocument xmldoc = xmlutility.getxmldocument(xmlpath);
            xmlnode root = xmldoc.selectsinglenode("columnsseting");
            xmlnode subnode = root.selectsinglenode("columns");
            xmlnodelist subnodelist = subnode.childnodes;
            for (int i = 0; i < subnodelist.count; i++)
            {
                string colname = xmlutility.getelement(subnodelist[i], "columnname");
                string colchinese = xmlutility.getelement(subnodelist[i], "columnchinese");
                string regcontent = xmlutility.getelement(subnodelist[i], "regcontent");
                string coldatatype = xmlutility.getelement(subnodelist[i], "datatype");

                for (int j = 0; j < dt.rows.count; j++)
                {
                    if (dt.columns[i].columnname.equals(colname))
                    {
                        string curval = convert.tostring(dt.rows[j][i]);
                        regex reg = new regex(regcontent);
                        if (!reg.ismatch(curval))
                        {
                            throw new exception(string.format("当前表格中【{0}】的第【{1}】行数据【{2}】的格式不正确,应为【{3}】,\n请修正后继续。",
                                colchinese,
                                j + 1, curval, coldatatype));
                        }
                    }
                }
            }
        }