How to make DateTime format applied without double clicking that particulat column

Topics: Developer Forum, User Forum
Nov 29, 2013 at 4:37 AM
Hi All,

I've managed to generate and download an excel file from oracle database using PHPexcel, below is my coding for reference:-

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
// Initialise the Excel row number
$rowCount = 2;

$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'ATB')->getStyle('A1')->getFill()->applyFromArray(
      'type'       => PHPExcel_Style_Fill::FILL_SOLID,
      'startcolor' => array('rgb' => 'FFFF00'),
      'endcolor'   => array('rgb' => 'FFFF00'),

$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'ATA')->getStyle('B1')->getFill()->applyFromArray(
      'type'       => PHPExcel_Style_Fill::FILL_SOLID,
      'startcolor' => array('rgb' => 'FFFF00'),
      'endcolor'   => array('rgb' => 'FFFF00')

// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($objResult = oci_fetch_array($objParse,OCI_ASSOC+OCI_RETURN_NULLS)){
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $objResult["ATB"])->getStyle('A'.$rowCount)->getNumberFormat()->setFormatCode('dd/mm/yyyy hh:mm:ss');
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $objResult["ATA"])->getStyle('B'.$rowCount)->getNumberFormat()->setFormatCode('dd/mm/yyyy hh:mm:ss');
    // Increment the Excel row counter

// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// Write the Excel file to filename some_excel_file.xlsx in the current directory
So when I open the excel file, the column value of ATB and ATA is formatted as dd/mm/yyyy hh:mm:ss. My intention right now is to SUM all column for ATB and get the total sum as a number. But the problem right now is after SUM all ATB the result is 0, so I need to double click each column for ATB and it will start to calculate the total SUM. Just for your information, my excel file look something like this:

| ATB | ATA |
|22/03/2013 07:00:00 | 21/03/2013 20:20:00 |
|23/03/2013 17:10:00 | 22/03/2013 04:45:00 |

I've noticed when I double click on that particular column, at the formula bar the value (example for ATB 22/03/2013 07:00:00) became like this one 22/3/2013 7:00:00 AM. When the value became like that, the total SUM column will automatically start summing. Imagine if I need to double click one thousand rows!

So I would like to seek any advise/assistance/opinion from experts. Is there any easy way to SUM all ATB without doing this messy work? I would prefer if the SUM also can be calculated automatically using PHPexcel.

(p/s: i knew that excel SUM function is based on 1/1/1900 00:00:00)

Glad if someone could help me. Many thanks.
Nov 30, 2013 at 4:28 PM
I think that your values from your database, are seen as text. The phenomenon that you recorded by double-clicking a cell is Excel that does the conversion work.
If you want the conversion to be performed by PHPExcel,, you can explicitly do the work (using the PHPExcel_Shared_Date methods, setCellValueExplicit with a number type, setFormatCode,...) or use an Advanced Value Binder (one that is provided in example handles texts that "look like" dates/times, see 4.5.6 documentation and code).
Do some testing, but as a first step, I would use database output the universal format (yyyy-mm-dd hh:mm:ss) and I'd let the binder update format: native formats (those that Excel displays with a ' *') are automatically converted to the locale.

Whatever the method chosen, each cell will contain a number, and PHPExcel will make the sum (I am however curious sense that one can give to this operation).