title: PHPExcel: Invalid cell coordinate error
body: <p>I'm extending a controller in CodeIgniter that handles PHPExcel. So far, I've not gotten anywhere near column Z, until now. Having arrived, I'm greeted with:</p>
  
  <blockquote>
    <p>Fatal error: Uncaught exception 'Exception' with message 'Invalid cell
    coordinate AA' in /PATH/phpexcel/PHPExcel/Cell.php:513 Stack trace: #0
    /PATH/phpexcel/PHPExcel/Worksheet.php(1119):
    PHPExcel_Cell::coordinateFromString('AA') #1
    /PATH/phpexcel/PHPExcel/Worksheet.php(1022):
    PHPExcel_Worksheet->getCell('AA') #2
    /PATH/application/modules/bookings/controllers/export.php(138):
    PHPExcel_Worksheet->setCellValue('AA', 'Purchase Order ...') #3
    [internal function]: Export->index() #4
    /PATH/system/core/CodeIgniter.php(297): call_user_func_array(Array,
    Array) #5 /PATH/index.php(163): require_once('/PATH...') #6 {main}
    thrown in /PATH/phpexcel/PHPExcel/Cell.php on line 513</p>
  </blockquote>
  
  <p>I've written some code to generate AB-AZ BB-BZ columns, but — as you can see above — PHPExcel isn't having it.</p>
  
  <pre><code>$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;
  $cacheSettings = array('memoryCacheSize' =&gt; '32MB');
  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
  $objPHPExcel = new PHPExcel();
  $objPHPExcel-&gt;getProperties()-&gt;setTitle($arrayAttr['form']['title']);
  $objPHPExcel-&gt;getProperties()-&gt;setDescription("Export of Advanced Search");
  $objPHPExcel-&gt;getProperties()-&gt;setCreator("Me!");
  $objPHPExcel-&gt;getProperties()-&gt;setLastModifiedBy("Me!");
  // Assign cell values
  $objPHPExcel-&gt;setActiveSheetIndex(0);
  $a=1;
  $p=1;
  for ($s=65; $s&lt;=90; $s++):
      // Assign the letters of the alphabet.
      for ($i=65; $i&lt;=90; $i++):
          if (chr($s) == "A"):
              $array_column_indices[$a] = chr($i) . $p;
          else:
              $array_column_indices[$a] = chr($s-1) . chr($i);
          endif;
          $a++;
      endfor;
      $p++;
  endfor;
  $b=2;
  foreach ($arrayAttr['results']['data_search']['results'] as $index_search_advanced =&gt; $array_value_search_advanced):
      $x=1;
      foreach ($arrayAttr['results']['data_search']['results'][0] as $keys =&gt; $values):
          if (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings'])
              &amp;&amp; ($keys != 'clients_options')):
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x],
              $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings'][$keys]);
              $x++;
          elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings_attendees'])
              &amp;&amp; ($keys != 'clients_options')):
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x],
              $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['bookings_attendees'][$keys]);
              $x++;
          elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['venues'])
              &amp;&amp; ($keys != 'clients_options')):
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x],
              $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['venues'][$keys]);
              $x++;
          elseif (array_key_exists($keys, $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['values'])
              &amp;&amp; ($keys != 'clients_options')):
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x],
              $arrayAttr['results']['config_database_tables']['tables'][$arrayAttr['results']['group']]['values'][$keys]);
              $x++;
          elseif ($keys == 'clients_options'):
              $array_options_for_booking = explode(chr(0x1D), $values);
              $a_=0;
              $array_options_sorted = array();
              foreach ($array_options_for_booking as $option):
                  if (strstr($option, chr(0x1F))):
                      $array_options_sorted[] = implode('', array_slice(explode(chr(0x1F), $option), $a_, 1));
                  else:
                      $array_options_sorted[] = $option;
                  endif;
                  $a_++;
              endforeach;
              // Write the custom column values.
              foreach ($array_options_fields as $index_options =&gt; $array_values_options):
                  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x], $array_values_options['label']);
                  $x++;
              endforeach;
          endif;
      endforeach;
      // Keep a count of the actual columns within the results, rather than just the columns used when displaying the results.
      $c=1;
      foreach ($array_value_search_advanced as $value_search_advanced):
          // "U" is the column where the custom values first occur.
          if (strstr($array_column_indices[$c], "U")):
              $array_options_for_booking = explode(chr(0x1D), $value_search_advanced);
              $a_=0;
              $array_options_sorted = array();
              foreach ($array_options_for_booking as $option):
                  if (strstr($option, chr(0x1F))):
                      $array_options_sorted[] = implode('', array_slice(explode(chr(0x1F), $option), $a_, 1));
                  else:
                      $array_options_sorted[] = $option;
                  endif;
                  $a_++;
              endforeach;
              $d=$c;
              $e=1;
              // Write the custom column values.
              foreach ($array_options_sorted as $index_options =&gt; $array_values_options):
                  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$d] . $b, $array_values_options);
                  $d++;
                  $e++;
              endforeach;
              $c=($d-1);
          // Else, if the columns are Venue Rate...
          elseif (strstr($array_column_indices[$c], "E")):
              // ... apply the number format to the cells.
              $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($array_column_indices[$c] . $b)-&gt;getNumberFormat()-&gt;setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
              // Write the regular column values.
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
          // Else, if the Number of Nights...
          elseif (strstr($array_column_indices[$c], "O")):
              // ... apply the number format to the cells.
              $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($array_column_indices[$c] . $b)-&gt;getNumberFormat()-&gt;setFormatCode();
              // Write the regular column values.
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
          else:
              // Write the regular column values.
              $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$c] . $b, $value_search_advanced);
          endif;
          $c++;
      endforeach;
      $b++;
  endforeach;
  $z = ($b-1);
  //print_r($objPHPExcel);
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueExplicit("E" . $b, '=SUM(E2:E' . $z . ')');
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueExplicit("O" . $b, '=SUM(O2:O' . $z . ')');
  header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
  header("Cache-Control: no-store, no-cache, must-revalidate");
  header("Cache-Control: post-check=0, pre-check=0", false);
  header("Pragma: no-cache");
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="Search Results.xls"');
  // Save it as a Micrtosoft Excel 2003 file
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
  $objWriter-&gt;save("library/profiles/reports/spreadsheet.xls");
  $objWriter-&gt;save('php://output');
  $objPHPExcel-&gt;disconnectWorksheets();
  unset($objPHPExcel);
  </code></pre>
  
  <p>Line 138 is:</p>
  
  <pre><code>$objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue($array_column_indices[$x], $array_values_options['label']);
  </code></pre>
  
  <p>Just to reiterate, everything was smooth as silk until I added more columns in the MySQL query.</p>
  
  <p>PHPExcel is something of a mystery to me, so any assistance would be wonderful.</p>
  
  <p><strong>Edit 11 June, 10:05am GMT</strong></p>
  
  <p>I've made some changes to the for() loops:</p>
  
  <pre><code>$a=1;
  $p=1;
  $r=0;
  for ($s=65; $s&lt;=90; $s++):
      // Assign the letters of the alphabet.
      for ($i=65; $i&lt;=90; $i++):
          if (chr($s) == "A"):
              $array_column_indices[$a] = chr($i) . $p;
          else:
              $array_column_indices[$a] = chr($s-1) . chr($i) . $p;
          endif;
          $a++;
      endfor;
      $r++;
  endfor;
  </code></pre>
  
  <p>While this has gotten around the column Z problem, I'm seeing strange things in the spreadsheet:</p>
  
  <ul>
  <li>After the column names in row 1, I have 10 row gap of empty cells.</li>
  <li>A block of 8 rows with data.</li>
  <li>13 rows of empty cells.</li>
  <li>A row containing a number of cells with SUM() formula in them.</li>
  <li>Almost a 100 rows of empty cells.</li>
  <li>A block of 23 rows with data.</li>
  </ul>
  
  <p>8 + 23 = 31 rows of data, which is correct, but as to why the formulas are in between, and the gaps? No idea.</p>
  
  <p>So, 2 steps forwards, 1 step backward.</p>