Defined Names

Defined names are very useful in Excel. It's possible to give a name to a cell, a range or a formula and use that name elsewhere.

//creating workbook with 2 worksheets
var workbook = new Workbook();
var exchangeRatesSheet = new Sheet("Exchange Rates");
workbook.Sheets.AddSheet(exchangeRatesSheet);
var pricesSheet = new Sheet("Prices");
workbook.Sheets.AddSheet(pricesSheet);

//defining currencies
exchangeRatesSheet["A1"].Value = "EUR";
exchangeRatesSheet["A2"].Value = "GBP";
exchangeRatesSheet["A3"].Value = "USD";

//defining exchange rates
exchangeRatesSheet["B1"].Value = 1.0;
exchangeRatesSheet["B2"].Value = 0.7;
exchangeRatesSheet["B3"].Value = 1.43;

//defining VAT percentage
exchangeRatesSheet["E1"].Value = "VAT";
exchangeRatesSheet["F1"].Value = 0.17;

//set named cell for VAT
workbook.DefinedNames.AddCell("VAT", Cell.Parse("F1"), exchangeRatesSheet);

//set named range for exchange rates
Range exchangeRatesRange = Range.Parse("A1:B3");
workbook.DefinedNames.AddRange("ExchangeRate", exchangeRatesRange, exchangeRatesSheet);

//defining headers in Prices sheet
pricesSheet["A1"].Value = "Product";
pricesSheet["B1"].Value = "Price";
pricesSheet["C1"].Value = "EUR";
pricesSheet["D1"].Value = "USD";
pricesSheet["E1"].Value = "GBP";

//setting headers style
var labelsRange = pricesSheet["A1", "E1"];
CellStyle headerStyle = new CellStyle();
headerStyle.Font.Bold = true;
headerStyle.Alignment.HAlign = HorizontalAlignment.Center;
labelsRange.SetStyle(headerStyle);

//defining products
pricesSheet["A2"].Value = "Xlio";
pricesSheet["B2"].Value = 50;
pricesSheet["A3"].Value = "Azzura";
pricesSheet["B3"].Value = 65;

//set formula for calculating prices in different currencies
string formulaString = "=LOOKUP(C$1, ExchangeRate)*$B2*(1+VAT)";
var sheetRange = pricesSheet["C2", "E3"];
sheetRange.SetFormula(formulaString);

workbook.Save(@"DefinedNames.xlsx");

Example shows creation of 2 sheets and named cells, ranges and formulas inside of them.

Named Cells

Named cell represents a cell that has a name (for example coefficient) instead of location (for example B1). Next snippet shows creation of sheet inside a workbook with a named cell.

var workbook = new Workbook();
var sheet = new Sheet("Some Sheet");
workbook.Sheets.AddSheet(sheet);
Cell coefCell = Cell.Parse("B1");
sheet[coefCell].Value = 2;
workbook.DefinedNames.AddCell("coefficient", coefCell, sheet);
workbook.Save(@"W:/Output.xlsx");

Named cell is created with AddCell() method which have 3 parameters:

Name - name of the named cell
Cell - instance of cell class, which defines location of the cell
Sheet - instance of the sheet class, where named cell belongs

Named Ranges

Named range is a range with a name. It is useful in formulas, where instead of SUM(B2:F7) you can write for example QuarterValues. AddRange() method has similar parameters as AddCell(), just instead of Cell, specified Range is used.

Range valuesRange = new Range("A1","A5");
workbook.DefinedNames.AddRange("values", valuesRange, sheet);

Named Formulas

Named formula represents defined formula which can be used on multiple places. AddValue() method is used for creation of named formula. Parameters are name and formula. When you add named formula to defined names inside workbook, you can assign it to a cell.

workbook.DefinedNames.AddValue("definedFormula", "SUM(values)*coefficient");
sheet["A6"].Formula = "=definedFormula";