TryGetValuesInAllRows <TValue>
(
String sColumnName,
ExcelUtil.TryGetValueFromCell <TValue> oTryGetValueFromCell,
out Dictionary <TValue, Int32> oValueDictionary
)
{
Debug.Assert(!String.IsNullOrEmpty(sColumnName));
Debug.Assert(oTryGetValueFromCell != null);
AssertValid();
oValueDictionary = null;
if (!TableExists)
{
return(false);
}
Range oDataBodyRange = m_oTable.DataBodyRange;
if (oDataBodyRange == null)
{
return(false);
}
Range oColumnData;
Object [,] aoColumnValues;
// Get the values in the column. This includes hidden rows but
// excludes the header row.
if (!ExcelTableUtil.TryGetTableColumnDataAndValues(
m_oTable.InnerObject, sColumnName, out oColumnData,
out aoColumnValues))
{
return(false);
}
oValueDictionary = new Dictionary <TValue, Int32>();
Int32 iDataBodyRangeRow = oDataBodyRange.Row;
Int32 iRows = oColumnData.Rows.Count;
for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++)
{
TValue tValue;
if (!oTryGetValueFromCell(aoColumnValues, iRowOneBased, 1,
out tValue))
{
continue;
}
oValueDictionary[tValue] = iRowOneBased + iDataBodyRangeRow - 1;
}
return(true);
}
TryGetNullableValue <TValue>
(
Dictionary <String, Int32> oValueRowsOneBased,
Object [,] aoValues,
String sName,
ExcelUtil.TryGetValueFromCell <TValue> oTryGetValueMethod,
out Nullable <TValue> tValue
)
where TValue : struct
{
Debug.Assert(oValueRowsOneBased != null);
Debug.Assert(aoValues != null);
Debug.Assert(!String.IsNullOrEmpty(sName));
AssertValid();
tValue = null;
Int32 iRowOneBased;
if (!oValueRowsOneBased.TryGetValue(sName, out iRowOneBased))
{
// The row doesn't exist, which is an error.
return(false);
}
TValue tMightBeValue;
if (oTryGetValueMethod(aoValues, iRowOneBased, 1, out tMightBeValue))
{
// The row exists and has value of type TValue. (Otherwise, the
// value is probably
// GraphMetricCalculatorBase.NotApplicableMessage, in which case
// tValue remains null.)
tValue = tMightBeValue;
}
return(true);
}
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();
}
}
GetSelectedColumnValues <TValue>
(
String columnName,
ExcelUtil.TryGetValueFromCell <TValue> tryGetValueFromCell
)
{
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(tryGetValueFromCell != null);
AssertValid();
// Create a HashSet for the selected values. The HashSet key is the
// cell value.
HashSet <TValue> oSelectedValues = new HashSet <TValue>();
if (!this.TableExists)
{
goto Done;
}
// The selected range can extend outside the table. Get the
// intersection of the table with the selection. Note that
// ExcelUtil.TryGetSelectedTableRange() activates the worksheet.
ListObject oTable;
Range oSelectedTableRange;
if (!ExcelTableUtil.TryGetSelectedTableRange(
(Workbook)m_oWorksheet.Parent, m_oWorksheet.Name,
m_oTable.Name, out oTable, out oSelectedTableRange))
{
goto Done;
}
Range oDataBodyRange = m_oTable.DataBodyRange;
Debug.Assert(oDataBodyRange != null);
// Get data for the specified column. This includes hidden rows but
// excludes the header row.
Range oColumnData;
Object [,] aoColumnValues;
if (!ExcelTableUtil.TryGetTableColumnDataAndValues(
m_oTable.InnerObject, columnName, out oColumnData,
out aoColumnValues))
{
goto Done;
}
// Read the column.
foreach (Range oSelectedTableRangeArea in oSelectedTableRange.Areas)
{
Int32 iFirstRowOneBased =
oSelectedTableRangeArea.Row - oDataBodyRange.Row + 1;
Int32 iLastRowOneBased =
iFirstRowOneBased + oSelectedTableRangeArea.Rows.Count - 1;
for (Int32 iRowOneBased = iFirstRowOneBased;
iRowOneBased <= iLastRowOneBased; iRowOneBased++)
{
TValue tValue;
if (tryGetValueFromCell(aoColumnValues, iRowOneBased, 1,
out tValue))
{
oSelectedValues.Add(tValue);
}
}
}
Done:
return(oSelectedValues);
}
