PHPExcel Excel2007 can not handle currency format correctly

Topics: Developer Forum, Project Management Forum, User Forum
Apr 23, 2015 at 7:42 PM
PHP 5.3, PHPExcel 1.7.6 or 1.8
Following is a test reuslt shows excel5 reader with excel5 xls file, function getForamtCode() returns
correct format code. excel2007 reader with excel2007 file, getFormatCode() returns correct code for currency format $#,##0 and $#,##0;[Red]$#,##0. But it returns General for $#,##0_);($#,##0) or
$#,##0_);Red.

Please explain it.

with reader Excel5

Setting in Sheet Result in PHP reader
Currency Custom detail Returned formatcode()
-$1,234 $#,##0 "$"#,##0
$1,234 $#,##0;[Red]$#,##0 $"#,##0;[Red]"$"#,##0
($1,234) $#,##0_);($#,##0) $"#,##0_);("$"#,##0)
($1,234) $#,##0_);Red $#,##0_);[Red]("$"#,##0)

Excel2007
Currency Custom detail Returned formatcode()
-$1,234 $#,##0 $#,##0
$1,234 $#,##0;[Red]$#,##0 $#,##0;[Red]"$"#,##0
($1,234) $#,##0_);($#,##0) General
($1,234) $#,##0_);Red General
Coordinator
Apr 23, 2015 at 8:21 PM
I can't explain it because I don't understand what you're saying

Do you have example spreadsheets in both formats to demonstrate the problem you've found?

Can you provide some working code to help explain what it is that you're reading, and what exactly you expect to see?
Apr 24, 2015 at 4:11 AM
MarkBaker,

Thanks for post your comments

I tested two excel files with same data. One save as Excel5 1997-2003 (xls). Other saved as Excel2007 xlsx.

I set cells format as currecy,then move click "custom". Then run PHPExcel to read and display the value of getFormatCode(). The results are:
For Excel5 file, getFormatCode() returns expected format code such as "$"#,##0, $"#,##0;[Red]"$"#,##0, $"#,##0_);("$"#,##0), or $#,##0_);Red
For Excel2007, if cell format is -$1,234 or $1,234, getFormatcode() returns $#,##0 or $#,##0;[Red]"$"#,##0 : which means "Expected"
if cell format is ($1,234), getFormatCode() returns General。
Coordinator
Apr 25, 2015 at 10:38 AM
For Excel2007, if cell format is -$1,234 or $1,234,

Neither -$1,234 nor $1,234 are valid format masks.... they're formatted values.

But I really need a working example showing with the format masks..... I can't tell what the PHPExcel Reader is loading from file without a file to load.... and I need to see what's in the actual file before PHPExcel loads it
Apr 27, 2015 at 5:20 PM
MarkBaker,

Thanks for your response.
I wrote a test PHP script to test curreny fornat for Excel5 and Excel2007. And I create TestExcel 5.xls and TestExcel2007.xlsx for test.
In each Exel file, I sent CELL 'I5' with currency format. I tested with all currency foramt: -1234, 1234 with red color, (1234), and (1234) woth red color.
The results tell me for Excel5, all returned formatcode are correct. For Excel2007, if format is -1234 or 1234 with red color, return correct format code. But, if set as
(1234) or (1234) woth red color, it returns "General".
Becasue I can not attahced the excel files,I copy part of the out put in the bottom. Please notes that first section of the out put is the test result for Ecel5, second is for Excel2007.

<?php //ShowFormatCode.php
session_start();
$shnum =0;
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';
//echo 'IN pre_load_first20<br>';

$inputFileName = "./upload/TestExcel5.xls";
echo "inputFileName***" .$inputFileName ."***<br>";
display_format($inputFileName,$shnum);

echo "*******************------------------------**************************----------------------------*******************<br>***";

