Exemple #1
0
 //Сдигает выделение после установки ссылки, isValue - производится запись свойства в ячейку, иначе установка ссылки
 public void MoveAfterLink()
 {
     try
     {
         var   acell = GeneralRep.Application.ActiveCell;
         Range rcell = null;
         int   d;
         if (int.TryParse(NextCellStep.Text, out d))
         {
             if (NextCellShift.Text == "Вправо")
             {
                 rcell = (Range)acell.Worksheet.Cells[acell.Row, acell.Column + d];
             }
             if (NextCellShift.Text == "Вниз")
             {
                 rcell = (Range)acell.Worksheet.Cells[acell.Row + d, acell.Column];
             }
             if (rcell != null)
             {
                 rcell.Activate();
             }
         }
         else
         {
             Different.MessageError("Недопустимое значение шага перехода после установки ссылки: " + NextCellStep.Text);
         }
     }
     catch { }
 }
Exemple #2
0
        private void MoveToKeyRow(Int32 keyRow)
        {
            XlHlp.DisplayInWatchWindow(string.Format("{0}",
                                                     System.Reflection.MethodInfo.GetCurrentMethod().Name));

            Microsoft.Office.Interop.Excel.Application app = Globals.ThisAddIn.Application;
            Worksheet ws = app.ActiveSheet;

            string currentTableName = GetCurrentTable(ws);

            try
            {
                ListObject currentTable = ws.ListObjects[currentTableName];

                // Put the rows back in ID order.

                //currentTable.Range.Sort(Key1: currentTable.ListColumns[LogColumns.ID].Range);

                currentTable.Sort.SortFields.Clear();

                currentTable.Sort.SortFields.Add(
                    Key: currentTable.ListColumns[1].Range,
                    SortOn: XlSortOn.xlSortOnValues,
                    Order: XlSortOrder.xlAscending,
                    DataOption: XlSortDataOption.xlSortNormal);

                currentTable.Sort.Apply();

                // And move to the key row
                Range targetCell = ws.Cells[keyRow, LogColumns.Duration];
                targetCell.Activate();
                targetCell.Interior.Pattern = XlPattern.xlPatternSolid;
                targetCell.Interior.Color   = System.Drawing.Color.Yellow;
                //targetCell.Interior.Color = Colors.Yellow;
                targetCell.Interior.PatternColor = System.Drawing.Color.Yellow;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Exemple #3
0
        /// <summary>
        /// Recherche le texte à l'intérieur des forumules et retourne l'adresse de la cellule du type A1.
        /// La recherche débute aprés le Range Actif, ce qui est parfois générateur de bug.
        /// </summary>
        /// <param name="TexteATrouver"></param>
        /// <returns></returns>
        public string ChercherSuivant(string TexteATrouver)
        {
            object What = (object)TexteATrouver;

            if (_MonRange.Cells.Count != 1)
            {
                this.SelectionRange("A1", "A1");
            }
            //fonction find
            _MonRange = _MaFeuille.Cells.Find(What, _MonRange, _M, XlLookAt.xlPart, XlSearchOrder.xlByColumns, XlSearchDirection.xlNext, (object)false, _M, (object)false);

            if (_MonRange == null)
            {
                return("Recherche infructeuse");
            }
            else
            {
                //Active la céllule trouvée
                _MonRange.Activate();
                return(string.Format("L{0}C{1}", _MonRange.Row.ToString(), _MonRange.Column.ToString()));
            }
        }
Exemple #4
0
        /// <summary> 进行数据的重新排列 </summary>
        public void btnReArrange_Click(object sender, RibbonControlEventArgs e)
        {
            // ---------------------------- 确定Range的有效范围 ------------------------------------------
            Worksheet sht    = _app.ActiveSheet;
            Range     rgData = _app.Selection;

            rgData = rgData.Areas[1];
            Range  firstCell  = default(Range); // 有效区间中的左上角第一个单元
            Range  bottomCell = default(Range); // 有效区间中的左下角的那个单元
            Range  rbcell     = default(Range); // 有效区间中的右下角的那个单元
            int    SortedId   = 0;
            double interval   = 0;

            string[] strInterval_Id = EditBox_ReArrangeIntervalId.Text.Split(',');
            double.TryParse(strInterval_Id[0], out interval);
            int.TryParse(strInterval_Id[1], out SortedId);
            int startRow;

            //
            rbcell     = rgData.Ex_CornerCell(CornerIndex.BottomRight);
            bottomCell = rgData.Cells[rgData.Rows.Count, SortedId];
            firstCell  = rgData.Cells[1, 1];
            if (bottomCell.Value == null)
            {
                bottomCell = bottomCell.End[XlDirection.xlUp];
            }
            if (firstCell.Value == null)
            {
                firstCell = firstCell.End[XlDirection.xlDown];
            }
            rgData   = sht.Range[firstCell, sht.Cells[bottomCell.Row, rbcell.Column]];
            startRow = Convert.ToInt32(rgData.Cells[1, 1].Row);

            // ------------------------------------- 提取参数 -------------------------------------
            Range  rgIdColumn = rgData.Columns[SortedId];
            double startData  = 0;
            double endData    = 0;

            try
            {
                startData = double.Parse(EditBox_ReArrangeStart.Text);
            }
            catch (Exception ex)
            {
                try
                {
                    startData = DateTime.Parse(EditBox_ReArrangeStart.Text).ToOADate();
                }
                catch (Exception)
                {
                    try
                    {
                        startData = Convert.ToDouble(_app.WorksheetFunction.Min(rgIdColumn));
                        EditBox_ReArrangeStart.Text = Convert.ToString(startData);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("指定的数据列中的数据不能进行排序!" + "\r\n" +
                                        ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }
            try
            {
                endData = double.Parse(EditBox_ReArrangeEnd.Text);
            }
            catch (Exception ex)
            {
                try
                {
                    endData = DateTime.Parse(EditBox_ReArrangeEnd.Text).ToOADate();
                }
                catch (Exception)
                {
                    try
                    {
                        endData = Convert.ToDouble(_app.WorksheetFunction.Max(rgIdColumn));
                        EditBox_ReArrangeEnd.Text = Convert.ToString(endData);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("指定的数据列中的数据不能进行排序!" + "\r\n" +
                                        ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                }
            }

            //
            // 检查参数的正确性
            if (endData <= startData || interval == 0 || interval > endData - startData || SortedId == 0 ||
                SortedId > rgData.Columns.Count)
            {
                MessageBox.Show("指定的参数不正确!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            // 检查数据的有效性
            object[,] Value = rgData.Value2;

            SortedList <double, int> v_row = new SortedList <double, int>(); //每一个key代表此标志列的实际数据,对应的value代表此数据在指定的区间内的局部行号
            int r = 0;

            try
            {
                object v = null;
                for (r = 1; r <= Value.Length - 1; r++)
                {
                    v = Value[r, SortedId];
                    if ((v != null) && string.Compare("", v.ToString().Trim()) != 0)
                    {
                        v_row.Add((double)v, r);
                    }
                }
            }
            catch (Exception ex)
            {
                Range c = rgData.Cells[r, SortedId];
                MessageBox.Show("单元格 " + c.Address + " 的数据不符合规范,请检查。" + "\r\n" +
                                ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                c.Activate();
                return;
            }

            // ------------------------------------------ 开始重新排列数据 ------------------------------------------
            int RowsCount = 0;
            int ColsCount = rgData.Columns.Count;

            RowsCount = (int)(Math.Floor((endData - startData) / interval) + 1);

            object[,] arrResult = new object[RowsCount - 1 + 1, ColsCount - 1 + 1];
            var arrKey   = v_row.Keys;
            var arrValue = v_row.Values;
            int valueRow = 0;

            for (r = 0; r <= RowsCount - 1; r++)
            {
                int sourceR = (int)arrKey.IndexOf(startData + r * interval);
                if (sourceR >= 0)
                {
                    valueRow = Convert.ToInt32(arrValue[sourceR]); // 指定的数据在Excel区间中的行号
                    for (int c = 0; c <= ColsCount - 1; c++)
                    {
                        arrResult[r, c] = Value[valueRow, c + 1];
                    }
                }
            }
            // 将排列完成后的结果放置回Excel单元格中

            Range rgResult = sht.Range[firstCell, firstCell.Offset[RowsCount - 1, ColsCount - 1]];

            rgResult.Value = arrResult;
            rgResult.Columns[SortedId].NumberFormatLocal = rgData.Cells[1, SortedId].NumberFormatLocal; // 还原这一列的数值格式
            rgResult.Select();
        }
Exemple #5
0
        //*************************************************************************
        // Creates a Worksheet with table overview
        private void AddTableOverview(Worksheet tableOverviewWorksheet, Schema schema, List <string> priorities)
        {
            tableOverviewWorksheet.Name = "tables";
            tableOverviewWorksheet.Columns.AutoFit();

            List <string> columnNames = new List <string>()
            {
                "table",
                "folder",
                "schema",
                "rows",
                "columns",
                "priority",
                // "pri-sort",
                "entity",
                "description",
                "note"
            };

            foreach (string name in columnNames)
            {
                tableOverviewWorksheet.Cells[1, columnNames.IndexOf(name) + 1] = name;
            }
            //-------------------------------------------------------------------
            int count = 2;

            foreach (Table table in schema.Tables)
            {
                Console.WriteLine("Table: " + table.Name + ", Description: " + table.Description);
                GetEntity(table.Description, table);
                Console.WriteLine("Table: " + table.Name + ", Description2: " + table.Description);

                if (priorities.Contains(table.TablePriority))
                {
                    if (includeTables)
                    {
                        Range c1       = tableOverviewWorksheet.Cells[count, 1];
                        Range c2       = tableOverviewWorksheet.Cells[count, 1];
                        Range linkCell = tableOverviewWorksheet.get_Range(c1, c2);

                        Hyperlinks links = tableOverviewWorksheet.Hyperlinks;
                        links.Add(linkCell, "", GetNumbers(table.Folder) + "!A1", "", table.Name);

                        Marshal.ReleaseComObject(c1);
                        Marshal.ReleaseComObject(c2);
                        Marshal.ReleaseComObject(linkCell);
                        Marshal.ReleaseComObject(links);
                    }
                    else
                    {
                        tableOverviewWorksheet.Cells[count, 1] = table.Name;
                    }

                    tableOverviewWorksheet.Cells[count, 2] = table.Folder;
                    tableOverviewWorksheet.Cells[count, 3] = schema.Name;
                    tableOverviewWorksheet.Cells[count, 4] = table.Rows;
                    tableOverviewWorksheet.Cells[count, 5] = table.Columns.Count;

                    Console.WriteLine("Table priority: " + table.TablePriority + ", Rows: " + table.Rows);
                    if (string.IsNullOrEmpty(table.TablePriority) && table.Rows == 0)
                    {
                        table.TablePriority = "EMPTY";
                    }
                    tableOverviewWorksheet.Cells[count, 6] = table.TablePriority;

                    // Pri sort
                    //tableOverviewWorksheet.Cells[count, 7] = Globals.PriSort(table.TablePriority);
                    tableOverviewWorksheet.Cells[count, 7] = table.TableEntity;
                    tableOverviewWorksheet.Cells[count, 8] = table.Description;

                    // Note
                    tableOverviewWorksheet.Cells[count, 9] = "";

                    count++;
                }
            }

            // Freeze Panes
            Range range = tableOverviewWorksheet.Cells[2, 1];

            range.Activate();
            range.Application.ActiveWindow.FreezePanes = true;

            Range tempRng = tableOverviewWorksheet.Range["A1", "I1"];

            tempRng.Characters.Font.Bold = true;

            // Border lines
            for (int m = 1; m < count; m++)
            {
                for (int n = 1; n < 10; n++)
                {
                    tempRng = tableOverviewWorksheet.Cells[m, n];
                    tempRng.Borders[XlBordersIndex.xlEdgeLeft].LineStyle   = XlLineStyle.xlContinuous;
                    tempRng.Borders[XlBordersIndex.xlEdgeRight].LineStyle  = XlLineStyle.xlContinuous;
                    tempRng.Borders[XlBordersIndex.xlEdgeTop].LineStyle    = XlLineStyle.xlContinuous;
                    tempRng.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                }
            }

            // Cell background color
            for (int m = 1; m < count; m++)
            {
                tempRng = tableOverviewWorksheet.Cells[m, 6];
                tempRng.Interior.Color = Color.LightYellow;

                tempRng = tableOverviewWorksheet.Cells[m, 7];
                tempRng.Interior.Color = Color.LightGreen;

                tempRng = tableOverviewWorksheet.Cells[m, 8];
                tempRng.Interior.Color = Color.LightSkyBlue;

                tempRng = tableOverviewWorksheet.Cells[m, 9];
                tempRng.Interior.Color = Color.LightGray;
            }

            // Alignment
            tableOverviewWorksheet.Columns.HorizontalAlignment = XlHAlign.xlHAlignLeft;
            tableOverviewWorksheet.Columns.VerticalAlignment   = XlVAlign.xlVAlignCenter;

            // Column widths
            tableOverviewWorksheet.Columns["A:A"].AutoFit();
            tableOverviewWorksheet.Columns["B:B"].AutoFit();  // .ColumnWidth = 8;
            tableOverviewWorksheet.Columns["C:C"].AutoFit();  // .ColumnWidth = 8;

            tableOverviewWorksheet.Columns["D:D"].AutoFit();
            tableOverviewWorksheet.Columns["D:D"].HorizontalAlignment = XlHAlign.xlHAlignCenter;

            tableOverviewWorksheet.Columns["E:E"].AutoFit();
            tableOverviewWorksheet.Columns["E:E"].HorizontalAlignment = XlHAlign.xlHAlignCenter;

            tableOverviewWorksheet.Columns["F:F"].ColumnWidth         = 10;
            tableOverviewWorksheet.Columns["F:F"].HorizontalAlignment = XlHAlign.xlHAlignCenter;

            tableOverviewWorksheet.Columns["G:G"].ColumnWidth = 20;
            tableOverviewWorksheet.Columns["G:G"].WrapText    = true;

            tableOverviewWorksheet.Columns["H:H"].ColumnWidth = 60;
            tableOverviewWorksheet.Columns["H:H"].WrapText    = true;

            tableOverviewWorksheet.Columns["I:I"].ColumnWidth = 60;
            tableOverviewWorksheet.Columns["I:I"].WrapText    = true;

            // Column sorting
            tableOverviewWorksheet.Sort.SortFields.Clear();

            tableOverviewWorksheet.Sort.SortFields.Add(tableOverviewWorksheet.Range["F:F"], XlSortOn.xlSortOnValues, XlSortOrder.xlAscending, "HIGH, MEDIUM, LOW, SYSTEM, STATS, EMPTY, DUMMY", XlSortDataOption.xlSortNormal);
            tableOverviewWorksheet.Sort.SetRange(tableOverviewWorksheet.UsedRange);
            tableOverviewWorksheet.Sort.Header = XlYesNoGuess.xlYes;
            tableOverviewWorksheet.Sort.Apply();

            Marshal.ReleaseComObject(tableOverviewWorksheet);
        }
Exemple #6
0
 public void Goto()
 {
     cellLocation.Activate();
 }
Exemple #7
0
        public void Createreport(string sRDGFile, string sOutputFile)
        {
            StringBuilder sbDetails = new StringBuilder();

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            int     row = 2, col = 1;
            DataSet ds = new DataSet();

            ds.ReadXml(sRDGFile);

            System.Data.DataTable Groups = ds.Tables["properties"];
            System.Data.DataTable Servers = ds.Tables["server"];

            var query =
                from groups in Groups.AsEnumerable()
                join servers in Servers.AsEnumerable()
                on groups.Field <int?>("group_Id") equals
                servers.Field <int?>("group_Id")
                select new
            {
                GroupName =
                    groups.Field <string>("Name"),
                ServerName =
                    servers.Field <string>("Name"),
                ServerDisplayName =
                    servers.Field <string>("displayName")
            };


            if (xlApp == null)
            {
                Console.WriteLine("Excel is not properly installed!!");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbook  xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp.Visible = true;

            xlWorkBook  = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets[1];

            if (xlWorkSheet == null)
            {
                Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
            }
            //xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1]  = "Server Name";
            xlWorkSheet.Cells[1, 2]  = "Environment";
            xlWorkSheet.Cells[1, 3]  = "Release";
            xlWorkSheet.Cells[1, 4]  = "Server Type";
            xlWorkSheet.Cells[1, 5]  = "Legacy Status";
            xlWorkSheet.Cells[1, 6]  = "NextGen Status";
            xlWorkSheet.Cells[1, 7]  = "DXI Status";
            xlWorkSheet.Cells[1, 8]  = "Mobile Status";
            xlWorkSheet.Cells[1, 9]  = "TLMServices Status";
            xlWorkSheet.Cells[1, 10] = "Dispatcher";

            Range firstRow = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Rows[1];

            firstRow.Activate();
            firstRow.Select();
            firstRow.Font.Bold = true;
            firstRow.AutoFilter(1, Type.Missing,
                                Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

            //xlWorkBook.Worksheets.Add(xlWorkSheet);

            //xlWorkBook.SaveAs("C:\\Work\\SVN_Source\\Utilities\\RemoteDesktopConnectionManager\\CreateServerList\\CreateServerList\\bin\\Debug\\ServerList.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            //xlWorkBook.Save();


            foreach (var server in query)
            {
                col = 1;
                xlWorkSheet.Cells[row, col++] = server.ServerName;
                xlWorkSheet.Cells[row, col++] = server.GroupName.Contains("-") ? server.GroupName.Substring(0, server.GroupName.IndexOf("-")) : server.GroupName;
                xlWorkSheet.Cells[row, col++] = server.GroupName.Contains("-") ? server.GroupName.Substring(server.GroupName.IndexOf("-"),
                                                                                                            server.GroupName.Length - server.GroupName.IndexOf("-")).Replace("_", ".").Replace(",", " ").Replace("-", "").Trim() : string.Empty;

                ServerType eServerType = GetServerType(server.ServerDisplayName, server.ServerName);

                xlWorkSheet.Cells[row, col++] = eServerType == ServerType.UnKnown ? "" : eServerType.ToString();

                switch (eServerType)
                {
                case ServerType.Web:
                    col = 5;
                    break;

                case ServerType.Proc:
                    break;

                case ServerType.MP:
                    col = 7;
                    break;

                case ServerType.MPMax:
                    col = 7;
                    break;

                case ServerType.DXI:
                    col = 7;
                    break;

                case ServerType.DXM:
                    col = 8;
                    break;

                case ServerType.DXIR:
                    col = 7;
                    break;

                case ServerType.ES:
                    col = 10;
                    break;

                case ServerType.EventRouter:
                    col = 10;
                    break;

                case ServerType.REDIR:
                    col = 5;
                    break;

                case ServerType.v18All:
                    col = 5;
                    break;

                default:
                    break;
                }

                string[] strArry = CreateStatusLinks(eServerType, server.ServerName);
                if (strArry != null && strArry.Length > 0)
                {
                    foreach (string str in strArry)
                    {
                        if (str != "")
                        {
                            string[] strLinks = str.ToString().Split(',');
                            //string strLink = "=HYPERLINK(\"" + strLinks[1] + "\",\"\"Infragistics\"\")";
                            if (eServerType == ServerType.v18All)
                            {
                                if (col == 6)
                                {
                                    col = 7;
                                }
                                else if (col == 9)
                                {
                                    col = 10;
                                }
                            }
                            xlWorkSheet.Hyperlinks.Add(xlWorkSheet.Cells[row, col++], strLinks[1], misValue, misValue, strLinks[0]);
                        }
                    }
                }
                row++;
            }

            // xlWorkBook.SaveAs("C:\\Work\\SVN_Source\\Utilities\\RemoteDesktopConnectionManager\\CreateServerList\\CreateServerList\\bin\\Debug\\ServerList.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);


            //using (System.IO.StreamWriter file = new System.IO.StreamWriter(sOutputFile, true))
            //{
            //    file.WriteLine(sbDetails.ToString());
            //}

            ds.Clear();
        }