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

Free Support Forum - aspose.com

Value Not Correct When Data Changes

Hi Support,


I have encountered values read by ASPOSE.CELL is not correct When I made some data changes.

- Delete cell value from ‘Working!A2:DR2’
- Recalculate
- Found that a lot of cells not correct compare to EXCEL

Attached is the sample file for your assessment.


Hi,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v8.4.2.5
and see if it fixes your issue.

If your problem still occurs, then please provide us your sample code replicating this issue with the latest version and also provide us a screenshot that highlights the problematic cells so that we could investigate this issue further at our end.

We will look into it and update you asap.

Hi Support,


Your new version v8.4.2.5 is no better than v8.4.2 (offcially release).
As I have not even change the data, the value being read is different from EXCEL when open.

Best regards
Henry Lim

Hi Henry,

Thanks for your feedback and using Aspose.Cells.

Please provide us your sample code and screenshot showing the cells with wrong calculated values.

I have generated the attached pdf with the latest version after recalculating the workbook. Please download it and see if you see any problematic cells, then highlight those cells in a screenshot.

C#


string filePath = @“D:\Downloads\ReadValueNotCorrect.xlsx”;


Workbook workbook = new Workbook(filePath);


workbook.CalculateFormula();


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(filePath + “.out.pdf”, opts);

Hi Faiz,


It’s works fine if just open and read.
You need to delete the cells value in from Working!A2 to Working!DR2
Then you try recalculate again
You will find that a lot of cells not correct compare to EXCEL

Best Regards
Henry Lim

Hi Henry,

Thanks for your explanation and using Aspose.Cells.

Please let me know the code you are using to delete the cell values from A2:DR2. I have tested it with the following code after deleting the contents of this range but it generated the correct results. I have attached the generated pdf for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\ReadValueNotCorrect.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[“Working”];


//Delete values of A2:DR2

CellArea area = CellArea.CreateCellArea(“A2”, “DR2”);

worksheet.Cells.ClearRange(area);


workbook.CalculateFormula();


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(filePath + “.out.pdf”, opts);


Hi faiz,


I use a for loop to remove the values.
I did not use your sample code as it remove the formats. correct?
I still need the cell formating to remain as it is.

For c As Integer = ca.StartColumn To ca.EndColumn
wksht.Cells(ca.StartRow, c).Value = DBNull.Value
Next

Best Regards
Henry Lim

Hi Faiz,


I use your code to delete the cells data but the result is still the same (many errors)
The errors appear in column (EI,EN,ES,ET). Almost all rows are not correct.

Best Regards
Henry Lim

Hi Henry,

Thanks for your posting and using Aspose.Cells.

I am unable to spot any problem with those columns, the results exactly match with Aspose.Cells generated results.

I have attached the screenshots showing the results of excel and output pdf which was generated after calculation.

Could you please highlight the cells with wrong calculated values in a screenshot so that we could easily spot the problem and fix it. Thanks for your cooperation.

Hi faiz,


I assume you have open the file to check the value.
Yes, The result look the same/correct when you do this.

but if you compare the result generate via EXCEL and ASPOSE.
the result is different when calculate at run time.

the screenshot I have attached will show you the difference in value.
The green table is the codes I have written to compare EXCEL vs ASPOSE when generate at run-time.
The cell that has text will have the description discrepancy.

Hope you guy can solve this bug as it is critical as it will run into endless loop based on our logic.

Best Regards
Henry Lim

Hi Fiaiz,


I recreated a new sample file for you to investigate.

Best Regards
Henry Lim
hrbusiness:
Hi Fiaiz,

I recreated a new sample file for you to investigate.

Best Regards
Henry Lim
Hi Henry,

Thanks for your posting and using Aspose.Cells.

If you check your screenshot, you will see Aspose value matches with Microsoft Excel but your green colored Excel value does not match with Microsoft Excel.

For example see cell EF1

Its Microsoft Excel value is

TTC-Mean

76,197
vs
51,756

And your green color screenshot shows, its Aspose value is same i.e

TTC-Mean

76,197
vs
51,756

However, its Excel value is wrong i.e

TTC-Mean

76,156
vs
51,756

Please compare others values. Aspose values matches with Microsoft Excel values but your green colored Excel values do not match with Microsoft Excel values.

I have attached the screenshot for your reference.

Hi Faiz,


Sorry, the screen shot does not remove data yet but if you repeat steps you will see the errors.
I’ve reattached the correct screenshot.
I not so concern on cell EF1 as porperly some ‘breakline’ issue.
Please focus on column EH, EI, ES.

