Incorrect WEEKNUM() output

Aspose.Cells Product Family
1

Hi,


I’m implementing a proof og concept for a calculation engine driven by a rather complex Excel spread sheet.
I’m getting decimal inconsistency from calculations done by Excel and done by Aspose.Cells WorkBook calculations.
I have done a lot of debugging and have found where the data gets distorted.

It actually seems to by a Aspose function calculation error, but it might relate to Culture issues.

The following outputs ‘2’, but should obviously output 1:

var testBook = new Workbook();
var testSheet = testBook.Worksheets[testBook.Worksheets.Add()];
var testDate = new DateTime(2013, 1, 1);
testSheet.Cells[0, 0].PutValue(testDate);
testSheet.Cells[1, 0].Formula = “=WEEKNUM(A1)”;
testSheet.Cells[1, 0].Calculate(false, null);
Console.WriteLine(“Weeknum for date [” + testDate+ "]: " + testSheet.Cells[1, 0].IntValue);

It seems hte WEEKNUM() function calculates the weeknumber incorrectly.

It’s absolutely necessary that Excel calculations and Aspose.Cells calculation are the same at 6. decimal, at the minimum, so we need this issues solved, for the precision to be satisfying.
We allready use Aspose.Words and would like to continue using your frameworks.

Could you please test and check if this bug is replicatable, and comment on the timeframe for a possible fix?

Thanking in advance
Soeren Praestegaard, EnergiMidt A/S
2

Hi,


Thanks for providing us details and sample code.

After an initial test, I observed the issue with Aspose.Cells formula calculation engine. It prints “2” rather than “1” as the Week number formula/function.
e.g
Sample code:

var testBook = new Workbook();
var testSheet = testBook.Worksheets[testBook.Worksheets.Add()];
var testDate = new DateTime(2013, 1, 1);
testSheet.Cells[0, 0].PutValue(testDate);
testSheet.Cells[1, 0].Formula = “=WEEKNUM(A1)”;
testSheet.Cells[1, 0].Calculate(false, null);
Console.WriteLine(“Weeknum for date [” + testDate+ "]: " + testSheet.Cells[1, 0].IntValue); //2

I have logged a ticket with an id “CELLSNET-42424” for your issue. We will look into it soon.

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

Thank you.
3

Hi Amjad,


Quick reply and issue acknowledge. Thanks a lot.

Is it possible to estimate a hotfix timeframe?
We need to go into production by 1. april 2014 (we’re on a tight schedule).

Thanking in advance
Soeren Praestegaard, EnergiMidt A/S
4

Hi,


Your issue is still pending for analysis. Once we do it we can provide an eta for your issue. Normally, if an issue is not complex, it takes only 3-5 days to fix it, for complex issue, it may take more time (a couple of weeks or even more than that). Hopefully, your issue is not complex and we could sort it out soon.

Thank you.
5

Great. Just what I hoped for.

Hopefully the issue isn’t that complex.

I’ll keep my fingers crossed :slight_smile:

Thanking in advance
Soeren Praestegaard, EnergiMidt A/S
6

Hi,

Thanks for your posting and using Aspose.Cells.

We have fixed this issue.

Please download and try the latest fix: Aspose.Cells for .NET 7.7.2.3 and let us know your feedback.

7

Thanks for the update.

We are stille experiencing inconsistency.

To simplify the error report, I used WEEKNNUM() with one parameter only. But we need to use WEEKNUM() with system:21, so the correct example should be:

var testBook = new Workbook();
var testSheet = testBook.Worksheets[testBook.Worksheets.Add()];
var testDate = new DateTime(2012, 12, 31);
testSheet.Cells[0, 0].PutValue(testDate);
testSheet.Cells[1, 0].Formula = "=WEEKNUM(A1,21)";
testSheet.Cells[1, 0].Calculate(false, null);
Console.WriteLine("Weeknum for date [" + testDate + "]: " + testSheet.Cells[1, 0].IntValue);

Output should be '1' but is '54'. So, the function is fixed for WEEKNUM() with no system parameter, but still fails for system:21 WEEKNUM([DATE],21)

I have attached example sheet.

Thanking in advance
Soeren praestegaard, EnergiMidt A/S

8

Hi,


When I set the formula on A2 cell i.e…, “=WEEKNUM(A1,21)” (after applying your desired DateTime on A1 cell) into MS Excel manually, MS Excel calculates is as
"#NUM!"

Thank you.

9

Hi,

We use Danish MSOffice, and formula notation is a bit different.

Try making a new Excel sheet in your MS Excel, and have a formula calculate the weeknum as =WEEKNUM(A1,21), where A1 contains the date "2012-12-31". Excel then returns '1'.

I have attached example screenshots.
(Danish Excel uses formula name UGE.NR(), and uses ';' instead of ',' as parameter separator)

I continue to get different results for same formula and input, so there is still a bug in the framework.

Thanking in advance
Soeren Praestegaard, EnergiMidt A/S

10

Hi again,

Please consider this material: http://www.rondebruin.nl/win/s8/win001.htm

Especially the passage about "ISO Week Numbers".

It's actually ISO week numbers we need.

Thanking in advance
Soeren Praestegaard, EnergiMidt A/S

11

Hi,


Thanks for providing further details and sample file.

Actually I was using Ms Excel 2007 so, I am not getting the calculated value for the formula. Now I used MS Excel 2010 and in MS Excel 2010, the formula in A2 cell i.e…, “=WEEKNUM(A1,21)” returns “1” (as I pasted the DateTime value “2012-12-31” into A1 cell already) but Aspose.Cells does not calculate it fine. I have reopened your issue “CELLSNET-42424” now. We will look into it soon.

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

Thank you.

12

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for .NET v7.7.2.5 and let us know your feedback.

13

Hi,

The WEEKNUM() function seems to work now. Thanks.

But the new Aspose.Cells release gives me problems in some row deleting logic. Is the binaries, attached above, a dedicated branch for this issue, or are there changes in other parts of the framework in that build?

Thanking in advance
Soeren Praestegaard, EnergiMidt

14

Hi Soeren,

Thank you for the confirmation on the fix for WEEKNUM formula.

Aspose.Cells for .NET 7.7.2.5 (as shared in our previous post) is actually a maintenance release containing existing functionality as it is with a few bug fixes including the one in reference to WEEKNUM formula.

Please note, we take regression issues very seriously therefore if you have encountered a problem that you believe was working fine with previous versions then please share your code snippets as well as the sample spreadsheet (if any) so we could analyze it on our end to provide further assistance in this regard.

15

Hi,

I have attached a test sheet (DeletionTest.xlsx), to use with the following example code:

var testBook = new Workbook(@"c:\exceldata\DeletionTest.xlsx");
var column = 1;
var row = 3;
var sheet = testBook.Worksheets[0];
var cell = sheet.Cells[row, column];
while (!String.IsNullOrEmpty(cell.StringValue))
{
Console.WriteLine("Deleting row: " + cell.StringValue);
var trick = sheet.Cells[row + 1, column].StringValue;
sheet.Cells.DeleteRow(row);
cell = sheet.Cells[row, column];
}

This snippet will display content from cell in column 1, for each row to be deleted.

But try running the same snippet, but without this line:

"var trick = sheet.Cells[row + 1, column].StringValue;"

It seems the wrong row is being deleted, if next cell isn't red.

This works fine with Aspose Cells v.7.7.2.3, but gives wrong output with v.7.7.2.5

I have put this dummy read of the next row cell in my code, så I get the expected result, but it's a hack.

Good luck with that one ;)

Thanking in advance
Soeren Praestegaard, EnergiMidt

16

Hi Soeren,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. Deletion test fails in Aspose.Cells v7.7.2.5 and works correct in v7.7.2.3. I have attached the screenshot showing the difference between the output of these. It seems, deletion of the row is not updated unless it is gone through a dummy read.

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

Screenshot:

17

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells
for .NET v7.7.2.6 and let us know your feedback.

18

Hi,

Looks good. It works now.

Thanking in advance

Soeren Praestegaard, EnergiMidt A/S

19

Hi,


Good to know that your issue is resolved now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
20

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

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