date and number problem

Topics: Developer Forum, Project Management Forum, User Forum
Sep 22, 2010 at 4:03 AM

I have a problem...

This is my code,
echo PHPExcel_Shared_Date::isDateTime($objWorksheet ->getCellByColumnAndRow(17, 6) ;
It returns  "1" 

But the format cell of (17,6)  is a accounting category. This is a numeric value, not date...

Please help and thanks

 

 

Coordinator
Sep 22, 2010 at 9:48 AM
Edited Sep 22, 2010 at 9:49 AM

PHPExcel's PHPExcel_Shared_Date::isDateTime() method attempts to identify whether a cell contains a date value or not be testing for by comparing it against the pre-defined date/time format masks, or tries to detect custom date formats by testing for the presence of certain characters within the format mask ('y', 'm', 'd', 'H', 'i', or 's') unless they fall within square brackets, so the d in [red] or the 'y' in [yellow] should not be picked up by the test.

It's possible that the regexp that does this test is returning a false positive: can you let me know the actual format mask that you have for cell (17,6).

Sep 24, 2010 at 3:17 AM

The value of  cell (17,6) is 350
Format cell category  is "Accounting", decimal places is "2" and symbol is "HK $"

The display format is HK$ 350.00

Thanks Mark.

 

Sep 24, 2010 at 3:38 AM

Mark,

I try it again,
if i set symbol is "$ Chinese (Taiwan)", it will return false
if symbol is  "HK $", it will return true

How can i get false if i use symbol is  "HK $"?

Thanks!

Coordinator
Sep 24, 2010 at 12:35 PM

I don't have the option to select either "$ Chinese (Taiwan)" or "HK $" as currency or accounting formats in my versions of Excel; but I created one by hand (using the Custom option) for "HK $", and PHPExcel correctly reports it as not being a date format.

Can you write a little script to load in your workbook and then execute the following lines (for the cells containing "$ Chinese (Taiwan)" and "HK $"):

 $cellID = 'R6';  // substitute your cell reference here
 echo "<b>$cellID</b><br />";
 echo 'Format Mask: ',$objPHPExcel->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode(),'<br />';
 echo 'Date/Time? ',((PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) ? 'Yes' : 'No'),'<br />';

The resulting display for "HK $" should look something like:

R6 
Format Mask: _-[$$HK]* #,##0.00_ ;_-[$$HK]* \-#,##0.00\ ;_-[$$HK]* "-"??_ ;_-@_ 
Date/Time? No
Sep 27, 2010 at 3:54 AM
Edited Sep 27, 2010 at 3:55 AM

i try it script:

$cellID = $objWorksheet ->getCellByColumnAndRow(17, 6)->getvalue();
echo "<b>$cellID</b><br />";
echo 'Format Mask: ',$objExecl->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode(),'<br />';
echo 'Date/Time? ',((PHPExcel_Shared_Date::isDateTime($objExecl->getActiveSheet()->getCell($cellID))) ? 'Yes' : 'No'),'<br />';

it return this:

350
Format Mask: General
Date/Time?
Fatal error: Uncaught exception 'Exception' with message 'Invalid cell coordinate.' in  
C:\AppServ\www\Project\Classes\PHPExcel\Cell.php:490 Stack trace: #0  
C:\AppServ\www\Project\Classes\PHPExcel\Worksheet.php(940):  PHPExcel_Cell::coordinateFromString('350') #1  
C:\AppServ\www\Project\BudgetSystem\Import.php(240):  PHPExcel_Worksheet->getCell(350) #2 {main} thrown in  
C:\AppServ\www\Project\Classes\PHPExcel\Cell.php on line 490

Sep 27, 2010 at 3:59 AM
Edited Sep 27, 2010 at 4:00 AM

If i try this script

$cellID = $objWorksheet ->getCellByColumnAndRow(17, 6)->getvalue();
$cellID2 = $objWorksheet ->getCellByColumnAndRow(17, 6);
echo "<b>$cellID</b><br />";
echo 'Format Mask: ',$objExecl->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode(),'<br />';
echo 'Date/Time? ',((PHPExcel_Shared_Date::isDateTime($cellID2)) ? 'Yes' : 'No'),'<br />';

it return

350
Format Mask: General
Date/Time? Yes

Any problem for my cell?

Thanks Mark!
Coordinator
Sep 27, 2010 at 10:46 PM

Use

$cellID = $objWorksheet ->getCellByColumnAndRow(17, 6)->getCoordinate();

We need the cell address for the call to $objExecl->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode(), not the value of the cell.

Sep 28, 2010 at 4:05 AM

 

$cellID = $objWorksheet ->getCellByColumnAndRow(17, 6)->getCoordinate();
echo "<b>$cellID</b><br />";
echo 'Format Mask: ',$objExecl->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode(),'<br />';
echo 'Date/Time? ',((PHPExcel_Shared_Date::isDateTime($objExecl->getActiveSheet()->getCell($cellID))) ? 'Yes' : 'No');

O...also return yes...
R6
Format Mask: _("HK$"* #,##0.00_);_("HK$"* \(#,##0.00\);_("HK$"* "-"??_);_(@_)
Date/Time? Yes
Coordinator
Sep 28, 2010 at 11:01 PM

The currency information (HK$) isn't in square brackets, so the PHPExcel_Shared_Date::isDateTime() method is detecting the H and assuming Hours, and therefore a date/time. I've modified the code logic to test for an underscore (_) in the first position of the format mask, or a "0 " as the first two characters, and return a false if either of these conditions matches, as these conditions are typically associated with numeric, currency, accounting or fraction masks, but never with dates. This should result in fewer false positives, and resolve your HK$ accounting mask.

Sep 29, 2010 at 5:26 AM

Mark, 

How can i solve this problem?
Please help and thanks!

Ray 

Coordinator
Sep 29, 2010 at 9:06 AM

Either download the latest source code from SVN (via the "Source Code" tab on this site)

or

in Classes\PHPExcel\Shared\Date.php, on or about line 259, insert

  // Typically number, currency or accounting (or occasionally fraction) formats
  if ((substr($pFormatCode,0,1) == '_') || (substr($pFormatCode,0,2) == '0 ')) {
     return false;
  }

immediately before

  // Try checking for any of the date formatting characters that don't appear within square braces
  if (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$pFormatCode)) {
     return true;
  }
Sep 30, 2010 at 10:24 AM

Thank you Mark!!!!