PHPExcel setCellValueByColumnAndRow不将数据写入电子表格 [英] PHPExcel setCellValueByColumnAndRow not writing data to spreadsheet

问题描述

我正在使用PHPExcel将数据从mysql数据库输出到excel工作簿.我的工作簿有3张纸，大多数都工作正常.我在输出到第三张纸的最后一部分时遇到问题.我想做的是输出带有行标题和列标题的表，其值取自mysql表，然后基于行/列标题的取值也取自mysql表的每个行/列组合的值.行标题和列标题应按原样写入文件，但内部表数字则不然.当我将输出回显到屏幕时，所有数据都会出现，并且行/列迭代正在递增，因为它们应该只是setCellValueByColumnAndRow似乎没有在工作表中设置值.我遇到问题的代码部分如下.谁能看到我的代码中的问题所在?

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;
}

推荐答案

**解决方案！ 实际上，我通过稍微更改代码解决了这个问题.我先写行标题和列标题，然后再添加内部表值.我对其进行了更改，以便在行和列中递增时添加标题和数据，如下面的代码所示.可能会帮助别人.

**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();

这篇关于PHPExcel setCellValueByColumnAndRow不将数据写入电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！