getPrecedents for range functions

getPrecedents for range functions

Posted by: asaf.shechter on 14 March 2023, 10:46 am EST

• 

  asaf.shechter

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 14 March 2023, 10:46 am EST - Updated 14 March 2023, 10:51 am EST

  Hello,
  
  I am trying to use the getPrecedents method for a cell that has a range in formula :

  

  and get only on cell back :

  

  there is another case when you have indirect function you also won’t get precedents at all , INDIRECT(“B2”) for example won’t return anything using the getPrecedents method…

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 16 March 2023, 1:54 pm EST

  Hi,

  This seems like a bug. So, we have escalated this issue to the development team. Will get back to you once we have any update from them.[Internal Tracking Id - DOCXLS-7791]

  Best Regards,
  
  Nitin

• 

  asaf.shechter

  • Post Options:
  • Link
    Copy

  Posted 27 March 2023, 7:23 am EST

  Hi,
  
  meanwhile is there a possibility to bypass this bug?

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 March 2023, 7:37 am EST - Updated 28 March 2023, 7:42 am EST

  Hi,

  Apologize for the inconvenience.
  
  As per the development team. This is by design. The parameter of the Indirect function is text and not a reference. We can only do static analysis to formula when using Precedents method such as =SUM(A1:B2).
  
  You can check with MS Excel (see Image)
  
  

  

  894×226 39.4 KB

  
  
  Apologize, but no workaround is possible to achieve this.

  Regards,
  
  Nitin

• 

  asaf.shechter

  • Post Options:
  • Link
    Copy

  Posted 28 March 2023, 11:31 am EST

  Ok good to know,
  
  and in case I want to get value of spilled formula in server side like UNIQUE(A1:A10) ,
  
  which method should I use?
  
  when using the getValue() method we get wrong result. (tried workbook.calculate() and still got result 0)
  
  

  

  

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 31 March 2023, 6:16 am EST

  Hi,

  Apologize for the delay.
  
  We are unable to replicate this issue at our end. Could you please provide a small Java sample to replicate this issue? So, that we can investigate and assist you accordingly.

  Best Regards,
  
  Nitin

• 

  asaf.shechter

  • Post Options:
  • Link
    Copy

  Posted 3 April 2023, 6:41 am EST - Updated 3 April 2023, 6:47 am EST

  Hi ,
  
  as you can see at the attached images ,
  
  the result is spilled ,
  
  but when trying to get the value in server side I get only first value ,
  
  also excel file with the relevant example is attached.
  
  

  

  1681×839 31.8 KB

  

  1287×625 26.4 KB

  uniqueFunc.zip

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 4 April 2023, 7:04 am EST

  Hi,

  Apologize for the delay.
  
  We are discussing this requirement with the development team. Will get back to you once we have any update from them.[Internal tracking Id -DOCXLS-7903]

  Best Regards,
  
  Nitin

• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 6 April 2023, 12:14 am EST

  Hi,

  You can get all spilled values through below code:

  Workbook workbook = new Workbook();
  workbook.open("uniqueFunc.xlsx");
  Object[][] array =(Object[][])workbook.getWorksheets().get(0).getRange("B1#").getValue();
  for (Object[] row : array) {
      for (Object cell : row) {
          System.out.println(cell);
  	}
  }

  Please refer the attached sample for the same: GCExcelSpill.zip

  Best Regards,
  
  Nitin

• 

  asaf.shechter

  • Post Options:
  • Link
    Copy

  Posted 20 April 2023, 5:30 am EST

  Hi Nitin,
  
  your solution seems to do the trick for us,
  
  that’s what we were looking for ,
  
  Thanks.