Phpexcel Column Loop

PHPExcel Column Loop

There is no ColumnIterator, so you'll have to do this by hand.

For any given worksheet:

To loop rows for a column:

$column = 'A';
$lastRow = $worksheet->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

To loop columns in a row, you can take advantage of PHP's Perls-style ability to increment characters:

$row = 1;
$lastColumn = $worksheet->getHighestColumn();
$lastColumn++;
for ($column = 'A'; $column != $lastColumn; $column++) {
    $cell = $worksheet->getCell($column.$row);
    //  Do what you want with the cell
}

Note that when comparing column letters to test for the last column in the loop, we can't simply use < or <= because we're comparing strings, and "B" > "AZ" in standard string comparison, so we use a != comparison, having incremented the highest column value to give the first column ID past the end point.

You can also use

$worksheet->cellExists($column.$row);

in the loop to test for the existence of a cell before accessing it using getCell() (or not) to emulate the iterator getIterateOnlyExistingCells() behaviour

The iterators are actually fairly slow, so you may well find these simple loops are faster than using the iterators.

UPDATE (2015-05-06)

PHPExcel version 1.8.1 has introduced a new Column Iterator. The Row and Column iterators also allows you to specify a range of rows or columns to iterate, and allow you to use prev() and well as next() when looping through

Looping through columns and rows in PHPExcel to create objects

Read row 1 to get a list of the column headings; this provides a heading map. It's probably a good idea to use the 5th argument for rangeToArray() so that you can get an array indexed by row/column number, so you can use the column address for easy lookup into the map.

Then loop through the data rows from row 2, returning a row/column indexed array again so that you can easily check the header mapping, testing for the cells that contain values, and looking up their column address against the column headings.

Something like:

$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

$columnLoopLimiter = $highestColumn;
++$columnLoopLimiter;
// get the column headings as a simple array indexed by column name
$headings = $sheet->rangeToArray('A1:' . $highestColumn . 1, NULL, TRUE, FALSE, TRUE)[1];

//  Loop through each data row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++)
{ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE, TRUE);
    echo $rowData[$row]['A'], '</br>';
    for ($column = 'B'; $column !== $columnLoopLimiter; ++$column) {
        if (!empty($rowData[$row][$column])) {
            echo $headings[$column], ':', $rowData[$row][$column], '</br>';
        }
    }
}

Note the $column !== $columnLoopLimiter;, I've incremented $columnLoopLimiter previously to the column immediately after the last column so that we can do a !== comparison rather than a <=. This takes advantage of PHP's perl-style character incrementor, but ensures that we do a clean comparison when the incrementor loops through Z to AA.

PHPExcel loop through rows and columns

As you're using the same variable $row for the row number in the Excel iteration and for the result of your select query, it's not surprising that you're running into problems.....

The integer value that holds the Excel row number is being overwritten by the array that you get from your SQL query, and then you're trying to use that result array as the next Excel row number

Solution: Use a different variable for these two elements.

$rowData = mysql_fetch_array($sql);
echo $rowData["Max_No"];**

PHPExcel Loop SetCellValue from a custom cell

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 Multi Sheet Loop Issue

You should first move the sheet generation code from the foreach($subarray loop to your foreach($this->AllSheetData (since you want to add new sheet for every.. well.. new sheet. Not for every new sheet property).

You should then use a very similar code to the one you had, and $counter will be used only within that part of the code. Note that to create a new sheet and place is as the last one, you should simply pass null to the createSheet() method.

So your code should look like this:

public function ExportMultiSheet() {

    ...
    $count = 0;
    foreach($this->AllSheetData as $subarray)
    {
      if ($count > 0)
      {
          $this->PHPExcel->createSheet(null);
          $this->PHPExcel->setActiveSheetIndex($count);
      }
      $count++

      foreach($subarray as $key => $value)
      ...
    }
    ...

PhpExcel many columns causes the file to be broken

This isn't a PHPExcel or a PhpSpreadsheet issue: what's happening here is that you're misunderstanding how the PHP increment operator works with a string value that contains mixed alpha and numeric characters.

Take a look at the output from your increment when there's no reference to PHPExcel or PhpSpreadsheet to see what's happening here:

$col ='A1';
for($x = 0; $x <= 12; ++$x){
    echo $col, PHP_EOL;
    ++$col; //in a loop;
}

PHP increments the numeric character until it reaches the size limit for that numeric (1 digit, so 1 through 9). It then resets the digit to 0,and increments the alpha (so 'A' becomes 'B'), and subsequent increments will increment the numeric again until it reaches 9, then it will reset the numeric back to 0 and increment the alpha again:

A1
A2
A3
A4
A5
A6
A7
A8
A9
B0
B1
B2
B3

Either set the column to A and concatenate with a hard-coded row 1

$col ='A';
foreach($columns as $key => $value){
    $sheet->setCellValue($col . '1',  $value);
    ++$col; //in a loop;
}

or make use of PhpSpreadsheet's fromArray() method to populate the whole row from the array that you already have

---

Related Topics