<%@ Page Language="C#" AutoEventWireup="true" CodeFile="new_hosiptalmsysuser_importData.aspx.cs" Inherits="Saleto_new_hosiptalmsysuser_importData" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title> <asp:Literal ID="lblTitle" Text="医院对应代表关联数据导入" runat="server"></asp:Literal></title> <base target="_self" /> <link id="Link1" rel="stylesheet" href="../css/jquery-ui-all.css.aspx" /> <link id="Link2" rel="stylesheet" href="../css/global.css.aspx" /> <script type="text/javascript" src="../js/common/common.js.aspx"></script> <script type="text/javascript" src="../js/common/jquery-ui-all.js.aspx"></script> </head> <body> <form id="form1" runat="server"> <input id="hidMainId" runat="server" type="hidden" /> <div> <table align="center" width="98%" class="query"> <tr> <td class="dialog-header" colspan="2"> <asp:Label ID="lblQueryName" runat="server" Text="医院对应代表关联数据导入"></asp:Label> </td> </tr> <tr> <td> <asp:Label ID="Label7" runat="server" Text="选择导入文件路径:" CssClass="label" ForeColor="Black"></asp:Label> </td> <td style=" 480px;"> <asp:FileUpload ID="FileUpload1" runat="server" CssClass="file" Width="100%" /> </td> </tr> <tr> <td style="text-align: right; margin-right: 2.5%" colspan="2"> <asp:Button ID="btnImport" runat="server" Text="导 入 " CssClass="button" OnClick="btnImport_Click" /> </td> </tr> <tr> <td colspan="2" style="height: 18px; border-top- thin; border-left- thin; border-left-color: black; border-bottom- thin; border-bottom-color: black; border-top-color: black; border-right- thin; border-right-color: black;"> <img id="progress" alt="progress" src="/rektecweb/_imgs/uploading.gif" width="0px" /> </td> </tr> <tr> <td colspan="2" style="height: 320px; text-align: left;"> <div style=" 100%; height: 320px; overflow: scroll; border: 1px solid; background-color: White;"> <asp:Label ID="lblResult" runat="server" Height="100%" Width="100%"></asp:Label> </div> </td> </tr> </table> </div> </form> </body> </html>
using RekTec.Crm.Biz.Saleto; using RekTec.Crm.Common; using RekTec.Crm.Common.Web; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class Saleto_new_hosiptalmsysuser_importData : PageBase { protected void Page_Load(object sender, EventArgs e) { //做导入动作时，将界面的按钮变灰 Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache); ButtonOnClientClick(this.btnImport); // hidMainId.Value = Request["id"].ToString(); } void _log(string msg) { if (msg.StartsWith("[Error]")) this.lblResult.Text += "<font color='red'>" + msg + "</font><br>"; else this.lblResult.Text += msg + "<br>"; } /// <summary> /// 导入事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnImport_Click(object sender, EventArgs e) { try { if (this.FileUpload1.HasFile) { string filename = Server.MapPath("../../../rektecweb/upload/") + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_" + FileUpload1.FileName; //获取文件后缀名 string type = filename.Substring(filename.LastIndexOf(".") + 1); if (type.ToLower() != "xls" && System.IO.Path.GetExtension(filename).ToLower() != ".xlsx") { this.ShowMessage("上传的文件格式不正确"); return; } FileUpload1.SaveAs(filename); Logging log = new Logging(); log.LogUI += new Logging.LogUIEvent(_log); new_hosiptalmsysuser_importCommand cmd = new new_hosiptalmsysuser_importCommand(); cmd.ImportIn(filename, log); } else { this.ShowMessage("请选择要上传的文件"); } } catch (Exception ex) { } } }
using Microsoft.Xrm.Sdk; using RekTec.Core; using RekTec.Crm.Common; using RekTec.Crm.Entity; using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.Linq; using System.Text; namespace RekTec.Crm.Biz.Saleto { public class new_hosiptalmsysuser_importCommand:CrmCommand { public new_hosiptalmsysuser_importCommand() { } public new_hosiptalmsysuser_importCommand(PersisitBroker broker) : base(broker) { } public void ImportIn(string filepath, Logger Logger) { try { #region 读取Excel中的数据 DataTable dt = new DataTable(); OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;OLE DB Services=-4;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES\";"); try { try { conn.Open(); } catch (Exception exception) { throw new Exception("打开Excel文件失败！" + exception.Message, exception); } string sheetName = "sheet1"; OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}$] ", sheetName), conn); DataSet dataSet = new DataSet(); adapter.Fill(dataSet); dt = dataSet.Tables[0]; #region 添加列 #endregion } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } #endregion #region 检查并添加列 string[] columns = new string[] { "年份", "月份", "品类", "医院名称", "代表", "分配比例", "区域", "大区", "省区", "省份", "地级市", "主管", "省区经理", "大区经理", "区域总监", "代表常驻城市" }; this.CheckContainsColumns(dt, columns); #endregion PersisitBroker pb = new PersisitBroker(); try { pb.OpenDb(); #region 检查Excel并创建数据 int i = 0; int successCount = 0; string businessunitId = string.Empty; //区域 string areabusinessId = string.Empty; //大区 string productTypeId = string.Empty; //品类 string provinceId = string.Empty; //省区 string provinceUnitId = string.Empty; //省份 string cityId = string.Empty; //地级市 string hospitalId = string.Empty; //医院名称 string systemuserid = string.Empty; //代表 string directorid = string.Empty; //主管 string provincesManagerid = string.Empty; //省区经理 string districtManagerid = string.Empty; //大区经理 string regionalDirectorid = string.Empty; //区域总监 string representativeCityid = string.Empty; //代表常驻城市 foreach (DataRow row in dt.Rows) { #region 品类 不为空 if (row["品类"] is DBNull || string.IsNullOrEmpty(row["品类"].ToString())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的品类为空。"); i++; continue; } else { string name = row["品类"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select new_producttypeid from New_producttype where statecode=0 and new_name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { productTypeId = table.Rows[0]["new_producttypeid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的品类在系统中不存在。"); i++; continue; } } #endregion #region 医院名称 不为空 if (row["医院名称"] is DBNull || string.IsNullOrEmpty(row["医院名称"].ToString())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的医院名称为空。"); i++; continue; } else { string name = row["医院名称"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select accountid from account where statecode=0 and name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { hospitalId = table.Rows[0]["accountid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的医院名称在系统中不存在。"); i++; continue; } } #endregion #region 代表 不为空 if (row["代表"] is DBNull || string.IsNullOrEmpty(row["代表"].ToString())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的代表为空。"); i++; continue; } else { string name = row["代表"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select systemuserid from systemuser where domainname=@name"; dic.Add("@name", name); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { systemuserid = table.Rows[0]["systemuserid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的代表在系统中不存在。"); i++; continue; } } #endregion #region 区域 不为空 if (row["区域"] is DBNull || string.IsNullOrEmpty(row["区域"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的区域为空。"); i++; continue; } else { string name = row["区域"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select BusinessUnitId from businessunit where name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { businessunitId = table.Rows[0]["BusinessUnitId"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的区域在系统中不存在。"); i++; continue; } } #endregion #region 大区 不为空 if (row["大区"] is DBNull || string.IsNullOrEmpty(row["大区"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的大区为空。"); i++; continue; } else { string name = row["大区"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select BusinessUnitId from businessunit where name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { areabusinessId = table.Rows[0]["BusinessUnitId"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的大区在系统中不存在。"); i++; continue; } } #endregion #region 省区 不为空 if (row["省区"] is DBNull || string.IsNullOrEmpty(row["省区"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省区为空。"); i++; continue; } else { string name = row["省区"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select BusinessUnitId from businessunit where name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { provinceId = table.Rows[0]["BusinessUnitId"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省区在系统中不存在。"); i++; continue; } } #endregion #region 省份 不为空 if (row["省份"] is DBNull || string.IsNullOrEmpty(row["省份"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省份为空。"); i++; continue; } else { string name = row["省份"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select New_provinceId from new_province where New_name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { provinceUnitId = table.Rows[0]["New_provinceId"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省份在系统中不存在。"); i++; continue; } } #endregion #region 地级市 不为空 if (row["地级市"] is DBNull || string.IsNullOrEmpty(row["地级市"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的地级市为空。"); i++; continue; } else { string name = row["地级市"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select new_cityid from new_city where statecode=0 and new_name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { cityId = table.Rows[0]["new_cityid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的地级市在系统中不存在。"); i++; continue; } } #endregion #region 主管 不为空 if (row["主管"] is DBNull || string.IsNullOrEmpty(row["主管"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的主管为空。"); i++; continue; } else { string name = row["主管"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select systemuserid from systemuser where domainname=@name"; dic.Add("@name", name); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { directorid = table.Rows[0]["systemuserid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的主管在系统中不存在。"); i++; continue; } } #endregion #region 省区经理 不为空 if (row["省区经理"] is DBNull || string.IsNullOrEmpty(row["省区经理"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省区经理为空。"); i++; continue; } else { string name = row["省区经理"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select systemuserid from systemuser where domainname=@name"; dic.Add("@name", name); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { provincesManagerid= table.Rows[0]["systemuserid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的省区经理在系统中不存在。"); i++; continue; } } #endregion #region 大区经理 不为空 if (row["大区经理"] is DBNull || string.IsNullOrEmpty(row["大区经理"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的大区经理为空。"); i++; continue; } else { string name = row["大区经理"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select systemuserid from systemuser where domainname=@name"; dic.Add("@name", name); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { districtManagerid= table.Rows[0]["systemuserid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的大区经理在系统中不存在。"); i++; continue; } } #endregion #region 区域总监 不为空 if (row["区域总监"] is DBNull || string.IsNullOrEmpty(row["区域总监"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的区域总监为空。"); i++; continue; } else { string name = row["区域总监"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select systemuserid from systemuser where domainname=@name"; dic.Add("@name", name); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { regionalDirectorid = table.Rows[0]["systemuserid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的区域总监在系统中不存在。"); i++; continue; } } #endregion #region 代表常驻城市 不为空 if (row["代表常驻城市"] is DBNull || string.IsNullOrEmpty(row["代表常驻城市"].ToString().Trim())) { Logger.InfoMsg("第" + (i + 2).ToString() + "行的代表常驻城市为空。"); i++; continue; } else { string name = row["代表常驻城市"].ToString(); Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select new_cityid from new_city where statecode=0 and new_name=@name"; dic.Add("@name", name); DataTable table = pb.Query(sql, dic); if (table != null && table.Rows.Count > 0) { representativeCityid= table.Rows[0]["new_cityid"].ToString(); } else { Logger.InfoMsg("第" + (i + 2).ToString() + "行的代表常驻城市在系统中不存在。"); i++; continue; } } #endregion #region 创建数据 new_hosiptalmsysuser rule = new new_hosiptalmsysuser(); rule.new_year = Convert.ToInt32(row["年份"]); rule.new_DistributionRatio = Convert.ToDouble(row["分配比例"]); string month = GetValueByPickListName("new_hosiptalmsysuser", "new_month", row["月份"].ToString()); if (!string.IsNullOrWhiteSpace(month)) { rule.new_month = new Microsoft.Xrm.Sdk.OptionSetValue(Convert.ToInt32(month)); } if (!string.IsNullOrWhiteSpace(productTypeId)) { rule.new_producttypeid = new Microsoft.Xrm.Sdk.EntityReference("account", new Guid(productTypeId)); } if (!string.IsNullOrWhiteSpace(businessunitId)) { rule.new_qyid = new Microsoft.Xrm.Sdk.EntityReference("businessunit", new Guid(businessunitId)); } if (!string.IsNullOrWhiteSpace(areabusinessId)) { rule.new_dqid = new Microsoft.Xrm.Sdk.EntityReference("businessunit", new Guid(areabusinessId)); } if (!string.IsNullOrWhiteSpace(provinceId)) { rule.new_sqid = new Microsoft.Xrm.Sdk.EntityReference("businessunit", new Guid(provinceId)); } if (!string.IsNullOrWhiteSpace(provinceUnitId)) { rule.new_provinces = new Microsoft.Xrm.Sdk.EntityReference("businessunit", new Guid(provinceUnitId)); } if (!string.IsNullOrWhiteSpace(cityId)) { rule.new_city = new Microsoft.Xrm.Sdk.EntityReference("new_city", new Guid(cityId)); } if (!string.IsNullOrWhiteSpace(hospitalId)) { rule.new_joinaccountid = new Microsoft.Xrm.Sdk.EntityReference("account", new Guid(hospitalId)); } if (!string.IsNullOrWhiteSpace(systemuserid)) { rule.new_systemuser = new Microsoft.Xrm.Sdk.EntityReference("systemuser", new Guid(systemuserid)); } if (!string.IsNullOrWhiteSpace(directorid)) { rule.new_zgid = new Microsoft.Xrm.Sdk.EntityReference("systemuser", new Guid(directorid)); } if (!string.IsNullOrWhiteSpace(provincesManagerid)) { rule.new_sqjlid = new Microsoft.Xrm.Sdk.EntityReference("systemuser", new Guid(provincesManagerid)); } if (!string.IsNullOrWhiteSpace(districtManagerid)) { rule.new_dqjlid = new Microsoft.Xrm.Sdk.EntityReference("systemuser", new Guid(districtManagerid)); } if (!string.IsNullOrWhiteSpace(regionalDirectorid)) { rule.new_qyzjid = new Microsoft.Xrm.Sdk.EntityReference("systemuser", new Guid(regionalDirectorid)); } if (!string.IsNullOrWhiteSpace(representativeCityid)) { rule.new_representative_city = new Microsoft.Xrm.Sdk.EntityReference("new_city", new Guid(representativeCityid)); } rule.new_approvalstatus = new OptionSetValue(1); pb.Create(rule); #endregion successCount++; i++; } Logger.InfoMsg("导入完成：共" + i.ToString() + "条数据，成功创建" + successCount.ToString() + "条数据，失败" + (i - successCount).ToString() + "条数据！"); #endregion } catch (Exception ex) { } finally { pb.CloseDb(); } } catch (Exception ex) { Logger.LogException(ex); } } /// <summary> /// 检查EXCEL中是否包含必须列 /// </summary> /// <param name="dtable"></param> /// <param name="columns"></param> /// <returns></returns> private bool CheckContainsColumns(DataTable dtable, string[] columns) { if (columns != null && columns.Length > 0) { foreach (string column in columns) { if (!dtable.Columns.Contains(column)) { throw new Exception("Excel文件中没有“" + column + "”列"); } } } return true; } /// <summary> /// 通过picklist的标签名，查询对应的value /// </summary> /// <param name="name"></param> /// <returns></returns> private string GetValueByPickListName(string entityName, string fieldName, string fieldNameLabel) { string value = string.Empty; if (!string.IsNullOrWhiteSpace(entityName) && !string.IsNullOrWhiteSpace(fieldName) && !string.IsNullOrWhiteSpace(fieldNameLabel)) { Dictionary<string, object> dic = new Dictionary<string, object>(); string sql = @" select distinct attributevalue as value from entity e inner join stringmap sm on e.objecttypecode = sm.objecttypecode and sm.attributename = @fieldName and value = @fieldNameLabel where logicalname = @entityName"; dic.Add("@fieldName", fieldName); dic.Add("@fieldNameLabel", fieldNameLabel); dic.Add("@entityName", entityName); DataTable table = Broker.Query(sql, dic); if (table != null && table.Rows.Count > 0) { value = table.Rows[0]["value"].ToString(); } } return value; } } }
添加导入按钮 ：
这种导入方式不需要在DataMapping.xml中进行配置