diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 044e497..d7b2854 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -142,6 +142,7 @@ + @@ -155,6 +156,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index f4f780b..1f2caba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -240,7 +240,6 @@ Boolean HasRichText { get; } IXLComment Comment { get; } Boolean HasComment { get; } - void DeleteComment(); Boolean IsMerged(); Boolean IsEmpty(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index e52ea8d..1319098 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -953,7 +953,8 @@ get { return _richText != null; } } - public IXLComment Comment + IXLComment IXLCell.Comment { get { return Comment; } } + internal XLComment Comment { get { @@ -962,7 +963,7 @@ // 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(Worksheet, defaultFont); + _comment = new XLComment(this, defaultFont); } return _comment; @@ -1447,7 +1448,7 @@ _dataType = source._dataType; FormulaR1C1 = source.FormulaR1C1; _richText = source._richText == null ? null : new XLRichText(source._richText, source.Style.Font); - _comment = source._comment == null ? null : new XLComment(source.Worksheet, source._comment, source.Style.Font); + _comment = source._comment == null ? null : new XLComment(this, source._comment, source.Style.Font); } public IXLCell CopyFrom(XLCell otherCell) @@ -1455,7 +1456,7 @@ var source = otherCell; _cellValue = source._cellValue; _richText = source._richText == null ? null : new XLRichText(source._richText, source.Style.Font); - _comment = source._comment == null ? null : new XLComment(source.Worksheet, source._comment, source.Style.Font); + _comment = source._comment == null ? null : new XLComment(this, source._comment, source.Style.Font); _dataType = source._dataType; FormulaR1C1 = source.FormulaR1C1; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 2e8983b..fafc66e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -15,16 +15,17 @@ private readonly List _rangeAddresses = new List(); private readonly bool _usedCellsOnly; private IXLStyle _style; - + private readonly Func _predicate; #endregion #region Constructor - public XLCells(bool usedCellsOnly, bool includeFormats) + public XLCells(bool usedCellsOnly, bool includeFormats, Func predicate = null) { _style = new XLStyle(this, XLWorkbook.DefaultStyle); _usedCellsOnly = usedCellsOnly; _includeFormats = includeFormats; + _predicate = predicate; } #endregion @@ -50,13 +51,17 @@ { if (oneRange) { - foreach(var cell in range.Worksheet.Internals.CellsCollection + var cellRange = range.Worksheet.Internals.CellsCollection .GetCells( range.FirstAddress.RowNumber, range.FirstAddress.ColumnNumber, range.LastAddress.RowNumber, range.LastAddress.ColumnNumber) - .Where(c => !c.IsEmpty(_includeFormats))) + .Where(c => !c.IsEmpty(_includeFormats) || (_includeFormats && c.HasComment)); + if (_predicate != null) + cellRange = cellRange.Where(c=>_predicate(c)); + + foreach(var cell in cellRange) { yield return cell; } @@ -113,12 +118,15 @@ { if (_usedCellsOnly) { - foreach ( - var cell in - cellsInRanges.SelectMany( + var cellRange = cellsInRanges.SelectMany( cir => cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( - cell => cell != null && !cell.IsEmpty(_includeFormats)))) + cell => cell != null && (!cell.IsEmpty(_includeFormats) || (_includeFormats && cell.HasComment)))); + + if (_predicate != null) + cellRange = cellRange.Where(c => _predicate(c)); + + foreach (var cell in cellRange) { yield return cell; } @@ -128,7 +136,16 @@ foreach (var cir in cellsInRanges) { foreach (XLSheetPoint a in cir.Value) - yield return cir.Key.Cell(a.Row, a.Column); + if (_predicate == null) + { + yield return cir.Key.Cell(a.Row, a.Column); + } + else + { + var cell = cir.Key.Cell(a.Row, a.Column); + if (_predicate(cell)) + yield return cell; + } } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs index b4c1668..a4fdb63 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs @@ -5,12 +5,26 @@ { public interface IXLComment : IXLFormattedText, IXLDrawing { + /// + /// Gets or sets this comment's author's name + /// String Author { get; set; } + /// + /// Sets the name of the comment's author + /// + /// Author's name IXLComment SetAuthor(String value); + /// + /// Adds a bolded line with the author's name + /// IXLRichString AddSignature(); - IXLRichString AddSignature(string username); + /// + /// Adds a carriage return to the comment + /// IXLRichString AddNewLine(); + + void Delete(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs index 7cb4f6f..ce56266 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs @@ -8,41 +8,80 @@ internal class XLComment : XLFormattedText, IXLComment { XLWorksheet _worksheet; - public XLComment(XLWorksheet worksheet, IXLFontBase defaultFont) + XLCell _cell; + public XLComment(XLCell cell, IXLFontBase defaultFont) : base(defaultFont) { - Initialize(worksheet); + Initialize(cell); } - public XLComment(XLWorksheet worksheet, XLFormattedText defaultComment, IXLFontBase defaultFont) + public XLComment(XLCell cell, XLFormattedText defaultComment, IXLFontBase defaultFont) : base(defaultComment, defaultFont) { - Initialize(worksheet); + Initialize(cell); } - public XLComment(XLWorksheet worksheet, String text, IXLFontBase defaultFont) + public XLComment(XLCell cell, String text, IXLFontBase defaultFont) : base(text, defaultFont) { - Initialize(worksheet); + Initialize(cell); } - private void Initialize(XLWorksheet worksheet) + private void Initialize(XLCell cell) { + Author = Environment.UserName; Container = this; Anchor = XLDrawingAnchor.MoveAndSizeWithCells; Style = new XLDrawingStyle(); - Style.Size.Height = 60; - Style.Size.Width = 60; - ZOrder = 1; - - Style.Margins.Left = 1.5; - Style.Margins.Right = 1.5; - Style.Margins.Top = 1.5; - Style.Margins.Bottom = 1.5; + Int32 pRow = cell.Address.RowNumber; + Double pRowOffset = 0; + if (pRow > 1) + { + pRow--; + var prevHeight = cell.CellAbove().WorksheetRow().Height; + if (prevHeight > 7) + pRowOffset = prevHeight - 7; + } + Position = new XLDrawingPosition + { + Column = cell.Address.ColumnNumber + 1, + ColumnOffset = 2, + Row = pRow, + RowOffset = pRowOffset + }; - SetVisible(); - _worksheet = worksheet; - ShapeId = worksheet.Workbook.ShapeIdManager.GetNext(); + ZOrder = cell.Worksheet.ZOrder++; + Style + .Margins.SetLeft(0.1) + .Margins.SetRight(0.1) + .Margins.SetTop(0.05) + .Margins.SetBottom(0.05) + .Margins.SetAutomatic() + + .Size.SetHeight(59.25) + .Size.SetWidth(19.2) + + .ColorsAndLines.SetLineColor(XLColor.Black) + .ColorsAndLines.SetFillColor(XLColor.FromArgb(255,255,225)) + .ColorsAndLines.SetLineDash(XLDashStyle.Solid) + .ColorsAndLines.SetLineStyle(XLLineStyle.Single) + .ColorsAndLines.SetLineWeight(0.75) + .ColorsAndLines.SetFillTransparency(1) + .ColorsAndLines.SetLineTransparency(1) + + .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Left) + .Alignment.SetVertical(XLDrawingVerticalAlignment.Top) + .Alignment.SetDirection(XLDrawingTextDirection.Context) + .Alignment.SetOrientation(XLDrawingTextOrientation.LeftToRight) + + .Properties.SetPositioning(XLDrawingAnchor.Absolute) + + .Protection.SetLocked() + .Protection.SetLockText(); + + _cell = cell; + _worksheet = cell.Worksheet; + ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); } public String Author { get; set; } @@ -54,23 +93,15 @@ public IXLRichString AddSignature() { - // existing Author might be someone else hence using current user name here - return AddSignature(Environment.UserName); - - } - - public IXLRichString AddSignature(string username) - { - return AddText(string.Format("{0}:{1}", username, Environment.NewLine)).SetBold(); + AddText(Author + ":").SetBold(); + return AddText(Environment.NewLine); } public IXLRichString AddNewLine() { return 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 public Int32 ShapeId { get; internal set; } @@ -103,55 +134,7 @@ public XLDrawingAnchor Anchor { get; set; } - public Int32 FirstColumn { get; set; } - public IXLComment SetFirstColumn(Int32 firstColumn) - { - FirstColumn = firstColumn; - return Container; - } - public Int32 FirstColumnOffset { get; set; } - public IXLComment SetFirstColumnOffset(Int32 firstColumnOffset) - { - FirstColumnOffset = firstColumnOffset; - return Container; - } - public Int32 FirstRow { get; set; } - public IXLComment SetFirstRow(Int32 firstRow) - { - FirstRow = firstRow; - return Container; - } - public Int32 FirstRowOffset { get; set; } - public IXLComment SetFirstRowOffset(Int32 firstRowOffset) - { - FirstRowOffset = firstRowOffset; - return Container; - } - - public Int32 LastColumn { get; set; } - public IXLComment SetLastColumn(Int32 firstColumn) - { - LastColumn = firstColumn; - return Container; - } - public Int32 LastColumnOffset { get; set; } - public IXLComment SetLastColumnOffset(Int32 firstColumnOffset) - { - LastColumnOffset = firstColumnOffset; - return Container; - } - public Int32 LastRow { get; set; } - public IXLComment SetLastRow(Int32 firstRow) - { - LastRow = firstRow; - return Container; - } - public Int32 LastRowOffset { get; set; } - public IXLComment SetLastRowOffset(Int32 firstRowOffset) - { - LastRowOffset = firstRowOffset; - return Container; - } + public IXLDrawingPosition Position { get; private set; } public Int32 ZOrder { get; set; } public IXLComment SetZOrder(Int32 zOrder) @@ -191,20 +174,6 @@ return Container; } - public Int32 OffsetX { get; set; } - public IXLComment SetOffsetX(Int32 offsetX) - { - OffsetX = offsetX; - return Container; - } - - public Int32 OffsetY { get; set; } - public IXLComment SetOffsetY(Int32 offsetY) - { - OffsetY = offsetY; - return Container; - } - public Int32 ExtentLength { get; set; } public IXLComment SetExtentLength(Int32 extentLength) { @@ -220,8 +189,11 @@ } public IXLDrawingStyle Style { get; private set; } + #endregion + public void Delete() { _cell.DeleteComment(); } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs index e345b6f..e83422a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs @@ -11,61 +11,35 @@ T SetVisible(); T SetVisible(Boolean hidden); - String Name { get; set; } - T SetName(String name); + ////String Name { get; set; } + ////T SetName(String name); - String Description { get; set; } - T SetDescription(String description); - - + ////String Description { get; set; } + ////T SetDescription(String description); - Int32 FirstColumn { get; set; } - T SetFirstColumn(Int32 firstColumn); - Int32 FirstColumnOffset { get; set; } - T SetFirstColumnOffset(Int32 firstColumnOffset); - Int32 FirstRow { get; set; } - T SetFirstRow(Int32 firstRow); - Int32 FirstRowOffset { get; set; } - T SetFirstRowOffset(Int32 firstRowOffset); - - Int32 LastColumn { get; set; } - T SetLastColumn(Int32 firstColumn); - Int32 LastColumnOffset { get; set; } - T SetLastColumnOffset(Int32 firstColumn); - Int32 LastRow { get; set; } - T SetLastRow(Int32 firstRow); - Int32 LastRowOffset { get; set; } - T SetLastRowOffset(Int32 firstRowOffset); + IXLDrawingPosition Position { get; } Int32 ZOrder { get; set; } T SetZOrder(Int32 zOrder); - Boolean HorizontalFlip { get; set; } - T SetHorizontalFlip(); - T SetHorizontalFlip(Boolean horizontalFlip); + //Boolean HorizontalFlip { get; set; } + //T SetHorizontalFlip(); + //T SetHorizontalFlip(Boolean horizontalFlip); - Boolean VerticalFlip { get; set; } - T SetVerticalFlip(); - T SetVerticalFlip(Boolean verticalFlip); + //Boolean VerticalFlip { get; set; } + //T SetVerticalFlip(); + //T SetVerticalFlip(Boolean verticalFlip); - Int32 Rotation { get; set; } - T SetRotation(Int32 rotation); + //Int32 Rotation { get; set; } + //T SetRotation(Int32 rotation); - Int32 OffsetX { get; set; } - T SetOffsetX(Int32 offsetX); + //Int32 ExtentLength { get; set; } + //T SetExtentLength(Int32 ExtentLength); - Int32 OffsetY { get; set; } - T SetOffsetY(Int32 offsetY); - - Int32 ExtentLength { get; set; } - T SetExtentLength(Int32 ExtentLength); - - Int32 ExtentWidth { get; set; } - T SetExtentWidth(Int32 extentWidth); + //Int32 ExtentWidth { get; set; } + //T SetExtentWidth(Int32 extentWidth); IXLDrawingStyle Style { get; } - //"position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:96pt;height:60pt;z-index:1; visibility:visible"; - } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawingPosition.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawingPosition.cs new file mode 100644 index 0000000..ac22822 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawingPosition.cs @@ -0,0 +1,20 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLDrawingPosition + { + Int32 Column { get; set; } + IXLDrawingPosition SetColumn(Int32 column); + Double ColumnOffset { get; set; } + IXLDrawingPosition SetColumnOffset(Double columnOffset); + + Int32 Row { get; set; } + IXLDrawingPosition SetRow(Int32 row); + Double RowOffset { get; set; } + IXLDrawingPosition SetRowOffset(Double rowOffset); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingAlignment.cs index 876331e..ab838a6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingAlignment.cs @@ -18,16 +18,18 @@ BottomToTop, TopToBottom } + public enum XLDrawingHorizontalAlignment { Left, Justify, Center, Right, Distributed } + public enum XLDrawingVerticalAlignment { Top, Justify, Center, Bottom, Distributed } public interface IXLDrawingAlignment { - XLAlignmentHorizontalValues Horizontal { get; set; } - XLAlignmentVerticalValues Vertical { get; set; } + XLDrawingHorizontalAlignment Horizontal { get; set; } + XLDrawingVerticalAlignment Vertical { get; set; } Boolean AutomaticSize { get; set; } XLDrawingTextDirection Direction { get; set; } XLDrawingTextOrientation Orientation { get; set; } - IXLDrawingStyle SetHorizontal(XLAlignmentHorizontalValues value); - IXLDrawingStyle SetVertical(XLAlignmentVerticalValues value); + IXLDrawingStyle SetHorizontal(XLDrawingHorizontalAlignment value); + IXLDrawingStyle SetVertical(XLDrawingVerticalAlignment value); IXLDrawingStyle SetAutomaticSize(); IXLDrawingStyle SetAutomaticSize(Boolean value); IXLDrawingStyle SetDirection(XLDrawingTextDirection value); IXLDrawingStyle SetOrientation(XLDrawingTextOrientation value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingColorsAndLines.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingColorsAndLines.cs index 68e2e41..11255b8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingColorsAndLines.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingColorsAndLines.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - public enum XLDashTypes + public enum XLDashStyle { Solid, RoundDot, @@ -16,37 +16,27 @@ LongDashDot, LongDashDotDot } - public enum XLLineStyles + public enum XLLineStyle { - OneQuarter, - OneHalf, - ThreeQuarters, - One, - OneAndOneHalf, - TwoAndOneQuarter, - Three, - FourAndOneHalf, - Six, - ThreeSplit, - FourAndOneHalfSplit1, - FourAndOneHalfSplit2, - SixSplit + Single, ThinThin, ThinThick, ThickThin, ThickBetweenThin } public interface IXLDrawingColorsAndLines { IXLColor FillColor { get; set; } - Int32 FillTransparency { get; set; } + Double FillTransparency { get; set; } IXLColor LineColor { get; set; } + Double LineTransparency { get; set; } Double LineWeight { get; set; } - XLDashTypes LineDash { get; set; } - XLLineStyles LineStyle { get; set; } + XLDashStyle LineDash { get; set; } + XLLineStyle LineStyle { get; set; } - IXLDrawingStyle SetFillColor(XLColor value); - IXLDrawingStyle SetFillTransparency(Int32 value); - IXLDrawingStyle SetLineColor(XLColor value); + IXLDrawingStyle SetFillColor(IXLColor value); + IXLDrawingStyle SetFillTransparency(Double value); + IXLDrawingStyle SetLineColor(IXLColor value); + IXLDrawingStyle SetLineTransparency(Double value); IXLDrawingStyle SetLineWeight(Double value); - IXLDrawingStyle SetLineDash(XLDashTypes value); - IXLDrawingStyle SetLineStyle(XLLineStyles value); + IXLDrawingStyle SetLineDash(XLDashStyle value); + IXLDrawingStyle SetLineStyle(XLLineStyle value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingMargins.cs index 7822100..cce06e8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingMargins.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingMargins.cs @@ -12,12 +12,14 @@ Double Right { get; set; } Double Top { get; set; } Double Bottom { get; set; } + Double All { set; } IXLDrawingStyle SetAutomatic(); IXLDrawingStyle SetAutomatic(Boolean value); IXLDrawingStyle SetLeft(Double value); IXLDrawingStyle SetRight(Double value); IXLDrawingStyle SetTop(Double value); IXLDrawingStyle SetBottom(Double value); + IXLDrawingStyle SetAll(Double value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingSize.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingSize.cs index f2537fb..5025a9d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingSize.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingSize.cs @@ -7,17 +7,13 @@ { public interface IXLDrawingSize { + Boolean AutomaticSize { get; set; } Double Height { get; set; } Double Width { get; set; } - Double ScaleHeight { get; set; } - Double ScaleWidth { get; set; } - Boolean LockAspectRatio { get; set; } + IXLDrawingStyle SetAutomaticSize(); IXLDrawingStyle SetAutomaticSize(Boolean value); IXLDrawingStyle SetHeight(Double value); IXLDrawingStyle SetWidth(Double value); - IXLDrawingStyle SetScaleHeight(Double value); - IXLDrawingStyle SetScaleWidth(Double value); - IXLDrawingStyle SetLockAspectRatio(); IXLDrawingStyle SetLockAspectRatio(Boolean value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingStyle.cs index 3c1bce3..fac4343 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingStyle.cs @@ -7,7 +7,7 @@ { public interface IXLDrawingStyle { - IXLDrawingFont Font { get; } + //IXLDrawingFont Font { get; } IXLDrawingAlignment Alignment { get; } IXLDrawingColorsAndLines ColorsAndLines { get; } IXLDrawingSize Size { get; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingWeb.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingWeb.cs index 73556bb..abc5ccd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingWeb.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/IXLDrawingWeb.cs @@ -7,8 +7,8 @@ { public interface IXLDrawingWeb { - String AlternativeText { get; set; } - IXLDrawingStyle SetAlternativeText(String value); + String AlternateText { get; set; } + IXLDrawingStyle SetAlternateText(String value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingAlignment.cs index f9f3b82..789b5c4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingAlignment.cs @@ -12,13 +12,9 @@ public XLDrawingAlignment(IXLDrawingStyle style) { _style = style; - Horizontal = XLAlignmentHorizontalValues.Left; - Vertical = XLAlignmentVerticalValues.Top; - Direction = XLDrawingTextDirection.Context; - Orientation = XLDrawingTextOrientation.LeftToRight; } - public XLAlignmentHorizontalValues Horizontal { get; set; } public IXLDrawingStyle SetHorizontal(XLAlignmentHorizontalValues value) { Horizontal = value; return _style; } - public XLAlignmentVerticalValues Vertical { get; set; } public IXLDrawingStyle SetVertical(XLAlignmentVerticalValues value) { Vertical = value; return _style; } + public XLDrawingHorizontalAlignment Horizontal { get; set; } public IXLDrawingStyle SetHorizontal(XLDrawingHorizontalAlignment value) { Horizontal = value; return _style; } + public XLDrawingVerticalAlignment Vertical { get; set; } public IXLDrawingStyle SetVertical(XLDrawingVerticalAlignment value) { Vertical = value; return _style; } public Boolean AutomaticSize { get; set; } public IXLDrawingStyle SetAutomaticSize() { AutomaticSize = true; return _style; } public IXLDrawingStyle SetAutomaticSize(Boolean value) { AutomaticSize = value; return _style; } public XLDrawingTextDirection Direction { get; set; } public IXLDrawingStyle SetDirection(XLDrawingTextDirection value) { Direction = value; return _style; } public XLDrawingTextOrientation Orientation { get; set; } public IXLDrawingStyle SetOrientation(XLDrawingTextOrientation value) { Orientation = value; return _style; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingColorsAndLines.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingColorsAndLines.cs index 16b9b06..3fcd933 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingColorsAndLines.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingColorsAndLines.cs @@ -12,18 +12,14 @@ public XLDrawingColorsAndLines(IXLDrawingStyle style) { _style = style; - FillColor = XLColor.FromArgb(255, 255, 225); - LineColor = XLColor.Black; - LineDash = XLDashTypes.Solid; - LineStyle = XLLineStyles.OneQuarter; - LineWeight = 0.75; } - public IXLColor FillColor { get; set; } public IXLDrawingStyle SetFillColor(XLColor value) { FillColor = value; return _style; } - public Int32 FillTransparency { get; set; } public IXLDrawingStyle SetFillTransparency(Int32 value) { FillTransparency = value; return _style; } - public IXLColor LineColor { get; set; } public IXLDrawingStyle SetLineColor(XLColor value) { LineColor = value; return _style; } + public IXLColor FillColor { get; set; } public IXLDrawingStyle SetFillColor(IXLColor value) { FillColor = value; return _style; } + public Double FillTransparency { get; set; } public IXLDrawingStyle SetFillTransparency(Double value) { FillTransparency = value; return _style; } + public IXLColor LineColor { get; set; } public IXLDrawingStyle SetLineColor(IXLColor value) { LineColor = value; return _style; } + public Double LineTransparency { get; set; } public IXLDrawingStyle SetLineTransparency(Double value) { LineTransparency = value; return _style; } public Double LineWeight { get; set; } public IXLDrawingStyle SetLineWeight(Double value) { LineWeight = value; return _style; } - public XLDashTypes LineDash { get; set; } public IXLDrawingStyle SetLineDash(XLDashTypes value) { LineDash = value; return _style; } - public XLLineStyles LineStyle { get; set; } public IXLDrawingStyle SetLineStyle(XLLineStyles value) { LineStyle = value; return _style; } + public XLDashStyle LineDash { get; set; } public IXLDrawingStyle SetLineDash(XLDashStyle value) { LineDash = value; return _style; } + public XLLineStyle LineStyle { get; set; } public IXLDrawingStyle SetLineStyle(XLLineStyle value) { LineStyle = value; return _style; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingMargins.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingMargins.cs index 6d5ebb6..3694114 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingMargins.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingMargins.cs @@ -7,18 +7,32 @@ { internal class XLDrawingMargins: IXLDrawingMargins { - private readonly IXLDrawingStyle _style; - + private readonly IXLDrawingStyle _style; public XLDrawingMargins(IXLDrawingStyle style) { _style = style; - Automatic = true; } public Boolean Automatic { get; set; } public IXLDrawingStyle SetAutomatic() { Automatic = true; return _style; } public IXLDrawingStyle SetAutomatic(Boolean value) { Automatic = value; return _style; } - public Double Left { get; set; } public IXLDrawingStyle SetLeft(Double value) { Left = value; return _style; } - public Double Right { get; set; } public IXLDrawingStyle SetRight(Double value) { Right = value; return _style; } - public Double Top { get; set; } public IXLDrawingStyle SetTop(Double value) { Top = value; return _style; } - public Double Bottom { get; set; } public IXLDrawingStyle SetBottom(Double value) { Bottom = value; return _style; } - + Double _left; + public Double Left { get { return _left; } set { _left = value; Automatic = false; } } + public IXLDrawingStyle SetLeft(Double value) { Left = value; return _style; } + Double _right; + public Double Right { get { return _right; } set { _right = value; Automatic = false; } } public IXLDrawingStyle SetRight(Double value) { Right = value; return _style; } + Double _top; + public Double Top { get { return _top; } set { _top = value; Automatic = false; } } public IXLDrawingStyle SetTop(Double value) { Top = value; return _style; } + Double _bottom; + public Double Bottom { get { return _bottom; } set { _bottom = value; Automatic = false; } } public IXLDrawingStyle SetBottom(Double value) { Bottom = value; return _style; } + public Double All + { + set + { + _left = value; + _right = value; + _top = value; + _bottom = value; + Automatic = false; + } + } + public IXLDrawingStyle SetAll(Double value) { All = value; return _style; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProperties.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProperties.cs index b55d3ce..6f0ce19 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProperties.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProperties.cs @@ -12,7 +12,6 @@ public XLDrawingProperties(IXLDrawingStyle style) { _style = style; - Positioning = XLDrawingAnchor.Absolute; } public XLDrawingAnchor Positioning { get; set; } public IXLDrawingStyle SetPositioning(XLDrawingAnchor value) { Positioning = value; return _style; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProtection.cs index 1ea3ec9..d95633c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProtection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingProtection.cs @@ -12,8 +12,6 @@ public XLDrawingProtection(IXLDrawingStyle style) { _style = style; - Locked = true; - LockText = true; } public Boolean Locked { get; set; } public IXLDrawingStyle SetLocked() { Locked = true; return _style; } public IXLDrawingStyle SetLocked(Boolean value) { Locked = value; return _style; } public Boolean LockText { get; set; } public IXLDrawingStyle SetLockText() { LockText = true; return _style; } public IXLDrawingStyle SetLockText(Boolean value) { LockText = value; return _style; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingSize.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingSize.cs index b8eee0a..11f30f9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingSize.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingSize.cs @@ -12,16 +12,11 @@ public XLDrawingSize(IXLDrawingStyle style) { _style = style; - Height = 0.82; - Width = 1.5; - ScaleHeight = 100; - ScaleWidth = 100; } + + public Boolean AutomaticSize { get { return _style.Alignment.AutomaticSize; } set { _style.Alignment.AutomaticSize = value; } } + public IXLDrawingStyle SetAutomaticSize() { AutomaticSize = true; return _style; } public IXLDrawingStyle SetAutomaticSize(Boolean value) { AutomaticSize = value; return _style; } public Double Height { get; set; } public IXLDrawingStyle SetHeight(Double value) { Height = value; return _style; } public Double Width { get; set; } public IXLDrawingStyle SetWidth(Double value) { Width = value; return _style; } - public Double ScaleHeight { get; set; } public IXLDrawingStyle SetScaleHeight(Double value) { ScaleHeight = value; return _style; } - public Double ScaleWidth { get; set; } public IXLDrawingStyle SetScaleWidth(Double value) { ScaleWidth = value; return _style; } - public Boolean LockAspectRatio { get; set; } public IXLDrawingStyle SetLockAspectRatio() { LockAspectRatio = true; return _style; } public IXLDrawingStyle SetLockAspectRatio(Boolean value) { LockAspectRatio = value; return _style; } - } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingStyle.cs index dbfb362..056f639 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingStyle.cs @@ -9,7 +9,7 @@ { public XLDrawingStyle() { - Font = new XLDrawingFont(this); + //Font = new XLDrawingFont(this); Alignment = new XLDrawingAlignment(this); ColorsAndLines = new XLDrawingColorsAndLines(this); Size = new XLDrawingSize(this); @@ -18,7 +18,7 @@ Margins = new XLDrawingMargins(this); Web = new XLDrawingWeb(this); } - public IXLDrawingFont Font { get; private set; } + //public IXLDrawingFont Font { get; private set; } public IXLDrawingAlignment Alignment { get; private set; } public IXLDrawingColorsAndLines ColorsAndLines { get; private set; } public IXLDrawingSize Size { get; private set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingWeb.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingWeb.cs index bcff831..c3fe40d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingWeb.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/Style/XLDrawingWeb.cs @@ -7,13 +7,13 @@ { internal class XLDrawingWeb : IXLDrawingWeb { - private readonly IXLDrawingStyle _style; + private readonly IXLDrawingStyle _style; public XLDrawingWeb(IXLDrawingStyle style) { _style = style; } - public String AlternativeText { get; set; } public IXLDrawingStyle SetAlternativeText(String value) { AlternativeText = value; return _style; } + public String AlternateText { get; set; } public IXLDrawingStyle SetAlternateText(String value) { AlternateText = value; return _style; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs index 3ce75c1..9f55075 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs @@ -8,6 +8,7 @@ public XLDrawing() { Style = new XLDrawingStyle(); + Position = new XLDrawingPosition(); } public Int32 ShapeId { get; internal set; } @@ -37,56 +38,8 @@ Description = description; return Container; } - - public Int32 FirstColumn { get; set; } - public T SetFirstColumn(Int32 firstColumn) - { - FirstColumn = firstColumn; - return Container; - } - public Int32 FirstColumnOffset { get; set; } - public T SetFirstColumnOffset(Int32 firstColumnOffset) - { - FirstColumnOffset = firstColumnOffset; - return Container; - } - public Int32 FirstRow { get; set; } - public T SetFirstRow(Int32 firstRow) - { - FirstRow = firstRow; - return Container; - } - public Int32 FirstRowOffset { get; set; } - public T SetFirstRowOffset(Int32 firstRowOffset) - { - FirstRowOffset = firstRowOffset; - return Container; - } - public Int32 LastColumn { get; set; } - public T SetLastColumn(Int32 firstColumn) - { - LastColumn = firstColumn; - return Container; - } - public Int32 LastColumnOffset { get; set; } - public T SetLastColumnOffset(Int32 firstColumnOffset) - { - LastColumnOffset = firstColumnOffset; - return Container; - } - public Int32 LastRow { get; set; } - public T SetLastRow(Int32 firstRow) - { - LastRow = firstRow; - return Container; - } - public Int32 LastRowOffset { get; set; } - public T SetLastRowOffset(Int32 firstRowOffset) - { - LastRowOffset = firstRowOffset; - return Container; - } + public IXLDrawingPosition Position { get; private set; } public Int32 ZOrder { get; set; } public T SetZOrder(Int32 zOrder) @@ -156,5 +109,6 @@ public IXLDrawingStyle Style { get; private set; } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawingPosition.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawingPosition.cs new file mode 100644 index 0000000..ccb73ae --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawingPosition.cs @@ -0,0 +1,20 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLDrawingPosition: IXLDrawingPosition + { + public Int32 Column { get; set; } + public IXLDrawingPosition SetColumn(Int32 column) { Column = column; return this; } + public Double ColumnOffset { get; set; } + public IXLDrawingPosition SetColumnOffset(Double columnOffset) { ColumnOffset = columnOffset; return this; } + + public Int32 Row { get; set; } + public IXLDrawingPosition SetRow(Int32 row) { Row = row; return this; } + public Double RowOffset { get; set; } + public IXLDrawingPosition SetRowOffset(Double rowOffset) { RowOffset = rowOffset; return this; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs index a4706a3..5d2867d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs @@ -1,5 +1,6 @@ using System; using DocumentFormat.OpenXml.Spreadsheet; +using DocumentFormat.OpenXml.Vml; namespace ClosedXML.Excel { @@ -492,7 +493,6 @@ #endregion } } - public static DynamicFilterValues ToOpenXml(this XLFilterDynamicType value) { switch (value) @@ -505,7 +505,6 @@ #endregion } } - public static SheetViewValues ToOpenXml(this XLSheetViewOptions value) { switch (value) @@ -519,7 +518,21 @@ #endregion } } - + public static StrokeLineStyleValues ToOpenXml(this XLLineStyle value) + { + switch (value) + { + case XLLineStyle.Single : return StrokeLineStyleValues.Single ; + case XLLineStyle.ThickBetweenThin: return StrokeLineStyleValues.ThickBetweenThin; + case XLLineStyle.ThickThin: return StrokeLineStyleValues.ThickThin; + case XLLineStyle.ThinThick: return StrokeLineStyleValues.ThinThick; + case XLLineStyle.ThinThin: return StrokeLineStyleValues.ThinThin; + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } #endregion #region To ClosedXml public static XLFontUnderlineValues ToClosedXml(this UnderlineValues value) @@ -1020,7 +1033,6 @@ #endregion } } - public static XLSheetViewOptions ToClosedXml(this SheetViewValues value) { switch (value) @@ -1035,6 +1047,22 @@ #endregion } } + public static XLLineStyle ToClosedXml(this StrokeLineStyleValues value) + { + switch (value) + { + case StrokeLineStyleValues.Single: return XLLineStyle.Single; + case StrokeLineStyleValues.ThickBetweenThin: return XLLineStyle.ThickBetweenThin; + case StrokeLineStyleValues.ThickThin: return XLLineStyle.ThickThin; + case StrokeLineStyleValues.ThinThick: return XLLineStyle.ThinThick; + case StrokeLineStyleValues.ThinThin: return XLLineStyle.ThinThin; + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 1119c2d..6cc99a0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -73,6 +73,8 @@ IXLCells Cells(String cells); + IXLCells Cells(Func predicate); + /// /// Returns the collection of cells that have a value. Formats are ignored. /// @@ -84,6 +86,10 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeFormats); + IXLCells CellsUsed(Func predicate); + + IXLCells CellsUsed(Boolean includeFormats, Func predicate); + /// /// Returns the first cell of this range. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index dda7b7a..90d0fd1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -251,6 +251,12 @@ return Ranges(cells).Cells(); } + public IXLCells Cells(Func predicate) + { + var cells = new XLCells(false, false, predicate) { RangeAddress }; + return cells; + } + public IXLCells CellsUsed() { var cells = new XLCells(true, false) {RangeAddress}; @@ -699,6 +705,18 @@ return cells; } + public IXLCells CellsUsed(Func predicate) + { + var cells = new XLCells(true, false, predicate) { RangeAddress }; + return cells; + } + + public IXLCells CellsUsed(Boolean includeFormats, Func predicate) + { + var cells = new XLCells(true, includeFormats, predicate) { RangeAddress }; + return cells; + } + public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns) { return InsertColumnsAfter(numberOfColumns, true); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 6352ec7..eca622d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -294,38 +294,25 @@ LoadFont(runProperties, rt); } - var shape = xdoc.Root.Elements().First(e => (string)e.Attribute("type") == "#_x0000_t202"); + var shape = xdoc.Root.Element("xml").Elements().First(e => (string)e.Attribute("type") == "#_x0000_t202"); LoadShapeProperties(xlComment, shape); - var insetmode = (string)shape.Attribute("insetmode"); - xlComment.Style.Margins.Automatic = insetmode.Equals("auto"); - var clientData = shape.Element("ClientData"); - var moveWithCells = clientData.Element("MoveWithCells"); + var clientData = shape.Elements().First(e => e.Name.LocalName == "ClientData"); + LoadClientData(xlComment, clientData); + var textBox = shape.Elements().First(e=>e.Name.LocalName == "textbox"); + LoadTextBox(xlComment, textBox); + + var alt = shape.Attribute("alt"); + if (alt != null) xlComment.Style.Web.SetAlternateText(alt.Value); + + LoadColorsAndLines(xlComment, shape); + + //var insetmode = (string)shape.Attributes().First(a=> a.Name.LocalName == "insetmode"); + //xlComment.Style.Margins.Automatic = insetmode != null && insetmode.Equals("auto"); + shape.Remove(); - // **** 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 @@ -350,25 +337,196 @@ LoadDefinedNames(workbook); } - private void LoadShapeProperties(IXLDrawing xlDrawing, XElement shape) + private void LoadColorsAndLines(IXLDrawing drawing, XElement shape) { - var style = (string)shape.Attribute("style"); + var strokeColor = shape.Attribute("strokecolor"); + if (strokeColor != null) drawing.Style.ColorsAndLines.LineColor = XLColor.FromHtml(strokeColor.Value); + + var strokeWeight = shape.Attribute("strokeweight"); + if (strokeWeight != null) drawing.Style.ColorsAndLines.LineWeight = Double.Parse(strokeWeight.Value.Substring(0, strokeWeight.Value.Length - 2)); + + var fillColor = shape.Attribute("fillcolor"); + if (fillColor != null) drawing.Style.ColorsAndLines.FillColor = XLColor.FromHtml(fillColor.Value); + + var fill = shape.Elements().First(e => e.Name.LocalName == "fill"); + if (fill != null) + { + var opacity = fill.Attribute("opacity"); + if (opacity != null) + { + String opacityVal = opacity.Value; + if (opacityVal.EndsWith("f")) + drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1)) / 65536.0; + else + drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal); + } + } + + var stroke = shape.Elements().First(e=>e.Name.LocalName == "stroke"); + if (stroke != null) + { + var opacity = stroke.Attribute("opacity"); + if (opacity != null) + { + String opacityVal = opacity.Value; + if (opacityVal.EndsWith("f")) + drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1)) / 65536.0; + else + drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal); + } + var dashStyle = stroke.Attribute("dashstyle"); + if (dashStyle != null) + { + String dashStyleVal = dashStyle.Value; + if (dashStyleVal == "1 1" || dashStyleVal == "shortdot") + { + var endCap = stroke.Attribute("endcap"); + if (endCap != null && endCap.Value == "round") + drawing.Style.ColorsAndLines.LineDash = XLDashStyle.RoundDot; + else + drawing.Style.ColorsAndLines.LineDash = XLDashStyle.SquareDot; + } + else + { + switch (dashStyleVal) + { + case "dash": drawing.Style.ColorsAndLines.LineDash = XLDashStyle.Dash; break; + case "dashDot": drawing.Style.ColorsAndLines.LineDash = XLDashStyle.DashDot; break; + case "longDash": drawing.Style.ColorsAndLines.LineDash = XLDashStyle.LongDash; break; + case "longDashDot": drawing.Style.ColorsAndLines.LineDash = XLDashStyle.LongDashDot; break; + case "longDashDotDot": drawing.Style.ColorsAndLines.LineDash = XLDashStyle.LongDashDotDot; break; + } + } + } + } + } + + private void LoadTextBox(IXLDrawing xlDrawing, XElement textBox) + { + var attStyle = textBox.Attribute("style"); + if (attStyle != null) LoadTextBoxStyle(xlDrawing, attStyle); + + var attInset = textBox.Attribute("inset"); + if (attInset != null) LoadTextBoxInset(xlDrawing, attInset); + } + + private void LoadTextBoxInset(IXLDrawing xlDrawing, XAttribute attInset) + { + var split = attInset.Value.Split(','); + xlDrawing.Style.Margins.Left = GetInsetValue(split[0]); + xlDrawing.Style.Margins.Top = GetInsetValue(split[1]); + xlDrawing.Style.Margins.Right = GetInsetValue(split[2]); + xlDrawing.Style.Margins.Bottom = GetInsetValue(split[3]); + } + + private double GetInsetValue(string value) + { + String v = value.Trim(); + if (v.EndsWith("pt")) + return Double.Parse(v.Substring(0, v.Length - 2)) / 72.0; + else + return Double.Parse(v.Substring(0, v.Length - 2)); + } + + private static void LoadTextBoxStyle(IXLDrawing xlDrawing, XAttribute attStyle) + { + var style = attStyle.Value; var attributes = style.Split(';'); foreach (String pair in attributes) { var split = pair.Split(':'); + if (split.Length != 2) continue; + + var attribute = split[0].Trim().ToLower(); + var value = split[1].Trim(); + Boolean isVertical = false; + switch (attribute) + { + case "mso-fit-shape-to-text": xlDrawing.Style.Size.SetAutomaticSize(value.Equals("t")); break; + case "mso-layout-flow-alt": + if (value.Equals("bottom-to-top")) xlDrawing.Style.Alignment.SetOrientation(XLDrawingTextOrientation.BottomToTop); + else if (value.Equals("top-to-bottom")) xlDrawing.Style.Alignment.SetOrientation(XLDrawingTextOrientation.Vertical); + break; + case "layout-flow": isVertical = value.Equals("vertical"); break; + case "mso-direction-alt": if (value == "auto") xlDrawing.Style.Alignment.Direction = XLDrawingTextDirection.Context; break; + case "direction": if (value == "RTL") xlDrawing.Style.Alignment.Direction = XLDrawingTextDirection.RightToLeft; break; + //case "margin-bottom": xlDrawing.Style.Margins.Bottom = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "width": xlDrawing.Style.Size.Width = Double.Parse(value.Replace("pt", String.Empty)) / 7.5; break; + //case "height": xlDrawing.Style.Size.Height = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "z-index": xlDrawing.ZOrder = Int32.Parse(value); break; + } + if (isVertical && xlDrawing.Style.Alignment.Orientation == XLDrawingTextOrientation.LeftToRight) + xlDrawing.Style.Alignment.Orientation = XLDrawingTextOrientation.TopToBottom; + } + } + + private void LoadClientData(IXLDrawing drawing, XElement clientData) + { + var anchor = clientData.Elements().First(e=>e.Name.LocalName == "Anchor"); + if (anchor != null) LoadClientDataAnchor(drawing, anchor); + + LoadDrawingPositioning(drawing, clientData); + LoadDrawingProtection(drawing, clientData); + } + + private void LoadDrawingProtection(IXLDrawing drawing, XElement clientData) + { + var lockedElement = clientData.Elements().First(e => e.Name.LocalName == "Locked"); + var lockTextElement = clientData.Elements().First(e => e.Name.LocalName == "LockText"); + Boolean locked = lockedElement != null && lockedElement.Value.ToLower() == "true"; + Boolean lockText = lockTextElement != null && lockTextElement.Value.ToLower() == "true"; + drawing.Style.Protection.Locked = locked; + drawing.Style.Protection.LockText = lockText; + + } + + private static void LoadDrawingPositioning(IXLDrawing drawing, XElement clientData) + { + var moveWithCellsElement = clientData.Elements().First(e => e.Name.LocalName == "MoveWithCells"); + var sizeWithCellsElement = clientData.Elements().First(e => e.Name.LocalName == "SizeWithCells"); + Boolean moveWithCells = moveWithCellsElement != null && moveWithCellsElement.Value.ToLower() == "true"; + Boolean sizeWithCells = sizeWithCellsElement != null && sizeWithCellsElement.Value.ToLower() == "true"; + if (moveWithCells && !sizeWithCells) + drawing.Style.Properties.Positioning = XLDrawingAnchor.MoveWithCells; + else if (moveWithCells && sizeWithCells) + drawing.Style.Properties.Positioning = XLDrawingAnchor.MoveAndSizeWithCells; + else + drawing.Style.Properties.Positioning = XLDrawingAnchor.MoveWithCells; + } + + private static void LoadClientDataAnchor(IXLDrawing drawing, XElement anchor) + { + var location = anchor.Value.Split(','); + drawing.Position.Column = int.Parse(location[0]) + 1; + drawing.Position.ColumnOffset = Double.Parse(location[1]) / 7.2; + drawing.Position.Row = int.Parse(location[2]) + 1; + drawing.Position.RowOffset = Double.Parse(location[3]); + } + + private void LoadShapeProperties(IXLDrawing xlDrawing, XElement shape) + { + var attStyle = shape.Attribute("style"); + if (attStyle == null) return; + + var style = attStyle.Value; + var attributes = style.Split(';'); + foreach (String pair in attributes) + { + var split = pair.Split(':'); + if (split.Length != 2) continue; + var attribute = split[0].Trim().ToLower(); var value = split[1].Trim(); switch (attribute) { case "visibility": xlDrawing.Visible = value.ToLower().Equals("visible"); break; - case "margin-left": xlDrawing.Style.Margins.Left = Double.Parse(value.Replace("pt", String.Empty)); break; - case "margin-right": xlDrawing.Style.Margins.Right = Double.Parse(value.Replace("pt", String.Empty)); break; - case "margin-top": xlDrawing.Style.Margins.Top = Double.Parse(value.Replace("pt", String.Empty)); break; - case "margin-bottom": xlDrawing.Style.Margins.Bottom = Double.Parse(value.Replace("pt", String.Empty)); break; - case "width": xlDrawing.Style.Size.Width = Double.Parse(value.Replace("pt", String.Empty)); break; - case "height": xlDrawing.Style.Size.Height = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "margin-left": xlDrawing.Style.Margins.Left = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "margin-right": xlDrawing.Style.Margins.Right = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "margin-top": xlDrawing.Style.Margins.Top = Double.Parse(value.Replace("pt", String.Empty)); break; + //case "margin-bottom": xlDrawing.Style.Margins.Bottom = Double.Parse(value.Replace("pt", String.Empty)); break; + case "width": xlDrawing.Style.Size.Width = Double.Parse(value.Replace("pt", String.Empty)) / 7.5; break; + case "height": xlDrawing.Style.Size.Height = Double.Parse(value.Replace("pt", String.Empty)) ; break; case "z-index": xlDrawing.ZOrder = Int32.Parse(value); break; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 6795c94..37c60f4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4312,17 +4312,15 @@ foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells(c=>c.HasComment)) { Comment comment = new Comment() { Reference = c.Address.ToStringRelative() }; - String authorName = StringExtensions.IsNullOrWhiteSpace(c.Comment.Author) - ? Environment.UserName - : c.Comment.Author; + String authorName = c.Comment.Author; - Int32 authorId; - if (!authorsDict.TryGetValue(authorName, out authorId)) - { - authorId = authorsDict.Count; - authorsDict.Add(authorName, authorId); - } - comment.AuthorId = (UInt32)authorId; + Int32 authorId; + if (!authorsDict.TryGetValue(authorName, out authorId)) + { + authorId = authorsDict.Count; + authorsDict.Add(authorName, authorId); + } + comment.AuthorId = (UInt32)authorId; CommentText commentText = new CommentText(); foreach (var rt in c.Comment) @@ -4573,28 +4571,128 @@ var columnNumber = c.Address.ColumnNumber; String shapeId = String.Format("_x0000_s{0}", c.Comment.ShapeId); // Unique per cell (workbook?), e.g.: "_x0000_s1026" - - return new Vml.Shape( - new Vml.Fill { Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2) }, + Vml.Spreadsheet.Anchor anchor = GetAnchor(c); + Vml.TextBox textBox = GetTextBox(c.Comment.Style); + var fill = new Vml.Fill { Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2) }; + if (c.Comment.Style.ColorsAndLines.FillTransparency < 1) + fill.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.FillTransparency), 2).ToString(); + Vml.Stroke stroke = GetStroke(c); + var shape = new Vml.Shape( + fill, + stroke, new Vml.Shadow() { On = true, Color = "black", Obscured = true }, new Vml.Path() { ConnectionPointType = Vml.Office.ConnectValues.None }, - new Vml.TextBox( /*
*/ ) { Style = "mso-direction-alt:auto" }, + textBox, new Vml.Spreadsheet.ClientData( new Vml.Spreadsheet.MoveWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.Absolute ? "True": "False"), // counterintuitive new Vml.Spreadsheet.ResizeWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.MoveAndSizeWithCells ? "False": "True"), // counterintuitive - new Vml.Spreadsheet.Anchor() { Text = string.Format("{0}, 15, {1}, 2, {2}, 31, {3}, 1", columnNumber, rowNumber - 1, columnNumber + 2, rowNumber + 3) }, + anchor, + new Vml.Spreadsheet.HorizontalTextAlignment(c.Comment.Style.Alignment.Horizontal.ToString()), + new Vml.Spreadsheet.VerticalTextAlignment(c.Comment.Style.Alignment.Vertical.ToString()), new Vml.Spreadsheet.AutoFill("False"), new Vml.Spreadsheet.CommentRowTarget() { Text = (rowNumber - 1).ToString() }, - new Vml.Spreadsheet.CommentColumnTarget() { Text = (columnNumber - 1).ToString() } + new Vml.Spreadsheet.CommentColumnTarget() { Text = (columnNumber - 1).ToString() }, + new Vml.Spreadsheet.Locked(c.Comment.Style.Protection.Locked ? "True" : "False"), + new Vml.Spreadsheet.LockText(c.Comment.Style.Protection.LockText ? "True" : "False"), + new Vml.Spreadsheet.Visible(c.Comment.Visible ? "True" : "False") ) { ObjectType = Vml.Spreadsheet.ObjectValues.Note } ) { Id = shapeId, Type = "#" + shapeTypeId, Style = GetCommentStyle(c), - FillColor = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2)//, - //InsetMode = c.Comment.Style.Margins.Automatic ? Vml.Office.InsetMarginValues.Auto : Vml.Office.InsetMarginValues.Custom + FillColor = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2), + StrokeColor = "#" + c.Comment.Style.ColorsAndLines.LineColor.Color.ToHex().Substring(2), + StrokeWeight = String.Format("{0}pt",c.Comment.Style.ColorsAndLines.LineWeight), + InsetMode = c.Comment.Style.Margins.Automatic ? Vml.Office.InsetMarginValues.Auto : Vml.Office.InsetMarginValues.Custom }; + if (!StringExtensions.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) + shape.Alternate = c.Comment.Style.Web.AlternateText; + + + return shape; + } + + private static Vml.Stroke GetStroke(XLCell c) + { + var lineDash = c.Comment.Style.ColorsAndLines.LineDash; + var stroke = new Vml.Stroke() { LineStyle = c.Comment.Style.ColorsAndLines.LineStyle.ToOpenXml(), + DashStyle= lineDash == XLDashStyle.RoundDot || lineDash == XLDashStyle.SquareDot ? "shortdot" : lineDash.ToString().ToLower() + }; + if (lineDash == XLDashStyle.RoundDot) + stroke.EndCap = Vml.StrokeEndCapValues.Round; + if (c.Comment.Style.ColorsAndLines.LineTransparency < 1) + stroke.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.LineTransparency), 2).ToString(); + return stroke; + } + + private static Vml.TextBox GetTextBox(IXLDrawingStyle ds) + { + // + // + + var sb = new StringBuilder(); + var a = ds.Alignment; + + if (a.Direction == XLDrawingTextDirection.Context) + sb.Append("mso-direction-alt:auto;"); + else if (a.Direction == XLDrawingTextDirection.RightToLeft) + sb.Append("direction:RTL;"); + + if (a.Orientation != XLDrawingTextOrientation.LeftToRight) + { + sb.Append("layout-flow:vertical;"); + if (a.Orientation == XLDrawingTextOrientation.BottomToTop) + sb.Append("mso-layout-flow-alt:bottom-to-top;"); + else if (a.Orientation == XLDrawingTextOrientation.Vertical) + sb.Append("mso-layout-flow-alt:top-to-bottom;"); + } + if (a.AutomaticSize) + sb.Append("mso-fit-shape-to-text:t;"); + var retVal = new Vml.TextBox() { Style = sb.ToString() }; + var dm = ds.Margins; + if (!dm.Automatic) + retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", dm.Left, dm.Top, dm.Right, dm.Bottom); + + return retVal; + } + + private static Vml.Spreadsheet.Anchor GetAnchor(XLCell cell) + { + var c = cell.Comment; + Double cWidth = c.Style.Size.Width; //(c.Style.Size.Width * 72.0 / 5.625); + Int32 fcNumber = c.Position.Column - 1; + Int32 fcOffset = Convert.ToInt32(c.Position.ColumnOffset * 7.5); + Double widthFromColumns = cell.Worksheet.Column(c.Position.Column).Width - c.Position.ColumnOffset; + XLCell lastCell = cell.CellRight(c.Position.Column - cell.Address.ColumnNumber); + while (widthFromColumns <= cWidth) + { + lastCell = lastCell.CellRight(); + widthFromColumns += lastCell.WorksheetColumn().Width; + } + + Int32 lcNumber = lastCell.WorksheetColumn().ColumnNumber() - 1; + Int32 lcOffset = Convert.ToInt32((lastCell.WorksheetColumn().Width - (widthFromColumns - cWidth)) * 7.5); + + Double cHeight = c.Style.Size.Height; //c.Style.Size.Height * 72.0; + Int32 frNumber = c.Position.Row - 1; + Int32 frOffset = Convert.ToInt32(c.Position.RowOffset); + Double heightFromRows = cell.Worksheet.Row(c.Position.Row).Height - c.Position.RowOffset; + lastCell = cell.CellBelow(c.Position.Row - cell.Address.RowNumber); + while (heightFromRows <= cHeight) + { + lastCell = lastCell.CellBelow(); + heightFromRows += lastCell.WorksheetRow().Height; + } + + Int32 lrNumber = lastCell.WorksheetRow().RowNumber() - 1; + Int32 lrOffset = Convert.ToInt32(lastCell.WorksheetRow().Height - (heightFromRows - cHeight)); + return new Vml.Spreadsheet.Anchor() { Text = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", + fcNumber, fcOffset, + frNumber, frOffset, + lcNumber, lcOffset, + lrNumber, lrOffset + ) }; } private static StringValue GetCommentStyle(XLCell cell) @@ -4606,26 +4704,26 @@ sb.Append(c.Visible ? "visible" : "hidden"); sb.Append(";"); - var margins = c.Style.Margins; - sb.Append("margin-left:"); - sb.Append(margins.Left.ToString()); - sb.Append("pt;"); - sb.Append("margin-right:"); - sb.Append(margins.Right.ToString()); - sb.Append("pt;"); - sb.Append("margin-top:"); - sb.Append(margins.Top.ToString()); - sb.Append("pt;"); - sb.Append("margin-bottom:"); - sb.Append(margins.Bottom.ToString()); - sb.Append("pt;"); + //var margins = c.Style.Margins; + //sb.Append("margin-left:"); + //sb.Append(margins.Left.ToString()); + //sb.Append("pt;"); + //sb.Append("margin-right:"); + //sb.Append(margins.Right.ToString()); + //sb.Append("pt;"); + //sb.Append("margin-top:"); + //sb.Append(margins.Top.ToString()); + //sb.Append("pt;"); + //sb.Append("margin-bottom:"); + //sb.Append(margins.Bottom.ToString()); + //sb.Append("pt;"); - //sb.Append("width:"); - //sb.Append(c.Style.Size.Width.ToString()); - //sb.Append("pt;"); - //sb.Append("height:"); - //sb.Append(c.Style.Size.Height.ToString()); - //sb.Append("pt;"); + sb.Append("width:"); + sb.Append(Math.Round(c.Style.Size.Width * 7.5 , 2).ToString()); + sb.Append("pt;"); + sb.Append("height:"); + sb.Append(Math.Round(c.Style.Size.Height, 2).ToString()); + sb.Append("pt;"); sb.Append("z-index:"); sb.Append(c.ZOrder.ToString()); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 26c1860..ae4bf40 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -32,7 +32,7 @@ private Double _rowHeight; private Boolean _tabActive; - + internal Int32 ZOrder = 1; #endregion #region Constructor diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index 452f134..fd5a38b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -7,6 +7,7 @@ { using System.Linq; using System.Text.RegularExpressions; + using System.Drawing; /// /// Common methods @@ -21,6 +22,8 @@ private const Int32 TwoT26 = 26 * 26; internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; + public static readonly Graphics Graphic = Graphics.FromImage(new Bitmap(32, 32)); + public static readonly Double DpiX = Graphic.DpiX; /// /// Gets the column number of a given column letter. @@ -206,6 +209,13 @@ return range.Contains('-') ? range.Replace('-', ':').Split(':') : range.Split(':'); } - + public static Int32 GetPtFromPx(Double px) + { + return Convert.ToInt32(px * 72.0 / DpiX); + } + public static Double GetPxFromPt(Int32 pt) + { + return Convert.ToDouble(pt) * DpiX / 72.0; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index 58c779f..951b4ae 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -170,28 +170,8 @@ var stringFont = new Font(font.FontName, (float)font.FontSize, GetFontStyle(font)); - //var textSize = TextRenderer.MeasureText(text, stringFont); - //double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256; - ////width = (double)decimal.Round((decimal)width + 0.2M, 2); - - //return width + 0; - - double fMaxDigitWidth = 0.0f; - - // I just need a Graphics object. Any reasonable bitmap size will do. - Graphics g = Graphics.FromImage(new Bitmap(200, 200)); - - for (int i = 0; i < 10; ++i) - { - double fDigitWidth = (double)g.MeasureString(i.ToString(), stringFont).Width; - if (fDigitWidth > fMaxDigitWidth) - { - fMaxDigitWidth = fDigitWidth; - } - } - g.Dispose(); - - // Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256 + double fMaxDigitWidth = (double)ExcelHelper.Graphic.MeasureString("X", stringFont).Width; + return Math.Truncate((text.ToCharArray().Count() * fMaxDigitWidth + 5.0) / fMaxDigitWidth * 256.0) / 256.0; } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index a6578cd..af7bcb6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -133,6 +133,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/AddingComments.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/AddingComments.cs new file mode 100644 index 0000000..200e91f --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/AddingComments.cs @@ -0,0 +1,83 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.IO; + +namespace ClosedXML_Examples +{ + public class AddingComments : IXLExample + { + + public void Create(string filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Comments"); + + ws.Cell("A1").SetValue("Hidden").Comment.AddText("Hidden"); + ws.Cell("A2").SetValue("Visible").Comment.AddText("Visible"); + ws.Cell("A3").SetValue("On Top").Comment.AddText("On Top"); + ws.Cell("A4").SetValue("Underneath").Comment.AddText("Underneath"); + ws.Cell("A4").Comment.Style.Alignment.SetVertical(XLDrawingVerticalAlignment.Bottom); + ws.Cell("A3").Comment.SetZOrder(ws.Cell("A4").Comment.ZOrder + 1); + + ws.Cell("D9").Comment.AddText("Vertical"); + ws.Cell("D9").Comment.Style.Alignment.Orientation = XLDrawingTextOrientation.Vertical; + ws.Cell("D9").Comment.Style.Size.SetAutomaticSize(); + + ws.Cell("E9").Comment.AddText("Top to Bottom"); + ws.Cell("E9").Comment.Style.Alignment.Orientation = XLDrawingTextOrientation.TopToBottom; + ws.Cell("E9").Comment.Style.Size.SetAutomaticSize(); + + ws.Cell("F9").Comment.AddText("Bottom to Top"); + ws.Cell("F9").Comment.Style.Alignment.Orientation = XLDrawingTextOrientation.BottomToTop; + ws.Cell("F9").Comment.Style.Size.SetAutomaticSize(); + + ws.Cell("E1").Comment.Position.SetColumn(5); + ws.Cell("E1").Comment.AddText("Start on Col E, on top border"); + ws.Cell("E1").Comment.Style.Size.SetWidth(10); + var cE3 = ws.Cell("E3").Comment; + cE3.AddText("Size and position"); + cE3.Position.SetColumn(5).SetRow(4).SetColumnOffset(7).SetRowOffset(10); + cE3.Style.Size.SetHeight(25).Size.SetWidth(10); + var cE7 = ws.Cell("E7").Comment; + cE7.Position.SetColumn(6).SetRow(7).SetColumnOffset(0).SetRowOffset(0); + cE7.Style.Size.SetHeight(ws.Row(7).Height).Size.SetWidth(ws.Column(6).Width); + + ws.Cell("G1").Comment.AddText("Automatic Size"); + ws.Cell("G1").Comment.Style.Alignment.SetAutomaticSize(); + var cG3 = ws.Cell("G3").Comment; + cG3.SetAuthor("MDeLeon"); + cG3.AddSignature(); + cG3.AddText("This is a test of the emergency broadcast system."); + cG3.AddNewLine(); + cG3.AddText("Do "); + cG3.AddText("NOT").SetFontColor(XLColor.RadicalRed).SetUnderline().SetBold(); + cG3.AddText(" forget it."); + cG3.Style + .Size.SetWidth(25) + .Size.SetHeight(100) + .Alignment.SetDirection(XLDrawingTextDirection.LeftToRight) + .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Distributed) + .Alignment.SetVertical(XLDrawingVerticalAlignment.Center) + .Alignment.SetOrientation(XLDrawingTextOrientation.LeftToRight) + .ColorsAndLines.SetFillColor(XLColor.Cyan) + .ColorsAndLines.SetFillTransparency(0.25) + .ColorsAndLines.SetLineColor(XLColor.DarkBlue) + .ColorsAndLines.SetLineTransparency(0.75) + .ColorsAndLines.SetLineDash(XLDashStyle.DashDot) + .ColorsAndLines.SetLineStyle(XLLineStyle.ThinThick) + .ColorsAndLines.SetLineWeight(5) + .Margins.SetAll(0.25) + .Properties.SetPositioning(XLDrawingAnchor.MoveAndSizeWithCells) + .Protection.SetLocked(false) + .Protection.SetLockText(false) + .Web.SetAlternateText("This won't be released to the web"); + + + ws.CellsUsed(true, c => !c.Address.ToStringRelative().Equals("A1") && c.HasComment).ForEach(c => c.Comment.SetVisible()); + wb.SaveAs(filePath); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs index 92bc63b..163bbea 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Comments/EditingComments.cs @@ -33,7 +33,7 @@ sheet.Cell("B3").Comment.AddText("more comment"); // delete - sheet.Cell("C1").DeleteComment(); + //sheet.Cell("C1").DeleteComment(); // clear contents sheet.Cell("D3").Clear(XLClearOptions.Contents); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 1f6f470..8f68671 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -271,6 +271,9 @@ Excel\Drawings\IXLDrawing.cs + + Excel\Drawings\IXLDrawingPosition.cs + Excel\Drawings\Style\IXLDrawingAlignment.cs @@ -328,6 +331,9 @@ Excel\Drawings\XLDrawing.cs + + Excel\Drawings\XLDrawingPosition.cs + Excel\EnumConverter.cs