Offset function behaves differently in excel

Aspose.Cells Product Family
1

Hi,

I’m not sure if it’s a bug in Aspose.Cells or Microsoft Excel but I noticed different behavior for following scenario:
  1. Define global named range in excel using excel’s offset function as =OFFSET(Sheet1!$A$1,0,0,2,2).
  2. Verify that this named range points to 2x2 range (2 rows and 2 columns) and identical 2x2 range is returned by Aspose.Cells using following code: workbook.getWorksheets().getNames().get(“offsetrange”).getRange()
  3. Merge cells A1, A2, A3 into one
  4. After that Aspose.Cells still returns the same 2x2 range but Excel is now returning 3x2 range (3 rows and 2 columns)
You can see it on attached image, thanks,
Piotr
2

Hi Piotr,


Thank you for contacting Aspose support.

We have evaluated the presented scenario by manually creating the spreadsheets in Excel (as per your directions) and tried to get the range information using Aspose.Cells for Java 8.7.1.4. We have noticed that after merging the cells A1, A2 & A3 in Excel, the Refers to formula does not change however, the cell selection for the range changes (see attached snapshots). Moreover, while retrieving the range after merging cells, the Aspose.Cells for Java API seems to return the information as per Refers to of the named range.

That said, in order to look further into this scenario, we have logged an investigative ticket CELLSJAVA-41791 in our database. Please spare us some time to properly analyze this case and get back with updates in this regard.
3

Hi,


We have evaluated your issue further. Well, it is not a bug of our component. In fact, it is because MS Excel cannot show part of the merged area in UI. For your template file, MS Excel still takes the calculated result of your Name “nyRange” as 2x2 range. You can input formula “=COUNTBLANK(nyRange)” to any cell out of the range, then you can see Excel give the result as 4 and not 6. So the resultant range our component gave is just correct.

Thanks for your understanding!