$inputFileName = "./upload/TestExcel2007.xlsx";
echo "inputFileName***" .$inputFileName ."***<br>"; 
display_format($inputFileName,$shnum);
function display_format($inputFileName,$shnum){
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    //$objReader = PHPExcel_IOFactory::createReader("Excel5");
    $objPHPExcel = $objReader->load($inputFileName);
    //$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    if (!$objPHPExcel){
        echo 'objPHPExcel is null<br>';
    }
    else{
      echo 'Excel type:***' .$inputFileType .'***<br>';
      // echo 'objPHPExcel is NOT null<br>';
       ;
    }   
} catch(Exception $e) {
     $er = 'Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage();
    echo $er; 
}
echo 'After try/catch **' .$shnum .'**<br>';
$objPHPExcel->setActiveSheetIndex($shnum);
//echo 'After setActiveSheetIndex**<br>';

$highestColumm = $objPHPExcel->getActiveSheet()->getHighestColumn();

echo 'highestColumm***' .$highestColumm .'***<br>';
$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow(); 
echo 'highestRow***' .$highestRow .'***<br>';

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumm);  

echo 'highestColumnIndex***' .$highestColumnIndex .'***<br>';
$dtcol = array();
$nodtcol = array();
$dtdex = array();
$highestRow = 8;
echo 'Only show first 7 rows---------------------<br>';
for($row =2; $row < $highestRow; $row++) {   
       for ($i = 0; $i < $highestColumnIndex; $i++) {   
             // echo 'in loop **' .$i  .'**column--';
             $cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $row);
            $lt = get_col_letter($i+1); 
            $range = $lt .$row;

             $frmcod = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $row)->getStyle()->getNumberFormat()->getFormatCode();

             $v = $cell->getValue();
// echo $range .'*' .$frmnm .'**' .$frmcod .'**value**' .$v  .'**<br>';
 echo $range .'**' .$frmcod .'**value**' .$v  .'**<br>';  //check formatcode 

      }//for in loop    
      echo  'End ' .$row .'th row-----------------------------------------------------<br>';
 }//for out loop
}//end function display_format

function get_col_letter($num){
$comp=0;    
$pre='';
$letters=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
//if the number is greater than 26, calculate to get the next letters
if($num > 26){
    //divide the number by 26 and get rid of the decimal
    $comp=floor($num/26);

    //add the letter to the end of the result and return it
     if($comp!=0)
   // don't subtract 1 if the comparative variable is greater than 0
return get_col_letter($comp).$letters[($num-$comp*26)];
    else
               return get_col_letter($comp).$letters[($num-$comp*26)-1];
}
else
//return the letter
 $num = $num -1;
 return $letters[$num];
}
?>

Part of the out put as shown:
inputFileName***./upload/TestExcel5.xls***
Excel type:Excel5

......
End 4th row-----------------------------------------------------
A5GeneralvalueG00000759
B5GeneralvalueF
C5GeneralvalueUMTA
D5GeneralvalueUNNY-03-0344
E5Generalvalue14.08.80
F5Generalvalue3456
G5#,##0.00value61438180.11
H5#,##0.00value61438180.11
I5**"$"#,##0_);[Red]("$"#,##0)value-1234.77**
J5Generalvalue****
K5Generalvalue****
End 5th row-----------------------------------------------------

------------------------*******----------------------------
inputFileName./upload/TestExcel2007.xlsx

Excel type:Excel2007
............

A5
GeneralvalueG00000759

B5Generalvalue
F

C5Generalvalue
UMTA

D5Generalvalue
UNNY-03-0344

E5Generalvalue14.08.80
F5Generalvalue3456
G5#,##0.00value61438180.11
H5#,##0.00value61438180.11
I5Generalvalue1234.77
J5Generalvalue****
K5Generalvalue****
End 5th row-----------------------------------------------------
Apr 27, 2015 at 5:22 PM
MarkBaker,

Thanks for your response.
I wrote a test PHP script to test curreny fornat for Excel5 and Excel2007. And I create TestExcel 5.xls and TestExcel2007.xlsx for test.
In each Exel file, I sent CELL 'I5' with currency format. I tested with all currency foramt: -1234, 1234 with red color, (1234), and (1234) woth red color.
The results tell me for Excel5, all returned formatcode are correct. For Excel2007, if format is -1234 or 1234 with red color, return correct format code. But, if set as
(1234) or (1234) woth red color, it returns "General".
Becasue I can not attahced the excel files,I copy part of the out put in the bottom. Please notes that first section of the out put is the test result for Ecel5, second is for Excel2007.

