Excel document not working after mysql_query() ??

Topics: User Forum
Jan 9, 2011 at 10:03 AM


Im trying to make a dynamic php document wich renders as an Excel 97-2003 document. I can get the Excel document to work and everything, but when i start collecting data from my database thrugh normal mysql_query() requests i can't download my document anymore, it just errors :(

The working code is as followed:

    // Start af Excel dokument
    // Header
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=".$filename."");
    header('Cache-Control: max-age=0');

    require_once 'class/PHPExcel.php';
    require_once 'class/PHPExcel/IOFactory.php';

    // Database og filnavn
    $filename = "holdliste.xls";
    $database = "tap_hold";
    $objPHPExcel = new PHPExcel();

    // S�tter indstillinger for Excel Ark
    $objPHPExcel->getProperties()->setCreator("Thor Astrup Pedersen")
                         ->setLastModifiedBy("Thor Astrup Pedersen")
                         ->setTitle("Tricking & Parkour Holdet i VIK")
                         ->setSubject("Hold Liste")
                         ->setDescription("Holdmedlemmer for Tricking & Parkour Holdet i VIK.")
                         ->setKeywords("Tricking Parkour holdliste thor astrup pedersen")
    // S�tter overskrifter
    $objPHPExcel->getActiveSheet()->setCellValue('A1', "#");
    $objPHPExcel->getActiveSheet()->setCellValue('B1', "Fulde Navn");
    $objPHPExcel->getActiveSheet()->setCellValue('C1', "Alder");
    $objPHPExcel->getActiveSheet()->setCellValue('D1', "Adresse");
    $objPHPExcel->getActiveSheet()->setCellValue('E1', "Telefon");
    $objPHPExcel->getActiveSheet()->setCellValue('F1', "Email");
    // Henter bruger data
    $sql = "SELECT * FROM `".$database."` WHERE `godkendt`='1' ORDER BY fullname ASC";
    $query = mysql_query($sql) or die(mysql_error());
    // Formatere Celler s� det ser p�nt ud
    // Renamer sheetet
    // Afslutter Excel Ark
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    // Slut af Excel dokument

Where i wrote on english (sorry the rest of the comments is in danish) /* THIS IS WHERE MY DOCUMENT GOES ROUGE! */ is where i get my data from my database and plot them into the excel document.

This is the fetch and plot code of my data:

// Henter bruger data
    $sql = "SELECT * FROM `".$database."` WHERE `godkendt`='1' ORDER BY fullname ASC";
    $query = mysql_query($sql) or die(mysql_error());
    // Inds�tter folk i Excel Ark
    $row = 2; // Hvilken r�kke folk starter med at blive listet i
    while($d = mysql_fetch_array($query))
        // Henter bruger of s�tter f�dselsdag
        $date       = date("d-m-Y", $d['fodselsdag']);
        $bruger     = $this->login->getUser($d['userid']);
        // Adresse formatering
        $adresse    = $d['adresse'] . ", " . $d['postnr'] . " " . $d['by'];
        $adresse    = utf8_encode($adresse);
        // Telefon formatering
        $telefon = $d['telefon'];
        $telefon = str_replace(" ", "", $telefon); // fjerner mellemrum            
        // ID formatting
        $id = $row-1;
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $id);
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $d['fullname']);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $row, $date);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $row, $adresse);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $row, $telefon);
        $objPHPExcel->getActiveSheet()->setCellValue('F' . $row, $bruger['email']);

When i paste this in already when i only paste in the $sql and $query lines in the beginning it stops working!? What am I doing wrong? (again sorry for the danish comments)

Best Regards,
Thor The Dane 

Jan 9, 2011 at 10:27 PM

First question: what exactly do you mean by "going rogue"? You say it errors: what error message do you get?

What encoding are you using in your database?

Jan 9, 2011 at 11:01 PM

Just a quick reply here, remember to go from:

$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $id);
$objPHPExcel->getActiveSheet()->setCellValue('A' . $row, utf8_encode($id));

If you didn't see it, utf8_encode() for the value inserted.
Kim Steinhaug, kim@steinhaug.com, www.easywebshop.no


Jan 10, 2011 at 11:24 AM



I don't see a mysql_connect() call anywehere, are you sure a db connection is available? Since you use a die() if the query fails, are yiou sure the query actually succeeds? Furthermore, if you want mysql to output utf8 character encodings, you have to tell it explicitly to do so, otherwise strange results may occur. I don't see the point of utf8-encoding an integer value anyway.




Jan 10, 2011 at 2:11 PM

i was about to write i testet out some minor things, and then found a stupid ass error :P
my bad!!

I was getting my user information through this line of code:


and what i should have done (i made this loop in a class some months ago) is call: "$cms->getUser($id)" DOH!!!

My bad everybody :P