help! having trouble with dates...

Nov 25, 2008 at 10:19 AM

help..

i have this project in which i had to read an excel file with php and it does...
but the problem is i cannot display the dates in the original format... from 6/30/08 it displays something like this 39629

here is my script:

<?
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/'); //Classes dir

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
 
$objPHPExcel = $objReader->load("test.xlsx"); //ARCHIVE excel2007 dir

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Arial'); //how to use this? is this only for fomatting?
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(8); //how to use this? is this only for fomatting?

$hrows = $objPHPExcel->getActiveSheet()->getHighestRow(); //total rows
$varray = array("A","B","C","D","E","F","G","H","I"); //these are my columns

//the C1..X is my date column formatted as 6/30/08

echo "<table border='1'>";
for ($row=6;$row<=$hrows;$row++){ //start of my data

    echo "<tr>";
    for ($col=0;$col<count($varray);$col++){ //fields
        $vcell = $varray[$col].$row;
        $val = ($objPHPExcel->getActiveSheet()->getCell($vcell)); //CELL
        
        if($varray[$col] == "I"){
            $temp = $val->getCalculatedValue();//returns the value in the cell
        }
        else{
            $temp = $val->getvalue();//returns the value in the cell
        }
        

        if($temp != ""){
            
            if($varray[$col] == "I" || $varray[$col] == "H" || $varray[$col] == "G"){
                echo "<td align='right'>".number_format($temp, 2, '.', ',')."</td>";
            }
            else{
                echo "<td>".$temp."</td>";
            }
            
        }
        else{
            echo "<td>&nbsp;</td>";
        }
    }
    echo "</tr>";
}

?>

and also is it possible that by reading the excel file can i copy or display its appearance/original formating in the browser?


Hope you could help me...
Thanks in advance....


Coordinator
Nov 25, 2008 at 1:09 PM

Dates are held in Excel as a date/timestamp value representing the number of days since 1st January 1900 (or 1st January 1904, depending on the operating platform/calendar). The actual formatting of dates in Excel so that they appear as a date, is handled by applying a formatting mask to the value.
PHP dates are held as a date/timestamp value representing the number of seconds since 1st January 1970.

The actual value in the Excel cell (39629) is the Excel date/timestamp. You need to format this to display it as a date in your HTML. You can convert it to a PHP date/timestamp value by calling the PHPExcel_Shared_Date::ExcelToPHP() method:

$phpDateValue = PHPExcel_Shared_Date::ExcelToPHP($excelDateValue);

and then using the PHP date functions to format it as you wish.

If you want to use Excel's defined format by reading the formatting mask, then you can use the PHPExcel_Style_NumberFormat::ToFormattedString() method:

$formattedDateString = PHPExcel_Style_NumberFormat::ToFormattedString($objPHPExcel->getActiveSheet()->getCell($cellID)->getValue(), $objPHPExcel->getActiveSheet()->getStyle($cellID)->getNumberFormat()->getFormatCode())

 

Coordinator
Nov 25, 2008 at 1:11 PM
Edited Nov 25, 2008 at 1:12 PM
>> and also is it possible that by reading the excel file can i copy or display its appearance/original formating in the browser?
Certainly it is.
Take a look at the HTML writer, and see how it creates styles based on the excel style settings, and then builds an HTML table applying those styles to the individual cells

In fact, for what you're doing in the code above, you could simply use the PHPExcel HTML writer to build the table
Nov 26, 2008 at 4:12 AM


hi again... hi mark thanks for the reply...
but still no luck...
do i make things right?

$vcell = $varray[$col].$row;
$val = ($objPHPExcel->getActiveSheet()->getCell($vcell)); //CELL

$temp = $val->getvalue();
                
$temp = PHPExcel_Shared_Date::ExcelToPHP($temp); // 39629 to 6/30/08 how to use it?
$temp = PHPExcel_Style_NumberFormat::ToFormattedString($objPHPExcel->getActiveSheet()->getCell($vcell)->getValue(), $objPHPExcel->getActiveSheet()->getStyle($vcell)->getNumberFormat()->getFormatCode());
//then 6/30/08 to PHP format?


im a little mix up...


on more thing, is it possible to make a query directly to excel file using the class?

Thanks in Advance....


Coordinator
Nov 26, 2008 at 9:52 PM
Nope, you're combining to different ways of performing the same task:

Either
$vcell = $varray[$col].$row;
$val = ($objPHPExcel->getActiveSheet()->getCell($vcell)); //CELL
$temp = $val->getvalue();
$temp = PHPExcel_Shared_Date::ExcelToPHP($temp); // 39629 to 6/30/08 how to use it?
echo date('m/d/y',$temp);    // Standard PHP date function, which you can use to format the date however you want

Or
$vcell = $varray[$col].$row;
$val = ($objPHPExcel->getActiveSheet()->getCell($vcell)); //CELL
$temp = $val->getvalue();
$temp = PHPExcel_Style_NumberFormat::ToFormattedString($temp, $objPHPExcel->getActiveSheet()->getStyle($vcell)->getNumberFormat()->getFormatCode());


>> on more thing, is it possible to make a query directly to excel file using the class?
Not quite sure what you mean by this... you've already read the excel file when you do $objPHPExcel = $objReader->load("test.xlsx");
What other queries do you want to make?

Nov 27, 2008 at 2:40 AM


got it! thanks mark!


about the queries...
can i pass sql queries to the class to get some information i wanted to display?

for example: sql queries on dates? between dates... directly to excel file? and the result/display will be the covering dates that i posted in the browser?


thanks in advance....
Coordinator
Nov 27, 2008 at 7:37 AM
>> can i pass sql queries to the class to get some information i wanted to display?
>>
The answer to this is "No".
SQL is a language for accessing data within a structured relational database, but an Excel workbook is simply a series of unstructured unrelated data.