<?php //ShowFormatCode.php
session_start();
$shnum =0;
set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
include 'PHPExcel/IOFactory.php';
//echo 'IN pre_load_first20<br>';

$inputFileName = "./upload/TestExcel5.xls";
echo "inputFileName***" .$inputFileName ."***<br>";
display_format($inputFileName,$shnum);

echo "*******************------------------------**************************----------------------------*******************<br>***";

$inputFileName = "./upload/TestExcel2007.xlsx";
echo "inputFileName***" .$inputFileName ."***<br>"; 
display_format($inputFileName,$shnum);
function display_format($inputFileName,$shnum){
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    //$objReader = PHPExcel_IOFactory::createReader("Excel5");
    $objPHPExcel = $objReader->load($inputFileName);
    //$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
    if (!$objPHPExcel){
        echo 'objPHPExcel is null<br>';
    }
    else{
      echo 'Excel type:***' .$inputFileType .'***<br>';
      // echo 'objPHPExcel is NOT null<br>';
       ;
    }   
} catch(Exception $e) {
     $er = 'Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage();
    echo $er; 
}
echo 'After try/catch **' .$shnum .'**<br>';
$objPHPExcel->setActiveSheetIndex($shnum);
//echo 'After setActiveSheetIndex**<br>';

$highestColumm = $objPHPExcel->getActiveSheet()->getHighestColumn();

echo 'highestColumm***' .$highestColumm .'***<br>';
$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow(); 
echo 'highestRow***' .$highestRow .'***<br>';

$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumm);  

echo 'highestColumnIndex***' .$highestColumnIndex .'***<br>';
$dtcol = array();
$nodtcol = array();
$dtdex = array();
$highestRow = 8;
echo 'Only show first 7 rows---------------------<br>';
for($row =2; $row < $highestRow; $row++) {   
       for ($i = 0; $i < $highestColumnIndex; $i++) {   
             // echo 'in loop **' .$i  .'**column--';
             $cell = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $row);
            $lt = get_col_letter($i+1); 
            $range = $lt .$row;

             $frmcod = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $row)->getStyle()->getNumberFormat()->getFormatCode();

             $v = $cell->getValue();
// echo $range .'*' .$frmnm .'**' .$frmcod .'**value**' .$v  .'**<br>';
 echo $range .'**' .$frmcod .'**value**' .$v  .'**<br>';  //check formatcode 

      }//for in loop    
      echo  'End ' .$row .'th row-----------------------------------------------------<br>';
 }//for out loop
}//end function display_format

function get_col_letter($num){
$comp=0;    
$pre='';
$letters=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
//if the number is greater than 26, calculate to get the next letters
if($num > 26){
    //divide the number by 26 and get rid of the decimal
    $comp=floor($num/26);

    //add the letter to the end of the result and return it
     if($comp!=0)
   // don't subtract 1 if the comparative variable is greater than 0
return get_col_letter($comp).$letters[($num-$comp*26)];
    else
               return get_col_letter($comp).$letters[($num-$comp*26)-1];
}
else
//return the letter
 $num = $num -1;
 return $letters[$num];
}
?>

Part of the out put as shown:
inputFileName***./upload/TestExcel5.xls***
Excel type:Excel5

......
End 4th row-----------------------------------------------------
A5GeneralvalueG00000759
B5GeneralvalueF
C5GeneralvalueUMTA
D5GeneralvalueUNNY-03-0344
E5Generalvalue14.08.80
F5Generalvalue3456
G5#,##0.00value61438180.11
H5#,##0.00value61438180.11
I5**"$"#,##0_);[Red]("$"#,##0)value-1234.77**
J5Generalvalue****
K5Generalvalue****
End 5th row-----------------------------------------------------

------------------------*******----------------------------
inputFileName./upload/TestExcel2007.xlsx

Excel type:Excel2007
............

A5
GeneralvalueG00000759

B5Generalvalue
F

C5Generalvalue
UMTA

D5Generalvalue
UNNY-03-0344

E5Generalvalue14.08.80
F5Generalvalue3456
G5#,##0.00value61438180.11
H5#,##0.00value61438180.11
I5Generalvalue1234.77
J5Generalvalue****
K5Generalvalue****
End 5th row-----------------------------------------------------