<?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}】,
请修正后继续。",
colchinese,
j + 1, curval, coldatatype));
}
}
}
}
}