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) ); }
GetAttributeColumn ( ListObject oTable, String sAttribute, out ListColumn oAttributeColumn, out Range oAttributeColumnData ) { Debug.Assert(oTable != null); Debug.Assert(!String.IsNullOrEmpty(sAttribute)); if (!ExcelTableUtil.TryGetTableColumn(oTable, sAttribute, out oAttributeColumn)) { if (!ExcelTableUtil.TryAddTableColumn(oTable, sAttribute, ExcelTableUtil.AutoColumnWidth, null, out oAttributeColumn)) { goto CannotGetColumn; } // Wrap the text in the new column's header. ExcelTableUtil.WrapTableColumnHeader(oAttributeColumn); // This sometimes wraps a single-word header. Fix it. oAttributeColumn.Range.EntireColumn.AutoFit(); } if (ExcelTableUtil.TryGetTableColumnData(oAttributeColumn, out oAttributeColumnData)) { // Success. return; } CannotGetColumn: throw new WorkbookFormatException( "The " + sAttribute + " column couldn't be added." ); }
TryGetVertexTableAndVisibleColumnData ( Workbook oWorkbook, out ListObject oVertexTable, out Range oVisibleNameColumnData, out Range oVisibleSubgraphImageColumnData ) { Debug.Assert(oWorkbook != null); oVertexTable = null; oVisibleNameColumnData = null; oVisibleSubgraphImageColumnData = null; Range oNameColumnData, oSubgraphImageColumnData; return( // If the vertex table, the name column data, or the image column // data aren't available, nothing can be done. ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable) && ExcelTableUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.VertexName, out oNameColumnData) && TryGetSubgraphImageColumnData(oVertexTable, out oSubgraphImageColumnData) // Reduce the name and image column data to visible areas only. && ExcelUtil.TryGetVisibleRange(oNameColumnData, out oVisibleNameColumnData) && ExcelUtil.TryGetVisibleRange(oSubgraphImageColumnData, out oVisibleSubgraphImageColumnData) ); }
TryGetRequiredColumnInformation ( GraphMetricColumn oGraphMetricColumn, ListObject oTable, out Range oVisibleColumnData ) { Debug.Assert(oGraphMetricColumn != null); Debug.Assert(oTable != null); AssertValid(); oVisibleColumnData = null; // Add the specified column if it's not already present. String sColumnName = oGraphMetricColumn.ColumnName; String sColumnStyle = oGraphMetricColumn.Style; Microsoft.Office.Interop.Excel.ListColumn oColumn; if ( !ExcelTableUtil.TryGetTableColumn(oTable, sColumnName, out oColumn) && !ExcelTableUtil.TryAddTableColumn(oTable, sColumnName, oGraphMetricColumn.ColumnWidthChars, sColumnStyle, out oColumn) ) { // Give up. return(false); } Range oColumnData; if (!ExcelTableUtil.TryGetTableColumnData(oTable, sColumnName, out oColumnData)) { return(false); } ExcelUtil.SetRangeStyle(oColumnData, sColumnStyle); String sNumberFormat = oGraphMetricColumn.NumberFormat; if (sNumberFormat != null) { oColumnData.NumberFormat = sNumberFormat; } // Wrapping text makes Range.set_Value() very slow, so turn it off. oColumn.Range.WrapText = false; // But wrap the text in the column's header. ExcelTableUtil.WrapTableColumnHeader(oColumn); // Get the visible range. if (!ExcelUtil.TryGetVisibleRange(oColumnData, out oVisibleColumnData)) { return(false); } return(true); }
FillVertexTable ( ListObject oVertexTable, HashSet <String> oUniqueVertexNames ) { Debug.Assert(oVertexTable != null); Debug.Assert(oUniqueVertexNames != null); AssertValid(); // There may already be some vertex names in the table. For each // existing name, remove the name from the HashSet. Int32 iExistingRows = 0; Range oVertexNameRange; if (ExcelTableUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.VertexName, out oVertexNameRange)) { iExistingRows = oVertexNameRange.Rows.Count; // Read the vertex names all at once. Object [,] aoVertexNameValues = ExcelUtil.GetRangeValues(oVertexNameRange); // Loop through the vertices. for (Int32 iRowOneBased = 1; iRowOneBased <= iExistingRows; iRowOneBased++) { // Get the vertex name and remove it from the HashSet. String sVertexName; if (ExcelUtil.TryGetNonEmptyStringFromCell( aoVertexNameValues, iRowOneBased, 1, out sVertexName)) { oUniqueVertexNames.Remove(sVertexName); } } } // Now create an array for the vertices that remain in the HashSet. // These are vertices that were in the edge table but not the vertex // table. Int32 iRowsToAdd = oUniqueVertexNames.Count; if (iRowsToAdd == 0) { return; } String [,] asAddedVertexNameValues = new String [iRowsToAdd, 1]; Int32 iIndex = 0; foreach (String sUniqueVertexName in oUniqueVertexNames) { asAddedVertexNameValues[iIndex, 0] = sUniqueVertexName; iIndex++; } // The table may be empty or contain empty rows. If so, the remaining // vertices should be appended after the last non-empty row. Int32 iLastNonEmptyRowOneBased; Range oDataBodyRange = oVertexTable.DataBodyRange; if ( oDataBodyRange == null || !ExcelUtil.TryGetLastNonEmptyRow(oDataBodyRange, out iLastNonEmptyRowOneBased) ) { // There were no non-empty data rows in the table. Use an offset // of 1 from the header row instead. oDataBodyRange = oVertexTable.HeaderRowRange; iExistingRows = 1; } else { iExistingRows = iLastNonEmptyRowOneBased - oDataBodyRange.Row + 1; } // Get the index of the vertex name column. ListColumn oVertexNameColumn; if (!ExcelTableUtil.TryGetTableColumn(oVertexTable, VertexTableColumnNames.VertexName, out oVertexNameColumn)) { // This can't happen, because GetRequiredTables() has // verified that the column exists. Debug.Assert(false); } Int32 iVertexNameColumnIndexOneBased = oVertexNameColumn.Index; Debug.Assert(oVertexTable.Parent is Worksheet); Worksheet oVertexWorksheet = (Worksheet)oVertexTable.Parent; Range oAddedVertexNameRange = oVertexWorksheet.get_Range( (Range)oDataBodyRange.Cells[iExistingRows + 1, iVertexNameColumnIndexOneBased], (Range)oDataBodyRange.Cells[iExistingRows + iRowsToAdd, iVertexNameColumnIndexOneBased] ); oAddedVertexNameRange.set_Value( Missing.Value, asAddedVertexNameValues); }
ImportGraph ( IGraph sourceGraph, String [] edgeAttributes, String [] vertexAttributes, Boolean clearTablesFirst, Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook ) { Debug.Assert(sourceGraph != null); Debug.Assert(destinationNodeXLWorkbook != null); if (clearTablesFirst) { NodeXLWorkbookUtil.ClearAllNodeXLTables(destinationNodeXLWorkbook); } // Get the required table that contains edge data. GetEdgeTable() // throws an exception if the table is missing. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); ListObject oEdgeTable = oEdgeWorksheetReader.GetEdgeTable(destinationNodeXLWorkbook); // Get the required columns. Range oVertex1NameColumnData = null; Range oVertex2NameColumnData = null; if ( !ExcelTableUtil.TryGetTableColumnData(oEdgeTable, EdgeTableColumnNames.Vertex1Name, out oVertex1NameColumnData) || !ExcelTableUtil.TryGetTableColumnData(oEdgeTable, EdgeTableColumnNames.Vertex2Name, out oVertex2NameColumnData) ) { ErrorUtil.OnMissingColumn(); } // Import the edges and their attributes into the workbook. ImportEdges(sourceGraph, edgeAttributes, oEdgeTable, oVertex1NameColumnData, oVertex2NameColumnData, !clearTablesFirst); // Populate the vertex worksheet with the name of each unique vertex in // the edge worksheet. (new VertexWorksheetPopulator()).PopulateVertexWorksheet( destinationNodeXLWorkbook, false); // Get the table that contains vertex data. ListObject oVertexTable; Range oVertexNameColumnData = null; Range oVisibilityColumnData = null; if ( !ExcelTableUtil.TryGetTable(destinationNodeXLWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable) || !ExcelTableUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.VertexName, out oVertexNameColumnData) || !ExcelTableUtil.TryGetTableColumnData(oVertexTable, CommonTableColumnNames.Visibility, out oVisibilityColumnData) ) { ErrorUtil.OnMissingColumn(); } // Import isolated vertices and the attributes for all the graph's // vertices. ImportVertices(sourceGraph, vertexAttributes, oVertexTable, oVertexNameColumnData, oVisibilityColumnData); }
WriteAllSettings() { AssertValid(); Debug.Assert(m_oSettings != null); ListObject oPerWorkbookSettingsTable; if (!TryGetPerWorkbookSettingsTable(out oPerWorkbookSettingsTable)) { return; } // Don't do this. For some reason, it causes the table to misbehave // in the ExcelUtil.SetRangeValues() calls below when the workbook is // saved via TaskAutomator. // // The consequence of not clearing the table is that names and values // that are no longer used will remain in the table. // // ExcelTableUtil.ClearTable(oPerWorkbookSettingsTable); // Attempt to get the optional table columns that contain the settings. Range oNameColumnData, oValueColumnData; if ( !ExcelTableUtil.TryGetTableColumnData(oPerWorkbookSettingsTable, PerWorkbookSettingsTableColumnNames.Name, out oNameColumnData) || !ExcelTableUtil.TryGetTableColumnData(oPerWorkbookSettingsTable, PerWorkbookSettingsTableColumnNames.Value, out oValueColumnData) ) { return; } // Copy the settings to arrays. Int32 iSettings = m_oSettings.Count; Object [,] aoNameColumnValues = ExcelUtil.GetSingleColumn2DArray(iSettings); Object [,] aoValueColumnValues = ExcelUtil.GetSingleColumn2DArray(iSettings); Int32 i = 1; foreach (KeyValuePair <String, Object> oKeyValuePair in m_oSettings) { aoNameColumnValues[i, 1] = oKeyValuePair.Key; aoValueColumnValues[i, 1] = ExcelUtil.RemoveFormulaFromValue(oKeyValuePair.Value); i++; } // Write the arrays to the columns. ExcelUtil.SetRangeValues(oNameColumnData, aoNameColumnValues); ExcelUtil.SetRangeValues(oValueColumnData, aoValueColumnValues); }
SetLocations <TLocationInfo> ( ICollection <TLocationInfo> locationInfo, System.Drawing.Rectangle graphRectangle, String xColumnName, String yColumnName, String lockedColumnName, TryGetRowIDAndLocation <TLocationInfo> tryGetRowIDAndLocation ) { Debug.Assert(locationInfo != null); Debug.Assert(!String.IsNullOrEmpty(xColumnName)); Debug.Assert(!String.IsNullOrEmpty(yColumnName)); Debug.Assert(tryGetRowIDAndLocation != null); AssertValid(); if (locationInfo.Count == 0) { return; } // Gather some required information. ListObject oTable = m_oTable.InnerObject; Range oXColumnData, oYColumnData, oLockedColumnData; Object [,] aoLockedColumnValues = null; // The key is a row ID and the value is the row's one-based row number // relative to the worksheet. Dictionary <Int32, Int32> oRowIDDictionary; if ( TryGetAllRowIDs(out oRowIDDictionary) && ExcelTableUtil.TryGetTableColumnData(oTable, xColumnName, out oXColumnData) && ExcelTableUtil.TryGetTableColumnData(oTable, yColumnName, out oYColumnData) && ( lockedColumnName == null || ExcelTableUtil.TryGetTableColumnDataAndValues(oTable, lockedColumnName, out oLockedColumnData, out aoLockedColumnValues) ) ) { // Activate this worksheet, because writing to an inactive // worksheet causes problems with the selection in Excel. ExcelActiveWorksheetRestorer oExcelActiveWorksheetRestorer = GetExcelActiveWorksheetRestorer(); ExcelActiveWorksheetState oExcelActiveWorksheetState = oExcelActiveWorksheetRestorer.ActivateWorksheet( m_oWorksheet.InnerObject); try { SetLocations <TLocationInfo>(locationInfo, graphRectangle, tryGetRowIDAndLocation, oRowIDDictionary, oXColumnData, oYColumnData, aoLockedColumnValues); } finally { oExcelActiveWorksheetRestorer.Restore( oExcelActiveWorksheetState); } } }