基于配置文件校验Excel
<?xml version="1.0"?> custCode 客户编码 custName 客户名称 materialCode 物料编码 materialName 物料名称 num 数量 ^(-)?(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,})?$ 数字 unit 单位 money 金额 ^(-)?(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,})?$ 数字用于匹配Excel格式的XML配置内容
///
/// 根据xml字典逐个单元格比较验证DataTable数据格式
///
/// Excel表格转化为的DataTable
/// 列名和列标题、格式化正则、格式类型xml配置文件路径
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));
}
}
}
}
}