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

Question

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)

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

Mark Baker · Accepted Answer · 2016-01-22T17:19:01.703

6

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()

edited Jan 22 '16 at 17:19

answered Jan 22 '16 at 17:02

Mark Baker

209,507
32
346
385

Great. Can you explain how to do the iteration of the sheets in your response? – user2635901 Jan 22 '16 at 17:07
Why don't you read some of the documentation or look at some of the examples.... a lot of what you're asking is demonstrated there – Mark Baker Jan 22 '16 at 17:19
I was having a few issues finding exactly what I wanted from the documentation. You explained perfectly. – user2635901 Jan 22 '16 at 17:42

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

1 Answers1

Linked