Why is the cell data duplicated in each column?

Topics: Developer Forum, User Forum
Dec 30, 2013 at 6:55 AM
I'm new to PHPExcel and somehow I got the same data duplicated in the Excel spreadsheet using the following code:
$roster_sql = "SELECT * FROM EMPLOYEE";
$roster_result = mysql_query($roster_sql) or die(mysql_error());

//Populate 2D Array
$kcount = 0;
while($sheet_array = mysql_fetch_array($roster_result))
{
$sheet[$kcount] = $sheet_array;
$kcount++ ;
}
$rowID = 1;
foreach($sheet as $rowArray)
{
$columnID = 'A';
foreach($rowArray as $columnValue)
{
  $objPHPExcel->getActiveSheet()->setCellValue($columnID.$rowID, $columnValue);
  $columnID++;
}
$rowID++;
}

The generated spreadsheet has each cell value duplicated in the column. Did I populate the array incorrectly or is there something wrong when I write out the array?

Further, how do I write the header row in the same spreadsheet without hard coding the column names from the SQL?

Thanks for your help.
Dec 30, 2013 at 7:42 AM
mysql_fetch_array($roster_result)
This return a array with string keys (fields name) AND numeric keys. Use MYSQL_ASSOC or MYSQL_NUM as 2nd param, not the default MYSQL_BOTH.
With MYSQL_ASSOC, you have the names for yours column headers...

Note : MySQL extension isn't recommended for writing new code.
Dec 30, 2013 at 8:02 AM
Thank you so much, LWol! The duplicate data issue is resolved right away!
Could you help me on getting the header row info and how to write it to the same spreadsheet?
Dec 30, 2013 at 12:41 PM
There are several ways to do this...
Considering that you ask mysql_fetch_array you return an associative array, you can retrieve the field names using array_keys then iterate in this array to write your headers.
Use a flag to do so only once when you course records.
$rowID=2//header in row 1, so first data in row 2
$writeHeader=true;
foreach($sheet as $rowArray)
{
    if($writeHeader){
        $hArray=array_keys($rowArray);
        $ColumnIdx=0; //column A, row for header hardcoded to 1
        foreach($hArray as $aHeader){
            $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($ColumnIdx++, 1, $aHeader);
        }
        $writeHeader=false;
    }
    $columnID = 'A';
    foreach($rowArray as $columnValue)
    {
      $objPHPExcel->getActiveSheet()->setCellValue($columnID.$rowID, $columnValue);
      $columnID++;//Warning: if you go after Z, this don't work
    }
    $rowID++;
}
Not tested :-)
I use setCellValueExplicitByColumnAndRow for two reasons:
-Explicit because I consider that the header is composed only of strings (which is the implicit type of explicit ;-)
-ByColumnAndRow: to avoid (bad) calculations on the column.
As I indicated in note in the code, increment the column when it is in the form of letter can lead to errors. If it is necessary to have as well, use the functions of support of PHPExcel (PHPExcel_Cell::columnIndexFromString and PHPExcel_Cell::stringFromColumnIndex) to convert the string to integer, make operations, convert the integer to string.

In doing so, if the table is empty, there is not any header.
Dec 30, 2013 at 5:36 PM
LWol:
Many thanks again for your great help. It works beautifully!
As for the $columnID++ increment, it actually works for me. I'm able to save all my data in the proper columns beyond "Z", up to column "AO" in my case.