We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

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

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"); 
    }
}

@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.

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();
		}
	}
}

@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.

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

@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.

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