基于 Aspose.Cells与XML导入excel 数据----操作类封装
前言
导入excel数据， 在每个项目中基本上都会遇到，第三方插件或者基于微软office，用的最多的就是npoi，aspose.cells和c#基于office这三种方式，其中各有各的优缺点，在这也不再累述。导入npoi封装的方法特别好，但是导出好像没有那一个是进行封装操作的，这两天正好有这块需求，所有就写了一个操作类xx，请不要看到xml就说这不是还要写东西吗，此封装是为了减少代码的书写。功能扩展基本上来源于XML。
需求分析
1.首先我们要做的就是要把excel中的数据导入到数据库，基于项目中的导入封装，我要吧excel转为对象数据集即：list<model>，那么就需要我们的model要和excle的数据对象进行编写。
2.在操作过程中会有一些特殊字段，比如PK(主键)，外键（客户不知道id只能根据名称找到id）
3.验证与判断：
验证---是有些字段必须不能为空等
判断----对象类型
为此我们就大致知道我们的xml到底该怎么来定义了。如下图
图片上已经大致介绍了每个节点表示的什么意思。其中OracleType如果为空excel可以为空。其他节点就不再累述。
功能实现
接下来实现就很简单了，只要获取excel数据集，让后获得对象数据的值把它和对象属性字段对象组成键值对，然后利用反射进行对象转换即可。
根基路径读取文件
/// <summary> /// 根据文件路径，读取文件 /// </summary> /// <typeparam name="T">对象</typeparam> /// <param name="strPath">文件地址</param> /// <param name="name">对象xml对应节点</param> /// <returns></returns> public List<T> ReadData<T>(string strPath, string name) { List<T> list = new List<T>(); Workbook wb = new Workbook(strPath); foreach (Worksheet ws in wb.Worksheets) { if (ws.Name == "ReadOnly") continue; ExcelToModel r = new ExcelToModel(); var l = r.ReadTable<T>(ws, name); list.AddRange(l); } return list; }
上述代码中支持多页excel上传如果你们像之上传第一页也行 list.AddRange(l) 合并list,你们可以把对象定义成嵌套对象也可以进行相应处理，在这不再累述。
具体实现代码
/// <summary> /// 获取excel数据集合 /// </summary> /// <typeparam name="T">对象</typeparam> /// <param name="ws">excel数据流</param> /// <param name="xmlNodeName">xml对应节点节点</param> /// <returns></returns> public List<T> ReadTable<T>(Worksheet ws, string xmlNodeName) { Cells all = ws.Cells; DataSet ds = new DataSet(); ds.ReadXml(System.Web.HttpContext.Current.Request.MapPath("~/ImportFiles/InputRules.xml")); DataTable dt = ds.Tables[xmlNodeName]; // 数据区 int idx = -1; List<T> list = new List<T>(); for (int i = 1; i < all.Rows.Count; i++) { T model = Activator.CreateInstance<T>(); Dictionary<string, object> dic = new Dictionary<string, object>(); for (int J = 0; J < ds.Tables[xmlNodeName].Rows.Count; J++) { #region 获取excel表格中的值 int ExcelColumn = int.Parse(ds.Tables[xmlNodeName].Rows[J]["ExcelColumn"].ToString()); string OracleType = ds.Tables[xmlNodeName].Rows[J]["OracleType"].ToString(); string OracleColumn = ds.Tables[xmlNodeName].Rows[J]["OracleColumn"].ToString(); string ParameterName = ds.Tables[xmlNodeName].Rows[J]["ParameterName"].ToString(); string MethodName = ds.Tables[xmlNodeName].Rows[J]["MethodName"].ToString(); string strValue = string.Empty; strValue = ExcelColumn > -1 ? all[i, ExcelColumn].StringValue : ""; if (!Verification(ExcelColumn, OracleType, strValue)) break; if (!string.IsNullOrEmpty(ParameterName.ToString())) { string ParameterValue = all[i, int.Parse(ParameterName)].StringValue; var factory = CustomParmeter_Factory.Cresate_CustomParmeter_Factory(MethodName, ParameterValue); strValue = factory.GetCustomParmeter(); if (string.IsNullOrEmpty(strValue)) break; if (OracleType == "Guid") dic.Add(OracleColumn, new Guid(strValue)); } else if (OracleType == "PK") dic.Add(OracleColumn, Guid.NewGuid()); else if (OracleType == "datetime") dic.Add(OracleColumn, Convert.ToDateTime(strValue)); else if (OracleType == "decimal") dic.Add(OracleColumn, Convert.ToDecimal(strValue)); else if (OracleType == "int") dic.Add(OracleColumn, Convert.ToInt32(strValue)); else if (OracleType == "Guid") dic.Add(OracleColumn, new Guid(strValue)); else dic.Add(OracleColumn, strValue); #endregion } PropertyInfo[] modelPro = model.GetType().GetProperties(); if (modelPro.Length > 0 && dic.Count() > 0) { for (int z = 0; z < modelPro.Length; z++) { if (dic.ContainsKey(modelPro[z].Name)) { modelPro[z].SetValue(model, dic[modelPro[z].Name], null); } } list.Add(model); } } return list; }
上述代码，首先获取xml各个节点：
其中ExcelColumn根据此节点获取OracleColumn（数据库对象名称）数据，把两字段存入字典 Dictionary<string, object>；
OracleType根据此字段进行条件判断，与数据类型转换（excel获取过来的都是string）代码如下：
判断
/// <summary> /// 数据验证 /// </summary> /// <param name="ExcelColumn">excek列号</param> /// <param name="OracleType">数据类型</param> /// <param name="strValue">获取的值</param> /// <returns></returns> public static bool Verification(int ExcelColumn, string OracleType, string strValue) { DateTime dtDate; if (ExcelColumn < 0) return true; if (string.IsNullOrEmpty(OracleType) || string.IsNullOrEmpty(strValue)) return false; if (OracleType == "datetime" && !DateTime.TryParse(strValue, out dtDate)) return false; return true; }
转换：
for (int J = 0; J < ds.Tables[xmlNodeName].Rows.Count; J++) { #region 获取excel表格中的值 int ExcelColumn = int.Parse(ds.Tables[xmlNodeName].Rows[J]["ExcelColumn"].ToString()); string OracleType = ds.Tables[xmlNodeName].Rows[J]["OracleType"].ToString(); string OracleColumn = ds.Tables[xmlNodeName].Rows[J]["OracleColumn"].ToString(); string ParameterName = ds.Tables[xmlNodeName].Rows[J]["ParameterName"].ToString(); string MethodName = ds.Tables[xmlNodeName].Rows[J]["MethodName"].ToString(); string strValue = string.Empty; strValue = ExcelColumn > -1 ? all[i, ExcelColumn].StringValue : ""; if (!Verification(ExcelColumn, OracleType, strValue)) break; if (!string.IsNullOrEmpty(ParameterName.ToString())) { string ParameterValue = all[i, int.Parse(ParameterName)].StringValue; var factory = CustomParmeter_Factory.Cresate_CustomParmeter_Factory(MethodName, ParameterValue); strValue = factory.GetCustomParmeter(); if (string.IsNullOrEmpty(strValue)) break; if (OracleType == "Guid") dic.Add(OracleColumn, new Guid(strValue)); } else if (OracleType == "PK") dic.Add(OracleColumn, Guid.NewGuid()); else if (OracleType == "datetime") dic.Add(OracleColumn, Convert.ToDateTime(strValue)); else if (OracleType == "decimal") dic.Add(OracleColumn, Convert.ToDecimal(strValue)); else if (OracleType == "int") dic.Add(OracleColumn, Convert.ToInt32(strValue)); else if (OracleType == "Guid") dic.Add(OracleColumn, new Guid(strValue)); else dic.Add(OracleColumn, strValue); #endregion }
其中没有把这段代码独立出来，其实你们可以独立出来的。
其中最为关键的为列号为-1的，表示它是主键或者需要根据excel中的值进行特殊获取。我这边是写了一个工厂类，为什么写工厂，考虑的是为了容易扩展，其实为了装13
public class CustomParmeter_Factory { public static ICustomParmeter Cresate_CustomParmeter_Factory(string MethodName, string ParameterName) { ICustomParmeter newService = null; switch (MethodName) { case "SetLine": newService = new SetLine(ParameterName); break; case "SetSegment": newService = new SetSegment(ParameterName); break; case "SetSite": newService = new SetSite(ParameterName); break; } return newService; } } public interface ICustomParmeter { string GetCustomParmeter(); } public class SetSegment : ICustomParmeter { private string ParameterName; public SetSegment(string _ParameterName) { ParameterName = _ParameterName; } public string GetCustomParmeter() { CustomParmeterCommonService c = new CustomParmeterCommonService(); return c.GetSegmentPatmet(ParameterName).ToString(); } } public class SetLine : ICustomParmeter { private static string ParameterName; public SetLine(string _ParameterName) { ParameterName = _ParameterName; } public string GetCustomParmeter() { CustomParmeterCommonService c = new CustomParmeterCommonService(); return c.GetLineParmet(ParameterName).ToString(); } } public class SetSite : ICustomParmeter { private string ParameterName; public SetSite(string _ParameterName) { ParameterName = _ParameterName; } public string GetCustomParmeter() { CustomParmeterCommonService c = new CustomParmeterCommonService(); return c.GetSitePatmet(ParameterName).ToString(); } }
其中还有一块比较重要的代买需要说明一下就是字典转为对象类型：
字典转为对象类型
PropertyInfo[] modelPro = model.GetType().GetProperties(); if (modelPro.Length > 0 && dic.Count() > 0) { for (int z = 0; z < modelPro.Length; z++) { if (dic.ContainsKey(modelPro[z].Name)) { modelPro[z].SetValue(model, dic[modelPro[z].Name], null); } } list.Add(model); }
其中如果你想导入新的数据库只需在xml写上自己的对象节点即可：
<?xml version="1.0" encoding="utf-8" ?> <InputRules> <BuildUnit ExcelColumn="-1" DisplayText="主键" OracleColumn="BID" OracleType="PK" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="-1" DisplayText="线路ID" OracleColumn="LineID" OracleType="Guid" ParameterName="0" MethodName="SetLine"/> <BuildUnit ExcelColumn="-1" DisplayText="标段ID" OracleColumn="SegmentID" OracleType="Guid" ParameterName="1" MethodName="SetSegment"/> <BuildUnit ExcelColumn="-1" DisplayText="工点ID" OracleColumn="SiteID" OracleType="Guid" ParameterName="2" MethodName="SetSite"/> <BuildUnit ExcelColumn="0" DisplayText="线路" OracleColumn="LineName" OracleType="varchar50" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="1" DisplayText="标段" OracleColumn="SegmentName" OracleType="varchar" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="2" DisplayText="工点" OracleColumn="SiteName" OracleType="varchar" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="3" DisplayText="计取月度" OracleColumn="RememberTime" OracleType="varchar" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="4" DisplayText="金额（元）" OracleColumn="Money" OracleType="decimal" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="5" DisplayText="创建人" OracleColumn="CreateUser" OracleType="varchar" ParameterName="" MethodName=""/> <BuildUnit ExcelColumn="6" DisplayText="创建时间" OracleColumn="CreateTime" OracleType="datetime" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="-1" DisplayText="主键" OracleColumn="CUID" OracleType="PK" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="-1" DisplayText="线路ID" OracleColumn="LineID" OracleType="Guid" ParameterName="0" MethodName="SetLine"/> <ConstructionUnit ExcelColumn="-1" DisplayText="标段ID" OracleColumn="SegmentID" OracleType="Guid" ParameterName="1" MethodName="SetSegment"/> <ConstructionUnit ExcelColumn="0" DisplayText="线路" OracleColumn="LineName" OracleType="varchar50" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="1" DisplayText="标段" OracleColumn="SegmentName" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="2" DisplayText="使用年月" OracleColumn="UseYears" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="3" DisplayText="使用时间" OracleColumn="UseTime" OracleType="datetime" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="4" DisplayText="付款对象" OracleColumn="Paymenter" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="5" DisplayText="金额（元）" OracleColumn="Money" OracleType="decimal" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="6" DisplayText="用途" OracleColumn="Actiion" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="7" DisplayText="创建人" OracleColumn="CreateUser" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="8" DisplayText="备注" OracleColumn="Remork" OracleType="varchar" ParameterName="" MethodName=""/> <ConstructionUnit ExcelColumn="9" DisplayText="创建时间" OracleColumn="CreateTime" OracleType="datetime" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="-1" DisplayText="主键" OracleColumn="ID" OracleType="PK" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="-1" DisplayText="线路ID" OracleColumn="LineID" OracleType="Guid" ParameterName="0" MethodName="SetLine"/> <SupervisionUnit ExcelColumn="-1" DisplayText="标段ID" OracleColumn="SegmentID" OracleType="Guid" ParameterName="1" MethodName="SetSegment"/> <SupervisionUnit ExcelColumn="-1" DisplayText="工点ID" OracleColumn="SiteID" OracleType="Guid" ParameterName="2" MethodName="SetSite"/> <SupervisionUnit ExcelColumn="0" DisplayText="线路" OracleColumn="LineName" OracleType="varchar50" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="1" DisplayText="标段" OracleColumn="SegmentName" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="2" DisplayText="工点" OracleColumn="SiteName" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="3" DisplayText="审核时间" OracleColumn="AuditTime" OracleType="datetime" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="4" DisplayText="付款对象" OracleColumn="PayMenter" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="5" DisplayText="审核金额" OracleColumn="AuditMoney" OracleType="decimal" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="6" DisplayText="创建者" OracleColumn="CreateUser" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="7" DisplayText="审核意见" OracleColumn="AuditOpinion" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="8" DisplayText="监理审核意见" OracleColumn="BossOpinion" OracleType="varchar" ParameterName="" MethodName=""/> <SupervisionUnit ExcelColumn="9" DisplayText="备注" OracleColumn="Remork" OracleType="varchar" ParameterName="" MethodName=""/> </InputRules>
记得特殊字段要在工厂类中添加相应的自定义函数哈，至此整个的功能应该就实现了。
注：如果有更好的实现方案大家请一起讨论，如果有不合理的地方还请你提出你的建议。
