formula sum with number have a weird bug

[huong]

I need calculate sum of a column.but there was a bug 'not a valid dat' .however number format not date type:

I have ran into method _setValueRowCol in worksheet.dart and found at statement follow:

final bool bDateTime =

!value.contains(cultureInfo.dateTimeFormat.dateSeparator) &&

dateValue != null;

in my opinion it should change to 

final bool bDateTime =

value.contains(cultureInfo.dateTimeFormat.dateSeparator) &&

dateValue != null;

I tested with value of string is 403625254

here are log

Error: Exception: Not a valid date at Object.throw_ [as throw] (http://localhost:53680/dart_sdk.js:5080:11) at Range._dateToTicks (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:31562:17) at Range._toOADate (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:31576:27) at [_setFormulaDateValue] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:31321:39) at [_setValueRowCol] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:34296:44) at [_pullUpdatedValue] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:6792:57) at get calculatedValue [as calculatedValue] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:30902:80) at http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24797:54 at [_insert] (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:228:9) at builder.XmlBuilder.new.element (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:147:24) at http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24786:39 at [_insert] (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:228:9) at builder.XmlBuilder.new.element (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:147:24) at http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24762:29 at [_insert] (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:228:9) at builder.XmlBuilder.new.element (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:147:24) at http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24758:19 at [_insert] (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:228:9) at builder.XmlBuilder.new.element (http://localhost:53680/packages/xml/src/xml/builder.dart.lib.js:147:24) at [_saveWorksheet] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24713:15) at [_saveWorksheets] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24688:32) at [_saveInternal] (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:24615:31) at xlsio.Workbook.new.saveAsStream (http://localhost:53680/packages/syncfusion_flutter_xlsio/xlsio.dart.lib.js:29190:35) at danh_sach_hoso_luu.DanhSachHosoLuuPreview.new._generateExcelMerge (http://localhost:53680/packages/vinasafe/views/baocao/printing/danh_sach_hoso_luu.dart.lib.js:668:30)

this is style used

final xlsio.Style style9 = workbook.styles.add('style9');

style9.backColor = '#CFEBF1';

style9.bold = true;

style9.vAlign = xlsio.VAlignType.center;

style9.borders.bottom.lineStyle = xlsio.LineStyle.medium;

style9.borders.bottom.color = '#308DA2';

style9.borders.right.lineStyle = xlsio.LineStyle.thin;

style9.borders.right.color = '#A6A6A6'; style9.numberFormat = '#,##0.00';

here is code set formula:

final xlsio.Range range11 = sheet.getRangeByName('H$sumLength:I$sumLength');

range11.merge(); range11.cellStyle = styles[9]; int lastValue = sumLength - 1;

range11.setFormula('=SUM(I9:I$lastValue)');

[Ramya Sivakumar]

Hi huong,

Greetings from Syncfusion.

We are checking the query and will get back to you with the details tomorrow.

Regards,

Ramya.

[Rahul Balasundaram]

Hi huong,

we’ll share the details tomorrow without fail.

Regards,

Rahul.

[Ramya Sivakumar]

Hi huong,

We are unable to reproduce the issue at our end. We have attached the sample which was tried at our end. Kindly share a sample illustrating the issue or else modify the following sample and reproduce the issue which would help us to proceed further.

Sample link - https://www.syncfusion.com/downloads/support/directtrac/general/ze/F-176824293577747

The “Not a valid date” is a known Exception. It will occur when we give the invalid Data time. So, please share the Date and time which you are using or else share the Entire code snippet which you are using at your end which will be helpful for us in investigating the query further.

Regards,

Ramya.

[huong]

all value i set are double not date.bug appear when I call

sheet.enableSheetCalculations();

.

Attachment: flutter_application_2_5782c4ea.zip

[Ramya Sivakumar]

Hi huong,

Thanks for sharing the sample.

We can reproduce the reported scenario at our end and validate it currently. We will share the validation details in 2 business days, on August 22, 2022.

Regards,

Ramya.

[Ramya Sivakumar]

Hi Huong,

We have confirmed the issue as Not a valid date exception is thrown for number value while creating Excel document with SUM formula and logged a defect report. We will include the fix for this issue in our upcoming weekly NuGet release scheduled for September 5, 2022.

You can track the status of the defect report through the following feedback link.

Track Status: https://www.syncfusion.com/feedback/37208/not-a-valid-date-exception-is-thrown-for-number-value-while-creating-excel

We will let you know once the new package version is available on nuget.org.

Disclaimer: The inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.

Regards,

Ramya.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Huong,

We regret for the inconvenience.

We were unable to move the fix in weekly NuGet release as promised, due to some internal issues. We will include it in next weekly NuGet scheduled for September 13^th,2022 and let you know once the package is available in nuget.org.

Regards,

Keerthi.

[Ramya Sivakumar]

Hi Huong,

We appreciate your patience. 

We have included the fix to resolve the issue Not a valid date exception is thrown for number value while creating Excel document with SUM formula in our weekly NuGet release version 20.2.0.49. Kindly upgrade your Syncfusion packages to this new 20.2.0.49 version and let us know if the issue is resolved

Regards,

Ramya

[huong]

I confirm that It is working now.thanks your team!

[Ramya Sivakumar]

Hi huong,

We are glad that the issue is resolved. Kindly let us know if you need any further assistance.

Regards,

Ramya.