date format could not be set

Topics: User Forum
Dec 7, 2010 at 12:40 PM

Hi

Date in the database is in mm/dd/yyyy format but when i use that in my excel sheet date changes to yyyy-mm-dd

I have tried the following statement to change the date format, but this doesn't seems to be working.

$DateFormatIndex = sprintf('A8:A%d',$i);
$objPHPExcel->getActiveSheet()->getStyle($DateFormatIndex)->getNumberFormat()->setFormatCode('DD MMM YYYY');

Coordinator
Dec 7, 2010 at 12:56 PM
Edited Dec 7, 2010 at 12:56 PM

Please try using 'dd mmm yyyy' rather than 'DD MMM YYYY'.

Dec 13, 2010 at 6:58 AM

hi

Mark

I have tried your suggestion but it's still not working. Actually what I am doing is first setting the values by the following statement

$objPHPExcel->getActiveSheet()->setCellValue($cellindexcol1, odbc_result($res7,'TR_DATE'));

then at end of data insertion in sheet

I tried the below statement

$DateFormatIndex = sprintf('A8:A%d',$i);
$objPHPExcel->getActiveSheet()->getStyle($DateFormatIndex)->getNumberFormat()->setFormatCode(dd mmm yyyy');

Dec 13, 2010 at 7:45 AM

I am using HTML writer

Coordinator
Dec 13, 2010 at 9:02 AM

Are you actually converting the database value to an Excel date at all, or simply writing it as you receive it from the database? If the latter, then you should be getting erroneous values. If the former, what datatype is the TR_DATE column in your table?

Dec 13, 2010 at 9:22 AM

I am simply writing as it is I receive it from the database

data type for TR_DATE in database is DATE (using DB2)

Dec 13, 2010 at 10:11 AM

I can confirm that the new version is doing something different with date formats, using 1.7.4 i get dd/mm/yyyy with 1.7.5 using the exact same code i get yyyy/mm/dd.

Coordinator
Dec 13, 2010 at 10:44 AM
Edited Dec 13, 2010 at 10:48 AM

@vikastharyani

The database query is going to be returning either a formatted date string, or a PHP date/time value. This needs to be converted to an Excel date value using the PHPExcel_Shared_Date::PHPToExcel() or PHPExcel_Shared_Date::FormattedPHPToExcel() methods before it is stored in the cell. Alternatively, if the database query returns a formatted date string, you can use the Advanced Value Binder, and PHPExcel will do the conversion automatically when you set the cell value.

@ale1981
I'm not aware of any code changes between 1.7.4 and 1.7.5 that could affect date formatting; but I can't find any errors in the code or replicate this problem, and need a worked example to identify where any issue might be occuring.
Dec 15, 2010 at 9:26 AM

Hi Mark

I am also using a DB to retrieve date values, the data in the DB has not changed since I changed versions so it must be doing something, I use the following SQL to format the date from a datetime column.

CONVERT(char(10),DATE_FIELD,103)

Coordinator
Dec 15, 2010 at 11:45 PM
ale1981 wrote:

Hi Mark

I am also using a DB to retrieve date values, the data in the DB has not changed since I changed versions so it must be doing something, I use the following SQL to format the date from a datetime column.

CONVERT(char(10),DATE_FIELD,103)

If you're using CONVERT to cast the value to a string in your SQL query, you should be converting it to an Excel date value; otherwise you'd just be storing a string in the cell, formatted according to the formatting rules of the database CONVERT.

Dec 16, 2010 at 7:15 AM

@mark

Hi Mark

I am in a doubt, that can I integrate pie charts, bar graph or any 3d UI in my reports using PHPExcel

Coordinator
Dec 16, 2010 at 12:25 PM
vikastharyani wrote:

I am in a doubt, that can I integrate pie charts, bar graph or any 3d UI in my reports using PHPExcel

 Not in the current version.

Jan 4, 2011 at 11:14 AM
Edited Jan 4, 2011 at 11:15 AM
MarkBaker wrote:
ale1981 wrote:

Hi Mark

I am also using a DB to retrieve date values, the data in the DB has not changed since I changed versions so it must be doing something, I use the following SQL to format the date from a datetime column.

CONVERT(char(10),DATE_FIELD,103)

If you're using CONVERT to cast the value to a string in your SQL query, you should be converting it to an Excel date value; otherwise you'd just be storing a string in the cell, formatted according to the formatting rules of the database CONVERT.

The string format I am converting the datetime field to is dd/mm/yyyy and this worked fine with 1.7.4, however if I chang to 1.7.5 then the date is displayed in yyyy/mm/dd. I can not understand that if something hasnt changed between the versions how I am getting different results?!