Exporting Dates to Excel

QuestionsExporting Dates to Excel
Mike Sheppard asked 4 months ago

When exporting dates to Excel, they do-not get interpreted as Dates inside Excel. I have tried a custom call-back and while I can format the column and the dates do look right, they are not read correctly and things like advanced filtering do-not work.

I believe we need to export to DateSerial for Excel.

Is this possible? I am trying to avoid a custom Excel export for every report and was trying to find a way to use the SrcFormat section and custom formatting on the Excel export.

Thanks

 

Mike

Abu Ghufran Staff replied 4 months ago

I’ll look into this matter and update you back.

8 Answers
Mike answered 4 months ago

I’ve created a quick screen-capture to show you how Excel dates filter when you manually enter them in Excel (or copy/paste) vs when you export them from GridPHP.

View post on imgur.com

Thanks

Mike

Abu Ghufran Staff answered 4 months ago

I’ve just modified the lib code to make all formatter=date columns to excel date format.

https://gist.github.com/gridphp/3b4484898043ca7725bdcc98eb48f00f

(line 5-24) to be added in main lib (jqgrid_dist.php)

To modify, search “if array changes, update it” comment and append the code as in gist.

 

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Mike answered 4 months ago

That code is similar to attempts I tried, but unfortunately the dates either come-up as 1970-01-01 or FALSE. It does not export an actual date.

I tried to test everything before reaching-out, but I literally stalled trying to figure-out the logic of PHP Date Serial and Excel Date Serial.

I blame Microsoft, but the report users are blaming me (ha ha).

LAST_ORDER_DATE

1970-01-01

FALSE

FALSE

FALSE

FALSE

FALSE

Abu Ghufran Staff answered 4 months ago

I’ve used column options as follows. Try changing¬†srcformat and newformat as below.

$col = array();
$col["title"] = "Date";
$col["name"] = "invdate"; 
$col["width"] = "50";
$col["editable"] = true; // this column is editable
$col["editoptions"] = array("size"=>20); // with default display of textbox with size 20
$col["editrules"] = array("required"=>true); // and is required
$col["formatter"] = "date"; // format as date
$col["formatoptions"] = array("srcformat"=>'Y-m-d',"newformat"=>'d/m/Y'); // format as date
$col["search"] = false;
$cols[] = $col;
_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Mike answered 4 months ago

Thanks for the update, I think I fixed the 1970 issue, it was likely my fault for having some of my testing code still in-place, I went back to stock settings.

I get the first date now, but each additional row just shows “false”.

As well, is still does-not open as a Date in Excel. I really think this could be a Microsoft glitch – but it’s so frustrating, as exporting to CSV works, but most of my reports lose something when exporting to CSV (such as leading zeros).

Thanks

Mike

LAST_ORDER_DATE

28/06/2013

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

Abu Ghufran Staff answered 4 months ago

I’m sending you my demo code that works with online version of excel.
(I don’t have locally installed due to licensing)

You can test this code on the demo database that comes with the package.

Demo Code here: https://gist.github.com/gridphp/ca2e8796ec680c63f206e832634625e9

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Mike answered 4 months ago

It’s encouraging you can get it to work with Excel Online.

I still can’t get all the dates to export – I only get FALSE for everything past the first date.

I think the array counter is not working in my case . . .

for($r=1;$r<count($arr);$r++)

I tried this:

for($r=1;$r<count($arr[0]);$r++)

But then it skips a few.

Is there a chance my jqgrid.dist is out of date compared to yours?

I’m running build 20210819-1130

 

Thanks

Mike

Abu Ghufran Staff answered 4 months ago

Please try running the demo code and see if it works at your end.

I’m also sending you latest build in direct email.

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Your Answer

7 + 12 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?