diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 1132fc3..a2e661e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -525,17 +525,32 @@ if(sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); - //if (includeFormats) - //{ - // using (var rowsUsed = Worksheet.Rows(1, 1)) - // { - // foreach (var row in rowsUsed) - // { - // if(!row.IsEmpty(true)) - // return Worksheet.Cell() - // } - // } - //} + if (includeFormats) + { + Int32 ro = 0; + var rowsUsed = Worksheet.Internals.RowsCollection.Where(r => + r.Key >= RangeAddress.FirstAddress.RowNumber + && r.Key <= RangeAddress.LastAddress.RowNumber); + + if (rowsUsed.Any()) + ro = rowsUsed.First().Key; + + + if (ro > 0) + { + Int32 co = 0; + var columnsUsed = Worksheet.Internals.ColumnsCollection.Where(r => + r.Key >= RangeAddress.FirstAddress.ColumnNumber + && r.Key <= RangeAddress.LastAddress.ColumnNumber); + + if (columnsUsed.Any()) + ro = columnsUsed.First().Key; + + if (co > 0) + return Worksheet.Cell(ro, co); + } + + } return null; @@ -932,23 +947,28 @@ RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber - numberOfColumns + RangeAddress.FirstAddress.ColumnNumber - 1 ); if (formatFromLeft && rangeToReturn.RangeAddress.FirstAddress.ColumnNumber > 1) { - var model = rangeToReturn.FirstColumn().ColumnLeft(); - var modelFirstRow = model.FirstCellUsed(true); - var modelLastRow = model.LastCellUsed(true); - if (modelLastRow != null) + using (var firstColumnUsed = rangeToReturn.FirstColumn()) { - Int32 firstRoReturned = modelFirstRow.Address.RowNumber - - model.RangeAddress.FirstAddress.RowNumber + 1; - Int32 lastRoReturned = modelLastRow.Address.RowNumber - - model.RangeAddress.FirstAddress.RowNumber + 1; - for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) + using (var model = firstColumnUsed.ColumnLeft()) { - rangeToReturn.Row(ro).Style = model.Cell(ro).Style; + var modelFirstRow = model.FirstCellUsed(true); + var modelLastRow = model.LastCellUsed(true); + if (modelLastRow != null) + { + Int32 firstRoReturned = modelFirstRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + Int32 lastRoReturned = modelLastRow.Address.RowNumber + - model.RangeAddress.FirstAddress.RowNumber + 1; + for (Int32 ro = firstRoReturned; ro <= lastRoReturned; ro++) + { + rangeToReturn.Row(ro).Style = model.Cell(ro).Style; + } + } } } } @@ -1111,7 +1131,7 @@ var rangeToReturn = Worksheet.Range( RangeAddress.FirstAddress.RowNumber - numberOfRows, RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber - numberOfRows, + RangeAddress.FirstAddress.RowNumber - 1, RangeAddress.LastAddress.ColumnNumber ); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index fda5dd1..112a172 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -349,7 +349,7 @@ 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)); + if (strokeWeight != null) drawing.Style.ColorsAndLines.LineWeight = Double.Parse(strokeWeight.Value.Substring(0, strokeWeight.Value.Length - 2), CultureInfo.InvariantCulture); var fillColor = shape.Attribute("fillcolor"); if (fillColor != null) drawing.Style.ColorsAndLines.FillColor = XLColor.FromHtml(fillColor.Value); @@ -362,9 +362,9 @@ { String opacityVal = opacity.Value; if (opacityVal.EndsWith("f")) - drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1)) / 65536.0; + drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1), CultureInfo.InvariantCulture) / 65536.0; else - drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal); + drawing.Style.ColorsAndLines.FillTransparency = Double.Parse(opacityVal, CultureInfo.InvariantCulture); } } @@ -376,10 +376,11 @@ { String opacityVal = opacity.Value; if (opacityVal.EndsWith("f")) - drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1)) / 65536.0; + drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal.Substring(0, opacityVal.Length - 1), CultureInfo.InvariantCulture) / 65536.0; else - drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal); + drawing.Style.ColorsAndLines.LineTransparency = Double.Parse(opacityVal, CultureInfo.InvariantCulture); } + var dashStyle = stroke.Attribute("dashstyle"); if (dashStyle != null) { @@ -404,6 +405,20 @@ } } } + + var lineStyle = stroke.Attribute("linestyle"); + if (lineStyle != null) + { + String lineStyleVal = lineStyle.Value.ToLower(); + switch (lineStyleVal) + { + case "single": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.Single ; break; + case "thickbetweenthin": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThickBetweenThin; break; + case "thickthin": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThickThin; break; + case "thinthick": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThinThick; break; + case "thinthin": drawing.Style.ColorsAndLines.LineStyle = XLLineStyle.ThinThin; break; + } + } } } @@ -429,9 +444,9 @@ { String v = value.Trim(); if (v.EndsWith("pt")) - return Double.Parse(v.Substring(0, v.Length - 2)) / 72.0; + return Double.Parse(v.Substring(0, v.Length - 2), CultureInfo.InvariantCulture) / 72.0; else - return Double.Parse(v.Substring(0, v.Length - 2)); + return Double.Parse(v.Substring(0, v.Length - 2), CultureInfo.InvariantCulture); } private static void LoadTextBoxStyle(IXLDrawing xlDrawing, XAttribute attStyle) @@ -456,10 +471,6 @@ 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; @@ -524,9 +535,9 @@ { var location = anchor.Value.Split(','); drawing.Position.Column = int.Parse(location[0]) + 1; - drawing.Position.ColumnOffset = Double.Parse(location[1]) / 7.2; + drawing.Position.ColumnOffset = Double.Parse(location[1], CultureInfo.InvariantCulture) / 7.2; drawing.Position.Row = int.Parse(location[2]) + 1; - drawing.Position.RowOffset = Double.Parse(location[3]); + drawing.Position.RowOffset = Double.Parse(location[3], CultureInfo.InvariantCulture); } private void LoadShapeProperties(IXLDrawing xlDrawing, XElement shape) @@ -547,12 +558,8 @@ 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)) / 7.5; break; - case "height": xlDrawing.Style.Size.Height = Double.Parse(value.Replace("pt", String.Empty)) ; break; + case "width": xlDrawing.Style.Size.Width = Double.Parse(value.Replace("pt", String.Empty), CultureInfo.InvariantCulture) / 7.5; break; + case "height": xlDrawing.Style.Size.Height = Double.Parse(value.Replace("pt", String.Empty), CultureInfo.InvariantCulture); break; case "z-index": xlDrawing.ZOrder = Int32.Parse(value); break; } } @@ -771,7 +778,6 @@ } else if (cell.DataType == CellValues.Date) { - //xlCell.cellValue = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture)); xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); xlCell._dataType = XLCellValues.DateTime; } @@ -1005,7 +1011,7 @@ if (isText) xlFilter.Value = filter.Val.Value; else - xlFilter.Value = Double.Parse(filter.Val.Value); + xlFilter.Value = Double.Parse(filter.Val.Value, CultureInfo.InvariantCulture); if (filter.Operator != null) xlFilter.Operator = filter.Operator.Value.ToClosedXml(); @@ -1067,7 +1073,7 @@ } else { - xlFilter.Value = Double.Parse(filter.Val.Value); + xlFilter.Value = Double.Parse(filter.Val.Value, CultureInfo.InvariantCulture); condition = o => (o as IComparable).CompareTo(xlFilter.Value) == 0; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 7824682..b32c88a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4385,23 +4385,15 @@ var ms = new MemoryStream(); CopyStream(vmlDrawingPart.GetStream(FileMode.OpenOrCreate), ms); 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); - //} writer.WriteStartElement("xml"); const string shapeTypeId = "_x0000_t202"; // arbitrary, assigned by office new Vml.Shapetype( - new Vml.Stroke() { JoinStyle = Vml.StrokeJoinStyleValues.Miter }, - new Vml.Path() { AllowGradientShape = true, ConnectionPointType = Vml.Office.ConnectValues.Rectangle } + new Vml.Stroke { JoinStyle = Vml.StrokeJoinStyleValues.Miter }, + new Vml.Path { AllowGradientShape = true, ConnectionPointType = Vml.Office.ConnectValues.Rectangle } ) { Id = shapeTypeId, @@ -4421,13 +4413,8 @@ if (ms.Length > 0) { ms.Position = 0; - //var sb = new StringBuilder(); - //while (reader.Read()) - // sb.Append(reader.ReadString()); - //var t = sb.ToString(); XDocument xdoc = XDocumentExtensions.Load(ms); xdoc.Root.Elements().ForEach(e=> writer.WriteRaw(e.ToString())); - //CopyXml(reader, writer); } @@ -4439,26 +4426,6 @@ // VML Shape for Comment private static Vml.Shape GenerateShape(XLCell c, string shapeTypeId) { - - #region Office VML - // - #endregion - - // Limitation: Most of the shape properties hard coded. - - var rowNumber = c.Address.RowNumber; var columnNumber = c.Address.ColumnNumber; @@ -4467,13 +4434,13 @@ 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(); + fill.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.FillTransparency), 2).ToString(CultureInfo.InvariantCulture); 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.Shadow { On = true, Color = "black", Obscured = true }, + new Vml.Path { ConnectionPointType = Vml.Office.ConnectValues.None }, textBox, new Vml.Spreadsheet.ClientData( new Vml.Spreadsheet.MoveWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.Absolute ? "True": "False"), // Counterintuitive @@ -4482,8 +4449,8 @@ new Vml.Spreadsheet.HorizontalTextAlignment(c.Comment.Style.Alignment.Horizontal.ToString().ToCamel()), new Vml.Spreadsheet.VerticalTextAlignment(c.Comment.Style.Alignment.Vertical.ToString().ToCamel()), new Vml.Spreadsheet.AutoFill("False"), - new Vml.Spreadsheet.CommentRowTarget() { Text = (rowNumber - 1).ToString() }, - new Vml.Spreadsheet.CommentColumnTarget() { Text = (columnNumber - 1).ToString() }, + new Vml.Spreadsheet.CommentRowTarget { Text = (rowNumber - 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") @@ -4508,21 +4475,18 @@ 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(), + var stroke = new Vml.Stroke { LineStyle = c.Comment.Style.ColorsAndLines.LineStyle.ToOpenXml(), DashStyle = lineDash == XLDashStyle.RoundDot || lineDash == XLDashStyle.SquareDot ? "shortDot" : lineDash.ToString().ToCamel() }; 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(); + stroke.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.LineTransparency), 2).ToString(CultureInfo.InvariantCulture); return stroke; } private static Vml.TextBox GetTextBox(IXLDrawingStyle ds) { - // - // - var sb = new StringBuilder(); var a = ds.Alignment; @@ -4541,10 +4505,14 @@ } if (a.AutomaticSize) sb.Append("mso-fit-shape-to-text:t;"); - var retVal = new Vml.TextBox() { Style = sb.ToString() }; + 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); + retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", + dm.Left.ToString(CultureInfo.InvariantCulture), + dm.Top.ToString(CultureInfo.InvariantCulture), + dm.Right.ToString(CultureInfo.InvariantCulture), + dm.Bottom.ToString(CultureInfo.InvariantCulture)); return retVal; } @@ -4552,7 +4520,7 @@ 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); + Double cWidth = c.Style.Size.Width; 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; @@ -4590,38 +4558,24 @@ private static StringValue GetCommentStyle(XLCell cell) { var c = cell.Comment; - var sb = new StringBuilder(); + var sb = new StringBuilder("position:absolute; "); 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(Math.Round(c.Style.Size.Width * 7.5 , 2).ToString()); + sb.Append(Math.Round(c.Style.Size.Width * 7.5, 2).ToString(CultureInfo.InvariantCulture)); sb.Append("pt;"); sb.Append("height:"); - sb.Append(Math.Round(c.Style.Size.Height, 2).ToString()); + sb.Append(Math.Round(c.Style.Size.Height, 2).ToString(CultureInfo.InvariantCulture)); sb.Append("pt;"); sb.Append("z-index:"); sb.Append(c.ZOrder.ToString()); - return "position:absolute; " + sb.ToString(); + return sb.ToString(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index af7bcb6..bc19451 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -217,16 +217,15 @@ + + + + {BD5E6BFE-E837-4A35-BCA9-39667D873A20} ClosedXML - - - - -