diff --git a/ClosedXML/ClosedXML/ClosedXML.sln b/ClosedXML/ClosedXML/ClosedXML.sln index 871b2e4..00bd435 100644 --- a/ClosedXML/ClosedXML/ClosedXML.sln +++ b/ClosedXML/ClosedXML/ClosedXML.sln @@ -51,12 +51,14 @@ {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Any CPU.Build.0 = Debug|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU - {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|x86.ActiveCfg = Debug|Any CPU + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|x86.ActiveCfg = Debug|x86 + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Debug|x86.Build.0 = Debug|x86 {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.ActiveCfg = Release|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Any CPU.Build.0 = Release|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|Mixed Platforms.Build.0 = Release|Any CPU - {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x86.ActiveCfg = Release|Any CPU + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x86.ActiveCfg = Release|x64 + {BD5E6BFE-E837-4A35-BCA9-39667D873A20}.Release|x86.Build.0 = Release|x64 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Any CPU.ActiveCfg = Debug|x86 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Mixed Platforms.ActiveCfg = Debug|x86 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Debug|Mixed Platforms.Build.0 = Debug|x86 @@ -65,8 +67,8 @@ {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Any CPU.ActiveCfg = Release|x86 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.ActiveCfg = Release|x86 {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|Mixed Platforms.Build.0 = Release|x86 - {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x86.ActiveCfg = Release|x86 - {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x86.Build.0 = Release|x86 + {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x86.ActiveCfg = Release|x64 + {38B882F0-E6F2-45C5-9BE9-CDC27FBEB4AB}.Release|x86.Build.0 = Release|x64 {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Any CPU.ActiveCfg = Debug|x86 {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Mixed Platforms.ActiveCfg = Debug|x86 {03A518D0-1CB7-488E-861C-C4E782B27A46}.Debug|Mixed Platforms.Build.0 = Debug|x86 @@ -75,18 +77,20 @@ {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Any CPU.ActiveCfg = Release|x86 {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Mixed Platforms.ActiveCfg = Release|x86 {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|Mixed Platforms.Build.0 = Release|x86 - {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|x86.ActiveCfg = Release|x86 - {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|x86.Build.0 = Release|x86 + {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|x86.ActiveCfg = Release|x64 + {03A518D0-1CB7-488E-861C-C4E782B27A46}.Release|x86.Build.0 = Release|x64 {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.ActiveCfg = Debug|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Any CPU.Build.0 = Debug|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Mixed Platforms.ActiveCfg = Debug|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|Mixed Platforms.Build.0 = Debug|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|x86.ActiveCfg = Debug|Any CPU + {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|x86.ActiveCfg = Debug|x86 + {5F43B12B-A900-40C6-9924-A0C0B032F791}.Debug|x86.Build.0 = Debug|x86 {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.ActiveCfg = Release|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Any CPU.Build.0 = Release|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Mixed Platforms.ActiveCfg = Release|Any CPU {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|Mixed Platforms.Build.0 = Release|Any CPU - {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|x86.ActiveCfg = Release|Any CPU + {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|x86.ActiveCfg = Release|x64 + {5F43B12B-A900-40C6-9924-A0C0B032F791}.Release|x86.Build.0 = Release|x64 {E005997D-B192-461F-AA3E-44007A33A3DF}.Debug|Any CPU.ActiveCfg = Debug|x86 {E005997D-B192-461F-AA3E-44007A33A3DF}.Debug|Mixed Platforms.ActiveCfg = Debug|x86 {E005997D-B192-461F-AA3E-44007A33A3DF}.Debug|Mixed Platforms.Build.0 = Debug|x86 @@ -95,8 +99,8 @@ {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|Any CPU.ActiveCfg = Release|x86 {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|Mixed Platforms.ActiveCfg = Release|x86 {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|Mixed Platforms.Build.0 = Release|x86 - {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|x86.ActiveCfg = Release|x86 - {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|x86.Build.0 = Release|x86 + {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|x86.ActiveCfg = Release|x64 + {E005997D-B192-461F-AA3E-44007A33A3DF}.Release|x86.Build.0 = Release|x64 EndGlobalSection GlobalSection(SolutionProperties) = preSolution HideSolutionNode = FALSE diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 47644cf..054c162 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -36,6 +36,72 @@ 4 bin\Release\ClosedXML.XML + + true + bin\x86\Debug\ + DEBUG;TRACE + full + x86 + bin\Debug\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + false + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + false + + + bin\x86\Release\ + TRACE + bin\Release\ClosedXML.XML + true + pdbonly + x86 + bin\Release\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + false + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + false + + + true + bin\x64\Debug\ + DEBUG;TRACE + full + x64 + bin\Debug\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + false + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + false + + + bin\x64\Release\ + TRACE + bin\Release\ClosedXML.XML + true + pdbonly + x64 + bin\Release\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + false + false + @@ -60,6 +126,17 @@ + + + + + + + + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 88cef1d..117159d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -135,5 +135,8 @@ IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable); XLHyperlink Hyperlink { get; set; } + IXLWorksheet Worksheet { get; } + + IXLDataValidation DataValidation { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 9148505..13a6420 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -12,7 +12,8 @@ internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime baseDate = new DateTime(1899, 12, 30); - XLWorksheet worksheet; + public IXLWorksheet Worksheet { get { return worksheet; } } + public XLWorksheet worksheet; public XLCell(IXLAddress address, IXLStyle defaultStyle, XLWorksheet worksheet) { this.Address = address; @@ -1001,5 +1002,13 @@ } } } + + public IXLDataValidation DataValidation + { + get + { + return this.AsRange().DataValidation; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs new file mode 100644 index 0000000..cb7e199 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidation.cs @@ -0,0 +1,46 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLErrorStyle { Stop, Warning, Information } + public enum XLAllowedValues { AnyValue, WholeNumber, Decimal, Date, Time, TextLength, List, Custom } + public enum XLOperator { EqualTo, NotEqualTo, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan, Between, NotBetween } + public interface IXLDataValidation + { + IXLRange Range { get; set; } + void Delete(); + void CopyFrom(IXLDataValidation dataValidation); + Boolean ShowInputMessage { get; set; } + Boolean ShowErrorMessage { get; set; } + Boolean IgnoreBlanks { get; set; } + Boolean InCellDropdown { get; set; } + String InputTitle { get; set; } + String InputMessage { get; set; } + String ErrorTitle { get; set; } + String ErrorMessage { get; set; } + XLErrorStyle ErrorStyle { get; set; } + XLAllowedValues AllowedValues { get; set; } + XLOperator Operator { get; set; } + + String Value { get; set; } + String MinValue { get; set; } + String MaxValue { get; set; } + + XLWholeNumberCriteria WholeNumber { get; } + XLDecimalCriteria Decimal { get; } + XLDateCriteria Date { get; } + XLTimeCriteria Time { get; } + XLTextLengthCriteria TextLength { get; } + + void List(String list); + void List(String list, Boolean inCellDropdown); + void List(IXLRange range); + void List(IXLRange range, Boolean inCellDropdown); + + void Custom(String customValidation); + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs new file mode 100644 index 0000000..661984c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLDataValidations.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLDataValidations: IEnumerable + { + void Add(IXLDataValidation dataValidation); + void Delete(IXLDataValidation dataValidation); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLValidationCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLValidationCriteria.cs new file mode 100644 index 0000000..e954b9f --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/IXLValidationCriteria.cs @@ -0,0 +1,37 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLValidationCriteria + { + void EqualTo(String value); + void NotEqualTo(String value); + void GreaterThan(String value); + void LessThan(String value); + void EqualOrGreaterThan(String value); + void EqualOrLessThan(String value); + void Between(String minValue, String maxValue); + void NotBetween(String minValue, String maxValue); + + void EqualTo(IXLRange range); + void NotEqualTo(IXLRange range); + void GreaterThan(IXLRange range); + void LessThan(IXLRange range); + void EqualOrGreaterThan(IXLRange range); + void EqualOrLessThan(IXLRange range); + void Between(IXLRange minValue, IXLRange maxValue); + void NotBetween(IXLRange minValue, IXLRange maxValue); + + void EqualTo(IXLCell cell); + void NotEqualTo(IXLCell cell); + void GreaterThan(IXLCell cell); + void LessThan(IXLCell cell); + void EqualOrGreaterThan(IXLCell cell); + void EqualOrLessThan(IXLCell cell); + void Between(IXLCell minValue, IXLCell maxValue); + void NotBetween(IXLCell minValue, IXLCell maxValue); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs new file mode 100644 index 0000000..84d72e8 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -0,0 +1,128 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLDataValidation: IXLDataValidation + { + public XLDataValidation(IXLRange range) + { + this.Range = range; + this.AllowedValues = XLAllowedValues.AnyValue; + this.IgnoreBlanks = true; + ShowErrorMessage = true; + ShowInputMessage = true; + InCellDropdown = true; + } + public IXLRange Range { get; set; } + public void Delete() + { + ((XLRange)Range).Worksheet.DataValidations.Delete(this); + } + public void CopyFrom(IXLDataValidation dataValidation) + { + IgnoreBlanks = dataValidation.IgnoreBlanks; + InCellDropdown = dataValidation.InCellDropdown; + ShowErrorMessage = dataValidation.ShowErrorMessage; + ShowInputMessage = dataValidation.ShowInputMessage; + InputTitle = dataValidation.InputTitle; + InputMessage = dataValidation.InputMessage; + ErrorTitle = dataValidation.ErrorTitle; + ErrorMessage = dataValidation.ErrorMessage; + ErrorStyle = dataValidation.ErrorStyle; + AllowedValues = dataValidation.AllowedValues; + Operator = dataValidation.Operator; + MinValue = dataValidation.MinValue; + MaxValue = dataValidation.MaxValue; + } + public Boolean IgnoreBlanks { get; set; } + public Boolean InCellDropdown { get; set; } + public Boolean ShowInputMessage { get; set; } + public String InputTitle { get; set; } + public String InputMessage { get; set; } + public Boolean ShowErrorMessage { get; set; } + public String ErrorTitle { get; set; } + public String ErrorMessage { get; set; } + public XLErrorStyle ErrorStyle { get; set; } + public XLAllowedValues AllowedValues { get; set; } + public XLOperator Operator { get; set; } + + public String Value + { + get { return MinValue; } + set { MinValue = value; } + } + public String MinValue { get; set; } + public String MaxValue { get; set; } + + public XLWholeNumberCriteria WholeNumber + { + get + { + AllowedValues = XLAllowedValues.WholeNumber; + return new XLWholeNumberCriteria(this); + } + } + public XLDecimalCriteria Decimal + { + get + { + AllowedValues = XLAllowedValues.Decimal; + return new XLDecimalCriteria(this); + } + } + public XLDateCriteria Date + { + get + { + AllowedValues = XLAllowedValues.Date; + return new XLDateCriteria(this); + } + } + + public XLTimeCriteria Time + { + get + { + AllowedValues = XLAllowedValues.Time; + return new XLTimeCriteria(this); + } + } + + public XLTextLengthCriteria TextLength + { + get + { + AllowedValues = XLAllowedValues.TextLength; + return new XLTextLengthCriteria(this); + } + } + + public void List(String list) + { + List(list, true); + } + public void List(String list, Boolean inCellDropdown) + { + AllowedValues = XLAllowedValues.List; + Value = list; + } + public void List(IXLRange range) + { + List(range, true); + } + public void List(IXLRange range, Boolean inCellDropdown) + { + AllowedValues = XLAllowedValues.List; + Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + } + + public void Custom(String customValidation) + { + AllowedValues = XLAllowedValues.Custom; + Value = customValidation; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs new file mode 100644 index 0000000..3753945 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidations.cs @@ -0,0 +1,31 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLDataValidations: IXLDataValidations + { + private Dictionary dataValidations = new Dictionary(); + public void Add(IXLDataValidation dataValidation) + { + dataValidations.Add(dataValidation.Range.RangeAddress.ToString(), dataValidation); + } + + public void Delete(IXLDataValidation dataValidation) + { + dataValidations.Remove(dataValidation.Range.RangeAddress.ToString()); + } + + public IEnumerator GetEnumerator() + { + return dataValidations.Values.GetEnumerator(); + } + + System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() + { + return GetEnumerator(); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs new file mode 100644 index 0000000..180fe05 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDateCriteria.cs @@ -0,0 +1,62 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using System.Globalization; + +namespace ClosedXML.Excel +{ + public class XLDateCriteria : XLValidationCriteria + { + public XLDateCriteria(IXLDataValidation dataValidation) + : base(dataValidation) + { + + } + + public void EqualTo(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(DateTime value) + { + dataValidation.Value = value.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(DateTime minValue, DateTime maxValue) + { + dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(DateTime minValue, DateTime maxValue) + { + dataValidation.MinValue = minValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.MaxValue = maxValue.ToOADate().ToString(CultureInfo.InvariantCulture); + dataValidation.Operator = XLOperator.NotBetween; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs new file mode 100644 index 0000000..a15275b --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDecimalCriteria.cs @@ -0,0 +1,61 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLDecimalCriteria : XLValidationCriteria + { + public XLDecimalCriteria(IXLDataValidation dataValidation) + : base(dataValidation) + { + + } + + public void EqualTo(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(Double value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(Double minValue, Double maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(Double minValue, Double maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.NotBetween; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs new file mode 100644 index 0000000..ce25362 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTextLengthCriteria.cs @@ -0,0 +1,61 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLTextLengthCriteria : XLValidationCriteria + { + public XLTextLengthCriteria(IXLDataValidation dataValidation) + : base(dataValidation) + { + + } + + public void EqualTo(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(UInt32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(UInt32 minValue, UInt32 maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(UInt32 minValue, UInt32 maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.NotBetween; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs new file mode 100644 index 0000000..697ff6b --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLTimeCriteria.cs @@ -0,0 +1,66 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLTimeCriteria : XLValidationCriteria + { + public XLTimeCriteria(IXLDataValidation dataValidation) + : base(dataValidation) + { + + } + + private String GetXLTime(TimeSpan value) + { + return (value.TotalHours / 24.0).ToString(); + } + + public void EqualTo(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(TimeSpan value) + { + dataValidation.Value = GetXLTime(value); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(TimeSpan minValue, TimeSpan maxValue) + { + dataValidation.MinValue = GetXLTime(minValue); + dataValidation.MaxValue = GetXLTime(maxValue); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(TimeSpan minValue, TimeSpan maxValue) + { + dataValidation.MinValue = GetXLTime(minValue); + dataValidation.MaxValue = GetXLTime(maxValue); + dataValidation.Operator = XLOperator.NotBetween; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs new file mode 100644 index 0000000..1680b8f --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLValidationCriteria.cs @@ -0,0 +1,146 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public abstract class XLValidationCriteria : IXLValidationCriteria + { + protected IXLDataValidation dataValidation; + internal XLValidationCriteria(IXLDataValidation dataValidation) + { + this.dataValidation = dataValidation; + } + + public void EqualTo(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(String value) + { + dataValidation.Value = value; + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(String minValue, String maxValue) + { + dataValidation.MinValue = minValue; + dataValidation.MaxValue = maxValue; + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(String minValue, String maxValue) + { + dataValidation.MinValue = minValue; + dataValidation.MaxValue = maxValue; + dataValidation.Operator = XLOperator.NotBetween; + } + + + public void EqualTo(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(IXLRange range) + { + dataValidation.Value = String.Format("'{0}'!{1}", ((XLRange)range).Worksheet.Name, range.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(IXLRange minValue, IXLRange maxValue) + { + dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, minValue.RangeAddress.ToString()); + dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, maxValue.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(IXLRange minValue, IXLRange maxValue) + { + dataValidation.MinValue = String.Format("'{0}'!{1}", ((XLRange)minValue).Worksheet.Name, minValue.RangeAddress.ToString()); + dataValidation.MaxValue = String.Format("'{0}'!{1}", ((XLRange)maxValue).Worksheet.Name, maxValue.RangeAddress.ToString()); + dataValidation.Operator = XLOperator.NotBetween; + } + + public void EqualTo(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(IXLCell cell) + { + dataValidation.Value = String.Format("'{0}'!{1}", cell.Worksheet.Name, cell.Address.ToString()); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(IXLCell minValue, IXLCell maxValue) + { + dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address.ToString()); + dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address.ToString()); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(IXLCell minValue, IXLCell maxValue) + { + dataValidation.MinValue = String.Format("'{0}'!{1}", minValue.Worksheet.Name, minValue.Address.ToString()); + dataValidation.MaxValue = String.Format("'{0}'!{1}", maxValue.Worksheet.Name, maxValue.Address.ToString()); + dataValidation.Operator = XLOperator.NotBetween; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLWholeNumberCriteria.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLWholeNumberCriteria.cs new file mode 100644 index 0000000..1db96fd --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLWholeNumberCriteria.cs @@ -0,0 +1,60 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLWholeNumberCriteria : XLValidationCriteria + { + public XLWholeNumberCriteria(IXLDataValidation dataValidation): base(dataValidation) + { + + } + + public void EqualTo(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualTo; + } + public void NotEqualTo(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.NotEqualTo; + } + public void GreaterThan(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.GreaterThan; + } + public void LessThan(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.LessThan; + } + public void EqualOrGreaterThan(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrGreaterThan; + } + public void EqualOrLessThan(Int32 value) + { + dataValidation.Value = value.ToString(); + dataValidation.Operator = XLOperator.EqualOrLessThan; + } + public void Between(Int32 minValue, Int32 maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.Between; + } + public void NotBetween(Int32 minValue, Int32 maxValue) + { + dataValidation.MinValue = minValue.ToString(); + dataValidation.MaxValue = maxValue.ToString(); + dataValidation.Operator = XLOperator.NotBetween; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index c8235f2..c4903b0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -250,5 +250,6 @@ IXLRange RangeUsed(); + IXLDataValidations DataValidations { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 9a989f8..c6c3dce 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -215,6 +215,8 @@ IXLTable CreateTable(String name); IXLRange RangeUsed(); + + //IXLWorksheet Worksheet { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 1b52c86..5ce7046 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -156,5 +156,7 @@ Boolean ShareString { set; } IXLHyperlinks Hyperlinks { get; } + + IXLDataValidation DataValidation { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 95eba21..c9d225b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -8,6 +8,7 @@ { internal class XLRange: XLRangeBase, IXLRange { + //public new IXLWorksheet Worksheet { get { return base.Worksheet; } } public XLRangeParameters RangeParameters { get; private set; } public XLRange(XLRangeParameters xlRangeParameters): base(xlRangeParameters.RangeAddress) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index bbc5820..5c6d45d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -14,7 +14,7 @@ protected IXLStyle defaultStyle; public IXLRangeAddress RangeAddress { get; protected set; } - internal XLWorksheet Worksheet { get; set; } + public XLWorksheet Worksheet { get; set; } public IXLCell FirstCell() { @@ -526,6 +526,14 @@ ); ClearMerged(); + + List hyperlinksToRemove = new List(); + foreach (var hl in Worksheet.Hyperlinks) + { + if (this.Contains(hl.Cell.AsRange())) + hyperlinksToRemove.Add(hl); + } + hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); } public void ClearStyles() @@ -650,6 +658,14 @@ } mergesToRemove.ForEach(r => Worksheet.Internals.MergedRanges.Remove(r)); + List hyperlinksToRemove = new List(); + foreach (var hl in Worksheet.Hyperlinks) + { + if (this.Contains(hl.Cell.AsRange())) + hyperlinksToRemove.Add(hl); + } + hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); + var shiftedRange = (XLRange)this.AsRange(); if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) { @@ -871,5 +887,46 @@ return hyperlinks; } } + + public IXLDataValidation DataValidation + { + get + { + String address = this.RangeAddress.ToString(); + if (Worksheet.DataValidations.Where(dv => dv.Range.RangeAddress.ToString() == address).Any()) + { + return Worksheet.DataValidations.Where(dv => dv.Range.RangeAddress.ToString() == address).Single(); + } + else + { + var dv = DataValidationsIntersects(); + if (dv != null) + { + dv.Delete(); + foreach (var c in dv.Range.Cells()) + { + if (!this.Contains(c.Address.ToString())) + { + c.DataValidation.CopyFrom(dv); + } + } + } + + var dataValidation = new XLDataValidation(this.AsRange()); + Worksheet.DataValidations.Add(dataValidation); + return dataValidation; + } + } + } + + private IXLDataValidation DataValidationsIntersects() + { + foreach (var dv in Worksheet.DataValidations) + { + if (dv.Range.Intersects(this)) + return dv; + } + return null; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index e824118..37dc69f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -327,7 +327,8 @@ } #endregion - + LoadDataValidations(worksheetPart, ws); + LoadHyperlinks(worksheetPart, ws); LoadPrintOptions(worksheetPart, ws); @@ -390,6 +391,33 @@ } } + private void LoadDataValidations(WorksheetPart worksheetPart, XLWorksheet ws) + { + var dataValidationList = worksheetPart.Worksheet.Descendants(); + if (dataValidationList.Count() > 0) + { + var dataValidations = (DataValidations)dataValidationList.First(); + foreach (var dvs in dataValidations.Descendants()) + { + var dvt = ws.Range(dvs.SequenceOfReferences.InnerText).DataValidation; + if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; + if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; + if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; + if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; + if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; + if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; + if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; + if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; + if (dvs.ErrorStyle != null) dvt.ErrorStyle = dataValidationErrorStyleValues.Single(p => p.Value == dvs.ErrorStyle).Key; + if (dvs.Type != null) dvt.AllowedValues = dataValidationValues.Single(p => p.Value == dvs.Type).Key; + if (dvs.Operator != null) dvt.Operator = dataValidationOperatorValues.Single(p => p.Value == dvs.Operator).Key; + if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; + if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; + + } + } + } + private void LoadHyperlinks(WorksheetPart worksheetPart, XLWorksheet ws) { var hyperlinkDictionary = new Dictionary(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index e834dde..88ad821 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -34,6 +34,9 @@ private List> referenceModeValues = new List>(); private List> alignmentReadingOrderValues = new List>(); private List> totalsRowFunctionValues = new List>(); + private List> dataValidationValues = new List>(); + private List> dataValidationErrorStyleValues = new List>(); + private List> dataValidationOperatorValues = new List>(); private Boolean populated = false; private void PopulateEnums() @@ -54,6 +57,9 @@ PopulateReferenceModeValues(); PopulateAlignmentReadingOrderValues(); PopulateTotalsRowFunctionValues(); + PopulateDataValidationValues(); + PopulateDataValidationErrorStyleValues(); + PopulateDataValidationOperatorValues(); populated = true; } } @@ -270,6 +276,37 @@ totalsRowFunctionValues.Add(new KeyValuePair(XLTotalsRowFunction.Custom, TotalsRowFunctionValues.Custom)); } + private void PopulateDataValidationValues() + { + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.AnyValue, DataValidationValues.None)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.Custom, DataValidationValues.Custom)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.Date, DataValidationValues.Date)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.Decimal, DataValidationValues.Decimal)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.List, DataValidationValues.List)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.TextLength, DataValidationValues.TextLength)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.Time, DataValidationValues.Time)); + dataValidationValues.Add(new KeyValuePair(XLAllowedValues.WholeNumber, DataValidationValues.Whole)); + } + + private void PopulateDataValidationErrorStyleValues() + { + dataValidationErrorStyleValues.Add(new KeyValuePair(XLErrorStyle.Information, DataValidationErrorStyleValues.Information)); + dataValidationErrorStyleValues.Add(new KeyValuePair(XLErrorStyle.Warning, DataValidationErrorStyleValues.Warning)); + dataValidationErrorStyleValues.Add(new KeyValuePair(XLErrorStyle.Stop, DataValidationErrorStyleValues.Stop)); + } + + private void PopulateDataValidationOperatorValues() + { + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.Between, DataValidationOperatorValues.Between)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.EqualOrGreaterThan , DataValidationOperatorValues.GreaterThanOrEqual )); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.EqualOrLessThan, DataValidationOperatorValues.LessThanOrEqual)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.EqualTo, DataValidationOperatorValues.Equal)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.GreaterThan, DataValidationOperatorValues.GreaterThan)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.LessThan, DataValidationOperatorValues.LessThan)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.NotBetween, DataValidationOperatorValues.NotBetween)); + dataValidationOperatorValues.Add(new KeyValuePair(XLOperator.NotEqualTo, DataValidationOperatorValues.NotEqual)); + } + #endregion private void CreatePackage(String filePath) @@ -1913,7 +1950,66 @@ var phoneticProperties = worksheetPart.Worksheet.Elements().FirstOrDefault(); + #region DataValidations + DataValidations dataValidations = null; + if (xlWorksheet.DataValidations.Count() == 0) + { + worksheetPart.Worksheet.RemoveAllChildren(); + } + else + { + worksheetPart.Worksheet.Elements().FirstOrDefault(); + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement; + if (phoneticProperties != null) + previousElement = phoneticProperties; + else if (mergeCells != null) + previousElement = mergeCells; + else if (customSheetViews != null) + previousElement = customSheetViews; + else if (autoFilter != null) + previousElement = autoFilter; + else if (sheetData != null) + previousElement = sheetData; + else if (columns != null) + previousElement = columns; + else + previousElement = worksheetPart.Worksheet.SheetFormatProperties; + + worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); + } + + dataValidations = worksheetPart.Worksheet.Elements().First(); + dataValidations.RemoveAllChildren(); + foreach (var dv in xlWorksheet.DataValidations) + { + DataValidation dataValidation = new DataValidation() + { + AllowBlank = dv.IgnoreBlanks, + Formula1 = new Formula1(dv.MinValue), + Formula2 = new Formula2(dv.MaxValue), + Type = dataValidationValues.Single(p => p.Key == dv.AllowedValues).Value, + ShowErrorMessage = dv.ShowErrorMessage, + Prompt = dv.InputMessage, + PromptTitle = dv.InputTitle, + ErrorTitle = dv.ErrorTitle, + Error = dv.ErrorMessage, + ShowDropDown = !dv.InCellDropdown, + ShowInputMessage = dv.ShowInputMessage, + ErrorStyle = dataValidationErrorStyleValues.Single(p => p.Key == dv.ErrorStyle).Value, + Operator = dataValidationOperatorValues.Single(p => p.Key == dv.Operator).Value, + SequenceOfReferences = new ListValue() { InnerText = dv.Range.RangeAddress.ToString() } + }; + + dataValidations.Append(dataValidation); + } + dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count(); + } + + + #endregion #region Hyperlinks Hyperlinks hyperlinks = null; @@ -1929,7 +2025,9 @@ if (worksheetPart.Worksheet.Elements().Count() == 0) { OpenXmlElement previousElement; - if (phoneticProperties != null) + if (dataValidations != null) + previousElement = dataValidations; + else if (phoneticProperties != null) previousElement = phoneticProperties; else if (mergeCells != null) previousElement = mergeCells; @@ -1981,6 +2079,8 @@ OpenXmlElement previousElement; if (hyperlinks != null) previousElement = hyperlinks; + else if (dataValidations != null) + previousElement = dataValidations; else if (phoneticProperties != null) previousElement = phoneticProperties; else if (mergeCells != null) @@ -2015,6 +2115,8 @@ previousElement = printOptions; else if (hyperlinks != null) previousElement = hyperlinks; + else if (dataValidations != null) + previousElement = dataValidations; else if (phoneticProperties != null) previousElement = phoneticProperties; else if (mergeCells != null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index f6068b8..b96268f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -30,6 +30,7 @@ SheetView = new XLSheetView(); Tables = new XLTables(); Hyperlinks = new XLHyperlinks(); + DataValidations = new XLDataValidations(); this.workbook = workbook; style = new XLStyle(this, workbook.Style); Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(workbook, workbook.Style) , workbook); @@ -600,5 +601,6 @@ #endregion public new IXLHyperlinks Hyperlinks { get; private set; } + public IXLDataValidations DataValidations { get; private set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 1064c4c..b62fc6d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -37,6 +37,37 @@ prompt 4 + + true + bin\x64\Debug\ + DEBUG;TRACE + full + x64 + bin\Debug\ClosedXML_Examples.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + bin\x64\Release\ + TRACE + true + pdbonly + x64 + bin\Release\ClosedXML_Examples.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + false + @@ -58,6 +89,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 50c16f1..61a8bfb 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -69,6 +69,7 @@ new InsertingTables().Create(@"C:\Excel Files\Created\InsertingTables.xlsx"); new InsertingData().Create(@"C:\Excel Files\Created\InsertingData.xlsx"); new Hyperlinks().Create(@"C:\Excel Files\Created\Hyperlinks.xlsx"); + new DataValidation().Create(@"C:\Excel Files\Created\DataValidation.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs new file mode 100644 index 0000000..2bc0e93 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs @@ -0,0 +1,89 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class DataValidation + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Data Validation"); + + // Decimal between 1 and 5 + ws.Cell(1, 1).DataValidation.Decimal.Between(1, 5); + + // Whole number equals 2 + var dv1 = ws.Range("A2:A3").DataValidation; + dv1.WholeNumber.EqualTo(2); + // Change the error message + dv1.ErrorStyle = XLErrorStyle.Warning; + dv1.ErrorTitle = "Number out of range"; + dv1.ErrorMessage = "This cell only allows the number 2."; + + // Date after the millenium + var dv2 = ws.Cell("A4").DataValidation; + dv2.Date.EqualOrGreaterThan(new DateTime(2000, 1, 1)); + // Change the input message + dv2.InputTitle = "Can't party like it's 1999."; + dv2.InputMessage = "Please enter a date in this century."; + + // From a list + ws.Cell("C1").Value = "Yes"; + ws.Cell("C2").Value = "No"; + ws.Cell("A5").DataValidation.List(ws.Range("C1:C2")); + + // Intersecting dataValidations + ws.Range("B1:B4").DataValidation.WholeNumber.EqualTo(1); + ws.Range("B3:B4").DataValidation.WholeNumber.EqualTo(2); + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs index 669348c..8782c5f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/Hyperlinks.cs @@ -99,6 +99,16 @@ // List all hyperlinks in a range: var hyperlinksInRange = ws.Range("A1:A3").Hyperlinks; + // Clearing a cell with a hyperlink + ws.Cell(++ro, 1).Value = "ERROR!"; + ws.Cell(ro, 1).Hyperlink.InternalAddress = "A1"; + ws.Cell(ro, 1).Clear(); + + // Deleting a cell with a hyperlink + ws.Cell(++ro, 1).Value = "ERROR!"; + ws.Cell(ro, 1).Hyperlink.InternalAddress = "A1"; + ws.Cell(ro, 1).Clear(); + ws.Columns().AdjustToContents(); wb.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 45a49ed..2b0846c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -36,6 +36,72 @@ 4 bin\Release\ClosedXML.XML + + true + bin\x86\Debug\ + DEBUG;TRACE + full + x86 + bin\Debug\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + bin\x86\Release\ + TRACE + bin\Release\ClosedXML.XML + true + pdbonly + x86 + bin\Release\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + true + bin\x64\Debug\ + DEBUG;TRACE + full + x64 + bin\Debug\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + bin\x64\Release\ + TRACE + bin\Release\ClosedXML.XML + true + pdbonly + x64 + bin\Release\ClosedXML.dll.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + @@ -94,6 +160,39 @@ Excel\CustomProperties\XLCustomProperty.cs + + Excel\DataValidation\IXLDataValidation.cs + + + Excel\DataValidation\IXLDataValidations.cs + + + Excel\DataValidation\IXLValidationCriteria.cs + + + Excel\DataValidation\XLDataValidation.cs + + + Excel\DataValidation\XLDataValidations.cs + + + Excel\DataValidation\XLDateCriteria.cs + + + Excel\DataValidation\XLDecimalCriteria.cs + + + Excel\DataValidation\XLTextLengthCriteria.cs + + + Excel\DataValidation\XLTimeCriteria.cs + + + Excel\DataValidation\XLValidationCriteria.cs + + + Excel\DataValidation\XLWholeNumberCriteria.cs + Excel\Hyperlinks\IXLHyperlinks.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 858a71f..93791a3 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -37,6 +37,38 @@ prompt 4 + + true + bin\x64\Debug\ + DEBUG;TRACE + full + x64 + bin\Debug\ClosedXML_Sandbox.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + bin\x64\Release\ + TRACE + true + pdbonly + x64 + bin\Release\ClosedXML_Sandbox.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.cs + prompt + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + False diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index d0d772a..81d2ee6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -15,20 +15,17 @@ static void Main(string[] args) { - //new ClosedXML_Examples.Ranges.UsingTables().Create(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); - var wb = new XLWorkbook(@"C:\Excel Files\ForTesting\Hyperlinks_2.xlsx"); - //var wb = new XLWorkbook(); - //var c = wb.Worksheets.Add("New").Cell(1, 1); - //c.Value = "Hello"; - //c.ShareString = false; + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("New"); + var dataValidation = ws.Range("A1:B2").DataValidation; + dataValidation.WholeNumber.Between(1, 5); + dataValidation.ErrorStyle = XLErrorStyle.Warning; + dataValidation.ErrorTitle = "Number out of range"; + dataValidation.ErrorMessage = "Please enter a number between 1 and 5"; wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); - - //String test = @"../file.txt"; - //Console.WriteLine("Uri.IsWellFormedUriString says: {0}", Uri.IsWellFormedUriString(test, UriKind.RelativeOrAbsolute)); - //Console.WriteLine("TryCreate says: {0}", TryCreate(test)); - //Console.WriteLine("IsValidUri says: {0}", IsValidUri(test)); - //Console.ReadKey(); } + + public static String GetSheetPassword(String password) { Int32 pLength = password.Length; diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/ClosedXML_Sandbox_VB.vbproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/ClosedXML_Sandbox_VB.vbproj index 3574cb0..3d2b043 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/ClosedXML_Sandbox_VB.vbproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox_VB/ClosedXML_Sandbox_VB.vbproj @@ -53,6 +53,40 @@ On + + true + true + true + bin\x64\Debug\ + ClosedXML_Sandbox_VB.xml + 42016,41999,42017,42018,42019,42032,42036,42020,42021,42022 + full + x64 + bin\Debug\ClosedXML_Sandbox_VB.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.vb + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules + true + + + true + bin\x64\Release\ + ClosedXML_Sandbox_VB.xml + true + 42016,41999,42017,42018,42019,42032,42036,42020,42021,42022 + pdbonly + x64 + bin\Release\ClosedXML_Sandbox_VB.exe.CodeAnalysisLog.xml + true + GlobalSuppressions.vb + MinimumRecommendedRules.ruleset + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\\Rule Sets + true + ;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\\Rules +