public override void PrepareWorksheetReadyForData()
        {
            Sheet.Name = "Largest Cells by Type";
            //
            Range r = null;

            //
            Utils.SetValue(Row, 1, Sheet, "Thread");
            r = Utils.SetValue(Row, 2, Sheet, "Largest Free Cell Size");
            r.AddComment(FileName1);
            r = Utils.SetValue(Row, 3, Sheet, "Largest Free Cell Size");
            r.AddComment(FileName2);
            Utils.SetValue(Row, 4, Sheet, "Delta");
            //
            Utils.SetValue(Row, 5, Sheet, " ");
            //
            r = Utils.SetValue(Row, 6, Sheet, "Largest Alloc Cell Size");
            r.AddComment(FileName1);
            r = Utils.SetValue(Row, 7, Sheet, "Largest Alloc Cell Size");
            r.AddComment(FileName2);
            Utils.SetValue(Row, 8, Sheet, "Delta");
            //
            Utils.MakeBoxedTitleRow(Row, 8, Sheet, 0xFF0000);
            //
            NextRow();
            NextRow();
        }
Beispiel #2
0
        private void MakeColumnTitleWithDelta(int aColumn, string aCaption)
        {
            Range r = null;

            //
            r = Utils.SetValue(Row, aColumn, Sheet, aCaption);
            r.AddComment(FileName1);
            r = Utils.SetValue(Row, aColumn + 1, Sheet, aCaption);
            r.AddComment(FileName2);
            Utils.SetValue(Row, aColumn + 2, Sheet, "Delta");
        }
Beispiel #3
0
        //Добавляет примечание comment в ячейку cell
        //tlist - список операций текущей транзакции, если не задан то транзакция создается из одной ячейки и добавляется в Transactions
        private void AddCellLink(string comment, Transaction tlist, Range cell)
        {
            string c = comment;

            if (cell.Comment != null)
            {
                var dicOld = cell.Comment.Text().ToPropertyDictionary();
                var dic    = comment.ToPropertyDictionary();
                if (dic.ContainsKey("NumPoints"))
                {
                    dic.Remove("NumPoints");
                }
                if (dicOld.ContainsKey("NumPoints"))
                {
                    dic.Add("NumPoints", dicOld["NumPoints"]);
                }
                c = dic.ToPropertyString();
            }
            var t = new TransactionCell(cell)
            {
                NewLink = c
            };

            cell.ClearComments();
            cell.AddComment(c);
            tlist.AddCell(t);
            GetCurrentLink(c, cell.Address.Replace("$", ""));
        }
Beispiel #4
0
        public override void PrepareWorksheetReadyForData()
        {
            Sheet.Name = "Slack Space";
            //
            Range r = null;

            //
            Utils.SetValue(Row, 1, Sheet, "Thread");
            r = Utils.SetValue(Row, 2, Sheet, "Slack Space");
            r.AddComment(FileName1);
            r = Utils.SetValue(Row, 3, Sheet, "Slack Space");
            r.AddComment(FileName2);
            Utils.SetValue(Row, 4, Sheet, "Delta");
            //
            Utils.MakeBoxedTitleRow(Row, 4, Sheet, 0xFF0000);
            //
            NextRow();
            NextRow();
        }
Beispiel #5
0
 public static void SetCellValue(Range rngR, object vntValue, XlHAlign horizontalAlignment = XlHAlign.xlHAlignLeft, string strComment = "")
 {
     //var _with12 = rngR;
     rngR.Value = vntValue;
     rngR.HorizontalAlignment = horizontalAlignment;
     if (!string.IsNullOrEmpty(strComment))
     {
         rngR.AddComment();
         rngR.Comment.Visible = false;
         rngR.Comment.Text(Text: strComment);
     }
 }
Beispiel #6
0
 /// <summary>
 /// 向单元格写入数据,对当前WorkSheet操作
 /// </summary>
 /// <param name="rowIndex">行索引</param>
 /// <param name="columnIndex">列索引</param>
 /// <param name="text">要写入的文本值</param>
 public void SetCellComment(int rowIndex, int columnIndex, string comment)
 {
     try
     {
         Range range = xlWorkSheet.Cells[rowIndex, columnIndex] as Range;
         range.AddComment(comment);
     }
     catch
     {
         this.KillExcelProcess(false);
         throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错!");
     }
 }
