[Feature] Add support for Date data type

[justkawal]

[Feature] Add support for Date data type

[nipunasudha]

I found out about a workaround until this is officially fixed. Please take a look at this article that explains that number means. You can write a simple function to convert this number into a DateTime.

How Dates Work in Excel – The Calendar System Explained + Video

Each Day is a Whole Number
Each day is represented by one whole number in Excel. Type a 1 in any cell and then format it as a date. You will get 1/1/1900. The first day of the calendar system.

Type a 2 in a cell and format it as a date. You will get 1/2/1900, or January 2nd. This means that one whole day is
represented by one whole number is Excel.
You can also take a cell that contains a date and format it as a number.

For example, this post was published on 1/27/2016. Put that number in a cell (the keyboard shortcut to enter today's date is Ctrl+;), and then format it as a number or General.
You will see the number 42,396. This is the number of days that have elapsed since 1/1/1900.

[BugsBuggy]

Here's a simple static util method I use to convert excel dates to DateTime. Hope this helps :)

  import 'package:jiffy/jiffy.dart';

  static DateTime tryParseExcelDateTimeDouble(double dateDouble, {DateTime ifInvalid}) {
    if (dateDouble == null || dateDouble < 1) return ifInvalid;
    return Jiffy(DateTime(1900, 1, 1))
        .add(days: dateDouble.toInt() - 1)
        .dateTime;
  }

It'll work if you change dateDouble.toInt() - 1 to dateDouble.toInt() - 2

[ashishbeck]

Also, the date value that we receive is not consistent. The CellTypes are either string or integer and it is pretty random as you can see here. It makes it very difficult to identify if a cell is an integer or date in disguise. For a sheet with a predetermined date column, it would be easy to convert them to DateTime depending on its type (string or int) but not for a generalized sheet. It would be great if you could also introduce a new CellType.date and return an actual DateTime object instead of a string/int.

[ashishbeck]

Of course, it must be a stress for you to dedicate to things outside of work and we are really grateful for it. All I can hope for is you to push fixes whenever possible.

[nipunasudha]

Here's a simple static util method I use to convert excel dates to DateTime. Hope this helps :)

  import 'package:jiffy/jiffy.dart';

  static DateTime tryParseExcelDateTimeDouble(double dateDouble, {DateTime ifInvalid}) {
    if (dateDouble == null || dateDouble < 1) return ifInvalid;
    return Jiffy(DateTime(1900, 1, 1))
        .add(days: dateDouble.toInt() - 1)
        .dateTime;
  }

[justkawal]

Started working on this in beta (unstable) branch :: https://github.com/kawal7415/excel/tree/beta

As we had started to work on providing Formula functionality, So we will be providing various CellType support.

Thank You
Stay Tuned...
Long Live Open S🔥urce

[st99]

Is there a solution for this problem.
I still am not able to recieve fields as date.

Excel only recieves the fields as date if it is in the first column. For columns other than that, I get values such as 43959.

[yunior123]

Hello, how do I receive the fields as Date?
I have a column with the fields as Date but when I order to display some numbers appear.
For example the date in the file is: 08/05/2020 12:00:00
No Flutter in print (as your example) appears: 43959.5
How to solve?

Hello, did you manage to solve this issue?

[yunior123]

I am having the same issue indicated above

[sleepingkit]

Same issue.

[ashishbeck]

Or you could simply return the default string value as it appears on the Excel (dd-mm-yyyy) and let us parse it. That would be really simple and easy.

[justkawal]

@ashishbeck
Structure of excel doesn't support to have normal date as String, If I would have done that way then the excel file would have easily broken on Google Sheets,, Sheets from the Apple software and also on Microsoft Excel.

I thinks that the answer from @nipunasudha would be promising to help you out in this scenario until I push a fix for it.

[Bes79]

Awaiting for DateTime column )

[MalikSamiAwan]

@nipunasudha your solution worked for me THANKS with just small issue its showing me one day ahead. Is that a big problem or should i manually add 1 day to it.

[joe-mybuzz]

Is there a way to set a cell as a Date when exporting Excel?

[MalikSamiAwan]

@BugsBuggy and how can we store date values?

[Bes79]

Any news?

[quaaantumdev]

I implemented the support for date, time and date-time values, here is the PR: #279
And so you may also use it like this as long as it is not available in the main branch:

# pubspec.yaml

dependencies:
  excel
    git: https://github.com/quaaantumdev/excel.git

( the repository may be deleted once the changes are merged )

[justkawal]

Can you try version:

excel: ^4.0.0