Open()
{
Debug.Assert(m_oDynamicFilterSettingsTable == null);
AssertValid();
// Get the table that contains the dynamic filter settings.
if (!ExcelUtil.TryGetTable(m_oWorkbook, WorksheetNames.Miscellaneous,
TableNames.DynamicFilterSettings,
out m_oDynamicFilterSettingsTable))
{
OnWorkbookFormatError(String.Format(
"A table that is required to use this feature is missing."
+ "\r\n\r\n{0}"
,
ErrorUtil.GetTemplateMessage()
));
}
// Read the table.
Range oDataBodyRange;
if (!ExcelUtil.TryGetVisibleTableRange(
m_oDynamicFilterSettingsTable, out oDataBodyRange))
{
// The table is empty. This is not an error.
return;
}
// The table is hidden, so no one should ever be able to filter its
// rows.
Debug.Assert(oDataBodyRange.Areas.Count == 1);
// Get the indexes of the columns within the table.
DynamicFilterSettingsTableColumnIndexes
oDynamicFilterSettingsTableColumnIndexes =
GetDynamicFilterSettingsTableColumnIndexes(
m_oDynamicFilterSettingsTable);
// The table contains one row per dynamic filter. Loop through the
// rows.
Int32 iRows = oDataBodyRange.Rows.Count;
Object [,] aoValues = ExcelUtil.GetRangeValues(oDataBodyRange);
Range oSelectedMinimumCell =
((Range)oDataBodyRange.Cells[1, 1]).get_Offset(0,
oDynamicFilterSettingsTableColumnIndexes.SelectedMinimum - 1);
Range oSelectedMaximumCell =
((Range)oDataBodyRange.Cells[1, 1]).get_Offset(0,
oDynamicFilterSettingsTableColumnIndexes.SelectedMaximum - 1);
for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++)
{
String sTableName, sColumnName;
Double dSelectedMinimum, dSelectedMaximum;
if (
ExcelUtil.TryGetNonEmptyStringFromCell(aoValues, iRowOneBased,
oDynamicFilterSettingsTableColumnIndexes.TableName,
out sTableName)
&&
ExcelUtil.TryGetNonEmptyStringFromCell(aoValues, iRowOneBased,
oDynamicFilterSettingsTableColumnIndexes.ColumnName, false,
out sColumnName)
&&
ExcelUtil.TryGetDoubleFromCell(aoValues, iRowOneBased,
oDynamicFilterSettingsTableColumnIndexes.SelectedMinimum,
out dSelectedMinimum)
&&
ExcelUtil.TryGetDoubleFromCell(aoValues, iRowOneBased,
oDynamicFilterSettingsTableColumnIndexes.SelectedMaximum,
out dSelectedMaximum)
)
{
// Create a SettingsForOneFilter object for each filter and
// store it in a dictionary.
SettingsForOneFilter oSettingsForOneFilter =
new SettingsForOneFilter();
oSettingsForOneFilter.SelectedMinimum =
(Decimal)dSelectedMinimum;
oSettingsForOneFilter.SelectedMaximum =
(Decimal)dSelectedMaximum;
oSettingsForOneFilter.SelectedMinimumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMinimumCell);
oSettingsForOneFilter.SelectedMaximumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMaximumCell);
m_oDynamicFilterSettingsDictionary.Add(
GetDictionaryKey(sTableName, sColumnName),
oSettingsForOneFilter);
}
oSelectedMinimumCell = oSelectedMinimumCell.get_Offset(1, 0);
oSelectedMaximumCell = oSelectedMaximumCell.get_Offset(1, 0);
}
}
SetSettings
(
String tableName,
String columnName,
Decimal selectedMinimum,
Decimal selectedMaximum,
out String selectedMinimumAddress,
out String selectedMaximumAddress
)
{
Debug.Assert(!String.IsNullOrEmpty(tableName));
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(selectedMaximum >= selectedMinimum);
AssertValid();
CheckOpen();
selectedMinimumAddress = selectedMaximumAddress = null;
SettingsForOneFilter oSettingsForOneFilter;
String sDictionaryKey = GetDictionaryKey(tableName, columnName);
Range oSelectedMinimumCell, oSelectedMaximumCell;
if (m_oDynamicFilterSettingsDictionary.TryGetValue(
sDictionaryKey, out oSettingsForOneFilter))
{
// The settings are already in the table. Update the table row.
Debug.Assert(m_oDynamicFilterSettingsTable.Parent is Worksheet);
Worksheet oWorksheet =
(Worksheet)m_oDynamicFilterSettingsTable.Parent;
oSelectedMinimumCell = oWorksheet.get_Range(
oSettingsForOneFilter.SelectedMinimumAddress, Missing.Value);
oSelectedMinimumCell.set_Value(Missing.Value, selectedMinimum);
oSelectedMaximumCell = oWorksheet.get_Range(
oSettingsForOneFilter.SelectedMaximumAddress, Missing.Value);
oSelectedMaximumCell.set_Value(Missing.Value, selectedMaximum);
// Update the dictionary entry.
oSettingsForOneFilter.SelectedMinimum = selectedMinimum;
oSettingsForOneFilter.SelectedMaximum = selectedMaximum;
}
else
{
// The settings aren't in the table yet. Add a row to the table.
Range oNewRow = m_oDynamicFilterSettingsTable.ListRows.Add(
Missing.Value).Range;
// Get the indexes of the columns within the table.
DynamicFilterSettingsTableColumnIndexes
oDynamicFilterSettingsTableColumnIndexes =
GetDynamicFilterSettingsTableColumnIndexes(
m_oDynamicFilterSettingsTable);
// Set the values of the row's cells.
((Range)oNewRow.Cells[1,
oDynamicFilterSettingsTableColumnIndexes.TableName]).
set_Value(Missing.Value, tableName);
((Range)oNewRow.Cells[1,
oDynamicFilterSettingsTableColumnIndexes.ColumnName]).
set_Value(Missing.Value, columnName);
oSelectedMinimumCell = (Range)oNewRow.Cells[1,
oDynamicFilterSettingsTableColumnIndexes.SelectedMinimum];
oSelectedMaximumCell = (Range)oNewRow.Cells[1,
oDynamicFilterSettingsTableColumnIndexes.SelectedMaximum];
oSelectedMinimumCell.set_Value(Missing.Value, selectedMinimum);
oSelectedMaximumCell.set_Value(Missing.Value, selectedMaximum);
// Add a dictionary entry.
oSettingsForOneFilter = new SettingsForOneFilter();
oSettingsForOneFilter.SelectedMinimum = selectedMinimum;
oSettingsForOneFilter.SelectedMaximum = selectedMaximum;
oSettingsForOneFilter.SelectedMinimumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMinimumCell);
oSettingsForOneFilter.SelectedMaximumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMaximumCell);
m_oDynamicFilterSettingsDictionary.Add(sDictionaryKey,
oSettingsForOneFilter);
}
selectedMinimumAddress = oSettingsForOneFilter.SelectedMinimumAddress;
selectedMaximumAddress = oSettingsForOneFilter.SelectedMaximumAddress;
Debug.Assert(!String.IsNullOrEmpty(selectedMinimumAddress));
Debug.Assert(!String.IsNullOrEmpty(selectedMaximumAddress));
}
SetSettings
(
String tableName,
String columnName,
Decimal selectedMinimum,
Decimal selectedMaximum,
out String selectedMinimumAddress,
out String selectedMaximumAddress
)
{
Debug.Assert( !String.IsNullOrEmpty(tableName) );
Debug.Assert( !String.IsNullOrEmpty(columnName) );
Debug.Assert(selectedMaximum >= selectedMinimum);
AssertValid();
CheckOpen();
selectedMinimumAddress = selectedMaximumAddress = null;
SettingsForOneFilter oSettingsForOneFilter;
String sDictionaryKey = GetDictionaryKey(tableName, columnName);
Range oSelectedMinimumCell, oSelectedMaximumCell;
if ( m_oDynamicFilterSettingsDictionary.TryGetValue(
sDictionaryKey, out oSettingsForOneFilter) )
{
// The settings are already in the table. Update the table row.
Debug.Assert(m_oDynamicFilterSettingsTable.Parent is Worksheet);
Worksheet oWorksheet =
(Worksheet)m_oDynamicFilterSettingsTable.Parent;
oSelectedMinimumCell = oWorksheet.get_Range(
oSettingsForOneFilter.SelectedMinimumAddress, Missing.Value);
oSelectedMinimumCell.set_Value(Missing.Value, selectedMinimum);
oSelectedMaximumCell = oWorksheet.get_Range(
oSettingsForOneFilter.SelectedMaximumAddress, Missing.Value);
oSelectedMaximumCell.set_Value(Missing.Value, selectedMaximum);
// Update the dictionary entry.
oSettingsForOneFilter.SelectedMinimum = selectedMinimum;
oSettingsForOneFilter.SelectedMaximum = selectedMaximum;
}
else
{
// The settings aren't in the table yet. Add a row to the table.
Range oNewRowRange = ExcelTableUtil.AddTableRow(
m_oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.TableName,
tableName,
DynamicFilterSettingsTableColumnNames.ColumnName,
columnName,
DynamicFilterSettingsTableColumnNames.SelectedMinimum,
selectedMinimum,
DynamicFilterSettingsTableColumnNames.SelectedMaximum,
selectedMaximum
);
oSelectedMinimumCell = (Range)oNewRowRange.Cells[ 1,
GetTableColumnIndex(m_oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.SelectedMinimum, true) ];
oSelectedMaximumCell = (Range)oNewRowRange.Cells[ 1,
GetTableColumnIndex(m_oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.SelectedMaximum, true) ];
// Add a dictionary entry.
oSettingsForOneFilter = new SettingsForOneFilter();
oSettingsForOneFilter.SelectedMinimum = selectedMinimum;
oSettingsForOneFilter.SelectedMaximum = selectedMaximum;
oSettingsForOneFilter.SelectedMinimumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMinimumCell);
oSettingsForOneFilter.SelectedMaximumAddress =
ExcelUtil.GetRangeAddressAbsolute(oSelectedMaximumCell);
m_oDynamicFilterSettingsDictionary.Add(sDictionaryKey,
oSettingsForOneFilter);
}
selectedMinimumAddress = oSettingsForOneFilter.SelectedMinimumAddress;
selectedMaximumAddress = oSettingsForOneFilter.SelectedMaximumAddress;
Debug.Assert( !String.IsNullOrEmpty(selectedMinimumAddress) );
Debug.Assert( !String.IsNullOrEmpty(selectedMaximumAddress) );
}
Open()
{
Debug.Assert(m_oDynamicFilterSettingsTable == null);
AssertValid();
// Get the table that contains the dynamic filter settings.
if (!ExcelTableUtil.TryGetTable(m_oWorkbook,
WorksheetNames.Miscellaneous, TableNames.DynamicFilterSettings,
out m_oDynamicFilterSettingsTable) )
{
OnWorkbookFormatError( String.Format(
"A table that is required to use this feature is missing."
+ "\r\n\r\n{0}"
,
ErrorUtil.GetTemplateMessage()
) );
}
foreach (ExcelTableReader.ExcelTableRow oRow in
( new ExcelTableReader(m_oDynamicFilterSettingsTable) ).GetRows() )
{
String sTableName, sColumnName;
Double dSelectedMinimum, dSelectedMaximum;
if (
oRow.TryGetNonEmptyStringFromCell(
DynamicFilterSettingsTableColumnNames.TableName,
out sTableName)
&&
oRow.TryGetNonEmptyStringFromCell(
DynamicFilterSettingsTableColumnNames.ColumnName,
out sColumnName)
&&
oRow.TryGetDoubleFromCell(
DynamicFilterSettingsTableColumnNames.SelectedMinimum,
out dSelectedMinimum)
&&
oRow.TryGetDoubleFromCell(
DynamicFilterSettingsTableColumnNames.SelectedMaximum,
out dSelectedMaximum)
)
{
// Create a SettingsForOneFilter object for each filter and
// store it in a dictionary.
SettingsForOneFilter oSettingsForOneFilter =
new SettingsForOneFilter();
oSettingsForOneFilter.SelectedMinimum =
(Decimal)dSelectedMinimum;
oSettingsForOneFilter.SelectedMaximum =
(Decimal)dSelectedMaximum;
oSettingsForOneFilter.SelectedMinimumAddress =
ExcelUtil.GetRangeAddressAbsolute(
oRow.GetRangeForCell(
DynamicFilterSettingsTableColumnNames.SelectedMinimum)
);
oSettingsForOneFilter.SelectedMaximumAddress =
ExcelUtil.GetRangeAddressAbsolute(
oRow.GetRangeForCell(
DynamicFilterSettingsTableColumnNames.SelectedMaximum)
);
m_oDynamicFilterSettingsDictionary.Add(
GetDictionaryKey(sTableName, sColumnName),
oSettingsForOneFilter);
}
}
}
Open()
{
Debug.Assert(m_oDynamicFilterSettingsTable == null);
AssertValid();
// Get the table that contains the dynamic filter settings.
if (!ExcelTableUtil.TryGetTable(m_oWorkbook,
WorksheetNames.Miscellaneous, TableNames.DynamicFilterSettings,
out m_oDynamicFilterSettingsTable))
{
OnWorkbookFormatError(String.Format(
"A table that is required to use this feature is missing."
+ "\r\n\r\n{0}"
,
ErrorUtil.GetTemplateMessage()
));
}
foreach (ExcelTableReader.ExcelTableRow oRow in
(new ExcelTableReader(m_oDynamicFilterSettingsTable)).GetRows())
{
String sTableName, sColumnName;
Double dSelectedMinimum, dSelectedMaximum;
if (
oRow.TryGetNonEmptyStringFromCell(
DynamicFilterSettingsTableColumnNames.TableName,
out sTableName)
&&
oRow.TryGetNonEmptyStringFromCell(
DynamicFilterSettingsTableColumnNames.ColumnName,
out sColumnName)
&&
oRow.TryGetDoubleFromCell(
DynamicFilterSettingsTableColumnNames.SelectedMinimum,
out dSelectedMinimum)
&&
oRow.TryGetDoubleFromCell(
DynamicFilterSettingsTableColumnNames.SelectedMaximum,
out dSelectedMaximum)
)
{
// Create a SettingsForOneFilter object for each filter and
// store it in a dictionary.
SettingsForOneFilter oSettingsForOneFilter =
new SettingsForOneFilter();
oSettingsForOneFilter.SelectedMinimum =
(Decimal)dSelectedMinimum;
oSettingsForOneFilter.SelectedMaximum =
(Decimal)dSelectedMaximum;
oSettingsForOneFilter.SelectedMinimumAddress =
ExcelUtil.GetRangeAddressAbsolute(
oRow.GetRangeForCell(
DynamicFilterSettingsTableColumnNames.SelectedMinimum)
);
oSettingsForOneFilter.SelectedMaximumAddress =
ExcelUtil.GetRangeAddressAbsolute(
oRow.GetRangeForCell(
DynamicFilterSettingsTableColumnNames.SelectedMaximum)
);
m_oDynamicFilterSettingsDictionary.Add(
GetDictionaryKey(sTableName, sColumnName),
oSettingsForOneFilter);
}
}
}