Beispiel #7
0
        private static void WriteQueryToExcelComment(Worksheet excelSheet, string daxQuery)
        {
            // Using lots of intermediate viarables so that we can release and COM RCW objects
            // see: http://badecho.com/2010/08/outlook-com-interop-and-reference-counting-or-how-i-learned-to-stop-worrying-and-love-the-rcw/
            const string cmtPrefix = "DAX Query:";
            Range        r         = excelSheet.Range["A1"];
            var          cmt       = r.AddComment(string.Format("{0}\n{1}", cmtPrefix, daxQuery));
            var          shp       = cmt.Shape;
            var          tf        = shp.TextFrame;
            var          c         = tf.Characters(cmtPrefix.Length);
            var          f         = c.Font;

            f.Bold = Office.MsoTriState.msoFalse;
        }
        internal static void ShowRefreshingComment(GoogleHistory history, string commnet = "Refreshing...")
        {
            ExcelUIThreadProtecter.CheckIsExcelUIMainThread();
            Worksheet ws          = ExcelApp.Application.ActiveSheet;
            Range     targetRange = MainThreadLogic.RangeManager.GetRange(ws, history.RangeName);

            if (targetRange != null)
            {
                targetRange = targetRange.Cells[1, 1];
                Comment refreshingComment = targetRange.Comment;
                if (refreshingComment == null)
                {
                    refreshingComment = targetRange.AddComment();
                }
                refreshingComment.Shape.TextFrame.AutoSize = true;
                refreshingComment.Shape.Fill.Visible       = Microsoft.Office.Core.MsoTriState.msoTrue;
                //refreshingComment.Shape.Fill.ForeColor.RGB =RGB(220, 220, 220);
                refreshingComment.Shape.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientDiagonalUp, 1, (float)0.4);
                refreshingComment.Visible = true;
                refreshingComment.Text(commnet);
            }
        }
