Function RANDBETWEEN(bottom, top) value results is different from Excel results in Java

Aspose.Cells Product Family
, , ,
1

The value range of randbetween (bottom, top) function in excel is: bottom < = x < = top, but after Aspose parses this function, the value range becomes: bottom < = x < topSMS-16612.zip (7.9 KB)

  import com.aspose.cells.Workbook
  public class AsposeTest {
    public static void main(String[] args) throws Exception { 
        Workbook workbook = new Workbook("D://test.xlsx");
        workbook.calculateFormula();
        workbook.save("D://test.html"); 
    }
}
2

@xutao,

I did test your scenario/case using our latest version/fix: Aspose.Cells for Java v21.8. It works as expected. I got “0” as calculated value in the output HTML file which is ok/same as per MS Excel (calculated value).

Please try our latest version and let us know if you still find any issue.

3

Thanks for your response, I have tried the latest version Aspose.Cells for Java v21.8, and the problem still exists as follows: in the spreadsheet, multiple cells use the formula = randbetween (0,1), the results in Excel include 0 and 1, which are calculateFormulaed and converted to HTML through Aspose, and all the results are 0
image.png (44.9 KB)
image.png (27.9 KB)

package test;
import com.aspose.cells.Workbook;
public class AsposeBug2 {
	public static void main(String args[]) {
		String designerFile = "D://Aspose//Aspose_Bug//2.xlsx";
		try {
			Workbook workbook = new Workbook(designerFile);
			workbook.calculateFormula();
			workbook.save("D://Aspose//Aspose_Bug//2/2.html");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
4

@xutao,
The screenshot and shared excel file test.xlsx are different because only D4 cell contains this function. Please share the excel file for which you have shared the screenshots.

5

this is the excel file
Excel文件.zip (11.9 KB)

6

@xutao,

Thanks for the file.

After initial testing, I am able to reproduce the issue (using your file) as you mentioned (via screenshots). I found the value range of RANDBETWEEN(bottom, top) function in Excel is different than what is shown in the output HTML file format by Aspose.Cells API. I have logged a ticket with an id “CELLSJAVA-43621” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

7

The issues you have found earlier (filed as CELLSJAVA-43621) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi