Convert integer to Excel column (letter or letter combination) - complete example PHP, unit test and explanation

Question

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

    /**
* 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(&$errorMessage, PDOStatement &$source, &$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->getActiveSheet();

    $sheet->setTitle($name);


    while ($row = $source->fetch(PDO::FETCH_ASSOC)){

        if ($rows === 0){

            $columnName = self::validateColumnNameArray($columnName, $row);

            $totalCols = count($row);

            $sheet->getStyle('A1:' . self::convertNumberToExcelCol($totalCols) . '1')->getFont()->setBold(true)->setSize(12);

            for ($column = 1; $column <= $totalCols; $column++){

                $sheet->getCell(self::convertNumberToExcelCol($column) . '1')->setValue($columnName[$column - 1]);

                $sheet->getColumnDimension(self::convertNumberToExcelCol($column))->setAutoSize(true);
            }

            $rows = 1;
        }

        $rows++;

        $column = 1;

        foreach ($row as $field){

            $sheet->getCell(self::convertNumberToExcelCol($column) . $rows)->setValue($field);

            $column++;
        }
    }

    $writer->save($path);

    unset($sheet, $writer, $excel);


    if ($rows < 1){

        if (is_file($path)){

            unlink($path);  
        }

        $errorMessage =str_replace("[TYPE]", "EXCEL", GeneralDbManager::getInstance()->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()->getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
    }
    else{

        $rows --;
    }

    return (empty($errorMessage) ? $path : "");
}

and we want to convert an integer value into an excel column using the convertNumberToExcelCol function.

Let's first explain this method to build the excel file and the next post will explain the algorithm to get the column.

The methods takes as parameter:

• ErrorMessage: use to return the error message
• source: contains the data to push to the excel file
• Rows: Use to return the number of data rows exported
• name: Name to give to the excel file
• columnName: an optional array used with human readable column name (or translation). If omitted, the method use the field name from the query.

The first rows are for initializing the parameters (PHP use loose typing, so we got to be careful with parameters).

This function make sure that the name have a valid name/extension:

/**
* 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) > 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, ".") > 1){

                        $name = str_replace(".", "", $name) . $extension;
                    }
                    else{

                        $name = str_replace(substr($name, strrpos($name, ".")), $extension, $name);
                    }
                }
            }
        }
    }

    return $name;
}

Then we open the connection to the excel file:

$excel = new PHPExcel();

$writer = PHPExcel_IOFactory::createWriter($excel, "Excel2007");

$sheet = $excel->getActiveSheet();

$sheet->setTitle($name);

This function makes sure that the column name array has the same length as the number of fields in the row array.

/**
* 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 &$columnName, array &$row){

    $buffer = array();

    $colPDO = count($row);

    $count = count($columnName);

    if ($count < $colPDO){

        foreach ($row as $key => $value){

            $buffer[] = $key;   
        }

        for($index = $count; $index < $colPDO; $index++){

            $columnName[] = $buffer[$index];
        }
    }

    unset($buffer);

    return $columnName;
}

Both validateFileExtention and validateColumnNameArray are meant to the shared code with the CSV creation function:

    /**
* 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(&$errorMessage, PDOStatement &$source, &$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->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 < 1){

        if (is_file($path)){

            unlink($path);  
        }

        $errorMessage =str_replace("[TYPE]", "CSV", GeneralDbManager::getInstance()->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()->getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
    }


    return (empty($errorMessage) ? $path : "");
}

If it's the first row we add to the excel file, then we set the basic formating:

if ($rows === 0){

            $columnName = self::validateColumnNameArray($columnName, $row);

            $totalCols = count($row);

            $sheet->getStyle('A1:' . self::convertNumberToExcelCol($totalCols) . '1')->getFont()->setBold(true)->setSize(12);

            for ($column = 1; $column <= $totalCols; $column++){

                $sheet->getCell(self::convertNumberToExcelCol($column) . '1')->setValue($columnName[$column - 1]);

                $sheet->getColumnDimension(self::convertNumberToExcelCol($column))->setAutoSize(true);
            }

            $rows = 1;
        }

With the getStyle method we set the header row in bold and 12 size.

The getCOlumnDimension method is used to set autosize so the user won't have to resize the column himself/herself when opening the file.

The rest of the loop is to transfer the data from the row array to the excel file.

After the loops, we close the connection and unset the variable use to manage Excel.

Then comes the error management:

if ($rows < 1){

        if (is_file($path)){

            unlink($path);  
        }

        $errorMessage =str_replace("[TYPE]", "EXCEL", GeneralDbManager::getInstance()->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()->getErrorMessage('EXPORT_NO_CREATED_FILE_ERR', 'We were not able to create the [TYPE] file: [NAME].'));
    }
    else{

        $rows --;
    }

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.

This allow me to reuse this generic message both in my excel and CSV file (or whatever what kind of file) I'm generating.

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.

Another way around is to use a function to clear the storage directory:

/**
* 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);
    }

}

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.

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.

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.

Finally the methods return the path to access the newly created file:

return (empty($errorMessage) ? $path : "");

but only if there was no error. So, if the function returns an empty string, that means that an error has occurred.

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:

$errorMessage = "";

            if ($_SESSION["adminAccount"]->updateAccountInfo($errorMessage,
                                                        (isset($_POST['FIRST_NAME_TEXT']) ? $_POST['FIRST_NAME_TEXT'] : $_SESSION["adminAccount"]->getFirstName()),
                                                        (isset($_POST['LAST_NAME_TEXT']) ? $_POST['LAST_NAME_TEXT'] : $_SESSION["adminAccount"]->getLastName()),
                                                        (isset($_POST['EMAIL_TEXT']) ? $_POST['EMAIL_TEXT'] : $_SESSION["adminAccount"]->getEmail()))){

                PageManager::displaySuccessMessage("Your account information were saved with success.", "USER_ACCOUNT_INFORMATION_SAVED");

            }
            else{

                PageManager::displayErrorMessage($errorMessage);    
            }

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.

Note: The unit test will be included by my answer.

Jonathan Parent-Lévesque from Montreal

Answer 1

Now here's the method allowing to convert an integer to an excel column.

An excel column can be a combination of one to three letters up to 16383 (XFD) which is the current limit for columns in an excel file:

https://support.office.com/en-nz/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

I use alongside my function an array containing all the english letters:

public static $letters = array(1 => "A", 2 => "B", 3=> "C", 4 => "D", 5 => "E", 6 => "F", 7 => "G", 8=> "H", 9=> "I", 10 => "J", 11 =>"K", 12 => "L", 13 => "M", 14 => "N", 15=> "O", 16 => "P", 17 => "Q", 18 => "R", 19 => "S", 20 => "T", 21 => "U", 22 => "V", 23 => "W", 24 => "X", 25 => "Y", 26 => "Z");

The function take a single parameter and assert that the value is numeric and between respect excel limitations:

public static function convertNumberToExcelCol($number){

    $column = "";

    if (is_numeric($number) and $number > 0 and $number < 16385){

If the number is between 1 and 26 (A-Z columns), then that's a piece of cake. We simply fetch the letter directly in the array.

$column = self::$letters[$number];

Let's test this:

for ($index = 1; $index < 27; $index++){

        $this->assertEquals(FileManager::$letters[$index], FileManager::convertNumberToExcelCol($index));   
    }

If the column if between 27 and 702 (AA-ZZ), the coding remains pretty simple:

    if ($number % 26 === 0){

        $first = floor($number / 26) - 1;

        $second = 26;
    }
    else{

        $first = floor($number / 26);

        $second = $number % 26;
    }

    $column = self::$letters[$first] . self::$letters[$second];

Since the english alphabet contains 26 letters, the whole algorithm is also on a base of 26.

For most of values, the we can simply get the first letter by rounding down the [number]/26 and the second letter by using the entire division (remaining of the division) of the number: [number] % 26.

$first = floor($number / 26);

$second = $number % 26;

Here are some example:

• 27 = AA
• 28 = AB
• 51 = AY
• 53 = BA

If the [number] % 26 = 0, by example: 52, 78, 104 and so on, then we must use a bit of different coding.

By example, 52 = AZ but, 52/26=2 (first letter) and 52%26=0 (second letter).

2=B and 0 is out of bound in our letter array. This is why, we must reduce the value by one for the first letter and force 26 as value for the second letter.

$first = floor($number / 26) - 1;

$second = 26;

Time for testing:

for ($first = 1; $first < 27; $first++){

        $temp = $first * 26;

        for ($second = 1; $second < 27; $second++){
            $this->assertEquals(FileManager::$letters[$first] . FileManager::$letters[$second], FileManager::convertNumberToExcelCol($temp + $second)); 
        }
    }

The real challenge comes when we try to manage three letters. Of course, you probably won't have a query with more than 702 fields, but for the sake of the completeness of the method and some real programming challenge, let's examinate how to do this!

At first, I had to try-error testing and end up with the following coding:

elseif($number < 1379){

    $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + 26);
}
elseif($number < 2028){

    $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);
}
elseif ($number < 2055){

    $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + 52);
}
elseif($number < 2704){

    $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);  
}
elseif ($number < 2731) {

    $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + 78); 
}
elseif ($number < 3380) {

    $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);      
}
elseif ($number < 3407){

    $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + 104);    
}

Yeah, that is no serious and you can go all the way up to 16k like this...

If you look closely, you'll see that there's a pattern drawing out of this. There are number that can be divide by 676 to get the first letter and modulo 676 to get the second and third letters. By example, 2027 = BBY.

The second pattern are numbers that can be divided for the first letter by the modulo of 702 + a compensation (26, 52, 78, 104, ...). That include number like 703=AAA.

Of course, 676 and 702 are both multiple of 26.

It took me quite a lot of calculating, but I came to realised that the second pattern is always a range of 27 numbers and those numbers always produce a number inferior to 27 (0-26) for the modulo of this number.

elseif($number < 2028){

    $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);
}
elseif ($number < 2055){

By example:

• 2028 % 676 = 0
• 2029 % 676 = 1
• ...
• 2054 % 676 = 26
• 2055 % 676 = 27 (now out of range)

Now that the range of number using the algorithm of 702 is found, we must determine how to calculate the compensation. The compensation is always 26 based, so there's probably a correlation with that...

For the range 2028-2054, the compensation is of 52.

• 2028 / 26 = 78
• 2029 / 26 = 78.03846
• ...
• 2054 / 26 = 79

For the range 2704-2730, the compensation is of 78.

• 2704 / 26 = 104
• 2730 / 26 = 105

As you can see, the rule is the [compensation] = [number] / 26 - 26 (minus 1 for the upper limit)).

So the coding for the coding for a three letters (previously an endless succession of if/else) can be resumed in 5 lines:

                if($number % 676 < 27){

                    $compensation = floor($number / 26) - 26;

                    $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + ($compensation % 26 === 0 ? $compensation : $compensation - 1));
                }
                else{
                    $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);
                }

The function returns the letter combination or an empty string if the parameter value was invalid.

Let's test this just to make sure I'm not fooling you:

        for ($first = 1; $first < 27; $first++){

        for ($second = 1; $second < 27; $second++){

            for ($third = 1; $third < 27; $third++){

                $temp = $first *  676 + (($second * 26) + $third);

                if ($temp < 16385){

                    $this->assertEquals(FileManager::$letters[$first] . FileManager::$letters[$second] . FileManager::$letters[$third], FileManager::convertNumberToExcelCol($temp));

                }
                else{

                    $this->assertEmpty(FileManager::convertNumberToExcelCol($temp + $index));   
                }
            }
        }
    }

Here's the complete function:

    /**
* Convert a $number to the letter (or combination of letters) representing a column in excel. 
*   Will return an empty string if $number is not a valid value.
*
* @param number Int must be is_numeric() and > 0 and < 16,385.
*
* @return String
*/
public static function convertNumberToExcelCol($number){

    $column = "";

    if (is_numeric($number) and $number > 0 and $number < 16385){

        if ($number < 27){

            $column = self::$letters[$number];
        }
        elseif ($number < 703){

            if ($number % 26 === 0){

                $first = floor($number / 26) - 1;

                $second = 26;
            }
            else{

                $first = floor($number / 26);

                $second = $number % 26;
            }

            $column = self::$letters[$first] . self::$letters[$second];
        }
        else{

            if($number % 676 < 27){

                $compensation = floor($number / 26) - 26;

                $column = self::$letters[floor($number / 702)] . self::convertNumberToExcelCol($number % 702 + ($compensation % 26 === 0 ? $compensation : $compensation - 1));
            }
            else{
                $column = self::$letters[floor($number / 676)] . self::convertNumberToExcelCol($number % 676);
            }   
        }
    }


    return $column;
}