Beispiel #9
0
        public static void BuildForm(Worksheet sheet, int colDepth)
        {
            int colCount      = 4;
            int colPerGroup   = 2;
            int XlSilverColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
            int XlBlackColor  = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
            int XlRedColor    = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            int XlYellowColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);

            sheet.UsedRange.Clear();
            sheet.UsedRange.ClearComments();
            sheet.UsedRange.Validation.Delete();

            // column group
            if (colDepth > 1)
            {
                colCount = Convert.ToInt32(Math.Pow(colPerGroup, colDepth));
                for (int r = 1; r < colDepth; r++)
                {
                    for (int c = 0; c < colCount; c++)
                    {
                        int groupCols = Convert.ToInt32(Math.Pow(colPerGroup, r));
                        if (c % groupCols == 0)
                        {
                            GetRange(sheet, colDepth - r, c + 2).Value = "그룹";
                            GetRange(sheet, colDepth - r, c + 2, 1, groupCols).Merge();
                        }
                    }
                }

                colCount++;
            }

            Range headerCells = GetRange(sheet, 1, 2, colDepth, colCount);

            headerCells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            headerCells.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            headerCells.Interior.Color      = XlSilverColor;
            headerCells.Font.Size           = 10;
            Borders borders = headerCells.Borders;

            borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle         = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeLeft].Color             = XlBlackColor;
            borders.Item[XlBordersIndex.xlEdgeRight].LineStyle        = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeRight].Color            = XlBlackColor;
            borders.Item[XlBordersIndex.xlEdgeTop].LineStyle          = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeTop].Color              = XlBlackColor;
            borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle       = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeBottom].Color           = XlBlackColor;
            borders.Item[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlInsideHorizontal].Color     = XlBlackColor;
            borders.Item[XlBordersIndex.xlInsideVertical].LineStyle   = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlInsideVertical].Color       = XlBlackColor;

            // column
            Range  stdCell = sheet.Range["B1"];
            double defSize = Utils.PixelToColumnWidth(DEF_WIDTH);

            for (var i = 0; i < colCount; i++)
            {
                GetRange(sheet, colDepth, 2 + i).Value = "컬럼" + (i + 1);
                Range nameCell = GetRange(sheet, colDepth + 1, 2 + i);
                nameCell.Value       = "column" + i;
                nameCell.ColumnWidth = defSize;
            }

            // left header
            int row          = 1 + colDepth;
            int fieldNameRow = row;
            int editTypeRow  = row + 1;
            int alignmentRow = row + 2;
            int editableRow  = row + 3;
            int numberFmtRow = row + 4;
            int dateFmtRow   = row + 5;
            int valuesRow    = row + 6;
            int labelsRow    = row + 7;
            int sizeRow      = row + 8;

            for (int p = 0; p < PROP_COUNT; p++)
            {
                Range r = GetRange(sheet, row + p, 1);
                r.Value     = PROP_TITLES[p];
                r.Font.Bold = true;
                if (p < 2)
                {
                    r.Font.Color = XlRedColor;
                }
                else if (p == 6)
                {
                    r.AddComment("드랍다운 또는 복수선택일 경우\n항목들의 값을 쉼표(,)로 나누어 입력\n예)A,B,C");
                }
                else if (p == 7)
                {
                    r.AddComment("드랍다운 또는 복수선택일 경우\n항목들의 라벨을 쉼표(,)로 나누어 입력\n미입력시 값이 표시");
                }
                else if (p == 8)
                {
                    r.AddComment("너비의 값이 있으면 입력된 숫자값을 사용, 없으면 시트상의 컬럼의 너비를 사용");
                }
            }

            Range bodyCells = GetRange(sheet, row, 2, PROP_COUNT, colCount);

            bodyCells.Interior.Color = XlYellowColor;
            bodyCells.Font.Size      = 10;
            borders = bodyCells.Borders;
            borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle         = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeLeft].Color             = XlBlackColor;
            borders.Item[XlBordersIndex.xlEdgeRight].LineStyle        = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeRight].Color            = XlBlackColor;
            borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle       = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlEdgeBottom].Color           = XlBlackColor;
            borders.Item[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlInsideHorizontal].Color     = XlSilverColor;
            borders.Item[XlBordersIndex.xlInsideVertical].LineStyle   = XlLineStyle.xlContinuous;
            borders.Item[XlBordersIndex.xlInsideVertical].Color       = XlSilverColor;

            Range typeCells = GetRange(sheet, editTypeRow, 2, 1, colCount);

            typeCells.Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, String.Join(",", TYPE_LABELS));
            typeCells.Value = TYPE_LABELS[0];

            Range alignCells = GetRange(sheet, alignmentRow, 2, 1, colCount);

            alignCells.Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, String.Join(",", ALIGN_LABELS));
            alignCells.Value = ALIGN_LABELS[1];

            Range editableCells = GetRange(sheet, editableRow, 2, 1, colCount);

            editableCells.Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, String.Join(",", EDIT_LABELS));
            editableCells.Value = EDIT_LABELS[0];

            Range numberCells = GetRange(sheet, numberFmtRow, 2, 1, colCount);

            numberCells.Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, String.Join(",", NFMT_LABELS));
            numberCells.Value = NFMT_LABELS[0];

            Range dateCells = GetRange(sheet, dateFmtRow, 2, 1, colCount);

            dateCells.Validation.Add(XlDVType.xlValidateList, Type.Missing, Type.Missing, String.Join(",", DFMT_LABELS));
            dateCells.Value = NFMT_LABELS[0];

            Range sizeCells = GetRange(sheet, sizeRow, 2, 1, colCount);

            sizeCells.Validation.Delete();
            sizeCells.Validation.Add(XlDVType.xlValidateWholeNumber,
                                     XlDVAlertStyle.xlValidAlertStop,
                                     XlFormatConditionOperator.xlBetween,
                                     10, 10000);
        }
