GetTableColumnIndex
(
ListObject oTable,
String sColumnName,
Boolean bColumnIsRequired
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sColumnName));
AssertValid();
ListColumn oColumn;
if (ExcelUtil.TryGetTableColumn(oTable, sColumnName, out oColumn))
{
return(oColumn.Index);
}
if (bColumnIsRequired)
{
OnWorkbookFormatError(String.Format(
"The table named \"{0}\" must have a column named \"{1}.\""
+ "\r\n\r\n{2}"
,
oTable.Name,
sColumnName,
ErrorUtil.GetTemplateMessage()
));
}
return(NoSuchColumn);
}
GetRequiredTables
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
out ListObject oEdgeTable,
out ListObject oVertexTable
)
{
Debug.Assert(oWorkbook != null);
AssertValid();
// Get the required table that contains edge data. GetEdgeTable()
// checks for the required vertex name columns.
EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader();
oEdgeTable = oEdgeWorksheetReader.GetEdgeTable(oWorkbook);
// Normally, the vertex table isn't required, but to avoid having to
// create the table in code if it's missing, require it here.
if (ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable))
{
// Make sure the vertex name column exists.
ListColumn oColumn;
if (!ExcelUtil.TryGetTableColumn(oVertexTable,
VertexTableColumnNames.VertexName, out oColumn))
{
oVertexTable = null;
}
}
else
{
oVertexTable = null;
}
if (oVertexTable == null)
{
throw new WorkbookFormatException(String.Format(
"To use this feature, there must be a worksheet named \"{0}\""
+ " that contains a table named \"{1}\", and that table must"
+ " contain a column named \"{2}\"."
+ "\r\n\r\n"
+ "{3}"
,
WorksheetNames.Vertices,
TableNames.Vertices,
VertexTableColumnNames.VertexName,
ErrorUtil.GetTemplateMessage()
));
}
}
GetEdgeTable
(
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(workbook != null);
AssertValid();
// Get the worksheet that contains edge data.
Worksheet oEdgeWorksheet;
if (!ExcelUtil.TryGetWorksheet(workbook, WorksheetNames.Edges,
out oEdgeWorksheet))
{
OnWorkbookFormatError(String.Format(
"The workbook must contain a worksheet named \"{0}\" that"
+ " contains edge data.\r\n\r\n{1}"
,
WorksheetNames.Edges,
ErrorUtil.GetTemplateMessage()
));
}
// Get the table (ListObject) that contains edge data.
ListObject oEdgeTable;
if (!ExcelUtil.TryGetTable(oEdgeWorksheet, TableNames.Edges,
out oEdgeTable))
{
OnWorkbookFormatError(String.Format(
"The worksheet named \"{0}\" must have a table named \"{1}\""
+ " that contains edge data.\r\n\r\n{2}"
,
WorksheetNames.Edges,
TableNames.Edges,
ErrorUtil.GetTemplateMessage()
));
}
// Make sure the vertex name columns exist.
GetTableColumnIndex(oEdgeTable, EdgeTableColumnNames.Vertex1Name,
true);
GetTableColumnIndex(oEdgeTable, EdgeTableColumnNames.Vertex2Name,
true);
return(oEdgeTable);
}
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);
}
}
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);
}