public void PrepareFormSections(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
OnLog(new LogEventArgs($"Reading {sheet.Name}"));
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var sectionId = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString();
var formId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 4).Value.ToString());
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
try
{
form = service.Retrieve("systemform", formId, new ColumnSet("formxml"));
forms.Add(form);
}
catch (Exception error) //lets not fail if the form is no more available in CRM
{
OnLog(new LogEventArgs
{
Type = LogType.Warning,
Message = $"Cannot find form {formId}: {error.Message}"
});
continue; //form is not found so no need to process further.
}
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var sectionNode =
docXml.DocumentElement.SelectSingleNode(
string.Format("tabs/tab/columns/column/sections/section[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", sectionId.ToLower()));
if (sectionNode != null)
{
var columnIndex = 6;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString();
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
UpdateXmlNode(sectionNode, lcid, label);
}
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
public void PrepareFormTabs(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
{
var tabId = row.Cells[0].Value.ToString();
var formId = new Guid(row.Cells[4].Value.ToString());
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
forms.Add(form);
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var tabNode = docXml.DocumentElement.SelectSingleNode(string.Format("tabs/tab[@id='{0}']", tabId));
if (tabNode != null)
{
var columnIndex = 5;
while (row.Cells[columnIndex].Value != null)
{
UpdateXmlNode(tabNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
row.Cells[columnIndex].Value.ToString());
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
public void Import(ExcelWorksheet sheet, IOrganizationService service)
{
var views = new List <Tuple <int, Entity> >();
foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
{
var currentViewId = new Guid(row.Cells[0].Value.ToString());
var request = new SetLocLabelsRequest
{
EntityMoniker = new EntityReference("savedquery", currentViewId),
AttributeName = row.Cells[3].Value.ToString() == "Name" ? "name" : "description"
};
var labels = new List <LocalizedLabel>();
var columnIndex = 4;
while (row.Cells[columnIndex].Value != null)
{
var currentLcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
labels.Add(new LocalizedLabel(row.Cells[columnIndex].Value.ToString(), currentLcid));
columnIndex++;
}
request.Labels = labels.ToArray();
service.Execute(request);
}
}
public void Import(ExcelWorksheet sheet, IOrganizationService service)
{
var rowsCount = sheet.Dimension.Rows;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var xml = new StringBuilder(string.Format("<LocLabel Id=\"{0}\"><Titles>", ZeroBasedSheet.Cell(sheet, rowI, 2).Value));
var columnIndex = 3;
while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
xml.Append(string.Format("<Title description=\"{0}\" languagecode=\"{1}\"/>",
ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value,
int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString())));
columnIndex++;
}
xml.Append("</Titles></LocLabel>");
var ribbonDiff = new Entity("ribbondiff")
{
Id = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString())
};
ribbonDiff["rdx"] = xml.ToString();
service.Update(ribbonDiff);
}
}
private static int ExportField(List <int> languages, ExcelWorksheet labelSheet, int line,
CrmFormLabel crmFormLabel)
{
var cell = 0;
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Id.ToString("B");
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Entity;
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Form;
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.FormUniqueId.ToString("B");
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.FormId.ToString("B");
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Tab;
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Section;
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = crmFormLabel.Attribute;
foreach (var lcid in languages)
{
bool exists = crmFormLabel.Names.ContainsKey(lcid);
ZeroBasedSheet.Cell(labelSheet, line, cell++).Value = exists
? crmFormLabel.Names.First(n => n.Key == lcid).Value
: string.Empty;
}
line++;
return(line);
}
public void Import(ExcelWorksheet sheet, IOrganizationService service)
{
foreach (ExcelRow row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
{
var xml = new StringBuilder(string.Format("<LocLabel Id=\"{0}\"><Titles>", row.Cells[2].Value));
var columnIndex = 3;
while (row.Cells[columnIndex].Value != null)
{
xml.Append(string.Format("<Title description=\"{0}\" languagecode=\"{1}\"/>",
row.Cells[columnIndex].Value,
int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString())));
columnIndex++;
}
xml.Append("</Titles></LocLabel>");
var ribbonDiff = new Entity("ribbondiff")
{
Id = new Guid(row.Cells[0].Value.ToString())
};
ribbonDiff["rdx"] = xml.ToString();
service.Update(ribbonDiff);
}
}
public void ImportFormName(ExcelWorksheet sheet, IOrganizationService service)
{
var rowsCount = sheet.Dimension.Rows;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var currentFormId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString());
var request = new SetLocLabelsRequest
{
EntityMoniker = new EntityReference("systemform", currentFormId),
AttributeName = ZeroBasedSheet.Cell(sheet, rowI, 2).Value.ToString() == "Name" ? "name" : "description"
};
var labels = new List <LocalizedLabel>();
var columnIndex = 3;
while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var currentLcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
labels.Add(new LocalizedLabel(ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString(), currentLcid));
columnIndex++;
}
request.Labels = labels.ToArray();
service.Execute(request);
}
}
public void PrepareAreas(ExcelWorksheet sheet, IOrganizationService service)
{
GetSiteMaps(service);
foreach (var siteMap in siteMaps.Entities)
{
var siteMapDoc = new XmlDocument();
siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
if (ZeroBasedSheet.Cell(sheet, rowI, 0).Value == null)
{
break;
}
if (ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString() != siteMap.Id.ToString())
{
continue;
}
if (!siteMapsToTranslate.Contains(siteMap.Id))
{
siteMapsToTranslate.Add(siteMap.Id);
}
var areaId = ZeroBasedSheet.Cell(sheet, rowI, 2).Value.ToString();
var areaNode = siteMapDoc.SelectSingleNode("SiteMap/Area[@Id='" + areaId + "']");
if (areaNode == null)
{
throw new Exception("Unable to find area with id " + areaId);
}
var columnIndex = 4;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString();
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
if (ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Title")
{
UpdateXmlNode(areaNode, "Titles", "Title", lcid, label);
}
else
{
UpdateXmlNode(areaNode, "Descriptions", "Description", lcid, label);
}
}
columnIndex++;
}
}
siteMap["sitemapxml"] = siteMapDoc.OuterXml;
}
}
public void Export(List <int> languages, ExcelWorksheet sheet, bool onlyUnmanaged, IOrganizationService service)
{
var qe = new QueryByAttribute("ribbondiff")
{
ColumnSet = new ColumnSet(true)
};
if (onlyUnmanaged)
{
qe.Attributes.AddRange("difftype", "ismanaged");
qe.Values.AddRange(3, false);
}
else
{
qe.Attributes.AddRange("difftype");
qe.Values.AddRange(3);
}
qe.AddOrder("entity", OrderType.Ascending);
var records = service.RetrieveMultiple(qe);
var line = 1;
AddHeader(sheet, languages);
foreach (var record in records.Entities)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, line, cell++).Value = record.Id.ToString("B");
ZeroBasedSheet.Cell(sheet, line, cell++).Value = record.GetAttributeValue <string>("entity");
ZeroBasedSheet.Cell(sheet, line, cell++).Value = record.GetAttributeValue <string>("diffid");
var xml = new XmlDocument();
xml.LoadXml(record.GetAttributeValue <string>("rdx"));
foreach (var lcid in languages)
{
var labelNode = xml.SelectSingleNode(string.Format("LocLabel/Titles/Title[@languagecode='{0}']", lcid));
ZeroBasedSheet.Cell(sheet, line, cell++).Value = labelNode == null ? string.Empty : labelNode.Attributes["description"].Value;
}
line++;
}
// Applying style to cells
for (int i = 0; i < (3 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(sheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 3; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(sheet, i, j).Style);
}
}
}
internal void Export(List <EntityMetadata> entities, List <int> languages, ExcelWorksheet sheet)
{
var line = 1;
AddHeader(sheet, languages);
foreach (var entity in entities.OrderBy(e => e.LogicalName))
{
foreach (var rel in entity.ManyToManyRelationships.ToList())
{
var cell = 0;
var amc = rel.Entity1LogicalName == entity.LogicalName ? rel.Entity1AssociatedMenuConfiguration : rel.Entity2AssociatedMenuConfiguration;
if (!(amc.Behavior.HasValue && amc.Behavior.Value == AssociatedMenuBehavior.UseLabel))
{
continue;
}
ZeroBasedSheet.Cell(sheet, line, cell++).Value = entity.LogicalName;
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.MetadataId.Value.ToString("B");
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.IntersectEntityName;
foreach (var lcid in languages)
{
var entity1Label = string.Empty;
if (amc.Label != null)
{
var displayNameLabel =
amc.Label.LocalizedLabels.FirstOrDefault(l => l.LanguageCode == lcid);
if (displayNameLabel != null)
{
entity1Label = displayNameLabel.Label;
}
}
ZeroBasedSheet.Cell(sheet, line, cell++).Value = entity1Label;
}
line++;
}
}
// Applying style to cells
for (int i = 0; i < (3 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(sheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 3; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(sheet, i, j).Style);
}
}
}
public void PrepareFormLabels(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
OnLog(new LogEventArgs($"Reading {sheet.Name}"));
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
if (HasEmptyCells(sheet, rowI, 6))
{
continue;
}
var labelId = ZeroBasedSheet.Cell(sheet, rowI, 0).Value?.ToString();
var formId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 3).Value?.ToString() ?? Guid.Empty.ToString());
if (labelId == null || formId == null)
{
continue;
}
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
forms.Add(form);
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var cellNode =
docXml.DocumentElement.SelectSingleNode(
string.Format("tabs/tab/columns/column/sections/section/rows/row/cell[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", labelId.ToLower()));
if (cellNode != null)
{
var columnIndex = 7;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString();
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
UpdateXmlNode(cellNode, lcid, label);
}
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
private static int ExportForm(List <int> languages, ExcelWorksheet formSheet, int line, CrmForm crmForm)
{
int cell;
if (settings.ExportNames)
{
line++;
cell = 0;
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.FormUniqueId.ToString("B");
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.Id.ToString("B");
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.Entity;
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = "Name";
foreach (var lcid in languages)
{
var name = crmForm.Names.FirstOrDefault(n => n.Key == lcid);
if (name.Value != null)
{
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = name.Value;
}
else
{
cell++;
}
}
}
if (settings.ExportDescriptions)
{
line++;
cell = 0;
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.FormUniqueId.ToString("B");
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.Id.ToString("B");
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = crmForm.Entity;
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = "Description";
foreach (var lcid in languages)
{
var desc = crmForm.Descriptions.FirstOrDefault(n => n.Key == lcid);
if (desc.Value != null)
{
ZeroBasedSheet.Cell(formSheet, line, cell++).Value = desc.Value;
}
else
{
cell++;
}
}
}
return(line);
}
public void PrepareSubAreas(ExcelWorksheet sheet, IOrganizationService service)
{
if (siteMap == null)
{
siteMap = GetSiteMap(service);
}
var siteMapDoc = new XmlDocument();
siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
if (ZeroBasedSheet.Cell(sheet, rowI, 0).Value == null)
{
break;
}
var areaId = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString();
var groupId = ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString();
var subAreaId = ZeroBasedSheet.Cell(sheet, rowI, 2).Value.ToString();
var subAreaNode = siteMapDoc.SelectSingleNode("SiteMap/Area[@Id='" + areaId + "']/Group[@Id='" + groupId + "']/SubArea[@Id='" + subAreaId + "']");
if (subAreaNode == null)
{
throw new Exception("Unable to find group with id " + groupId + " in area " + areaId);
}
var columnIndex = 4;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString();
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
if (ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString() == "Title")
{
UpdateXmlNode(subAreaNode, "Titles", "Title", lcid, label);
}
else
{
UpdateXmlNode(subAreaNode, "Descriptions", "Description", lcid, label);
}
}
columnIndex++;
}
}
siteMap["sitemapxml"] = siteMapDoc.OuterXml;
}
private void AddAreaHeader(ExcelWorksheet sheet, IEnumerable <int> languages)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Area Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Type";
foreach (var lcid in languages)
{
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = lcid.ToString(CultureInfo.InvariantCulture);
}
}
protected bool HasEmptyCells(ExcelWorksheet sheet, int row, int lastCellToCheck)
{
for (int i = 0; i < lastCellToCheck; i++)
{
if (ZeroBasedSheet.Cell(sheet, row, i) == null || ZeroBasedSheet.Cell(sheet, row, i).Value == null)
{
return(true);
}
}
return(false);
}
public void ImportFormName(ExcelWorksheet sheet, IOrganizationService service, BackgroundWorker worker)
{
OnLog(new LogEventArgs($"Reading {sheet.Name}"));
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
var requests = new List <SetLocLabelsRequest>();
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var currentFormId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString());
var request = new SetLocLabelsRequest
{
EntityMoniker = new EntityReference("systemform", currentFormId),
AttributeName = ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Name" ? "name" : "description"
};
var labels = new List <LocalizedLabel>();
var columnIndex = 4;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
labels.Add(new LocalizedLabel(label, lcid));
}
columnIndex++;
}
request.Labels = labels.ToArray();
requests.Add(request);
}
OnLog(new LogEventArgs($"Importing {sheet.Name} translations"));
var arg = new TranslationProgressEventArgs {
SheetName = sheet.Name
};
foreach (var request in requests)
{
AddRequest(request);
ExecuteMultiple(service, arg, requests.Count);
}
ExecuteMultiple(service, arg, requests.Count, true);
}
private void AddHeader(ExcelWorksheet sheet, IEnumerable <int> languages)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Ribbon Diff Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Entity Logical Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Ribbon Component";
foreach (var lcid in languages)
{
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = lcid.ToString(CultureInfo.InvariantCulture);
}
}
private void AddHeader(ExcelWorksheet sheet, IEnumerable <int> languages)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Entity";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Relationship Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Relationship Intersect Entity";
foreach (var lcid in languages)
{
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = lcid.ToString(CultureInfo.InvariantCulture);
}
}
public void PrepareSubAreas(ExcelWorksheet sheet, IOrganizationService service)
{
if (siteMap == null)
{
siteMap = GetSiteMap(service);
}
var siteMapDoc = new XmlDocument();
siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());
foreach (var row in sheet.Rows)
{
if (row.Index == 0)
{
continue;
}
if (row.Cells[0].Value == null)
{
break;
}
var areaId = row.Cells[0].Value.ToString();
var groupId = row.Cells[1].Value.ToString();
var subAreaId = row.Cells[2].Value.ToString();
var subAreaNode = siteMapDoc.SelectSingleNode("SiteMap/Area[@Id='" + areaId + "']/Group[@Id='" + groupId + "']/SubArea[@Id='" + subAreaId + "']");
if (subAreaNode == null)
{
throw new Exception("Unable to find group with id " + groupId + " in area " + areaId);
}
var columnIndex = 4;
while (row.Cells[columnIndex].Value != null)
{
if (row.Cells[1].Value.ToString() == "Title")
{
UpdateXmlNode(subAreaNode, "Titles", "Title", ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
row.Cells[columnIndex].Value.ToString());
}
else
{
UpdateXmlNode(subAreaNode, "Descriptions", "Description", ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
row.Cells[columnIndex].Value.ToString());
}
columnIndex++;
}
}
siteMap["sitemapxml"] = siteMapDoc.OuterXml;
}
private void AddFormTabHeader(ExcelWorksheet sheet, IEnumerable <int> languages)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Tab Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Unique Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Id";
foreach (var lcid in languages)
{
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = lcid.ToString(CultureInfo.InvariantCulture);
}
}
private void AddFormLabelsHeader(ExcelWorksheet sheet, IEnumerable <int> languages)
{
var cell = 0;
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Label Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Entity Logical Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Unique Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Form Id";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Tab Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Section Name";
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = "Attribute";
foreach (var lcid in languages)
{
ZeroBasedSheet.Cell(sheet, 0, cell++).Value = lcid.ToString(CultureInfo.InvariantCulture);
}
}
private int ExportTab(List <int> languages, ExcelWorksheet tabSheet, int line, CrmFormTab crmFormTab)
{
var cell = 0;
ZeroBasedSheet.Cell(tabSheet, line, cell++).Value = crmFormTab.Id.ToString("B");
ZeroBasedSheet.Cell(tabSheet, line, cell++).Value = crmFormTab.Form;
ZeroBasedSheet.Cell(tabSheet, line, cell++).Value = crmFormTab.FormUniqueId.ToString("B");
ZeroBasedSheet.Cell(tabSheet, line, cell++).Value = crmFormTab.FormId.ToString("B");
foreach (var lcid in languages)
{
bool exists = crmFormTab.Names.ContainsKey(lcid);
ZeroBasedSheet.Cell(tabSheet, line, cell++).Value = exists
? crmFormTab.Names.First(n => n.Key == lcid).Value
: string.Empty;
}
line++;
return(line);
}
private static int ExportSection(List <int> languages, ExcelWorksheet sectionSheet, int line,
CrmFormSection crmFormSection)
{
var cell = 0;
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Id.ToString("B");
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Form;
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.FormUniqueId.ToString("B");
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.FormId.ToString("B");
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = crmFormSection.Tab;
foreach (var lcid in languages)
{
bool exists = crmFormSection.Names.ContainsKey(lcid);
ZeroBasedSheet.Cell(sectionSheet, line, cell++).Value = exists
? crmFormSection.Names.First(n => n.Key == lcid).Value
: string.Empty;
}
line++;
return(line);
}
public void PrepareFormSections(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
var rowsCount = sheet.Dimension.Rows;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var sectionId = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString();
var formId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString());
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
forms.Add(form);
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var sectionNode =
docXml.DocumentElement.SelectSingleNode(
string.Format("tabs/tab/columns/column/sections/section[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", sectionId.ToLower()));
if (sectionNode != null)
{
var columnIndex = 5;
while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
UpdateXmlNode(sectionNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString());
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
public void PrepareFormLabels(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
var rowsCount = sheet.Dimension.Rows;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var labelId = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString();
var formId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 4).Value.ToString());
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
forms.Add(form);
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var cellNode =
docXml.DocumentElement.SelectSingleNode(
string.Format("tabs/tab/columns/column/sections/section/rows/row/cell[@id='{0}']", labelId));
if (cellNode != null)
{
var columnIndex = 8;
while (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
UpdateXmlNode(cellNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString());
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
public void Import(ExcelWorksheet sheet, IOrganizationService service)
{
var views = new List <Tuple <int, Entity> >();
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var currentViewId = new Guid(ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString());
var request = new SetLocLabelsRequest
{
EntityMoniker = new EntityReference("savedquery", currentViewId),
AttributeName = ZeroBasedSheet.Cell(sheet, rowI, 3).Value.ToString() == "Name" ? "name" : "description"
};
var labels = new List <LocalizedLabel>();
var columnIndex = 4;
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
labels.Add(new LocalizedLabel(label, lcid));
}
columnIndex++;
}
request.Labels = labels.ToArray();
service.Execute(request);
}
}
public void PrepareFormLabels(ExcelWorksheet sheet, IOrganizationService service, List <Entity> forms)
{
foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
{
var labelId = row.Cells[0].Value.ToString();
var formId = new Guid(row.Cells[4].Value.ToString());
var form = forms.FirstOrDefault(f => f.Id == formId);
if (form == null)
{
form = service.Retrieve("systemform", formId, new ColumnSet(new[] { "formxml" }));
forms.Add(form);
}
// Load formxml
var formXml = form.GetAttributeValue <string>("formxml");
var docXml = new XmlDocument();
docXml.LoadXml(formXml);
var cellNode =
docXml.DocumentElement.SelectSingleNode(
string.Format("tabs/tab/columns/column/sections/section/rows/row/cell[translate(@id,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='{0}']", labelId.ToLower()));
if (cellNode != null)
{
var columnIndex = 8;
while (row.Cells[columnIndex].Value != null)
{
UpdateXmlNode(cellNode, ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString(),
row.Cells[columnIndex].Value.ToString());
columnIndex++;
}
}
form["formxml"] = docXml.OuterXml;
}
}
public void Import(ExcelWorksheet sheet, List <EntityMetadata> emds, IOrganizationService service, BackgroundWorker worker)
{
var rmds = new List <OneToManyRelationshipMetadata>();
var rowsCount = sheet.Dimension.Rows;
var cellsCount = sheet.Dimension.Columns;
for (var rowI = 1; rowI < rowsCount; rowI++)
{
var rmd = rmds.FirstOrDefault(r => ZeroBasedSheet.Cell(sheet, rowI, 1).Value != null && r.MetadataId == new Guid(ZeroBasedSheet.Cell(sheet, rowI, 1).Value.ToString()));
if (rmd == null)
{
var currentEntity = emds.FirstOrDefault(e => e.LogicalName == ZeroBasedSheet.Cell(sheet, rowI, 0).Value?.ToString());
if (currentEntity == null)
{
var request = new RetrieveEntityRequest
{
LogicalName = ZeroBasedSheet.Cell(sheet, rowI, 0).Value.ToString(),
EntityFilters = EntityFilters.Entity | EntityFilters.Attributes | EntityFilters.Relationships
};
var response = (RetrieveEntityResponse)service.Execute(request);
currentEntity = response.EntityMetadata;
emds.Add(currentEntity);
}
rmd =
currentEntity.OneToManyRelationships.FirstOrDefault(
r => r.SchemaName == ZeroBasedSheet.Cell(sheet, rowI, 2).Value?.ToString());
if (rmd == null)
{
rmd =
currentEntity.ManyToOneRelationships.FirstOrDefault(
r => r.SchemaName == ZeroBasedSheet.Cell(sheet, rowI, 2).Value?.ToString());
}
rmds.Add(rmd);
}
int columnIndex = 4;
rmd.AssociatedMenuConfiguration.Label = new Label();
while (columnIndex < cellsCount)
{
if (ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value != null)
{
var lcid = int.Parse(ZeroBasedSheet.Cell(sheet, 0, columnIndex).Value.ToString());
var label = ZeroBasedSheet.Cell(sheet, rowI, columnIndex).Value.ToString();
rmd.AssociatedMenuConfiguration.Label.LocalizedLabels.Add(new LocalizedLabel(label, lcid));
}
columnIndex++;
}
}
int i = 0;
foreach (var rmd in rmds)
{
var request = new UpdateRelationshipRequest
{
Relationship = rmd,
};
try
{
service.Execute(request);
OnResult(new TranslationResultEventArgs
{
Success = true,
SheetName = sheet.Name
});
}
catch (Exception error)
{
OnResult(new TranslationResultEventArgs
{
Success = false,
SheetName = sheet.Name,
Message = $"{request.Relationship.SchemaName}: {error.Message}"
});
}
i++;
worker.ReportProgressIfPossible(0, new ProgressInfo
{
Item = i * 100 / rmds.Count
});
}
}
internal void Export(List <EntityMetadata> entities, List <int> languages, ExcelWorksheet sheet)
{
var line = 1;
AddHeader(sheet, languages);
var exportedRelationships = new List <Guid>();
foreach (var entity in entities.OrderBy(e => e.LogicalName))
{
var relationships = new List <OneToManyRelationshipMetadata>();
relationships.AddRange(entity.OneToManyRelationships);
relationships.AddRange(entity.ManyToOneRelationships);
foreach (var rel in relationships)
{
if (exportedRelationships.Contains(rel.MetadataId.Value))
{
continue;
}
exportedRelationships.Add(rel.MetadataId.Value);
var cell = 0;
if (!rel.AssociatedMenuConfiguration.Behavior.HasValue ||
rel.AssociatedMenuConfiguration.Behavior.Value != AssociatedMenuBehavior.UseLabel)
{
continue;
}
// entity1Label
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.ReferencedEntity;
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.MetadataId.Value.ToString("B");
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.SchemaName;
ZeroBasedSheet.Cell(sheet, line, cell++).Value = rel.ReferencingEntity;
foreach (var lcid in languages)
{
var entity1Label = string.Empty;
if (rel.AssociatedMenuConfiguration.Label != null)
{
var displayNameLabel =
rel.AssociatedMenuConfiguration.Label.LocalizedLabels.FirstOrDefault(
l => l.LanguageCode == lcid);
if (displayNameLabel != null)
{
entity1Label = displayNameLabel.Label;
}
}
ZeroBasedSheet.Cell(sheet, line, cell++).Value = entity1Label;
}
line++;
}
}
// Applying style to cells
for (int i = 0; i < (4 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(sheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 4; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(sheet, i, j).Style);
}
}
}
public void Export(List <int> languages, ExcelWorkbook file, IOrganizationService service)
{
// Retrieve current user language information
var setting = GetCurrentUserSettings(service);
var userSettingLcid = setting.GetAttributeValue <int>("uilanguageid");
var currentSetting = userSettingLcid;
var crmForms = new List <CrmForm>();
var crmFormTabs = new List <CrmFormTab>();
var crmFormSections = new List <CrmFormSection>();
var crmFormLabels = new List <CrmFormLabel>();
foreach (var lcid in languages)
{
if (userSettingLcid != lcid)
{
setting["localeid"] = lcid;
setting["uilanguageid"] = lcid;
setting["helplanguageid"] = lcid;
service.Update(setting);
currentSetting = lcid;
}
var forms = RetrieveDashboardList(service);
foreach (var form in forms)
{
#region Tabs
// Load Xml definition of form
var sFormXml = form.GetAttributeValue <string>("formxml");
var formXml = new XmlDocument();
formXml.LoadXml(sFormXml);
foreach (XmlNode tabNode in formXml.SelectNodes("//tab"))
{
var tabName = ExtractTabName(tabNode, lcid, crmFormTabs, form);
#region Sections
foreach (
XmlNode sectionNode in tabNode.SelectNodes("columns/column/sections/section"))
{
var sectionName = ExtractSection(sectionNode, lcid, crmFormSections, form, tabName);
#region Labels
foreach (XmlNode labelNode in sectionNode.SelectNodes("rows/row/cell"))
{
ExtractField(labelNode, crmFormLabels, form, tabName, sectionName,
lcid);
}
#endregion Labels
}
#endregion Sections
}
#endregion Tabs
}
}
if (userSettingLcid != currentSetting)
{
setting["localeid"] = userSettingLcid;
setting["uilanguageid"] = userSettingLcid;
setting["helplanguageid"] = userSettingLcid;
service.Update(setting);
}
var forms2 = RetrieveDashboardList(service);
foreach (var form in forms2)
{
var crmForm =
crmForms.FirstOrDefault(f => f.FormUniqueId == form.GetAttributeValue <Guid>("formidunique"));
if (crmForm == null)
{
crmForm = new CrmForm
{
FormUniqueId = form.GetAttributeValue <Guid>("formidunique"),
Id = form.GetAttributeValue <Guid>("formid"),
Names = new Dictionary <int, string>(),
Descriptions = new Dictionary <int, string>()
};
crmForms.Add(crmForm);
}
// Names
var request = new RetrieveLocLabelsRequest
{
AttributeName = "name",
EntityMoniker = new EntityReference("systemform", form.Id)
};
var response = (RetrieveLocLabelsResponse)service.Execute(request);
foreach (var locLabel in response.Label.LocalizedLabels)
{
crmForm.Names.Add(locLabel.LanguageCode, locLabel.Label);
}
// Descriptions
request = new RetrieveLocLabelsRequest
{
AttributeName = "description",
EntityMoniker = new EntityReference("systemform", form.Id)
};
response = (RetrieveLocLabelsResponse)service.Execute(request);
foreach (var locLabel in response.Label.LocalizedLabels)
{
crmForm.Descriptions.Add(locLabel.LanguageCode, locLabel.Label);
}
}
var line = 1;
var formSheet = file.Worksheets.Add("Dashboards");
AddFormHeader(formSheet, languages);
foreach (var crmForm in crmForms)
{
line = ExportForm(languages, formSheet, line, crmForm);
}
// Applying style to cells
for (int i = 0; i < (3 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(formSheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 3; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(formSheet, i, j).Style);
}
}
var tabSheet = file.Worksheets.Add("Dashboards Tabs");
line = 1;
AddFormTabHeader(tabSheet, languages);
foreach (var crmFormTab in crmFormTabs)
{
line = ExportTab(languages, tabSheet, line, crmFormTab);
}
// Applying style to cells
for (int i = 0; i < (4 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(tabSheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 4; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(tabSheet, i, j).Style);
}
}
var sectionSheet = file.Worksheets.Add("Dashboards Sections");
line = 1;
AddFormSectionHeader(sectionSheet, languages);
foreach (var crmFormSection in crmFormSections)
{
line = ExportSection(languages, sectionSheet, line, crmFormSection);
}
// Applying style to cells
for (int i = 0; i < (5 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(sectionSheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 5; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(sectionSheet, i, j).Style);
}
}
var labelSheet = file.Worksheets.Add("Dashboards Fields");
AddFormLabelsHeader(labelSheet, languages);
line = 1;
foreach (var crmFormLabel in crmFormLabels)
{
line = ExportField(languages, labelSheet, line, crmFormLabel);
}
// Applying style to cells
for (int i = 0; i < (7 + languages.Count); i++)
{
StyleMutator.TitleCell(ZeroBasedSheet.Cell(labelSheet, 0, i).Style);
}
for (int i = 1; i < line; i++)
{
for (int j = 0; j < 7; j++)
{
StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(labelSheet, i, j).Style);
}
}
}
