Пример #1
0
        /// <summary>
        ///
        /// </summary>
        /// <example>
        /// viewId;entityLogicalName;viewName;ViewType;Type;LCID1;LCID2;...;LCODX
        /// </example>
        /// <param name="languages"></param>
        /// <param name="file"></param>
        /// <param name="service"></param>
        public void Export(List<int> languages, ExcelWorkbook file, IOrganizationService service)
        {
            var line = 1;

            var siteMap = GetSiteMap(service);
            var siteMapDoc = new XmlDocument();
            siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());

            var crmSiteMapAreas = new List<CrmSiteMapArea>();
            var crmSiteMapGroups = new List<CrmSiteMapGroup>();
            var crmSiteMapSubAreas = new List<CrmSiteMapSubArea>();

            #region Export Area

            var areaNodes = siteMapDoc.SelectNodes("SiteMap/Area");
            foreach (XmlNode areaNode in areaNodes)
            {
                var area = new CrmSiteMapArea { Id = areaNode.Attributes["Id"].Value };
                foreach (XmlNode titleNode in areaNode.SelectNodes("Titles/Title"))
                {
                    area.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                }
                foreach (XmlNode titleNode in areaNode.SelectNodes("Descriptions/Description"))
                {
                    area.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                }

                crmSiteMapAreas.Add(area);

                #region Export Groups

                var groupNodes = areaNode.SelectNodes("Group");
                foreach (XmlNode groupNode in groupNodes)
                {
                    var group = new CrmSiteMapGroup
                    {
                        Id = groupNode.Attributes["Id"].Value,
                        AreaId = areaNode.Attributes["Id"].Value
                    };
                    foreach (XmlNode titleNode in groupNode.SelectNodes("Titles/Title"))
                    {
                        group.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                    }
                    foreach (XmlNode titleNode in groupNode.SelectNodes("Descriptions/Description"))
                    {
                        group.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                    }

                    crmSiteMapGroups.Add(group);

                    #region Export SubArea

                    var subAreaNodes = groupNode.SelectNodes("SubArea");
                    foreach (XmlNode subAreaNode in subAreaNodes)
                    {
                        var subArea = new CrmSiteMapSubArea()
                        {
                            Id = subAreaNode.Attributes["Id"].Value,
                            GroupId = groupNode.Attributes["Id"].Value,
                            AreaId = areaNode.Attributes["Id"].Value
                        };
                        foreach (XmlNode titleNode in subAreaNode.SelectNodes("Titles/Title"))
                        {
                            subArea.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                        }
                        foreach (XmlNode titleNode in groupNode.SelectNodes("Descriptions/Description"))
                        {
                            subArea.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                        }

                        crmSiteMapSubAreas.Add(subArea);
                    }

                    #endregion Export SubArea
                }

                #endregion Export Groups
            }

            #endregion Export Area

            #region Area sheet

            var areaSheet = file.Worksheets.Add("SiteMap Areas");
            AddAreaHeader(areaSheet, languages);
            foreach (var crmArea in crmSiteMapAreas)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion Area sheet

            #region Group sheet

            line = 1;
            var groupSheet = file.Worksheets.Add("SiteMap Groups");
            AddGroupHeader(groupSheet, languages);
            foreach (var crmGroup in crmSiteMapGroups)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion Group sheet

            #region SubArea sheet

            line = 1;
            var subAreaSheet = file.Worksheets.Add("SiteMap SubAreas");
            AddSubAreaHeader(subAreaSheet, languages);
            foreach (var crmSubArea in crmSiteMapSubAreas)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion SubArea sheet

            // Applying style to cells
            for (int i = 0; i < (2 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(areaSheet, 0, i).Style);
            }
            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 2; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(areaSheet, i, j).Style);
                }
            }

            for (int i = 0; i < (3 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(groupSheet, 0, i).Style);
            }

            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 3; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(groupSheet, i, j).Style);
                }
            }

            for (int i = 0; i < (4 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(subAreaSheet, 0, i).Style);
            }

            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 4; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(subAreaSheet, i, j).Style);
                }
            }
        }
