有一个实体的子表数据量太大，于是客户想用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" />
<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