unable to fetch '%' in output from excel 2007

Topics: Developer Forum, User Forum
Nov 13, 2010 at 11:40 AM

hi i am reading data from excel 2007 file. the problem is that the columns which contains '%'. it does not show the values in percentage. but convert it in float values.

 how can i get rid of this problem. how i can display % with the values.

Coordinator
Nov 13, 2010 at 12:07 PM

The data stored in the cell is a float value, and it is the number format mask that indicates it should be displayed as a percentage; so when you read the cell using getValue() you will get a float returned. You can either divide this value by 100 and append the '%' sign yourself, or you can use:

$cellData = PHPExcel_Style_NumberFormat::ToFormattedString(
     $objPHPExcel->getActiveSheet()->getCell('A1')->getValue(),    
     $objPHPExcel->getCellXfByIndex( $objPHPExcel->getActiveSheet()->getCell('A1')->getXfIndex() )->getNumberFormat()->getFormatCode() 
);

which will set $cellData with a string containing the cell value formatted according to the cell's number format mask

 

Nov 15, 2010 at 10:01 AM

 $j=1;                                                  // iterated.
  foreach ($cellIterator as $cell)
  {
     
      if($i==1)
      {
        echo "<td bgcolor='#0e3f7b' height=35 style='width:13%' class='headingboldwhite'>" . "" . $cell->getValue() .'</td>' . "\n"; 
      }
      else if($i>1)
      {
          if(fmod($i,2)==0 )
          {
              $color="white";
          }
          else
          {
              $color="#eaf0f2";
          }
   
          if($j==1)
          {
              echo "<td class="bodytext" style='color:#de5918;font-weight:bold;height:30px;' bgcolor='$color'>" . $cell->getValue() .'</td>' . "\n";
          }
          else
          {
                  echo "<td class="bodytext" bgcolor='$color'>" . $cell->getValue() ."</td>" . "\n";
          }
      }
     
      $j++;
  }
 
  echo '</tr>' . "\n";

  $i++;
}

you have given the code for a a specific column, where as i don't know which column contain percentage. i can be any column, so

how to check it and find the column containg percentage m unable to usderstand.

in highlighted line, i am printing the data. now please guide me how to convert it.

 

Coordinator
Nov 15, 2010 at 12:03 PM
Edited Nov 15, 2010 at 12:06 PM

 

echo "<td class="bodytext" bgcolor='$color'>" . 
     PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(), 
                                                     $cell->getParent()->getParent()->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode() 
                                                   ) . 
     "</td>" . "\n";

 As an alternative to what you're doing, building up HTML from the worksheet; you could always use PHPExcel's existing HTML Writer.

 

Nov 23, 2010 at 6:19 AM
Edited Nov 23, 2010 at 6:22 AM
MarkBaker wrote:

 

echo "<td class="bodytext" bgcolor='$color'>" . 
     PHPExcel_Style_NumberFormat::ToFormattedString( $cell->getValue(), 
                                                     $cell->getParent()->getParent()->getCellXfByIndex( $cell->getXfIndex() )->getNumberFormat()->getFormatCode() 
                                                   ) . 
     "</td>" . "\n";

 As an alternative to what you're doing, building up HTML from the worksheet; you could always use PHPExcel's existing HTML Writer.

 

i have run this code, but i got the same output. it also shows fractional values and does not display '%'.  :(

Nov 23, 2010 at 6:19 AM

the output is same. it neither displays '%' nor get the current cell format but simply converts data in fractional values and print them out. :(

i have tried a solution  which is  that

i check for values containing '.' then i multiply those values with 100 and concatinate '%' with those values. but its problem is that if we have values like 100%, 200%, 300% etc 

in excel file then we get output as 1, 2 , 3, 4.  in case of 1000%, 200% we get 10, 20 and so on.

Coordinator
Nov 23, 2010 at 8:37 AM

Can you please provide me with a specific example, perhaps a sample workbook that demonstrates this problem. This solution works perfectly well fo rme: if it doesn't work for you, then I need to know what is different in order to try and resolve your problem.