Пример #2
0
        /// <summary>
        ///
        /// </summary>
        /// <example>
        /// viewId;entityLogicalName;viewName;ViewType;Type;LCID1;LCID2;...;LCODX
        /// </example>
        /// <param name="languages"></param>
        /// <param name="file"></param>
        /// <param name="service"></param>
        public void Export(List <int> languages, ExcelWorkbook file, IOrganizationService service)
        {
            var line = 1;

            var siteMap    = GetSiteMap(service);
            var siteMapDoc = new XmlDocument();

            siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());

            var crmSiteMapAreas    = new List <CrmSiteMapArea>();
            var crmSiteMapGroups   = new List <CrmSiteMapGroup>();
            var crmSiteMapSubAreas = new List <CrmSiteMapSubArea>();

            #region Export Area

            var areaNodes = siteMapDoc.SelectNodes("SiteMap/Area");
            foreach (XmlNode areaNode in areaNodes)
            {
                var area = new CrmSiteMapArea {
                    Id = areaNode.Attributes["Id"].Value
                };
                foreach (XmlNode titleNode in areaNode.SelectNodes("Titles/Title"))
                {
                    area.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                }
                foreach (XmlNode titleNode in areaNode.SelectNodes("Descriptions/Description"))
                {
                    area.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                }

                crmSiteMapAreas.Add(area);

                #region Export Groups

                var groupNodes = areaNode.SelectNodes("Group");
                foreach (XmlNode groupNode in groupNodes)
                {
                    var group = new CrmSiteMapGroup
                    {
                        Id     = groupNode.Attributes["Id"].Value,
                        AreaId = areaNode.Attributes["Id"].Value
                    };
                    foreach (XmlNode titleNode in groupNode.SelectNodes("Titles/Title"))
                    {
                        group.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                    }
                    foreach (XmlNode titleNode in groupNode.SelectNodes("Descriptions/Description"))
                    {
                        group.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                    }

                    crmSiteMapGroups.Add(group);

                    #region Export SubArea

                    var subAreaNodes = groupNode.SelectNodes("SubArea");
                    foreach (XmlNode subAreaNode in subAreaNodes)
                    {
                        var subArea = new CrmSiteMapSubArea()
                        {
                            Id      = subAreaNode.Attributes["Id"].Value,
                            GroupId = groupNode.Attributes["Id"].Value,
                            AreaId  = areaNode.Attributes["Id"].Value
                        };
                        foreach (XmlNode titleNode in subAreaNode.SelectNodes("Titles/Title"))
                        {
                            subArea.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Title"].Value);
                        }
                        foreach (XmlNode titleNode in subAreaNode.SelectNodes("Descriptions/Description"))
                        {
                            subArea.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value), titleNode.Attributes["Description"].Value);
                        }

                        crmSiteMapSubAreas.Add(subArea);
                    }

                    #endregion Export SubArea
                }

                #endregion Export Groups
            }

            #endregion Export Area

            #region Area sheet

            var areaSheet = file.Worksheets.Add("SiteMap Areas");
            AddAreaHeader(areaSheet, languages);
            foreach (var crmArea in crmSiteMapAreas)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion Area sheet

            #region Group sheet

            line = 1;
            var groupSheet = file.Worksheets.Add("SiteMap Groups");
            AddGroupHeader(groupSheet, languages);
            foreach (var crmGroup in crmSiteMapGroups)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion Group sheet

            #region SubArea sheet

            line = 1;
            var subAreaSheet = file.Worksheets.Add("SiteMap SubAreas");
            AddSubAreaHeader(subAreaSheet, languages);
            foreach (var crmSubArea in crmSiteMapSubAreas)
            {
                var cell = 0;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Title";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                }

                line++;
                cell = 0;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Description";

                foreach (var lcid in languages)
                {
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                }
                line++;
            }

            #endregion SubArea sheet

            // Applying style to cells
            for (int i = 0; i < (2 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(areaSheet, 0, i).Style);
            }
            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 2; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(areaSheet, i, j).Style);
                }
            }

            for (int i = 0; i < (3 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(groupSheet, 0, i).Style);
            }

            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 3; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(groupSheet, i, j).Style);
                }
            }

            for (int i = 0; i < (4 + languages.Count); i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(subAreaSheet, 0, i).Style);
            }

            for (int i = 1; i < line; i++)
            {
                for (int j = 0; j < 4; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(subAreaSheet, i, j).Style);
                }
            }
        }
        /// <summary>
        ///
        /// </summary>
        /// <example>
        /// viewId;entityLogicalName;viewName;ViewType;Type;LCID1;LCID2;...;LCODX
        /// </example>
        /// <param name="languages"></param>
        /// <param name="file"></param>
        /// <param name="service"></param>
        /// <param name="settings"></param>
        public void Export(List <int> languages, ExcelWorkbook file, IOrganizationService service, ExportSettings settings, ConnectionDetail detail)
        {
            var line = 0;
            int cell;

            siteMaps = GetSiteMaps(settings, service, detail);

            var areaSheet    = file.Worksheets.Add("SiteMap Areas");
            var groupSheet   = file.Worksheets.Add("SiteMap Groups");
            var subAreaSheet = file.Worksheets.Add("SiteMap SubAreas");

            AddAreaHeader(areaSheet, languages);
            AddGroupHeader(groupSheet, languages);
            AddSubAreaHeader(subAreaSheet, languages);

            var crmSiteMapAreas    = new List <CrmSiteMapArea>();
            var crmSiteMapGroups   = new List <CrmSiteMapGroup>();
            var crmSiteMapSubAreas = new List <CrmSiteMapSubArea>();

            foreach (var siteMap in siteMaps.Entities)
            {
                var siteMapDoc = new XmlDocument();
                siteMapDoc.LoadXml(siteMap["sitemapxml"].ToString());

                #region Export Area

                var areaNodes = siteMapDoc.SelectNodes("SiteMap/Area");
                foreach (XmlNode areaNode in areaNodes)
                {
                    var area = new CrmSiteMapArea
                    {
                        Id          = areaNode.Attributes["Id"].Value,
                        SiteMapId   = siteMap.Id,
                        SiteMapName = siteMap.GetAttributeValue <string>("sitemapname")
                    };

                    if (settings.ExportNames)
                    {
                        foreach (XmlNode titleNode in areaNode.SelectNodes("Titles/Title"))
                        {
                            area.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                            titleNode.Attributes["Title"].Value);
                        }
                    }

                    if (settings.ExportDescriptions)
                    {
                        foreach (XmlNode titleNode in areaNode.SelectNodes("Descriptions/Description"))
                        {
                            area.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                                  titleNode.Attributes["Description"].Value);
                        }
                    }

                    crmSiteMapAreas.Add(area);

                    #region Export Groups

                    var groupNodes = areaNode.SelectNodes("Group");
                    foreach (XmlNode groupNode in groupNodes)
                    {
                        var group = new CrmSiteMapGroup
                        {
                            Id          = groupNode.Attributes["Id"].Value,
                            AreaId      = areaNode.Attributes["Id"].Value,
                            SiteMapId   = siteMap.Id,
                            SiteMapName = siteMap.GetAttributeValue <string>("sitemapname")
                        };

                        if (settings.ExportNames)
                        {
                            foreach (XmlNode titleNode in groupNode.SelectNodes("Titles/Title"))
                            {
                                group.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                                 titleNode.Attributes["Title"].Value);
                            }
                        }

                        if (settings.ExportDescriptions)
                        {
                            foreach (XmlNode titleNode in groupNode.SelectNodes("Descriptions/Description"))
                            {
                                group.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                                       titleNode.Attributes["Description"].Value);
                            }
                        }

                        crmSiteMapGroups.Add(group);

                        #region Export SubArea

                        var subAreaNodes = groupNode.SelectNodes("SubArea");
                        foreach (XmlNode subAreaNode in subAreaNodes)
                        {
                            var subArea = new CrmSiteMapSubArea()
                            {
                                Id          = subAreaNode.Attributes["Id"].Value,
                                GroupId     = groupNode.Attributes["Id"].Value,
                                AreaId      = areaNode.Attributes["Id"].Value,
                                SiteMapId   = siteMap.Id,
                                SiteMapName = siteMap.GetAttributeValue <string>("sitemapname")
                            };

                            if (settings.ExportNames)
                            {
                                foreach (XmlNode titleNode in subAreaNode.SelectNodes("Titles/Title"))
                                {
                                    subArea.Titles.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                                       titleNode.Attributes["Title"].Value);
                                }
                            }

                            if (settings.ExportDescriptions)
                            {
                                foreach (XmlNode titleNode in subAreaNode.SelectNodes("Descriptions/Description"))
                                {
                                    subArea.Descriptions.Add(int.Parse(titleNode.Attributes["LCID"].Value),
                                                             titleNode.Attributes["Description"].Value);
                                }
                            }

                            crmSiteMapSubAreas.Add(subArea);
                        }

                        #endregion Export SubArea
                    }

                    #endregion Export Groups
                }

                #endregion Export Area
            }

            #region Area sheet

            foreach (var crmArea in crmSiteMapAreas)
            {
                if (settings.ExportNames)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.SiteMapName;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Title";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(areaSheet, line, cell++).Value =
                            crmArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }

                if (settings.ExportDescriptions)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.SiteMapName;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = crmArea.Id;
                    ZeroBasedSheet.Cell(areaSheet, line, cell++).Value = "Description";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(areaSheet, line, cell++).Value =
                            crmArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }
            }

            #endregion Area sheet

            #region Group sheet

            line = 0;
            foreach (var crmGroup in crmSiteMapGroups)
            {
                if (settings.ExportNames)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.SiteMapName;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Title";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(groupSheet, line, cell++).Value =
                            crmGroup.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }

                if (settings.ExportDescriptions)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.SiteMapName;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.AreaId;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = crmGroup.Id;
                    ZeroBasedSheet.Cell(groupSheet, line, cell++).Value = "Description";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(groupSheet, line, cell++).Value =
                            crmGroup.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }
            }

            #endregion Group sheet

            #region SubArea sheet

            line = 0;
            foreach (var crmSubArea in crmSiteMapSubAreas)
            {
                if (settings.ExportNames)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.SiteMapName;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Title";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value =
                            crmSubArea.Titles.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }

                if (settings.ExportDescriptions)
                {
                    line++;
                    cell = 0;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.SiteMapName;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.SiteMapId.ToString();
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.AreaId;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.GroupId;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = crmSubArea.Id;
                    ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value = "Description";

                    foreach (var lcid in languages)
                    {
                        ZeroBasedSheet.Cell(subAreaSheet, line, cell++).Value =
                            crmSubArea.Descriptions.FirstOrDefault(n => n.Key == lcid).Value;
                    }
                }
            }

            #endregion SubArea sheet

            // Applying style to cells
            for (int i = 0; i < 4 + languages.Count; i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(areaSheet, 0, i).Style);
            }
            for (int i = 1; i <= crmSiteMapAreas.Select(c => c.Titles).Count() + crmSiteMapAreas.Select(c => c.Descriptions).Count(); i++)
            {
                for (int j = 0; j < 4; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(areaSheet, i, j).Style);
                }
            }

            for (int i = 0; i < 5 + languages.Count; i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(groupSheet, 0, i).Style);
            }

            for (int i = 1; i <= crmSiteMapGroups.Select(c => c.Titles).Count() + crmSiteMapGroups.Select(c => c.Descriptions).Count(); i++)
            {
                for (int j = 0; j < 5; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(groupSheet, i, j).Style);
                }
            }

            for (int i = 0; i < 6 + languages.Count; i++)
            {
                StyleMutator.TitleCell(ZeroBasedSheet.Cell(subAreaSheet, 0, i).Style);
            }

            for (int i = 1; i <= crmSiteMapSubAreas.Select(c => c.Titles).Count() + crmSiteMapSubAreas.Select(c => c.Descriptions).Count(); i++)
            {
                for (int j = 0; j < 6; j++)
                {
                    StyleMutator.HighlightedCell(ZeroBasedSheet.Cell(subAreaSheet, i, j).Style);
                }
            }
        }