GetOrAddTableColumn ( ListColumn oSourceColumn, ListObject oNewTable ) { Debug.Assert(oSourceColumn != null); Debug.Assert(oNewTable != null); AssertValid(); String sColumnName = oSourceColumn.Name; Range oSourceColumnData = oSourceColumn.DataBodyRange; Debug.Assert(oSourceColumnData != null); ListColumn oNewColumn; if (!ExcelTableUtil.TryGetOrAddTableColumn(oNewTable, sColumnName, (Double)oSourceColumnData.ColumnWidth, null, out oNewColumn)) { throw new ExportWorkbookException( "A column couldn't be added to the new workbook." ); } Range oNewColumnData = oNewColumn.DataBodyRange; Debug.Assert(oNewColumnData != null); oNewColumnData.NumberFormat = oSourceColumnData.NumberFormat; return(oNewColumnData); }
MarkRowsForDeletion ( ListObject oEdgeTable, Object [,] aoVertex1NameValues, Object [,] aoVertex2NameValues, Object [,] aoThirdColumnValues, Boolean bGraphIsDirected, out ListColumn oDeleteIfEmptyColumn, out Range oDeleteIfEmptyData, out Object [,] aoDeleteIfEmptyValues ) { Debug.Assert(oEdgeTable != null); Debug.Assert(aoVertex1NameValues != null); Debug.Assert(aoVertex2NameValues != null); AssertValid(); HashSet <String> oUniqueEdgeKeys = new HashSet <String>(); if (!ExcelTableUtil.TryGetOrAddTableColumn(oEdgeTable, DeleteIfEmptyColumnName, ExcelTableUtil.AutoColumnWidth, null, out oDeleteIfEmptyColumn, out oDeleteIfEmptyData, out aoDeleteIfEmptyValues)) { throw new InvalidOperationException( "Can't add marked for deletion column."); } Int32 iRows = GetRowCount(aoVertex1NameValues); for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++) { String sEdgeKey; Object oDeleteIfEmpty = 1; if ( TryGetEdgeKey(iRowOneBased, aoVertex1NameValues, aoVertex2NameValues, aoThirdColumnValues, bGraphIsDirected, out sEdgeKey) && !oUniqueEdgeKeys.Add(sEdgeKey) ) { // This is a duplicate that is not the first instance. It // should be deleted. oDeleteIfEmpty = null; } aoDeleteIfEmptyValues[iRowOneBased, 1] = oDeleteIfEmpty; } oDeleteIfEmptyData.set_Value(Missing.Value, aoDeleteIfEmptyValues); }
TryGetMarkedColumnData ( out Microsoft.Office.Interop.Excel.Range oMarkedColumnData ) { AssertValid(); oMarkedColumnData = null; Microsoft.Office.Interop.Excel.ListColumn oMarkedColumn; return( ExcelTableUtil.TryGetOrAddTableColumn(Vertices.InnerObject, VertexTableColumnNames.IsMarked, ExcelTableUtil.AutoColumnWidth, null, out oMarkedColumn) && ExcelTableUtil.TryGetTableColumnData(oMarkedColumn, out oMarkedColumnData) ); }
CountDuplicateEdges ( ListObject oEdgeTable, Object [,] aoVertex1NameValues, Object [,] aoVertex2NameValues, Object [,] aoThirdColumnValues, Boolean bGraphIsDirected ) { Debug.Assert(oEdgeTable != null); Debug.Assert(aoVertex1NameValues != null); Debug.Assert(aoVertex2NameValues != null); AssertValid(); ListColumn oEdgeWeightColumn; Range oEdgeWeightData; Object [,] aoEdgeWeightValues; if (!ExcelTableUtil.TryGetOrAddTableColumn(oEdgeTable, EdgeTableColumnNames.EdgeWeight, 13.7F, null, out oEdgeWeightColumn, out oEdgeWeightData, out aoEdgeWeightValues)) { throw new InvalidOperationException( "Can't add edge weight column."); } // The key identifies a unique edge and the value is the sum of the // edge weight values for all duplicate edges with the key. Dictionary <String, Double> oEdgeWeightSums = new Dictionary <String, Double>(); Int32 iRows = GetRowCount(aoVertex1NameValues); // Populate the dictionary. for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++) { String sEdgeKey; if (TryGetEdgeKey(iRowOneBased, aoVertex1NameValues, aoVertex2NameValues, aoThirdColumnValues, bGraphIsDirected, out sEdgeKey)) { // Does the row already have an edge weight in the edge weight // column? Double dEdgeWeightForRow; if (!ExcelUtil.TryGetDoubleFromCell(aoEdgeWeightValues, iRowOneBased, 1, out dEdgeWeightForRow)) { // No. dEdgeWeightForRow = 1; } // Has a row with the same key already been found? Double dEdgeWeightSum; if (!oEdgeWeightSums.TryGetValue(sEdgeKey, out dEdgeWeightSum)) { // No. dEdgeWeightSum = 0; } oEdgeWeightSums[sEdgeKey] = dEdgeWeightForRow + dEdgeWeightSum; } } // Now fill in the edge weight cells with the dictionary values. for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++) { String sEdgeKey; if (TryGetEdgeKey(iRowOneBased, aoVertex1NameValues, aoVertex2NameValues, aoThirdColumnValues, bGraphIsDirected, out sEdgeKey)) { aoEdgeWeightValues[iRowOneBased, 1] = oEdgeWeightSums[sEdgeKey]; } } oEdgeWeightData.set_Value(Missing.Value, aoEdgeWeightValues); }
FillIDColumn ( ListObject oTable ) { Debug.Assert(oTable != null); AssertValid(); // Read the range that contains visible data. If the table is // filtered, the range may contain multiple areas. Range oVisibleRange; ListColumn oIDColumn; if ( !ExcelTableUtil.TryGetVisibleTableRange(oTable, out oVisibleRange) || ExcelTableUtil.VisibleTableRangeIsEmpty(oTable) || !ExcelTableUtil.TryGetOrAddTableColumn(oTable, CommonTableColumnNames.ID, ExcelTableUtil.AutoColumnWidth, null, out oIDColumn) ) { return; } Int32 iIDColumnIndex = oIDColumn.Index; Range oDataBodyRange = oTable.DataBodyRange; Debug.Assert(oDataBodyRange != null); Debug.Assert(oTable.Parent is Worksheet); Worksheet oWorksheet = (Worksheet)oTable.Parent; foreach (Range oArea in oVisibleRange.Areas) { // Get the rows within the ID column that should be filled in. Int32 iAreaStartRowOneBased = oArea.Row; Int32 iRowsInArea = oArea.Rows.Count; Int32 iTableStartRowOneBased = oTable.Range.Row; Range oIDRange = oWorksheet.get_Range( (Range)oDataBodyRange.Cells[ iAreaStartRowOneBased - iTableStartRowOneBased, iIDColumnIndex], (Range)oDataBodyRange.Cells[ iAreaStartRowOneBased - iTableStartRowOneBased + iRowsInArea - 1, iIDColumnIndex] ); // Use the Excel row numbers as the unique IDs. Create a // one-column array, then fill it in with the row numbers. Int32 iRows = oIDRange.Rows.Count; Object [,] aoValues = ExcelUtil.GetSingleColumn2DArray(iRows); for (Int32 i = 1; i <= iRows; i++) { aoValues[i, 1] = iAreaStartRowOneBased + i - 1; } oIDRange.Value2 = aoValues; #if false // Note: Don't use the following clever code to fill in the row // numbers. On large worksheets, the calculations take forever. oIDRange.Value2 = "=ROW()"; oIDRange.Value2 = oIDRange.Value2; #endif } }
UpdateWorkbook ( Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(workbook != null); Worksheet oWorksheet; ListObject oTable; ListColumn oColumn; // Rename the Radius column on the Vertices worksheet to Size. if (ExcelTableUtil.TryGetTable(workbook, "Vertices", "Vertices", out oTable) && ExcelTableUtil.TryGetTableColumn(oTable, "Radius", out oColumn) ) { oColumn.Name = "Size"; } // Rename the Clusters worksheet to Groups. if (ExcelUtil.TryGetWorksheet(workbook, "Clusters", out oWorksheet)) { oWorksheet.Name = "Groups"; if (ExcelTableUtil.TryGetTable(oWorksheet, "Clusters", out oTable)) { oTable.Name = "Groups"; if (ExcelTableUtil.TryGetTableColumn(oTable, "Cluster", out oColumn)) { oColumn.Name = "Group"; } } } // Rename the Cluster Vertices worksheet to Group Vertices. if (ExcelUtil.TryGetWorksheet(workbook, "Cluster Vertices", out oWorksheet)) { oWorksheet.Name = "Group Vertices"; if (ExcelTableUtil.TryGetTable(oWorksheet, "ClusterVertices", out oTable)) { oTable.Name = "GroupVertices"; if (ExcelTableUtil.TryGetTableColumn(oTable, "Cluster", out oColumn)) { oColumn.Name = "Group"; } } } // Add a Label column to the Groups worksheet. if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Groups, TableNames.Groups, out oTable)) { ExcelTableUtil.TryGetOrAddTableColumn(oTable, GroupTableColumnNames.Label, ExcelTableUtil.AutoColumnWidth, CellStyleNames.Label, out oColumn); } // Rename the Metric column on the Overall Metrics worksheet to Graph // Metric. if (ExcelUtil.TryGetWorksheet(workbook, "Overall Metrics", out oWorksheet)) { if (ExcelTableUtil.TryGetTable(oWorksheet, "OverallMetrics", out oTable)) { if (ExcelTableUtil.TryGetTableColumn(oTable, "Metric", out oColumn)) { oColumn.Name = "Graph Metric"; } } } }