diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 6df9e8e..c515d2d 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1721,12 +1721,16 @@ { if (openXMLFill.PatternFill.BackgroundColor != null) closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); } else { // yes, source is foreground! if (openXMLFill.PatternFill.ForegroundColor != null) closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.ForegroundColor); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); } break; @@ -1736,6 +1740,8 @@ if (openXMLFill.PatternFill.BackgroundColor != null) closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); break; } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 9e92af5..ae5af7c 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -3405,7 +3405,7 @@ foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) { styleId++; - if (CellFormatsAreEqual(f, ss.Value)) + if (CellFormatsAreEqual(f, ss.Value, compareAlignment: true)) break; } if (styleId == -1) @@ -3572,7 +3572,7 @@ { var info = styleInfo; var foundOne = - workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info)); + workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info, compareAlignment: true)); if (foundOne) continue; @@ -3611,7 +3611,7 @@ var info = styleInfo; var foundOne = workbookStylesPart.Stylesheet.CellStyleFormats.Cast().Any( - f => CellFormatsAreEqual(f, info)); + f => CellFormatsAreEqual(f, info, compareAlignment: false)); if (foundOne) continue; @@ -3672,7 +3672,17 @@ }; } - private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) + /// + /// Check if two style are equivalent. + /// + /// Style in the OpenXML format. + /// Style in the ClosedXML format. + /// Flag specifying whether or not compare the alignments of two styles. + /// Styles in x:cellStyleXfs section do not include alignment so we don't have to compare it in this case. + /// Styles in x:cellXfs section, on the opposite, do include alignments, and we must compare them. + /// + /// True if two formats are equivalent, false otherwise. + private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo, bool compareAlignment) { return f.BorderId != null && styleInfo.BorderId == f.BorderId @@ -3683,7 +3693,7 @@ f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo)) && (f.ApplyBorder == null && styleInfo.Style.Border == XLBorderValue.Default || f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo)) - && (f.Alignment == null || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)) + && (!compareAlignment || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)) && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) ; } diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index 90c74e2..49a4dd3 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -71,7 +71,7 @@ else if (i < 26 * 27) letter = letters[i / 26 - 1] + letters[i % 26]; else - letter = letters[i / 26 / 26 - 1] + letters[(i / 26 - 1) % 26] + letters[i % 26]; + letter = letters[(i - 26) / 26 / 26 - 1] + letters[(i / 26 - 1) % 26] + letters[i % 26]; allLetters[i] = letter; letterIndexes.Add(letter, i + 1); } diff --git a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs index 20853d6..7ef97ff 100644 --- a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs +++ b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs @@ -1,5 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; namespace ClosedXML_Tests.Excel { @@ -71,6 +73,7 @@ CheckColumnNumber(52); CheckColumnNumber(53); CheckColumnNumber(1000); + CheckColumnNumber(1353); } [Test] @@ -91,5 +94,81 @@ Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + "1")); Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + XLHelper.MaxRowNumber)); } + + [Test] + public void TestColumnLetterLookup() + { + var columnLetters = new List(); + for (int c = 1; c <= XLHelper.MaxColumnNumber; c++) + { + var columnLetter = NaiveGetColumnLetterFromNumber(c); + columnLetters.Add(columnLetter); + + Assert.AreEqual(columnLetter, XLHelper.GetColumnLetterFromNumber(c)); + } + + foreach (var cl in columnLetters) + { + var columnNumber = NaiveGetColumnNumberFromLetter(cl); + Assert.AreEqual(columnNumber, XLHelper.GetColumnNumberFromLetter(cl)); + } + } + + #region Old XLHelper methods + + private static readonly string[] letters = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; + + /// + /// These used to be the methods in XLHelper, but were later changed + /// We now use them as a check against the new methods + /// Gets the column number of a given column letter. + /// + /// The column letter to translate into a column number. + private static int NaiveGetColumnNumberFromLetter(string columnLetter) + { + if (string.IsNullOrEmpty(columnLetter)) throw new ArgumentNullException("columnLetter"); + + int retVal; + columnLetter = columnLetter.ToUpper(); + + //Extra check because we allow users to pass row col positions in as strings + if (columnLetter[0] <= '9') + { + retVal = Int32.Parse(columnLetter, XLHelper.NumberStyle, XLHelper.ParseCulture); + return retVal; + } + + int sum = 0; + + for (int i = 0; i < columnLetter.Length; i++) + { + sum *= 26; + sum += (columnLetter[i] - 'A' + 1); + } + + return sum; + } + + /// + /// Gets the column letter of a given column number. + /// + /// The column number to translate into a column letter. + /// if set to true the column letter will be restricted to the allowed range. + /// + private static string NaiveGetColumnLetterFromNumber(int columnNumber, bool trimToAllowed = false) + { + if (trimToAllowed) columnNumber = XLHelper.TrimColumnNumber(columnNumber); + + columnNumber--; // Adjust for start on column 1 + if (columnNumber <= 25) + { + return letters[columnNumber]; + } + var firstPart = (columnNumber) / 26; + var remainder = ((columnNumber) % 26) + 1; + return NaiveGetColumnLetterFromNumber(firstPart) + NaiveGetColumnLetterFromNumber(remainder); + } + + #endregion Old XLHelper methods } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index bd7b27e..1cbf38d 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -490,5 +490,23 @@ } } } + + [Test] + public void PreserveAlignmentOnSaving() + { + using (var input = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\HorizontalAlignment.xlsx"))) + using (var output = new MemoryStream()) + { + using (var wb = new XLWorkbook(input)) + { + wb.SaveAs(output); + } + + using (var wb = new XLWorkbook(output)) + { + Assert.AreEqual(XLAlignmentHorizontalValues.Center, wb.Worksheets.First().Cell("B1").Style.Alignment.Horizontal); + } + } + } } } diff --git a/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx b/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx new file mode 100644 index 0000000..319da43 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx index 6953f33..be28d7b 100644 --- a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx +++ b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx Binary files differ