Version 4.5.3.3 is the last version of EPPlus you can use without a license for commercial use.
See some alternatives if that is a dealbreaker for you!

We now have a blog series on an alternative Excel package ClosedXML
Which is basically on equal footing with EPPlus.

That’s fine!

In case your users want to continue working with the Excels after generation.

Not a problem for EPPlus. Instead of calculating values and writing them to an excel, leverage the power of Excel formulas.

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