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();
}
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");
}
//Добавляет примечание 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("$", ""));
}
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();
}
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);
}
}
/// <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 + "]写数据出错！");
}
}
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);
}
}
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);
}
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++;
}
//}
}
/// <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);
}
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");
}
}
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);
}
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;
}
