Referred area collection - getEndCol and getEndRow

Aspose.Cells Product Family
1

Hi,

I applied the formula =C21+D21 in E21 .

Both getEndCol() and getEndRow() on the referred area(C21) returns 0 .

But getStartCol() and getStartRow() works fine returning 2 and 20 respectively.


Can you explain me the above case ?




2

Hi,

Thanks for your posting and using Aspose.Cells.

Please explain your issue in more detail. Also let us know which version of Aspose.Cells you are using. Please illustrate your issue with some sample code and sample excel file.

We will look into your issue and help you asap. Thanks for your cooperation.

3
Hi,

I ran the following piece of code for the cells.
if(cell.isFormula()){
String formula = cell.getFormula();
ReferredAreaCollection collection=cell.getPrecedents();
for (Iterator iterator = collection.iterator(); iterator.hasNext();)
{
ReferredArea ra= iterator.next();
System.out.println(ra.getSheetName()+" "+ra.getStartRow()+" "+ra.getStartColumn()+" "+ra.getEndRow()+" "+ra.getEndColumn());
}
}
Output:
Sheet1 3 3 0 0
Sheet1 3 4 0 0
Sheet1 3 5 0 0

The output is shown for first formula cell (G4) alone.
I expected the output to be ,

Output:
Sheet1 3 3 3 3
Sheet1 3 4 3 4
Sheet1 3 5 3 5

Also in Sheet 2 I have the same data as a table . In that case I get the expected output.

Output:
Sheet2 3 3 3 3
Sheet2 3 4 3 4
Sheet2 3 5 3 5

I have attached the sample file.

Thanks in advance.



4

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and found ReferredArea.getEndRow() and ReferredArea.getEndColumn() returns 0 and does not match the expected output as you have given it. However, the same thing works fine with the cell G4 inside the Table in second sheet.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41247 - ReferredArea.getEndRow() and ReferredArea.getEndColumn() returns 0.

We have tested this issue with the following sample code and also shown the console output of the code for a reference.

Java

String filePath = “F:\Shak-Data-RW\Downloads\FormulaCase.xlsx”;


Workbook workbook = new Workbook(filePath);


System.out.println(“It does not work fine with G4 inside the first sheet.”);


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


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


if (cell.isFormula()) {

String formula = cell.getFormula();

ReferredAreaCollection collection = cell.getPrecedents();

for (Iterator iterator = collection.iterator(); iterator

.hasNext():wink: {

ReferredArea ra = iterator.next();

System.out.println(ra.getSheetName() + " " + ra.getStartRow()

+ " " + ra.getStartColumn() + " " + ra.getEndRow()

+ " " + ra.getEndColumn());

}//for

}//if



//But it works fine with G4 inside the Table in the second sheet.

System.out.println("-------------------------------------------------------");

System.out.println(“But it works fine with G4 inside the Table in the second sheet.”);

worksheet = workbook.getWorksheets().get(1);

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


if (cell.isFormula()) {

String formula = cell.getFormula();

ReferredAreaCollection collection = cell.getPrecedents();

for (Iterator iterator = collection.iterator(); iterator

.hasNext():wink: {

ReferredArea ra = iterator.next();

System.out.println(ra.getSheetName() + " " + ra.getStartRow()

+ " " + ra.getStartColumn() + " " + ra.getEndRow()

+ " " + ra.getEndColumn());

}//for

}//if




Console Output:
It does not work fine with G4 inside the first sheet.
Sheet1 3 3 0 0
Sheet1 3 4 0 0
Sheet1 3 5 0 0
-------------------------------------------------------
But it works fine with G4 inside the Table in the second sheet.
Sheet2 3 3 3 3
Sheet2 3 4 3 4
Sheet2 3 5 3 5

5

Thanks for the reply. I am looking forward for the issue fix.

6

Hi,

Thanks for your posting and using Aspose.Cells

Hopefully, your issue will be fixed in a week or two. Once, the fix is available for you or we have some other update for you, we will let you know asap.

7

Hi,

Thanks for using Aspose.Cells.

We have looked into this issue further and found that it is a correct output. If ReferredArea.isArea() is false, EndColumn and EndRow are useless.