diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs index 9e538d7..b4c1668 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/IXLComment.cs @@ -8,9 +8,6 @@ String Author { get; set; } IXLComment SetAuthor(String value); - Boolean Visible { get; set; } - IXLComment SetVisible(); IXLComment SetVisible(Boolean value); - IXLRichString AddSignature(); IXLRichString AddSignature(string username); IXLRichString AddNewLine(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs index 181ea72..7cb4f6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs @@ -31,8 +31,15 @@ Container = this; Anchor = XLDrawingAnchor.MoveAndSizeWithCells; Style = new XLDrawingStyle(); - Style.Size.Height = 4; // I think this is misused for legacy drawing - Style.Size.Width = 2; + 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; + SetVisible(); _worksheet = worksheet; ShapeId = worksheet.Workbook.ShapeIdManager.GetNext(); @@ -62,21 +69,21 @@ 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; } + //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; } - public Boolean Hidden { get; set; } - public IXLComment SetHidden() + public Boolean Visible { get; set; } + public IXLComment SetVisible() { - Hidden = true; + Visible = true; return Container; } - public IXLComment SetHidden(Boolean hidden) + public IXLComment SetVisible(Boolean hidden) { - Hidden = hidden; + Visible = hidden; return Container; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs index 5884a22..e345b6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/IXLDrawing.cs @@ -7,9 +7,9 @@ { Int32 ShapeId { get; } - Boolean Hidden { get; set; } - T SetHidden(); - T SetHidden(Boolean hidden); + Boolean Visible { get; set; } + T SetVisible(); + T SetVisible(Boolean hidden); String Name { get; set; } T SetName(String name); @@ -17,7 +17,7 @@ String Description { get; set; } T SetDescription(String description); - XLDrawingAnchor Anchor { get; set; } + Int32 FirstColumn { get; set; } T SetFirstColumn(Int32 firstColumn); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs index 60cb1a8..3ce75c1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Drawings/XLDrawing.cs @@ -7,21 +7,20 @@ internal T Container; public XLDrawing() { - Anchor = XLDrawingAnchor.MoveAndSizeWithCells; Style = new XLDrawingStyle(); } public Int32 ShapeId { get; internal set; } - public Boolean Hidden { get; set; } - public T SetHidden() + public Boolean Visible { get; set; } + public T SetVisible() { - Hidden = true; + Visible = true; return Container; } - public T SetHidden(Boolean hidden) + public T SetVisible(Boolean hidden) { - Hidden = hidden; + Visible = hidden; return Container; } @@ -38,9 +37,7 @@ Description = description; return Container; } - - public XLDrawingAnchor Anchor { get; set; } - + public Int32 FirstColumn { get; set; } public T SetFirstColumn(Int32 firstColumn) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs index e3a3897..a4706a3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs @@ -506,6 +506,20 @@ } } + public static SheetViewValues ToOpenXml(this XLSheetViewOptions value) + { + switch (value) + { + case XLSheetViewOptions.Normal: return SheetViewValues.Normal; + case XLSheetViewOptions.PageBreakPreview: return SheetViewValues.PageBreakPreview; + case XLSheetViewOptions.PageLayout: return SheetViewValues.PageLayout; + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + #endregion #region To ClosedXml public static XLFontUnderlineValues ToClosedXml(this UnderlineValues value) @@ -1007,6 +1021,20 @@ } } + public static XLSheetViewOptions ToClosedXml(this SheetViewValues value) + { + switch (value) + { + case SheetViewValues.Normal: return XLSheetViewOptions.Normal; + case SheetViewValues.PageBreakPreview: return XLSheetViewOptions.PageBreakPreview; + case SheetViewValues.PageLayout: return XLSheetViewOptions.PageLayout; + + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetView.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetView.cs index 800593e..eee1ed0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetView.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetView.cs @@ -2,6 +2,7 @@ namespace ClosedXML.Excel { + public enum XLSheetViewOptions { Normal, PageBreakPreview, PageLayout } public interface IXLSheetView { /// @@ -29,5 +30,9 @@ /// The rows to freeze. /// The columns to freeze. void Freeze(Int32 rows, Int32 columns); + + XLSheetViewOptions View { get; set; } + + IXLSheetView SetView(XLSheetViewOptions value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs index 7ad2e12..2d7b22d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetView.cs @@ -4,8 +4,10 @@ { internal class XLSheetView: IXLSheetView { - public XLSheetView() { } - public XLSheetView(IXLSheetView sheetView) + public XLSheetView() { + View = XLSheetViewOptions.Normal; + } + public XLSheetView(IXLSheetView sheetView):this() { this.SplitRow = sheetView.SplitRow; this.SplitColumn = sheetView.SplitColumn; @@ -31,5 +33,14 @@ SplitColumn = columns; FreezePanes = true; } + + + public XLSheetViewOptions View { get; set; } + + public IXLSheetView SetView(XLSheetViewOptions value) + { + View = value; + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index f68fe34..7238105 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -22,6 +22,7 @@ using System.Drawing; using Ap; using Op; + using System.Xml.Linq; #endregion @@ -274,10 +275,12 @@ // **** MAYBE FUTURE SHAPE SIZE SUPPORT // var shapes = wsPart.VmlDrawingParts.SelectMany(p => new System.Xml.XmlTextReader(p.GetStream()).Read() - + XDocument xdoc = XDocument.Load(wsPart.VmlDrawingParts.First().GetStream(FileMode.Open)); + foreach (Comment c in comments) { // find cell by reference var cell = ws.Cell(c.Reference); + XLComment xlComment = cell.Comment as XLComment; xlComment.Author = authors[(int)c.AuthorId.Value].InnerText; //xlComment.ShapeId = (Int32)c.ShapeId.Value; @@ -291,6 +294,10 @@ LoadFont(runProperties, rt); } + var shape = xdoc.Root.Elements().First(e => (string)e.Attribute("type") == "#_x0000_t202"); + LoadShapeProperties(xlComment, shape); + + shape.Remove(); // **** MAYBE FUTURE SHAPE SIZE SUPPORT //var shape = shapes.FirstOrDefault(sh => { // var cd = sh.GetFirstChild(); @@ -338,6 +345,32 @@ LoadDefinedNames(workbook); } + private void LoadShapeProperties(IXLDrawing xlDrawing, XElement shape) + { + var style = (string)shape.Attribute("style"); + var attributes = style.Split(';'); + foreach (String pair in attributes) + { + var split = pair.Split(':'); + 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 "z-index": xlDrawing.ZOrder = Int32.Parse(value); break; + } + } + + } + + private void LoadDefinedNames(Workbook workbook) { if (workbook.DefinedNames == null) return; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 0f1bfa8..ac235ff 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2507,6 +2507,11 @@ else sheetView.RightToLeft = null; + if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal) + sheetView.View = null; + else + sheetView.View = xlWorksheet.SheetView.View.ToOpenXml(); + var pane = sheetView.Elements().FirstOrDefault(); if (pane == null) { @@ -2514,6 +2519,8 @@ sheetView.AppendChild(pane); } + + pane.State = PaneStateValues.FrozenSplit; Double hSplit = xlWorksheet.SheetView.SplitColumn; Double ySplit = xlWorksheet.SheetView.SplitRow; @@ -4414,13 +4421,13 @@ ms.Position = 0; XmlTextReader reader = new XmlTextReader(ms); XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8); - //if (ms.Length == 0) - // writer.WriteStartElement("xml"); - //else - //{ - // //ms.Position = 0; - // CopyXml(reader, writer); - //} + if (ms.Length == 0) + writer.WriteStartElement("xml"); + else + { + //ms.Position = 0; + CopyXml(reader, writer); + } writer.WriteStartElement("xml"); @@ -4457,17 +4464,17 @@ GenerateShape(c, shapeTypeId).WriteTo(writer); } - if (ms.Length > 0) - { - //ms.Position = 0; - //var sb = new StringBuilder(); - //while (reader.Read()) - // sb.Append(reader.ReadString()); - //var t = sb.ToString(); - CopyXml(reader, writer); - } + //if (ms.Length > 0) + //{ + // //ms.Position = 0; + // //var sb = new StringBuilder(); + // //while (reader.Read()) + // // sb.Append(reader.ReadString()); + // //var t = sb.ToString(); + // CopyXml(reader, writer); + //} - //writer.WriteEndElement(); + writer.WriteEndElement(); writer.Flush(); writer.Close(); @@ -4508,7 +4515,7 @@ } break; case XmlNodeType.EndElement: - if (docElemName == xtr.Name) + if (docElemName == xtr.Name || xtr.Name == "xml") b = false; else xtw.WriteEndElement(); @@ -4565,16 +4572,6 @@ var rowNumber = c.Address.RowNumber; var columnNumber = c.Address.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 : 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; - - String shapeId = String.Format("_x0000_s{0}", c.Comment.ShapeId); // Unique per cell (workbook?), e.g.: "_x0000_s1026" return new Vml.Shape( @@ -4583,9 +4580,9 @@ new Vml.Path() { ConnectionPointType = Vml.Office.ConnectValues.None }, new Vml.TextBox( /*
*/ ) { Style = "mso-direction-alt:auto" }, new Vml.Spreadsheet.ClientData( - 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.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) }, new Vml.Spreadsheet.AutoFill("False"), new Vml.Spreadsheet.CommentRowTarget() { Text = (rowNumber - 1).ToString() }, new Vml.Spreadsheet.CommentColumnTarget() { Text = (columnNumber - 1).ToString() } @@ -4600,10 +4597,41 @@ }; } - private static StringValue GetCommentStyle(XLCell c) + private static StringValue GetCommentStyle(XLCell cell) { - String visibility = c.Comment.Visible ? "visible" : "hidden"; - return "position:absolute; margin-left:59.25pt;margin-top:1.5pt;width:96pt;height:60pt;z-index:1; visibility:" + visibility; + var c = cell.Comment; + var sb = new StringBuilder(); + + sb.Append("visibility:"); + 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;"); + + 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("z-index:"); + sb.Append(c.ZOrder.ToString()); + + + return "position:absolute; " + sb.ToString(); } }