{"id":1684,"date":"2017-04-27T10:00:00","date_gmt":"2017-04-27T08:00:00","guid":{"rendered":"https:\/\/demo.sidekick.be\/itenium\/create-xlsx-excel-with-epplus-csharp-formulas-and-datavalidation\/"},"modified":"2025-11-28T13:31:47","modified_gmt":"2025-11-28T12:31:47","slug":"create-xlsx-excel-with-epplus-csharp-formulas-and-datavalidation","status":"publish","type":"post","link":"https:\/\/demo.sidekick.be\/itenium\/blog\/dotnet\/create-xlsx-excel-with-epplus-csharp-formulas-and-datavalidation\/","title":{"rendered":"Create Excels with C# and EPPlus: Formulas & DataValidation"},"content":{"rendered":"

<\/p>\n

\n

<\/p>\n

That\u2019s fine!<\/h1>\n

In case your users want to continue working with the Excels after generation.<\/p>\n

Not a problem for EPPlus. Instead of calculating values and writing them to an excel, leverage the power of Excel formulas.<\/p>\n

Formulas<\/h1>\n

The project code<\/a> creates an Excel like this:<\/p>\n\n\n\n\n\n\n\n\n\n
<\/th>\nA<\/th>\nB<\/th>\nC<\/th>\nD<\/th>\nE<\/th>\nF<\/th>\nG<\/th>\nH<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nName<\/td>\nQuantity<\/td>\nPrice<\/td>\nBase total<\/td>\nDiscount<\/td>\nTotal<\/td>\nSpecial<\/td>\nPayup<\/td>\n<\/tr>\n
2<\/td>\nNails<\/td>\n37<\/td>\n\u20ac3.99<\/td>\n=B2*C2<\/td>\n<\/td>\n=D2<\/td>\n<\/td>\n=F2 * 0.8<\/td>\n<\/tr>\n
3<\/td>\nHammer<\/td>\n5<\/td>\n\u20ac12.10<\/td>\n=B3*C3<\/td>\n10%<\/td>\n=D3 * 0.9<\/td>\n<\/td>\n=F3 * 0.8<\/td>\n<\/tr>\n
4<\/td>\nSaw<\/td>\n12<\/td>\n\u20ac15.37<\/td>\n=B4*C4<\/td>\n<\/td>\n=D4<\/td>\n<\/td>\n=F4 * 0.8<\/td>\n<\/tr>\n
5<\/td>\n#COUNT<\/td>\n<\/td>\n<\/td>\n#SUBTOTAL<\/td>\n<\/td>\n#SUBTOTAL<\/td>\n20%<\/td>\n#TOTAL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The formulas below show how to set the #FORMULAS.<\/p>\n

using (var package = new ExcelPackage())\n{\n\tvar sheet = package.Workbook.Worksheets.Add(\"Formula\");\n\n\t\/\/ Starting = is optional\n\tsheet.Cells[\"A5\"].Formula = \"=COUNTA(A2:A4)\";\n\n\t\/\/ Total column\n\tsheet.Cells[\"D2:D4\"].Formula = \"B2*C2\"; \/\/ quantity * price\n\tAssert.That(sheet.Cells[\"D2\"].FormulaR1C1, Is.EqualTo(\"RC[-2]*RC[-1]\"));\n\tAssert.That(sheet.Cells[\"D4\"].FormulaR1C1, Is.EqualTo(\"RC[-2]*RC[-1]\"));\n\n\t\/\/ Total - discount column\n\t\/\/ Calculate formulas before they are available in the sheet\n\t\/\/ (Opening an Excel with Office will do this automatically)\n\tsheet.Cells[\"F2:F4\"].Formula = \"IF(ISBLANK(E2),D2,D2*(1-E2))\";\n\tAssert.That(sheet.Cells[\"F2\"].Text, Is.Empty);\n\tsheet.Calculate();\n\tAssert.That(sheet.Cells[\"F2\"].Text, Is.Not.Empty);\n\n\t\/\/ Total row\n\t\/\/ R1C1 reference style\n\tsheet.Cells[\"D5\"].FormulaR1C1 = \"SUBTOTAL(9,R[-3]C:R[-1]C)\"; \/\/ total\n\tAssert.That(sheet.Cells[\"D5\"].Formula, Is.EqualTo(\"SUBTOTAL(9,D2:D4)\"));\n\tsheet.Cells[\"F5\"].FormulaR1C1 = \"SUBTOTAL(9,R[-3]C:R[-1]C)\"; \/\/ total - discount\n\tAssert.That(sheet.Cells[\"F5\"].Formula, Is.EqualTo(\"SUBTOTAL(9,F2:F4)\"));\n\n\tsheet.Calculate();\n\tsheet.Cells[\"H2:H5\"].Formula = \"F2*(1-$G$5)\"; \/\/ Pin G5\n\n\tpackage.SaveAs(new FileInfo(@\"\"));\n}\n<\/code><\/pre>\n
SUBTOTAL<\/h2>\n
SUBTOTAL(9,\u2026), as opposed to using SUM directly, will not include other subtotals in their calculation.<\/p>\n
SUBTOTAL(FUNC_NUM,ref1,[ref2],...)\n<\/code><\/pre>\n
Add 100 to FUNC_NUM to ignore manually hidden rows. Filtered-out rows are always excluded.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
FUNC_NUM<\/th>\nFunction name<\/th>\nDesc<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nAVERAGE<\/td>\n<\/td>\n<\/tr>\n
2<\/td>\nCOUNT<\/td>\nCounts how many numbers<\/td>\n<\/tr>\n
3<\/td>\nCOUNTA<\/td>\nCounts how many values<\/td>\n<\/tr>\n
4<\/td>\nMAX<\/td>\n<\/td>\n<\/tr>\n
5<\/td>\nMIN<\/td>\n<\/td>\n<\/tr>\n
6<\/td>\nPRODUCT<\/td>\n<\/td>\n<\/tr>\n
7<\/td>\nSTDEV<\/td>\nEstimates standard deviation based on a sample<\/td>\n<\/tr>\n
8<\/td>\nSTDEVP<\/td>\nCalculates standard deviation based on the entire population<\/td>\n<\/tr>\n
9<\/td>\nSUM<\/td>\n<\/td>\n<\/tr>\n
10<\/td>\nVAR<\/td>\nEstimates variance based on a sample<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
DataValidation<\/h1>\n
Dropdownlists<\/h2>\n
using (var package = new ExcelPackage())\n{\n\tvar sheet = package.Workbook.Worksheets.Add(\"Validation\");\n\n\tvar list1 = sheet.Cells[\"C7\"].DataValidation.AddListDataValidation();\n\tlist1.Formula.Values.Add(\"Apples\");\n\tlist1.Formula.Values.Add(\"Oranges\");\n\tlist1.Formula.Values.Add(\"Lemons\");\n\n\t\/\/ Or load from another sheet\n\t\/\/package.Workbook.Worksheets.Add(\"OtherSheet\");\n\t\/\/list1.Formula.ExcelFormula = \"OtherSheet!A1:A4\";\n\n\tlist1.ShowErrorMessage = true;\n\tlist1.Error = \"We only have those available :(\";\n\n\tlist1.ShowInputMessage = true;\n\tlist1.PromptTitle = \"Choose your juice\";\n\tlist1.Prompt = \"Apples, oranges or lemons?\";\n\n\tlist1.AllowBlank = true;\n\n\tsheet.Cells[\"C7\"].Value = \"Pick\";\n\tpackage.SaveAs(new FileInfo(@\"\"));\n}\n<\/code><\/pre>\n
Integer & DateTime validation<\/h2>\n
using (var package = new ExcelPackage())\n{\n\tvar sheet = package.Workbook.Worksheets.Add(\"intsAndSuch\");\n\n\t\/\/ Integer validation\n\tIExcelDataValidationInt intValidation = sheet.DataValidations.AddIntegerValidation(\"A1\");\n\tintValidation.Prompt = \"Value between 1 and 5\";\n\tintValidation.Operator = ExcelDataValidationOperator.between;\n\tintValidation.Formula.Value = 1;\n\tintValidation.Formula2.Value = 5;\n\n\t\/\/ DateTime validation\n\tIExcelDataValidationDateTime dateTimeValidation = sheet.DataValidations.AddDateTimeValidation(\"A2\");\n\tdateTimeValidation.Prompt = \"A date greater than today\";\n\tdateTimeValidation.Operator = ExcelDataValidationOperator.greaterThan;\n\tdateTimeValidation.Formula.Value = DateTime.Now.Date;\n\n\t\/\/ Time validation\n\tIExcelDataValidationTime timeValidation = sheet.DataValidations.AddTimeValidation(\"A3\");\n\ttimeValidation.Operator = ExcelDataValidationOperator.greaterThan;\n\tvar time = timeValidation.Formula.Value;\n\ttime.Hour = 13;\n\ttime.Minute = 30;\n\ttime.Second = 10;\n\n\t\/\/ Existing validations\n\tvar validations = package.Workbook.Worksheets.SelectMany(sheet1 => sheet1.DataValidations);\n\n\tpackage.SaveAs(new FileInfo(@\"\"));\n}\n<\/code><\/pre>\n
Also in this series<\/h1>\n