public static void Run() { using (var stream = new FileStream($"{nameof(DataValidation)}.xlsx", FileMode.Create, FileAccess.Write)) using (var xlsxWriter = new XlsxWriter(stream)) { xlsxWriter.BeginWorksheet("Sheet 1") .BeginRow() .AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\"")) .Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace))) .SkipColumns(1) .AddDataValidation(1, 2, new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\"")) .Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace)), repeatCount: 2) .BeginRow() .AddDataValidation(XlsxDataValidation.List(new[] { "Lorem", "Ipsum", "Dolor" })) .Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace))) .BeginRow() .AddDataValidation(XlsxDataValidation.List(new[] { "1", "3.141592", "Sit" }, showErrorMessage: true, showInputMessage: true, errorStyle: XlsxDataValidation.ErrorStyle.Warning)) .Write(XlsxStyle.Default.With(new XlsxFill(Color.AliceBlue))) .BeginRow() .AddDataValidation(XlsxDataValidation.List(new[] { "1", "\"2\"", "\"3.141592\"", "\"3,141592\"", "3,,141592", "\"Sit, Amet\"" }, showErrorMessage: true, showInputMessage: true, errorTitle: "Error title", error: "A very informative error message", promptTitle: "Prompt title", prompt: "A very enlightening prompt")) .Write(XlsxStyle.Default.With(new XlsxFill(Color.BlueViolet))) .BeginRow() .AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "=Choices!A1:A3")) .Write(XlsxStyle.Default.With(new XlsxFill(Color.PaleGreen))) .BeginWorksheet("Choices").BeginRow().Write(3.141592).BeginRow().Write("Lorem").BeginRow().Write("Ipsum, Dolor"); } }
public static void ListAsFormula() { using (var stream = new MemoryStream()) { using (var xlsxWriter = new XlsxWriter(stream)) { var dataValidation = new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\""); xlsxWriter.SetDefaultStyle(XlsxStyle.Default.With(new XlsxFill(Color.OldLace))).BeginWorksheet("Sheet 1") .BeginRow().AddDataValidation(dataValidation).Write().SkipColumns(1).AddDataValidation(1, 2, dataValidation).Write(repeatCount: 2); } using (var package = new ExcelPackage(stream)) { var dataValidation = package.Workbook.Worksheets[0].DataValidations[0] as ExcelDataValidationList; dataValidation.Should().NotBeNull(); dataValidation.ValidationType.Should().Be(ExcelDataValidationType.List); dataValidation.Address.Address.Should().Be("A1 C1:D1"); dataValidation.Formula.Values.Should().BeEquivalentTo("Lorem", "Ipsum", "Dolor"); } } }
public static void ListByRange() { using (var stream = new MemoryStream()) { using (var xlsxWriter = new XlsxWriter(stream)) { var dataValidation = new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "=Choices!A1:A3"); xlsxWriter .BeginWorksheet("Sheet 1").BeginRow().AddDataValidation(dataValidation).Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace))) .BeginWorksheet("Choices").BeginRow().Write(3.141592).BeginRow().Write("Lorem").BeginRow().Write("Ipsum, Dolor"); } using (var package = new ExcelPackage(stream)) { var dataValidation = package.Workbook.Worksheets[0].DataValidations[0] as ExcelDataValidationList; dataValidation.Should().NotBeNull(); dataValidation.ValidationType.Should().Be(ExcelDataValidationType.List); dataValidation.Address.Address.Should().Be("A1"); dataValidation.Formula.ExcelFormula.Should().Be("=Choices!A1:A3"); } } }
public static void ListAsChoices() { using (var stream = new MemoryStream()) { using (var xlsxWriter = new XlsxWriter(stream)) xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().AddDataValidation(XlsxDataValidation.List(new[] { "Lorem", "Ipsum", "Dolor" })).Write(); using (var package = new ExcelPackage(stream)) { var dataValidation = package.Workbook.Worksheets[0].DataValidations[0] as ExcelDataValidationList; dataValidation.Should().NotBeNull(); dataValidation.ValidationType.Should().Be(ExcelDataValidationType.List); dataValidation.Address.Address.Should().Be("A1"); dataValidation.Formula.Values.Should().BeEquivalentTo("Lorem", "Ipsum", "Dolor"); } } }