Member Avatar for accra

am trying to export data from mysql db to excel format, but the excel file i get is damaged.
i get two varibles by $_GET, and run a search, and then creates the excel file based on the results...

                        `$p_na = $_GET['p_na'];
                         $svreg = $_GET['svreg'];



                        include_once("classes/PHPExcel.php");
                        $result = mysql_query("SELECT * FROM history WHERE  p_na LIKE '$p_na%' AND svreg LIKE '$svreg%' ");  //select query
                        // Execute the database query
                        //$result = mysql_query($query) or die(mysql_error());

                        // Instantiate a new PHPExcel object
                        $objPHPExcel = new PHPExcel(); 
                        // Set the active Excel worksheet to sheet 0
                        $objPHPExcel->setActiveSheetIndex(0); 
                        // Initialise the Excel row number
                        $rowCount = 1; 
                        // Iterate through each result from the SQL query in turn
                        // We fetch each database result row into $row in turn
                        while($row = mysql_fetch_array($result)){ 
                            // Set cell An to the "name" column from the database (assuming you have a column called name)
                            //    where n is the Excel row number (ie cell A1 in the first row)
                            $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['p_na']); 
                            // Set cell Bn to the "age" column from the database (assuming you have a column called age)
                            //    where n is the Excel row number (ie cell A1 in the first row)
                            $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['svreg']); 
                            // Increment the Excel row counter
                            $rowCount++; 
                        } 

                        // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
                        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
                        // Write the Excel file to filename some_excel_file.xlsx in the current directory
                        $objWriter->save('some_excel_file.xlsx'); 

            //header to make force download the file
            header("Content-Disposition: attachment; filename=Export_Excel".date('d-m-Y').".csv");
            print $contents;    `
Edited by accra
  • 4 Contributors
  • 3 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by furqan219

Recommended Answers

All 3 Replies

Member Avatar for diafol

So which bit of this is causing a problem?
What have you done so far to track down the problem? Is it the querystring, the query, your implementation of PHPExcel?

Member Avatar for Bachu

Check the below link.Very simple code for Export MySQL table to CSV using PHP.

Click Here

Member Avatar for furqan219

Below is a code for export to excel in PDO

<?php
    // Time limit to 0 for exporting big records.
    set_time_limit(0); 
    // mysql hostname
    $hostname = 'localhost';
    // mysql username
    $username = 'root';
    // mysql password
    $password = '';
    // Database Connection using PDO with try catch method. 
    try { $dbh = new PDO("mysql:host=$hostname;dbname=PaginationTestDb", $username, $password); }
    // In case of error PDO exception will show error message.
    catch(PDOException $e) {    echo $e->getMessage();    }
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment;Filename=Server_Stats.xls");
    echo "<html>";
    echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
    echo "<body>";
    echo "<table border=1>";
    echo"<tr><th>Sr#</th><th>Server</th><th>Hi Memory Utilization</th><th>Avg Memory Utilization</th><th>Hi CPU Utilization</th><th>Avg CPU Utilization</th><th>Hi I/O Utilization</th><th>Avg I/O Utilization</th><th>Hi Disk Usage</th><th>Avg Disk Usage</th><th>By</th></tr>";
    // We will assign variable here for entry By. you can use your variables here.
    $EntryBy = $_GET[val];
    // Get data using PDO prepare Query.
    $STM2 = $dbh->prepare("SELECT `SrNo`, `ServerName`, `HiMemUti`, `AvgMemUti`, `HiCpuUti`, `AvgCpuUti`, `HiIOPerSec`, `AvgIOPerSec`, `HiDiskUsage`, `AvgDsikUsage`, `EntryBy` FROM statstracker WHERE EntryBy = :EntryBy ORDER BY SrNo");
    // bind paramenters, Named paramenters alaways start with colon(:)
    $STM2->bindParam(':EntryBy', $EntryBy);
    // For Executing prepared statement we will use below function
    $STM2->execute();
    // We will fetch records like this and use foreach loop to show multiple Results later in bottom of the page.
    $STMrecords = $STM2->fetchAll();
    // We use foreach loop here to echo records.
    foreach($STMrecords as $r)
        {
            echo "<tr>";
            echo "<td>" .$r[0] ."</td>";
            echo "<td>" .$r[1] ."</td>";
            echo "<td>" .$r[2] ."</td>";
            echo "<td>" .$r[3] ."</td>";
            echo "<td>" .$r[4] ."</td>";
            echo "<td>" .$r[5] ."</td>";
            echo "<td>" .$r[6] ."</td>";
            echo "<td>" .$r[7] ."</td>";
            echo "<td>" .$r[8] ."</td>";
            echo "<td>" .$r[9] ."</td>";
            echo "<td>" .$r[10] ."</td>";
            echo "</tr>";  
        }
    echo "</table>";
    echo "</body>";
    echo "</html>";
    // Closing MySQL database connection   
    $dbh = null;
?>
Edited by pritaeas because: Please stop promoting your website. If you want, you can put a link in your signature, but do not add a link to each of your replies.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.