And of course, as promised, here's the unit testing code for the excel/csv file creation.

    public function testvalidateFileExtention(){

    //invalid extension
    $this->assertEquals("woot", FileManager::validateFileExtention("woot", true));

    $this->assertEquals("woot", FileManager::validateFileExtention("woot", ""));

    $this->assertEquals("woot", FileManager::validateFileExtention("woot", "."));

    $this->assertEquals("woot.blu", FileManager::validateFileExtention("woot", ".b.l.u.."));


    //invalid name
    $this->assertEquals("my_file.blu", FileManager::validateFileExtention(true, ".blu"));

    $this->assertEquals("my_file.blu", FileManager::validateFileExtention("", ".blu"));

    $this->assertEquals("my_file.blu", FileManager::validateFileExtention(".woot", ".blu"));

    $this->assertEquals("woot.blu", FileManager::validateFileExtention("w.o.o.t.", ".blu"));


    //valid file name and extension
    $this->assertEquals("woot.blu", FileManager::validateFileExtention("woot", "blu"));

    $this->assertEquals("woot.blu", FileManager::validateFileExtention("woot", ".blu"));

}

public function testCreateCSVFromRS(){

    FileManager::emptyArchiveFolder();

    $errorMessage = "";
    $path = realpath(dirname(__FILE__)) . '/../Archive/woot.csv';
    $rows = 0;


    //no data to export
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table WHERE field_id='woot for loots'");

    $this->assertInstanceOf('PDOStatement', $rs);


    $this->assertEmpty(FileManager::createCSVFromRS($errorMessage, $rs, $rows, "woot"));

    $this->assertNotEmpty($errorMessage);

    $this->assertEquals(0, $rows);

    $this->assertFileNotExists($path);


    //data, but missing columns in the header array
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table LIMIT 100");

    $this->assertNotEmpty(FileManager::createCSVFromRS($errorMessage, $rs, $rows, "woot", array("homer", "simpson")));

    $this->assertInstanceOf('PDOStatement', $rs);

    $this->assertEmpty($errorMessage);

    $this->assertEquals(100, $rows);

    $this->assertFileExists($path);


    $handle = fopen($path, "r");

    $this->assertNotEquals(false, $handle);

    $row = fgetcsv($handle);


    $this->assertContains("homer", $row);

    $this->assertNotContains("id", $row);


    $this->assertContains("simpson", $row);

    $this->assertNotContains("field_id", $row);


    $this->assertContains("field_value", $row);

    $this->assertContains("language", $row);


    fclose($handle);


    //data, changing all columns in the header array
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table LIMIT 10");

    $this->assertNotEmpty(FileManager::createCSVFromRS($errorMessage, $rs, $rows, "woot", array("kyle", "eric", "kenny", "stan")));

    $this->assertInstanceOf('PDOStatement', $rs);

    $this->assertEmpty($errorMessage);

    $this->assertEquals(10, $rows);

    $this->assertFileExists($path);


    $handle = fopen($path, "r");

    $this->assertNotEquals(false, $handle);

    $row = fgetcsv($handle);


    $this->assertContains("kyle", $row);

    $this->assertNotContains("id", $row);


    $this->assertContains("eric", $row);

    $this->assertNotContains("field_id", $row);


    $this->assertContains("kenny", $row);

    $this->assertNotContains("field_value", $row);


    $this->assertContains("stan", $row);

    $this->assertNotContains("language", $row);


    fclose($handle);

    unlink($path);
}

