[FIXED] How can I create a new column and concatenate some values using php Excel?

Issue

I want to read an excel file and add a new column to the left of the first column on each sheet using PHP. (The excel file can have as many as 10 sheets)

2. concatenate "AFS-" to the data in each row of the first column and store the new string in the new column to the left.

Eg. sheet 1

Solution

To insert a single new column before column A:

$objPHPExcel->getActiveSheet()->insertNewColumnBefore('A', 1);

Then you can either insert the new values for cells in column 'A' as a formula:

for($row = 2; $row = 20; $row++) {
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A'.$row, '="AFS-" && B'.$row);
}

or as an absolute value

for($row = 2; $row = 20; $row++) {
    $objPHPExcel->getActiveSheet()
        ->setCellValue('A'.$row, 'AFS-' . $objPHPExcel->getActiveSheet()->getCell("B".$row)->getValue());
}

To do this for all sheets, just iterate over the sheets

EDIT

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    ...
}

and then reference $worksheet instead of $objPHPExcel->getActiveSheet()

Answered By - Mark Baker

Answer Checked By - Candace Johnson (PHPFixing Volunteer)