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");
}
}
}