public function testConvertNumberToExcelCol(){

    //invalid paramter
    $this->assertEmpty(FileManager::convertNumberToExcelCol("a"));

    $this->assertEmpty(FileManager::convertNumberToExcelCol(array()));

    $this->assertEmpty(FileManager::convertNumberToExcelCol(-1));

    $this->assertEmpty(FileManager::convertNumberToExcelCol(1000000000));


    //single letter
    for ($index = 1; $index < 27; $index++){

        $this->assertEquals(FileManager::$letters[$index], FileManager::convertNumberToExcelCol($index));   
    }

    //double letters
    for ($first = 1; $first < 27; $first++){

        $temp = $first * 26;

        for ($second = 1; $second < 27; $second++){
            $this->assertEquals(FileManager::$letters[$first] . FileManager::$letters[$second], FileManager::convertNumberToExcelCol($temp + $second)); 
        }
    }

    //tripple letters
    for ($first = 1; $first < 27; $first++){

        for ($second = 1; $second < 27; $second++){

            for ($third = 1; $third < 27; $third++){

                $temp = $first *  676 + (($second * 26) + $third);

                if ($temp < 16385){

                    $this->assertEquals(FileManager::$letters[$first] . FileManager::$letters[$second] . FileManager::$letters[$third], FileManager::convertNumberToExcelCol($temp));

                }
                else{

                    $this->assertEmpty(FileManager::convertNumberToExcelCol($temp + $index));   
                }
            }
        }
    }
}


