Junk in excel during MySQL export

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
kushmakarsharma
Forum Newbie
Posts: 1
Joined: Mon Dec 14, 2009 2:03 am

Junk in excel during MySQL export

Post by kushmakarsharma »

Hi,

I have been trying to generate an excel report from MySQL database. The query is working fine but the excel contains a lot of junk data along with the valid result. I have provided the source code file along with this post. Please let me know what am i doing wrong.

Regards,
Kush

Code: Select all

 
<?php
    defined('_JEXEC') or die('Restricted Access');
    $file = getcwd();//Get the current working directory
?>
<html>
    <head>
        <script type="text/javascript" src="<?=$file;?>\components\com_content\lightbox\lightbox.js"></script>
        <link rel="stylesheet" href="<?=$file;?>\components\com_content\lightbox\lightbox1.css" />
    </head>
</html>
<?php
    //Some other code
    Call to function exportlist()
    }
    function exportlist(){
        $file = getcwd();
        $eventId = JRequest::getVar('eveid');
        require_once("$file\configuration.php");
        $config = new JConfig();
 
        $host = $config->host;
        $user = $config->user;
        $pass = $config->password;
        $db = $config->db;
        $file = 'export';
 
        $conn = mysql_connect($host,$user ,$pass) or die ("Could not connect to host:".mysql_error());
        $db_selected = mysql_select_db($db,$conn) or die ("Could not connect to database:".mysql_error());
 
        $result = mysql_query("select firstname,lastname from jos_comprofiler where user_id IN(Select userid from jos_jev_register where eventid = $eventId)", $conn) or die ("Could not execute query:".mysql_error());
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header('Content-type: text/html; charset=UTF-8');
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");;
        header("Content-Disposition: attachment;filename=ListofParticipants.txt");
        header("Content-Transfer-Encoding: binary ");
 
        xlsBOF();
 
        /*
        Make a top line on your excel sheet at line 1 (starting at 0).
        The first number is the row number and the second number is the column, both are start at '0'
        */
        xlsWriteLabel(0,0,"Serial Number");
        xlsWriteLabel(0,1,"Name of participant");
 
        // Make column labels. (at line 3)
 
        $xlsRow = 1;
 
        // Put data records from mysql by while loop.
        while($row=mysql_fetch_array($result)){
        xlsWriteLabel($xlsRow,0,"$xlsrow");
        xlsWriteLabel($xlsRow,1,"$row[firstname]"."$row[lastname]");
 
        $xlsRow++;
        }
        xlsEOF();
        exit();
    }
    function xlsBOF() {
        echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
        return;
    }
    function xlsEOF() {
        echo pack("ss", 0x0A, 0x00);
        return;
    }
    function xlsWriteNumber($Row, $Col, $Value) {
        echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
        echo pack("d", $Value);
        return;
    }
    function xlsWriteLabel($Row, $Col, $Value ) {
        $L = strlen($Value);
        echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
        echo $Value;
        return;
    }
?>
 
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Junk in excel during MySQL export

Post by josh »

What about the output it is giving, you didn't post it. And I don't have a jos_comprofiler table on my machine :D
Post Reply