private void InitializeMainWindow()
{
//Создал окно
mainWindow = new Window(2, 2, 40, 5, "Информация о компьютере", true, 0, ref app);
mainWindow.BackgroundColor = ConsoleColor.Blue;
mainWindow.TextColor = ConsoleColor.White;
listWnd = new ListObject(mainWindow.Left + 2, mainWindow.Top + 1, 29, 3, true, true);
listWnd.BackgroundActiveColor = ConsoleColor.White;
listWnd.BackgroundColor = ConsoleColor.Blue;
listWnd.List = new List <string>();
listWnd.ButtonClicked += ListWnd_ButtonClicked;
listWnd.List.Add("Информация о системе");
listWnd.List.Add("Информация о процессах");
listWnd.List.Add("Сетевая статистика");
btnExit = new ButtonObject(mainWindow.Left + 31, mainWindow.Top + 3, 7, 1, false, true, "Выход");
btnExit.BackgroundColor = ConsoleColor.DarkGray;
btnExit.TextColor = ConsoleColor.Black;
btnExit.BackgroundActiveColor = ConsoleColor.White;
btnExit.ButtonClicked += BtnExit_ButtonClicked;
mainWindow.AddChildren(btnExit);
mainWindow.AddChildren(listWnd);
}
TryGetColumnGroupTable
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
ColumnGroup eColumnGroup,
out ListObject oTable
)
{
Debug.Assert(oWorkbook != null);
oTable = null;
String sColumnGroup = eColumnGroup.ToString();
if (sColumnGroup.StartsWith("Edge"))
{
return(ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Edges,
TableNames.Edges, out oTable));
}
if (sColumnGroup.StartsWith("Vertex"))
{
return(ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oTable));
}
if (sColumnGroup.StartsWith("Group"))
{
return(ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Groups,
TableNames.Groups, out oTable));
}
Debug.Assert(false);
return(false);
}
private static string CheckDuplicateDataInPk(string primaryKey, ListObject listObject)
{
var index = -1;
for (int i = 0; i < listObject.ListColumns.Count; ++i)
{
var lcn = listObject.ListColumns[i].Name;
if (lcn == primaryKey)
{
index = i;
break;
}
}
if (index > -1)
{
HashSet <string> pkData = new HashSet <string>();
var dataTable = listObject.DataRange.ExportDataTable();
for (int i = 0; i < dataTable.Rows.Count; ++i)
{
if (pkData.Contains(dataTable.Rows[i][index].ToString()))
{
return(string.Format(" <color=#ff0000>-->主键列中不能包含重复数据,导出终止,请修复Excel表 --主键>列名：{0}</color>", primaryKey));
}
if (string.IsNullOrEmpty(dataTable.Rows[i][index].ToString()))
{
return(string.Format(" <color=#ff0000>-->主键列中不能包含空数据,导出终止,请修复Excel表 --主键>列名：{0}</color>", primaryKey));
}
pkData.Add(dataTable.Rows[i][index].ToString());
}
}
return(null);
}
TryGetRequiredColumnWithIDInformation
(
GraphMetricColumnWithID oGraphMetricColumnWithID,
ListObject oTable,
out Range oVisibleColumnData,
out Range oIDColumnData
)
{
Debug.Assert(oGraphMetricColumnWithID != null);
Debug.Assert(oTable != null);
AssertValid();
oVisibleColumnData = null;
oIDColumnData = null;
// Get the specified column.
if (!TryGetRequiredColumnInformation(oGraphMetricColumnWithID, oTable,
out oVisibleColumnData))
{
return(false);
}
// Get the ID column.
if (!ExcelTableUtil.TryGetTableColumnData(oTable,
CommonTableColumnNames.ID, out oIDColumnData))
{
return(false);
}
return(true);
}
TryCreateStackedTable
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
String sWorksheetName,
GraphMetricColumn oGraphMetricColumn,
out ListObject oStackedTable
)
{
Debug.Assert(oWorkbook != null);
Debug.Assert(!String.IsNullOrEmpty(sWorksheetName));
Debug.Assert(oGraphMetricColumn != null);
AssertValid();
oStackedTable = null;
Worksheet oWorksheet;
Range oColumnARange;
if (
!ExcelUtil.TryGetOrAddWorksheet(oWorkbook, sWorksheetName,
out oWorksheet)
||
!ExcelUtil.TryGetRange(oWorksheet, "A:A", out oColumnARange)
)
{
return(false);
}
try
{
// The worksheet can contain multiple top-N-by tables, so we need
// to find where to put the new table.
Range oColumn1HeaderCell = GetColumn1HeaderCellForStackedTable(
oWorksheet, oColumnARange);
// Create just the first column. The table will auto-expand when
// this class adds the table's other column.
//
// (It's assumed here that oGraphMetricColumn represents the
// table's first column.)
oColumn1HeaderCell.set_Value(Missing.Value,
oGraphMetricColumn.ColumnName);
Range oTableRange = oColumn1HeaderCell;
ExcelUtil.ResizeRange(ref oTableRange, 2, 1);
oStackedTable = ExcelTableUtil.AddTable(oWorksheet, oTableRange,
oGraphMetricColumn.TableName, TableStyleNames.NodeXLTable);
ExcelTableUtil.SetColumnWidth(oColumn1HeaderCell,
oGraphMetricColumn.ColumnWidthChars);
}
catch (System.Runtime.InteropServices.COMException)
{
return(false);
}
return(true);
}
public static void ExportDataTableToWorksheet(DataTable tb, ImportOpt opt, List <string> formats)
{
if (tb == null || opt == null)
{
return;
}
Excel.Range topLeft = opt.topLeft ?? Globals.ThisAddIn.Application.ActiveCell;
Excel.Worksheet nativeActiveSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Worksheet sheet = Globals.Factory.GetVstoObject(nativeActiveSheet);
Excel.Range range = topLeft.Resize[tb.Rows.Count + 1, tb.Columns.Count];
if (opt.overwrite)
{
DeleteListObjectInRange(sheet, range);
}
if (opt.maxRowsToLoadIntoExcel >= 0)
{
RemoveLastNRowsInDataTable(tb, tb.Rows.Count - opt.maxRowsToLoadIntoExcel);
}
ListObject list = sheet.Controls.AddListObject(range, GenListObjName(sheet, "DDBTable"));
list.AutoSetDataBoundColumnHeaders = true;
list.SetDataBinding(tb);
list.Disconnect();
if (formats != null)
{
ApplyRangeFormat(list.ListColumns, formats);
}
}
GetOrAddTableColumn
(
ListColumn oSourceColumn,
ListObject oNewTable
)
{
Debug.Assert(oSourceColumn != null);
Debug.Assert(oNewTable != null);
AssertValid();
String sColumnName = oSourceColumn.Name;
Range oSourceColumnData = oSourceColumn.DataBodyRange;
Debug.Assert(oSourceColumnData != null);
ListColumn oNewColumn;
if (!ExcelTableUtil.TryGetOrAddTableColumn(oNewTable, sColumnName,
(Double)oSourceColumnData.ColumnWidth, null,
out oNewColumn))
{
throw new ExportWorkbookException(
"A column couldn't be added to the new workbook."
);
}
Range oNewColumnData = oNewColumn.DataBodyRange;
Debug.Assert(oNewColumnData != null);
oNewColumnData.NumberFormat = oSourceColumnData.NumberFormat;
return(oNewColumnData);
}
AutoFillGroupTable
(
ListObject oGroupTable,
AutoFillUserSettings oAutoFillUserSettings,
AutoFillWorkbookResults oAutoFillWorkbookResults
)
{
Debug.Assert(oGroupTable != null);
Debug.Assert(oAutoFillUserSettings != null);
Debug.Assert(oAutoFillWorkbookResults != null);
AutoFillNumericComparisonColumn(oGroupTable,
oAutoFillUserSettings.GroupCollapsedSourceColumnName,
GroupTableColumnNames.Collapsed,
oAutoFillUserSettings.GroupCollapsedDetails
);
if (
AutoFillColumnViaCopy(oGroupTable,
oAutoFillUserSettings.GroupLabelSourceColumnName,
GroupTableColumnNames.Label)
&&
oAutoFillUserSettings.GroupLabelDetails.PrependWithGroupName
)
{
PrependGroupLabelsWithGroupNames(oGroupTable);
}
}
public void ReadObjectWithList()
{
JsonSerializerOptions options = new JsonSerializerOptions();
options.SetupExtensions();
const string json = @"{""IntList"":[1,2],""ObjectList"":[{""IntValue"":1},{""IntValue"":2}],""StringList"":[""a"",""b""]}";
ListObject obj = Helper.Read <ListObject>(json, options);
Assert.NotNull(obj);
Assert.NotNull(obj.IntList);
Assert.Equal(2, obj.IntList.Count);
Assert.Equal(1, obj.IntList[0]);
Assert.Equal(2, obj.IntList[1]);
Assert.NotNull(obj.ObjectList);
Assert.Equal(2, obj.ObjectList.Count);
Assert.NotNull(obj.ObjectList[0]);
Assert.Equal(1, obj.ObjectList[0].IntValue);
Assert.NotNull(obj.ObjectList[1]);
Assert.Equal(2, obj.ObjectList[1].IntValue);
Assert.NotNull(obj.StringList);
Assert.Equal(2, obj.StringList.Count);
Assert.Equal("a", obj.StringList[0]);
Assert.Equal("b", obj.StringList[1]);
}
public static int GetColumnIndex(ListObject table, string columnName, bool ignoreNotFound = false)
{
foreach (ListColumn col in table.ListColumns)
{
if (col.Name.ToUpper() == columnName.ToUpper())
{
return(col.Index);
}
}
if (ignoreNotFound)
{
return(-1);
}
else
{
string errMsg = "Cannot find Column " + columnName + " in table " + table.Name;
System.Windows.Forms.MessageBox.Show(errMsg);
throw new Exception(errMsg);
}
//try {
// // this function returns index of specified column in the table
// return table.ListColumns[columnName].Index;
//} catch (Exception ex) {
// if (ignoreNotFound) {
// return -1;
// } else {
// System.Windows.Forms.MessageBox.Show("Cannot find Column " + columnName + " in table " + table.Name);
// throw ex;
// }
//}
}
private static int GetColumnIndexOld(ListObject table, string columnName, bool ignoreNotFound = false)
{
try
{
// this function returns index of specified column in the table
string rangeStr = null;
rangeStr = "#tableName[[#Headers],[#columnName]]";
// add check on the listSeparator, if not equal to ",", then change
// Dim listSeparator As String
// listSeparator = Application.International(xlListSeparator)
// If listSeparator <> "," Then
// rangeStr = Replace(rangeStr, ",", listSeparator)
// End If
rangeStr = rangeStr.Replace("#tableName", table.Name);
rangeStr = rangeStr.Replace("#columnName", columnName);
return(table.Range.Worksheet.Range[rangeStr].Column - table.Range.Column + 1);
}
catch (Exception ex)
{
if (ignoreNotFound)
{
return(-1);
}
else
{
System.Windows.Forms.MessageBox.Show("Cannot find Column " + columnName + " in table " + table.Name);
throw ex;
}
}
}
public static long LookupFilteredRowNum(ListObject table, string filterColumn, object filterValue, string lookupColumn, object value)
{
// this function is to lookup RowNum from a table on the filtered result
// filterColumn must be sorted
long startRow = 0;
long endRow = 0;
Range rng = default(Range);
startRow = LookUpRowNum(table, filterColumn, filterValue);
if (startRow <= 0)
{
return(-1);
}
endRow = LookUpRowNum(table, filterColumn, filterValue);
rng = table.Application.Range[table.ListColumns[lookupColumn].DataBodyRange[startRow, 1], table.ListColumns[lookupColumn].DataBodyRange[endRow, 1]];
try
{
long rowNum = (long)table.Application.WorksheetFunction.Match(value, rng, 0);
if (rowNum > 0)
{
return(startRow + rowNum - 1);
}
else
{
return(-1);
}
}
catch
{
return(-1);
}
}
public static ListObject CreateTable(Worksheet sh
, string TableName
, int Row
, int Col
, string[] ColumnNames
, string[] NumberFormat)
{
ListObject tbl = CreateTable(sh: sh, TableName: TableName, Row: Row, Col: Col);
// create columns
for (int i = ColumnNames.Length - tbl.ListColumns.Count; i > 0; i--)
{
tbl.ListColumns.Add();
}
// rename and format column
for (int i = 0; i < ColumnNames.Length; i++)
{
tbl.ListColumns[i + 1].Name = ColumnNames[i];
if (i < NumberFormat.Length && NumberFormat[i] != "")
{
tbl.ListColumns[i + 1].Range.EntireColumn.NumberFormat = NumberFormat[i];
}
}
return(tbl);
}
public static void SortTable(ListObject tbl, int UpToColumnIndex)
{
if (UpToColumnIndex < 1)
{
return;
}
if (UpToColumnIndex > tbl.ListColumns.Count)
{
UpToColumnIndex = tbl.ListColumns.Count;
}
Sort s = tbl.Sort;
s.SortFields.Clear();
if (tbl.ListRows.Count > 0)
{
for (int i = 1; i <= UpToColumnIndex; i++)
{
s.SortFields.Add(Key: tbl.ListColumns[i].DataBodyRange
, SortOn: XlSortOn.xlSortOnValues
, Order: XlSortOrder.xlAscending
, DataOption: XlSortDataOption.xlSortNormal);
}
s.Header = XlYesNoGuess.xlYes;
s.MatchCase = false;
s.Orientation = XlSortOrientation.xlSortColumns;
s.SortMethod = XlSortMethod.xlStroke;
s.Apply();
}
}
public static void SortTable(ListObject tbl, List <int> columnIdList)
{
if (columnIdList.Count == 0)
{
return;
}
Sort s = tbl.Sort;
s.SortFields.Clear();
if (tbl.ListRows.Count > 0)
{
foreach (int colId in columnIdList)
{
if (colId > 0)
{
s.SortFields.Add(Key: tbl.ListColumns[colId].DataBodyRange
, SortOn: XlSortOn.xlSortOnValues
, Order: XlSortOrder.xlAscending
, DataOption: XlSortDataOption.xlSortNormal);
}
else
{
MessageBox.Show("Column " + colId + " does not exist in Table " + tbl.Name);
}
}
s.Header = XlYesNoGuess.xlYes;
s.MatchCase = false;
s.Orientation = XlSortOrientation.xlSortColumns;
s.SortMethod = XlSortMethod.xlStroke;
s.Apply();
}
}
public void ApplyTeam(ListObject id)
{
string NewName = AllTeamFields[id.InstanceId].SymmetrySelected.GetComponent <InputField>().text;
int c = 0;
bool WrongName = false;
for (int t = 0; t < MapLuaParser.Current.ScenarioLuaFile.Data.Configurations[c].Teams.Length; t++)
{
if (t != id.InstanceId && NewName == MapLuaParser.Current.ScenarioLuaFile.Data.Configurations[c].Teams[t].name)
{
WrongName = true;
break;
}
}
if (!WrongName)
{
Undo.RegisterUndo(new UndoHistory.HistoryArmiesChange());
MapLuaParser.Current.ScenarioLuaFile.Data.Configurations[c].Teams[id.InstanceId].name = NewName;
id.ObjectName.text = NewName;
}
id.Selected.SetActive(true);
id.SymmetrySelected.SetActive(false);
}
ShiftColumnGroupNames
(
ListObject oVertexTable
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oVertexTable.Parent is Worksheet);
Worksheet oWorksheet = (Worksheet)oVertexTable.Parent;
Range oRange = ExcelUtil.GetRange(oWorksheet, "B1");
Object oValue = ExcelUtil.GetRangeValues(oRange)[1, 1];
if (oValue is String && (String)oValue == "Visual Properties")
{
// Note: Don't try to use Range.Insert() here. That raises a
// COMException if the table has hidden rows. Selecting the range
// and then inserting at the selection works.
oRange.Select();
oWorksheet.Application.Selection.Insert(
XlInsertShiftDirection.xlShiftToRight,
XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
}
public static void copyTableColumn(ListObject destTable
, string destColumn
, string lookupTable
, string returnColumn
, string matchColumn
, string matchToColumn
, int ignoreNA = 0)
{
if (destTable.ListRows.Count > 0)
{
string formulaTemp = null;
string formulaText = null;
if (ignoreNA > 0)
{
formulaTemp = "=IFERROR(INDEX($LookupTable[$ReturnColumn]," + "MATCH($DestTable[[#This Row]," + "[$MatchColumn]],$LookupTable[$MatchToColumn],0)),\"\")";
}
else
{
formulaTemp = "=INDEX($LookupTable[$ReturnColumn]," + "MATCH($DestTable[[#This Row]," + "[$MatchColumn]],$LookupTable[$MatchToColumn],0))";
}
formulaText = formulaTemp;
formulaText = formulaText.Replace("$LookupTable", lookupTable);
formulaText = formulaText.Replace("$ReturnColumn", returnColumn);
formulaText = formulaText.Replace("$DestTable", destTable.Name);
formulaText = formulaText.Replace("$MatchColumn", matchColumn);
formulaText = formulaText.Replace("$MatchToColumn", matchToColumn);
SetTableColumn(destTable, destColumn, formulaText);
}
}
private static void ChangePivotCells(PivotTable pt, List <PivotCell> cells, List <string> values)
{
Worksheet sh = pt.TableRange1.Worksheet;
string sourceString = pt.PivotCache().SourceData;
ListObject dt = ListObjectHelper.getListObject(Globals.ThisAddIn.Application.ActiveWorkbook, sourceString);
if (dt == null)
{
MessageBox.Show("Datas source of the pivot table must be a Table");
pt.PivotCache().Refresh();
return;
// Range sourceRange = SheetRangeHelper.getRange(Globals.ThisAddIn.Application.ActiveWorkbook, sourceString);
}
// loop through changed cells, apply changes
for (int i = 0; i <= cells.Count - 1; i++)
{
if (!GeneralSettings.IsNumeric(values[i]) && !string.IsNullOrEmpty(values[i]))
{
System.Windows.Forms.MessageBox.Show("Only numeric values are allowed.");
}
else
{
ChangePivotCell(cells[i], values[i], dt);
}
}
ListObjectHelper.ResetTable(dt);
}
EndAutoFill
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
ListObject oEdgeTable,
ListObject oVertexTable,
ExcelHiddenColumns oHiddenEdgeColumns,
ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
if (oEdgeTable != null && oHiddenEdgeColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oEdgeTable,
oHiddenEdgeColumns);
}
if (oVertexTable != null && oHiddenVertexColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oVertexTable,
oHiddenVertexColumns);
}
SetScreenUpdating(oWorkbook, true);
}
AutoFillNumericComparisonColumn
(
ListObject oTable,
String sSourceColumnName,
String sDestinationColumnName,
NumericComparisonColumnAutoFillUserSettings oDetails
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sDestinationColumnName));
Debug.Assert(oDetails != null);
if (String.IsNullOrEmpty(sSourceColumnName))
{
return;
}
TableColumnMapper.MapToTwoStrings(
oTable, sSourceColumnName, sDestinationColumnName,
oDetails.ComparisonOperator,
oDetails.SourceNumberToCompareTo,
oDetails.DestinationString1,
oDetails.DestinationString2
);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook object
Workbook book = new Workbook();
// Access first worksheet
Worksheet sheet = book.Worksheets[0];
// Add column headings in cell A1 and B1
sheet.Cells[0, 0].PutValue("Column A");
sheet.Cells[0, 1].PutValue("Column B");
// Add list object, set its name and style
ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add(0, 0, 1, sheet.Cells.MaxColumn, true)];
listObject.TableStyleType = TableStyleType.TableStyleMedium2;
listObject.DisplayName = "Table";
// Set the formula of second column so that it propagates to new rows automatically while entering data
listObject.ListColumns[1].Formula = "=[Column A] + 1";
// Save the workbook in xlsx format
book.Save(dataDir + "output_out.xlsx");
// ExEnd:1
}
public void WriteWithList()
{
JsonSerializerOptions options = new JsonSerializerOptions();
options.SetupExtensions();
const string json = @"{""IntList"":[1,2],""ObjectList"":[{""IntValue"":1},{""IntValue"":2}],""StringList"":[""a"",""b""]}";
ListObject obj = new ListObject
{
IntList = new List <int> {
1, 2
},
ObjectList = new List <IntObject>
{
new IntObject {
IntValue = 1
},
new IntObject {
IntValue = 2
}
},
StringList = new List <string> {
"a", "b"
}
};
Helper.TestWrite(obj, json, options);
}
GetDynamicFilterSettingsTableColumnIndexes
(
ListObject oDynamicFilterSettingsTable
)
{
Debug.Assert(oDynamicFilterSettingsTable != null);
AssertValid();
DynamicFilterSettingsTableColumnIndexes
oDynamicFilterSettingsTableColumnIndexes =
new DynamicFilterSettingsTableColumnIndexes();
oDynamicFilterSettingsTableColumnIndexes.TableName =
GetTableColumnIndex(oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.TableName, true);
oDynamicFilterSettingsTableColumnIndexes.ColumnName =
GetTableColumnIndex(oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.ColumnName, true);
oDynamicFilterSettingsTableColumnIndexes.SelectedMinimum =
GetTableColumnIndex(oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.SelectedMinimum, true);
oDynamicFilterSettingsTableColumnIndexes.SelectedMaximum =
GetTableColumnIndex(oDynamicFilterSettingsTable,
DynamicFilterSettingsTableColumnNames.SelectedMaximum, true);
return(oDynamicFilterSettingsTableColumnIndexes);
}
//*************************************************************************
// Constructor: ExcelTableReader()
//
/// <summary>
/// Initializes a new instance of the <see cref="ExcelTableReader" />
/// class.
/// </summary>
///
/// <param name="table">
/// The table to read. The table must have no hidden columns.
/// </param>
///
/// <remarks>
/// If <paramref name="table" /> has hidden columns, an exception is
/// thrown. Use the <see cref="ExcelColumnHider" /> class to temporarily
/// show all hidden columns if necessary.
/// </remarks>
//*************************************************************************
public ExcelTableReader(
ListObject table
)
{
Debug.Assert(table != null);
m_oTable = table;
m_oColumnIndexesOneBased = new Dictionary<String, Int32>();
ListColumns oColumns = table.ListColumns;
Int32 iColumns = oColumns.Count;
for (Int32 i = 1; i <= iColumns; i++)
{
String sColumnName = oColumns[i].Name;
if ( !String.IsNullOrEmpty(sColumnName) )
{
m_oColumnIndexesOneBased.Add(sColumnName, i);
}
}
m_oCurrentSubrange = null;
m_aoCurrentSubrangeValues = null;
m_iCurrentRowOneBased = Int32.MinValue;
AssertValid();
}
public static string GetTime(string city)
{
ListObject TimeTable = RoutesSheet.ListObjects["Timetable"];
string time = "";
int isinternational = (from r in InternationalCityList
where r == city
select r).ToArray().Length;
if (isinternational > 0)
{
city = "LTL";
}
foreach (ListRow row in TimeTable.ListRows)
{
string direction = row.Range[1, TimeTable.ListColumns["Направление"].Index].Text;
if (direction.Contains(city))
{
time = row.Range[1, TimeTable.ListColumns["Время подачи ТС"].Index].Text;
break;
}
if (direction.Contains("Межгород"))
{
time = row.Range[1, TimeTable.ListColumns["Время подачи ТС"].Index].Text;
}
}
return(time);
}
DeleteMarkedRows
(
ListObject oEdgeTable,
Range oDeleteIfEmptyData,
Object [,] aoDeleteIfEmptyValues
)
{
Debug.Assert(oEdgeTable != null);
Debug.Assert(oDeleteIfEmptyData != null);
Debug.Assert(aoDeleteIfEmptyValues != null);
AssertValid();
Range oMarkedRows = null;
if (oDeleteIfEmptyData.Rows.Count != 1)
{
try
{
oMarkedRows = oDeleteIfEmptyData.SpecialCells(
XlCellType.xlCellTypeBlanks, Missing.Value);
}
catch (COMException)
{
// There are no such rows.
oMarkedRows = null;
}
}
else
{
// Range.SpecialCells() can't be used in the one-cell case, for
// which it behaves in a bizarre manner. See this posting:
//
// http://ewbi.blogs.com/develops/2006/03/determine_if_a_.html
//
// ...of which this is an excerpt:
//
// "SpecialCells ignores any source Range consisting of only one
// cell. When executing SpecialCells on a Range having only one
// cell, it will instead consider all of the cells falling within
// the boundary marked by the bottom right cell of the source Range
// sheet's UsedRange."
//
// Instead, just check the single row.
if (aoDeleteIfEmptyValues[1, 1] == null)
{
oMarkedRows = oDeleteIfEmptyData.EntireRow;
}
}
if (oMarkedRows != null)
{
// Delete the marked rows, which are now contiguous.
Debug.Assert(oMarkedRows.Areas.Count == 1);
oMarkedRows.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
}
}
public static void Run()
{
// ExStart:1
// Load sample Excel file containing a table.
Workbook workbook = new Workbook(sourceDir + "sampleCreateSlicerToExcelTable.xlsx");
// Access first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
// Access first table inside the worksheet.
ListObject table = worksheet.ListObjects[0];
// Add slicer
int idx = worksheet.Slicers.Add(table, 0, "H5");
Slicer slicer = worksheet.Slicers[idx];
slicer.Placement = PlacementType.FreeFloating;
slicer.RowHeightPixel = 50;
slicer.WidthPixel = 500;
slicer.Title = "Aspose";
slicer.AlternativeText = "Alternate Text";
slicer.IsPrintable = false;
slicer.IsLocked = false;
// Refresh the slicer.
slicer.Refresh();
// Save the workbook in output XLSX format.
workbook.Save(outputDir + "outputChangeSlicerProperties.xlsx", SaveFormat.Xlsx);
// ExEnd:1
Console.WriteLine("ChangeSlicerProperties executed successfully.");
}
ResizeTable
(
ListObject oTable,
Int32 iMinimumRows
)
{
Debug.Assert(oTable != null);
Debug.Assert(iMinimumRows >= 0);
AssertValid();
ListRows oRows = oTable.ListRows;
// Note that ListRows.Count returns 0 even if there is one data row.
// For example, if the table is empty when this method is called and
// iMinimumRows=5, ListRows.Count will return 0 but adding 5 rows will
// result in 6 rows.
Int32 iRows = Math.Max(oRows.Count, 1);
while (iRows < iMinimumRows)
{
oRows.Add(Missing.Value);
iRows++;
}
}
TryGetSubgraphImageColumnData
(
ListObject oVertexTable,
out Range oSubgraphImageColumnData
)
{
Debug.Assert(oVertexTable != null);
oSubgraphImageColumnData = null;
ListColumn oSubgraphImageColumn;
if (
ExcelTableUtil.TryGetOrInsertTableColumn(oVertexTable,
VertexTableColumnNames.SubgraphImage,
OneBasedSubgraphImageColumnIndex,
SubgraphImageColumnWidthChars, null, out oSubgraphImageColumn)
&&
ExcelTableUtil.TryGetTableColumnData(oSubgraphImageColumn,
out oSubgraphImageColumnData)
)
{
ShiftColumnGroupNames(oVertexTable);
return(true);
}
return(false);
}
/// <summary>
/// Adds
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddListObject_Click(object sender, EventArgs e)
{
// This is Microsoft.Office.Interop.Excel.Worksheet (COM)
Excel.Worksheet ws = (Excel.Worksheet)cboWorksheets.SelectedItem;
ws.Activate();
// This is Microsoft.Office.Tools.Excel.Worksheet (VSTO wrapper)
Worksheet vstoWs = Worksheet.GetVstoObject(ws);
try
{
// Now we have the VSTO wrapper, add some VSTO objects to it...
// First a ListObject
ListObject lo = vstoWs.Controls.AddListObject(vstoWs.Range["A3", Type.Missing], "myTable");
// Try bind some data to the ListObject
lo.DataSource = GetDemoData();
lo.DataMember = "DemoTable";
// Now add a button.
Button btnVsto = vstoWs.Controls.AddButton(vstoWs.Range["A1", Type.Missing], "btnVSTO");
btnVsto.Text = "VSTO Button";
btnVsto.Width = 100;
btnVsto.Height = 23;
// Setup the button Click event handler.
btnVsto.Click += delegate(object s, EventArgs args)
{
MessageBox.Show("VSTO button clicked.", "GetVstoObject demo", MessageBoxButtons.OK, MessageBoxIcon.Information);
};
}
catch (RuntimeException rtEx)
{
MessageBox.Show(rtEx.ToString(), "GetVstoObject demo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void SameListObjectsAreEqual()
{
// Arrange
var values = Many<string>().ToArray();
var listObject1 = new ListObject(values);
var listObject2 = new ListObject(values);
// Assert
listObject1.GetHashCode().Should().Be(listObject2.GetHashCode(), "hash code");
listObject1.Equals(listObject2).Should().BeTrue("Equals");
(listObject1 == listObject2).Should().BeTrue("==");
}
public override DetailGroup GetDetails(ListObject computer)
{
// You can cast computer to the specific object and get
// more detailed information
if (computer != null)
{
DetailGroup group = new DetailGroup("Computer detailed information");
group.Add("Computer Id:", computer.Id);
return group;
}
else
{
return null;
}
}
ShowHiddenColumns
(
ListObject table
)
{
Debug.Assert(table != null);
ExcelHiddenColumns oExcelHiddenColumns = new ExcelHiddenColumns();
foreach (ListColumn oColumn in table.ListColumns)
{
if ( (Boolean)oColumn.Range.EntireColumn.Hidden )
{
oExcelHiddenColumns.AddLast(oColumn.Name);
ShowOrHideColumn(oColumn, true);
}
}
return (oExcelHiddenColumns);
}
ActivateWorksheet
(
ListObject table
)
{
Debug.Assert(table != null);
Debug.Assert(table.Parent is Worksheet);
ActivateWorksheet((Worksheet)table.Parent);
}
TryAddTableColumnWithRowNumbers
(
ListObject table,
String columnName,
Double columnWidthChars,
String columnStyle,
out ListColumn listColumn
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(columnWidthChars == AutoColumnWidth ||
columnWidthChars >= 0);
listColumn = null;
if (!TryAddTableColumn(table, columnName, columnWidthChars,
columnStyle, out listColumn))
{
return (false);
}
Range oDataBodyRange = listColumn.DataBodyRange;
if (oDataBodyRange != null)
{
// Fill the column with a ROW() formulas.
oDataBodyRange.set_Value(Missing.Value, "=ROW()");
// Convert the formulas to static values.
oDataBodyRange.Copy(Missing.Value);
PasteValues(oDataBodyRange);
}
return (true);
}
//*************************************************************************
// Method: TryAutoFillNumericRangeColumn()
//
/// <summary>
/// Runs the application's AutoFill feature on a destination column that
/// should contain a numeric range.
/// </summary>
///
/// <param name="oTable">
/// The table containing the source and destination columns.
/// </param>
///
/// <param name="sSourceColumnName">
/// Name of the source column. If null or empty, this method does nothing.
/// </param>
///
/// <param name="sDestinationColumnName">
/// Name of the destination column.
/// </param>
///
/// <param name="oDetails">
/// User-specified details for the destination column.
/// </param>
///
/// <param name="dSourceCalculationNumber1">
/// Where the actual first source number used in the calculations gets
/// stored if true is returned.
/// </param>
///
/// <param name="dSourceCalculationNumber2">
/// Where the actual second source number used in the calculations gets
/// stored if true is returned.
/// </param>
///
/// <param name="iDecimalPlaces">
/// Where the number of decimal places displayed in the column gets stored.
/// </param>
///
/// <returns>
/// true if the autofill was performed.
/// </returns>
//*************************************************************************
private static Boolean TryAutoFillNumericRangeColumn(
ListObject oTable,
String sSourceColumnName,
String sDestinationColumnName,
NumericRangeColumnAutoFillUserSettings oDetails,
out Double dSourceCalculationNumber1,
out Double dSourceCalculationNumber2,
out Int32 iDecimalPlaces
)
{
Debug.Assert(oTable != null);
Debug.Assert( !String.IsNullOrEmpty(sDestinationColumnName) );
Debug.Assert(oDetails != null);
dSourceCalculationNumber1 = dSourceCalculationNumber2 =
Double.MinValue;
iDecimalPlaces = Int32.MinValue;
if ( String.IsNullOrEmpty(sSourceColumnName) )
{
return (false);
}
return (TableColumnMapper.TryMapToNumericRange(
oTable, sSourceColumnName, sDestinationColumnName,
oDetails.UseSourceNumber1,
oDetails.UseSourceNumber2,
oDetails.SourceNumber1,
oDetails.SourceNumber2,
oDetails.DestinationNumber1,
oDetails.DestinationNumber2,
oDetails.IgnoreOutliers,
oDetails.UseLogs,
out dSourceCalculationNumber1,
out dSourceCalculationNumber2,
out iDecimalPlaces
) );
}
//*************************************************************************
// Method: AutoFillNumericComparisonColumn()
//
/// <summary>
/// Runs the application's AutoFill feature on a destination column that
/// should contain one of two strings.
/// </summary>
///
/// <param name="oTable">
/// The table containing the source and destination columns.
/// </param>
///
/// <param name="sSourceColumnName">
/// Name of the source column. If null or empty, this method does nothing.
/// </param>
///
/// <param name="sDestinationColumnName">
/// Name of the destination column.
/// </param>
///
/// <param name="oDetails">
/// User-specified details for the destination column.
/// </param>
//*************************************************************************
private static void AutoFillNumericComparisonColumn(
ListObject oTable,
String sSourceColumnName,
String sDestinationColumnName,
NumericComparisonColumnAutoFillUserSettings oDetails
)
{
Debug.Assert(oTable != null);
Debug.Assert( !String.IsNullOrEmpty(sDestinationColumnName) );
Debug.Assert(oDetails != null);
if ( String.IsNullOrEmpty(sSourceColumnName) )
{
return;
}
TableColumnMapper.MapToTwoStrings(
oTable, sSourceColumnName, sDestinationColumnName,
oDetails.ComparisonOperator,
oDetails.SourceNumberToCompareTo,
oDetails.DestinationString1,
oDetails.DestinationString2
);
}
//*************************************************************************
// Method: AutoFillColumnViaCopy()
//
/// <summary>
/// Runs the application's AutoFill feature on a destination column that
/// should contain a copy of the source column.
/// </summary>
///
/// <param name="oTable">
/// The table containing the source and destination columns.
/// </param>
///
/// <param name="sSourceColumnName">
/// Name of the source column. If null or empty, this method does nothing.
/// </param>
///
/// <param name="sDestinationColumnName">
/// Name of the destination column.
/// </param>
//*************************************************************************
private static void AutoFillColumnViaCopy(
ListObject oTable,
String sSourceColumnName,
String sDestinationColumnName
)
{
Debug.Assert(oTable != null);
Debug.Assert( !String.IsNullOrEmpty(sDestinationColumnName) );
if ( String.IsNullOrEmpty(sSourceColumnName) )
{
return;
}
TableColumnMapper.MapViaCopy(
oTable, sSourceColumnName, sDestinationColumnName
);
}
TryGetTableColumn
(
ListObject table,
String columnName,
out ListColumn column
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
column = null;
try
{
column = table.ListColumns[columnName];
return (true);
}
catch (COMException)
{
return (false);
}
}
TryGetTableColumnDataAndValues
(
ListObject table,
String columnName,
out Range tableColumnData,
out Object[,] tableColumnDataValues
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
tableColumnData = null;
tableColumnDataValues = null;
if (!ExcelUtil.TryGetTableColumnData(table, columnName,
out tableColumnData))
{
return (false);
}
tableColumnDataValues = ExcelUtil.GetRangeValues(tableColumnData);
return (true);
}
//*************************************************************************
// Method: FillVertexTable()
//
/// <summary>
/// Fills in the vertex name column with specified vertex names.
/// </summary>
///
/// <param name="oVertexTable">
/// Vertex table.
/// </param>
///
/// <param name="oVertexNameDictionary">
/// Dictionary of vertex names. The key is the vertex name and the value
/// is unused. IMPORTANT: This method might remove some entries from the
/// dictionary.
/// </param>
///
/// <remarks>
/// IMPORTANT: This method might remove some entries from the dictionary.
/// </remarks>
//*************************************************************************
protected void FillVertexTable(
ListObject oVertexTable,
Dictionary<String, Char> oVertexNameDictionary
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oVertexNameDictionary != null);
AssertValid();
// There may already be some vertex names in the table. For each
// existing name, remove the name from the dictionary.
Int32 iExistingRows = 0;
Range oVertexNameRange;
if ( ExcelUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameRange) )
{
iExistingRows = oVertexNameRange.Rows.Count;
// Read the vertex names all at once.
Object [,] aoVertexNameValues =
ExcelUtil.GetRangeValues(oVertexNameRange);
// Loop through the vertices.
for (Int32 iRowOneBased = 1; iRowOneBased <= iExistingRows;
iRowOneBased++)
{
// Get the vertex name and remove it from the dictionary.
String sVertexName;
if ( ExcelUtil.TryGetNonEmptyStringFromCell(
aoVertexNameValues, iRowOneBased, 1, out sVertexName) )
{
oVertexNameDictionary.Remove(sVertexName);
}
}
}
// Now create an array for the vertices that remain in the dictionary.
// These are vertices that were in the edge table but not the vertex
// table.
Int32 iRowsToAdd = oVertexNameDictionary.Count;
if (iRowsToAdd == 0)
{
return;
}
String [,] asAddedVertexNameValues = new String [iRowsToAdd, 1];
Int32 iIndex = 0;
foreach (KeyValuePair<String, Char> oKeyValuePair in
oVertexNameDictionary)
{
asAddedVertexNameValues[iIndex, 0] = oKeyValuePair.Key;
iIndex++;
}
// The table may be empty or contain empty rows. If so, the remaining
// vertices should be appended after the last non-empty row.
Int32 iLastNonEmptyRowOneBased;
Range oDataBodyRange = oVertexTable.DataBodyRange;
if (
oDataBodyRange == null
||
!ExcelUtil.TryGetLastNonEmptyRow(oDataBodyRange,
out iLastNonEmptyRowOneBased)
)
{
// There were no non-empty data rows in the table. Use an offset
// of 1 from the header row instead.
oDataBodyRange = oVertexTable.HeaderRowRange;
iExistingRows = 1;
}
else
{
iExistingRows = iLastNonEmptyRowOneBased - oDataBodyRange.Row + 1;
}
// Get the index of the vertex name column.
ListColumn oVertexNameColumn;
if ( !ExcelUtil.TryGetTableColumn(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameColumn) )
{
// This can't happen, because GetRequiredTables() has
// verified that the column exists.
Debug.Assert(false);
}
Int32 iVertexNameColumnIndexOneBased = oVertexNameColumn.Index;
Debug.Assert(oVertexTable.Parent is Worksheet);
Worksheet oVertexWorksheet = (Worksheet)oVertexTable.Parent;
Range oAddedVertexNameRange = oVertexWorksheet.get_Range(
oDataBodyRange.Cells[iExistingRows + 1,
iVertexNameColumnIndexOneBased],
oDataBodyRange.Cells[iExistingRows + iRowsToAdd,
iVertexNameColumnIndexOneBased]
);
oAddedVertexNameRange.set_Value(
Missing.Value, asAddedVertexNameValues);
}
TryAddOrInsertTableColumn
(
ListObject table,
String columnName,
Int32 oneBasedColumnIndex,
Double columnWidthChars,
String columnStyle,
out ListColumn listColumn
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(oneBasedColumnIndex == -1 || oneBasedColumnIndex >= 1);
Debug.Assert(columnWidthChars == AutoColumnWidth ||
columnWidthChars >= 0);
listColumn = null;
Object oPosition;
ListColumns oColumns = table.ListColumns;
Int32 iColumns = oColumns.Count;
Double[] adColumnWidthChars = null;
if (oneBasedColumnIndex == -1)
{
oPosition = Missing.Value;
}
else
{
oPosition = oneBasedColumnIndex;
// When inserting a column, Excel messes up the widths of the
// columns after the insertion point. Save the widths of those
// columns.
if (oneBasedColumnIndex <= iColumns)
{
adColumnWidthChars =
new Double[iColumns - oneBasedColumnIndex + 1];
for (Int32 iOneBasedIndex = oneBasedColumnIndex;
iOneBasedIndex <= iColumns; iOneBasedIndex++)
{
adColumnWidthChars[iOneBasedIndex - oneBasedColumnIndex] =
(Double)oColumns[iOneBasedIndex].Range.ColumnWidth;
}
}
}
try
{
listColumn = oColumns.Add(oPosition);
}
catch (COMException oCOMException)
{
if (oCOMException.ErrorCode == -2146827284)
{
// This can happen, for example, if adding a table column
// would cause a merged cells to unmerge, the user is asked
// if he wants to allow the unmerge, and he says no.
return (false);
}
throw;
}
// Set various properties on the new column.
listColumn.Name = columnName;
Range oColumnRange = listColumn.Range;
if (columnWidthChars == AutoColumnWidth)
{
oColumnRange.EntireColumn.AutoFit();
}
else
{
oColumnRange.ColumnWidth = columnWidthChars;
}
oColumnRange.Validation.Delete();
SetRangeStyle(oColumnRange, columnStyle);
if (adColumnWidthChars != null)
{
// Restore the widths of the columns after the insertion point.
for (Int32 iOneBasedIndex = oneBasedColumnIndex;
iOneBasedIndex <= iColumns; iOneBasedIndex++)
{
oColumns[iOneBasedIndex + 1].Range.ColumnWidth =
adColumnWidthChars[iOneBasedIndex - oneBasedColumnIndex];
}
}
return (true);
}
ClearTableAutoFilters
(
ListObject table
)
{
Debug.Assert(table != null);
AutoFilter oAutoFilter = table.AutoFilter;
if (oAutoFilter == null)
{
return;
}
Filters oFilters = oAutoFilter.Filters;
Int32 iFilters = oFilters.Count;
Range oTableRange = table.Range;
for (Int32 i = 1; i <= iFilters; i++)
{
if (oFilters[i].On)
{
oTableRange.AutoFilter(i, Missing.Value,
XlAutoFilterOperator.xlAnd, Missing.Value, Missing.Value);
}
}
}
ClearTable
(
ListObject table
)
{
Debug.Assert(table != null);
// First, clear the data body rows. This clears the contents but
// retains formatting and data validation.
Range oDataBodyRange = table.DataBodyRange;
if (oDataBodyRange != null)
{
oDataBodyRange.ClearContents();
}
// Reduce the size of the table to get rid of the formatting.
Int32 iRows = 1;
if (table.HeaderRowRange != null)
{
iRows++;
}
if (table.TotalsRowRange != null)
{
iRows++;
}
Range oTableRange = table.Range;
oTableRange = oTableRange.get_Resize(iRows, oTableRange.Columns.Count);
table.Resize(oTableRange);
}
GetTableColumnNames
(
ListObject table,
String[] columnNamesToExclude,
String[] columnNameBasesToExclude
)
{
Debug.Assert(table != null);
Debug.Assert(columnNamesToExclude != null);
Debug.Assert(columnNameBasesToExclude != null);
List<String> oTableColumnNames = new List<String>();
// Loop through the table's columns.
foreach (ListColumn oColumn in table.ListColumns)
{
String sColumnName = oColumn.Name;
if (String.IsNullOrEmpty(sColumnName))
{
goto Skip;
}
if (Array.IndexOf(columnNamesToExclude, sColumnName) >= 0)
{
goto Skip;
}
foreach (String sColumnNameBaseToExclude in
columnNameBasesToExclude)
{
if (sColumnName.StartsWith(sColumnNameBaseToExclude))
{
goto Skip;
}
}
oTableColumnNames.Add(sColumnName);
Skip:
;
}
return (oTableColumnNames.ToArray());
}
TryInsertTableColumn
(
ListObject table,
String columnName,
Int32 oneBasedColumnIndex,
Double columnWidthChars,
String columnStyle,
out ListColumn listColumn
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(oneBasedColumnIndex >= 1);
Debug.Assert(columnWidthChars == AutoColumnWidth ||
columnWidthChars >= 0);
return (TryAddOrInsertTableColumn(table, columnName,
oneBasedColumnIndex, columnWidthChars, columnStyle,
out listColumn));
}
private void FormatSheet()
{
_excelApp = Globals.ThisAddIn.Application;
var excelBook = _excelApp.Workbooks.Add();
Worksheet excelSheet = excelBook.ActiveSheet;
_sheetName01 = "MSO261 - Pronto Pago";
excelSheet.Name = _sheetName01;
#region Instructions
_cells.GetCell(1, 1).Value = "CERREJÓN";
_cells.GetCell(1, 1).Style = _cells.GetStyle(StyleConstants.HeaderDefault);
_cells.MergeCells(1, 1, 1, 2);
_cells.GetCell("B1").Value = "Registro y Verificacion de Pronto Pagos";
_cells.GetCell("B1").Style = _cells.GetStyle(StyleConstants.HeaderSize17);
_cells.MergeCells(2, 1, 7, 2);
#endregion
#region Datos
_cells.GetCell(1, 4).Value = "Porcentaje de Descuento";
_cells.GetCell(1, 5).Value = "Dias";
_cells.GetCell(1, 6).Value = "Branch Code";
_cells.GetCell(1, 7).Value = "Bank Account";
_cells.GetRange(1, 4, 1, 7).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_cells.GetCell(1, TittleRow).Value = "Supplier";
_cells.GetCell(2, TittleRow).Value = "Factura";
_cells.GetCell(3, TittleRow).Value = "Fecha pago solicitada";
_cells.GetCell(4, TittleRow).Value = "Fecha pago original";
_cells.GetCell(5, TittleRow).Value = "PMT Status";
_cells.GetCell(6, TittleRow).Value = "Proveedor";
_cells.GetCell(7, TittleRow).Value = "Codigo Banco Original";
_cells.GetCell(8, TittleRow).Value = "ST";
_cells.GetCell(9, TittleRow).Value = "Vr total factura";
_cells.GetCell(10, TittleRow).Value = "Vr Base de Descuento";
_cells.GetCell(11, TittleRow).Value = "Diferencia";
_cells.GetCell(12, TittleRow).Value = "Descuento calculado";
_cells.GetCell(13, TittleRow).Value = "Vr descuento aplicado ";
_cells.GetCell(14, TittleRow).Value = "Fecha de pago modificada";
_cells.GetCell(15, TittleRow).Value = "Banco de Pago Modificado";
_cells.GetCell(ResultColumn, TittleRow).Value = "Result";
_cells.GetRange(1, TittleRow, ResultColumn, TittleRow).Style = _cells.GetStyle(StyleConstants.TitleRequired);
_excelSheetItems = excelSheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, _cells.GetRange(1, TittleRow, ResultColumn, TittleRow + 1), XlListObjectHasHeaders: XlYesNoGuess.xlYes);
_cells.GetRange(1, TittleRow + 1, ResultColumn, _excelSheetItems.ListRows.Count + TittleRow).NumberFormat = "@";
_cells.GetRange(9, TittleRow + 1, 10, _excelSheetItems.ListRows.Count + TittleRow).NumberFormat = "$ #,##0.00";
_cells.GetRange(12, TittleRow + 1, 13, _excelSheetItems.ListRows.Count + TittleRow).NumberFormat = "$ #,##0.00";
_cells.GetCell(2, 4).NumberFormat = "0.00%";
excelSheet.Cells.Columns.AutoFit();
excelSheet.Cells.Rows.AutoFit();
ImportFile();
#endregion
}
//*************************************************************************
// Method: GetRequiredTables()
//
/// <summary>
/// Gets the tables required for populating the vertex worksheet.
/// </summary>
///
/// <param name="oWorkbook">
/// Workbook containing the graph data.
/// </param>
///
/// <param name="oEdgeTable">
/// Where the edge table gets stored.
/// </param>
///
/// <param name="oVertexTable">
/// Where the vertex table gets stored.
/// </param>
///
/// <remarks>
/// This method checks for tables and table columns that are required for
/// vertex worksheet population.
///
/// <para>
/// If there is a problem with the workbook, a <see
/// cref="WorkbookFormatException" /> is thrown.
/// </para>
///
/// </remarks>
//*************************************************************************
protected void 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()
) );
}
}
//*************************************************************************
// Method: ReadEdgeTable()
//
/// <summary>
/// Reads the edge table and populates a dictionary with unique vertex
/// names.
/// </summary>
///
/// <param name="oEdgeTable">
/// Edge table.
/// </param>
///
/// <param name="oVertexNameDictionary">
/// Dictionary to populate. The key is the vertex name and the value is
/// not used.
/// </param>
//*************************************************************************
protected void ReadEdgeTable(
ListObject oEdgeTable,
Dictionary<String, Char> oVertexNameDictionary
)
{
Debug.Assert(oEdgeTable != null);
Debug.Assert(oVertexNameDictionary != null);
Debug.Assert(oVertexNameDictionary.Count == 0);
AssertValid();
// Get the vertex name column ranges.
Range oVertex1NameRange, oVertex2NameRange;
if ( !ExcelUtil.TryGetTableColumnData(oEdgeTable,
EdgeTableColumnNames.Vertex1Name, out oVertex1NameRange)
||
!ExcelUtil.TryGetTableColumnData(oEdgeTable,
EdgeTableColumnNames.Vertex2Name, out oVertex2NameRange)
)
{
return;
}
Int32 iRows = oVertex1NameRange.Rows.Count;
Debug.Assert(oVertex2NameRange.Rows.Count == iRows);
// Read the vertex names all at once.
Object [,] aoVertex1NameValues =
ExcelUtil.GetRangeValues(oVertex1NameRange);
Object [,] aoVertex2NameValues =
ExcelUtil.GetRangeValues(oVertex2NameRange);
// Loop through the edges.
for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++)
{
// Get the vertex names and add them to the dictionary. Use
// Dictionary.Item() instead of Dictionary.Add() to allow for
// duplicate vertex names.
String sVertex1Name, sVertex2Name;
if ( ExcelUtil.TryGetNonEmptyStringFromCell(aoVertex1NameValues,
iRowOneBased, 1, out sVertex1Name) )
{
oVertexNameDictionary[sVertex1Name] = ' ';
}
if ( ExcelUtil.TryGetNonEmptyStringFromCell(aoVertex2NameValues,
iRowOneBased, 1, out sVertex2Name) )
{
oVertexNameDictionary[sVertex2Name] = ' ';
}
}
}
TryGetOrAddTableColumn
(
ListObject table,
String columnName,
Double columnWidthChars,
String columnStyle,
out ListColumn listColumn
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Debug.Assert(columnWidthChars == AutoColumnWidth ||
columnWidthChars >= 0);
return (
TryGetTableColumn(table, columnName, out listColumn)
||
TryAddTableColumn(table, columnName, columnWidthChars, columnStyle,
out listColumn)
);
}
//*************************************************************************
// Method: ImportEdges()
//
/// <summary>
/// Imports edges and their attributes from a graph to the edge worksheet.
/// </summary>
///
/// <param name="oSourceGraph">
/// Graph to import the edges from.
/// </param>
///
/// <param name="asEdgeAttributes">
/// Array of edge attribute names that have been added to the metadata of
/// the graph's edges. Can be null.
/// </param>
///
/// <param name="oEdgeTable">
/// Edge table the edges will be imported to.
/// </param>
///
/// <param name="oVertex1NameColumnData">
/// Data body range of the vertex 1 name column.
/// </param>
///
/// <param name="oVertex2NameColumnData">
/// Data body range of the vertex 2 name column.
/// </param>
///
/// <param name="bAppendToTable">
/// true to append the edges to any edges already in the edge table, false
/// to overwrite any edges.
/// </param>
//*************************************************************************
protected void ImportEdges(
IGraph oSourceGraph,
String [] asEdgeAttributes,
ListObject oEdgeTable,
Range oVertex1NameColumnData,
Range oVertex2NameColumnData,
Boolean bAppendToTable
)
{
Debug.Assert(oSourceGraph != null);
Debug.Assert(oEdgeTable != null);
Debug.Assert(oVertex1NameColumnData != null);
Debug.Assert(oVertex2NameColumnData != null);
AssertValid();
Int32 iRowOffsetToWriteTo = 0;
if (bAppendToTable)
{
iRowOffsetToWriteTo =
ExcelUtil.GetOffsetOfFirstEmptyTableRow(oEdgeTable);
ExcelUtil.OffsetRange(ref oVertex1NameColumnData,
iRowOffsetToWriteTo, 0);
ExcelUtil.OffsetRange(ref oVertex2NameColumnData,
iRowOffsetToWriteTo, 0);
}
Range [] aoEdgeAttributeColumnData = null;
Object [][,] aaoEdgeAttributeValues = null;
Int32 iEdgeAttributes = 0;
IEdgeCollection oEdges = oSourceGraph.Edges;
Int32 iEdges = oEdges.Count;
// Create vertex name and edge attribute arrays that will be written to
// the edge table.
Object [,] aoVertex1NameValues =
ExcelUtil.GetSingleColumn2DArray(iEdges);
Object [,] aoVertex2NameValues =
ExcelUtil.GetSingleColumn2DArray(iEdges);
if (asEdgeAttributes != null)
{
iEdgeAttributes = asEdgeAttributes.Length;
aoEdgeAttributeColumnData = new Range[iEdgeAttributes];
aaoEdgeAttributeValues = new Object[iEdgeAttributes][,];
ListColumn oEdgeAttributeColumn;
Range oEdgeAttributeColumnData;
for (Int32 i = 0; i < iEdgeAttributes; i++)
{
String sEdgeAttribute = asEdgeAttributes[i];
if (
!ExcelUtil.TryGetOrAddTableColumn(oEdgeTable,
sEdgeAttribute, ExcelUtil.AutoColumnWidth, null,
out oEdgeAttributeColumn)
||
!ExcelUtil.TryGetTableColumnData(oEdgeAttributeColumn,
out oEdgeAttributeColumnData)
)
{
throw new WorkbookFormatException(
"The " + sEdgeAttribute + " column couldn't be added."
);
}
if (bAppendToTable)
{
ExcelUtil.OffsetRange(ref oEdgeAttributeColumnData,
iRowOffsetToWriteTo, 0);
}
aoEdgeAttributeColumnData[i] = oEdgeAttributeColumnData;
aaoEdgeAttributeValues[i] =
ExcelUtil.GetSingleColumn2DArray(iEdges);
}
}
// Fill in the vertex name and edge attribute arrays.
Int32 iEdge = 1;
foreach (IEdge oEdge in oEdges)
{
IVertex [] aoVertices = oEdge.Vertices;
aoVertex1NameValues[iEdge, 1] = aoVertices[0].Name;
aoVertex2NameValues[iEdge, 1] = aoVertices[1].Name;
Object oEdgeAttribute;
for (Int32 i = 0; i < iEdgeAttributes; i++)
{
if ( oEdge.TryGetValue(asEdgeAttributes[i],
out oEdgeAttribute) )
{
aaoEdgeAttributeValues[i][iEdge, 1] =
RemoveFormulaFromAttribute(oEdgeAttribute);
}
}
iEdge++;
}
// Write the vertex name and edge attribute arrays to the table.
ExcelUtil.SetRangeValues( (Range)oVertex1NameColumnData.Cells[1, 1],
aoVertex1NameValues );
ExcelUtil.SetRangeValues( (Range)oVertex2NameColumnData.Cells[1, 1],
aoVertex2NameValues );
for (Int32 i = 0; i < iEdgeAttributes; i++)
{
ExcelUtil.SetRangeValues(
(Range)aoEdgeAttributeColumnData[i].Cells[1, 1],
aaoEdgeAttributeValues[i] );
}
}
public void DifferentListObjectsAreNotEqual()
{
// Arrange
var listObject1 = new ListObject(Many<string>().ToArray());
var listObject2 = new ListObject(Many<string>().ToArray());
// Assert
listObject1.GetHashCode().Should().NotBe(listObject2.GetHashCode(), "hash code");
listObject1.Equals(listObject2).Should().BeFalse("Equals");
(listObject1 == listObject2).Should().BeFalse("==");
}
//*************************************************************************
// Method: ImportVertexAttributes()
//
/// <summary>
/// Imports attributes from the graph's vertices to the vertex worksheet.
/// </summary>
///
/// <param name="oSourceGraph">
/// Graph to import the edges from.
/// </param>
///
/// <param name="asVertexAttributes">
/// Array of vertex attribute names that have been added to the metadata of
/// the graph's vertices. Can't be null.
/// </param>
///
/// <param name="oVertexDictionary">
/// The key is a vertex name and the value is the one-based row offset.
/// </param>
///
/// <param name="oVertexTable">
/// Vertex table the vertices will be imported to.
/// </param>
//*************************************************************************
protected void ImportVertexAttributes(
IGraph oSourceGraph,
String [] asVertexAttributes,
Dictionary<String, Int32> oVertexDictionary,
ListObject oVertexTable
)
{
Debug.Assert(oSourceGraph != null);
Debug.Assert(asVertexAttributes != null);
Debug.Assert(oVertexDictionary != null);
Debug.Assert(oVertexTable != null);
AssertValid();
// Create vertex attribute arrays that will be written to the vertex
// table.
Int32 iVertexAttributes = asVertexAttributes.Length;
Range [] aoVertexAttributeColumnData = new Range[iVertexAttributes];
Object [][,] aaoVertexAttributeValues =
new Object[iVertexAttributes][,];
ListColumn oVertexAttributeColumn;
Range oVertexAttributeColumnData;
for (Int32 i = 0; i < iVertexAttributes; i++)
{
String sVertexAttribute = asVertexAttributes[i];
if (
!ExcelUtil.TryGetOrAddTableColumn(oVertexTable,
sVertexAttribute, ExcelUtil.AutoColumnWidth, null,
out oVertexAttributeColumn)
||
!ExcelUtil.TryGetTableColumnData(oVertexAttributeColumn,
out oVertexAttributeColumnData)
)
{
throw new WorkbookFormatException( String.Format(
"The {0} column couldn't be added."
,
sVertexAttribute
) );
}
aoVertexAttributeColumnData[i] = oVertexAttributeColumnData;
aaoVertexAttributeValues[i] =
ExcelUtil.GetRangeValues(oVertexAttributeColumnData);
}
foreach (IVertex oVertex in oSourceGraph.Vertices)
{
String sVertexName = oVertex.Name;
Object oVertexAttribute;
Int32 iOneBasedRowOffset;
if ( !oVertexDictionary.TryGetValue(sVertexName,
out iOneBasedRowOffset) )
{
Debug.Assert(false);
}
for (Int32 i = 0; i < iVertexAttributes; i++)
{
if ( oVertex.TryGetValue(asVertexAttributes[i],
out oVertexAttribute) )
{
Debug.Assert(iOneBasedRowOffset >= 1);
Debug.Assert( iOneBasedRowOffset <=
aaoVertexAttributeValues[i].GetUpperBound(0) );
aaoVertexAttributeValues[i][iOneBasedRowOffset, 1] =
RemoveFormulaFromAttribute(oVertexAttribute);
}
}
}
for (Int32 i = 0; i < iVertexAttributes; i++)
{
aoVertexAttributeColumnData[i].set_Value( Missing.Value,
aaoVertexAttributeValues[i] );
}
}
//*************************************************************************
// Method: AutoFillEdgeTable()
//
/// <summary>
/// Runs the application's AutoFill feature on the edge table.
/// </summary>
///
/// <param name="oEdgeTable">
/// The table to autofill.
/// </param>
///
/// <param name="oAutoFillUserSettings">
/// Specifies one or more source-to-destination column mappings.
/// </param>
///
/// <param name="oAutoFillWorkbookResults">
/// Stores the autofill results.
/// </param>
//*************************************************************************
private static void AutoFillEdgeTable(
ListObject oEdgeTable,
AutoFillUserSettings oAutoFillUserSettings,
AutoFillWorkbookResults oAutoFillWorkbookResults
)
{
Debug.Assert(oEdgeTable != null);
Debug.Assert(oAutoFillUserSettings != null);
Debug.Assert(oAutoFillWorkbookResults != null);
Double dSourceCalculationNumber1, dSourceCalculationNumber2;
Int32 iDecimalPlaces;
if ( TryAutoFillColorColumn(oEdgeTable,
oAutoFillUserSettings.EdgeColorSourceColumnName,
EdgeTableColumnNames.Color,
oAutoFillUserSettings.EdgeColorDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.EdgeColorResults =
new AutoFillColorColumnResults(
oAutoFillUserSettings.EdgeColorSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.EdgeColorDetails.DestinationColor1,
oAutoFillUserSettings.EdgeColorDetails.DestinationColor2
);
}
if ( TryAutoFillNumericRangeColumn(oEdgeTable,
oAutoFillUserSettings.EdgeWidthSourceColumnName,
EdgeTableColumnNames.Width,
oAutoFillUserSettings.EdgeWidthDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.EdgeWidthResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.EdgeWidthSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.EdgeWidthDetails.DestinationNumber1,
oAutoFillUserSettings.EdgeWidthDetails.DestinationNumber2
);
}
if ( TryAutoFillNumericRangeColumn(oEdgeTable,
oAutoFillUserSettings.EdgeAlphaSourceColumnName,
CommonTableColumnNames.Alpha,
oAutoFillUserSettings.EdgeAlphaDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.EdgeAlphaResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.EdgeAlphaSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.EdgeAlphaDetails.DestinationNumber1,
oAutoFillUserSettings.EdgeAlphaDetails.DestinationNumber2
);
}
AutoFillNumericComparisonColumn(oEdgeTable,
oAutoFillUserSettings.EdgeVisibilitySourceColumnName,
CommonTableColumnNames.Visibility,
oAutoFillUserSettings.EdgeVisibilityDetails
);
AutoFillColumnViaCopy(oEdgeTable,
oAutoFillUserSettings.EdgeLabelSourceColumnName,
EdgeTableColumnNames.Label
);
}
//*************************************************************************
// Method: ImportVertices()
//
/// <summary>
/// Imports vertices and their attributes from a graph to the vertex
/// worksheet.
/// </summary>
///
/// <param name="oSourceGraph">
/// Graph to import the edges from.
/// </param>
///
/// <param name="asVertexAttributes">
/// Array of vertex attribute names that have been added to the metadata of
/// the graph's vertices. Can be null.
/// </param>
///
/// <param name="oVertexTable">
/// Vertex table the vertices will be imported to.
/// </param>
///
/// <param name="oVertexNameColumnData">
/// Data body range of the vertex name column.
/// </param>
///
/// <param name="oVisibilityColumnData">
/// Data body range of the vertex visibility column.
/// </param>
//*************************************************************************
protected void ImportVertices(
IGraph oSourceGraph,
String [] asVertexAttributes,
ListObject oVertexTable,
Range oVertexNameColumnData,
Range oVisibilityColumnData
)
{
Debug.Assert(oSourceGraph != null);
Debug.Assert(oVertexTable != null);
Debug.Assert(oVertexNameColumnData != null);
Debug.Assert(oVisibilityColumnData != null);
AssertValid();
// Create a dictionary that maps vertex names to row numbers in the
// vertex worksheet.
Dictionary<String, Int32> oVertexDictionary =
new Dictionary<String, Int32>();
Object [,] aoVertexNameValues =
ExcelUtil.GetRangeValues(oVertexNameColumnData);
Int32 iRows = oVertexNameColumnData.Rows.Count;
if (iRows == 1 && aoVertexNameValues[1, 1] == null)
{
// Range.get_Value() (and therefore ExcelUtil.GetRangeValues())
// returns a single null cell when the table is empty. Work around
// this.
iRows = 0;
}
for (Int32 iRowOneBased = 1; iRowOneBased <= iRows; iRowOneBased++)
{
String sVertexName;
if ( ExcelUtil.TryGetNonEmptyStringFromCell(aoVertexNameValues,
iRowOneBased, 1, out sVertexName) )
{
oVertexDictionary[sVertexName] = iRowOneBased;
}
}
aoVertexNameValues = null;
// Create a list of vertices not already included in the vertex table.
// This can occur when the graph has isolated vertices.
List<String> oIsolatedVertexNames = new List<String>();
foreach (IVertex oVertex in oSourceGraph.Vertices)
{
String sVertexName = oVertex.Name;
if ( !oVertexDictionary.ContainsKey(sVertexName) )
{
oIsolatedVertexNames.Add(sVertexName);
}
}
Int32 iIsolatedVertices = oIsolatedVertexNames.Count;
if (iIsolatedVertices > 0)
{
// Append the isolated vertices to the table. The vertex
// visibilities should be set to Show to force them to be shown
// even though they are not included in edges.
String [,] asAddedVertexNameValues =
new String [iIsolatedVertices, 1];
String [,] asAddedVisibilityValues =
new String [iIsolatedVertices, 1];
String sShow = ( new VertexVisibilityConverter() ).GraphToWorkbook(
VertexWorksheetReader.Visibility.Show);
for (Int32 i = 0; i < iIsolatedVertices; i++)
{
String sIsolatedVertexName = oIsolatedVertexNames[i];
asAddedVertexNameValues[i, 0] = sIsolatedVertexName;
asAddedVisibilityValues[i, 0] = sShow;
oVertexDictionary[sIsolatedVertexName] = iRows + i + 1;
}
ExcelUtil.SetRangeValues(
oVertexNameColumnData.get_Offset(iRows, 0),
asAddedVertexNameValues);
ExcelUtil.SetRangeValues(
oVisibilityColumnData.get_Offset(iRows, 0),
asAddedVisibilityValues);
}
if (asVertexAttributes != null)
{
ImportVertexAttributes(oSourceGraph, asVertexAttributes,
oVertexDictionary, oVertexTable);
}
}
//*************************************************************************
// Method: AutoFillVertexTable()
//
/// <summary>
/// Runs the application's AutoFill feature on the vertex table.
/// </summary>
///
/// <param name="oVertexTable">
/// The table to autofill.
/// </param>
///
/// <param name="oAutoFillUserSettings">
/// Specifies one or more source-to-destination column mappings.
/// </param>
///
/// <param name="oAutoFillWorkbookResults">
/// Stores the autofill results.
/// </param>
//*************************************************************************
private static void AutoFillVertexTable(
ListObject oVertexTable,
AutoFillUserSettings oAutoFillUserSettings,
AutoFillWorkbookResults oAutoFillWorkbookResults
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oAutoFillUserSettings != null);
Debug.Assert(oAutoFillWorkbookResults != null);
Double dSourceCalculationNumber1, dSourceCalculationNumber2;
Int32 iDecimalPlaces;
if ( TryAutoFillColorColumn(oVertexTable,
oAutoFillUserSettings.VertexColorSourceColumnName,
VertexTableColumnNames.Color,
oAutoFillUserSettings.VertexColorDetails,
out dSourceCalculationNumber1,
out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.VertexColorResults =
new AutoFillColorColumnResults(
oAutoFillUserSettings.VertexColorSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.VertexColorDetails.DestinationColor1,
oAutoFillUserSettings.VertexColorDetails.DestinationColor2
);
}
AutoFillNumericComparisonColumn(oVertexTable,
oAutoFillUserSettings.VertexShapeSourceColumnName,
VertexTableColumnNames.Shape,
oAutoFillUserSettings.VertexShapeDetails
);
if ( TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexRadiusSourceColumnName,
VertexTableColumnNames.Radius,
oAutoFillUserSettings.VertexRadiusDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.VertexRadiusResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.VertexRadiusSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.VertexRadiusDetails.
DestinationNumber1,
oAutoFillUserSettings.VertexRadiusDetails.
DestinationNumber2
);
}
if ( TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexAlphaSourceColumnName,
CommonTableColumnNames.Alpha,
oAutoFillUserSettings.VertexAlphaDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
) )
{
oAutoFillWorkbookResults.VertexAlphaResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.VertexAlphaSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.VertexAlphaDetails.DestinationNumber1,
oAutoFillUserSettings.VertexAlphaDetails.DestinationNumber2
);
}
AutoFillColumnViaCopy(oVertexTable,
oAutoFillUserSettings.VertexLabelSourceColumnName,
VertexTableColumnNames.Label
);
TryAutoFillColorColumn(oVertexTable,
oAutoFillUserSettings.VertexLabelFillColorSourceColumnName,
VertexTableColumnNames.LabelFillColor,
oAutoFillUserSettings.VertexLabelFillColorDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
);
AutoFillColumnViaCopy(oVertexTable,
oAutoFillUserSettings.VertexToolTipSourceColumnName,
VertexTableColumnNames.ToolTip
);
AutoFillNumericComparisonColumn(oVertexTable,
oAutoFillUserSettings.VertexVisibilitySourceColumnName,
CommonTableColumnNames.Visibility,
oAutoFillUserSettings.VertexVisibilityDetails
);
TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexLayoutOrderSourceColumnName,
VertexTableColumnNames.LayoutOrder,
oAutoFillUserSettings.VertexLayoutOrderDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
);
Boolean bXAutoFilled = TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexXSourceColumnName,
VertexTableColumnNames.X,
oAutoFillUserSettings.VertexXDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
);
Double dYSourceCalculationNumber1, dYSourceCalculationNumber2;
Boolean bYAutoFilled = TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexYSourceColumnName,
VertexTableColumnNames.Y,
oAutoFillUserSettings.VertexYDetails,
out dYSourceCalculationNumber1, out dYSourceCalculationNumber2,
out iDecimalPlaces
);
if (bXAutoFilled && bYAutoFilled)
{
// (Note that the decimal places for the Y column take precedence
// here.)
oAutoFillWorkbookResults.VertexXResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.VertexXSourceColumnName,
dSourceCalculationNumber1, dSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.VertexXDetails.DestinationNumber1,
oAutoFillUserSettings.VertexXDetails.DestinationNumber2
);
oAutoFillWorkbookResults.VertexYResults =
new AutoFillNumericRangeColumnResults(
oAutoFillUserSettings.VertexYSourceColumnName,
dYSourceCalculationNumber1, dYSourceCalculationNumber2,
iDecimalPlaces,
oAutoFillUserSettings.VertexYDetails.DestinationNumber1,
oAutoFillUserSettings.VertexYDetails.DestinationNumber2
);
}
TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexPolarRSourceColumnName,
VertexTableColumnNames.PolarR,
oAutoFillUserSettings.VertexPolarRDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
);
TryAutoFillNumericRangeColumn(oVertexTable,
oAutoFillUserSettings.VertexPolarAngleSourceColumnName,
VertexTableColumnNames.PolarAngle,
oAutoFillUserSettings.VertexPolarAngleDetails,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
);
}
TryGetVisibleTableColumnData
(
ListObject table,
String columnName,
out Range visibleTableColumnData
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
visibleTableColumnData = null;
Range oTempRange;
return (
TryGetTableColumnData(table, columnName, out oTempRange)
&&
TryGetVisibleRange(oTempRange, out visibleTableColumnData)
);
}
TryGetTableColumnData
(
ListObject table,
String columnName,
out Range tableColumnData
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
tableColumnData = null;
ListColumn oColumn;
return (
TryGetTableColumn(table, columnName, out oColumn)
&&
TryGetTableColumnData(oColumn, out tableColumnData)
);
}
TryClearTableColumnDataContents
(
ListObject table,
String columnName
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Range oTableColumnData;
if (!TryGetTableColumnData(table, columnName, out oTableColumnData))
{
return (false);
}
oTableColumnData.ClearContents();
return (true);
}
