diff --git a/ClosedXML/Excel/Style/IXLStyle.cs b/ClosedXML/Excel/Style/IXLStyle.cs index 259f568..7634e61 100644 --- a/ClosedXML/Excel/Style/IXLStyle.cs +++ b/ClosedXML/Excel/Style/IXLStyle.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; namespace ClosedXML.Excel @@ -8,14 +9,18 @@ IXLBorder Border { get; set; } + IXLNumberFormat DateFormat { get; } + IXLFill Fill { get; set; } IXLFont Font { get; set; } + Boolean IncludeQuotePrefix { get; set; } + IXLNumberFormat NumberFormat { get; set; } - IXLNumberFormat DateFormat { get; } - IXLProtection Protection { get; set; } + + IXLStyle SetIncludeQuotePrefix(Boolean includeQuotePrefix = true); } } diff --git a/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/Excel/Style/XLStyle.cs index 95e63f2..5c5f1ff 100644 --- a/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/Excel/Style/XLStyle.cs @@ -126,6 +126,21 @@ } } + public Boolean IncludeQuotePrefix + { + get { return Value.IncludeQuotePrefix; } + set + { + Modify(k => { k.IncludeQuotePrefix = value; return k; }); + } + } + + public IXLStyle SetIncludeQuotePrefix(Boolean includeQuotePrefix = true) + { + IncludeQuotePrefix = includeQuotePrefix; + return this; + } + public IXLNumberFormat NumberFormat { get { return new XLNumberFormat(this, Value.NumberFormat); } diff --git a/ClosedXML/Excel/Style/XLStyleKey.cs b/ClosedXML/Excel/Style/XLStyleKey.cs index 46eea98..e5fc690 100644 --- a/ClosedXML/Excel/Style/XLStyleKey.cs +++ b/ClosedXML/Excel/Style/XLStyleKey.cs @@ -12,6 +12,8 @@ public XLFontKey Font { get; set; } + public Boolean IncludeQuotePrefix { get; set; } + public XLNumberFormatKey NumberFormat { get; set; } public XLProtectionKey Protection { get; set; } @@ -23,6 +25,7 @@ hashCode = hashCode * -1521134295 + Border.GetHashCode(); hashCode = hashCode * -1521134295 + Fill.GetHashCode(); hashCode = hashCode * -1521134295 + Font.GetHashCode(); + hashCode = hashCode * -1521134295 + IncludeQuotePrefix.GetHashCode(); hashCode = hashCode * -1521134295 + NumberFormat.GetHashCode(); hashCode = hashCode * -1521134295 + Protection.GetHashCode(); return hashCode; @@ -34,6 +37,7 @@ Border == other.Border && Fill == other.Fill && Font == other.Font && + IncludeQuotePrefix == other.IncludeQuotePrefix && NumberFormat == other.NumberFormat && Protection == other.Protection; } @@ -42,11 +46,12 @@ { return this == XLStyle.Default.Key ? "Default" : - string.Format("Alignment: {0} Border: {1} Fill: {2} Font: {3} NumberFormat: {4} Protection: {5}", + string.Format("Alignment: {0} Border: {1} Fill: {2} Font: {3} IncludeQuotePrefix: {4} NumberFormat: {5} Protection: {6}", Alignment == XLStyle.Default.Key.Alignment ? "Default" : Alignment.ToString(), Border == XLStyle.Default.Key.Border ? "Default" : Border.ToString(), Fill == XLStyle.Default.Key.Fill ? "Default" : Fill.ToString(), Font == XLStyle.Default.Key.Font ? "Default" : Font.ToString(), + IncludeQuotePrefix == XLStyle.Default.Key.IncludeQuotePrefix ? "Default" : IncludeQuotePrefix.ToString(), NumberFormat == XLStyle.Default.Key.NumberFormat ? "Default" : NumberFormat.ToString(), Protection == XLStyle.Default.Key.Protection ? "Default" : Protection.ToString()); } diff --git a/ClosedXML/Excel/Style/XLStyleValue.cs b/ClosedXML/Excel/Style/XLStyleValue.cs index a2745bf..f046f91 100644 --- a/ClosedXML/Excel/Style/XLStyleValue.cs +++ b/ClosedXML/Excel/Style/XLStyleValue.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel.Caching; +using System; namespace ClosedXML.Excel { @@ -17,6 +18,7 @@ Border = XLBorderValue.Default.Key, Fill = XLFillValue.Default.Key, Font = XLFontValue.Default.Key, + IncludeQuotePrefix = false, NumberFormat = XLNumberFormatValue.Default.Key, Protection = XLProtectionValue.Default.Key }); @@ -31,6 +33,8 @@ public XLFontValue Font { get; private set; } + public Boolean IncludeQuotePrefix { get; private set; } + public XLNumberFormatValue NumberFormat { get; private set; } public XLProtectionValue Protection { get; private set; } @@ -42,6 +46,7 @@ Border = XLBorderValue.FromKey(Key.Border); Fill = XLFillValue.FromKey(Key.Fill); Font = XLFontValue.FromKey(Key.Font); + IncludeQuotePrefix = key.IncludeQuotePrefix; NumberFormat = XLNumberFormatValue.FromKey(Key.NumberFormat); Protection = XLProtectionValue.FromKey(Key.Protection); } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 2357cb7..1ea2246 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -2690,6 +2690,8 @@ var xlStyle = XLStyle.Default.Key; + xlStyle.IncludeQuotePrefix = OpenXmlHelper.GetBooleanValueAsBool(cellFormat.QuotePrefix, false); + if (cellFormat.ApplyProtection != null) { var protection = cellFormat.Protection; diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index c6542ee..dbd1e87 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -139,6 +139,7 @@ public UInt32 BorderId; public UInt32 FillId; public UInt32 FontId; + public Boolean IncludeQuotePrefix; public Int32 NumberFormatId; public XLStyleValue Style; public UInt32 StyleId; diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 3c80d15..cbadd34 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -3326,6 +3326,7 @@ FontId = 0, FillId = 0, BorderId = 0, + IncludeQuotePrefix = false, NumberFormatId = 0 //AlignmentId = 0 }); @@ -3432,7 +3433,8 @@ FontId = context.SharedFonts[xlStyle.Font].FontId, FillId = allSharedFills[xlStyle.Fill].FillId, BorderId = allSharedBorders[xlStyle.Border].BorderId, - NumberFormatId = numberFormatId + NumberFormatId = numberFormatId, + IncludeQuotePrefix = xlStyle.IncludeQuotePrefix }); } @@ -3700,6 +3702,7 @@ FontId = styleInfo.FontId, FillId = styleInfo.FillId, BorderId = styleInfo.BorderId, + QuotePrefix = OpenXmlHelper.GetBooleanValue(styleInfo.IncludeQuotePrefix, false), ApplyNumberFormat = true, ApplyAlignment = true, ApplyFill = ApplyFill(styleInfo), @@ -3725,6 +3728,7 @@ && f.FillId != null && styleInfo.FillId == f.FillId && f.FontId != null && styleInfo.FontId == f.FontId && f.NumberFormatId != null && styleInfo.NumberFormatId == f.NumberFormatId + && QuotePrefixesAreEqual(f.QuotePrefix, styleInfo.IncludeQuotePrefix) && (f.ApplyFill == null && styleInfo.Style.Fill == XLFillValue.Default || f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo)) && (f.ApplyBorder == null && styleInfo.Style.Border == XLBorderValue.Default || @@ -3747,6 +3751,11 @@ return p.Equals(xlProtection.Key); } + private static bool QuotePrefixesAreEqual(BooleanValue quotePrefix, Boolean includeQuotePrefix) + { + return OpenXmlHelper.GetBooleanValueAsBool(quotePrefix, false) == includeQuotePrefix; + } + private static bool AlignmentsAreEqual(Alignment alignment, XLAlignmentValue xlAlignment) { if (alignment != null) diff --git a/ClosedXML_Examples/StyleExamples.cs b/ClosedXML_Examples/StyleExamples.cs index 7617e76..869c9ab 100644 --- a/ClosedXML_Examples/StyleExamples.cs +++ b/ClosedXML_Examples/StyleExamples.cs @@ -61,6 +61,7 @@ new StyleBorder().Create(Path.Combine(path, "styleBorder.xlsx")); new StyleAlignment().Create(Path.Combine(path, "styleAlignment.xlsx")); new StyleNumberFormat().Create(Path.Combine(path, "styleNumberFormat.xlsx")); + new StyleIncludeQuotePrefix().Create(Path.Combine(path, "styleIncludeQuotePrefix.xlsx")); } // Private diff --git a/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs b/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs new file mode 100644 index 0000000..47129c6 --- /dev/null +++ b/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs @@ -0,0 +1,39 @@ +using ClosedXML.Excel; +using System; +using System.Linq; + +namespace ClosedXML_Examples.Styles +{ + public class StyleIncludeQuotePrefix : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Style IncludeQuotePrefix"); + + var data = Enumerable.Range(1, 20) + .Select(i => + new + { + IntegerIndex = i, + StringIndex = i.ToString(), + PaddedString1000 = (i * 1000).ToString().PadLeft(8, '0'), + PrependedString1000 = "Str" + (i * 1000).ToString().PadLeft(8, '0') + }); + + ws.FirstCell().InsertData(data); + + // Columns B to D will be of type text + // but column B will not have the leading quotation mark + ws.Column("B").Style.IncludeQuotePrefix = false; + + // Columns C and D will have the leading quotation mark + ws.Column("C").Style.IncludeQuotePrefix = true; + ws.Column("D").Style.SetIncludeQuotePrefix(); + + ws.Columns().AdjustToContents(); + + workbook.SaveAs(filePath); + } + } +} diff --git a/ClosedXML_Tests/Examples/StylesTests.cs b/ClosedXML_Tests/Examples/StylesTests.cs index c400a81..ac6b17e 100644 --- a/ClosedXML_Tests/Examples/StylesTests.cs +++ b/ClosedXML_Tests/Examples/StylesTests.cs @@ -49,6 +49,12 @@ } [Test] + public void StyleIncludeQuotePrefix() + { + TestHelper.RunTestExample(@"Styles\StyleIncludeQuotePrefix.xlsx"); + } + + [Test] public void StyleRowsColumns() { TestHelper.RunTestExample(@"Styles\StyleRowsColumns.xlsx"); diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx new file mode 100644 index 0000000..5e08f98 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx Binary files differ