Wrong parsing of cell value

[AlbertoBonfiglio]

Hi there, maybe I am doing something wrong but when I read a cell formatted as custom string with value 20997.8364 (so it shows in excel as $20,998) it actually gets read as: CellType.String and _value: "1957-06-26T20:04:24.959"

The cell object shows as : Data (Data(1957-06-26T20:04:24.959, 1, 13, null, Greencross - Vet KPI))

Any help is appreciated

[hrwn]

I have same problem, I change format cells in excel file to text and solve it

[AlbertoBonfiglio]

Maybe the problem is that by default it converts to string. It would be interesting to see how it comes in to potentially fix it.

[SlickSlime]

But why would the value be in DateTime format? Any idea?

[niklasbartsch]

I have the same problem and its converting my number 000700 into the DateTime String "1901-11-30 00:00:00.000"

I tried some things and it seems like it is always taking the days since the 30.12.1899 😅

        final dateTime = DateTime.tryParse(number);
        print(dateTime.toString());  // 1901-11-30 00:00:00.000
        final differences =
        dateTime!.difference(DateTime(1899, 12, 30)).inDays;
        print('differences: $differences') // 700

I would guess it is taking the numbers after the dot for the minutes since this date

Just a quick workaround for now.

[AlexanderArendar]

Problem is actual and blocks usage of the package for a very basic case: reading an xlsx file with numeric floating point values. Would be great if this gets fixed.

[AlexanderArendar]

Currently we can use following workaround elaborated from the comment of @niklasbartsch :

      final row = sheet.rows[i];
      final rawValue = row.elementAt(columnIndex)!.value as String;
      final date = DateTime.parse(rawValue);
      final diffDays = date.difference(DateTime(1899, 12, 30)).inDays;
      final diffMillis = date.difference(DateTime(1899, 12, 30)).inMilliseconds;
      final partDelta = diffDays * 24 * 3600 * 1000;
      final remainder = diffMillis - partDelta;
      final millisInDay = 24 * 3600 * 1000;
      double fractionalPart = remainder / millisInDay;
      result = diffDays + fractionalPart;

[FauconSpartiate]

@AlbertoBonfiglio @hrwn @niklasbartsch Hey, is someone still experiencing this issue on the latest version 2.0.1? If yes, could you provide an example Excel file? I couldn't reproduce it on my part... Make of course sure that it doesn't contain any critical data.

[DamianBender]

Hey, I still have this issue with version 2.0.1. Here is the output that I get from printing the sheet: Here is the sheet: test.xlsx

[FauconSpartiate]

@justkawal

[DamianBender]

Is there any new info about this problem?

[FauconSpartiate]

@DamianBender Nope, someone motivated enough troubleshoot this is welcome, solving it shouldn't be that complicated...

[justkawal]

I'll look into it....

[DamianBender]

Hey, wanted to let you know that I'm still experiencing that same issue in version 2.1.0. What's the progress of this issue?

[DamianBender]

Hello guys, it's already been a few months from the last post. Can you please give me a short response on that?

[FauconSpartiate]

@DamianBender Nope, someone motivated enough troubleshoot this is welcome, solving it shouldn't be that complicated...

If you're that desperate, you can take a look at the codebase for yourself 👍

[quaaantumdev]

@DamianBender Nope, someone motivated enough troubleshoot this is welcome, solving it shouldn't be that complicated...

If you're that desperate, you can take a look at the codebase for yourself 👍

@FauconSpartiate stumbled over the same issue, took your advice and fixed it ;) the following should do the trick: https://github.com/justkawal/excel/pull/279

And the problem here is, that your custom number format most likely takes the first custom number format id (numFmtId) which is 164. In the library, this number is hard-coded to be a date. This is incorrect but introduced by https://github.com/justkawal/excel/pull/47. I guess it was an attempt by someone to make his file work without further knowledge of what he is doing there ;)

[FauconSpartiate]

Fixed in #279