Beispiel #10
0
        public void AddTestCasesResult()
        {
            //foreach (clientList ClientList in ExecutionSession.lstClient)
            //{
            foreach (ExecutedTestCase testCase in ExecutionSession.lstExecutedTestCases)
            {
                string tcNameRange = string.Format("C{0}:E{0}", currentRowNo);
                string rowRange    = string.Format("A{0}:I{0}", currentRowNo);
                workSheet_range = xlWorkSheet.get_Range(tcNameRange, System.Type.Missing);
                workSheet_range.Merge(System.Type.Missing);
                String[]    HostName     = testCase.clientUrl.Split('/');
                IPAddress[] ip_Addresses = Dns.GetHostAddresses(HostName[2].ToString());
                xlWorkSheet.Cells[currentRowNo, 1] = testCase.clientUrl + " (" + ip_Addresses[0].ToString() + ")";
                xlWorkSheet.Cells[currentRowNo, 2] = testCaseNo;
                Range     range     = xlWorkSheet.get_Range("C" + currentRowNo.ToString(), System.Type.Missing);
                Hyperlink hyperlink = (Hyperlink)range.Hyperlinks.Add(range, testCase.TestCaseResultUrl,
                                                                      System.Type.Missing, System.Type.Missing, testCase.TestCaseName);
                string browser = Enum.GetName(typeof(Browser), testCase.Browser);
                if (HelperClass.runMultipleBrowsers || HelperClass.runAllBrowsers)
                {
                    browser = "Multiple Browsers";
                }
                xlWorkSheet.Cells[currentRowNo, 8] = browser;
                xlWorkSheet.Cells[currentRowNo, 9] = Enum.GetName(typeof(Priority), testCase.Priority);
                switch (testCase.Status)
                {
                case OverAllResult.PASS:
                    xlWorkSheet.Cells[currentRowNo, 6] = Enum.GetName(typeof(OverAllResult), testCase.Status);
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Bold  = true;
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Green);
                    break;

                case OverAllResult.FAIL:
                    xlWorkSheet.Cells[currentRowNo, 6] = Enum.GetName(typeof(OverAllResult), testCase.Status);
                    Range CommentRange = xlWorkSheet.Cells[currentRowNo, 6];
                    xlWorkSheet.Cells[currentRowNo, 7] = testCase.FailedStep;
                    CommentRange.AddComment(testCase.FailedStep);
                    float val1 = 2.001f;
                    float val2 = 2.001f;
                    CommentRange.Comment.Shape.ScaleWidth(val2, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
                    CommentRange.Comment.Shape.ScaleHeight(val1, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Bold  = true;
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Red);
                    //  xlWorkSheet.get_Range("G" + currentRowNo.ToString(), System.Type.Missing).WrapText = false;
                    break;

                case OverAllResult.WARNING:
                    xlWorkSheet.Cells[currentRowNo, 6] = Enum.GetName(typeof(OverAllResult), testCase.Status);
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Bold  = true;
                    xlWorkSheet.get_Range("F" + currentRowNo.ToString(), System.Type.Missing).Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Brown);
                    break;
                }

                workSheet_range = xlWorkSheet.get_Range(rowRange, System.Type.Missing);
                workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                workSheet_range = xlWorkSheet.get_Range(tcNameRange, System.Type.Missing);
                workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                testCaseNo++;
                currentRowNo++;
            }


            //}
        }
Beispiel #11
0
 /// <summary>
 /// Apply a comment to the provided range
 /// </summary>
 /// <param name="range">
 /// A <see cref="Range"/> object to which the comment has to be applied
 /// </param>
 /// <param name="comment">
 /// The content of the comment
 /// </param>
 /// <param name="visible">
 /// a value indicating whether the comment should be visible or not
 /// </param>
 private static void ApplyComment(Range range, string comment, bool visible = false)
 {
     range.ClearComments();
     range.AddComment(comment);
     range.Comment.Visible = visible;
 }
        /// <summary>
        /// Adds a comment to the cell in the worksheet
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="commentValue"></param>
        public void AddCellComment(int row, int col, string commentValue)
        {
            Range cRange = (Range)(worksheet.Cells[row, col]);

            cRange.AddComment(commentValue);
        }