public function testCreateExcelFromRS(){

    $errorMessage = "";
    $path = realpath(dirname(__FILE__)) . '/../Archive/woot.xlsx';
    $rows = 0;


    //no data to export
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table WHERE field_id='woot for loots'");

    $this->assertInstanceOf('PDOStatement', $rs);


    $this->assertEmpty(FileManager::createExcelFromRS($errorMessage, $rs, $rows, "woot"));

    $this->assertNotEmpty($errorMessage);

    $this->assertEquals(0, $rows);

    $this->assertFileNotExists($path);


    //data, but missing columns in the header array
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table LIMIT 100");

    $this->assertNotEmpty(FileManager::createExcelFromRS($errorMessage, $rs, $rows, "woot", array("homer", "simpson")));

    $this->assertInstanceOf('PDOStatement', $rs);

    $this->assertEmpty($errorMessage);

    $this->assertEquals(100, $rows);

    $this->assertFileExists($path);


    $reader = PHPExcel_IOFactory::createReaderForFile($path);
    $reader->setReadDataOnly(true);
    $excel = $reader->load($path);


    $this->assertEquals("homer", $excel->getSheet(0)->getCell('A1')->getValue());

    $this->assertEquals("simpson", $excel->getSheet(0)->getCell('B1')->getValue());

    $this->assertEquals("field_value", $excel->getSheet(0)->getCell('C1')->getValue());

    $this->assertContains("language", $excel->getSheet(0)->getCell('D1')->getValue());

    $excel->disconnectWorksheets();

    unset($excel);



    //data, changing all columns in the header array
    $rs = $this->fetchData("SELECT id, field_id, field_value, language FROM error_message_table LIMIT 10");

    $this->assertNotEmpty(FileManager::createExcelFromRS($errorMessage, $rs, $rows, "woot", array("kyle", "eric", "kenny", "stan")));

    $this->assertInstanceOf('PDOStatement', $rs);

    $this->assertEmpty($errorMessage);

    $this->assertEquals(10, $rows);

    $this->assertFileExists($path);


    $reader = PHPExcel_IOFactory::createReaderForFile($path);
    $reader->setReadDataOnly(true);
    $excel = $reader->load($path);


    $this->assertEquals("kyle", $excel->getSheet(0)->getCell('A1')->getValue());

    $this->assertEquals("eric", $excel->getSheet(0)->getCell('B1')->getValue());

    $this->assertEquals("kenny", $excel->getSheet(0)->getCell('C1')->getValue());

    $this->assertContains("stan", $excel->getSheet(0)->getCell('D1')->getValue());

    $excel->disconnectWorksheets();


    unlink($path);
}

