Create Excels with C# and EPPlus: Formulas & DataValidation

Formulas

The project code creates an Excel like this:

The formulas below show how to set the #FORMULAS.

using (var package = new ExcelPackage())
{
	var sheet = package.Workbook.Worksheets.Add("Formula");

	// Starting = is optional
	sheet.Cells["A5"].Formula = "=COUNTA(A2:A4)";

	// Total column
	sheet.Cells["D2:D4"].Formula = "B2*C2"; // quantity * price
	Assert.That(sheet.Cells["D2"].FormulaR1C1, Is.EqualTo("RC[-2]*RC[-1]"));
	Assert.That(sheet.Cells["D4"].FormulaR1C1, Is.EqualTo("RC[-2]*RC[-1]"));

	// Total - discount column
	// Calculate formulas before they are available in the sheet
	// (Opening an Excel with Office will do this automatically)
	sheet.Cells["F2:F4"].Formula = "IF(ISBLANK(E2),D2,D2*(1-E2))";
	Assert.That(sheet.Cells["F2"].Text, Is.Empty);
	sheet.Calculate();
	Assert.That(sheet.Cells["F2"].Text, Is.Not.Empty);

	// Total row
	// R1C1 reference style
	sheet.Cells["D5"].FormulaR1C1 = "SUBTOTAL(9,R[-3]C:R[-1]C)"; // total
	Assert.That(sheet.Cells["D5"].Formula, Is.EqualTo("SUBTOTAL(9,D2:D4)"));
	sheet.Cells["F5"].FormulaR1C1 = "SUBTOTAL(9,R[-3]C:R[-1]C)"; // total - discount
	Assert.That(sheet.Cells["F5"].Formula, Is.EqualTo("SUBTOTAL(9,F2:F4)"));

	sheet.Calculate();
	sheet.Cells["H2:H5"].Formula = "F2*(1-$G$5)"; // Pin G5

	package.SaveAs(new FileInfo(@""));
}

SUBTOTAL

SUBTOTAL(9,…), as opposed to using SUM directly, will not include other subtotals in their calculation.

SUBTOTAL(FUNC_NUM,ref1,[ref2],...)

Add 100 to FUNC_NUM to ignore manually hidden rows. Filtered-out rows are always excluded.

DataValidation

Dropdownlists

using (var package = new ExcelPackage())
{
	var sheet = package.Workbook.Worksheets.Add("Validation");

	var list1 = sheet.Cells["C7"].DataValidation.AddListDataValidation();
	list1.Formula.Values.Add("Apples");
	list1.Formula.Values.Add("Oranges");
	list1.Formula.Values.Add("Lemons");

	// Or load from another sheet
	//package.Workbook.Worksheets.Add("OtherSheet");
	//list1.Formula.ExcelFormula = "OtherSheet!A1:A4";

	list1.ShowErrorMessage = true;
	list1.Error = "We only have those available :(";

	list1.ShowInputMessage = true;
	list1.PromptTitle = "Choose your juice";
	list1.Prompt = "Apples, oranges or lemons?";

	list1.AllowBlank = true;

	sheet.Cells["C7"].Value = "Pick";
	package.SaveAs(new FileInfo(@""));
}

Integer & DateTime validation

using (var package = new ExcelPackage())
{
	var sheet = package.Workbook.Worksheets.Add("intsAndSuch");

	// Integer validation
	IExcelDataValidationInt intValidation = sheet.DataValidations.AddIntegerValidation("A1");
	intValidation.Prompt = "Value between 1 and 5";
	intValidation.Operator = ExcelDataValidationOperator.between;
	intValidation.Formula.Value = 1;
	intValidation.Formula2.Value = 5;

	// DateTime validation
	IExcelDataValidationDateTime dateTimeValidation = sheet.DataValidations.AddDateTimeValidation("A2");
	dateTimeValidation.Prompt = "A date greater than today";
	dateTimeValidation.Operator = ExcelDataValidationOperator.greaterThan;
	dateTimeValidation.Formula.Value = DateTime.Now.Date;

	// Time validation
	IExcelDataValidationTime timeValidation = sheet.DataValidations.AddTimeValidation("A3");
	timeValidation.Operator = ExcelDataValidationOperator.greaterThan;
	var time = timeValidation.Formula.Value;
	time.Hour = 13;
	time.Minute = 30;
	time.Second = 10;

	// Existing validations
	var validations = package.Workbook.Worksheets.SelectMany(sheet1 => sheet1.DataValidations);

	package.SaveAs(new FileInfo(@""));
}