Errors while trying to query MySQL and export to excel 2007

Topics: User Forum
Mar 1, 2014 at 8:27 AM
Edited Mar 1, 2014 at 8:36 AM
I am new to PHPExcel and have run into an issue and was hoping to get some guidance.

I have modified the 30template.php example file. My goal is to be able to run queries against MySQL and populate the results into a template file. I need to actually loop over the template and generate (at current count) 89 new worksheets in the same workbook.

I added in my first query and, in so doing, the script now fails to write the file and it is throwing errors upon execution. Interestingly, I am not doing anything with the query data. No part of the script has changed so I am mystified why it breaks upon adding the query.

Can anyone see what is going on here and/or advise on 'best practices' to achieve my goal (stated above)? Here is my code:
<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
 * @version    1.7.9, 2013-06-02
 */

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('America/Chicago');

/** PHPExcel_IOFactory */
require_once '/phpexcel/Classes/PHPExcel/IOFactory.php';

// Need to get which fields to use based on pool type
// Need to gather data to populate excel fields

// Testing variables
include 'init.php';
$event_id = 25;
$pool_type = "5pool";
$pool_name = "Pool_C4A_1";

// Static variables
$filename = "PSG_PoolCard_Template2.xlsx";
$dir = "files/PSG_PoolCard_Template2.xlsx";

echo date('H:i:s') , " Load from Excel2007 template" , EOL;
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($dir);

$init_cnt = "";


echo date('H:i:s') , " Add new data to the template" , EOL;




//Query to get pool names and associated schedule types
$query = "SELECT DISTINCT(event_team.pool_name), event_team.pool_sched_type, event_div.div_id, event_team.div_sname, event_team.pool_num, event_team.div_lname
        FROM event_team, event_div, divisions 
        WHERE event_team.div_lname = divisions.div_lname 
        AND event_div.div_id = divisions.div_id 
        AND event_team.event_id = $event_id 
        AND event_team.active = 1 
        AND event_team.pool_name IS NOT NULL 
        ORDER BY event_team.pool_name;";

//echo "|".$query."|<br>";   // Leave this line for debugging the sql query.

$result = mysqli_query($conn, $query);
    
if (!$result) 
{
    echo "Could not run query: $query" . mysqli_error($conn);
    exit;
}

// Declare variables
$pool_name = array();
$pool_sched_type = array();
$div_id = array();
$div_sname = array();
$pool_num = array();
$div_lname = array();

/// Push data into the arrays
while ($row = mysqli_fetch_row($result))
{
    array_push ($pool_name, $row[0]);
    array_push ($pool_sched_type, $row[1]);
    array_push ($div_id, $row[2]);
    array_push ($div_sname, $row[3]);
    array_push ($pool_num, $row[4]);
    array_push ($div_lname, $row[5]);
    //array_push ($div_name, mysql_real_escape_string($row[1]));
}   






if ($pool_type == "4pool")
{
    $newSheet = clone $objPHPExcel->getSheetByName("4team");
    $newSheet->setTitle($pool_name);
    $init_cnt = $objPHPExcel->getSheetCount();
    //echo date('H:i:s') , " Sheet count is ".$init_cnt , EOL;
    //$new_cnt = $init_cnt+1;
    //$newSheetIndex = $new_cnt;
    //echo date('H:i:s') , " NEW sheet count is ".$new_cnt , EOL;
    $objPHPExcel->addSheet($newSheet,$init_cnt); // Use $init_cnt because sheets are 0 indexed
    
    $objPHPExcel->setActiveSheetIndex($init_cnt)
                           ->setCellValue('C3', "Coed")
                           ->setCellValue('C5', "pool_name")
                           ->setCellValue('C7', "A1")
                           ->setCellValue('F4', "Team 1")
                           ->setCellValue('F5', "Team 2")
                           ->setCellValue('F6', "Team 3")
                           ->setCellValue('F7', "Team 4");  
}

if ($pool_type == "5pool")
{
    $newSheet = clone $objPHPExcel->getSheetByName("5team");
    $newSheet->setTitle($pool_name);
    $init_cnt = $objPHPExcel->getSheetCount();
    //echo date('H:i:s') , " Sheet count is ".$init_cnt , EOL;
    //$new_cnt = $init_cnt+1;
    //$newSheetIndex = $new_cnt;
    //echo date('H:i:s') , " NEW sheet count is ".$new_cnt , EOL;
    $objPHPExcel->addSheet($newSheet,$init_cnt); // Use $init_cnt because sheets are 0 indexed
    
    $objPHPExcel->setActiveSheetIndex($init_cnt)
                           ->setCellValue('C3', "Coed")
                           ->setCellValue('C5', "pool_name")
                           ->setCellValue('C7', "A1")
                           ->setCellValue('C9', "A2")
                           ->setCellValue('G4', "Team 1")
                           ->setCellValue('G5', "Team 2")
                           ->setCellValue('G6', "Team 3")
                           ->setCellValue('G7', "Team 4")
                           ->setCellValue('G8', "Team 5");  
}

//$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel(time()));


echo date('H:i:s') , " Write to Excel2007 format" , EOL;
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$save_dir = "D:\\htdocs\\psg\\files\\PSG_PoolCards_".date("mdy").".xlsx";
$objWriter->save($save_dir);
//D:\htdocs\psg\files
//echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo date('H:i:s') , " File written to " , $save_dir , EOL;

// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;
The following error message says the file was created successfully but the file itself is corrupted and will not open. This is the error message(s) I am getting when calling the page:
02:09:41 Load from Excel2007 template
02:09:41 Add new data to the template

Warning: iconv_strlen() expects parameter 1 to be string, array given in D:\htdocs\psg\phpexcel\Classes\PHPExcel\Shared\String.php on line 557
02:09:41 Write to Excel2007 format

Warning: XMLWriter::writeElement() expects parameter 2 to be string, array given in D:\htdocs\psg\phpexcel\Classes\PHPExcel\Writer\Excel2007\DocProps.php on line 104

Warning: XMLWriter::writeAttribute() expects parameter 2 to be string, array given in D:\htdocs\psg\phpexcel\Classes\PHPExcel\Writer\Excel2007\Workbook.php on line 250

Notice: Array to string conversion in D:\htdocs\psg\phpexcel\Classes\PHPExcel\Worksheet.php on line 2599
02:09:42 File written to D:\htdocs\psg\files\PSG_PoolCards_030114.xlsx
02:09:42 Peak memory usage: 17.5 MB
02:09:42 Done writing file
File has been created in D:\htdocs\psg

Warning: Illegal offset type in isset or empty in D:\htdocs\psg\phpexcel\Classes\PHPExcel\Calculation.php on line 1924
Thanks in advance!!!!
Mar 4, 2014 at 8:59 AM
It would be useful to have the call stack to see where does the initial error (see extension xdebug on a development machine).
After a quick reading of your code, I see $pool_name = array () (and the array_push later) then calling setTitle with $pool_name. This method expects a string.