Format Date Columns & $Columns

Topics: Developer Forum
Aug 8, 2012 at 2:58 PM

Hello,

     I seem to be having a little trouble formatting 2 columns and I was wondering if someone could give me a few pointers as too how I can format these columns. One column I am trying to format is a date column. My code reads an Excel spreadsheet and returns numbers like 40809, and the date should read 9/23/2011. The next column I am trying to format contains prices. When my code reads the $ column it returns numbers like 200000...instead of $200,000. Here is my code

<?php
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC'); // Php's date function uses this value!



require_once 'C:\wamp\www\PHPExcel\Classes\PHPExcel\IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("worksheet.xls");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo "<br>The Worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' rows.';
    echo '<br>Data: <table border="1"><tr>';
    for ($row = 1; $row <= $highestRow; ++ $row) {
        echo '<tr>';
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
            $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
            echo '<td>' . $val . '<br> </td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

date_default_timezone_set($saveTimeZone);
?>
Any help would be greatly appreciated!! Thanks guys!!

Aug 8, 2012 at 3:39 PM

 

$val = $cell->getFormattedValue(); // Returns formatted cell data.
Aug 8, 2012 at 3:56 PM

COOL BEANS!!!! Thanks schirl1964!!! I went back to my code and changed  

$val = $cell->getValue(); to 
$val = $cell->getFormattedValue();

and it worked perfect!! Thanks again!!