Getting exponential values after applying checkcell

Aspose.Cells Product Family
1

Hi Team,

We are trying to get the cell value using the following code.

Cell cell =
sectionRange.getWorksheet().getCells().checkCell(absoluteRowId,
absoluteColumnId);

//absoluteRowId=29;

//absoluteColumnId=5;

if (cell != null)

{

// Based on the type of value expected the

// same is being returned.

if (CellValueType.NATIVE.equals(valueType))

{

returnData = cell.getValue();

}

else if (CellValueType.FORMATTED.equals(valueType))

{

returnData = cell.getStringValue();

}

}

We are getting -7.10543E-15 value in regionalOrderSumm32___nationalVolatileSumm.noMatchAdjustmentTotal cell in excel sheet after applying the checkcell method on F30 th cell of allocReport2 sheet.But the value in F30 cell is 0.The F30 th cell is formula cell{(=SUM(F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28,F29)}.

So we think it is a problem with check cell method in Aspose 8.3.4. Its working fine in In Old Aspose 2.3.1

PFA the excel which we used as Input. Refer the cell names in the attached excel.

Can you provide me the solutions to resolve it?

2

Hi,


Thanks for the template file and sample code.

Please try to call Workbook.calculateFormula() method before using your code segment, it would work fine as I tested.

Let us know your feedback.

Thank you.
3
Hi,

We already calculated the formulae in F30 th cell of allocReport2 sheet using Workbook.calculateFormula() method.

volatileMap.put("noMatchAdjustmentTotal", getDouble(cellSet, "salesNoMatch.noMatchAdjustment"));

Now we are trying to copy the value from salesNoMatch.noMatchAdjustment in allocReport2 sheet to noMatchAdjustmentTotal in regionalOrderSumm32 sheet using above line of code.

So is it needed to recalculate the cell again?

4

Hi,


Well, if you are inserting/updating value(s) into the concerned cells, then you must call Workbook.calculateFormula() before copying values between worksheets. Otherwise, you may not call Workbook.calculateFormula() method again.

Thank you.
5

Hi,

Is this scenario is applicable for Aspose 8.3.4?Because this scenario is working fine in Aspose 2.3.4.
6

Hi,


Well, yes, this scenario (as mentioned in my previous post) is applicable to newer versions of the product.

Thank you.
7
Hi,

Thanks for the response.Before inserting itself,we are getting the value as -7.10543E-15.While fetching data from the first sheet itself,We are getting the value as -7.10543E-15 instead of 0.

How can we fetch the values as it is in sheet1?
8

Hi,


Well, as requested earlier, you have to call Workbook.calculateFormula() method first to retrieve the value(s) from the formula cell(s).

Thank you.


9

Hi,


We used the below code with calculateFormula()to get the value from the excel sheet.But we are not able to fetch the correct value.
private Range sectionRange;
private Object getValueByIndex(int attributeIndex, CellValueType valueType)
{
if (!isValidRow())
{
// VEH00059535 - Lates Aspose changes - start
throw new IllegalArgumentException(" The row pointer [" + rowIndex + “] for the page Id + [”
+ cellSetMetaData.getContainedPageId() + “] with Range Name: [” + sectionRange.getName()
+ “] is invalid.”);
// VEH00059535 - Lates Aspose changes - end
}

if (attributeIndex > getAttributeCount())
{
// VEH00059535 - Lates Aspose changes - start
throw new IllegalArgumentException(" The attributeIndex [" + attributeIndex + "] for the page Id + " + “[”
+ cellSetMetaData.getContainedPageId() + “] with Range Name: [” + sectionRange.getName()
+ “] is invalid.”);
// VEH00059535 - Lates Aspose changes - end
}

Object returnData = null;

// The row and the column indexes that are decided based on orientation.
int absoluteRowId = getAbsoluteRowId(attributeIndex);
int absoluteColumnId = getAbsoluteColumnId(attributeIndex);
// VEH00059535 - Lates Aspose changes - start
sectionRange.getWorksheet().getWorkbook().calculateFormula();
Cell cell = sectionRange.getWorksheet().getCells().checkCell(absoluteRowId, absoluteColumnId);
/Style style = cell.getStyle();
style.setNumber(1);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
cell.setStyle(style, flag);/

//Cell cell = sectionRange.getCellOrNull(absoluteRowId - sectionRange.getFirstRow(), absoluteColumnId - sectionRange.getFirstColumn());
// VEH00059535 - Lates Aspose changes - end

if (cell != null)
{
// Based on the type of value expected the
// same is being returned.
if (CellValueType.NATIVE.equals(valueType))
{
returnData = cell.getValue();
}
else if (CellValueType.FORMATTED.equals(valueType))
{
returnData = cell.getStringValue();
}
}

// convert the Null to the blank string otherwise NULL will be displayed
// on the screen.
if (returnData == null)
{
returnData = “”;
}

return returnData;
}

