Dynamic CRM 2013学习笔记（三十一）自定义用excel批量导入实体数据

有一个实体的子表数据量太大，于是客户想用execel来导入实体数据。首先想到的是用系统自带的Import Data，客户嫌太麻烦，比如lookup字段要做map等。

下面是具体的实现步骤：

一、定义excel数据模板

1. 利用系统自带的Download Template For Import下载系统自带的模板

2. 去掉不需要的列，比如有些列是自动计算，自动赋值

3. 保存为excel文件，并copy到crm server里的isv目录下

4. 定义一个按钮，并指定调用下面的js：

// export purchase detail template
function downloadDetailTemplate() {
    var url = "/ISV/TempDownLoad/OrderDetail.xls";
    var hiddenIFrameID = 'hiddenDownloader',
            iframe = document.getElementById(hiddenIFrameID);
    if (iframe === null) {
        iframe = document.createElement('iframe');
        iframe.id = hiddenIFrameID;
        iframe.style.display = 'none';
        document.body.appendChild(iframe);
    }
    iframe.src = url;
}

二、导入数据

1. 在上面定义的excel模板里填充数据

2. 定义一个aspx页面：

<div style="margin-top: 20px; margin-left: 10px">
       <asp:FileUpload ID="FileUpload1" runat="server" Height="20px" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
       <asp:Button ID="btSubmit" runat="server" Text="上传" OnClientClick="javascript:return extension();"
           OnClick="btSubmit_Click" Height="20px" />
       <br />
       <br />
       <asp:Label ID="lbMessage" runat="server" Text=""></asp:Label>
   </div>

很简单，一个上传控件，一个上传按钮

3. 为这个实体建一个类，跟excel里的列一一对应

public class PurchaseOrderDetails
    {
        //public string OrderNo { get; set; }

        public string PartNo { get; set; }
        public decimal Quantity { get; set; }
        public decimal UnitPrice { get; set; }
        public string Remarks { get; set; }
        public string CorrespondingSONo { get; set; }
        public decimal SWAPRate { get; set; }
        public DateTime RequestedDeliveryDate { get; set; }

        public string Model { get; set; }
        public string Description { get; set; }
        public decimal SOQuantity { get; set; }
    }

4. 读取excel里的数据，并转换成上面定义的类

System.IO.FileInfo fileinfo = new System.IO.FileInfo(FileUpload1.PostedFile.FileName);
int fileLen = FileUpload1.PostedFile.ContentLength;
Byte[] FileData = new Byte[fileLen];

HttpPostedFile hps = FileUpload1.PostedFile;
System.IO.Stream stream = hps.InputStream;
stream.Read(FileData, 0, fileLen);
ExcelDataReader.ExcelDataReader spreadsheet = new ExcelDataReader.ExcelDataReader(stream);
if (spreadsheet.WorkbookData.Tables.Count == 0)
{
    throw new Exception("File loading error！");
}

DataTable dt = spreadsheet.WorkbookData.Tables[0];
int filecount = dt.Columns.Count;
PurchaseOrderDetails detail = new PurchaseOrderDetails();
Type type = detail.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
if (propertyInfos.Length != filecount)
{
    throw new Exception("File Template is not correct！");
}

List<PurchaseOrderDetails> detailList = new List<PurchaseOrderDetails>();
for (int index = 1; index < dt.Rows.Count - 1; index++)
{
    detail = new PurchaseOrderDetails();
    int count = 0;
    int nullcount = 0;//判断是否空数据

    foreach (PropertyInfo property in propertyInfos)
    {
        string csvvalue = dt.Rows[index][count].ToString();
        if (csvvalue != null && csvvalue != "")
        {
            csvvalue = ToDBC(csvvalue);
            object convertValue = null;
            try
            {
                convertValue = Convert.ChangeType(csvvalue, property.PropertyType);
            }
            catch (Exception ex)
            {
                property.SetValue(detail, convertValue, null);
                count++;
                continue;
            }
            property.SetValue(detail, convertValue, null);
        }
        else
        {
            property.SetValue(detail, null, null);


            nullcount++;
        }
        count++;
    }
    if (nullcount == propertyInfos.Length)
    {
        continue;
    }

    detailList.Add(detail);
}


spreadsheet.WorkbookData.Dispose();

stream.Close();

5. 验证，转换成真实的实体

Entity orderDetail = new Entity("new_purchase_details");

// 0. order no
orderDetail["new_purchaseid"] = new EntityReference("new_purchase", Guid.Parse( entityId));

// 1. part no
var ents = getData("new_product", "new_name", item.PartNo.Trim());
Guid productid;
if (ents.Entities.Count == 0)
{
throw new Exception(string.Format("Part No (row {0}) is wrong !", i + 1));
}
else
{
productid = ents.Entities[0].Id;
orderDetail["new_productid"] = new EntityReference("new_product", productid);
}

// 2. model
ents = getData("new_model", "new_name", item.Model.Trim());
if (ents.Entities.Count == 0)
{
throw new Exception(string.Format("Model (row {0}) is wrong !", i + 1));
}
else
{
orderDetail["new_model"] = new EntityReference("new_model", ents.Entities[0].Id);
}

// 3. Quantity
orderDetail["new_request_quantity"] = item.Quantity;

// 4. Description
orderDetail["new_description"] = item.Description;

// 5. Unit Price
orderDetail["new_unit_price"] = item.UnitPrice;

// 6. Amount
orderDetail["new_amount"] = item.Quantity * item.UnitPrice;

using (OrganizationServiceContext orgContext = new OrganizationServiceContext(m_CrmService))
{
    foreach (Entity item in entityList)
    {
        orgContext.AddObject(item);
    }
    orgContext.SaveChanges();
}

6. 发布这个页面，并在实体上加上按钮，调用下面的js

var openURL = "http://12.2.3/ImportOrderDetail.aspx?entityId=";
window.open(openURL + Xrm.Page.data.entity.getId().toString(), "_blank", "dialogWidth=800px;dialogHeight=400px;help:no;status:no");

这里要把主实体的id带过去， 因为插入子实体时要用到，否保存时会报下面的错误：

Exception has been thrown by the target of an invocation

Dynamic CRM 2013学习笔记 系列汇总 -- 持续更新中