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

Not a problem for ClosedXML. 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.

When setting a range with FormulaA1, it will not adjust columns/rows accordingly, you have to use FormulaR1C1.

using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Formula");

// Starting = is optional
sheet.Cell("A5").FormulaA1 = "=COUNTA(A2:A4)";

// Total column
sheet.Range("D2:D4").FormulaR1C1 = "RC[-2]*RC[-1]";
Assert.That(sheet.Cell("D2").FormulaR1C1, Is.EqualTo("RC[-2]*RC[-1]"));
Assert.That(sheet.Cell("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)
// Cell.Value has a performance hit because it will calculate formulas.
sheet.Range("F2:F4").FormulaR1C1 = "IF(ISBLANK(RC[-1]),RC[-2],RC[-2]*(1-RC[-1]))";
Assert.That(sheet.Cell("F2").CachedValue.ToString(), Is.Empty);
workbook.RecalculateAllFormulas();
Assert.That(sheet.Cell("F2").CachedValue.ToString(), Is.Not.Empty);

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

workbook.RecalculateAllFormulas();
sheet.Range("H2:H5").FormulaR1C1 = "RC[-2]*(1-R5C7)"; // R5C7 is G5

// Recalculate all dirty formulas before saving
workbook.SaveAs("file.xlsx", new SaveOptions() {EvaluateFormulasBeforeSaving = true});

Support

• Supported Functions
• Graphical representation of function evaluation

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 workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Validation");

var validation = sheet.Cell("C7").CreateDataValidation();
// ATTN: The list must start and end with a double quote!
validation.List("\"Apples,Oranges,Lemons\"");

// Also possible to load from another sheet:
validation.List("OtherSheet!A1:A4");

validation.ErrorStyle = XLErrorStyle.Stop;
validation.ErrorTitle = "Invalid Selection";
validation.ErrorMessage = "We only have those available :(";
validation.ShowErrorMessage = true;

validation.InputTitle = "Choose your juice";
validation.InputMessage = "Apples, oranges or lemons?";
validation.ShowInputMessage = true;

validation.IgnoreBlanks = true;

Number & DateTime validation

using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("intsAndSuch");

// Integer/Decimal validation
var validation = sheet.Cell("A2").CreateDataValidation();
validation.WholeNumber.Between(1, 5);
// validation.Decimal.Between(1, 5);

// DateTime validation
var dateTimeValidation = sheet.Cell("B2").CreateDataValidation();
dateTimeValidation.Date.GreaterThan(DateTime.Now.Date);

// ATTN: While CreateDataValidation().Time does exist,
//       it was crashing for me when opening the Excel