# Create Excels with C# and EPPlus: Formulas & DataValidation

## Create Excels with C# and EPPlus: Formulas & DataValidation

posted in dotnet on

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:

A B C D E F G H
1 Name Quantity Price Base total Discount Total Special Payup
2 Nails 37 €3.99 =B2*C2   =D2   =F2 * 0.8
3 Hammer 5 €12.10 =B3*C3 10% =D3 * 0.9   =F3 * 0.8
4 Saw 12 €15.37 =B4*C4   =D4   =F4 * 0.8
5 #COUNT     #SUBTOTAL   #SUBTOTAL 20% #TOTAL

The formulas below show how to set the #FORMULAS.

``````using (var package = new ExcelPackage())
{

// 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 apposed 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.

FUNC_NUM Function name Desc
1 AVERAGE
2 COUNT Counts how many numbers
3 COUNTA Counts how many values
4 MAX
5 MIN
6 PRODUCT
7 STDEV Estimates standard deviation based on a sample
8 STDEVP Calculates standard deviation based on the entire population
9 SUM
10 VAR Estimates variance based on a sample

# DataValidation

``````using (var package = new ExcelPackage())
{

// Or load from another sheet
//list1.Formula.ExcelFormula = "OtherSheet!A1:A4";

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

list1.ShowInputMessage = true;
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())
{

// Integer validation
intValidation.Prompt = "Value between 1 and 5";
intValidation.Operator = ExcelDataValidationOperator.between;
intValidation.Formula.Value = 1;
intValidation.Formula2.Value = 5;

// DateTime validation
dateTimeValidation.Prompt = "A date greater than today";
dateTimeValidation.Operator = ExcelDataValidationOperator.greaterThan;
dateTimeValidation.Formula.Value = DateTime.Now.Date;

// Time validation
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(@""));
}
``````

Stuff that came into being during the making of this post
Tags: excel tutorial