PopulateVertexWorksheet
(
Microsoft.Office.Interop.Excel.Workbook workbook,
Boolean activateVertexWorksheetWhenDone
)
{
Debug.Assert(workbook != null);
AssertValid();
// Get the required tables.
ListObject oEdgeTable;
ListObject oVertexTable;
GetRequiredTables(workbook, out oEdgeTable, out oVertexTable);
HashSet <String> oUniqueVertexNames = new HashSet <String>();
// Populate the HashSet with unique vertex names from the edge table.
ReadEdgeTable(oEdgeTable, oUniqueVertexNames);
if (oUniqueVertexNames.Count > 0)
{
FillVertexTable(oVertexTable, oUniqueVertexNames);
}
if (activateVertexWorksheetWhenDone)
{
ExcelUtil.ActivateWorksheet(oVertexTable);
}
return(oVertexTable);
}
MergeDuplicateEdges
(
Microsoft.Office.Interop.Excel.Workbook workbook,
Boolean countDuplicateEdges,
Boolean deleteDuplicateEdges,
String thirdColumnNameForDuplicateDetection
)
{
Debug.Assert(workbook != null);
AssertValid();
ListObject oEdgeTable;
if (TryGetEdgeTable(workbook, out oEdgeTable))
{
ExcelUtil.ActivateWorksheet(oEdgeTable);
// Clear AutoFiltering, which would make this code much more
// complicated.
ExcelTableUtil.ClearTableAutoFilters(oEdgeTable);
Boolean bGraphIsDirected;
Range oVertex1NameData, oVertex2NameData;
Object [,] aoVertex1NameValues, aoVertex2NameValues,
aoThirdColumnValues;
if (TryGetInformationFromEdgeTable(workbook, oEdgeTable,
thirdColumnNameForDuplicateDetection,
out bGraphIsDirected,
out oVertex1NameData, out aoVertex1NameValues,
out oVertex2NameData, out aoVertex2NameValues,
out aoThirdColumnValues
))
{
if (countDuplicateEdges)
{
CountDuplicateEdges(oEdgeTable, aoVertex1NameValues,
aoVertex2NameValues, aoThirdColumnValues,
bGraphIsDirected);
}
if (deleteDuplicateEdges)
{
DeleteDuplicateEdges(oEdgeTable, aoVertex1NameValues,
aoVertex2NameValues, aoThirdColumnValues,
bGraphIsDirected);
}
}
}
}
ActivateWorksheet
(
Worksheet worksheet
)
{
Debug.Assert(worksheet != null);
AssertValid();
ExcelActiveWorksheetState oExcelActiveWorksheetState =
GetActiveWorksheetState();
ExcelUtil.ActivateWorksheet(worksheet);
return (oExcelActiveWorksheetState);
}
Restore
(
ExcelActiveWorksheetState excelActiveWorksheetState
)
{
Debug.Assert(excelActiveWorksheetState != null);
AssertValid();
Worksheet oWorksheetToActivate =
excelActiveWorksheetState.ActiveWorksheet;
if (oWorksheetToActivate != null)
{
ExcelUtil.ActivateWorksheet(oWorksheetToActivate);
}
m_oWorkbook.Application.ScreenUpdating =
excelActiveWorksheetState.ScreenUpdating;
}
PopulateVertexWorksheet
(
Microsoft.Office.Interop.Excel.Workbook workbook,
Boolean activateVertexWorksheetWhenDone
)
{
Debug.Assert(workbook != null);
AssertValid();
// Get the required tables.
ListObject oEdgeTable;
ListObject oVertexTable;
GetRequiredTables(workbook, out oEdgeTable, out oVertexTable);
// Create a dictionary to keep track of vertices. The key is the
// vertex name and the value is unused.
Dictionary <String, Char> oVertexNameDictionary =
new Dictionary <String, Char>();
// Populate the dictionary with unique vertex names from the edge
// table.
ReadEdgeTable(oEdgeTable, oVertexNameDictionary);
if (oVertexNameDictionary.Count > 0)
{
// Fill the vertex table with the unique vertex names.
FillVertexTable(oVertexTable, oVertexNameDictionary);
}
if (activateVertexWorksheetWhenDone)
{
ExcelUtil.ActivateWorksheet(oVertexTable);
}
return(oVertexTable);
}
ShowOrHideColumnGroup
(
Microsoft.Office.Interop.Excel.Workbook workbook,
ColumnGroup columnGroup,
Boolean show,
Boolean activateWorksheet
)
{
Debug.Assert(workbook != null);
ListObject oTable;
if (TryGetColumnGroupTable(workbook, columnGroup, out oTable))
{
ExcelColumnHider.ShowOrHideColumns(oTable,
GetColumnNames(workbook, columnGroup), show);
if (columnGroup == ColumnGroup.VertexOtherColumns)
{
// Hiding the subgraph image column doesn't hide the images in
// the column.
TableImagePopulator.ShowOrHideImagesInColumn(workbook,
WorksheetNames.Vertices,
VertexTableColumnNames.SubgraphImage, show);
}
if (activateWorksheet)
{
ExcelUtil.ActivateWorksheet(oTable);
}
(new PerWorkbookSettings(workbook)).SetColumnGroupVisibility(
columnGroup, show);
}
}
ActivateRelevantWorksheet
(
IEnumerable <GraphMetricColumn> graphMetricColumns,
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(graphMetricColumns != null);
Debug.Assert(workbook != null);
AssertValid();
Boolean bEdgeMetricCalculated = false;
Boolean bVertexMetricCalculated = false;
Boolean bOverallMetricsCalculated = false;
Boolean bGroupsCalculated = false;
Boolean bGroupMetricCalculated = false;
Boolean bWordMetricCalculated = false;
Boolean bTopNByMetricCalculated = false;
Boolean bShowGraphMetricColumnGroups = false;
foreach (GraphMetricColumn oGraphMetricColumn in graphMetricColumns)
{
switch (oGraphMetricColumn.WorksheetName)
{
case WorksheetNames.Edges:
bEdgeMetricCalculated = true;
bShowGraphMetricColumnGroups = true;
break;
case WorksheetNames.Vertices:
bVertexMetricCalculated = true;
bShowGraphMetricColumnGroups = true;
break;
case WorksheetNames.Groups:
if (oGraphMetricColumn.ColumnName ==
GroupTableColumnNames.Name)
{
bGroupsCalculated = true;
}
else if (oGraphMetricColumn.ColumnName ==
GroupTableColumnNames.Vertices)
{
bGroupMetricCalculated = true;
bShowGraphMetricColumnGroups = true;
}
break;
case WorksheetNames.OverallMetrics:
bOverallMetricsCalculated = true;
break;
case WorksheetNames.WordCounts:
case WorksheetNames.WordPairCounts:
bWordMetricCalculated = true;
break;
case WorksheetNames.TopNByMetrics:
bTopNByMetricCalculated = true;
break;
default:
break;
}
}
if (bShowGraphMetricColumnGroups)
{
ColumnGroupManager.ShowOrHideColumnGroups(workbook,
ColumnGroups.EdgeGraphMetrics |
ColumnGroups.VertexGraphMetrics |
ColumnGroups.GroupGraphMetrics |
ColumnGroups.GroupEdgeGraphMetrics,
true, true);
}
// The worksheet to activate should be the one corresponding to the
// bottommost checked item in the GraphMetricsDialog.
String sWorksheetNameToActivate = null;
if (bTopNByMetricCalculated)
{
sWorksheetNameToActivate = WorksheetNames.TopNByMetrics;
}
else if (bWordMetricCalculated)
{
sWorksheetNameToActivate = WorksheetNames.WordCounts;
}
else if (bOverallMetricsCalculated)
{
sWorksheetNameToActivate = WorksheetNames.OverallMetrics;
}
else if (bGroupsCalculated || bGroupMetricCalculated)
{
sWorksheetNameToActivate = WorksheetNames.Groups;
}
else if (bEdgeMetricCalculated)
{
sWorksheetNameToActivate = WorksheetNames.Edges;
}
else if (bVertexMetricCalculated)
{
sWorksheetNameToActivate = WorksheetNames.Vertices;
}
Worksheet oWorksheetToActivate;
if (
sWorksheetNameToActivate != null
&&
ExcelUtil.TryGetWorksheet(workbook, sWorksheetNameToActivate,
out oWorksheetToActivate)
)
{
ExcelUtil.ActivateWorksheet(oWorksheetToActivate);
}
}
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);
}
}
TryAddSelectedVerticesToGroup
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
Sheet2 oVertexWorksheet
)
{
Debug.Assert(oWorkbook != null);
Debug.Assert(oVertexWorksheet != null);
AddSelectedVerticesToGroupDialog oAddSelectedVerticesToGroupDialog =
new AddSelectedVerticesToGroupDialog(oWorkbook);
if (oAddSelectedVerticesToGroupDialog.ShowDialog() !=
DialogResult.OK)
{
return(false);
}
// First, remove the selected vertices from any groups they belong to.
ListObject oGroupTable, oGroupVertexTable;
ICollection <String> oSelectedVertexNames;
if (
!TryRemoveSelectedVerticesFromGroups(oWorkbook, oVertexWorksheet,
out oSelectedVertexNames)
||
!ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Groups,
TableNames.Groups, out oGroupTable)
||
!ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.GroupVertices,
TableNames.GroupVertices, out oGroupVertexTable)
)
{
return(false);
}
String sGroupName = oAddSelectedVerticesToGroupDialog.GroupName;
if (oAddSelectedVerticesToGroupDialog.IsNewGroup)
{
// Add the new group to the group table.
//
// Note that the group table (and the group-vertex table, below)
// needs to be activated before being written to. If this isn't
// done, the formula written to the group-vertex table below also
// mysteriously appears in the vertex table on the vertex
// worksheet.
//
// It's up to the caller to use the ExcelActiveWorksheetRestorer
// class to save and restore the active worksheet.
ExcelUtil.ActivateWorksheet(oGroupTable);
ExcelTableUtil.AddTableRow(oGroupTable,
GroupTableColumnNames.Name,
sGroupName
);
SetVertexAttributesForAllGroups(oGroupTable);
}
// Add the selected vertices to the group-vertex table.
ExcelUtil.ActivateWorksheet(oGroupVertexTable);
foreach (String sSelectedVertexName in oSelectedVertexNames)
{
ExcelTableUtil.AddTableRow(oGroupVertexTable,
GroupVertexTableColumnNames.GroupName,
sGroupName,
GroupVertexTableColumnNames.VertexName,
sSelectedVertexName,
GroupVertexTableColumnNames.VertexID,
GetExcelFormulaForVertexID()
);
}
return(true);
}
AddColumnPair
(
Microsoft.Office.Interop.Excel.Workbook workbook,
String worksheetName,
String tableName,
String column1NameBase,
Single column1WidthChars,
String column2NameBase,
Single column2WidthChars
)
{
Debug.Assert(workbook != null);
Debug.Assert(!String.IsNullOrEmpty(worksheetName));
Debug.Assert(!String.IsNullOrEmpty(tableName));
Debug.Assert(!String.IsNullOrEmpty(column1NameBase));
Debug.Assert(column1WidthChars > 0);
Debug.Assert(!String.IsNullOrEmpty(column2NameBase));
Debug.Assert(column2WidthChars > 0);
AssertValid();
ListObject oTable;
if (!ExcelUtil.TryGetTable(workbook, worksheetName, tableName,
out oTable))
{
throw new WorkbookFormatException(String.Format(
"To use this feature, there must be a worksheet named \"{0}\""
+ " that contains a table named \"{1}\"."
+ "\r\n\r\n"
+ "{2}"
,
worksheetName,
tableName,
ErrorUtil.GetTemplateMessage()
));
}
Int32 iMaximumAppendedNumber = Math.Max(
GetMaximumAppendedNumber(oTable, column1NameBase),
GetMaximumAppendedNumber(oTable, column2NameBase)
);
if (iMaximumAppendedNumber != 0)
{
String sStringToAppend =
" " + (iMaximumAppendedNumber + 1).ToString();
column1NameBase += sStringToAppend;
column2NameBase += sStringToAppend;
}
ListColumn oListColumn1, oListColumn2;
if (
!ExcelUtil.TryAddTableColumn(oTable, column1NameBase,
column1WidthChars, null, out oListColumn1)
||
!ExcelUtil.TryAddTableColumn(oTable, column2NameBase,
column2WidthChars, null, out oListColumn2)
)
{
FormUtil.ShowWarning("The columns weren't added.");
}
ExcelUtil.ActivateWorksheet(oTable);
}
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);
}
MergeDuplicateEdges
(
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(workbook != null);
AssertValid();
ListObject oEdgeTable;
if (
!ExcelUtil.TryGetTable(workbook, WorksheetNames.Edges,
TableNames.Edges, out oEdgeTable)
||
oEdgeTable.DataBodyRange == null
)
{
// (The DataBodyRange test catches the odd case where the user
// deletes the first data row of the table. It looks like the row
// is still there, but it's not. Continuing with a null
// DataBodyRange can cause a variety of problems.)
return;
}
ExcelUtil.ActivateWorksheet(oEdgeTable);
// Clear AutoFiltering, which would make this code much more
// complicated.
ExcelUtil.ClearTableAutoFilters(oEdgeTable);
// Get the vertex name data.
Range oVertex1NameData, oVertex2NameData;
Object [,] aoVertex1NameValues, aoVertex2NameValues;
if (
!ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable,
EdgeTableColumnNames.Vertex1Name, out oVertex1NameData,
out aoVertex1NameValues)
||
!ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable,
EdgeTableColumnNames.Vertex2Name, out oVertex2NameData,
out aoVertex2NameValues)
)
{
return;
}
// Add an edge weight column if it doesn't already exist.
ListColumn oEdgeWeightColumn;
if (
!ExcelUtil.TryGetTableColumn(oEdgeTable,
EdgeTableColumnNames.EdgeWeight, out oEdgeWeightColumn)
&&
!ExcelUtil.TryAddTableColumn(oEdgeTable,
EdgeTableColumnNames.EdgeWeight, ExcelUtil.AutoColumnWidth,
null, out oEdgeWeightColumn)
)
{
return;
}
// Get the edge weight data.
Range oEdgeWeightData;
Object [,] aoEdgeWeightValues;
if (!ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable,
EdgeTableColumnNames.EdgeWeight, out oEdgeWeightData,
out aoEdgeWeightValues))
{
return;
}
// Determine whether the graph is directed.
PerWorkbookSettings oPerWorkbookSettings =
new PerWorkbookSettings(workbook);
Boolean bGraphIsDirected = (oPerWorkbookSettings.GraphDirectedness ==
GraphDirectedness.Directed);
// Now that the required information has been gathered, do the actual
// merge.
MergeDuplicateEdges(oEdgeTable, oVertex1NameData, aoVertex1NameValues,
oVertex2NameData, aoVertex2NameValues, oEdgeWeightColumn,
oEdgeWeightData, aoEdgeWeightValues, bGraphIsDirected);
oEdgeTable.HeaderRowRange.Select();
}
ActivateRelevantWorksheet
(
IEnumerable <GraphMetricColumn> graphMetricColumns,
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(graphMetricColumns != null);
Debug.Assert(workbook != null);
AssertValid();
Boolean bVertexMetricCalculated = false;
Boolean bOverallMetricsCalculated = false;
Boolean bGroupsCalculated = false;
Boolean bGroupMetricCalculated = false;
foreach (GraphMetricColumn oGraphMetricColumn in graphMetricColumns)
{
switch (oGraphMetricColumn.WorksheetName)
{
case WorksheetNames.Vertices:
bVertexMetricCalculated = true;
break;
case WorksheetNames.OverallMetrics:
bOverallMetricsCalculated = true;
break;
case WorksheetNames.Groups:
if (oGraphMetricColumn.ColumnName ==
GroupTableColumnNames.Name)
{
bGroupsCalculated = true;
}
else if (oGraphMetricColumn.ColumnName ==
GroupTableColumnNames.Vertices)
{
bGroupMetricCalculated = true;
}
break;
default:
break;
}
}
String sRelevantWorksheetName = null;
if (bVertexMetricCalculated)
{
sRelevantWorksheetName = WorksheetNames.Vertices;
}
else if (bGroupsCalculated || bGroupMetricCalculated)
{
sRelevantWorksheetName = WorksheetNames.Groups;
}
else if (bOverallMetricsCalculated)
{
sRelevantWorksheetName = WorksheetNames.OverallMetrics;
}
if (bVertexMetricCalculated || bGroupMetricCalculated)
{
ColumnGroupManager.ShowOrHideColumnGroup(workbook,
ColumnGroup.VertexGraphMetrics, true, false);
ColumnGroupManager.ShowOrHideColumnGroup(workbook,
ColumnGroup.GroupGraphMetrics, true, false);
}
Worksheet oRelevantWorksheet;
if (sRelevantWorksheetName != null && ExcelUtil.TryGetWorksheet(
workbook, sRelevantWorksheetName, out oRelevantWorksheet))
{
ExcelUtil.ActivateWorksheet(oRelevantWorksheet);
}
}
SelectTableRowsByColumnValues <TValue>
(
String columnName,
ICollection <TValue> valuesToSelect,
ExcelUtil.TryGetValueFromCell <TValue> tryGetValueFromCell
)
{
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(valuesToSelect != null);
Debug.Assert(tryGetValueFromCell != null);
AssertValid();
if (!TableExists)
{
return;
}
ExcelUtil.ActivateWorksheet(m_oWorksheet.InnerObject);
Int32 iValuesToSelect = valuesToSelect.Count;
if (iValuesToSelect == 0)
{
// Unselect any currently selected rows.
m_oTable.HeaderRowRange.Select();
return;
}
// Get a dictionary containing the value of each row in the table. The
// key is the value from the specified column and the value is the
// one-based row number relative to the worksheet.
Dictionary <TValue, Int32> oValueDictionary;
if (!TryGetValuesInAllRows <TValue>(columnName, tryGetValueFromCell,
out oValueDictionary))
{
return;
}
// Build a range address string that is the union of the rows to
// select. Sample: "3:3,6:6,12:12". Excel allows this for an address
// up to MaximumBuiltRangeAddressLength characters. (This was
// determined experimentally.) Building a union via a string address
// is much more efficient than creating one range per row and using
// Application.Union() on all of them.
StringBuilder oBuiltRangeAddress = new StringBuilder();
const Int32 MaximumBuiltRangeAddressLength = 250;
Range oAccumulatedRange = null;
// The ExcelLocale1033(true) attribute in AssemblyInfo.cs is supposed
// to make the Excel object model act the same in all locales, so a
// hard-coded comma should always work as the list separator for a
// union range address. That's not the case, though; Excel uses the
// locale-specified list separator instead. Is this a bug in the Excel
// PIAs? Here is a posting from someone else who found the same
// problem:
//
// http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/
// 0e4bd7dc-37d3-42ea-9ce4-53b9e5a53719/
String sListSeparator =
CultureInfo.CurrentCulture.TextInfo.ListSeparator;
Int32 i = 0;
foreach (TValue tValueToSelect in valuesToSelect)
{
Int32 iRow;
if (oValueDictionary.TryGetValue(tValueToSelect, out iRow))
{
if (oBuiltRangeAddress.Length != 0)
{
oBuiltRangeAddress.Append(sListSeparator);
}
oBuiltRangeAddress.Append(String.Format(
"{0}:{0}",
iRow
));
}
// In the following test, assume that the next appended address
// would be ",1048576:1048576".
Int32 iBuiltRangeAddressLength = oBuiltRangeAddress.Length;
if (
iBuiltRangeAddressLength + 1 + 7 + 1 + 7 >
MaximumBuiltRangeAddressLength
||
(i == iValuesToSelect - 1 && iBuiltRangeAddressLength > 0)
)
{
// Get the range specified by the StringBuilder.
Range oBuiltRange = m_oWorksheet.InnerObject.get_Range(
oBuiltRangeAddress.ToString(), Missing.Value);
Debug.Assert(oBuiltRange != null);
// Add it to the total.
if (!ExcelUtil.TryUnionRanges(
oAccumulatedRange, oBuiltRange, out oAccumulatedRange))
{
Debug.Assert(false);
}
oBuiltRangeAddress.Length = 0;
}
i++;
}
// Intersect the accumulated range with the table and select the
// intersection.
Range oRangeToSelect;
if (ExcelUtil.TryIntersectRanges(oAccumulatedRange,
m_oTable.DataBodyRange, out oRangeToSelect))
{
oRangeToSelect.Select();
}
}