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
$objPHPExcel>setActiveSheetIndex(0);
// Initialise the Excel row number
$rowCount = 2;
$objPHPExcel>getActiveSheet()>SetCellValue('A1', 'ATB')>getStyle('A1')>getFill()>applyFromArray(
array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'startcolor' => array('rgb' => 'FFFF00'),
'endcolor' => array('rgb' => 'FFFF00'),
)
);
$objPHPExcel>getActiveSheet()>SetCellValue('B1', 'ATA')>getStyle('B1')>getFill()>applyFromArray(
array(
'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
$rowCount++;
}
// 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
$objWriter>save('report_actual.xlsx');
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.
