Smrf.NodeXL.ExcelTemplate.GroupManager.GetExcelFormulaForVertexID C# (CSharp) Method

    GetExcelFormulaForVertexID()
    {
        // Given a vertex name in the group-vertex table, this formula
        // retrieves the corresponding vertex ID from the vertex worksheet.
        // When formatted, it looks like this:
        //
        // =VLOOKUP(GroupVertices[[#This Row],[Vertex]], Vertices,
        // MATCH("ID", Vertices[#Headers], 0), FALSE)

        return (String.Format(

            "=VLOOKUP({0}[[#This Row],[{1}]], {2},"
                + " MATCH(\"{3}\", {2}[#Headers], 0), FALSE)"
            ,
            TableNames.GroupVertices,
            GroupVertexTableColumnNames.VertexName,
            TableNames.Vertices,
            CommonTableColumnNames.ID
            ) );
    }

        Convert <TGroup>
        (
            ICollection <TGroup> groups,
            GroupToGroupVertices <TGroup> groupToGroupVertices,
            GroupToString <TGroup> groupToGroupName,
            Boolean collapseGroups,
            GroupToString <TGroup> groupToCollapsedGroupAttributes
        )
        {
            Debug.Assert(groups != null);
            Debug.Assert(groupToGroupVertices != null);

            // These columns are needed:
            //
            // * Group name on the group worksheet.
            //
            // * Vertex color on the group worksheet.
            //
            // * Vertex shape on the group worksheet.
            //
            // * Group name on the group-vertex worksheet.
            //
            // * Vertex name on the group-vertex worksheet.
            //
            // * Vertex ID on the group-vertex worksheet.  This gets copied from
            //   the hidden ID column on the Vertex worksheet via an Excel
            //   formula.

            // These columns might be needed:
            //
            // * Collapsed flag on the group worksheet.
            //
            // * Collapsed group attributes on the group worksheet.


            List <GraphMetricValueOrdered> oGroupNamesForGroupWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oVertexColorsForGroupWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oVertexShapesForGroupWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oCollapsedFlagsForGroupWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oCollapsedAttributesForGroupWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oGroupNamesForGroupVertexWorksheet =
                new List <GraphMetricValueOrdered>();

            List <GraphMetricValueOrdered> oVertexNamesForGroupVertexWorksheet =
                new List <GraphMetricValueOrdered>();

            // This column contains a constant value, which is a formula.

            GraphMetricValueOrdered [] aoVertexIDsForGroupVertexWorksheet =
            {
                new GraphMetricValueOrdered(
                    GroupManager.GetExcelFormulaForVertexID())
            };

            Int32 iGroups = groups.Count;
            Int32 iGroup  = 0;

            ColorConverter2 oColorConverter2 = new ColorConverter2();

            VertexShapeConverter oVertexShapeConverter =
                new VertexShapeConverter();

            GraphMetricValueOrdered oTrueGraphMetricValueOrdered =
                new GraphMetricValueOrdered(
                    (new BooleanConverter()).GraphToWorkbook(true));

            foreach (TGroup oGroup in
                     from oGroup in groups
                     orderby groupToGroupVertices(oGroup).Count descending
                     select oGroup)
            {
                String sGroupName;

                if (groupToGroupName == null)
                {
                    sGroupName = 'G' + (iGroup + 1).ToString(
                        CultureInfo.InvariantCulture);
                }
                else
                {
                    sGroupName = groupToGroupName(oGroup);
                }

                Color       oColor;
                VertexShape eShape;

                GroupManager.GetVertexAttributes(iGroup, iGroups, out oColor,
                                                 out eShape);

                GraphMetricValueOrdered oGroupNameGraphMetricValue =
                    new GraphMetricValueOrdered(sGroupName);

                oGroupNamesForGroupWorksheet.Add(oGroupNameGraphMetricValue);

                // Write the color in a format that is understood by
                // ColorConverter2.WorkbookToGraph(), which is what
                // WorksheetReaderBase uses.

                oVertexColorsForGroupWorksheet.Add(
                    new GraphMetricValueOrdered(
                        oColorConverter2.GraphToWorkbook(oColor)));

                oVertexShapesForGroupWorksheet.Add(
                    new GraphMetricValueOrdered(
                        oVertexShapeConverter.GraphToWorkbook(eShape)));

                if (collapseGroups)
                {
                    oCollapsedFlagsForGroupWorksheet.Add(
                        oTrueGraphMetricValueOrdered);
                }

                if (groupToCollapsedGroupAttributes != null)
                {
                    oCollapsedAttributesForGroupWorksheet.Add(
                        new GraphMetricValueOrdered(
                            groupToCollapsedGroupAttributes(oGroup)));
                }

                Int32 iVertices = 0;

                foreach (IVertex oVertex in groupToGroupVertices(oGroup))
                {
                    oGroupNamesForGroupVertexWorksheet.Add(
                        oGroupNameGraphMetricValue);

                    oVertexNamesForGroupVertexWorksheet.Add(
                        new GraphMetricValueOrdered(
                            ExcelTextUtil.ForceCellText(oVertex.Name)));

                    iVertices++;
                }

                iGroup++;
            }

            List <GraphMetricColumn> oGraphMetricColumns =
                new List <GraphMetricColumn>();

            oGraphMetricColumns.AddRange(

                new GraphMetricColumn [] {
                new GraphMetricColumnOrdered(WorksheetNames.Groups,
                                             TableNames.Groups,
                                             GroupTableColumnNames.Name,
                                             ExcelTableUtil.AutoColumnWidth, null,
                                             CellStyleNames.Required,
                                             oGroupNamesForGroupWorksheet.ToArray()
                                             ),

                new GraphMetricColumnOrdered(WorksheetNames.Groups,
                                             TableNames.Groups,
                                             GroupTableColumnNames.VertexColor,
                                             ExcelTableUtil.AutoColumnWidth, null,
                                             CellStyleNames.VisualProperty,
                                             oVertexColorsForGroupWorksheet.ToArray()
                                             ),

                new GraphMetricColumnOrdered(WorksheetNames.Groups,
                                             TableNames.Groups,
                                             GroupTableColumnNames.VertexShape,
                                             ExcelTableUtil.AutoColumnWidth, null,
                                             CellStyleNames.VisualProperty,
                                             oVertexShapesForGroupWorksheet.ToArray()
                                             ),

                new GraphMetricColumnOrdered(WorksheetNames.GroupVertices,
                                             TableNames.GroupVertices,
                                             GroupVertexTableColumnNames.GroupName,
                                             ExcelTableUtil.AutoColumnWidth, null, null,
                                             oGroupNamesForGroupVertexWorksheet.ToArray()
                                             ),

                new GraphMetricColumnOrdered(WorksheetNames.GroupVertices,
                                             TableNames.GroupVertices,
                                             GroupVertexTableColumnNames.VertexName,
                                             ExcelTableUtil.AutoColumnWidth, null, null,
                                             oVertexNamesForGroupVertexWorksheet.ToArray()
                                             ),

                new GraphMetricColumnOrdered(WorksheetNames.GroupVertices,
                                             TableNames.GroupVertices,
                                             GroupVertexTableColumnNames.VertexID,
                                             ExcelTableUtil.AutoColumnWidth, null, null,
                                             aoVertexIDsForGroupVertexWorksheet
                                             ),
            });

            if (collapseGroups)
            {
                oGraphMetricColumns.Add(
                    new GraphMetricColumnOrdered(WorksheetNames.Groups,
                                                 TableNames.Groups,
                                                 GroupTableColumnNames.Collapsed,
                                                 ExcelTableUtil.AutoColumnWidth, null,
                                                 CellStyleNames.VisualProperty,
                                                 oCollapsedFlagsForGroupWorksheet.ToArray()
                                                 ));
            }

            if (groupToCollapsedGroupAttributes != null)
            {
                oGraphMetricColumns.Add(
                    new GraphMetricColumnOrdered(WorksheetNames.Groups,
                                                 TableNames.Groups,
                                                 GroupTableColumnNames.CollapsedAttributes,
                                                 ExcelTableUtil.AutoColumnWidth, null,
                                                 CellStyleNames.DoNotEdit,
                                                 oCollapsedAttributesForGroupWorksheet.ToArray()
                                                 ));
            }

            return(oGraphMetricColumns.ToArray());
        }