SetVisualAttribute
(
SetVisualAttributeEventArgs e,
Range selectedRange,
String colorColumnName,
String alphaColumnName
)
{
Debug.Assert(e != null);
Debug.Assert(selectedRange != null);
Debug.Assert(ExcelUtil.WorksheetIsActive(m_oWorksheet.InnerObject));
AssertValid();
if (e.VisualAttribute == VisualAttributes.Color &&
colorColumnName != null)
{
String sColor;
// Get a color from the user.
if (NodeXLWorkbookUtil.TryGetColor(out sColor))
{
ExcelUtil.SetVisibleSelectedTableColumnData(
m_oTable.InnerObject, selectedRange, colorColumnName,
sColor);
e.VisualAttributeSet = true;
}
}
}
TryRemoveAllGroups
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook
)
{
Debug.Assert(oWorkbook != null);
NodeXLWorkbookUtil.ClearGroupTables(oWorkbook);
return(true);
}
TrySelectGroupsWithSelectedVertices
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
Sheet2 oVertexWorksheet,
Sheet5 oGroupWorksheet
)
{
Debug.Assert(oWorkbook != null);
Debug.Assert(oVertexWorksheet != null);
Debug.Assert(oGroupWorksheet != null);
// For each selected vertex, get the vertex's group name from the
// group-vertex worksheet and select that group in the group worksheet.
ICollection <String> oSelectedVertexNames =
oVertexWorksheet.GetSelectedVertexNames();
oGroupWorksheet.SelectGroups(
NodeXLWorkbookUtil.GetGroupNamesByVertexName(oWorkbook,
oSelectedVertexNames));
return(true);
}
ImportEdgeWorkbook
(
String sourceWorkbookName,
ICollection <Int32> oneBasedColumnNumbersToImport,
Int32 columnNumberToUseForVertex1OneBased,
Int32 columnNumberToUseForVertex2OneBased,
Boolean sourceColumnsHaveHeaders,
Boolean clearDestinationTablesFirst,
Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook
)
{
Debug.Assert(!String.IsNullOrEmpty(sourceWorkbookName));
Debug.Assert(oneBasedColumnNumbersToImport != null);
Debug.Assert(columnNumberToUseForVertex1OneBased >= 1);
Debug.Assert(columnNumberToUseForVertex2OneBased >= 1);
Debug.Assert(destinationNodeXLWorkbook != null);
AssertValid();
// Get the active worksheet of the source workbook.
Application oApplication = destinationNodeXLWorkbook.Application;
Worksheet oSourceWorksheet = GetActiveSourceWorksheet(
oApplication, sourceWorkbookName);
// Get the boundaries of the non-empty range in the source worksheet.
Range oNonEmptySourceRange = GetNonEmptySourceRange(oSourceWorksheet);
Int32 iFirstNonEmptySourceRowOneBased = oNonEmptySourceRange.Row;
Int32 iLastNonEmptySourceRowOneBased = oNonEmptySourceRange.Rows.Count
+ iFirstNonEmptySourceRowOneBased - 1;
// Get the edge table in the destination NodeXL workbook.
ListObject oDestinationEdgeTable = GetDestinationEdgeTable(
destinationNodeXLWorkbook);
ExcelUtil.ActivateWorksheet(oDestinationEdgeTable);
Int32 iDestinationRowOffset = 0;
if (clearDestinationTablesFirst)
{
NodeXLWorkbookUtil.ClearAllNodeXLTables(destinationNodeXLWorkbook);
}
else
{
iDestinationRowOffset =
ExcelUtil.GetOffsetOfFirstEmptyTableRow(oDestinationEdgeTable);
}
// Copy the vertex columns to the destination NodeXL workbook.
CopyVertexColumn(oSourceWorksheet, columnNumberToUseForVertex1OneBased,
sourceColumnsHaveHeaders, iFirstNonEmptySourceRowOneBased,
iLastNonEmptySourceRowOneBased, oDestinationEdgeTable,
iDestinationRowOffset, true);
CopyVertexColumn(oSourceWorksheet, columnNumberToUseForVertex2OneBased,
sourceColumnsHaveHeaders, iFirstNonEmptySourceRowOneBased,
iLastNonEmptySourceRowOneBased, oDestinationEdgeTable,
iDestinationRowOffset, false);
// Copy the other columns.
CopyOtherColumns(oSourceWorksheet, oneBasedColumnNumbersToImport,
columnNumberToUseForVertex1OneBased,
columnNumberToUseForVertex2OneBased, sourceColumnsHaveHeaders,
iFirstNonEmptySourceRowOneBased, iLastNonEmptySourceRowOneBased,
iDestinationRowOffset, oDestinationEdgeTable);
// Clear the moving border and selection. The odd cast is to work
// around the inability to set CutCopyMode to false.
oApplication.CutCopyMode = (XlCutCopyMode)0;
Range oHeaderRowRange = oDestinationEdgeTable.HeaderRowRange;
if (oHeaderRowRange != null)
{
ExcelUtil.SelectRange(oHeaderRowRange);
}
}
ImportGraph
(
IGraph sourceGraph,
String [] edgeAttributes,
String [] vertexAttributes,
Boolean clearTablesFirst,
Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook
)
{
Debug.Assert(sourceGraph != null);
Debug.Assert(destinationNodeXLWorkbook != null);
AssertValid();
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 (
!ExcelUtil.TryGetTableColumnData(oEdgeTable,
EdgeTableColumnNames.Vertex1Name, out oVertex1NameColumnData)
||
!ExcelUtil.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 (
!ExcelUtil.TryGetTable(destinationNodeXLWorkbook,
WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable)
||
!ExcelUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameColumnData)
||
!ExcelUtil.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);
}
WriteToDestinationWorkbook
(
LinkedList <String> oVertex1Names,
LinkedList <String> oVertex2Names,
LinkedList <Double> oEdgeWeights,
Boolean bClearDestinationTablesFirst,
Microsoft.Office.Interop.Excel.Workbook oDestinationNodeXLWorkbook
)
{
Debug.Assert(oVertex1Names != null);
Debug.Assert(oVertex2Names != null);
Debug.Assert(oEdgeWeights != null);
Debug.Assert(oVertex2Names.Count == oVertex1Names.Count);
Debug.Assert(oEdgeWeights.Count == oVertex1Names.Count);
Debug.Assert(oDestinationNodeXLWorkbook != null);
AssertValid();
Int32 iVertexNames = oVertex1Names.Count;
ListObject oDestinationEdgeTable = GetDestinationEdgeTable(
oDestinationNodeXLWorkbook);
ExcelUtil.ActivateWorksheet(oDestinationEdgeTable);
Int32 iRowOffsetToWriteTo = 0;
if (bClearDestinationTablesFirst)
{
NodeXLWorkbookUtil.ClearAllNodeXLTables(oDestinationNodeXLWorkbook);
}
else
{
iRowOffsetToWriteTo =
ExcelUtil.GetOffsetOfFirstEmptyTableRow(oDestinationEdgeTable);
}
// Create an array to hold the vertex 1 names and write them to the
// edge table. Repeat for the vertex 2 names.
String [,] asVertexNames =
ExcelUtil.GetSingleColumn2DStringArray(iVertexNames);
Int32 iRowOneBased;
foreach (Boolean bVertex1 in new Boolean [] { true, false })
{
iRowOneBased = 1;
foreach (String sVertexName in
bVertex1 ? oVertex1Names: oVertex2Names)
{
// VertexWorksheetPopulator.PopulateVertexWorksheet() writes
// strings to the vertex column of the vertex table, so the
// values must all be strings.
asVertexNames[iRowOneBased, 1] = sVertexName;
iRowOneBased++;
}
Range oVertexColumnData = GetVertexColumnData(
oDestinationEdgeTable, bVertex1);
ExcelUtil.OffsetRange(ref oVertexColumnData, iRowOffsetToWriteTo,
0);
ExcelUtil.SetRangeValues(oVertexColumnData, asVertexNames);
}
asVertexNames = null;
// Create an array to hold the edge weights and write them to the edge
// table.
Object [,] aoEdgeWeights =
ExcelUtil.GetSingleColumn2DArray(iVertexNames);
iRowOneBased = 1;
foreach (Double dEdgeWeight in oEdgeWeights)
{
aoEdgeWeights[iRowOneBased, 1] = dEdgeWeight;
iRowOneBased++;
}
ListColumn oEdgeWeightColumn;
Range oEdgeWeightColumnData = null;
if (
!ExcelUtil.TryGetOrAddTableColumn(oDestinationEdgeTable,
EdgeTableColumnNames.EdgeWeight, ExcelUtil.AutoColumnWidth,
null, out oEdgeWeightColumn)
||
!ExcelUtil.TryGetTableColumnData(oDestinationEdgeTable,
EdgeTableColumnNames.EdgeWeight, out oEdgeWeightColumnData)
)
{
OnInvalidSourceWorkbook(String.Format(
"An {0} column couldn't be added to the edge worksheet."
,
EdgeTableColumnNames.EdgeWeight
));
}
ExcelUtil.OffsetRange(ref oEdgeWeightColumnData, iRowOffsetToWriteTo,
0);
ExcelUtil.SetRangeValues(oEdgeWeightColumnData, aoEdgeWeights);
}
OnGroupTableSelectionChange()
{
AssertValid();
SheetHelper oSheetHelper = m_oGroupWorksheet.SheetHelper;
if (IgnoreTableSelectionChange(oSheetHelper))
{
return;
}
// Enable the "set visual attribute" buttons in the Ribbon.
m_oThisWorkbook.EnableSetVisualAttributes();
LinkedList <Int32> oVertexRowIDsToSelect = new LinkedList <Int32>();
LinkedList <String> oCollapsedGroupNamesToSelect =
new LinkedList <String>();
foreach (String sSelectedGroupName in
oSheetHelper.GetSelectedStringColumnValues(
GroupTableColumnNames.Name))
{
if (m_oTaskPane.IsCollapsedGroup(sSelectedGroupName))
{
oCollapsedGroupNamesToSelect.AddLast(sSelectedGroupName);
}
else
{
foreach (Int32 iVertexIDInGroup in
NodeXLWorkbookUtil.GetVertexIDsInGroup(
m_oThisWorkbook.InnerObject, sSelectedGroupName))
{
oVertexRowIDsToSelect.AddLast(iVertexIDInGroup);
}
}
}
m_bIgnoreSelectionEvents = true;
// Select the vertices in the graph, then defer the selection of the
// corresponding rows in the edge and vertex worksheets until those
// worksheets are activated.
m_oTaskPane.SetSelectedVerticesByRowID(oVertexRowIDsToSelect);
m_bUpdateEdgeSelectionOnActivation = true;
m_bUpdateVertexSelectionOnActivation = true;
// Select the vertices that represent collapsed groups. This has to be
// done after selecting the other vertices, because selecting the other
// vertices clears the selection.
foreach (String sCollapsedSelectedGroupName in
oCollapsedGroupNamesToSelect)
{
m_oTaskPane.SelectCollapsedGroup(sCollapsedSelectedGroupName);
}
m_bIgnoreSelectionEvents = false;
}
AutomateFolder
(
String folderToAutomate,
AutomationTasks tasksToRun,
Microsoft.Office.Interop.Excel.Application application
)
{
Debug.Assert(!String.IsNullOrEmpty(folderToAutomate));
Debug.Assert(application != null);
foreach (String sFileName in Directory.GetFiles(folderToAutomate,
"*.xlsx"))
{
String sFilePath = Path.Combine(folderToAutomate, sFileName);
try
{
if (!NodeXLWorkbookUtil.FileIsNodeXLWorkbook(sFilePath))
{
continue;
}
}
catch (IOException)
{
// Skip any workbooks that are already open, or that have any
// other problems that prevent them from being opened.
continue;
}
// Ideally, the Excel API would be used here to open the workbook
// and run the AutomateThisWorkbook() method on it. Two things
// make that impossible:
//
// 1. When you open a workbook using
// Application.Workbooks.Open(), you get only a native Excel
// workbook, not an "extended" ThisWorkbook object or its
// associated Ribbon object. AutomateThisWorkbook() requires
// a Ribbon object.
//
// Although a GetVstoObject() extension method is available to
// convert a native Excel workbook to an extended workbook,
// that method doesn't work on a native workbook opened via
// the Excel API -- it always returns null.
//
// It might be possible to refactor AutomateThisWorkbook() to
// require only a native workbook. However, problem 2 would
// still make things impossible...
//
// 2. If this method is being run from a modal dialog, which it
// is (see AutomateTasksDialog), then code in the workbook
// that needs to be automated doesn't run until the modal
// dialog closes.
//
// The following code works around these problems.
try
{
// Store an "automate tasks on open" flag in the workbook,
// indicating that task automation should be run on it the next
// time it's opened. This can be done via the Excel API.
Microsoft.Office.Interop.Excel.Workbook oWorkbookToAutomate =
ExcelUtil.OpenWorkbook(sFilePath, application);
PerWorkbookSettings oPerWorkbookSettings =
new PerWorkbookSettings(oWorkbookToAutomate);
oPerWorkbookSettings.AutomateTasksOnOpen = true;
oWorkbookToAutomate.Save();
oWorkbookToAutomate.Close(false, Missing.Value, Missing.Value);
// Now open the workbook in another instance of Excel, which
// bypasses problem 2. Code in the workbook's Ribbon will
// detect the flag's presence, run task automation on it, close
// the workbook, and close the other instance of Excel.
OpenWorkbookToAutomate(sFilePath);
}
catch (Exception oException)
{
ErrorUtil.OnException(oException);
return;
}
}
}
