export data from mysql to excel/csv format

Question

accra 1 Junior Poster in Training

11 Years Ago

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

mysql php

Edited 11 Years Ago by accra

4 Contributors
3 Replies
2K Views
3 Days Discussion Span
Latest Post 11 Years Ago Latest Post by furqan219

diafol

11 Years Ago

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?

Reply to this topic

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.

Bachu 45 Newbie Poster · Answer 1 · 2013-11-04T06:15:08+00:00

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

Click Here

furqan219 -9 Junior Poster in Training · Answer 2 · 2013-11-06T05:01:47+00:00

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