Defined Names

Defined names refer to names given to constants, tables, cell ranges, or formulas so that you can refer to them in a formula without making it too complex to understand. The defined names are especially useful in complex calculations, such as calculating taxes for a whole financial year, where you will have difficulty finding and understanding the cells having different investments, taxable incomes, etc.

DsExcel supports defined names with the help of getNames and setNames methods in IWorksheet interfaces and getName and setName methods in ITable interface.

Name a Table

Name a table by using getName method of ITable interface. The scope of the table name is workbook by default, as tables are created in workbook scope only. This name appears in Excel's Name Manager, as shown below.

Refer to the following example code to name the table:

// Name a table.
ITable table = worksheet.getTables().add(worksheet.getRange("A1:F7"), true);
table.setName("Master Table");

Name a Cell Range, Formula, and Constant

Name a cell range, formula, and constant using the getNames method with Workbook and Worksheet objects. This method adds an IName object storing the name and referenced cell, formula, or constant. The name added to a workbook object is stored in the workbook scope, while the name added to a worksheet object is saved in the worksheet scope. It appears in Excel¨s Name Manager, as shown below.

Refer to the following example code to name a cell range, a formula, and a constant in workbook scope:

// Name a range in workbook scope.
workbook.getNames().add("Range1", "Data Sheet!A1:C7");

// Name formula in workbook scope.
workbook.getNames().add("Sum", "=SUM(F2:F7)");

// Name a constant in workbook scope.
workbook.getNames().add("pi", "3.14");

// Name a cell in workbook scope.
workbook.getNames().add("Weight", "E1");

Refer to the following example code to name a cell range, a formula, and a constant in worksheet scope:

// Name a range in worksheet scope.
workbook.getWorksheets().get(worksheet.getIndex()).getNames().add("Range2", "Data Sheet!D1:F7");

// Name formula in worksheet scope.
workbook.getWorksheets().get(worksheet.getIndex()).getNames().add("Count", "=COUNT(E2:E7)");

// Name a constant in worksheet scope.
workbook.getWorksheets().get(worksheet.getIndex()).getNames().add("Euler_Number", "2.71");

// Name a cell in worksheet scope.
workbook.getWorksheets().get(worksheet.getIndex()).getNames().add("Height", "F1");

Show or Hide Defined Names

DsExcel Java allows you to control whether a defined name is visible in Excel's Name Manager by using the setVisible method of the IName interface. This method is supported for both workbook-scoped and worksheet-scoped defined names.

Set the setVisible method to false to hide a defined name. Hidden defined names remain available to formulas and workbook logic, but they do not appear in Excel's Name Manager. Newly created defined names are visible by default.

Refer to the following example code to hide workbook-scoped and worksheet-scoped defined names.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Sheet1");

// Add two workbook-scoped defined names and set one of them to be invisible.
IName workbookName = workbook.getNames().add("HiddenWorkbookName", "=Sheet1!$A$1");
workbookName.setVisible(false);
IName visibleWorkbookName = workbook.getNames().add("VisibleWorkbookName", "=Sheet1!$B$1");
visibleWorkbookName.setVisible(true);

// Add two worksheet-scoped defined names and set one of them to be invisible.
IName worksheetName = worksheet.getNames().add("HiddenWorksheetName", "=Sheet1!$C$1");
worksheetName.setVisible(false);
IName visibleWorksheetName = worksheet.getNames().add("VisibleWorksheetName", "=Sheet1!$D$1");
visibleWorksheetName.setVisible(true);

// Save the workbook.
workbook.save("HiddenDefinedNames.xlsx");

The Name Manager will appear as shown in the image.