PowerApps简单的Excel导入
添加导入按钮 ：
<%@ 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; ">
<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中进行配置