Value Not Correct When Data Changes

hrbusiness · June 8, 2015, 10:54pm

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.

shakeel.faiz · June 8, 2015, 11:07pm

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.

hrbusiness · June 8, 2015, 11:42pm

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

shakeel.faiz · June 9, 2015, 12:41am

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

hrbusiness · June 9, 2015, 1:30am

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

shakeel.faiz · June 9, 2015, 3:04am

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

hrbusiness · June 9, 2015, 4:09am

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

Henry Lim

hrbusiness · June 9, 2015, 4:51am

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

shakeel.faiz · June 9, 2015, 8:54am

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.

hrbusiness · June 9, 2015, 9:49pm

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

hrbusiness · June 9, 2015, 10:29pm

Hi Fiaiz,

I recreated a new sample file for you to investigate.

Best Regards

Henry Lim

shakeel.faiz · June 10, 2015, 12:01pm

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.

hrbusiness · June 10, 2015, 8:28pm

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

shakeel.faiz · June 11, 2015, 8:11pm

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

hrbusiness · June 15, 2015, 1:58am

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”)

Dim dr As DataRow

dr = Me.dt_ASPOSE.NewRow

Me.dt_ASPOSE.Rows.Add(dr)

dr = Nothing

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

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

shakeel.faiz · June 15, 2015, 10:42pm

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.

hrbusiness · June 17, 2015, 9:58pm

hi Fiaz,

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

Best regards

Henry

shakeel.faiz · June 18, 2015, 11:44am

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

amjad.sahi · June 26, 2015, 5:40am

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.

aspose.notifier · July 16, 2015, 5:07am

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.