PHPExcel Loop SetCellValue from a custom cell

Question

I need to loop from Cell V1 up to Cell HA1 custom period of DATES. Here is a manual way of doing it:

$objPHPExcel->getActiveSheet()->SetCellValue('V1', '2015-11-29');
$objPHPExcel->getActiveSheet()->SetCellValue('W1', '2015-11-30');
$objPHPExcel->getActiveSheet()->SetCellValue('X1', '2015-12-01');
$objPHPExcel->getActiveSheet()->SetCellValue('Y1', '2015-12-02');
$objPHPExcel->getActiveSheet()->SetCellValue('Z1', '2015-12-03');
$objPHPExcel->getActiveSheet()->SetCellValue('AA1', '2015-12-04');

But this is obviously too much code. and note that i cannot use any other function then SetCellValue as there are 20 more cell values before those. I need to loop the dates from CELL V1 2015-11-29 up to CELL HA1 2016-06-04 how can i do that?

General question: Why are you storing dates as strings when you should probably be storing MS Excel Serialized DateTime values and setting a formatting mask — Mark Baker, Dec 09 '15 at 10:17
@MarkBaker what is the correct way? I am not very familiar with PHPExcel and excel generally — Ilanus, Dec 09 '15 at 10:22

Mark Baker · Accepted Answer · 2015-12-09T10:33:21.820

This isn't particularly a PHPExcel method, just a standard PHP approach to looping

You just need to create a loop from column V to column HA and increment the column address and date value each iteration

$startColumn = 'V';
$endColumn = 'HA';

$date = new DateTime('2015-11-29');
$interval = new DateInterval('P1D');

$endColumn++;
for($column = $startColumn; $column !== $endColumn; $column++) {
    $objPHPExcel->getActiveSheet()
        ->SetCellValue($column.'1', $date->format('Y-m-d'));
    $date->add($interval);
}

However, you should really be using MS Serialized Date/Time values for dates, and setting a formatting mask to display the values as dates:

$startColumn = 'V';
$endColumn = 'HA';
$lastColumn = $endColumn;

$date = new DateTime('2015-11-29');
$interval = new DateInterval('P1D');

$endColumn++;
for($column = $startColumn; $column !== $endColumn; $column++) {
    $objPHPExcel->getActiveSheet()
        ->SetCellValue(
            $column.'1',
            PHPExcel_Shared_Date::PHPToExcel($date)
        );
    $date->add($interval);
}
$objPHPExcel->getActiveSheet()
    ->getStyle($startColumn.'1:'.$lastColumn.'1')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

PHPExcel Loop SetCellValue from a custom cell

1 Answers1