Beispiel #13
0
        void SelectionLocationReceived(object sender, EventArgs e)
        {
            RangePacket packet    = (RangePacket)sender;
            Range       selection = ActiveWorksheet.Range[packet.RangeInfo];
            ExcelUser   user      = packet.User;

            if (lastRange != null)
            {
                Debug.WriteLine(lastLeftColor);
                lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Color       = lastLeftColor;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight      = lastLeftWeight;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle   = lastLeftStyle;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Color        = lastTopColor;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].Weight       = lastTopWeight;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle    = lastTopStyle;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Color      = lastRightColor;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].Weight     = lastRightWeight;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle  = lastRightStyle;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Color     = lastBottomColor;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight    = lastBottomWeight;
                lastRange.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = lastBottomStyle;
                lastRange.ClearComments();
                if (lastComments.Count >= 0)
                {
                    if (lastComments[0] != null)
                    {
                        lastRange.AddComment(lastComments[0]);
                    }
                }


                //all you have to do is .copy!!!!!!!!!!!!!!!!!!!!!!
                //selection.Copy(lastRange);
                //ActiveWorksheet.Range["A1"].Value = "test";
            }
            lastRange = selection;
            Debug.WriteLine("got selection change");
            ActiveRange  = selection;
            lastComments = new List <string>();

            lastLeftColor    = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color;
            lastLeftWeight   = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight;
            lastLeftStyle    = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeLeft].LineStyle;
            lastTopColor     = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color;
            lastTopWeight    = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight;
            lastTopStyle     = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle;
            lastRightColor   = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color;
            lastRightWeight  = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight;
            lastRightStyle   = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeRight].LineStyle;
            lastBottomColor  = (XlRgbColor)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color;
            lastBottomWeight = (XlBorderWeight)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight;
            lastBottomStyle  = (XlLineStyle)selection.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle;

            selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Color    = XlRgbColor.rgbBlue;
            selection.Borders.Item[XlBordersIndex.xlEdgeLeft].Weight   = XlBorderWeight.xlMedium;
            selection.Borders.Item[XlBordersIndex.xlEdgeTop].Color     = XlRgbColor.rgbBlue;
            selection.Borders.Item[XlBordersIndex.xlEdgeTop].Weight    = XlBorderWeight.xlMedium;
            selection.Borders.Item[XlBordersIndex.xlEdgeRight].Color   = XlRgbColor.rgbBlue;
            selection.Borders.Item[XlBordersIndex.xlEdgeRight].Weight  = XlBorderWeight.xlMedium;
            selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Color  = XlRgbColor.rgbBlue;
            selection.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;

            lastComments.Add(selection.Comment?.Text());
            selection.ClearComments();
            Debug.WriteLine("Made it to comment adding");
            if (selection.Count < 2)
            {
                selection.AddComment(user.ToString() + ": Updating this cell at the moment");
            }
            else
            {
                selection[selection.Columns.Count]?.AddComment(user.ToString() + ": Updating these cells at the moment");
            }
        }
