Set locale dependent date number format in cell

Jun 12, 2009 at 9:12 PM

Hi all and thank you for this great stuff.

I tried to find solution to my problem, but I was not succesful. I want to write formula into Excel cell and I want to set this cell to be locale dependent date number format. Because this .xls file will be downloaded by people from USA, Europe, etc. and date should be presented in this cell relative to their locale settings, for example 06/11/2009 in the USA, 11.6.2009 in the Czech republic, etc. Is it possible somehow? Or is there any other solution? Thank you very much for your help...

Developer
Jun 13, 2009 at 3:25 AM

It should be possible starting with PHPExcel 1.6.7. Example:

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$time = gmmktime(0, 0, 0, 12, 31, 2008); // int(1230681600)
$sheet->getCell('A1')->setValue(PHPExcel_Shared_Date::PHPToExcel($time));
$sheet->getStyle('A1')->getNumberFormat()->setBuiltInFormatCode(14);

$sheet->getColumnDimension('A')->setWidth(30);

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');

Let's check the result. In Windows XP, going to Control Panel > Regional and Language Options, with English (United States) locale.

Date shows as 12/31/2008
http://img190.imageshack.us/img190/9807/localeenus.png
http://img190.imageshack.us/img190/4253/writeenus.png

Changing the locale to Danish and re-opening the file

Date shows as 31-12-2008
http://img23.imageshack.us/img23/1797/localeda.png
http://img23.imageshack.us/img23/3826/writeda.png

For this built-in number format, date shows up differently, according to system locale.

Built-in format code 14 is a locale dependent date number format. There is also built-in format code 22 which is also locale dependent, but it also has a time component.

Jun 15, 2009 at 11:16 AM
Edited Jun 15, 2009 at 11:17 AM

Great, it works. Thank you very much.

But I encounter another problem:
I also insert formula into one cell and I need to be number. This formula is DDE connection to another application. But coming number is not number even if I tried following code:

$objPHPExcel->getActiveSheet()->getCell('F'.$radek)->setValueExplicit('25', PHPExcel_Cell_DataType::TYPE_NUMERIC);
$objPHPExcel->getActiveSheet()->getStyle('F'.$radek)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

Maybe the problem is in zeroes between thousands, but I am no sure. Is it possible to handle it somehow?

http://img7.imageshack.us/img7/931/volumeproblem.jpg

Thank you for your help...

Developer
Jun 15, 2009 at 10:48 PM

This look strange. I don't know why the number in that cell is left aligned unless the horizontal alignment has accidentally been set to 'left'. Can you send me the Excel file? (erik at phpexcel dot net)