diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
index 1f2caba..f4f780b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
@@ -240,6 +240,7 @@
Boolean HasRichText { get; }
IXLComment Comment { get; }
Boolean HasComment { get; }
+ void DeleteComment();
Boolean IsMerged();
Boolean IsEmpty();
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs
index 5fba63b..890024c 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCells.cs
@@ -37,6 +37,11 @@
IXLCells Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats);
///
+ /// Delete the comments of these cells.
+ ///
+ void DeleteComments();
+
+ ///
/// Sets the cells' formula with A1 references.
///
/// The formula with A1 references.
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
index 491311e..65969d0 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
@@ -695,7 +695,7 @@
{
Hyperlink = null;
_richText = null;
- _comment = null;
+ //_comment = null;
_cellValue = String.Empty;
FormulaA1 = String.Empty;
}
@@ -883,8 +883,10 @@
{
if (_comment == null)
{
- var style = GetStyleForRead();
- _comment = new XLComment(style.Font);
+ // MS Excel uses Tahoma 8 Swiss no matter what current style font
+ // var style = GetStyleForRead();
+ var defaultFont = new XLFont() { FontName = "Tahoma", FontSize = 8, FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss };
+ _comment = new XLComment(defaultFont);
}
return _comment;
@@ -896,6 +898,10 @@
get { return _comment != null; }
}
+ public void DeleteComment() {
+ _comment = null;
+ }
+
public Boolean IsMerged()
{
return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs
index 330f9c8..2e8983b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs
@@ -182,6 +182,10 @@
return this;
}
+ public void DeleteComments() {
+ this.ForEach(c => c.DeleteComment());
+ }
+
public String FormulaA1
{
set { this.ForEach(c => c.FormulaA1 = value); }
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs
index 8d95448..8bc72e3 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs
@@ -11,6 +11,9 @@
Boolean Visible { get; set; }
IXLComment SetVisible(); IXLComment SetVisible(Boolean value);
+ void AddSignature();
+ void AddSignature(string username);
+ void AddNewLine();
}
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs
index 971d8d0..5ac310a 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs
@@ -31,6 +31,8 @@
Container = this;
Anchor = XLDrawingAnchor.MoveAndSizeWithCells;
Style = new XLDrawingStyle();
+ Style.Size.Height = 4; // I think this is misused for legacy drawing
+ Style.Size.Width = 2;
}
public String Author { get; set; }
@@ -40,6 +42,22 @@
return this;
}
+ public void AddSignature()
+ {
+ // existing Author might be someone else hence using current user name here
+ this.AddSignature(Environment.UserName);
+ }
+
+ public void AddSignature(string username)
+ {
+ this.AddText(string.Format("{0}:{1}", username, Environment.NewLine)).SetBold();
+ }
+
+ public void AddNewLine()
+ {
+ this.AddText(Environment.NewLine);
+ }
+
public Boolean Visible { get; set; } public IXLComment SetVisible() { Visible = true; return this; } public IXLComment SetVisible(Boolean value) { Visible = value; return this; }
#region IXLDrawing
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
index 8227d74..7f67727 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs
@@ -201,6 +201,11 @@
///
/// Specify what you want to clear.
IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats);
+
+ ///
+ /// Deletes the cell comments from this range.
+ ///
+ void DeleteComments();
IXLRangeBase SetValue(T value);
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
index be12fd8..a860cd8 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -317,6 +317,9 @@
return this;
}
+ public void DeleteComments() {
+ Cells().DeleteComments();
+ }
public bool Contains(String rangeAddress)
{
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/XLColor_Static.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/XLColor_Static.cs
index 9134960..fbc0fef 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/XLColor_Static.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/Colors/XLColor_Static.cs
@@ -125,6 +125,8 @@
}
}
+ public static IXLColor NoColor { get { return new XLColor(); } }
+
public static IXLColor AliceBlue { get { return FromColor(Color.AliceBlue); } }
public static IXLColor AntiqueWhite { get { return FromColor(Color.AntiqueWhite); } }
public static IXLColor Aqua { get { return FromColor(Color.Aqua); } }
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
index 7db9278..aa8853d 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -10,6 +10,8 @@
using DocumentFormat.OpenXml.Spreadsheet;
using Ap = DocumentFormat.OpenXml.ExtendedProperties;
using Op = DocumentFormat.OpenXml.CustomProperties;
+using Vml = DocumentFormat.OpenXml.Vml;
+using Ss = DocumentFormat.OpenXml.Vml.Spreadsheet;
#endregion
@@ -247,6 +249,55 @@
}
#endregion
+
+ #region LoadComments
+
+ if (wsPart.WorksheetCommentsPart != null) {
+ var root = wsPart.WorksheetCommentsPart.Comments;
+ var authors = root.GetFirstChild().ChildElements;
+ var comments = root.GetFirstChild().ChildElements;
+
+ // **** MAYBE FUTURE SHAPE SIZE SUPPORT
+ // var shapes = wsPart.VmlDrawingParts.SelectMany(p => new System.Xml.XmlTextReader(p.GetStream()).Read()
+
+ foreach (Comment c in comments) {
+ // find cell by reference
+ var cell = ws.Cell(c.Reference);
+ cell.Comment.Author = authors[(int)c.AuthorId.Value].InnerText;
+ var runs = c.GetFirstChild().Elements();
+ foreach (Run run in runs) {
+ var runProperties = run.RunProperties;
+ String text = run.Text.InnerText.FixNewLines();
+ var rt = cell.Comment.AddText(text);
+ LoadFont(runProperties, rt);
+ }
+
+ // **** MAYBE FUTURE SHAPE SIZE SUPPORT
+ //var shape = shapes.FirstOrDefault(sh => {
+ // var cd = sh.GetFirstChild();
+ // return cd.GetFirstChild().InnerText == cell.Address.RowNumber.ToString()
+ // && cd.GetFirstChild().InnerText == cell.Address.ColumnNumber.ToString();
+ // });
+
+ //var location = shape.GetFirstChild().InnerText.Split(',');
+
+ //var leftCol = int.Parse(location[0]);
+ //var leftOffsetPx = int.Parse(location[1]);
+ //var topRow = int.Parse(location[2]);
+ //var topOffsetPx = int.Parse(location[3]);
+ //var rightCol = int.Parse(location[4]);
+ //var riightOffsetPx = int.Parse(location[5]);
+ //var bottomRow = int.Parse(location[6]);
+ //var bottomOffsetPx = int.Parse(location[7]);
+
+ //cmt.Style.Size.Height = bottomRow - topRow;
+ //cmt.Style.Size.Width = rightCol = leftCol;
+
+ }
+
+ }
+
+ #endregion
}
var workbook = dSpreadsheet.WorkbookPart.Workbook;
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
index 8ab6bee..f2cb799 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -141,6 +141,12 @@
else
worksheetPart = workbookPart.AddNewPart(wsRelId);
+ context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Worksheet);
+
+ // delete comment related parts (todo: review)
+ worksheetPart.DeletePart(worksheetPart.WorksheetCommentsPart);
+ worksheetPart.DeleteParts(worksheetPart.GetPartsOfType());
+
if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any())
{
WorksheetCommentsPart worksheetCommentsPart =
@@ -4332,11 +4338,15 @@
var rowNumber = c.Address.RowNumber;
var columnNumber = c.Address.ColumnNumber;
- var leftCol = columnNumber;
+
+ var leftCol = columnNumber; // always right next to column
+ var leftOffset = 15;
var topRow = rowNumber == 1 ? rowNumber - 1 : rowNumber - 2; // -1 : zero based index, -2 : moved up
- var topOffset = rowNumber == 1 ? 2 : 15; // on first row, comment is 2px down, on any other is 15 px up
- var rightCol = leftCol + c.Comment.Style.Size.Width;
+ var topOffset = rowNumber == 1 ? 2 : 9; // on first row, comment is 2px down, on any other is 15 px up
+ var rightCol = leftCol + c.Comment.Style.Size.Width;
+ var rightOffset = 15;
var bottomRow = topRow + c.Comment.Style.Size.Height;
+ var bottomOffset = rowNumber == 1 ? 2 : 9;
var shapeId = string.Format("_x0000_s{0}", c.GetHashCode().ToString()); // Unique per cell, e.g.: "_x0000_s1026"
@@ -4346,10 +4356,10 @@
new Vml.Path() { ConnectionPointType = Vml.Office.ConnectValues.None },
new Vml.TextBox( /* */ ) { Style = "mso-direction-alt:auto" },
new Vml.Spreadsheet.ClientData(
- new Vml.Spreadsheet.MoveWithCells() { },
- new Vml.Spreadsheet.ResizeWithCells() { },
- new Vml.Spreadsheet.Anchor() { Text = string.Format(" {0}, 15, {1}, {2}, {3}, 10, {4}, 1", leftCol, topRow, topOffset, rightCol, bottomRow) },
- new Vml.Spreadsheet.AutoFill("False") { },
+ new Vml.Spreadsheet.MoveWithCells("False"), // counterintuitive
+ new Vml.Spreadsheet.ResizeWithCells("False"), // counterintuitive
+ new Vml.Spreadsheet.Anchor() { Text = string.Format(" {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", leftCol, leftOffset, topRow, topOffset, rightCol, rightOffset, bottomRow, bottomOffset) },
+ new Vml.Spreadsheet.AutoFill("False"),
new Vml.Spreadsheet.CommentRowTarget() { Text = (rowNumber - 1).ToString() },
new Vml.Spreadsheet.CommentColumnTarget() { Text = (columnNumber - 1).ToString() }
) { ObjectType = Vml.Spreadsheet.ObjectValues.Note }
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
index 4730c6c..e8cfb87 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj
@@ -129,6 +129,7 @@
+
diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs
new file mode 100644
index 0000000..92bc63b
--- /dev/null
+++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs
@@ -0,0 +1,55 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using ClosedXML.Excel;
+using System.IO;
+
+namespace ClosedXML_Examples
+{
+ class EditingComments : IXLExample
+ {
+
+ public void Create(string filePath) {
+
+ // Exercise(@"path/to/test/resources/comments");
+
+ }
+
+ public void Exercise(string basePath)
+ {
+
+ // INCOMPLETE
+
+ var book = new XLWorkbook(Path.Combine(basePath, "EditingComments.xlsx"));
+ var sheet = book.Worksheet(1);
+
+ // no change
+ // A1
+
+ // edit existing comment
+ sheet.Cell("B3").Comment.AddNewLine();
+ sheet.Cell("B3").Comment.AddSignature();
+ sheet.Cell("B3").Comment.AddText("more comment");
+
+ // delete
+ sheet.Cell("C1").DeleteComment();
+
+ // clear contents
+ sheet.Cell("D3").Clear(XLClearOptions.Contents);
+
+ // new basic
+ sheet.Cell("E1").Comment.AddText("non authored comment");
+
+ // new with author
+ sheet.Cell("F3").Comment.AddSignature();
+ sheet.Cell("F3").Comment.AddText("comment from author");
+
+ // TODO: merge with cells
+ // TODO: resize with cells
+ // TODO: visible
+
+ book.SaveAs(Path.Combine(basePath, "EditingComments_modified.xlsx"));
+ }
+ }
+}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
index 951e44c..a77c372 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -192,7 +192,9 @@
+
+