private function fetchData($query, $db = "language_manager"){

    $_SESSION['domain'] = $db;

    $errorMessage = "";

    $dbManager = GeneralDBManager::getInstance();

    $rs = $dbManager->fetchData($query . ";/*th1s 1s a v4l1d qu3ry*/", $errorMessage);

    unset($dbManager);

    return $rs; 
}

Conclusion: The example coding allow you to automatically generate an excel (or a CSV) file from any query via PDO in one line of code.

In my previous employment, the lack of centralization led me to write countless functions (in VB6) to export data from the software (GEM-CAR). Of course, writing a basic data export function has nothing esoteric, yet every new export requires to write a new functions. Each function themselves end up to be a challenge to maintain over time.

With those centralise methods, only one line of code is require to generate a file. Meaning that you only have to change SQL query to change the content of the file (the values of the columnName array if you want multilingual support or avoid revealing the real name of your fields in your database).

I may be utopia for myself, but I assume that you read the whole explanation before blindly pasting my coding into your software. Copying/pasting coding directly from the internet is actually the worst thing one can do.

Not only you can't be sure you don't carry useless code tightly binded to it's original context, you can be sure you are using the code properly neither.

Even worse, it could even lead to a security breach in your system if copying a whole module from an unknown source. One can never be sure of the good or ill intent of an internet user...

Shall the force be with you all,

Jonathan Parent-Lévesque from Montreal