You need to delete the cells value in from Working!A2 to Working!DR2
Then you try recalculate again
You will find that a lot of cells not correct compare to EXCEL

Best Regards
Henry


Hi Henry,

Thanks for your posting and using Aspose.Cells.

I have looked into this issue further and tested this issue with the following sample code using the latest version: Aspose.Cells
for .NET v8.4.2.5
and found the latest version is calculating the cells values correctly.

Either, you are not using latest version or there is some error in your code. Please provide us your sample console application project replicating this issue with the latest version so that we could investigate and fix this issue.

I have attached the output pdf generated with the code and screenshot comparing Excel and PDF values for a reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\ReadValueNotCorrect2.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[“Working”];


//Delete values of A2:DR2

CellArea area = CellArea.CreateCellArea(“A2”, “DR2”);


for (int c = area.StartColumn; c <= area.EndColumn; c++)

{

worksheet.Cells[area.StartRow, c].Value = DBNull.Value;

}


workbook.CalculateFormula();


PdfSaveOptions opts = new PdfSaveOptions();

opts.OnePagePerSheet = true;


workbook.Save(filePath + “.out.pdf”, opts);

Hi faiz,


I’ve tried your version also have errors.
I have attached 2 file for your checking.
basically “ReadValueNotCorrect2_1before.xlsx” is the file before code run.
“ReadValueNotCorrect2_2after.xlsx” is after running the codes (delete data in row 2).

Below is the code I written to compare values with EXCEL cell by cell.

Best Regards
Henry Lim

Public Sub ReadByASPOSE()
File.Copy(Me.Template, Me.TmpFullPath, True)

Dim XLSCom As New XLSCom_ASPOSE
Dim xlsOpts As LoadOptions = New LoadOptions(LoadFormat.Xlsx)
xlsOpts.MemorySetting = MemorySetting.MemoryPreference

Dim wkbk As Workbook

wkbk = New Workbook(Me.TmpFullPath, xlsOpts)
wkbk.Settings.CreateCalcChain = False

Dim wksht As Worksheet
wksht = wkbk.Worksheets(CInt(Me.SheetIndex) - 1)

'Add Code Here
Me.AddCodes_ASPOSE(wksht)
wkbk.Save(Me.TmpFullPath)

wksht.CalculateFormula(False, True, Nothing)

Me.dt_ASPOSE = New DataTable

For c As Integer = ColChkNumSTART To Me.ColChkNumEND
CreateColumnInDataTable_NoEnum(Me.dt_ASPOSE, “Col_” & c, “String”)
Next

For r As Long = Me.RowChkNumSTART To Me.RowChkNumEND
Dim dr As DataRow
dr = Me.dt_ASPOSE.NewRow
Me.dt_ASPOSE.Rows.Add(dr)
dr = Nothing
Next

Dim ic As Integer
Dim ir As Integer

ic = 0
ir = 0
For c As Integer = ColChkNumSTART To Me.ColChkNumEND
ir = 0
For r As Long = Me.RowChkNumSTART To Me.RowChkNumEND
Me.dt_ASPOSE.Rows(ir).Item(ic) = wksht.Cells(r - 1, c - 1).Value
ir += 1
Next
ic += 1
Next

wksht = Nothing
wkbk = Nothing
xlsopts = Nothing
XLSCom = Nothing
End Sub

Private Sub AddCodes_ASPOSE(ByRef wksht As Worksheet)
'Clear value
Dim ca As CellArea = CellArea.CreateCellArea(“A2”, “DR2”)
wksht.Cells.ClearRange(ca)
ca = Nothing
End Sub

Hi,

Thanks for your posting and using Aspose.Cells.

I am unable to run your code because it is not runnable and gives compile time errors. I have attached the screenshot showing the errors.

Please provide us your runnable sample console application project that could help us replicate this issue at our end.

Please also download and try the latest version: Aspose.Cells
for .NET v8.4.2.7
and see if it makes any difference.

hi Fiaz,


I have compress my test aspose.cell whole project solution.

Best regards
Henry

Hi Henry,

Thanks for your sample web application project and using Aspose.Cells.

We were able to observe the differences of Aspose.Cells and Excel values after executing the TestPositionReport.aspx page. However, this problem might be related to your excel component that reads the values and not related to Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43724 - Values are not correct when data changes

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.5.0.1

We have fixed your issue “CELLSNET-43724” now.

Let us know your feedback.

Thank you.

The issues you have found earlier (filed as CELLSNET-43724) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.