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);
        }
예제 #2
0
        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);
        }
예제 #3
0
    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);
    }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #6
0
        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);
            }
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
            }
        }
예제 #9
0
        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();
            }
        }
예제 #16
0
    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);
            }
        }
예제 #19
0
        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);
        }
예제 #20
0
        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);
        }
예제 #21
0
        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
                );
        }
예제 #22
0
        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
        }
예제 #23
0
        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);
        }
예제 #24
0
        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);
        }
예제 #25
0
        //*************************************************************************
        //  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();
        }
예제 #26
0
        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);
        }
예제 #27
0
        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);
            }
        }
예제 #28
0
        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.");
        }
예제 #29
0
        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);
        }
예제 #31
0
        /// <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);
            }
        }
예제 #32
0
        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);
    }
예제 #35
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        ActivateWorksheet
        (
            ListObject table
        )
        {
            Debug.Assert(table != null);

            Debug.Assert(table.Parent is Worksheet);

            ActivateWorksheet((Worksheet)table.Parent);
        }
예제 #36
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
        }
예제 #37
0
        //*************************************************************************
        //  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
            ) );
        }
예제 #38
0
        //*************************************************************************
        //  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
            );
        }
예제 #39
0
        //*************************************************************************
        //  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
            );
        }
예제 #40
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
            }
        }
예제 #41
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
        }
예제 #43
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
        }
예제 #44
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
                }
            }
        }
예제 #45
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
        }
예제 #46
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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());
        }
예제 #47
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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] = ' ';
            }
            }
        }
예제 #51
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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)
                );
        }
예제 #52
0
        //*************************************************************************
        //  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] );
            }
        }
예제 #53
0
        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("==");
        }
예제 #54
0
        //*************************************************************************
        //  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] );
            }
        }
예제 #55
0
        //*************************************************************************
        //  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
            );
        }
예제 #56
0
        //*************************************************************************
        //  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);
            }
        }
예제 #57
0
        //*************************************************************************
        //  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
            );
        }
예제 #58
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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)
                );
        }
예제 #59
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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)
                );
        }
예제 #60
0
파일: ExcelUtil.cs 프로젝트: cpatmoore/bio
        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);
        }