title: Convert integer to Excel column (letter or letter combination) - complete example PHP, unit test and explanation
body: <p>Let's suppose we have a function like this one that takes a PDOStatement (any query) and auto generates an excel file (using PHPExcel library):</p>
  
  <pre><code>    /**
  * Create an Excel file from an opened PDOStatement. Return the path to access the Excel file
  *   or an empty string if we were not able to create the Excel File
  *
  *
  * @param $errorMessage String to return the error message.
  * @param $source PDOStatement containing the data to export to Excel. 
  * @param $rows Int Use to return the number of rows exported (the header row isn't counted).
  * @param $name String name to give to the Excel file (no need to specify the extension).
  * @param $columnName (optional) String Array used for the name of the row in the Excel file.
  *
  * @return String
  */
  public static function createExcelFromRS(&amp;$errorMessage, PDOStatement &amp;$source, &amp;$rows , $name, array $columnName = array()){
  
      $errorMessage = "";
  
      $name = self::validateFileExtention($name, "xlsx");
  
      $path = realpath(dirname(__FILE__)) . '/Archive/' .  $name;
  
      $rows = 0;
  
      $totalCols = 0;
  
  
      $excel = new PHPExcel();
  
      $writer = PHPExcel_IOFactory::createWriter($excel, "Excel2007");
  
      $sheet = $excel-&gt;getActiveSheet();
  
      $sheet-&gt;setTitle($name);
  
  
      while ($row = $source-&gt;fetch(PDO::FETCH_ASSOC)){
  
          if ($rows === 0){
  
              $columnName = self::validateColumnNameArray($columnName, $row);
  
              $totalCols = count($row);
  
              $sheet-&gt;getStyle('A1:' . self::convertNumberToExcelCol($totalCols) . '1')-&gt;getFont()-&gt;setBold(true)-&gt;setSize(12);
  
              for ($column = 1; $column &lt;= $totalCols; $column++){
  
                  $sheet-&gt;getCell(self::convertNumberToExcelCol($column) . '1')-&gt;setValue($columnName[$column - 1]);
  
                  $sheet-&gt;getColumnDimension(self::convertNumberToExcelCol($column))-&gt;setAutoSize(true);
              }
  
              $rows = 1;
          }
  
          $rows++;
  
          $column = 1;
  
          foreach ($row as $field){
  
              $sheet-&gt;getCell(self::convertNumberToExcelCol($column) . $rows)-&gt;setValue($field);
  
              $column++;
          }
      }
  
      $writer-&gt;save($path);
  
      unset($sheet, $writer, $excel);
  
  
      if ($rows &lt; 1){
  
          if (is_file($path)){
  
              unlink($path);  
          }
  
          $errorMessage =str_replace("[TYPE]", "EXCEL", GeneralDbManager::getInstance()-&gt;getErrorMessage('NO_DATA_TO_EXPORT_FILE_ERR', 'There is no data to export to the [TYPE] file.'));    
      }
      elseif(!is_file($path)){
  
          $errorMessage = str_replace(array("[TYPE]", "[NAME]"), array("EXCEL", $name), GeneralDbManager::getInstance()-&gt;getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
      }
      else{
  
          $rows --;
      }
  
      return (empty($errorMessage) ? $path : "");
  }
  </code></pre>
  
  <p>and we want to convert an integer value into an excel column using the <code>convertNumberToExcelCol</code> function.</p>
  
  <p>Let's first explain this method to build the excel file and the next post will explain the algorithm to get the column.</p>
  
  <p>The methods takes as parameter:</p>
  
  <ul>
  <li><strong>ErrorMessage</strong>: use to return the error message</li>
  <li><strong>source</strong>: contains the data to push to the excel file</li>
  <li><strong>Rows</strong>: Use to return the number of data rows exported</li>
  <li><strong>name</strong>: Name to give to the excel file</li>
  <li><strong>columnName</strong>: an optional array used with human readable column name (or translation). If omitted, the method use the field name from the query.</li>
  </ul>
  
  <p>The first rows are for initializing the parameters (PHP use loose typing, so we got to be careful with parameters).</p>
  
  <p>This function make sure that the name have a valid name/extension:</p>
  
  <pre><code>/**
  * Validate that the file $name has the proper file $extension
  * and return the fixed name with the proper extension
  *
  * Note: No modification will be made if the extension is not a string or is empty
  *
  * @param $name String file name with or without extension
  * @param $extension String example: csv, xls
  *
  * @return String
  */
  public static function validateFileExtention($name, $extension){
  
      if (is_string($extension)){
  
          $extension = "." . str_replace(".", "", $extension);
  
          if (strlen($extension) &gt; 1){
  
              if (!is_string($name) or empty($name) or strpos($name, ".") === 0){
  
                  $name = "my_file" . $extension; 
              }
              elseif(strpos(strtolower($name), $extension) === false){
  
                  if (strrpos($name, ".") === false){
  
                      $name .= $extension;    
                  }
                  else{
  
                      if (substr_count($name, ".") &gt; 1){
  
                          $name = str_replace(".", "", $name) . $extension;
                      }
                      else{
  
                          $name = str_replace(substr($name, strrpos($name, ".")), $extension, $name);
                      }
                  }
              }
          }
      }
  
      return $name;
  }
  </code></pre>
  
  <p>Then we open the connection to the excel file:</p>
  
  <pre><code>$excel = new PHPExcel();
  
  $writer = PHPExcel_IOFactory::createWriter($excel, "Excel2007");
  
  $sheet = $excel-&gt;getActiveSheet();
  
  $sheet-&gt;setTitle($name);
  </code></pre>
  
  <p>This function makes sure that the column name array has the same length as the number of fields in the row array.</p>
  
  <pre><code>/**
  * Take the array containing the $columnName for data export (CSV, Excel) and make sure
  * that it is the number of entry as there are fields in $row.
  *
  * If column name are missing, we will use the column name used in the query.
  *
  * Return the merged array
  *
  * @param $columnName Array containing the column names
  * @param $row Array produce by fetch(PDO::FETCH_ASSOC).
  *
  * @return Array ($columnName)
  */
  private static function validateColumnNameArray(array &amp;$columnName, array &amp;$row){
  
      $buffer = array();
  
      $colPDO = count($row);
  
      $count = count($columnName);
  
      if ($count &lt; $colPDO){
  
          foreach ($row as $key =&gt; $value){
  
              $buffer[] = $key;   
          }
  
          for($index = $count; $index &lt; $colPDO; $index++){
  
              $columnName[] = $buffer[$index];
          }
      }
  
      unset($buffer);
  
      return $columnName;
  }
  </code></pre>
  
  <p>Both <code>validateFileExtention</code> and <code>validateColumnNameArray</code> are meant to the shared code with the CSV creation function:</p>
  
  <pre><code>    /**
  * Create a CSV file from an opened PDOStatement. Return the path to access the CSV file
  *   or an empty string if we were not able to create the CSV File
  *
  *
  * @param $errorMessage String to return the error message.
  * @param $source PDOStatement containing the data to export to CSV
  * @param $rows Int Use to return the number of rows exported (the header row isn't counted).
  * @param $name String name to give to the CSV file (no need to specify the extension).
  * @param $columnName (optional) String Array used for the name of the row in the CSV file.
  *
  * @return String
  */
  public static function createCSVFromRS(&amp;$errorMessage, PDOStatement &amp;$source, &amp;$rows , $name, array $columnName = array()){
  
      $errorMessage = "";
  
      $name = self::validateFileExtention($name, "csv");
  
      $path = realpath(dirname(__FILE__)) . '/Archive/' . $name;
  
      $rows = 0;
  
  
      $file = fopen($path, "w");
  
      while ($row = $source-&gt;fetch(PDO::FETCH_ASSOC)){
  
          if ($rows === 0){
  
              fputcsv($file, array_map('utf8_decode',self::validateColumnNameArray($columnName, $row)));
          }
  
          fputcsv($file, array_map('utf8_decode',array_values($row)));
  
          $rows++;
      }
  
      fclose($file);
  
  
      if ($rows &lt; 1){
  
          if (is_file($path)){
  
              unlink($path);  
          }
  
          $errorMessage =str_replace("[TYPE]", "CSV", GeneralDbManager::getInstance()-&gt;getErrorMessage('NO_DATA_TO_EXPORT_FILE_ERR', 'There is no data to export to the [TYPE] file.'));  
      }
      elseif(!is_file($path)){
  
          $errorMessage = str_replace(array("[TYPE]", "[NAME]"), array("CSV", $name), GeneralDbManager::getInstance()-&gt;getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
      }
  
  
      return (empty($errorMessage) ? $path : "");
  }
  </code></pre>
  
  <p>If it's the first row we add to the excel file, then we set the basic formating:</p>
  
  <pre><code>if ($rows === 0){
  
              $columnName = self::validateColumnNameArray($columnName, $row);
  
              $totalCols = count($row);
  
              $sheet-&gt;getStyle('A1:' . self::convertNumberToExcelCol($totalCols) . '1')-&gt;getFont()-&gt;setBold(true)-&gt;setSize(12);
  
              for ($column = 1; $column &lt;= $totalCols; $column++){
  
                  $sheet-&gt;getCell(self::convertNumberToExcelCol($column) . '1')-&gt;setValue($columnName[$column - 1]);
  
                  $sheet-&gt;getColumnDimension(self::convertNumberToExcelCol($column))-&gt;setAutoSize(true);
              }
  
              $rows = 1;
          }
  </code></pre>
  
  <p>With the getStyle method we set the header row in bold and 12 size.</p>
  
  <p>The getCOlumnDimension method is used to set autosize so the user won't have to resize the column himself/herself when opening the file.</p>
  
  <p>The rest of the loop is to transfer the data from the row array to the excel file.</p>
  
  <p>After the loops, we close the connection and unset the variable use to manage Excel.</p>
  
  <p>Then comes the error management:</p>
  
  <pre><code>if ($rows &lt; 1){
  
          if (is_file($path)){
  
              unlink($path);  
          }
  
          $errorMessage =str_replace("[TYPE]", "EXCEL", GeneralDbManager::getInstance()-&gt;getErrorMessage('NO_DATA_TO_EXPORT_FILE_ERR', 'There is no data to export to the [TYPE] file.'));    
      }
      elseif(!is_file($path)){
  
          $errorMessage = str_replace(array("[TYPE]", "[NAME]"), array("EXCEL", $name), GeneralDbManager::getInstance()-&gt;getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
      }
      else{
  
          $rows --;
      }
  </code></pre>
  
  <p>The message are stored in a database so we can offer translated message to the user. I use generic [TYPE] and [NAME] tag in my message which I replace with the proper file type and file name.</p>
  
  <p>This allow me to reuse this generic message both in my excel and CSV file (or whatever what kind of file) I'm generating.</p>
  
  <p>If the file created is empty, I erase it. This operation is optional, but I like to clear unused file from the disk as soon as I'm done with it.</p>
  
  <p>Another way around is to use a function to clear the storage directory:</p>
  
  <pre><code>/**
  * Clear all the archives (zip) files in the archive folder.
  */
  public static function emptyArchiveFolder(){
  
      $handle = NULL;
      $path = realpath(dirname(__FILE__)) . '/Archive/';
  
      if (is_dir($path) and $handle = opendir($path)) {
  
          while (false !== ($entry = readdir($handle))) {
  
              $file = $path . $entry;
  
              if (is_file($file)){
  
                  unlink($file);  
              }
          }
  
          unset($handle);
      }
  
  }
  </code></pre>
  
  <p>I'm personally only using this methods when I'm doing the automatic backup procedure of my source file and database at midnight. Running it during the day increase the chance of deleting files use by another user.</p>
  
  <p>This is why I'm considering best practice to delete the files as soon as they are sent to the user via the browser and leave the cleaning methods for maintenance purpose only.</p>
  
  <p>If there are no errors I decrement the number of rows by one since I don't want to count the header row. That line can be removed if you consider the header row like a data row.</p>
  
  <p>Finally the methods return the path to access the newly created file:</p>
  
  <pre><code>return (empty($errorMessage) ? $path : "");
  </code></pre>
  
  <p>but only if there was no error. So, if the function returns an empty string, that means that an error has occurred. </p>
  
  <p>PHP being type loose, you can return anything including boolean or even the error message, but I prefer returning always the same data type for constancy purpose. My personal favorite methods is boolean return values and an error message variable passed by reference. So I can use code like this:</p>
  
  <pre><code>$errorMessage = "";
  
              if ($_SESSION["adminAccount"]-&gt;updateAccountInfo($errorMessage,
                                                          (isset($_POST['FIRST_NAME_TEXT']) ? $_POST['FIRST_NAME_TEXT'] : $_SESSION["adminAccount"]-&gt;getFirstName()),
                                                          (isset($_POST['LAST_NAME_TEXT']) ? $_POST['LAST_NAME_TEXT'] : $_SESSION["adminAccount"]-&gt;getLastName()),
                                                          (isset($_POST['EMAIL_TEXT']) ? $_POST['EMAIL_TEXT'] : $_SESSION["adminAccount"]-&gt;getEmail()))){
  
                  PageManager::displaySuccessMessage("Your account information were saved with success.", "USER_ACCOUNT_INFORMATION_SAVED");
  
              }
              else{
  
                  PageManager::displayErrorMessage($errorMessage);    
              }
  </code></pre>
  
  <p>That way, the error is managed internally by the class method and the success message can be adjusted base on the view context. The boolean return value is used to determine whether we must display the error or the success message.</p>
  
  <p><strong>Note</strong>: The unit test will be included by my answer.</p>
  
  <p>Jonathan Parent-Lévesque from Montreal</p>