Beispiel #14
0
        public static void ReadValidateErrorWorkCenterFile(string fileName, string excelExtension, string excelName, string validatePath)
        {
            try
            {
                Application xlApp       = new Application();
                Workbook    xlWorkbook  = xlApp.Workbooks.Open(excelName);
                _Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                Range       xlRange     = xlWorksheet.UsedRange;

                string[] lines = File.ReadAllLines(fileName, Encoding.UTF8);
                // init line 5
                for (int i = 5; i < lines.Count(); i++)
                {
                    string[] MessageCNMaterial    = { "00300", "00055" };
                    string[] MessageCNAlternative = { "29003" };
                    string[] MessageCNUnit        = { "29175" };

                    string[] text = lines[i].Split('\t');

                    string errorCode = string.Format("{0}{1}", text[14].Trim(), text[15].Trim()); // Message Class + Message Number

                    if (text[13] == "E")                                                          // Message Type Error
                    {
                        int headerIndex = Int32.Parse(text[9].Trim()) + 3;                        // index header
                        if (MessageCNMaterial.Contains(errorCode))
                        {
                            // fix position at 1 is Header Material
                            Range cell = xlRange.Cells[1, headerIndex];
                            // hilight fill
                            cell.Font.Color        = ColorTranslator.ToOle(Color.Red);
                            cell.Borders.LineStyle = XlLineStyle.xlContinuous;
                            cell.Borders.Weight    = 2d;
                            cell.Borders.Color     = ColorTranslator.ToOle(Color.Red);
                            // add message comments
                            cell.AddComment(text[2]);
                        }

                        if (MessageCNAlternative.Contains(errorCode))
                        {
                            // fix position at 4 is BOMAlternative
                            Range cell = xlRange.Cells[4, headerIndex];
                            // hilight fill
                            cell.Font.Color        = ColorTranslator.ToOle(Color.Red);
                            cell.Borders.LineStyle = XlLineStyle.xlContinuous;
                            cell.Borders.Weight    = 2d;
                            cell.Borders.Color     = ColorTranslator.ToOle(Color.Red);
                            // add message comments
                            cell.AddComment(text[2]);
                        }

                        if (MessageCNUnit.Contains(errorCode))
                        {
                            // fix position at 5,3 is Unit
                            Range cell = xlRange.Cells[5, 3];
                            // hilight fill
                            cell.Font.Color        = ColorTranslator.ToOle(Color.Red);
                            cell.Borders.LineStyle = XlLineStyle.xlContinuous;
                            cell.Borders.Weight    = 2d;
                            cell.Borders.Color     = ColorTranslator.ToOle(Color.Red);
                            // add message comments
                            cell.AddComment(text[2]);
                        }
                    }
                }
                xlWorkbook.SaveAs(validatePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                xlWorkbook.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Launch the ontology import wizard and generate an Excel skeleton from the ontology.
        /// </summary>
        public void LoadOntology()
        {
            this.resourcesToImport.Clear();
            this.nestedProperties.Clear();

            // Displays an OpenFileDialog so the user can select an ontology.
            OpenFileDialog openOntologyFileDialog = new OpenFileDialog();

            openOntologyFileDialog.Filter = "RDF/XML (*.rdf)|*.rdf|Turtle (*.ttl)|*.ttl|JSON-LD (*.jsonld)|*.jsonld|NTriples (*.nt)|*.nt|NQuads (*.nq)|*.nq|TriG (*.trig)|*.trig";
            openOntologyFileDialog.Title  = "Select an ontology file";

            // Show the Dialog.
            // If the user clicked OK in the dialog and an OWL file was selected, open it.
            if (openOntologyFileDialog.ShowDialog() == DialogResult.OK)
            {
                OntologyGraph g = new OntologyGraph();
                FileLoader.Load(g, openOntologyFileDialog.FileName);

                ImportOptionsForm importOptionsForm = new ImportOptionsForm(g);
                if (importOptionsForm.ShowDialog() == DialogResult.OK)
                {
                    // Iterate through the named bottom classes; generate one worksheet for each
                    foreach (OntologyClass oClass in g.OwlClasses)
                    {
                        if (oClass.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oClass.ToString()))
                        {
                            Worksheet newWorksheet = Globals.ThisAddIn.Application.Worksheets.Add();

                            UriNode classAsUriNode = (UriNode)oClass.Resource;
                            newWorksheet.Name = Helper.GetLocalName(classAsUriNode.Uri);

                            // Start iterating from the first column
                            int column = 1;

                            // Add column for the IRI identifier
                            // <IRI> is a special identifier used for this purpose, signaling that a) the IRI shall
                            // be minted from this column, and b) the subsequent row will contain the OWL class for all minted entities
                            string identifierColumnName = Helper.GetExcelColumnName(column);
                            string identifierColumnHeaderCellIdentifier = String.Format("{0}1", identifierColumnName);
                            Range  identifierColumnHeaderCell           = newWorksheet.get_Range(identifierColumnHeaderCellIdentifier);
                            identifierColumnHeaderCell.Value = "Identifier";
                            string identifierNote = "<IRI>";
                            identifierNote += String.Format("\n<{0}>", classAsUriNode.Uri.ToString());
                            identifierColumnHeaderCell.NoteText(identifierNote);
                            column++;

                            // Iterate through the properties for which this class is in the domain;
                            // generate one column for each property (named from label and if that does not exist from IRI)
                            // Order the columns by type, with datatype properties coming before object properties,
                            // then by string representation
                            foreach (OntologyProperty oProperty in oClass.IsDomainOf.OrderBy(o => o.Types.First()).OrderBy(o => o.ToString()))
                            {
                                if (oProperty.Resource.NodeType == NodeType.Uri && resourcesToImport.Contains(oProperty.ToString()))
                                {
                                    // This is because Excel uses strange adressing, i.e., "A1" instead of something
                                    // numeric and zero-indexed such as "0,0".
                                    string headerColumnName     = Helper.GetExcelColumnName(column);
                                    string headerCellIdentifier = String.Format("{0}1", headerColumnName);
                                    Range  headerCellRange      = newWorksheet.get_Range(headerCellIdentifier);

                                    UriNode propertyAsUriNode = (UriNode)oProperty.Resource;

                                    // TODO: the below code is extremely repetitive. Sometime, when not sick and brain is working better,
                                    // Future Karl will refactor and simplify this (hopefully)
                                    if (nestedProperties.Keys.Contains(propertyAsUriNode.Uri.AbsoluteUri))
                                    {
                                        foreach (string nestedPropertyUri in nestedProperties[propertyAsUriNode.Uri.AbsoluteUri])
                                        {
                                            // Repeat header cell selection for each nested property
                                            headerColumnName     = Helper.GetExcelColumnName(column);
                                            headerCellIdentifier = String.Format("{0}1", headerColumnName);
                                            headerCellRange      = newWorksheet.get_Range(headerCellIdentifier);

                                            // Find and assign label
                                            string headerLabel;

                                            // Assign property IRI
                                            string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString());

                                            // Asign property type hinting
                                            string propertyType = oProperty.Types.First().ToString();
                                            noteText += String.Format("\n<{0}>", propertyType);

                                            // Assign range hinting IRI
                                            // TODO: what if no range exists? see same case below and after else clause
                                            OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
                                            if (namedRanges.Count() > 0)
                                            {
                                                UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource;
                                                string  rangeUri       = rangeAsUriNode.Uri.ToString();
                                                noteText += String.Format("\n<{0}>", rangeUri);
                                            }

                                            // Branching for special case of rdfs:label
                                            if (nestedPropertyUri.Equals(OntologyHelper.PropertyLabel))
                                            {
                                                // Assign header label
                                                headerLabel = "rdfs:label";

                                                // Nested property IRI (i.e., rdfs:label)
                                                noteText += String.Format("\n<{0}>", OntologyHelper.PropertyLabel);

                                                // Nested property type
                                                noteText += String.Format("\n<{0}>", OntologyHelper.OwlAnnotationProperty);

                                                // Nested property range
                                                noteText += String.Format("\n<{0}>", XmlSpecsHelper.XmlSchemaDataTypeString);
                                            }
                                            else
                                            {
                                                // Get the property from the ontology
                                                OntologyProperty nestedProperty          = g.OwlProperties.Where(property => ((UriNode)property.Resource).Uri.AbsoluteUri.Equals(nestedPropertyUri)).First();
                                                UriNode          nestedPropertyAsUriNode = (UriNode)nestedProperty.Resource;

                                                // Assign header label
                                                if (nestedProperty.Label.Count() > 0)
                                                {
                                                    ILiteralNode labelNode = nestedProperty.Label.First();
                                                    headerLabel = labelNode.Value;
                                                }
                                                else
                                                {
                                                    headerLabel = Helper.GetLocalName(nestedPropertyAsUriNode.Uri);
                                                }

                                                // Nested property IRI
                                                noteText += String.Format("\n<{0}>", nestedPropertyAsUriNode.Uri.ToString());

                                                // Asign nested property type hinting
                                                string nestedPropertyType;
                                                if (nestedProperty.Types.Count() > 0)
                                                {
                                                    nestedPropertyType = nestedProperty.Types.First().ToString();
                                                }
                                                else
                                                {
                                                    nestedPropertyType = "";
                                                }
                                                noteText += String.Format("\n<{0}>", nestedPropertyType);

                                                // Nested range hinting IRI
                                                OntologyClass[] namedNestedRanges = nestedProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
                                                string          nestedRange;
                                                if (namedNestedRanges.Count() > 0)
                                                {
                                                    nestedRange = ((UriNode)namedNestedRanges.First().Resource).Uri.ToString();
                                                }
                                                else
                                                {
                                                    nestedRange = "";
                                                }
                                                noteText += String.Format("\n<{0}>", nestedRange);
                                            }

                                            // Assign header label
                                            headerLabel           = headerLabel + " (through " + Helper.GetLocalName(propertyAsUriNode.Uri) + ")";
                                            headerCellRange.Value = headerLabel;

                                            // Assign note text
                                            headerCellRange.AddComment(noteText);
                                            column++;
                                        }
                                    }
                                    else
                                    {
                                        // Find and assign label
                                        string propertyLabel;
                                        if (oProperty.Label.Count() > 0)
                                        {
                                            ILiteralNode labelNode = oProperty.Label.First();
                                            propertyLabel = labelNode.Value;
                                        }
                                        else
                                        {
                                            propertyLabel = Helper.GetLocalName(propertyAsUriNode.Uri);
                                        }
                                        headerCellRange.Value = propertyLabel;

                                        // Assign property IRI
                                        string noteText = String.Format("<{0}>", propertyAsUriNode.Uri.ToString());

                                        // Asign property type hinting
                                        string propertyType = oProperty.Types.First().ToString();
                                        noteText += String.Format("\n<{0}>", propertyType);

                                        // Assign range hinting IRI (provided simple )
                                        OntologyClass[] namedRanges = oProperty.Ranges.Where(o => o.Resource.NodeType == NodeType.Uri).ToArray();
                                        if (namedRanges.Count() > 0)
                                        {
                                            UriNode rangeAsUriNode = (UriNode)namedRanges.First().Resource;
                                            string  rangeUri       = rangeAsUriNode.Uri.ToString();
                                            noteText += String.Format("\n<{0}>", rangeUri);
                                        }

                                        // Assign note text
                                        headerCellRange.AddComment(noteText);
                                        column++;
                                    }
                                }
                            }

                            // Bold the header row and fit the columns so things look nice
                            Range headerRow = newWorksheet.get_Range("A1").EntireRow;
                            headerRow.Font.Bold = true;
                            headerRow.Columns.AutoFit();
                        }
                    }
                }
            }
        }
        static void Main(string[] args)
        {
            Console.WriteLine("Interop Performance Test - 10.000 Cells.");

            /*
             * start excel and disable messageboxes and screen updating
             */
            Excel.Application excelApplication = new Excel.Application();
            excelApplication.DisplayAlerts  = false;
            excelApplication.ScreenUpdating = false;
            //excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);

            /*
             *  create new empty worksheet
             */
            Excel.Workbooks books  = excelApplication.Workbooks;
            Excel.Workbook  book   = books.Add(Missing.Value);
            Excel.Sheets    sheets = book.Worksheets;
            Excel.Worksheet sheet  = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            /*
             *  do the test
             *  we collect all references and release after time recording
             *  the 2 latebind libs release the references at end automaticly and we want a fair test
             */
            List <object> comReferenesList = new List <object>();
            DateTime      timeStart        = DateTime.Now;

            for (int i = 1; i <= 10000; i++)
            {
                string rangeAdress = "$A" + i.ToString();
                Range  cellRange   = (Range)sheet.Range[rangeAdress];
                cellRange.Value        = "value";
                cellRange.Font.Name    = "Verdana";
                cellRange.NumberFormat = "@";
                cellRange.WrapText     = false;
                Comment sampleComment = cellRange.AddComment("Sample Comment");
                comReferenesList.Add(cellRange);
                comReferenesList.Add(sampleComment);
            }
            DateTime timeEnd     = DateTime.Now;
            TimeSpan timeElapsed = timeEnd - timeStart;

            foreach (var item in comReferenesList)
            {
                Marshal.ReleaseComObject(item);
            }

            /*
             * display for user
             */
            string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);

            Console.WriteLine(outputConsole);
            Console.Read();

            /*
             * write result in logfile
             */
            string logFile       = Path.Combine(Environment.CurrentDirectory, "Interop.log");
            string logFileAppend = timeElapsed.ToString() + Environment.NewLine;

            File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);

            /*
             * release & quit
             */
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            excelApplication.Quit();
            Marshal.ReleaseComObject(excelApplication);
        }
Beispiel #17
0
        public static void SetCellComment(this Worksheet aWorksheet, int aRow, int aCol, string aComment)
        {
            Range aCell = aWorksheet.Cells[aRow, aCol] as Range;

            aCell.AddComment(aComment).Visible = false;
        }