PHPExcel setCellValueByColumnAndRow not writing data to spreadsheet

PHPphpexcel

i'm outputting data from a mysql database to an excel workbook using PHPExcel. My workbook has 3 sheets and most of it is working fine. I'm having problems with the last section of output to the third sheet. What I am trying to do is output a table with row headers and column headers the values of which are taken from a mysql table and then a figure per row/column combination also taken from the mysql table based on what that row/column header is. The row and column headers are written to the file as they should be but the inner table figures are not. When I echo the output to screen all the data is appearing and the row/ column iterations are incrementing as they should just the setCellValueByColumnAndRow appears to not be setting the values in the worksheet. The section of code that i'm having trouble with is below. Can anyone see where the problem is in my code? 

$objPHPExcel->setActiveSheetIndex(2);

while($srow = mysql_fetch_assoc($query_company))
{
$newarray[] = $srow['entity'];
}
$row4 = 2;
$col4 = 1;
while($trow = mysql_fetch_row($query_ctry))
{
$country = $trow[0];

while($comp = each($newarray))
{
$company = $comp[1];
$total = mysql_query("SELECT noparts FROM totalslist WHERE country = '$country' AND entity = '$company'") or die (mysql_error());
if ($numrows = mysql_num_rows($total) == 0)
{
$totalres = 0;
}
else
{
$result3 = mysql_fetch_row($total);
$totalres = $result3[0];
}

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col4, $row4, $totalres); 
$col4++;

}
reset($newarray);
$row4++;
$col4 = 1;
}

Best Answer

**Solution! Actually I solved this problem by changing the code slightly. I was writing the row and column headers first and then adding the inner table values after. I changed it so that I added the headers and data as I incremented through the rows and columns as shown in code below. Might help someone else.

$objPHPExcel->createSheet(2);
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setTitle('PivotTable');
$query_ctry = mysql_query("SELECT DISTINCT country FROM totalslist ORDER BY country");
$numctry = mysql_num_rows($query_ctry);
$query_company = mysql_query("SELECT DISTINCT entity FROM totalslist ORDER BY entity");
$numcomp = mysql_num_rows($query_company);

while($srow = mysql_fetch_assoc($query_company))
{
    $newarray[] = $srow['entity'];
}
$row3 = 2;
$col3 = 1;
while($trow = mysql_fetch_row($query_ctry))
{
    $country = $trow[0];

    while($comp = each($newarray))
    {
        $company = $comp[1];
        $total = mysql_query("SELECT noparts FROM totalslist WHERE country = '$country' AND entity = '$company'");
    if ($numrows = mysql_num_rows($total) == 0)
    {
        $totalres = 0;
    }
    else
    {
        $result3 = mysql_fetch_row($total);
        $totalres = $result3[0];
    }
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $row3, $country);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col3, 1, $company);
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col3, $row3, $totalres); 
    $col3++;

    }
        reset($newarray);
        $row3++;
        $col3 = 1;
}
        // Save Excel file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit();
Related Topic