diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index d1900d0..17b5dfa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -420,7 +420,7 @@ } if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) - if (((Int32)Double.Parse(cellValue)).ToString() == cellValue) + if (cellValue.Contains('.')) Style.NumberFormat.NumberFormatId = 14; else Style.NumberFormat.NumberFormatId = 22; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index b567f17..b390ad3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -109,13 +109,13 @@ else { IXLStyle style = this.Style; - if (this.Style.ToString() == this.Worksheet.Style.ToString()) + if (this.Style.Equals(this.Worksheet.Style)) { if (this.Worksheet.Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) - && this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style.ToString() != this.Worksheet.Style.ToString()) + && !this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style.Equals(this.Worksheet.Style)) style = this.Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style; else if (this.Worksheet.Internals.ColumnsCollection.ContainsKey(absoluteAddress.ColumnNumber) - && this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.ToString() != this.Worksheet.Style.ToString()) + && !this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style.Equals(this.Worksheet.Style)) style = this.Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; } var newCell = new XLCell(absoluteAddress, style, Worksheet); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs index f9aefeb..9021888 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs @@ -33,7 +33,7 @@ Top } - public interface IXLAlignment + public interface IXLAlignment: IEquatable { XLAlignmentHorizontalValues Horizontal { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs index ce756d4..e1be727 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs @@ -23,7 +23,7 @@ Thick, Thin } - public interface IXLBorder + public interface IXLBorder: IEquatable { XLBorderStyleValues LeftBorder { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs index 610280d..4c938de 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs @@ -29,7 +29,7 @@ Solid } - public interface IXLFill + public interface IXLFill:IEquatable { Color BackgroundColor { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs index f1326a8..9388f09 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs @@ -32,7 +32,7 @@ Decorative = 5 } - public interface IXLFont + public interface IXLFont: IEquatable { Boolean Bold { get; set; } Boolean Italic { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs index 3572a6a..e3401c5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - public interface IXLNumberFormat + public interface IXLNumberFormat: IEquatable { Int32 NumberFormatId { get; set; } String Format { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs index 525157d..3bc79bc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs @@ -5,7 +5,7 @@ namespace ClosedXML.Excel { - public interface IXLStyle + public interface IXLStyle: IEquatable { IXLAlignment Alignment { get; set; } IXLBorder Border { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index 29bbf3d..97218ab 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -242,5 +242,44 @@ sb.Append("-"); return sb.ToString(); } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + Horizontal.GetHashCode(); + hash = hash * 23 + Vertical.GetHashCode(); + hash = hash * 23 + Indent.GetHashCode(); + hash = hash * 23 + JustifyLastLine.GetHashCode(); + hash = hash * 23 + ReadingOrder.GetHashCode(); + hash = hash * 23 + RelativeIndent.GetHashCode(); + hash = hash * 23 + ShrinkToFit.GetHashCode(); + hash = hash * 23 + TextRotation.GetHashCode(); + hash = hash * 23 + WrapText.GetHashCode(); + return hash; + } + } + + public bool Equals(IXLAlignment other) + { + return + this.Horizontal.Equals(other.Horizontal) + && this.Vertical.Equals(other.Vertical) + && this.Indent.Equals(other.Indent) + && this.JustifyLastLine.Equals(other.JustifyLastLine) + && this.ReadingOrder.Equals(other.ReadingOrder) + && this.RelativeIndent.Equals(other.RelativeIndent) + && this.ShrinkToFit.Equals(other.ShrinkToFit) + && this.TextRotation.Equals(other.TextRotation) + && this.WrapText.Equals(other.WrapText) + ; + } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index 33401ef..469488c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -251,5 +251,49 @@ sb.Append(DiagonalDown.ToString()); return sb.ToString(); } + + public bool Equals(IXLBorder other) + { + return + this.LeftBorder.Equals(other.LeftBorder) + && this.LeftBorderColor.Equals(other.LeftBorderColor) + && this.RightBorder.Equals(other.RightBorder) + && this.RightBorderColor.Equals(other.RightBorderColor) + && this.TopBorder.Equals(other.TopBorder) + && this.TopBorderColor.Equals(other.TopBorderColor) + && this.BottomBorder.Equals(other.BottomBorder) + && this.BottomBorderColor.Equals(other.BottomBorderColor) + && this.DiagonalBorder.Equals(other.DiagonalBorder) + && this.DiagonalBorderColor.Equals(other.DiagonalBorderColor) + && this.DiagonalUp.Equals(other.DiagonalUp) + && this.DiagonalDown.Equals(other.DiagonalDown) + ; + } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + LeftBorder.GetHashCode(); + hash = hash * 23 + LeftBorderColor.GetHashCode(); + hash = hash * 23 + RightBorder.GetHashCode(); + hash = hash * 23 + RightBorderColor.GetHashCode(); + hash = hash * 23 + TopBorder.GetHashCode(); + hash = hash * 23 + TopBorderColor.GetHashCode(); + hash = hash * 23 + BottomBorder.GetHashCode(); + hash = hash * 23 + BottomBorderColor.GetHashCode(); + hash = hash * 23 + DiagonalBorder.GetHashCode(); + hash = hash * 23 + DiagonalBorderColor.GetHashCode(); + hash = hash * 23 + DiagonalUp.GetHashCode(); + hash = hash * 23 + DiagonalDown.GetHashCode(); + return hash; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs index 1f005e2..e443541 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs @@ -115,5 +115,31 @@ } #endregion + + public bool Equals(IXLFill other) + { + return + this.BackgroundColor.Equals(other.BackgroundColor) + && this.PatternType.Equals(other.PatternType) + && this.PatternColor.Equals(other.PatternColor) + ; + } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + BackgroundColor.GetHashCode(); + hash = hash * 23 + PatternType.GetHashCode(); + hash = hash * 23 + PatternColor.GetHashCode(); + return hash; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index de7dfac..3849c5f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -257,5 +257,45 @@ return val; } + + public Boolean Equals(IXLFont other) + { + return + this.Bold.Equals(other.Bold) + && this.Italic.Equals(other.Italic) + && this.Underline.Equals(other.Underline) + && this.Strikethrough.Equals(other.Strikethrough) + && this.VerticalAlignment.Equals(other.VerticalAlignment) + && this.Shadow.Equals(other.Shadow) + && this.FontSize.Equals(other.FontSize) + && this.FontColor.Equals(other.FontColor) + && this.FontName.Equals(other.FontName) + && this.FontFamilyNumbering.Equals(other.FontFamilyNumbering) + ; + } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + Bold.GetHashCode(); + hash = hash * 23 + Italic.GetHashCode(); + hash = hash * 23 + Underline.GetHashCode(); + hash = hash * 23 + Strikethrough.GetHashCode(); + hash = hash * 23 + VerticalAlignment.GetHashCode(); + hash = hash * 23 + Shadow.GetHashCode(); + hash = hash * 23 + FontSize.GetHashCode(); + hash = hash * 23 + FontColor.GetHashCode(); + hash = hash * 23 + FontName.GetHashCode(); + hash = hash * 23 + FontFamilyNumbering.GetHashCode(); + return hash; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs index 7a8e905..57c52dd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -77,5 +77,29 @@ } #endregion + + public bool Equals(IXLNumberFormat other) + { + return + this.NumberFormatId.Equals(other.NumberFormatId) + && this.Format.Equals(other.Format) + ; + } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + NumberFormatId.GetHashCode(); + hash = hash * 23 + Format.GetHashCode(); + return hash; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs index cedb2e2..3c87bb4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs @@ -69,5 +69,34 @@ return sb.ToString(); } + public bool Equals(IXLStyle other) + { + return + this.Font.Equals(other.Font) + && this.Fill.Equals(other.Fill) + && this.Border.Equals(other.Border) + && this.NumberFormat.Equals(other.NumberFormat) + && this.Alignment.Equals(other.Alignment) + ; + } + + public override bool Equals(object obj) + { + return this.GetHashCode().Equals(obj.GetHashCode()); + } + + public override int GetHashCode() + { + unchecked // Overflow is fine, just wrap + { + int hash = 17; + hash = hash * 23 + Font.GetHashCode(); + hash = hash * 23 + Fill.GetHashCode(); + hash = hash * 23 + Border.GetHashCode(); + hash = hash * 23 + NumberFormat.GetHashCode(); + hash = hash * 23 + Alignment.GetHashCode(); + return hash; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index eff6dcf..a030f15 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -45,44 +45,63 @@ /// The cell address. public XLAddress(String cellAddressString) { - Match m = a1Regex.Match(cellAddressString); - fixedColumn = m.Groups[1].Value.StartsWith("$"); - columnLetter = m.Groups[1].Value.Replace("$", ""); - fixedRow = m.Groups[1].Value.StartsWith("$"); - rowNumber = Int32.Parse(m.Groups[2].Value.Replace("$", "")); + fixedColumn = cellAddressString[0] == '$'; + Int32 startPos; + if (fixedColumn) + startPos = 1; + else + startPos = 0; + + Int32 rowPos = startPos; + while (cellAddressString[rowPos] > '9') + rowPos++; + + fixedRow = cellAddressString[rowPos] == '$'; + + if (fixedRow) + { + columnLetter = cellAddressString.Substring(startPos, rowPos - 1); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos + 1)); + } + else + { + columnLetter = cellAddressString.Substring(startPos, rowPos); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos)); + } + columnNumber = 0; } #endregion #region Static - + private static readonly Int32 twoT26 = 26 * 26; /// /// Gets the column number of a given column letter. /// /// The column letter to translate into a column number. public static Int32 GetColumnNumberFromLetter(String columnLetter) { - Int32 iTest; - if (Int32.TryParse(columnLetter, out iTest)) return iTest; + if (columnLetter[0] <= '9') + return Int32.Parse(columnLetter); columnLetter = columnLetter.ToUpper(); - - if (columnLetter.Length == 1) + var length = columnLetter.Length; + if (length == 1) { - return Convert.ToByte(Convert.ToChar(columnLetter)) - 64; + return Convert.ToByte(columnLetter[0]) - 64; } - else if (columnLetter.Length == 2) + else if (length == 2) { return ((Convert.ToByte(columnLetter[0]) - 64) * 26) + (Convert.ToByte(columnLetter[1]) - 64); } - else if (columnLetter.Length == 3) + else if (length == 3) { return - ((Convert.ToByte(columnLetter[0]) - 64) * 26 * 26) + + ((Convert.ToByte(columnLetter[0]) - 64) * twoT26) + ((Convert.ToByte(columnLetter[1]) - 64) * 26) + (Convert.ToByte(columnLetter[2]) - 64); } @@ -123,6 +142,61 @@ return s; } + public static Int32 GetRowFromAddress1(String cellAddressString) + { + Int32 rowPos = 1; + while (cellAddressString[rowPos] > '9') + rowPos++; + + return Int32.Parse(cellAddressString.Substring(rowPos)); + } + + public static Int32 GetColumnNumberFromAddress1(String cellAddressString) + { + Int32 rowPos = 0; + while (cellAddressString[rowPos] > '9') + rowPos++; + + return GetColumnNumberFromLetter(cellAddressString.Substring(0, rowPos)); + } + + public static Int32 GetRowFromAddress2(String cellAddressString) + { + Int32 rowPos = 1; + while (cellAddressString[rowPos] > '9') + rowPos++; + + if (cellAddressString[rowPos] == '$') + { + return Int32.Parse(cellAddressString.Substring(rowPos + 1)); + } + else + { + return Int32.Parse(cellAddressString.Substring(rowPos)); + } + } + + public static Int32 GetColumnNumberFromAddress2(String cellAddressString) + { + Int32 startPos; + if (cellAddressString[0] == '$') + startPos = 1; + else + startPos = 0; + + Int32 rowPos = startPos; + while (cellAddressString[rowPos] > '9') + rowPos++; + + if (cellAddressString[rowPos] == '$') + { + return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos - 1)); + } + else + { + return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); + } + } #endregion #region Properties @@ -197,6 +271,13 @@ } #endregion + #region Methods + public string GetTrimmedAddress() + { + return ColumnLetter + rowNumber.ToString(); + } + #endregion + #region Operator Overloads public static XLAddress operator +(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 9f57d6d..2d1d774 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -151,8 +151,7 @@ foreach (var row in worksheetPart.Worksheet.Descendants().Where(r => r.CustomFormat != null && r.CustomFormat).Select(r => r)) { - //var dRow = (Column)col; - var xlRow = ws.Row((Int32)row.RowIndex.Value); + var xlRow = ws.Row((Int32)row.RowIndex.Value, false); if (row.Height != null) xlRow.Height = row.Height; else @@ -178,17 +177,15 @@ } } - foreach (var cell in worksheetPart.Worksheet.Descendants() - .Where(c => c.CellFormula != null && c.CellFormula.SharedIndex != null && c.CellFormula.Reference != null) - .Select(c => c)) - { - sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); - } foreach (var cell in worksheetPart.Worksheet.Descendants()) { + if (cell.CellFormula != null && cell.CellFormula.SharedIndex != null && cell.CellFormula.Reference != null) + sharedFormulas.Add(cell.CellFormula.SharedIndex.Value, cell.CellFormula); + var dCell = (Cell)cell; Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; var xlCell = ws.CellFast(dCell.CellReference); + if (styleIndex > 0) { //styleIndex = Int32.Parse(dCell.StyleIndex.InnerText); @@ -450,35 +447,54 @@ Properties.Title = p.Title; } - private System.Drawing.Color? GetColor(ColorType color) + private struct XLColor { - var indexedColors = GetIndexedColors(); - if (color == null) - return null; - - if (color.Rgb != null) - return System.Drawing.ColorTranslator.FromHtml("#" + color.Rgb.Value); - else if (color.Indexed != null && color.Indexed < 64) - return System.Drawing.ColorTranslator.FromHtml("#" + indexedColors[(Int32)color.Indexed.Value]); - - return null; + public System.Drawing.Color Color { get; set; } + public Boolean HasValue { get; set; } } - //private Dictionary alignmentDictionary; - //private Dictionary GetAlignmentDictionary(Stylesheet s) - //{ - // if (alignmentDictionary == null) - // { - // alignmentDictionary = new Dictionary(); - // for (Int32 i = 0; i < s.CellFormats.Count; i++) - // { - // var alignment = ((CellFormat)s.CellFormats.ElementAt(i)).Alignment; - // if (alignment != null) - // alignmentDictionary.Add(i, alignment); - // } - // } - // return alignmentDictionary; - //} + private Dictionary colorList = new Dictionary(); + private XLColor GetColor(ColorType color) + { + var retVal = new XLColor(); + if (color != null) + { + if (color.Rgb != null) + { + String htmlColor = "#" + color.Rgb.Value; + System.Drawing.Color thisColor; + if (!colorList.ContainsKey(htmlColor)) + { + thisColor = System.Drawing.ColorTranslator.FromHtml(htmlColor); + colorList.Add(htmlColor, thisColor); + } + else + { + thisColor = colorList[htmlColor]; + } + retVal.HasValue = true; + retVal.Color = thisColor; + } + else if (color.Indexed != null && color.Indexed < 64) + { + var indexedColors = GetIndexedColors(); + String htmlColor = "#" + indexedColors[(Int32)color.Indexed.Value]; + System.Drawing.Color thisColor; + if (!colorList.ContainsKey(htmlColor)) + { + thisColor = System.Drawing.ColorTranslator.FromHtml(htmlColor); + colorList.Add(htmlColor, thisColor); + } + else + { + thisColor = colorList[htmlColor]; + } + retVal.HasValue = true; + retVal.Color = thisColor; + } + } + return retVal; + } private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats) { @@ -495,10 +511,10 @@ xlStylized.Style.Fill.PatternType = fillPatternValues.Single(p => p.Value == fill.PatternFill.PatternType).Key; var fgColor = GetColor(fill.PatternFill.ForegroundColor); - if (fgColor != null) xlStylized.Style.Fill.PatternColor = fgColor.Value; + if (fgColor.HasValue) xlStylized.Style.Fill.PatternColor = fgColor.Color; var bgColor = GetColor(fill.PatternFill.BackgroundColor); - if (bgColor != null) xlStylized.Style.Fill.PatternBackgroundColor = bgColor.Value; + if (bgColor.HasValue) xlStylized.Style.Fill.PatternBackgroundColor = bgColor.Color; } } @@ -545,8 +561,8 @@ xlStylized.Style.Border.BottomBorder = borderStyleValues.Single(b => b.Value == bottomBorder.Style.Value).Key; var bottomBorderColor = GetColor(bottomBorder.Color); - if (bottomBorderColor != null) - xlStylized.Style.Border.BottomBorderColor = bottomBorderColor.Value; + if (bottomBorderColor.HasValue) + xlStylized.Style.Border.BottomBorderColor = bottomBorderColor.Color; } var topBorder = (TopBorder)border.TopBorder; if (topBorder != null) @@ -554,8 +570,8 @@ if (topBorder.Style != null) xlStylized.Style.Border.TopBorder = borderStyleValues.Single(b => b.Value == topBorder.Style.Value).Key; var topBorderColor = GetColor(topBorder.Color); - if (topBorderColor != null) - xlStylized.Style.Border.TopBorderColor = topBorderColor.Value; + if (topBorderColor.HasValue) + xlStylized.Style.Border.TopBorderColor = topBorderColor.Color; } var leftBorder = (LeftBorder)border.LeftBorder; if (leftBorder != null) @@ -563,8 +579,8 @@ if (leftBorder.Style != null) xlStylized.Style.Border.LeftBorder = borderStyleValues.Single(b => b.Value == leftBorder.Style.Value).Key; var leftBorderColor = GetColor(leftBorder.Color); - if (leftBorderColor != null) - xlStylized.Style.Border.LeftBorderColor = leftBorderColor.Value; + if (leftBorderColor.HasValue) + xlStylized.Style.Border.LeftBorderColor = leftBorderColor.Color; } var rightBorder = (RightBorder)border.RightBorder; if (rightBorder != null) @@ -572,8 +588,8 @@ if (rightBorder.Style != null) xlStylized.Style.Border.RightBorder = borderStyleValues.Single(b => b.Value == rightBorder.Style.Value).Key; var rightBorderColor = GetColor(rightBorder.Color); - if (rightBorderColor != null) - xlStylized.Style.Border.RightBorderColor = rightBorderColor.Value; + if (rightBorderColor.HasValue) + xlStylized.Style.Border.RightBorderColor = rightBorderColor.Color; } var diagonalBorder = (DiagonalBorder)border.DiagonalBorder; if (diagonalBorder != null) @@ -581,8 +597,8 @@ if (diagonalBorder.Style != null) xlStylized.Style.Border.DiagonalBorder = borderStyleValues.Single(b => b.Value == diagonalBorder.Style.Value).Key; var diagonalBorderColor = GetColor(diagonalBorder.Color); - if (diagonalBorderColor != null) - xlStylized.Style.Border.DiagonalBorderColor = diagonalBorderColor.Value; + if (diagonalBorderColor.HasValue) + xlStylized.Style.Border.DiagonalBorderColor = diagonalBorderColor.Color; if (border.DiagonalDown != null) xlStylized.Style.Border.DiagonalDown = border.DiagonalDown; if (border.DiagonalUp != null) @@ -600,8 +616,8 @@ xlStylized.Style.Font.Bold = GetBoolean(font.Bold); var fontColor = GetColor(font.Color); - if (fontColor != null) - xlStylized.Style.Font.FontColor = fontColor.Value; + if (fontColor.HasValue) + xlStylized.Style.Font.FontColor = fontColor.Color; if (font.FontFamilyNumbering != null && ((FontFamilyNumbering)font.FontFamilyNumbering).Val != null) xlStylized.Style.Font.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(((FontFamilyNumbering)font.FontFamilyNumbering).Val.ToString()); @@ -619,10 +635,19 @@ xlStylized.Style.Font.Italic = GetBoolean(font.Italic); xlStylized.Style.Font.Shadow = GetBoolean(font.Shadow); xlStylized.Style.Font.Strikethrough = GetBoolean(font.Strike); - if (font.Underline != null && ((Underline)font.Underline).Val != null) - xlStylized.Style.Font.Underline = underlineValuesList.Single(u => u.Value == ((Underline)font.Underline).Val).Key; - if (font.VerticalTextAlignment != null && ((VerticalTextAlignment)font.VerticalTextAlignment).Val != null) + + if (font.Underline != null) + if (font.Underline.Val != null) + xlStylized.Style.Font.Underline = underlineValuesList.Single(u => u.Value == ((Underline)font.Underline).Val).Key; + else + xlStylized.Style.Font.Underline = XLFontUnderlineValues.Single; + + if (font.VerticalTextAlignment != null) + + if (font.VerticalTextAlignment.Val != null) xlStylized.Style.Font.VerticalAlignment = fontVerticalTextAlignmentValues.Single(f => f.Value == ((VerticalTextAlignment)font.VerticalTextAlignment).Val).Key; + else + xlStylized.Style.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; } if (s.CellFormats != null) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 72963f0..2fc8910 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -97,7 +97,7 @@ public IXLStyle Style; }; - private Dictionary sharedStyles = new Dictionary(); + private Dictionary sharedStyles = new Dictionary(); private CellValues GetCellValue(XLCellValues xlCellValue) { @@ -317,6 +317,10 @@ foreach (var worksheet in Worksheets.Cast().OrderBy(w=>w.SheetId)) { + //Int32 sheetCount = Worksheets.Count(); + //for (Int32 i = 0; i < sheetCount; i++) + //{ + // var worksheet = (XLWorksheet)Worksheets.Worksheet(i); WorksheetPart worksheetPart; var sheets = workbookPart.Workbook.Sheets.Elements(); if (workbookPart.Parts.Where(p => p.RelationshipId == "rId" + worksheet.SheetId.ToString()).Any()) @@ -673,39 +677,29 @@ private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { - List modifiedStrings = new List(); - Worksheets.Cast().ForEach(w => modifiedStrings.AddRange(w.Internals.CellsCollection.Values.Where(c => c.DataType == XLCellValues.Text && !StringExtensions.IsNullOrWhiteSpace(c.InnerText)).Select(c => c.GetString()).Distinct())); - - List existingStrings; - if (sharedStringTablePart.SharedStringTable != null) - existingStrings = sharedStringTablePart.SharedStringTable.Elements().Select(e => e.Text.Text).ToList(); - else + Dictionary modifiedStrings = new Dictionary(); + foreach (var w in Worksheets.Cast()) { - existingStrings = new List(); - sharedStringTablePart.SharedStringTable = new SharedStringTable() { Count = 0, UniqueCount = 0 }; + foreach (var c in w.Internals.CellsCollection.Values) + { + if ( + c.DataType == XLCellValues.Text + && !StringExtensions.IsNullOrWhiteSpace(c.InnerText) + && !modifiedStrings.ContainsKey(c.Value.ToString()) + ) + { + modifiedStrings.Add(c.Value.ToString(), null); + } + } } - var distinctStrings = modifiedStrings.Distinct().Union(existingStrings); + sharedStringTablePart.SharedStringTable = new SharedStringTable() { Count = 0, UniqueCount = 0 }; - UInt32 stringCount = (UInt32)distinctStrings.Count(); + UInt32 stringCount = (UInt32)modifiedStrings.Count(); - foreach (var s in distinctStrings) + Int32 stringId = 0; + foreach (var s in modifiedStrings.Keys) { - Int32 stringId = 0; - var ds = sharedStringTablePart.SharedStringTable.Elements().Select(t=>t.Text.Text).Distinct(); - Boolean foundOne = false; - foreach (var ssi in ds) - { - if (ssi == s) - { - foundOne = true; - break; - } - stringId++; - } - - if (!foundOne) - { SharedStringItem sharedStringItem = new SharedStringItem(); Text text = new Text(); text.Text = s; @@ -713,31 +707,31 @@ sharedStringTablePart.SharedStringTable.Append(sharedStringItem); sharedStringTablePart.SharedStringTable.Count += 1; sharedStringTablePart.SharedStringTable.UniqueCount += 1; - } sharedStrings.Add(s, (UInt32)stringId); + stringId++; } } private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart) { var defaultStyle = DefaultStyle; - Dictionary sharedFonts = new Dictionary(); - sharedFonts.Add(defaultStyle.Font.ToString(), new FontInfo() { FontId = 0, Font = defaultStyle.Font }); + Dictionary sharedFonts = new Dictionary(); + sharedFonts.Add(defaultStyle.Font, new FontInfo() { FontId = 0, Font = defaultStyle.Font }); - Dictionary sharedFills = new Dictionary(); - sharedFills.Add(defaultStyle.Fill.ToString(), new FillInfo() { FillId = 2, Fill = defaultStyle.Fill }); + Dictionary sharedFills = new Dictionary(); + sharedFills.Add(defaultStyle.Fill, new FillInfo() { FillId = 2, Fill = defaultStyle.Fill }); - Dictionary sharedBorders = new Dictionary(); - sharedBorders.Add(defaultStyle.Border.ToString(), new BorderInfo() { BorderId = 0, Border = defaultStyle.Border }); + Dictionary sharedBorders = new Dictionary(); + sharedBorders.Add(defaultStyle.Border, new BorderInfo() { BorderId = 0, Border = defaultStyle.Border }); - Dictionary sharedNumberFormats = new Dictionary(); - sharedNumberFormats.Add(defaultStyle.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat }); + Dictionary sharedNumberFormats = new Dictionary(); + sharedNumberFormats.Add(defaultStyle.NumberFormat, new NumberFormatInfo() { NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat }); //Dictionary sharedAlignments = new Dictionary(); //sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment }); - sharedStyles.Add(defaultStyle.ToString(), + sharedStyles.Add(defaultStyle, new StyleInfo() { StyleId = 0, @@ -768,24 +762,24 @@ foreach (var xlStyle in xlStyles) { - if (!sharedFonts.ContainsKey(xlStyle.Font.ToString())) + if (!sharedFonts.ContainsKey(xlStyle.Font)) { - sharedFonts.Add(xlStyle.Font.ToString(), new FontInfo() { FontId = fontCount++, Font = xlStyle.Font }); + sharedFonts.Add(xlStyle.Font, new FontInfo() { FontId = fontCount++, Font = xlStyle.Font }); } - if (!sharedFills.ContainsKey(xlStyle.Fill.ToString())) + if (!sharedFills.ContainsKey(xlStyle.Fill)) { - sharedFills.Add(xlStyle.Fill.ToString(), new FillInfo() { FillId = fillCount++, Fill = xlStyle.Fill }); + sharedFills.Add(xlStyle.Fill, new FillInfo() { FillId = fillCount++, Fill = xlStyle.Fill }); } - if (!sharedBorders.ContainsKey(xlStyle.Border.ToString())) + if (!sharedBorders.ContainsKey(xlStyle.Border)) { - sharedBorders.Add(xlStyle.Border.ToString(), new BorderInfo() { BorderId = borderCount++, Border = xlStyle.Border }); + sharedBorders.Add(xlStyle.Border, new BorderInfo() { BorderId = borderCount++, Border = xlStyle.Border }); } - if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat.ToString())) + if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) { - sharedNumberFormats.Add(xlStyle.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormatId = numberFormatCount + 164, NumberFormat = xlStyle.NumberFormat }); + sharedNumberFormats.Add(xlStyle.NumberFormat, new NumberFormatInfo() { NumberFormatId = numberFormatCount + 164, NumberFormat = xlStyle.NumberFormat }); numberFormatCount++; } } @@ -800,22 +794,22 @@ foreach (var xlStyle in xlStyles) { - if (!sharedStyles.ContainsKey(xlStyle.ToString())) + if (!sharedStyles.ContainsKey(xlStyle)) { Int32 numberFormatId; if (xlStyle.NumberFormat.NumberFormatId >= 0) numberFormatId = xlStyle.NumberFormat.NumberFormatId; else - numberFormatId = allSharedNumberFormats[xlStyle.NumberFormat.ToString()].NumberFormatId; + numberFormatId = allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; - sharedStyles.Add(xlStyle.ToString(), + sharedStyles.Add(xlStyle, new StyleInfo() { StyleId = styleCount++, Style = xlStyle, - FontId = allSharedFonts[xlStyle.Font.ToString()].FontId, - FillId = allSharedFills[xlStyle.Fill.ToString()].FillId, - BorderId = allSharedBorders[xlStyle.Border.ToString()].BorderId, + FontId = allSharedFonts[xlStyle.Font].FontId, + FillId = allSharedFills[xlStyle.Fill].FillId, + BorderId = allSharedBorders[xlStyle.Border].BorderId, NumberFormatId = numberFormatId }); } @@ -830,14 +824,14 @@ if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Any()) { - var defaultFormatId = sharedStyles.Values.Where(s => s.Style.ToString() == DefaultStyle.ToString()).Single().StyleId; + var defaultFormatId = sharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)defaultFormatId, BuiltinId = (UInt32Value)0U }; workbookStylesPart.Stylesheet.CellStyles.Append(cellStyle1); } workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); - var newSharedStyles = new Dictionary(); + var newSharedStyles = new Dictionary(); foreach (var ss in sharedStyles) { Int32 styleId = -1; @@ -904,12 +898,12 @@ workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellFormats.Count(); } - private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart) + private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart) { if (workbookStylesPart.Stylesheet.CellStyleFormats == null) workbookStylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); - var allSharedStyles = new Dictionary(); + var allSharedStyles = new Dictionary(); foreach (var styleInfo in sharedStyles.Values) { Int32 styleId = 0; @@ -928,7 +922,7 @@ CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; workbookStylesPart.Stylesheet.CellStyleFormats.Append(cellStyleFormat); } - allSharedStyles.Add(styleInfo.Style.ToString(), new StyleInfo() { Style = styleInfo.Style, StyleId = (UInt32)styleId }); + allSharedStyles.Add(styleInfo.Style, new StyleInfo() { Style = styleInfo.Style, StyleId = (UInt32)styleId }); } workbookStylesPart.Stylesheet.CellStyleFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyleFormats.Count(); @@ -991,15 +985,15 @@ if (alignment.JustifyLastLine != null) a.JustifyLastLine = alignment.JustifyLastLine.Value; } - return a.ToString() == xlAlignment.ToString(); + return a.Equals(xlAlignment); } - private Dictionary ResolveBorders(WorkbookStylesPart workbookStylesPart, Dictionary sharedBorders) + private Dictionary ResolveBorders(WorkbookStylesPart workbookStylesPart, Dictionary sharedBorders) { if (workbookStylesPart.Stylesheet.Borders == null) workbookStylesPart.Stylesheet.Borders = new Borders(); - var allSharedBorders = new Dictionary(); + var allSharedBorders = new Dictionary(); foreach (var borderInfo in sharedBorders.Values) { Int32 borderId = 0; @@ -1018,7 +1012,7 @@ Border border = GetNewBorder(borderInfo); workbookStylesPart.Stylesheet.Borders.Append(border); } - allSharedBorders.Add(borderInfo.Border.ToString(), new BorderInfo() { Border = borderInfo.Border, BorderId = (UInt32)borderId }); + allSharedBorders.Add(borderInfo.Border, new BorderInfo() { Border = borderInfo.Border, BorderId = (UInt32)borderId }); } workbookStylesPart.Stylesheet.Borders.Count = (UInt32)workbookStylesPart.Stylesheet.Borders.Count(); return allSharedBorders; @@ -1070,8 +1064,8 @@ if (b.LeftBorder.Style != null) nb.LeftBorder = borderStyleValues.Single(p => p.Value == b.LeftBorder.Style).Key; var bColor = GetColor(b.LeftBorder.Color); - if (bColor != null) - nb.LeftBorderColor = bColor.Value; + if (bColor.HasValue) + nb.LeftBorderColor = bColor.Color; } if (b.RightBorder != null) @@ -1079,8 +1073,8 @@ if (b.RightBorder.Style != null) nb.RightBorder = borderStyleValues.Single(p => p.Value == b.RightBorder.Style).Key; var bColor = GetColor(b.RightBorder.Color); - if (bColor != null) - nb.RightBorderColor = bColor.Value; + if (bColor.HasValue) + nb.RightBorderColor = bColor.Color; } if (b.TopBorder != null) @@ -1088,8 +1082,8 @@ if (b.TopBorder.Style != null) nb.TopBorder = borderStyleValues.Single(p => p.Value == b.TopBorder.Style).Key; var bColor = GetColor(b.TopBorder.Color); - if (bColor != null) - nb.TopBorderColor = bColor.Value; + if (bColor.HasValue) + nb.TopBorderColor = bColor.Color; } if (b.BottomBorder != null) @@ -1097,14 +1091,14 @@ if (b.BottomBorder.Style != null) nb.BottomBorder = borderStyleValues.Single(p => p.Value == b.BottomBorder.Style).Key; var bColor = GetColor(b.BottomBorder.Color); - if (bColor != null) - nb.BottomBorderColor = bColor.Value; + if (bColor.HasValue) + nb.BottomBorderColor = bColor.Color; } - return nb.ToString() == xlBorder.ToString(); + return nb.Equals(xlBorder); } - private Dictionary ResolveFills(WorkbookStylesPart workbookStylesPart, Dictionary sharedFills) + private Dictionary ResolveFills(WorkbookStylesPart workbookStylesPart, Dictionary sharedFills) { if (workbookStylesPart.Stylesheet.Fills == null) workbookStylesPart.Stylesheet.Fills = new Fills(); @@ -1112,7 +1106,7 @@ ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.None); ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.Gray125); - var allSharedFills = new Dictionary(); + var allSharedFills = new Dictionary(); foreach (var fillInfo in sharedFills.Values) { Int32 fillId = 0; @@ -1131,7 +1125,7 @@ Fill fill = GetNewFill(fillInfo); workbookStylesPart.Stylesheet.Fills.Append(fill); } - allSharedFills.Add(fillInfo.Fill.ToString(), new FillInfo() { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); + allSharedFills.Add(fillInfo.Fill, new FillInfo() { Fill = fillInfo.Fill, FillId = (UInt32)fillId }); } workbookStylesPart.Stylesheet.Fills.Count = (UInt32)workbookStylesPart.Stylesheet.Fills.Count(); @@ -1179,22 +1173,22 @@ nF.PatternType = fillPatternValues.Single(p => p.Value == f.PatternFill.PatternType).Key; var fColor = GetColor(f.PatternFill.ForegroundColor); - if (fColor != null) - nF.PatternColor = fColor.Value; + if (fColor.HasValue) + nF.PatternColor = fColor.Color; var bColor = GetColor(f.PatternFill.BackgroundColor); - if (bColor != null) - nF.PatternBackgroundColor = bColor.Value; + if (bColor.HasValue) + nF.PatternBackgroundColor = bColor.Color; } - return nF.ToString() == xlFill.ToString(); + return nF.Equals(xlFill); } - private Dictionary ResolveFonts(WorkbookStylesPart workbookStylesPart, Dictionary sharedFonts) + private Dictionary ResolveFonts(WorkbookStylesPart workbookStylesPart, Dictionary sharedFonts) { if (workbookStylesPart.Stylesheet.Fonts == null) workbookStylesPart.Stylesheet.Fonts = new Fonts(); - var allSharedFonts = new Dictionary(); + var allSharedFonts = new Dictionary(); foreach (var fontInfo in sharedFonts.Values) { Int32 fontId = 0; @@ -1213,7 +1207,7 @@ Font font = GetNewFont(fontInfo); workbookStylesPart.Stylesheet.Fonts.Append(font); } - allSharedFonts.Add(fontInfo.Font.ToString(), new FontInfo() { Font = fontInfo.Font, FontId = (UInt32)fontId }); + allSharedFonts.Add(fontInfo.Font, new FontInfo() { Font = fontInfo.Font, FontId = (UInt32)fontId }); } workbookStylesPart.Stylesheet.Fonts.Count = (UInt32)workbookStylesPart.Stylesheet.Fonts.Count(); return allSharedFonts; @@ -1253,30 +1247,36 @@ nf.Bold = f.Bold != null; nf.Italic = f.Italic != null; if (f.Underline != null) - nf.Underline = underlineValuesList.Single(u => u.Value == f.Underline.Val).Key; + if (f.Underline.Val != null) + nf.Underline = underlineValuesList.Single(u => u.Value == f.Underline.Val).Key; + else + nf.Underline = XLFontUnderlineValues.Single; nf.Strikethrough = f.Strike != null; if (f.VerticalTextAlignment != null) - nf.VerticalAlignment = fontVerticalTextAlignmentValues.Single(v => v.Value == f.VerticalTextAlignment.Val).Key; + if (f.VerticalTextAlignment.Val != null) + nf.VerticalAlignment = fontVerticalTextAlignmentValues.Single(v => v.Value == f.VerticalTextAlignment.Val).Key; + else + nf.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; nf.Shadow = f.Shadow != null; if (f.FontSize != null) nf.FontSize = f.FontSize.Val; var fColor = GetColor(f.Color); - if (fColor != null) - nf.FontColor = fColor.Value; + if (fColor.HasValue) + nf.FontColor = fColor.Color; if (f.FontName != null) nf.FontName = f.FontName.Val; if (f.FontFamilyNumbering != null) nf.FontFamilyNumbering = (XLFontFamilyNumberingValues)f.FontFamilyNumbering.Val.Value; - return nf.ToString() == xlFont.ToString(); + return nf.Equals(xlFont); } - private Dictionary ResolveNumberFormats(WorkbookStylesPart workbookStylesPart, Dictionary sharedNumberFormats) + private Dictionary ResolveNumberFormats(WorkbookStylesPart workbookStylesPart, Dictionary sharedNumberFormats) { if (workbookStylesPart.Stylesheet.NumberingFormats == null) workbookStylesPart.Stylesheet.NumberingFormats = new NumberingFormats(); - var allSharedNumberFormats = new Dictionary(); + var allSharedNumberFormats = new Dictionary(); foreach (var numberFormatInfo in sharedNumberFormats.Values) { Int32 numberingFormatId = 0; @@ -1295,7 +1295,7 @@ NumberingFormat numberingFormat = new NumberingFormat() { NumberFormatId = (UInt32)numberingFormatId, FormatCode = numberFormatInfo.NumberFormat.Format }; workbookStylesPart.Stylesheet.NumberingFormats.Append(numberingFormat); } - allSharedNumberFormats.Add(numberFormatInfo.NumberFormat.ToString(), new NumberFormatInfo() { NumberFormat = numberFormatInfo.NumberFormat, NumberFormatId = numberingFormatId }); + allSharedNumberFormats.Add(numberFormatInfo.NumberFormat, new NumberFormatInfo() { NumberFormat = numberFormatInfo.NumberFormat, NumberFormatId = numberingFormatId }); } workbookStylesPart.Stylesheet.NumberingFormats.Count = (UInt32)workbookStylesPart.Stylesheet.NumberingFormats.Count(); return allSharedNumberFormats; @@ -1310,7 +1310,7 @@ else if (nf.NumberFormatId != null) newXLNumberFormat.NumberFormatId = (Int32)nf.NumberFormatId.Value; - return newXLNumberFormat.ToString() == xlNumberFormat.ToString(); + return newXLNumberFormat.Equals(xlNumberFormat); } private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet) @@ -1435,7 +1435,7 @@ { UInt32Value min = 1; UInt32Value max = (UInt32)(minInColumnsCollection - 1); - var styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; + var styleId = sharedStyles[xlWorksheet.Style].StyleId; for (var co = min; co <= max; co++) { @@ -1461,7 +1461,7 @@ Int32 outlineLevel = 0; if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) { - styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style.ToString()].StyleId; + styleId = sharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; columnWidth = xlWorksheet.Internals.ColumnsCollection[co].Width; isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; @@ -1469,7 +1469,7 @@ } else { - styleId = sharedStyles[xlWorksheet.Style.ToString()].StyleId; + styleId = sharedStyles[xlWorksheet.Style].StyleId; columnWidth = xlWorksheet.ColumnWidth; } @@ -1490,7 +1490,7 @@ foreach (var col in columns.Elements().Where(c => c.Min > (UInt32)(maxInColumnsCollection)).OrderBy(c => c.Min.Value)) { - col.Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId; + col.Style = sharedStyles[xlWorksheet.Style].StyleId; col.Width = xlWorksheet.ColumnWidth; col.CustomWidth = true; if ((Int32)col.Max.Value > maxInColumnsCollection) @@ -1503,7 +1503,7 @@ { Min = (UInt32)(maxInColumnsCollection + 1), Max = (UInt32)(XLWorksheet.MaxNumberOfColumns), - Style = sharedStyles[xlWorksheet.Style.ToString()].StyleId, + Style = sharedStyles[xlWorksheet.Style].StyleId, Width = xlWorksheet.ColumnWidth, CustomWidth = true }; @@ -1561,10 +1561,10 @@ if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) { var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; - var thisRowStyleString = thisRow.Style.ToString(); + //var thisRowStyleString = thisRow.Style.ToString(); row.Height = thisRow.Height; row.CustomHeight = true; - row.StyleIndex = sharedStyles[thisRowStyleString].StyleId; + row.StyleIndex = sharedStyles[thisRow.Style].StyleId; row.CustomFormat = true; if (thisRow.IsHidden) row.Hidden = true; if (thisRow.Collapsed) row.Collapsed = true; @@ -1588,13 +1588,13 @@ foreach (var opCell in xlWorksheet.Internals.CellsCollection .Where(c => c.Key.RowNumber == distinctRow) - .OrderBy(c => c.Key) + .OrderBy(c => c.Key.ColumnNumber) .Select(c => c)) { - var styleId = sharedStyles[opCell.Value.Style.ToString()].StyleId; + var styleId = sharedStyles[opCell.Value.Style].StyleId; var dataType = opCell.Value.DataType; - var cellReference = opCell.Key.ToString(); + var cellReference = ((XLAddress)opCell.Key).GetTrimmedAddress(); Boolean isNewCell = false; Cell cell = row.Elements().FirstOrDefault(c => c.CellReference.Value == cellReference); if (cell == null) @@ -1607,11 +1607,19 @@ } else { - Int32 newColumn = new XLAddress(cellReference).ColumnNumber; - Cell cellBeforeInsert = row.Elements() - .Where(c => new XLAddress(c.CellReference.Value).ColumnNumber > newColumn) - .OrderBy(c => new XLAddress(c.CellReference.Value).ColumnNumber) - .FirstOrDefault(); + Int32 newColumn = XLAddress.GetColumnNumberFromAddress1(cellReference); + + Cell cellBeforeInsert = null; + Int32 lastCo = Int32.MaxValue; + foreach (var c in row.Elements().Where(c => XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + { + var thidCo = XLAddress.GetColumnNumberFromAddress1(c.CellReference.Value); + if (lastCo > thidCo) + { + cellBeforeInsert = c; + lastCo = thidCo; + } + } if (cellBeforeInsert == null) row.Append(cell); else diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 34acf8e..2ddd527 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -327,7 +327,11 @@ return retVal; } - public IXLRow Row( Int32 row) + public IXLRow Row(Int32 row) + { + return Row(row, true); + } + public IXLRow Row(Int32 row, Boolean pingCells) { IXLStyle styleToUse; if (this.Internals.RowsCollection.ContainsKey(row)) @@ -336,16 +340,25 @@ } else { - // This is a new row so we're going to reference all - // cells in columns of this row to preserve their formatting - var distinctColumns = this.Internals.CellsCollection.Keys.Select(k=>k.ColumnNumber).Distinct().ToDictionary(r=>r, r=>0); + if (pingCells) + { + // This is a new row so we're going to reference all + // cells in columns of this row to preserve their formatting + var distinctColumns = new Dictionary(); + foreach (var k in this.Internals.CellsCollection.Keys) + { + if (!distinctColumns.ContainsKey(k.ColumnNumber)) + distinctColumns.Add(k.ColumnNumber, null); + } - var usedColumns = from c in this.Internals.ColumnsCollection - join dc in distinctColumns - on c.Key equals dc.Key - select c.Key; - - usedColumns.ForEach(c => Cell(row, c)); + var usedColumns = from c in this.Internals.ColumnsCollection + join dc in distinctColumns + on c.Key equals dc.Key + where !this.Internals.CellsCollection.ContainsKey(new XLAddress(row, c.Key)) + select c.Key; + + usedColumns.ForEach(c => Cell(row, c)); + } styleToUse = this.Style; this.Internals.RowsCollection.Add(row, new XLRow(row, new XLRowParameters(this, styleToUse, false))); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 7c572c9..00d51cb 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -1,5 +1,4 @@ -using System; -using System.Collections.Generic; +using System;using System.Collections.Generic; using System.Linq; using System.Text; using ClosedXML.Excel; @@ -13,33 +12,100 @@ { static void Main(string[] args) { - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); - var sheet1 = wb.Worksheets.Worksheet("Sheet1"); - sheet1.Cell(5, 1).Value = 200; - sheet1.Cell(6, 1).Value = 200; - sheet1.Cell(4, 1).FormulaA1 = "A2 + 5"; - var sheet2 = wb.Worksheets.Worksheet("Sheet2"); - sheet2.Cell("B3").Value = 50; - wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox_Saved.xlsx"); - //File.Copy(@"C:\Excel Files\ForTesting\NamedRanges.xlsx", @"C:\Excel Files\ForTesting\Sandbox_Merged.xlsx", true); - //var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\NamedRanges.xlsx"); - //wb.Worksheets.Worksheet(0).Cell(1, 1).Value = "XXX"; - //var ws = wb.Worksheets.Add("Testing"); - //ws.PageSetup.PrintAreas.Add("A1:C3"); - //ws.Range("A1").CreateNamedRange("SuperTest"); - //ws.Cell(1, 1).Value = "Nada"; - //ws.Cell(1, 1).Style.Fill.BackgroundColor = Color.Red; - //wb.NamedRanges.Delete("PeopleData"); - //wb.NamedRanges.Add("PeopleData", ws.Range("A1"), "SuperComment"); - //ws.Cell(1, 2).FormulaA1 = "1+1"; + FillStyles(); + var wb = new XLWorkbook(); + foreach (var i in Enumerable.Range(1, 1)) + { + var ws = wb.Worksheets.Add("Sheet" + i); + foreach (var ro in Enumerable.Range(1, 100)) + { + foreach (var co in Enumerable.Range(1, 100)) + { + ws.Cell(ro, co).Style = GetRandomStyle(); + ws.Cell(ro, co).Value = GetRandomValue(); + } + System.Threading.Thread.Sleep(10); + } + } + var start = DateTime.Now; + wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + var end = DateTime.Now; + Console.WriteLine("Saved in {0} secs.", (end - start).TotalSeconds); - //wb.MergeInto(@"C:\Excel Files\ForTesting\Sandbox_Merged.xlsx"); - //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox_Saved.xlsx"); - //wb.SaveChangesTo(@"C:\Excel Files\ForTesting\Sandbox_Changes.xlsx"); + var start1 = DateTime.Now; + var wb1 = new XLWorkbook(@"C:\Excel Files\ForTesting\Benchmark.xlsx"); + + var end1 = DateTime.Now; + Console.WriteLine("Loaded in {0} secs.", (end1 - start1).TotalSeconds); + + //var ws = wb.Worksheets.Worksheet(0); + //wb.SaveAs(@"C:\Excel Files\ForTesting\Benchmark_Saved.xlsx"); + + //Console.ReadKey(); + } + + private static IXLStyle style1; + private static IXLStyle style2; + private static IXLStyle style3; + private static void FillStyles() + { + + style1 = XLWorkbook.DefaultStyle; + style1.Font.Bold = true; + style1.Fill.BackgroundColor = Color.Azure; + style1.Border.BottomBorder = XLBorderStyleValues.Medium; + style1.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + + style2 = XLWorkbook.DefaultStyle; + style2.Font.Italic = true; + style2.Fill.BackgroundColor = Color.Orange; + style2.Border.LeftBorder = XLBorderStyleValues.Medium; + style2.Alignment.Vertical = XLAlignmentVerticalValues.Center; + + style3 = XLWorkbook.DefaultStyle; + style3.Font.FontColor = Color.Red; + style3.Fill.PatternColor = Color.Blue; + style3.Fill.PatternType = XLFillPatternValues.DarkTrellis; + style3.Border.DiagonalBorder = XLBorderStyleValues.Dotted; + } + private static IXLStyle GetRandomStyle() + { + + var val = rnd.Next(1, 3); + if (val == 1) + { + return style1; + } + else if (val == 2) + { + return style2; + } + else + return style3; } + private static DateTime baseDate = DateTime.Now; + private static Random rnd = new Random(); + private static object GetRandomValue() + { + var val = rnd.Next(1, 6); + if (val == 1) + return Guid.NewGuid().ToString().Substring(1, 5); + else if (val == 2) + return true; + else if (val == 3) + return false; + else if (val == 4) + return DateTime.Now; + else if (val == 5) + return rnd.Next(1, 1000); + else + return (DateTime.Now - baseDate ); + } + + class Person {