Using Named Range name in formula first and then writing the Named range name causes "Invalid Name Error"

Aspose.Cells Product Family
1

Hi,
The usage of named range in a cell is not valid until and unless the named cell is written first, in other words, if we use the named cell name in a formula first without writing the named cell first and then write the named cell we end up in “Invalid Name Error” in excel.
To better understand this issue I am attaching the file which got generated by below sample code, and even attaching the file that got generated, as you can see B2 has “Invalid Name Error”.
Is this is an known issue or is there any workaround for fixing the “Invalid Name Error”, as in our case we would some times write the formula using named cell and then set the name some where else, and we end up with “Invalid Name Error”.

static void worksheetEvalutionException() throws Exception
{
InputStream templateAsStream = null;
try {
Workbook workbook = new Workbook(LoadFormat.AUTO);
Worksheet worksheet = workbook.getWorksheets().get(0);

/*
* This works fine
* 1. Creating named cell
* 2. Using it
/
Range r = worksheet.getCells().createRange(“A1”);
r.setName("_cell1_A1");
Cell cell = worksheet.getCells().get(“A1”);
cell.putValue(11); // setting 11 in name1
cell = worksheet.getCells().get(“B1”);
cell.setFormula("_cell1_A1");

/
* This does not work
* We end up in “Invalid Name Error” in Cell B2
* 1. First writing/using the name in the cell
* 2. Creating the named cell which is used above in step.1
*/
cell = worksheet.getCells().get(“B2”);
cell.setFormula("_cell1_A2");
cell = worksheet.getCells().get(“A2”);
cell.putValue(12);
r = worksheet.getCells().createRange(“A2”);
r.setName("_cell1_A2");

workbook.save(“testAsposeIssue.xls”);
}
finally {
if (templateAsStream != null) {
templateAsStream.close();
}
}
}

2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Please download and use the latest version: Aspose.Cells
for Java v7.3.4.3. It should work fine at your end.

I have tested your issue with the latest version with the following code and the generated output file did not show any error in the Ms-Excel file.

I have attached the output file and screenshot for your reference.

Java


Workbook workbook = new Workbook(LoadFormat.AUTO);

Worksheet worksheet = workbook.getWorksheets().get(0);


/*
* This works fine
* 1. Creating named cell
* 2. Using it
/

Range r = worksheet.getCells().createRange(“A1”);

r.setName(“_cell1_A1”);

Cell cell = worksheet.getCells().get(“A1”);

cell.putValue(11); // setting 11 in name1

cell = worksheet.getCells().get(“B1”);

cell.setFormula(“_cell1_A1”);


/
* This does not work
* We end up in “Invalid Name Error” in Cell B2
* 1. First writing/using the name in the cell
* 2. Creating the named cell which is used above in step.1
*/

cell = worksheet.getCells().get(“B2”);

cell.setFormula(“_cell1_A2”);

cell = worksheet.getCells().get(“A2”);

cell.putValue(12);

r = worksheet.getCells().createRange(“A2”);

r.setName(“_cell1_A2”);


workbook.save(“output.xls”);

Screenshot:

3

The version we are using is 7.1.2, and the issue is not replicated with the latest version of Aspose (7.3.4.3), thanks for the update and fix.

4
Hi,

Good to know that your issue is resolved by the latest version/fix (shared by Shakeel Faiz).
Feel free to contact us any time if you need further help or have any other issue. We will be happy to assist you here.

Thank you.