10

Hi,


Thanks for the code segment and some details.

I am afraid, I could not evaluate your issue properly as I am not sure about some of the object variables/ pointers’ values in your code segment. So, if you still find the issue with v8.6.3.x, kindly provide us simple JAVA program (runnable), we will check it soon. Also you may use static values for your objects/variables, it will help us to evaluate your issue precisely.

Thank you.
11

Hi,

We are using the following code to fetch the value from allocReport2 sheet in workbook which i attached earlier. After applying the following line in code,we are not getting proper value.
sectionRange.getWorksheet().getWorkbook().calculateFormula();

The returnData value should be 0.But we are getting -7.10543E-15.

Please find the below code and i have given the variable values also.Try to use the values in the method and let me know are you able to get the proper value?




attributeIndex=4
valueType=CellValueType[FORMATTED]
absoluteRowId=29
absoluteColumnId=5

private Range sectionRange;
private Object getValueByIndex(int attributeIndex, CellValueType valueType)
{

Object returnData = null;

// The row and the column indexes that are decided based on orientation.
int absoluteRowId = getAbsoluteRowId(attributeIndex);
int absoluteColumnId = getAbsoluteColumnId(attributeIndex);
// VEH00059535 - Lates Aspose changes - start
sectionRange.getWorksheet().getWorkbook().calculateFormula();
Cell cell = sectionRange.getWorksheet().getCells().checkCell(absoluteRowId, absoluteColumnId);

if (cell != null)
{
// Based on the type of value expected the
// same is being returned.
if (CellValueType.NATIVE.equals(valueType))
{
returnData = cell.getValue();
}
else if (CellValueType.FORMATTED.equals(valueType))
{
returnData = cell.getStringValue();
}
}

// convert the Null to the blank string otherwise NULL will be displayed
// on the screen.
if (returnData == null)
{
returnData = “”;
}

return returnData;
}

12

Hi,


I have tested your scenario/case using the following two code segments (runnable example codes) but I still could not find the issue, it works absolutely fine with our latest fix/version: Aspose.Cells for Java v8.6.3.4 (please try it). I have simplified your code in a simple JAVA program (runnable) as I am still not able to run your code directly or in a program for I am not sure about some variable objects like sectionRange or some others and their values. I have to provide some static values to them in my example code.
e.g
Sample code:

1)
Workbook workbook = new Workbook(“vly6yPfDR8.xls”);
workbook.calculateFormula();
Cell cell = workbook.getWorksheets().get(“allocReport2”).getCells().checkCell(29,5);

if (cell != null)
{
System.out.println(cell.getValue());
System.out.println(cell.getStringValue());
}

2)
Workbook workbook = new Workbook(“vly6yPfDR8.xls”);
workbook.calculateFormula();
int absoluteRowId=29;
int absoluteColumnId=5;
Object returnData=null;
Object returnData1=null;

Range sectionRange = workbook.getWorksheets().getRangeByName(“allocReport2___salesNoMatch.noMatchAdjustment”);
sectionRange.getWorksheet().getWorkbook().calculateFormula();
Cell cell = sectionRange.getWorksheet().getCells().checkCell(absoluteRowId, absoluteColumnId);

if (cell != null)
{
returnData = cell.getValue();
returnData1 = cell.getStringValue();
}
System.out.println(returnData.toString());
System.out.println(returnData1.toString());


Both gives the following output on Windows console prompt:
0.0
0

Thank you.

13

Hi,


Thanks for the support.Will try this and will update you…
14

Hi,


<span style=“font-size:11.0pt;line-height:115%;
font-family:“Arial”,sans-serif;mso-fareast-font-family:Calibri;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>We ran the code in Aspose 8.6.3 and its giving the value as 0.
<span style=“font-size:11.0pt;line-height:115%;
font-family:“Arial”,sans-serif;mso-fareast-font-family:Calibri;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>
<span style=“font-size:11.0pt;line-height:115%;
font-family:“Arial”,sans-serif;mso-fareast-font-family:Calibri;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”>But in our application We are using Aspose v.8.3.1.We ran the code which was given in previous post,but we are getting the value as -7.10543E-15 instead of 0…Can you please run the code in Aspose v.8.3.1 and provide me the solution for Aspose v.8.3.1.
15

Hi,


It looks like there was an issue with the older version i.e., Aspose.Cells v8.3.1 regarding Aspose.Cells’ formula calculation engine. For your information, we cannot evaluate or fix any issue (if found) in older versions of the product. Moreover, with every new release we continuously enhance the formula calculation engine to further extent, so we recommend you to kindly upgrade to and try our latest version/fix of the product as there is no alternative to figure out your issue.

We are sorry for any inconvenience caused!