export date from DataTables to excel

davide commented on Jul 27, 2023

Hi, I've a DataTables with a date field defined as follows:

"mydate" => array(
          "label" => "End",
          "type" => "date",
          "format" => "Y-m-d",
          "displayFormat" => "d/m/Y",
        ),

If I export the table to excel it is read as String and nota Date. If I change the format to date I'm not able to order the column.

The only way to have a correct export is to define the field as follows:

"mydate" => array(
          "label" => "End",
          "type" => "date"
        ),

but in the report it is shown in the format "Y-m-d"

Thank you

David Winterburn commented on Jul 31, 2023

Davide, is this server-side exporting with Excel package (i.e using excel/Table widget in excel view file) or DataTables' client-side export with Buttons plugin?

In case you are using Excel package, there's a property for excel/Table widget's column called "excelFormatCode" which can be used to format a datetime or numeric column like this:

//MyReportExcel.view.php
\koolreport\excel\Table::create(array(
    ...
    "columns" => array(
        "datetimeColumn" => array(
            "type" => "datetime", // or "date", "time"
            "excelFormatCode" => "dd/mm/yyyy", // e.g "31/12/2012"
            ...
        )
    )
));

This format code is the same one applied by MS Excel, for example:

Code 	Date

dd/mm/yyyy 	03/01/2012
mm/dd/yyyy 	01/03/2012
d-m-yyyy 	3-1-2012
dd-mm-yy 	03-01-12
dddd 	Tuesday
dd. mm. yyyy dddd 	03. 01. 2012 Tuesday
mmmm 	January
d. mmmm yyyy 	3. January 2012
mmmm dd, yyyy 	January 03, 2012
dddd, mmmm dd yyyy 	Tuesday, January 03 2012
ddmmyy 	030112
dd. “text” 	03. text

hh.mm 	06.25
hhmm 	0625
h:mm:ss AM/PM 	6:25:31 am
hh “hours and” mm “minutes” 	06 hours and 25 minutes
[m] 	385 (the number of minutes since 00:00:00)
[s] 	23131 (the number of seconds since 00:00:00)

hh:mm dd/mm/yyyy 	06:25 04/11/2012
dddd hh:mm 	Thursday 06:25

Let us know if this works for you or not.

davide commented on Jul 31, 2023

Hi David, I'm using DataTables client-side export. I defined it as follows:

      "options" => array(
        "dom" => 'Bfrtip',
        "buttons" => [
          'excel',
        ],
      ),

David Winterburn commented on Aug 3, 2023

It would be a lot more complicated if you wanted to format excel data with client-side export using DataTables' Buttons. Here's an answer for setting number format code:

JQuery Datatable Excel export number format

You can try to change the format code to a datetime one and see if it works.

KoolReport's Forum

Export date from DataTables to excel #3109

Build Your Excellent Data Report